Showing posts with label call. Show all posts
Showing posts with label call. Show all posts

Tuesday, March 27, 2012

@@Identity not returning Value

I have a stored procedure that inserts a record. I call the @.@.Identity variable and assign that to a variable in my SQL statement in my asp.net page.

That all worked fine when i did it just like that. Now I'm using a new stored procedure that inserts records into 3 tables successively, and the value of the @.@.Identity field is no longer being returned.

As you can see below, since I don't want the identity field value of the 2 latter records, I call for that value immediately after the first insert. I then use the value to populate the other 2 tables. I just can't figure out why the value is not being returned to my asp.net application. Think there's something wrong with the SP or no?

When I pass the value of the TicketID variable to a text field after the insert, it gives me "@.TicketID".

Anyone have any ideas?


CREATE PROCEDURE [iguser].[newticket]
(
@.Category nvarchar(80),
@.Description nvarchar(200),
@.Detail nvarchar(3000),
@.OS nvarchar(150),
@.Browser nvarchar(250),
@.Internet nvarchar(100),
@.Method nvarchar(50),
@.Contacttime nvarchar(50),
@.Knowledge int,
@.Importance int,
@.Sendcopy bit,
@.Updateme bit,
@.ClientID int,
@.ContactID int,
@.TicketID integer OUTPUT
)
AS

INSERT INTO Tickets
(
Opendate,
Category,
Description,
Detail,
OS,
Browser,
Internet,
Method,
Contacttime,
Knowledge,
Importance,
Sendcopy,
Updateme
)
VALUES
(
Getdate(),
@.Category,
@.Description,
@.Detail,
@.OS,
@.Browser,
@.Internet,
@.Method,
@.Contacttime,
@.Knowledge,
@.Importance,
@.Sendcopy,
@.Updateme
)
SELECT
@.TicketID = @.@.Identity

INSERT INTO Contacts_to_Tickets
(
U2tUserID,
U2tTicketID
)
VALUES
(
@.ContactID,
@.TicketID
)

INSERT INTO Clients_to_Tickets
(
C2tClientID,
C2tTicketID
)
VALUES
(
@.ClientID,
@.TicketID
)

Fixed the problem, it was with my .net code|||The best practice is to constrain, you should use IDENT_CURRENT('Tickets') instead of @.@.IDENTITY when you are inserting into multiple tables. IDENT_CURRENT gives you the last Identity generated in a specific table, as @.@.IDENTITY has no constraint and returns the last Identity of any table in the session or scope.

Based on your execution @.@.IDENTITY will work, but I figured I would throw this out there anyway.

Sunday, March 25, 2012

HOW: Auotincrement without using autoincrement

I have a master table which I have been updating in multiple queries to
create a new row. It has a primary key, call it WorkID. I can't make it
autoincrement. And it is more complicated because...
WorkID can also appear in two other tables, as references to the master
record, but I need to provide for the master record being gone, and as
such the records in the other two tables are orphaned. That is fine.
To create a new master record I select MAX(WorkID) unioned on all 3
tables, and increment the result, and this is my new WorkID. However,
even if in a transaction, this query work can result in duplicate WorkID
numbers when new masters are created in extremely rapid succession. I
see the flaw here. Don't think this approach can be salvaged when the
MAX and INSERT queries are done separately.
I have been racking my brain to find a way to deal with this WITHOUT
resorting to auto-increment on the master table. I can think of no
expression for an INSERT value which will take the MAX+1 of WorkID on
the three tables, but it seems there must be a way to do this in a
single statement. Something like:
INSERT WORK (WorkID) VALUES (SELECT MAX(WorkID)+1 FROM ... UNION ... )
Maybe I need a new single row, single column table that arbitrates new
WorkID values? Or maybe someone has a favorite trick?
Thx - Lee>> I have a master table .. <<
I have not heard that term since I moved from navigational DBMS to
RDBMS in thw 1970's. Let's get back to the basics of an RDBMS. Rows
are not records; fields are not columns; tables are not files. The
differences are VITAL!!!
Of course not! An exposed physical locator created inside the hardware
cannot be a relational key by definition.
Well, you need a new brain :) What is the natrual, relational key in
the SPECS YOU NEVER POSTED -- repeated SPECS YOU NEVER POSTED? I
assume that you are not soooooo ignorant that you want to have a
"magical one-size-fits-all" auto numbering.|||Wow.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123641244.396767.117310@.g14g2000cwa.googlegroups.com...
> I have not heard that term since I moved from navigational DBMS to
> RDBMS in thw 1970's. Let's get back to the basics of an RDBMS. Rows
> are not records; fields are not columns; tables are not files. The
> differences are VITAL!!!
>
> Of course not! An exposed physical locator created inside the hardware
> cannot be a relational key by definition.
>
> Well, you need a new brain :) What is the natrual, relational key in
> the SPECS YOU NEVER POSTED -- repeated SPECS YOU NEVER POSTED? I
> assume that you are not soooooo ignorant that you want to have a
> "magical one-size-fits-all" auto numbering.
>|||On Tue, 09 Aug 2005 12:18:38 -0700, Lee Gillie wrote:

> I have been racking my brain to find a way to deal with this WITHOUT
> resorting to auto-increment on the master table. I can think of no
> expression for an INSERT value which will take the MAX+1 of WorkID on
> the three tables, but it seems there must be a way to do this in a
> single statement. Something like:
> INSERT WORK (WorkID) VALUES (SELECT MAX(WorkID)+1 FROM ... UNION ... )
> Maybe I need a new single row, single column table that arbitrates new
> WorkID values? Or maybe someone has a favorite trick?
> Thx - Lee
As Celko said, you could probably do well with a full redesign that uses a
natural key. Failing that, how about this:
INSERT WORK (WorkID)
SELECT 1+MAX(WorkID)
FROM (
SELECT MAX(WorkID) WorkID FROM WORK
UNION ALL
SELECT MAX(WorkID) WorkID FROM OtherTable1
UNION ALL
SELECT MAX(WorkID) WorkID FROM OtherTable2
)
A better way might be to keep your available unused workIDs in yet another
table. First gather all used WorkIDs in the table:
CREATE TABLE WorkIDs (WorkID int, Used bit)
INSERT INTO WorkIDs (WorkID, Used)
SELECT WorkID, 1 FROM WORK
UNION
SELECT WorkID, 1 FROM OtherTable1
UNION
SELECT WorkID, 1 FROM OtherTable2
Now add some new ones for future use:
DECLARE @.maxworkID int
DECLARE @.x int
SELECT @.maxworkID = MAX(WorkID) FROM WorkIDs
SET @.x = @.maxworkID
WHILE @.x < @.maxworkID + 10000
BEGIN
INSERT INTO WorkIDs (WorkID, Used) VALUES (@.x, 0)
SET @.x = @.x + 1
END
Now here's your procedure for creating a new workID:
BEGIN TRANSACTION
DECLARE @.newWorkID int
SELECT @.newWorkID = MIN(WorkID) FROM WorkIDs WHERE Used=0
INSERT INTO WORK (WorkID) VALUES (@.newWorkID)
UPDATE WorkIDs SET Used=1 WHERE WorkID=@.newWorkID
COMMIT TRANSACTION|||> BEGIN TRANSACTION
> DECLARE @.newWorkID int
> SELECT @.newWorkID = MIN(WorkID) FROM WorkIDs WHERE Used=0
> INSERT INTO WORK (WorkID) VALUES (@.newWorkID)
> UPDATE WorkIDs SET Used=1 WHERE WorkID=@.newWorkID
> COMMIT TRANSACTION
Actually this is the worst case scenario for a busy system unless you use
exclusive locks. Say two users start the select at the same time. Boom,
deadlock. They both have a shared read lock on the workId's, the both
insert into work and then the UPDATE tries to WorkIDs to the same value
requiring exclusive locks. (unless the Unique Key fails them in the Work
table.)

> INSERT WORK (WorkID)
> SELECT 1+MAX(WorkID)
> FROM (
> SELECT MAX(WorkID) WorkID FROM WORK
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable1
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable2
> )
This would have to single thread over ALL tables! The SELECT by default
would only take shared locks, so any two users could be getting the max
values at the same time.
This gets even worse if this transaction gets called in another transaction.
There is not a good way to do this without single threading.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Ross Presser" <rpresser@.NOSPAMgmail.com.invalid> wrote in message
news:85kyr6seugm6$.dlg@.rosspresser.dyndns.org...
> On Tue, 09 Aug 2005 12:18:38 -0700, Lee Gillie wrote:
>
> As Celko said, you could probably do well with a full redesign that uses a
> natural key. Failing that, how about this:
> INSERT WORK (WorkID)
> SELECT 1+MAX(WorkID)
> FROM (
> SELECT MAX(WorkID) WorkID FROM WORK
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable1
> UNION ALL
> SELECT MAX(WorkID) WorkID FROM OtherTable2
> )
> A better way might be to keep your available unused workIDs in yet another
> table. First gather all used WorkIDs in the table:
> CREATE TABLE WorkIDs (WorkID int, Used bit)
> INSERT INTO WorkIDs (WorkID, Used)
> SELECT WorkID, 1 FROM WORK
> UNION
> SELECT WorkID, 1 FROM OtherTable1
> UNION
> SELECT WorkID, 1 FROM OtherTable2
> Now add some new ones for future use:
> DECLARE @.maxworkID int
> DECLARE @.x int
> SELECT @.maxworkID = MAX(WorkID) FROM WorkIDs
> SET @.x = @.maxworkID
> WHILE @.x < @.maxworkID + 10000
> BEGIN
> INSERT INTO WorkIDs (WorkID, Used) VALUES (@.x, 0)
> SET @.x = @.x + 1
> END
> Now here's your procedure for creating a new workID:
> BEGIN TRANSACTION
> DECLARE @.newWorkID int
> SELECT @.newWorkID = MIN(WorkID) FROM WorkIDs WHERE Used=0
> INSERT INTO WORK (WorkID) VALUES (@.newWorkID)
> UPDATE WorkIDs SET Used=1 WHERE WorkID=@.newWorkID
> COMMIT TRANSACTION|||On Thu, 11 Aug 2005 21:39:02 -0500, Louis Davidson wrote:
[snip]

> This gets even worse if this transaction gets called in another transactio
n.
> There is not a good way to do this without single threading.
Thanks for your comments. So, is there any decent answer to the problem of
generating new WorkIDs without using IDENTITY?

Sunday, March 11, 2012

.Net Stored Procedures on IA64 SSAS

Does anybody know if it is possible to call .Net stored procedures from a 64 bit Analysis Services Installation? Unfortunately the .Net stored procedures which we are trying to use target the x86 platform and not IA64.

(The procedures use an ODBC driver that is only available to 32 bit applications.)

Any information would be appreciated. The trial and error process gives me an MDX error that says that my stored procedures cannot be found. However, SSAS lets me add the x86 assembly to my database without any problem.

You can't cross 32/64 bit process boundaries within a single process this way. You can't try to load 32bit stored procedure into 64 bit process. Analysis Server is not checking the target platform you compiled you SP for on load, but later as you can see it fails to load it.

If you absolutely must to use the 32bit ODBC driver, you have an option of installing 32bit AS on 64bit platform. But that is highly unintuitive. Take a look if you can find native 64bit version of OLEDB provider instead of using 32bit ODBC driver.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||I also would like to add here, that unless you are using COM based sprocs (which are off by default), the .NET sprocs are platform agnostic, just like .NET itself. You can take sproc .NET assembly and deploy it to either 32 or 64 bit server without recompilation - it should work fine.

Tuesday, March 6, 2012

.net and Sql Express

Hi all, I have written a simple .net class to call sql functions like create subscription and sync replication. I am using merge replication. My Question is this:-- my class calls Microsoft.SqlServer.Replication.dll. On a machine that has SQL Express loaded it works but on a machine that does not have sql express it fails. I have made a setup project and deployed my "service". I added all references into the project and all works well on a machine that has sql express. We are running a network with 1 machine that has sql express and other machines that plug into this sql express. Is there a sql express runtime setup or do I have to load sql express on each machine. OR Am I deploying my class wrong. my setup project has the Microsoft.SqlServer.Replication.dll under detected dependencies but not any where else. Do I need to add it as reference. Tried Under Properties of Microsoft.SqlServer.Replication.dll in setup project Register : vsdraCOMRelativePath / vsdraCOM Exclude : False All the dlls I use are deployed to the install directory but gives Error "could not load dll or assembly SqlServer.Replication" Cheers

I havn't use replication with express yet, but yu might want to install the sqlnative client onto the client machines. You can get this download from the microsoft downloads site.

|||

This particular dll seems to be part of the sdk kit of sql server 2005.

directory 90\SDK\Assemblies

The sql native client did not help. I installed it and still no avail.

How would one deploy the sdk kit files

Cheers

|||

Hi Robert,

I'm not clear on why you would want to run your service on a computer that doesn't have SQL Express installed, could you explian? Replication is about communication between two SQL Servers, without a SQL Server, what exactly are you synchronizing?

Thanks for the further detail.

Regards,

Mike

|||

hi,

Mike already pointed out there's no real sens in doing what you are trying to do...

are you perhaps trying something already built-in, like Query Notifications? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/querynotification.asp, http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx

regards

|||

I am trying to have one machine that replicates to a internet based server. This one machine is on our local lan. Other users can log into our program which then connects to the local "lan" sql express. Once all work is done, a "On Demand " sync is done. I wrote the dll to perform this on demand sync.I don't want sql express installed on all 10 machines but rather on one lan machine that everyone can use.

Problem is that the dll needs the sql replication dll to function. A machine without sql express installed cannot ask for the sync via the dll.It generates an error
I have tried to include the sql replication dll in my install but it still does not want to work.I think I am registering it wrong.If I install sql express then the dll works.
(I am using VS 2005 .net setup to deploy the project dll.). This is something I am trying to avoid.
At the moment the way it has to work is that everybody does their changes and then requests a sync update from the user, who has the sql express installed, to update the replication.Quite tiresome.

Regards

Robert

|||Robert you answer lies in the application distribution. So this thread should probably be moved to Coding forum.

But anyway. I think your answer lies in remoting, using a multi-tier application.

We have a similar setup where sql exp is used as a server for two or more client applications. You will need to create Data class that run on the machine where SQL Express is installed and then use .Net Remoting from your client machines to invoke the Data class. This centralised data class will be the one that instantiates the SQL replication dll and thus it will call it from it's local machine (The Data Server). For this purpose the data server is just the machine where SQL exp is installed.

There fore the clients need know nothing about SQL Replication only that they create the remoting object and instruct the data class on the server to do the work. Hence they don't need SQL Express installed on every machine.

If you are not familiar with remoting then you will need to do quite a bit of reading up as there can be pitfalls and it may require you application to be restructured completely.

Hope this points you to your answer

Cheers
Rab|||

Just a few question.

Why does the dll not work on its own.
Can I not simply distribute the dll.
I suppose the dll does not contain all the libary files from SQL to invoke the calls made to sql express.
Thanks anyway for the answer.

Cheers

Robert

.net and Sql Express

Hi all, I have written a simple .net class to call sql functions like create subscription and sync replication. I am using merge replication. My Question is this:-- my class calls Microsoft.SqlServer.Replication.dll. On a machine that has SQL Express loaded it works but on a machine that does not have sql express it fails. I have made a setup project and deployed my "service". I added all references into the project and all works well on a machine that has sql express. We are running a network with 1 machine that has sql express and other machines that plug into this sql express. Is there a sql express runtime setup or do I have to load sql express on each machine. OR Am I deploying my class wrong. my setup project has the Microsoft.SqlServer.Replication.dll under detected dependencies but not any where else. Do I need to add it as reference. Tried Under Properties of Microsoft.SqlServer.Replication.dll in setup project Register : vsdraCOMRelativePath / vsdraCOM Exclude : False All the dlls I use are deployed to the install directory but gives Error "could not load dll or assembly SqlServer.Replication" Cheers

I havn't use replication with express yet, but yu might want to install the sqlnative client onto the client machines. You can get this download from the microsoft downloads site.

|||

This particular dll seems to be part of the sdk kit of sql server 2005.

directory 90\SDK\Assemblies

The sql native client did not help. I installed it and still no avail.

How would one deploy the sdk kit files

Cheers

|||

Hi Robert,

I'm not clear on why you would want to run your service on a computer that doesn't have SQL Express installed, could you explian? Replication is about communication between two SQL Servers, without a SQL Server, what exactly are you synchronizing?

Thanks for the further detail.

Regards,

Mike

|||

hi,

Mike already pointed out there's no real sens in doing what you are trying to do...

are you perhaps trying something already built-in, like Query Notifications? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/querynotification.asp, http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx

regards

|||

I am trying to have one machine that replicates to a internet based server. This one machine is on our local lan. Other users can log into our program which then connects to the local "lan" sql express. Once all work is done, a "On Demand " sync is done. I wrote the dll to perform this on demand sync.I don't want sql express installed on all 10 machines but rather on one lan machine that everyone can use.

Problem is that the dll needs the sql replication dll to function. A machine without sql express installed cannot ask for the sync via the dll.It generates an error
I have tried to include the sql replication dll in my install but it still does not want to work.I think I am registering it wrong.If I install sql express then the dll works.
(I am using VS 2005 .net setup to deploy the project dll.). This is something I am trying to avoid.
At the moment the way it has to work is that everybody does their changes and then requests a sync update from the user, who has the sql express installed, to update the replication.Quite tiresome.

Regards

Robert

|||Robert you answer lies in the application distribution. So this thread should probably be moved to Coding forum.

But anyway. I think your answer lies in remoting, using a multi-tier application.

We have a similar setup where sql exp is used as a server for two or more client applications. You will need to create Data class that run on the machine where SQL Express is installed and then use .Net Remoting from your client machines to invoke the Data class. This centralised data class will be the one that instantiates the SQL replication dll and thus it will call it from it's local machine (The Data Server). For this purpose the data server is just the machine where SQL exp is installed.

There fore the clients need know nothing about SQL Replication only that they create the remoting object and instruct the data class on the server to do the work. Hence they don't need SQL Express installed on every machine.

If you are not familiar with remoting then you will need to do quite a bit of reading up as there can be pitfalls and it may require you application to be restructured completely.

Hope this points you to your answer

Cheers
Rab|||

Just a few question.

Why does the dll not work on its own.
Can I not simply distribute the dll.
I suppose the dll does not contain all the libary files from SQL to invoke the calls made to sql express.
Thanks anyway for the answer.

Cheers

Robert

.net and Sql Express

Hi all, I have written a simple .net class to call sql functions like create subscription and sync replication. I am using merge replication. My Question is this:-- my class calls Microsoft.SqlServer.Replication.dll. On a machine that has SQL Express loaded it works but on a machine that does not have sql express it fails. I have made a setup project and deployed my "service". I added all references into the project and all works well on a machine that has sql express. We are running a network with 1 machine that has sql express and other machines that plug into this sql express. Is there a sql express runtime setup or do I have to load sql express on each machine. OR Am I deploying my class wrong. my setup project has the Microsoft.SqlServer.Replication.dll under detected dependencies but not any where else. Do I need to add it as reference. Tried Under Properties of Microsoft.SqlServer.Replication.dll in setup project Register : vsdraCOMRelativePath / vsdraCOM Exclude : False All the dlls I use are deployed to the install directory but gives Error "could not load dll or assembly SqlServer.Replication" Cheers

I havn't use replication with express yet, but yu might want to install the sqlnative client onto the client machines. You can get this download from the microsoft downloads site.

|||

This particular dll seems to be part of the sdk kit of sql server 2005.

directory 90\SDK\Assemblies

The sql native client did not help. I installed it and still no avail.

How would one deploy the sdk kit files

Cheers

|||

Hi Robert,

I'm not clear on why you would want to run your service on a computer that doesn't have SQL Express installed, could you explian? Replication is about communication between two SQL Servers, without a SQL Server, what exactly are you synchronizing?

Thanks for the further detail.

Regards,

Mike

|||

hi,

Mike already pointed out there's no real sens in doing what you are trying to do...

are you perhaps trying something already built-in, like Query Notifications? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/querynotification.asp, http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx

regards

|||

I am trying to have one machine that replicates to a internet based server. This one machine is on our local lan. Other users can log into our program which then connects to the local "lan" sql express. Once all work is done, a "On Demand " sync is done. I wrote the dll to perform this on demand sync.I don't want sql express installed on all 10 machines but rather on one lan machine that everyone can use.

Problem is that the dll needs the sql replication dll to function. A machine without sql express installed cannot ask for the sync via the dll.It generates an error
I have tried to include the sql replication dll in my install but it still does not want to work.I think I am registering it wrong.If I install sql express then the dll works.
(I am using VS 2005 .net setup to deploy the project dll.). This is something I am trying to avoid.
At the moment the way it has to work is that everybody does their changes and then requests a sync update from the user, who has the sql express installed, to update the replication.Quite tiresome.

Regards

Robert

|||Robert you answer lies in the application distribution. So this thread should probably be moved to Coding forum.

But anyway. I think your answer lies in remoting, using a multi-tier application.

We have a similar setup where sql exp is used as a server for two or more client applications. You will need to create Data class that run on the machine where SQL Express is installed and then use .Net Remoting from your client machines to invoke the Data class. This centralised data class will be the one that instantiates the SQL replication dll and thus it will call it from it's local machine (The Data Server). For this purpose the data server is just the machine where SQL exp is installed.

There fore the clients need know nothing about SQL Replication only that they create the remoting object and instruct the data class on the server to do the work. Hence they don't need SQL Express installed on every machine.

If you are not familiar with remoting then you will need to do quite a bit of reading up as there can be pitfalls and it may require you application to be restructured completely.

Hope this points you to your answer

Cheers
Rab
|||

Just a few question.

Why does the dll not work on its own.
Can I not simply distribute the dll.
I suppose the dll does not contain all the libary files from SQL to invoke the calls made to sql express.
Thanks anyway for the answer.

Cheers

Robert

Thursday, February 9, 2012

(re)using a temporary table in a stored proc (was "Confusion")

Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?

Howdy!Yes..the local temp table is only around for the length of process...

A global temp table sticks around as long as it's referenced by any process...

So you can do something like this...basically wrapping your sprocs with a driver sproc

USE Northwind
GO

CREATE PROC mySproc00
AS
BEGIN
UPDATE ##myGlobalTemp99 SET CustomerId = 'VINET'
END
GO

CREATE PROC mySproc99
AS
BEGIN
SELECT * INTO ##myGlobalTemp99 FROM Orders

SELECT TOP 10 * FROM ##myGlobalTemp99

EXEC mySproc00

SELECT TOP 10 * FROM ##myGlobalTemp99

DROP TABLE ##myGlobalTemp99

END
GO

EXEC mySproc99
GO

DROP PROC mySproc00
DROP PROC mySproc99
GO|||Hi, sir. thanx for helpin around. there's a little problem though. The procedure will be executed by many users concurrently; i get error; table already exists when older session persist and another executes the procedure.

Any other guidlines!

Howdy!|||post what the table looks like...and what the sproc is suppose to be doing...|||Instead of Temp table, declare table type variable and work with that.

Madhivanan|||I believe he needs to pass and store data between processing threads...table variables won't let you do that.

Why I asked for the Table Layout is this.

Each Process gets it's own unique spid.

I would create a permanent table, and and a column for the spid.

Capture the spid, from a driver sproc, and for every row in the process make sure you use that spid.

Just make sure you clear out the rows for your spid before you start the process.|||Yes, Brett; that idea helped. thanx. :)

Howdy!|||Depending on the volum of activity...I might consider a partitioned view using the spid as the partioning range in different files and on different drives...

I'll take a look into this...|||I hate when people are so dogmatic in their statements. What would happen if you run this code? DON'T RUN IT, just answer the questions first!

create proc sp_1 as selct * from #t
go
create proc sp_2 as
ceate table #t (f1 int null)
insert #t select 1 union select 2
exec sp_1
go
exec sp_2
go
drop proc sp_1, sp_2
go

Here, regardless of how many users execute sp_2 simulteneously, they will always see only their temporary tables.|||Doooh...it's the same spid

As long as it's all referenced in the same driver your temp table is isolated..I got confused that it was with separate threads

Even with that...what I suggested wouldn't buy you anything anyway

Thanks for pointing that out...|||Doooh...Even with that...what I suggested wouldn't buy you anything anyway...So why did you suggest it? :D|||Cause I'm a moron...

It's like flying down the freeway...I missed the exit

You could use that method to pass data to a trigger though...|||So if that is not causing the problem he is having, then what is?

If i call #mytable after executing the procedure; won't work.

This seems to indicate he wants to access the table through a different connection...|||If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?Actually it looks like the same connection, unless his definition of a "session" is different...|||It's like flying down the freeway...I missed the exit...Don't feel bad, I use turn-arounds very often :D|||Then maybe he should post his code, 'cause something must be going wrong...|||He's probably creating the temp in the nested proc

The temp needs to be created in the driver

I imagine this is what's going on

create proc sp_2 as
create table #t (f1 int null)
insert #t select 1 union select 2
go

create proc sp_1 as
EXEC sp_2
select * from #t
go

exec sp_1
go

drop proc sp_1, sp_2
go|||Hi folks; u've been so kind! Sorry for getting back late;

I've a proc proc1 that performs some routine and outputs a temporary table #tab1. I want this table be accessed through other procs or within a batch that calls this procedure; WITHIN THE SAME CONNECTION(SESSION,SPID :))

The only way around i could find was the SPID idea into a permanent table.

The proc1 is a standard routine and many other procs or scripts need the output from it; i wonder if i could use function that returns a table... that could be used further.

Howdy|||actually i need to collect the record from a few join table. and then calculate the score and group the date in weekly basic.

so now i able to select all the data from the source, and then use case to create a column call weekGroup. but i fail to group weekGroup for the source. so i plan to create temporary table and store all the record that i select from the join table and then do again the select statement from the temporary table to group the data again.

anyone can help me?:eek:

regards
terence chua|||actually i need to collect the record from a few join table. and then calculate the score and group the date in weekly basic.

so now i able to select all the data from the source, and then use case to create a column call weekGroup. but i fail to group weekGroup for the source. so i plan to create temporary table and store all the record that i select from the join table and then do again the select statement from the temporary table to group the data again.

anyone can help me?:eek:

regards
terence chua

First read Brett's sticky on top of the forum.|||...and start a new thread rather than resurrecting this old one...