Database Discussion Boards

That method is not backup up the database; it is creating a script to drop and recreate the tables in the database.

And it's not doing a very good job of it. It won't work with tables in different schemas, and it won't work with tables that have "special" characters in their names. Also, the generated script will only work in a database where the tables already exist.

A database backup will backup the whole database. If you only want a copy of certain rows from certain tables in your database, then you need to export that data to a file. You can either write code to do it, or use the Import and Export wizard[^] to do it.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

I can not use the Import and Export wizard, i must use in my soft by functions add on write by c#.
I think can backup the rows with command SQL but I do not how to edit the code.

thank for you repl.

Here is my attempt at providing a solution for you ….

You can convert this to C# and provide some more paramters such as table name, special Select statement, etc

Give this a shot and see if it works for you.

David
Cool | :cool:

Private Sub ExportTable()
      Dim rdr As SqlDataReader = Nothing

      Using SW As New StreamWriter("c:\temp\zMyTable.txt")
          Using sqlconn As New SqlConnection("<put your connection string here>")
              sqlconn.Open()

              Using sqlcmd As New SqlCommand("SELECT * FROM <some desired table>", sqlconn)
                  rdr = sqlcmd.ExecuteReader
                  While (rdr.Read)
                      Call ReadSingleRow(SW, "~", rdr)
                  End While
                  rdr.Close()

              End Using
          End Using
      End Using

  End Sub

  Private Sub ReadSingleRow(sw As StreamWriter, delim As Char, datareader As SqlDataReader)
      Dim i As Integer

      For i = 0 To datareader.FieldCount - 1
          If (i > 0) Then sw.Write("{0}", delim)
          sw.Write("{0}", datareader(i))
      Next
  End Sub

thank for David.
This function are only backup one table.But in my database contain 30 tables, in every one table has department column.I want to backup database (all rows of all tables where department equal '10').
and write by c#.

again thanks you very much.

OK.

Here is what you need to do:
1) Add a parameter to the ExportTable subroutine passing the select statement you want to use.

For example "SELECT * FROM TABLE1 WHERE DEPARTMENT = '10'"

2) Add another parameter to the ExportTable function which is the filename you want the table exported to.

There are lots of Free Online VB to C# code converters out there. I have confidence that you can find one.

I used a this free Online tool: Code Converter C# to VB and VB to C# – Telerik[^]

public void ExportTable()
{
    SqlDataReader rdr = null/* TODO Change to default(_) if this is not a reference type */;

    using (StreamWriter SW = new StreamWriter(@"c:\temp\zMyTable.txt"))
    {
        using (SqlConnection sqlconn = new SqlConnection("<put your connection string here>"))
        {
            sqlconn.Open();

            using (SqlCommand sqlcmd = new SqlCommand("SELECT * FROM <some desired table>", sqlconn))
            {
                rdr = sqlcmd.ExecuteReader;
                while ((rdr.Read))
                    ReadSingleRow(SW, "~", rdr);
                rdr.Close();
            }
        }
    }
}









Convert Code

public void ExportTable()
{
    SqlDataReader rdr = null/* TODO Change to default(_) if this is not a reference type */;

    using (StreamWriter SW = new StreamWriter(@"c:\temp\zMyTable.txt"))
    {
        using (SqlConnection sqlconn = new SqlConnection("<put your connection string here>"))
        {
            sqlconn.Open();

            using (SqlCommand sqlcmd = new SqlCommand("SELECT * FROM <some desired table>", sqlconn))
            {
                rdr = sqlcmd.ExecuteReader;
                while ((rdr.Read))
                    ReadSingleRow(SW, "~", rdr);
                rdr.Close();
            }
        }
    }
}

Dear David.

Is your ExportTable() function only export one table in database?
In my DB have 30 tables, so how can I do embled the ExportTable() to my CreateScriptDataBase() function?

Thank you for your enthusiastic help!

anybody can help me improve the code belowing in order to export rows that department column is '10':

foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))
            {
                sb.Append(s);
                sb.Append(Environment.NewLine);
}

Hello.
I have a problem with sa login to ms sql server 2014 and 2008 on Windows 10.
I have new computer with Windows 10. I am login to windows by domain user. When I installed MSSQL Server 2008 i had error 0x84B10001. I created local user and installed sql with him. When i loggin to windows by domain user i'm not login to sql by sa. I have error nr 18456 with state nr 8 (The password is incorrect). But when I'm login to windows by local user i can logi to sql by sa with the same password.
What i'm doing to working sa login on the domain user?

Lots of possible causes for that error:
MSSQLSERVER_18456 - SQL Server | Microsoft Docs[^]

It sounds like your connection is using Windows authentication instead of SQL authentication. The full error message should give you more details.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Hey
What is the difference between SQL Server Management Studio VS SQL Server in Visual Studio 2019? Can I use it in Visual Studio 2019 for Large database? Thank you so much Smile | :)

SQL Server Management Studio (SSMS) - SQL Server | Microsoft Docs[^].
SQL Server 2019 | Microsoft[^].

Ebrahimaw wrote:
SQL Server in Visual Studio 2019

What does that mean? I don't have VS2019 but I doubt there is a sql server in it.

Social Media - A platform that makes it easier for the crazies to find each other.

Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.

When you fire up SSMS it mentions Visual Studio which has probably confused the OP.

Never underestimate the power of human stupidity -
RAH
I'm old. I know stuff - JSOP

Ebrahimaw wrote:
SQL Server Management Studio

(MS)SQLSMS* is an IDE that specifically targets SQL Server. Meaning it is an application that helps you with managing (editing) your databases. It is not a database-server in itself, it is just the interface you use to manage the SQL database-server. It has some nice advanced features that can help you optimize queries. Simply put, you want to install it and get to know it.

Ebrahimaw wrote:
SQL Server in Visual Studio 2019?

VS2019 is an IDE that targets programming languages, not databases. It still has an impressive UI to administer databases to help in development, but should not be your primary interface; MSSQLSMS is free, so no reason not to have it.

Ebrahimaw wrote:
Can I use it in Visual Studio 2019 for Large database

Yes.

Very large even. SQL Server is a database-server, which can be used without having Visual Studio. The free (Express) version limits you to 1Gb of memory usage and databases limited to 10Gb. You are allowed to have multiple databases that go up to 10Gb each though. You can have multiple users acces the database at the same time, without (much) problems. The Express version also supports Full-Text search, which means you can search the all text-columns for a specific text, with a lower impact than a normal query would have.

If you have a larger customer, you want the full verion of SQL Server; it performs better since it can utilize more memory and more CPU-cores. If you are having trouble on deciding when it is time for that, as a rule of thumb, you want it as soon as you are buying good hardware specifically for the database-server.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

Thank you so much Smile | :)

No problem Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]
"If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

My objective is to add and update data into xml element which store in table. i am weak in xquery to do it. so looking for help.

in my table each row has one field which store xml. suppose my xml data look like below one which store in my table

XML

<?xml version="1.0" encoding="utf-8"?>
<TickerBrokerStandardDateLineitem>
  <Ticker />
  <TickerID />
  <TickerBrokerStandardDateLineitemValues>
    <TickerBrokerStandardDateLineitemValue>
      <TabName>Consensus Model</TabName>
      <StandardDate>2010 FY</StandardDate>
      <XFundCode>TRIN</XFundCode>
      <BRTab></BRTab>
      <BRLineItem></BRLineItem>
      <StandardLineItem>Total Sales</StandardLineItem>
      <StandardValue></StandardValue>
      <ActualProvidedByCompany>NO</ActualProvidedByCompany>
    </TickerBrokerStandardDateLineitemValue>
    <TickerBrokerStandardDateLineitemValue>
      <TabName>Consensus Model</TabName>
      <StandardDate>2011 FY</StandardDate>
      <XFundCode>TRIN</XFundCode>
      <BRTab></BRTab>
      <BRLineItem></BRLineItem>
      <StandardLineItem>Total Sales</StandardLineItem>
      <StandardValue></StandardValue>
      <ActualProvidedByCompany>NO</ActualProvidedByCompany>
    </TickerBrokerStandardDateLineitemValue>
  </TickerBrokerStandardDateLineitemValues>
</TickerBrokerStandardDateLineitem>

now i have to update XFundCode in all records of xml data if my supplied TabName & StandardLineItem match those records.

also i have to add one element called ID=55 to each records if my supplied TabName & StandardLineItem match those records in xml data.

so i have two task that i have to add one element called ID = 55 to each records of xml data if my TabName & StandardLineItem found in those records of xml data.

also i have to update XFundCode element if my TabName & StandardLineItem found in those records of xml data.

Please give me xquery code which i can run in sql server to add & update element in xml data. thanks

Anyone can please help me to understand pivoting in SQL

Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^]
Questions About Pivoting Data in SQL Server You Were Too Shy to Ask - Simple Talk[^]
SQL SERVER - PIVOT and UNPIVOT Table Examples - SQL Authority with Pinal Dave[^]

Google (or your preferred search engine) will find you lots more.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

We have a couple of tables that we have to un-pivot - one has 245+ columns and (on average) about 510k rows - it needs to be un-pivotted down to 8 columns, and the columns we need to pivot are a mix of int, float, and varchar. This means we have to cast all of the pivoted columns to varchar, and then re-cast in the unpivot clause.

When we redesign the database (soon I hope), we won't have to un-pivot, but will instead have to pivot the columns into a schema the app can understand. Hopefully though, we won't have to do any un-pivoting/pivoting at all - it's a pretty expensive operation.

".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013

modified 27-Nov-19 11:20am.

Hello, all!

I'm experienced front-end developer. Few months ago I started play with back-end technologies - nodejs, express, mongo,mongoose. I can get everything quickly. Just need some help to initiate thinking over there.

Now I develop polls application and struggle with how to structure my mongoose schemas to implement my features. Also there is implemented simple authentication.
Don't worry everything will be explained step by step.

Features:

- User can create poll on "POST /polls". Poll contains question:string, and options:[string];
- User can vote for poll on "POST /polls/:pollID/vote" as select option index;
- User can get polls on "GET /polls?...". But you receive their vote result only if you are creator or voter. And you can see always the count of all voters;
- User can bookmark poll on "POST /polls/:pollID/bookmark;
- User can get all bookmarked polls on "GET /poll/:pollID/bookmarks sorted by bookmark date;

Front-end poll object looks like;
-Always show question, options, votesCount;
-Only show vote if user is creator or voted for the poll;
-Always show if user bookmarked the poll;

My current Poll schema:

{
    question: {
        type: mongoose.Schema.Types.String,
        required: true,
    },
    options: {
        type: [{
            type: mongoose.Schema.Types.String,
            required: true,
        }]
    },
    optionsVote: {
        type: mongoose.Schema.Types.Map,
        of: mongoose.Schema.Types.Number,
    },
    createdAt: {
        type: mongoose.Schema.Types.Date,
        default: Date.now,
    },
    votes: {
        type: [{
            user: {
                type: mongoose.Schema.Types.ObjectId,
                ref: 'User',
            },
            option: mongoose.Schema.Types.Number,
        }]
    },
    votesCount: {
        type: mongoose.Schema.Types.Number,
        default: 0,
    },
    creator: {
        type: mongoose.Schema.Types.ObjectId,
        required: true,
    },
    bookmarks: {
        type: [{
            user: {
                type: mongoose.Schema.Types.ObjectId,
                ref: 'User',
            },
            createdAt: mongoose.Schema.Types.Number,
        }]
    },
}

What I did then ...

// First added useless field in the schema I think. That helped my to limit the access(with query, not with pure js on the server) of $optionsVote field map for those that did not vote. Cannot I use just $votes field array?

// Poll Schema: 
{
   ...
    voters: [{
        type: mongoose.Schema.Types.ObjectId,
        ref: 'User',
    }],
   ...
}

// Route handler "GET /polls/?category=''&order='asc'..."
const polls = await Poll.aggregate([
            {
                $project: {
                    question: true,
                    options: true,
                    createdAt: true,
                    votesCount: true,
                    optionsVote: {
                        $cond: {
                            if: {$in: [mongoose.Types.ObjectId(req.user.id), "$voters"]},
                            then: "$optionsVote",
                            else: "$$REMOVE",
                        },
                    },
                },
            }
        ]).sort({[category]: order})

So my question ...
Can be divided into:
Is the current schema right for that features?
Should I create Bookmark and Vote collections?
What queries should I implement for bookmarks and votes to be with partially restricted access ?
Can you give me direction?

I will discuss, support and edit what is needed. Thumbs Up | :thumbsup:

... And I am extremely grateful for your attention! Thank you very much! Smile | :)

create ER diagram to represent the tables of the StayHome video rental shop (as described in the Access 2000 manual

What have you tried?
Where are you stuck?
What help do you need?

"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
AntiTwitter: @DalekDave is now a follower!

i want make for me ER diagram