Showing posts with label below. Show all posts
Showing posts with label below. Show all posts

Wednesday, March 21, 2012

Problems with PIVOT tables

I'm a having a problem with PIVOT tables.

I have a table that stores the results from multiple surveys, (see format below)...

 
SurveyID RespondantID QuestionID Answer========================================================= 1 1 1 X 1 1 2 Y 1 1 3 Z 1 2 1 R 1 2 2 S 1 2 3 T 14 10 1 A 14 10 2 B 14 11 1 C 14 11 2 D

I want to use a pivot table to convert the data it to a format like the following (for surveyID = 1)...

 
RespondantID Q1 Q2 Q3 =============================== 1 A B C 1 R S T

The 2 differences I need from a normal pivot query are...

(a) I don't want to summarise my results at all(b) I have multiple surveys in the same table so need to use an extra clause to pick out info for the survey I am interested in.

So far I have come up with...

SELECT RespondantID, [1] As Q1, [2] As Q2, [3] As Q3, [4] As Q4, [5] As Q5, [6] As Q6, [7] As Q7, [8] As Q8, [9] As Q9, [10]
As Q10 FROM (SELECT RespondantlD, QuestionlD, Answer FROM "3_Temp" WHERE SurveylD=3) AS preData PIVOT (
COUNT(Answer) FOR QuestionlD IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) ) AS data ORDER BV RespondantlD

But it doesn't work and I can't figure out why (error message when executing in VBA = "RunSQL function requires a SQL argument"

Can anyone help?

Thanks for posting sample data.

The PIVOT function is for SQL Server 2005, but you mention that you are excuting in VBA?

By the way, is this correct?

RespondantID Q1 Q2 Q3
===============================
1 A B C
1 2 R S T

If you need help for your question in either SQL Server 2005 or 2000, please post back.

|||

Yes that should be respondantID 2.

I am building an access data project (.adp) which links to tables in SQL Server 2005 so it is a SQL issue.

Thanks,

Martin

|||

Answer to your Question q: The aggregate function is required here, but you can use MAX instead of COUNT.

Answer to your Question b: No problem. As as you did in your query.

I don't know whether you can run this with your setup. Let us know if you find out.

I can run this in SQL Server 2005 Management Studio:

SELECT RespondantID, [1]As Q1, [2]As Q2, [3]As Q3, [4]As Q4, [5]As Q5, [6]As Q6, [7]As Q7, [8]As Q8, [9]As Q9, [10]

As Q10FROM(SELECT RespondantID, QuestionID, AnswerFROM [3_Temp]

WHERE SurveyID=1)AS preDataPIVOT

(MAX(Answer)FOR QuestionIDIN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]))

AS data

ORDERBY RespondantID

|||

Thanks for correcting my code Limno!

The only problem now is that I need to export the data to excel, but access will only do this from a table. I tried saving the above code as a view and then doing

SELECT *INTO tablenameFROM PivotedView
but I get an error saying "could not find PivotedView". Do you know how I can modify the pivot query directly, i.e.
SELECT RespondantID, [1]As Q1 ...INTO PivotedTable
Thanks!|||

This was resolved in post:http://forums.asp.net/t/1185101.aspx

Monday, March 12, 2012

problems with insert query and primary key

I am trying to run an insert query off of a sql datasource and I am erroring out. My code and stored procedure as of now are listed below. You will notice the section of the stored procedure that is pulling the value for facility_ID (primary key). I have also tried to pull these and pass the parameter from a label, but that does not work, giving an error that the stored procedure expects the parameter @.Facility_ID which was not supplied. One other odd thing is that stepping through the code, I watched the parameter count total 21, but when running the insert command, the insert parameter count shows 20. With the code below (my current project), I get an error that null values can not be entered for facility_ID. Please help.

CODE:

Dim myConnection As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("FacilitiesBuild").ConnectionString)

'open myconnection

myConnection.Open()

Dim myCommand As New Data.SqlClient.SqlCommand(SqlDataSourceFac.InsertCommand, myConnection)

myCommand.CommandType = Data.CommandType.StoredProcedure

myCommand.Parameters.Add("@.Name", Data.SqlDbType.VarChar, 50).Value = CType(Me.DetailsView1.FindControl("Textbox5"), TextBox).Text
myCommand.Parameters.Add("@.Address1", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox3"), TextBox).Text
myCommand.Parameters.Add("@.Address2", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox4"), TextBox).Text
myCommand.Parameters.Add("@.State", Data.SqlDbType.VarChar, 2).Value = CType(Me.DetailsView1.FindControl("Textbox17"), TextBox).Text
myCommand.Parameters.Add("@.Zip", Data.SqlDbType.VarChar, 10).Value = CType(Me.DetailsView1.FindControl("Textbox6"), TextBox).Text
myCommand.Parameters.Add("@.Phone", Data.SqlDbType.VarChar, 14).Value = CType(Me.DetailsView1.FindControl("Textbox7"), TextBox).Text
myCommand.Parameters.Add("@.Admin_Name", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("Textbox8"), TextBox).Text
myCommand.Parameters.Add("@.Comments", Data.SqlDbType.VarChar, 250).Value = CType(Me.DetailsView1.FindControl("Textbox10"), TextBox).Text
myCommand.Parameters.Add("@.Owner", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("TypeOwnerInsert"), Label).Text
myCommand.Parameters.Add("@.Beds", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("BedsInsert"), Label).Text
myCommand.Parameters.Add("@.Population", Data.SqlDbType.NText).Value = CType(Me.DetailsView1.FindControl("PopulationInsert"), Label).Text
myCommand.Parameters.Add("@.Type_Facility", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("TypeFacilityInsert"), Label).Text
myCommand.Parameters.Add("@.Type_Other", Data.SqlDbType.VarChar, 40).Value = CType(Me.DetailsView1.FindControl("TypeOtherInsert"), Label).Text
myCommand.Parameters.Add("@.Profit", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ProfitInsert"), Label).Text
myCommand.Parameters.Add("@.Religious", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ReligiousInsert"), Label).Text
myCommand.Parameters.Add("@.Licensed", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("LicensedInsert"), Label).Text
myCommand.Parameters.Add("@.Active", Data.SqlDbType.Char, 1).Value = CType(Me.DetailsView1.FindControl("ActiveInsert"), Label).Text
myCommand.Parameters.Add("@.City_ID", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("InsertCityLabel"), Label).Text
myCommand.Parameters.Add("@.Agency_ID", Data.SqlDbType.Int).Value = CType(Me.DetailsView1.FindControl("InsertAgencyLabel"), Label).Text
myCommand.Parameters.Add("@.County", Data.SqlDbType.NVarChar, 3).Value = CType(Me.DetailsView1.FindControl("InsertCountyLabel"), Label).Text
myCommand.Parameters.Add("@.Facility_ID", Data.SqlDbType.VarChar, 6).Value = CType(Me.DetailsView1.FindControl("InsertFacilityLabel"), Label).Text

If CType(Me.DetailsView1.FindControl("DropDownList1"), DropDownList).SelectedItem.Text = "Please Select One" Then
MsgBox("You must select an agency")
Else : SqlDataSourceFac.Insert()
End If

STORED PROCEDURE:

ALTER PROCEDURE [dbo].[SP_OMBFacilityAddDOTNET]


@.Name varchar(50),
@.Address1 varchar(40),
@.Address2 varchar(40),
@.State varchar(2),
@.Zip varchar(10),
@.Phone varchar(14),
@.Admin_Name varchar(40),
@.Comments varchar(250),
@.Owner char(1),
@.Beds int,
@.Population numeric(10,0),
@.Type_Facility char(1),
@.Type_Other varchar(40),
@.Profit char(1),
@.Religious char(1),
@.Licensed char(1),
@.Active char(1),
@.City_ID int,
@.Agency_ID int,
@.County nvarchar(3)

AS
BEGIN
DECLARE @.Facility_ID varchar(6)
DECLARE @.nextID varchar(3)

/* get next facilityID */
SELECT @.nextID = MAX(RIGHT(Facility_ID, LEN(Facility_ID)-(CHARINDEX('-', Facility_ID)))) + 1
From OMBFacility
Where Agency_ID = @.Agency_ID

SELECT @.Facility_ID = CAST(@.Agency_ID AS varchar(2)) + '-' + RIGHT('000' + RTRIM(@.nextID), 3)
INSERT INTO [AIMS].[dbo].[OMBFacility]
([Name],
[Address1],
[Address2],
[State],
[Zip],
[Phone],
[Admin_Name],
[Comments],
[Owner],
[Beds],
[Population],
[Type_Facility],
[Type_Other],
[Profit],
[Religious],
[Licensed],
[Active],
[City_ID],
[Agency_ID],
[County],
[Facility_ID])

VALUES
(@.Name
,@.Address1
,@.Address2
,@.State
,@.Zip
,@.Phone
,@.Admin_Name
,@.Comments
,@.Owner
,@.Beds
,@.Population
,@.Type_Facility
,@.Type_Other
,@.Profit
,@.Religious
,@.Licensed
,@.Active
,@.City_ID
,@.Agency_ID
,@.County
,@.Facility_ID)

END

Here you are:Getting the identity of the most recently added record

|||

I don't mean to be a moron, but will that work in a stored procedure as well? Where would I place "SET @.NewId = Scope_Identity()" in the stored procedure? I tried it at the first using both NewId thinking I could declare it later and Facility_ID which is the name of my primary key, but either way it tells me I have to declare the variable. This error comes up whether or not I declare the variable with the main set of variables in the stored procedure.

|||

lukeduke:

I don't mean to be a moron

Sorry - I'm the moron for not reading your post correctly. I saw the subject and had an almost Pavlovian reaction. The article specifically talks about getting the value of autoincrement fields. Your's isn't doing that. I need to study your post a bit more carefully.

|||

That's cool man, wipe the slobber off of your keyboard. Have you got any idea on what's causing the issue, I am pulling hair out trying to find it.

|||

If i am reading this all correctly here might be a solution:

If you want the ID of the newly inserted record(facility_ID), dont pass that value to the stored procedure, but declare that variable in the procedure like you have

declare @.faility_ID int; set @.facility_ID = 0

dont have it in the insert list, so the list should end with County and @.County. After the insert code run this:

INSERT INTO [AIMS].[dbo].[OMBFacility] ([Name], [Address1], [Address2], [State], [Zip], [Phone], [Admin_Name], [Comments], [Owner], [Beds], [Population], [Type_Facility], [Type_Other], [Profit], [Religious], [Licensed], [Active], [City_ID], [Agency_ID], [County])VALUES (@.Name ,@.Address1 ,@.Address2 ,@.State ,@.Zip ,@.Phone ,@.Admin_Name ,@.Comments ,@.Owner ,@.Beds ,@.Population ,@.Type_Facility ,@.Type_Other ,@.Profit ,@.Religious ,@.Licensed ,@.Active ,@.City_ID ,@.Agency_ID ,@.County)
if(@.@.error = 0)begin set @.facility_ID = scope_identity()end

and have a

select @.facility_ID facilityID

at the end to return the newly inserted ID, this will return a value > 0 if success and 0 if failure.

|||

kyle.spitzer:

If i am reading this all correctly here might be a solution:

I think you fell into the same trap as I did. Scope_Identity will only return an Identity column. Lukeduke's constructing his own varchar PK.

@.LukeDuke:

Could you post some more code so I have half a chance of knocking up a sample page to test? The aspx perhaps? Or just the relevant parts?

|||

Let me try a couple of things that were suggested first. If they don't work, I'll try and build a mockup to post the code for. The app I'm building now is a beast with seven datasources pulling from different tables, a gridview and two detailsview that are both using about 15 template fields each. I'll post the mockup this afternoon or tomorrow morning if my other attempts fail. Thanks for your input so far.

|||

One thing that Kyle did point out though - you have @.FacilityID in the parameter list, but whatever value you have set that doesn't appear to be used anywhere in the procedure. You have declared @.FacilityID as a local variable in the proc and are setting its value purely internally. I can't see why you have added it to the datasource control's parameter collection.

|||

That is where everything gets weird. Earlier in the code, I call a stored procedure that fetches the next facility id and assigns it to the text property of a databound label (the one that supplies the parameter). I run the program in VS2005 debug mode using autoS and set a breakpoint when the parameters are being passed. I step through the code and when the paramter collection is completed, autoS shows that the parameter count is 21.

The next step in the code is to make sure the user selected an agency and to run the insert command of the data source. When stepping to that point, autoS shows that the insertparameter count is 20. Each time facility_ID is dropped. I have moved it up in the list of parameters being passed thinking maybe the bottom parameter is being ignored, but it drops the value for facility_ID each time. I have tried to remove the databinding from the label and I get the same error. Each time I get, the stored procedure SP_OMBFacilityAddDOTNET expects the parameter @.Facility_ID which was not supplied.

Does that have anything to do with the Facility_ID being a primary key (and foreign key to other tables)?

There are two options I am going to try now. First, I'm going to make the facility_ID generate in an event that would fire before the button click event that is populating the parameter collection.

The other option I am going to attempt is to call the stored procedure that returns the new facility_ID inside of my the current stored procedure. The tricky part of that is the Facility_ID is in the format of Agency_ID - Facility_ID or 2-125 for example. In my label, I have to combine the two fields to set the text property.

|||

I fixed the problem. I had to get another eye on the code, but the parameter collection was not passing to the sqldatasource. The way I worked it out was to go into the properties of the sqldatasource and declare the parameters on the insert query. I then used session variables to pass the parameters and everything is working fine.

Thanks for all of your help!

|||

Yes, your original code seemed a little odd.

You built up a sqlcommand object using the stored procedure name stored in your SqlDatasource, manually added a bunch of parameters and assigned the values to them, and then you executed the insert of the SqlDatasource (instead of the SqlCommand).

The SqlCommand, and the parameters you built aren't being used at all.

I'm glad you got it finally working, however, you may want to take a look at implementing your code inside the SqlDatasource_Inserting event instead. Inside there, you can assign the values (or modify the default ones). That way you aren't stuffing arbitrary values into the session object that really don't belong in session. Although, I'm a little confused as to why you aren't using two-way databinding since it appears all the data is coming from a detailsview object.

Problems with GROUP BY

I have the below SELECT statement and I am having a problem:
SELECT status, SUM(NoItems) * Price AS NewRevenue
FROM orders
GROUP BY status, Price
The problem is because I need to place the Price field in the GROUP BY I
receive multiple rows. I need the result to be on one row. Any ideas on what
I am doing wrong?
Thank you
Peace in Christ
Marco Napoli
http://www.ourlovingmother.orgIs this what you're looking for?
SELECT status, SUM(NoItems * Price) AS NewRevenue
FROM orders
GROUP BY status
Hope this helps.
Dan Guzman
SQL Server MVP
"Marco Napoli" <marco@.avantitecnospam.com> wrote in message
news:uoBFPu7nFHA.1412@.TK2MSFTNGP09.phx.gbl...
>I have the below SELECT statement and I am having a problem:
> SELECT status, SUM(NoItems) * Price AS NewRevenue
> FROM orders
> GROUP BY status, Price
> The problem is because I need to place the Price field in the GROUP BY I
> receive multiple rows. I need the result to be on one row. Any ideas on
> what I am doing wrong?
> Thank you
> Peace in Christ
> Marco Napoli
> http://www.ourlovingmother.org
>

Friday, March 9, 2012

Problems with Dynamic Query from Newbie

Thanks for the help thus far. I'm still having problems
Below are the error message.
I can run the print statement noted below and it works.
I can also just do the select statement but placing the insert into breaks
the procedure.
Questions.
1. What am I doing wrong.
2. I need to output the @.TempVS variable to use. How is that done?
3. Can I do a Select * from @.VStemp after the table has been inserted?
4/ Any other way to optimize this code?
The query below works when I substitute values
(Insert Into @.Level3
Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.level and (Result <> 'DNR' ) and Result
<>''
and (DateDrawn > (GetDate() - @.Level2)
)
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Insert'.
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.Level3'.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near ')'.
/****** Object: Table [dbo].[TempVsTest] Script Date: 5/7/2006 3:02:18
PM ******/
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TempVSTEst]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [TempVSTest] (
[patNo] [int] NULL ,
[DateDrawn] [datetime] NULL ,
[Sort] [int] NULL ,
[TestnameDisplay] [varchar] (50) NULL ,
[Result] [varchar] (100) NULL ,
[Units] [varchar] (50) NULL ,
[abn] [varchar] (50) NULL ,
[Range] [varchar] (50) NULL
) ON [PRIMARY]
END
Insert Into TempVSTest
(patNo, DateDrawn, Sort, TestnameDisplay, Result, Units,abn, Range)
values
(240, '1/1/2005', 1,'Cholesterol', '300', 'mg/dl','H','<200')
GO
/* Test script */
DECLARE
@.TempVS varchar(20),
@.table Varchar(20)
-- @.PatNo int,
-- @.Type int,
-- @.Range int
-- @.sqlStmt nvarchar(500)
Set @.table = 'TempVSTest'
--Set @.Type = 0
--Set @.Range = 36000
--Make unique Global Temp Table Name
set @.TempVS = '##TempVS' + cast(datepart(minute, getdate()) as
varchar(3)) + cast(datepart(millisecond, getdate()) as varchar(3))
--print @.TempVS
EXEC ('SELECT * INTO ' + @.TempVS + ' FROM ' + @.table + ' WHERE 1=2')
--EXEC ('Select * from '+ @.TempVS)
--IF @.Type = 0 -- All
--BEGIN
Declare @.sqlStmt nvarchar(500)
Declare @.Param1 NVARCHAR(500)
Declare @.Param2 NVARCHAR(500)
Declare @.PatNo int
Declare @.Range int
set @.PatNO = 1
Set @.Range = 120
SET @.sqlStmt = N'(Insert Into @.Level3
Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
Result, Units, abn, Range
FROM PatLabVal2 where PatNo = @.level and (Result <> ''DNR'' ) and Result
<>''''
and (DateDrawn > (GetDate() - @.Level2))
)'
print @.sqlStmt
SET @.Param1 = N'@.level int, @.level2 int, @.Level3 varchar(20)'
EXECUTE sp_Executesql @.sqlStmt,
@.Param1,
@.level = @.PatNO ,
@.level2 = @.Range,
@.level3 = @.TempVSHi
I am not sure what the braces are at the start and end of the statement! You
can not use a variable as the table name in an insert statement, you will
need to build up the string in the same way as you use @.TempVS in EXEC
('SELECT * INTO ' + @.TempVS + ' FROM ' + @.table + ' WHERE 1=2')
It is not clear why you are going to the trouble of using a global temporary
table, your code should work with a temporary table, and therefore remove th
e
need to substituting the table name.
John
"Stephen K. Miyasato" wrote:

> Thanks for the help thus far. I'm still having problems
> Below are the error message.
> I can run the print statement noted below and it works.
> I can also just do the select statement but placing the insert into breaks
> the procedure.
> Questions.
> 1. What am I doing wrong.
> 2. I need to output the @.TempVS variable to use. How is that done?
> 3. Can I do a Select * from @.VStemp after the table has been inserted?
> 4/ Any other way to optimize this code?
> The query below works when I substitute values
> (Insert Into @.Level3
> Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.level and (Result <> 'DNR' ) and Result
> <>''
> and (DateDrawn > (GetDate() - @.Level2)
> )
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Insert'.
> Server: Msg 137, Level 15, State 1, Line 2
> Must declare the variable '@.Level3'.
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near ')'.
>
> /****** Object: Table [dbo].[TempVsTest] Script Date: 5/7/2006 3:02:18
> PM ******/
> if not exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[TempVSTEst]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> BEGIN
> CREATE TABLE [TempVSTest] (
> [patNo] [int] NULL ,
> [DateDrawn] [datetime] NULL ,
> [Sort] [int] NULL ,
> [TestnameDisplay] [varchar] (50) NULL ,
> [Result] [varchar] (100) NULL ,
> [Units] [varchar] (50) NULL ,
> [abn] [varchar] (50) NULL ,
> [Range] [varchar] (50) NULL
> ) ON [PRIMARY]
> END
> Insert Into TempVSTest
> (patNo, DateDrawn, Sort, TestnameDisplay, Result, Units,abn, Range)
> values
> (240, '1/1/2005', 1,'Cholesterol', '300', 'mg/dl','H','<200')
> GO
> /* Test script */
> DECLARE
> @.TempVS varchar(20),
> @.table Varchar(20)
> -- @.PatNo int,
> -- @.Type int,
> -- @.Range int
> -- @.sqlStmt nvarchar(500)
> Set @.table = 'TempVSTest'
> --Set @.Type = 0
> --Set @.Range = 36000
> --Make unique Global Temp Table Name
> set @.TempVS = '##TempVS' + cast(datepart(minute, getdate()) as
> varchar(3)) + cast(datepart(millisecond, getdate()) as varchar(3))
> --print @.TempVS
> EXEC ('SELECT * INTO ' + @.TempVS + ' FROM ' + @.table + ' WHERE 1=2')
> --EXEC ('Select * from '+ @.TempVS)
> --IF @.Type = 0 -- All
> --BEGIN
>
> Declare @.sqlStmt nvarchar(500)
> Declare @.Param1 NVARCHAR(500)
> Declare @.Param2 NVARCHAR(500)
> Declare @.PatNo int
> Declare @.Range int
> set @.PatNO = 1
> Set @.Range = 120
> SET @.sqlStmt = N'(Insert Into @.Level3
> Select Distinct patNo, DateDrawn, Sort,TestnameDisplay,
> Result, Units, abn, Range
> FROM PatLabVal2 where PatNo = @.level and (Result <> ''DNR'' ) and Result
> <>''''
> and (DateDrawn > (GetDate() - @.Level2))
> )'
> print @.sqlStmt
> SET @.Param1 = N'@.level int, @.level2 int, @.Level3 varchar(20)'
> EXECUTE sp_Executesql @.sqlStmt,
> @.Param1,
> @.level = @.PatNO ,
> @.level2 = @.Range,
> @.level3 = @.TempVS
>
>|||> It is not clear why you are going to the trouble of using a global
> temporary
> table, your code should work with a temporary table, and therefore remove
> the
> need to substituting the table name.
I need the table to build a array similar to crosstabs. I then had problems
with multi-user use and other persons data coming into the tables. I thought
that using global temp tables would be the answer to this. Any comments?
Thanks
Stephen K. Miyasato
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:881223D1-5B5E-45BB-B154-940BF5A4DE2E@.microsoft.com...
> Hi
> I am not sure what the braces are at the start and end of the statement!
> You
> can not use a variable as the table name in an insert statement, you will
> need to build up the string in the same way as you use @.TempVS in EXEC
> ('SELECT * INTO ' + @.TempVS + ' FROM ' + @.table + ' WHERE 1=2')
> It is not clear why you are going to the trouble of using a global
> temporary
> table, your code should work with a temporary table, and therefore remove
> the
> need to substituting the table name.
>
> John
>
> "Stephen K. Miyasato" wrote:
>|||Stephen K. Miyasato (miyasat@.flex.com) writes:
> I need the table to build a array similar to crosstabs. I then had
> problems with multi-user use and other persons data coming into the
> tables. I thought that using global temp tables would be the answer to
> this. Any comments?
Use a regular temp table instead. A plain #temp table is visible only
to the process that created it.
Global temp tables are very messy to work with. And I have a strong
feeling that it was a design mistake that brought them into the product.
I've yet found any useful application for them.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi
For static crosstabs you should not need to use a temporary table
http://msdn.microsoft.com/library/d...r />
_04j7.asp
or use the new PIVOT function in SQL 2005, for dynamic crosstabs you may wan
t
to look at Itziks articles on crosstabs in SQL Server magazine
http://www.sqlmag.com/Articles/Arti...5608/15608.html
John
"Stephen K. Miyasato" wrote:

> I need the table to build a array similar to crosstabs. I then had problem
s
> with multi-user use and other persons data coming into the tables. I thoug
ht
> that using global temp tables would be the answer to this. Any comments?
> Thanks
> Stephen K. Miyasato
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:881223D1-5B5E-45BB-B154-940BF5A4DE2E@.microsoft.com...
>
>