Create table by user id

[mydisney]

I have an application (vb 2005) that reads 8 different tables in 8 different data base and builds a table for the selected records (using sp's). This table is used as basis for building a data grid and cosequently export to crystal report.

My question: How can I create a table for each user, so multiple users can run the same program at the same time. I tried using #temptable but that did not work. Can I in a sp create a table with a name that contains the user id passed to the sp as a parm?

 

[gmmastros]

>> I tried using #temptable but that did not work.

I first reaction is that a temp table should work in this situation. So... Can you please explain what exactly did not work for you? In what way does the temp table fail?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Crowley16]

you can use dynamic sql to autocreate tables...

--------------------
Procrastinate Now!

 

[SQLSister]

Do not use dynamic sql. The temp table should work. Like George I would like to know why you say it didn't. Dynamic SQl is a very bad way to go. It is very insecure and very difficult to completely test and in general not as efficient. Very poor practice to use dynamic sql if it can be avoided.

"NOTHING is more important in a database than integrity." ESquared

 

[mydisney]

Here is what I've tried:

I have an application that:

1. calls a sp and creates a temp table
2. reads a table using data reader
3. updates the temp table.

When I update the temp table I get an error:


Quote:
System.Data.SqlClient.SqlException was unhandled
Class=16
ErrorCode=-2146232060
LineNumber=9
Message="Invalid object name '#jobtemptable'."
Number=208
Procedure="sp_UpdateJobTempTable"
Server="VGIWPW03-SQL3\ASPHALT"
Source=".Net SqlClient Data Provider"

Here is the sp for creating temp table


Code:
CREATE PROCEDURE sp_CreateJobTempTable
as

if exists (
select *
from
tempdb.dbo.sysobjects o
where
o.xtype in ('U') and
o.id = object_id( N'tempdb..'+'#jobtemptable' )
)

drop table #jobtemptable



create table #jobtemptable
(
job_date datetime,
job_number char(15),
job_phase char(15),
asphalt_delivered int,
job_received int
)
GOHere is my vb code to update the temp table:



Code:
ServerCommand.Parameters.Add(New SqlClient.SqlParameter("@parm1", SqlDbType.DateTime)).Value = MyAdsDataReader.GetDateTime(0) 'date
ServerCommand.Parameters.Add(New SqlClient.SqlParameter("@parm2", SqlDbType.Char)).Value = MyAdsDataReader.GetString(1) 'job
ServerCommand.Parameters.Add(New SqlClient.SqlParameter("@parm3", SqlDbType.Char)).Value = MyAdsDataReader.GetString(2) 'job phase
ServerCommand.Parameters.Add(New SqlClient.SqlParameter("@parm4", SqlDbType.Int)).Value = MyAdsDataReader.GetInt32(3) 'tonnage
ServerCommand.CommandText = "sp_UpdateJobTempTable"
ServerCommand.ExecuteNonQuery()Here is the sp for updating temp table:


Code:
CREATE PROCEDURE sp_UpdateJobTempTable
(
@parm1 datetime,
@parm2 char(15),
@parm3 char(15),
@parm4 int
)
AS
INSERT INTO #jobtemptable
(
job_date,
job_number,
job_phase,
asphalt_received
)
VALUES
(
@parm1,
@parm2,
@parm3,
@parm4
)
GO

 

[gmmastros]

The problem is... the temp table is gone by the time you get to the UPDATE procedure because it has gone out of scope. Instead, I would suggest that you create the temp table and use it all within the same procedure.

[COLOR=blue]CREATE[/color] [COLOR=blue]PROCEDURE[/color] sp_UpdateJobTempTable
    (
    @parm1      [COLOR=#FF00FF]datetime[/color],
    @parm2      [COLOR=blue]char[/color](15),
    @parm3      [COLOR=blue]char[/color](15),
    @parm4      [COLOR=blue]int[/color]
        )
[COLOR=blue]AS[/color]
	
	[COLOR=blue]SET[/color] [COLOR=#FF00FF]NOCOUNT[/color] [COLOR=blue]ON[/color]

    [COLOR=blue]create[/color] [COLOR=blue]table[/color] #jobtemptable
    (
    job_date [COLOR=#FF00FF]datetime[/color],
    job_number [COLOR=blue]char[/color](15),
    job_phase [COLOR=blue]char[/color](15),
    asphalt_delivered [COLOR=blue]int[/color],
    job_received [COLOR=blue]int[/color]
    )

    [COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] #jobtemptable
    (
        job_date,
        job_number,
        job_phase,
        asphalt_received
                )
    [COLOR=blue]VALUES[/color]
    (
        @parm1,
        @parm2,
        @parm3,
        @parm4
                   )

    [COLOR=green]-- Use the temp table for something
[/color]
    [COLOR=blue]Drop[/color] [COLOR=blue]Table[/color] #jobtemptable

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mydisney]

I'm afraid it will go out of scope because of what I need to do:

I have 8 data bases and one table in each of these that I need to read:

So, I open connection to 1st db, loops through a data reader and calls the sp, then I close connection. This is repeated 8 times. I thought this would take me out of scope after I have read the 1st db or?

 

[gmmastros]

Let me see if I understand.

You want to.....

Create a temp table
Put data in to the temp table from db1
Put data in to the temp table from db2
Put data in to the temp table from db3
Put data in to the temp table from db4
Put data in to the temp table from db5
Put data in to the temp table from db6
Put data in to the temp table from db7
Put data in to the temp table from db8
Do something with this data

Can you explain what you want to do with the data? Return it to the client?

I think all of this can be done within a single stored procedure, and probably without the use of a temp table. The basic structure would look something like...

Select job_date, job_number, job_phase, asphalt_delivered,    job_received
From   [!]db1[/!].dbo.[!]TableName[/!]

Union All

Select job_date, job_number, job_phase, asphalt_delivered,    job_received
From   [!]db2[/!].dbo.[!]TableName[/!]

Union All

Select job_date, job_number, job_phase, asphalt_delivered,    job_received
From   [!]db3[/!].dbo.[!]TableName[/!]

-- Etc.....

Of course, this is assuming that all of the databases are attached to the same server. If the databases are on different servers, then you can still accomplish this by creating linked servers and then prefixing the the tables with:

From [!]Server1[/!].[!]dbX[/!].dbo.[!]TableName[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mydisney]

First, sorry for my lack of knowledge and thank you for your help.

Business scope:
We have 8 asphalt plants that delivers asphalt to road paving jobs. The delivieries are recorded a what we call sales ticket table for each plant. These plants run on Advantage data base. Eigth different servers out in the field.

On the job site we have field reporting where receipts are recorded and updated in a sequel server data base (wireless from the field).

Now we need to compare what is delivered and what has been received at the job sites (by job number).

The starting point is to consolidate (by date and job number) all tonnage delivered from the plants. Then read the receipt data base table and match them to what was delivered.

Table structure:
Date Job Number Delivered Received

I therefore need to read each plant's sales table (which I'm doing using query select) and update my temp table. I do this eight times. I connect, query the data, call the sp (data reader) and disconnect.

When this is done I need to grab the field reporting table (for receipts and match records to deliveries). If record already exists, then update with receipt, if does not exists, insert.

At the end I need to show the temp table in a data grid with option to export to crystal report. Note: several users could run this application at the same time.

 

[mydisney]

Maybe I could use table variables??

CREATE PROCEDURE sp_UpdateJobTable
(
@parm1 datetime,
@parm2 char(15),
@parm3 char(15),
@parm4 int
)
AS
DECLARE @jobtable TABLE (
job_date datetime,
job_number char(15),
job_phase char(15),
asphalt_delivered int,
job_received int
)

INSERT INTO @jobtable
(
job_date,
job_number,
job_phase,
asphalt_delivered
)
VALUES
(
@parm1,
@parm2,
@parm3,
@parm4
)
GO

 

[gmmastros]

OK. Let's me get this straight.

You have 8 remote locations, each running SQL Server. You also have a central location (Corporate Headquarters), also running SQL Server.

You need to gather the data from each of the 8 separate locations, compare it with data at corporate (where you will insert or update data), and then report on the data. Do I have this correct?

Before I give you advice on this, can you tell me how you are connecting to the remote SQL Servers? Do you have linked servers set up already? If so, that will make this process a lot easier and tons faster.

If you do not have linked servers set up, can you create them? I mean... do you have permissions to do that. Again, linked servers will make this process lots faster and more secure. It also simplifies the code and therefore is likely to be more reliable.

The reason I'm asking is because you can probably do ALL of this within a single stored procedure (assuming linked servers). It will make the front end code a lot simpler too.





-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

You'll have the same problem with table variables.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Let me also say....

I understand that my advice thus far is NOT directly related to the question you asked. I understand that. In my opinion, there's a better way to accomplish your overall objective. If you prefer to continue with the 'table per user' approach, I understand and can help you with that also. Just to be clear, I am willing to help with either approach. I just want to give you an alternative method that I think will be better.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mydisney]

George,

Thank you for the offer. Very nice of you.

Please note that the plants are NOT running sequel server, they are running Advantage data base server (

http://www.extendedsystems.com/web/content.aspx?key=79D35E8C44D23A8EA9BB2E0A5A8536A2).

Have their own drivers etc

I am to the point wher I am filling the "temp" table with delivery tonnage from each plant. It works except it goes out of scope. I need to be able to update/insert this table when I extract the data for tonnage receipts and cosequently show a grid.

From reading an artice it says:
Table Variables. If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory (

http://www.sqlteam.com/article/temporary-tables)

Here is the code snippet for filling the table:

Public Sub ReadAsphaltTables()
'Loop through tables for each plant and update temp table
For i = 0 To AdsConnection.GetUpperBound(0)
If i < 8 Then
OpenAstecConnection()
If ConnectionError = False Then
SelectString = "select ticket_date, job, job_phase, max(qty_shipped_today) as tons FROM salestkt where ticket_date >= " & "'" & DateTimePicker1.Text & "'" & " and ticket_date <= " & "'" & DateTimePicker2.Text & "'" & " and substring(job, 1, 3) = " & "'" & JobPrefix(i) & "'" & " group by ticket_date, job, job_phase"
Dim MyAdsSelect As String = SelectString
Dim MyAdsCommand As New AdsCommand(MyAdsSelect, AstecConnection)
Dim MyAdsDataReader = MyAdsCommand.ExecuteReader()
If MyAdsDataReader.HasRows Then
Do While MyAdsDataReader.Read()
ServerCommand.Parameters.Add(New SqlClient.SqlParameter("@parm1", SqlDbType.DateTime)).Value = MyAdsDataReader.GetDateTime(0) 'date
ServerCommand.Parameters.Add(New SqlClient.SqlParameter("@parm2", SqlDbType.Char)).Value = MyAdsDataReader.GetString(1) 'job
ServerCommand.Parameters.Add(New SqlClient.SqlParameter("@parm3", SqlDbType.Char)).Value = MyAdsDataReader.GetString(2) 'job phase
ServerCommand.Parameters.Add(New SqlClient.SqlParameter("@parm4", SqlDbType.Int)).Value = MyAdsDataReader.GetInt32(3) 'tonnage
ServerCommand.CommandText = "sp_UpdateJobTable"
ServerCommand.ExecuteNonQuery()
ServerCommand.Parameters.Clear()
Loop
End If
End If
End If
AstecConnection.Close()
Next
End Sub


Code for "temp table"

CREATE PROCEDURE sp_UpdateJobTable
(
@parm1 datetime,
@parm2 char(15),
@parm3 char(15),
@parm4 int
)
AS
DECLARE @jobtable TABLE (
job_date datetime,
job_number char(15),
job_phase char(15),
asphalt_delivered int,
job_received int
)

INSERT INTO @jobtable
(
job_date,
job_number,
job_phase,
asphalt_delivered
)
VALUES
(
@parm1,
@parm2,
@parm3,
@parm4
)
GO

 

[gmmastros]

You'll have the same problem with a table variable. Let me explain....

Since you are inserting each record one at a time, each call to the procedure will create a table variable. You then insert data in to that table variable. Then, the procedure ends. At the end of the procedure, the table variable is destroyed... gone... poof.

There are, of course, several different ways to handle this.

1. You could create a 'real' table that you put the data in to. This is probably going to be the slowest method and have the most code (and therefore more prone to bugs).

2. You could get all the data from each server, save it to an ascii file and bulk load it. This method is likely to be a lot faster for you and will probably be more reliable.

3. You could get all the data from each server, and store it (in memory) to an xml document. Then, you call a single stored procedure with the XML document as a parameter.

4. I can't say for sure, but I suspect that you could set up linked servers between your corporate server and the remote db's. According to the link you provided, your remotes are running sybase. I know you can set up a linked server between SQL Server and sybase, but there are other factors that may prevent you from doing this (security, firewall, etc...).

Of all the methods I mention above, the simplest is probably #2. Here's what I suggest....

Get the data from each remote location and save it to an ASCII file. Then, write a procedure that imports data from the ASCII file in to a temp table. From there, you should be able to compare/save/update your table at the corporate site.

In your procedure, you'll want to have code similar to this...

Create Table #jobtable (
job_date datetime,
job_number char(15),
job_phase char(15),
asphalt_delivered int,
job_received int
)

bulk insert #jobtable
From '[!]YourDataFile.txt[/!]'

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mydisney]

George:

If I create a real table how can I preserve integrity if several users are running the program at the same time?

If I create an ASCI file how will I be able to extract each field element when I bulk load it into a temp table?

It seems to me that creating an xml document is the slickest way to go. I guess when I extract corporate data I will be able to update a xml documet that exists as well?

If I have populated a temp table will I be able to bind the table to a data grid (since I am exiting the sp)?


Thank you.

 

[gmmastros]

>> If I create a real table how can I preserve integrity if several users are running the program at the same time?

You would have to create a table with a unique name each time. Messy. Very messy.

If I create an ASCI file how will I be able to extract each field element when I bulk load it into a temp table?

Use delimiters. Typically, tab character between data elements and Carriage Return/Line Field between rows.

It seems to me that creating an xml document is the slickest way to go. I guess when I extract corporate data I will be able to update a xml documet that exists as well?

I don't know about slick, but performance would be pretty good. Understanding that my idea is to use an XML document as an intermediate step between collecting data from the 8 remote servers and inserting in the corporate server.

If I have populated a temp table will I be able to bind the table to a data grid (since I am exiting the sp)?

I don't think you can bind a table to a temp table, but there are other ways to present the data to the user. Better of asking this in the vb.net forum.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom