grant DELETE Global Temp Table permission to users

I suppose I have to do it in the tempdb database. I create a new user in thi
s database and add him to the db_owner role. Everything seems to work fine.
After restart SQL Server the users are dropded in the tempdb database.
Which is the correct way to grant these rights to a user?
Thanks."Edi Fellmann" <anonymous@.discussions.microsoft.com> wrote in message
news:E0499352-2B5D-46C4-A38E-34FC85B5BEC9@.microsoft.com...
> I suppose I have to do it in the tempdb database. I create a new user in
this database and add him to the db_owner role. Everything seems to work
fine.
> After restart SQL Server the users are dropded in the tempdb database.
> Which is the correct way to grant these rights to a user?
Tempdb is designed to be used by SQL Server as temporary storage and work
space. Any contents of tempdb are reset when SQL Server is restarted.
Consider creating a "temp" database for this purpose.
Steve|||I use the global temp table in a sp with dynamic SQL. It is not posible to u
se temp tables in this sp.
Is there any other solution?
Thankssql

Globals!FileName.Value?

Does anyone know if there is a Global that stores the file name? I am
looking to put something in the footer that lists the path and filename
of the report.
=Globals!ReportFolder + Globals!ReportName
Doesn't seem to work...any suggestions'
thnx
-benScratch that...it works ;-)
sullins602 wrote:
> Does anyone know if there is a Global that stores the file name? I am
> looking to put something in the footer that lists the path and filename
> of the report.
> =Globals!ReportFolder + Globals!ReportName
> Doesn't seem to work...any suggestions'
> thnx
> -ben

Global.asa for MS SQL 2000

Hello,
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
.....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
......
End Sub
</SCRIPT>
Thanks a lot,
Hank
Why would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>
|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>
|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>

Global.asa for MS SQL 2000

Hello,
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
.....
End Sub
</SCRIPT>
Thanks a lot,
HankWhy would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> on
>

Global.asa for MS SQL 2000

Hello,
I'd like to use global.asa to connect a database in MS SQL 2000, running on
the same MS Server 2003. I wondered if any of you know about the code for
the two functions:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
....
End Sub
</SCRIPT>
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
.....
End Sub
</SCRIPT>
Thanks a lot,
HankWhy would you connect to the database in global.asa? Connect to the
database in the code of each page. You don't want to create a connection
object for the application or session scope. http://www.aspfaq.com/2053
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Hank Leigh" <hairong@.msu.edu> wrote in message
news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
> Hello,
> I'd like to use global.asa to connect a database in MS SQL 2000, running
on
> the same MS Server 2003. I wondered if any of you know about the code for
> the two functions:
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Application_OnStart
> ....
> End Sub
> </SCRIPT>
> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
> Sub Session_OnStart
> .....
> End Sub
> </SCRIPT>
> Thanks a lot,
> Hank
>|||Thank you. That makes a lot of sense. My database file is at
C:\Program Files\Microsoft SQL Server\MSSQL\Data
Would you mind sharing a code to use in each of the ASP page?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
>> Hello,
>> I'd like to use global.asa to connect a database in MS SQL 2000, running
> on
>> the same MS Server 2003. I wondered if any of you know about the code for
>> the two functions:
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Application_OnStart
>> ....
>> End Sub
>> </SCRIPT>
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Session_OnStart
>> .....
>> End Sub
>> </SCRIPT>
>> Thanks a lot,
>> Hank
>>
>|||Thank you again. Instead of using global.asa, I embedded a connection string
in each page and it works really fast! Below is the code I modified from a
message at http://www.aspfaq.com, an excellent site:
cst = "Provider=SQLOLEDB;Data Source=(local);" & _
"Initial Catalog=signmeup;Network=DBMSSOCN;"& _
"User Id=sa;Password="
set signmeup = CreateObject("ADODB.Connection")
signmeup.open cst
All the best,
Hank
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewtt5k0$EHA.1604@.TK2MSFTNGP12.phx.gbl...
> Why would you connect to the database in global.asa? Connect to the
> database in the code of each page. You don't want to create a connection
> object for the application or session scope. http://www.aspfaq.com/2053
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Hank Leigh" <hairong@.msu.edu> wrote in message
> news:e8DrD7z$EHA.4004@.tk2msftngp13.phx.gbl...
>> Hello,
>> I'd like to use global.asa to connect a database in MS SQL 2000, running
> on
>> the same MS Server 2003. I wondered if any of you know about the code for
>> the two functions:
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Application_OnStart
>> ....
>> End Sub
>> </SCRIPT>
>> <SCRIPT LANGUAGE=VBScript RUNAT=Server>
>> Sub Session_OnStart
>> .....
>> End Sub
>> </SCRIPT>
>> Thanks a lot,
>> Hank
>>
>

global vars in script files ?

Is it possible to declare a global variable in a SQL script ... or some
workaround ?
[ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
In the following script, the local var @.ptId is lost once a "GO" is
executed.
USE [ContentDB]
GO
INSERT INTO [dbo].[PageTypes]
([ptName]
,[ptPath]
,[ptParamName])
VALUES
('unused'
,'/redirect.aspx'
,'url')
DECLARE @.ptId int
SET @.ptId = @.@.IDENTITY
.
.
.
.
<lots and lots of other SQL>
.
.
.
.
GO
.
.
.
.
<lots and lots of other SQL>
.
.
.There are no global variables in TSQL. You can use a temp table for this, or
check out SET
CONTEXT_INFO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:eYtWs8JRGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Is it possible to declare a global variable in a SQL script ... or some wo
rkaround ?
> [ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
> In the following script, the local var @.ptId is lost once a "GO" is execut
ed.
> USE [ContentDB]
> GO
> INSERT INTO [dbo].[PageTypes]
> ([ptName]
> ,[ptPath]
> ,[ptParamName])
> VALUES
> ('unused'
> ,'/redirect.aspx'
> ,'url')
> DECLARE @.ptId int
> SET @.ptId = @.@.IDENTITY
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
> .
> GO
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
>

global variables urgent,

hey
i declare 3 global variables inside a formula.but can not refer those variables in another formula in the same report.
tell me how & where to declare global variables.
thanx.hi
try using this way :

formula @.x
numbervar a;
a:=10+20;

formula @.y
numbervar b;
numbervar a;
b:= 20+30;
b:=a+b;

as many variable as you want decalre in a formula, but the each formula should declare the variable.

This will give an idea about the global varialbles.
please try and let me know.|||Use shared variable also. Read it more in help file

global variables in dtspump task

I have two connections, an oracle and sqlserver. the dtspump task is pulling data from oracle to sqlserver. I am trying to automate this package by substituting the where clause values to variables since they are based on year and month. My question is - how does one use global variables in a dtspump task? How can I use variables to do this? Can it be done or do I need to use an Active X script for it?

thanks for any help.
novice userMay check with SQL DTS (http://www.sqldts.com) website.

Global Variables And the Script Component in DataFlow

I can't find anything on how to get to a global variable in a script component in the dataflow. I can get to it in a script task with no problem by using dts.variables but i doesn't appear you can do the dts variables in the script component.

I did add it to the readwrite variable list but I haven't been able to access it.

Type "Me.Variables." and upon typing the second period, intellisense will kick in and give you a alist of all the vars that you have put into ReadOnlyVariables/ReadWriteVariables.

-Jamie

Global Variables

Hello,
Im having a little problem(i hope) with global variables.
Im working with the DTS of the SQL SERVER 2000! Does someone knows how can i load global variables using an SQL Task!?
Thank you all!!
Kind Regards,
LULUin package properties, add global variable x with correct type
create execute sql task , select the value you want to
populate your global variable,click on parameters in the exec sql task properties,
click on output parameters,set type (row,rowset), map the output to the correct global variable.
To use the variable, in a exec sql task, click on parameters,
get your global variable, map it to parameter 1
then in query use a ? to reference the global variable.
-des|||Originally posted by DesmondX
in package properties, add global variable x with correct type
create execute sql task , select the value you want to
populate your global variable,click on parameters in the exec sql task properties,
click on output parameters,set type (row,rowset), map the output to the correct global variable.
To use the variable, in a exec sql task, click on parameters,
get your global variable, map it to parameter 1
then in query use a ? to reference the global variable.
-des

Thanks.... DESMONDX

Global Variables

Hi,

Is there a way to declare a persistent global variable in SQL Server?

I'd like my stored procs to fetch data in a different source depending on a debug (or development) variable.

For example, I'd like to be able to set a variable to either 0 or 1 (true or false) and have a static SP defined as:

IF @.MYVARIABLE = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')

What do you think? Since these SPs should be called a lot, I don't want to store the info in a table, I want it as a global variableso it will be as fast as possible.

Any other suggestions are also welcomed.

Thanks,

Skip.Wouldn't you just put this into a table? Then add the statement:

DECLARE
@.Environment bit

SELECT @.Environment = Environment FROM tblFlags

IF @.Environment = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')

But wouldn't it be better to have a dedicated test server with data refreshed as you require? Sorry, may not always be realistic, but just a thought.

Regards,

hmscott|||First of all, yes having a dev server makes way more sense. Actually, this is what we have here, the switch (the if...else) will be used to create the linked server to point to the correct server instance. The example above was only a reference and did not represent an actual situation.

Second, what I have now is a variable in a "commands" table. It works fine but the function is called so many times that it slows my system. I'd like to have a global variable in memory to speed up the process of reading its value.

Any other suggestions?

Thanks,

Skip.|||Nothing comes to mind. You can use the PINTABLE command to put that table into memory, but if it is called so frequently, it's probably already there.

You said you have a "commands" table, but that the function (what function?) is called so often that it slows the system. Just to check, is the table very large? Is it properly indexed? What do you consider to be heavy usage?

We have a particular sp that is called more or less each time a user hits a page on our site (to pull back configuration and web settings). We have about 300 users and about 2000 page hits per day (frequently more). I've never had any complaints about this particular sp running slowly.

Regards,

hmscott

Global variable value lost during insertion in a table

Hi,

This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.

This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.

My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?

How can i insert the value stored in a variable in a table from previous SQL Task.

Can anyone suggest some solution,links to try a workaround?

Thanks in advance.

Regards,

Aman

Hello,

Im not sure i understand your question, but even so, i think that you want a variable to exist during all the execution of your DTS, so If you variable has a dts scope not a function or a package scope, should fix your problem.

Or you are telling that you have a dts that calls several packages and in one package you do your sql insert task and then you will call another package, inside the same dts?

Regards,

|||

Hi,

I had declared my variables with global scope(at package level). In my 1st 2 Execute SQL Tasks i was saving those variables as input(which was wrong!). When I changed the variable property as Output things worked fine. In my 3rd SQL Task I wanted to insert those variables.

Moreover I was working on a single package. Hopefully you got my requirement now?

anyway thanks for your reply.

Regards,

Aman

|||

Hello Aman,

I assume that you still have the problem altough you marked the thread as awnsered.

In the variables tab activate the show user variables button, check the information for that.

Afterwards check if the sql task has, in the properties, the result set to single row. If you are expecting to have more than 1 line, then you would need a foreach loop to read every value of the variable.

If all that im saying isnt helping you, use the breakpoints and check the locals tab for the value of the variables.

Because, the variables, shouldn't loose the value, something is wrong.

Hope you can get your problem sorted out.

Good luck

Global variable value lost during insertion in a table

Hi,

This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.

This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.

My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?

How can i insert the value stored in a variable in a table from previous SQL Task.

Can anyone suggest some solution,links to try a workaround?

Thanks in advance.

Regards,

Aman

Hello,

Im not sure i understand your question, but even so, i think that you want a variable to exist during all the execution of your DTS, so If you variable has a dts scope not a function or a package scope, should fix your problem.

Or you are telling that you have a dts that calls several packages and in one package you do your sql insert task and then you will call another package, inside the same dts?

Regards,

|||

Hi,

I had declared my variables with global scope(at package level). In my 1st 2 Execute SQL Tasks i was saving those variables as input(which was wrong!). When I changed the variable property as Output things worked fine. In my 3rd SQL Task I wanted to insert those variables.

Moreover I was working on a single package. Hopefully you got my requirement now?

anyway thanks for your reply.

Regards,

Aman

|||

Hello Aman,

I assume that you still have the problem altough you marked the thread as awnsered.

In the variables tab activate the show user variables button, check the information for that.

Afterwards check if the sql task has, in the properties, the result set to single row. If you are expecting to have more than 1 line, then you would need a foreach loop to read every value of the variable.

If all that im saying isnt helping you, use the breakpoints and check the locals tab for the value of the variables.

Because, the variables, shouldn't loose the value, something is wrong.

Hope you can get your problem sorted out.

Good luck

Global variable scope - across package

Hi,

I have a Main.dtsx file which is having executing some 10 .dtsx packages. Can I declare a variable in Main.dtsx and use it in all the other packages which it is executing?

Note: I am using Yukon April CTP

Thanks
HariniYes you can. This post explains how: http://blogs.conchango.com/jamiethomson/archive/2005/09/01/2096.aspx

-Jamie|||Thanks. This is what exactly I want.

Global Variable question

Anyone out there know how to bring in the global variable value for where the
report was run from, whether it's the core report or a linked report? There
already is a global variable for Report Folder but that displays where the
core object sits so if you have a linked report sitting in a different
folder, you get misleading data.
It would be nice to be able to display the actual folder from where the
report was run.
Any ideas out there? Any help appreciated.
Thanks
rjsehOne thing you might try is to create a parameter, and have its data set query
the reporting server database to follow the link back to its parent...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"rjseh2001" wrote:
> Anyone out there know how to bring in the global variable value for where the
> report was run from, whether it's the core report or a linked report? There
> already is a global variable for Report Folder but that displays where the
> core object sits so if you have a linked report sitting in a different
> folder, you get misleading data.
> It would be nice to be able to display the actual folder from where the
> report was run.
> Any ideas out there? Any help appreciated.
> Thanks
> rjseh

Global variable in stored procedure

Hi I'm having problems using a global variable that has been declared as a varchar in a stored procedure. The stored procedure is recursive, and after calling itself I'm no longer able to access the global variable, see the sp bellow.

Any help would be great

Thanks

Dan

CREATE PROCEDURE dbo.kb_c_getChildren
(
@.CategoryID uniqueidentifier
)
AS
SET NOCOUNT ON

/* Global variables */
if @.@.nestlevel = 1
begin
declare @.@.CatList varchar (8000)
set @.@.CatList = ''
end

/* Find children */

declare @.child uniqueidentifier
declare children cursor local for
select CatID from Category where Parent_CatID = @.CategoryID

open children

fetch next from children
into @.child

while @.@.fetch_status = 0
begin
set @.@.CatList = @.@.CatList + '{' + cast(@.child as varchar(38)) + '},'
print cast(@.@.nestlevel as varchar(3)) + ' ' + cast(@.child as varchar(38))
if exists(select CatID from Category where Parent_CatID = @.child)
begin
/* If the child category has children, find them */
exec kb_c_getChildren @.child
end

fetch next from children
into @.child

end

close children
deallocate children

print @.@.CatList

RETURN 1When I looked in Books On Line for Global variables I didn't find anyhting to support what you are trying to do. Would this work?

CREATE PROCEDURE dbo.kb_c_getChildren(
@.CategoryID uniqueidentifier,
@.CatList varchar(8000) = Null OUTPUT)
AS
SET NOCOUNT ON

/* Find children */

declare @.child uniqueidentifier
select @.child = min(CatID)
from Category
where PArent_CatID = @.CategoryID

while (@.child is not null) begin
set @.CatList = @.CatList + '{' + cast(@.child as varchar(38)) + '},'
print cast(@.@.nestlevel as varchar(3)) + ' ' + cast(@.child as varchar(38))
if exists(select CatID from Category where Parent_CatID = @.child) begin
/* If the child category has children, find them */
exec kb_c_getChildren @.child, @.CatList OUTPUT
end

select @.child = min(CatID)
from Category
where Parent_CatID = @.CategoryID
and CatID > @.child
end

if (@.@.nestlevel = 1)
print @.CatList

RETURN 1

I changed from using a cursor to a simple select and test, this is just a personnal thing for me, and changed your catlist to be an optional output parameter. On the 2nd and subsequent calls to the SP you will past your populated catlist to kb_c_getChildren, modify the contents, and return it to the calling sp. on the last itiration you should fall out of the while loop print the results.|||Hi Paul,

Thanks for the info, couldn't use the select statement as I'm using uniqueidentifers, but the output parameter works a treat.

Thanks again

Dan

Global Variable in SQL Server

Hi,

I have a question on SQL Server.

How do I have a value that passing from application to Stored Proc. Then the variable will pass from stored proc to the trigger without storing into any table.

I have done by using declaring #TempTable on StoredProc and use it in Triggers but it doesn't work. Anyone know any alternative?

Please help.

Thanks

If those passed parameters are already inserted/updated on your table then you can access those values from the trigger using INSERTED table. (Note Inserted table only accessable from the Trigger Scope & it will have the same table structure as the main table).

Inside Your Trigger:

Code Snippet

Declare @.SomeValue as Varchar(100);

Select @.SomeValue = SomeColumn From Inserted;

It is not good idea to use Gloabal Variable / Temp Table on triggers. You can't say the values always inserted from your SP.

|||

No. the passed parameter I do not want to stored in the table due to some reason. How can I pass the value from stored proc to triggers without storing into table?

|||

You cannot

pass the value from stored proc to triggers

without storing the data in the table.

All input data for the TRIGGER MUST exist within the TABLE that the TRIGGER fires on.

Of course, if you do not wish that the data be kept in the database for concern about security/visibility, you could have the TRIGGER set the field to NULL -thereby obliterating the data that was initially input.

|||Can we do something on tempdb? I mean can I stored the value into tempdb in stored proc then retrieve it from tempdb in triggers?|||

According to you,

I have done by using declaring #TempTable on StoredProc and use it in Triggers but it doesn't work.

Did it work?

|||

The following example may help you...

Code Snippet

Create Table ThisIsIt (
[Id] int,
[Value] varchar(100))

go

Create Table ThisIsLog (
[Users] varchar(100),
[When] datetime,
[Id] int,
[Operation] int,
[ValuesAffected] Varchar(8000))

go

Create Trigger trg_ThisIsIt_logger on ThisIsIt For Insert
as
Begin
Declare @.ValuesAffected as Varchar(8000);
Declare @.User as varchar(100);
Declare @.Id as Int;

Select @.ValuesAffected = '"?1";"?2"'
Select @.Id = Id, @.ValuesAffected = Replace(Replace(@.ValuesAffected,'?1', Id),'?2',Value)
From Inserted
If Exists(Select ID From tempdb..Sysobjects Where id = Object_id('tempdb..#Info'))
Select @.User = [User] from #Info;

Insert Into ThisIsLog Values
(@.User, getdate(), @.Id, 1, @.ValuesAffected)
End

go

Create proc InsertThisIsIt
(
@.Id int,
@.Value varchar(100),
@.User varchar(100)
)
as
Begin
Create table #Info
(
[User] varchar(100)
)

Insert Into #Info Values(@.User);

Insert Into ThisIsIt Values(@.Id, @.Value);
End

Global variable for hyperlinks in reports

We are using Reporting Services 2000. We have several reports that all
contain an "Edit" hyperlink that goes to a URL not related to the report
server url. We cannot directly enter this URL into the "Jump To" section,
because the url is different depending on the environment (dev, test,
production). So, because the reports uploaded in dev, test, and production
need to be identical, we need to get to this url some other way.
Ideally, we could upload a text file or enter some type of global variable
in the Report Manager that all the reports could access. The contents of
that variable/file would then be used to get the value for the "Jump To"
field of the hyperlink.
Does anyone have any ideas on the best way to accomplish this? Thanks!First off, you would want to use Jump to URL, not Jump to Report. The URL
can be an expression.
You could have a Report Parameter but that gets a little cumbersome. What I
suggest is having a dataset that has a single value that determines whether
it is test, dev, production. It could query a table that returns this value.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"David" <dilworth@.newsgroups.nospam> wrote in message
news:F8AD4F01-FB63-44EE-9900-E72F8A2845BD@.microsoft.com...
> We are using Reporting Services 2000. We have several reports that all
> contain an "Edit" hyperlink that goes to a URL not related to the report
> server url. We cannot directly enter this URL into the "Jump To" section,
> because the url is different depending on the environment (dev, test,
> production). So, because the reports uploaded in dev, test, and
> production
> need to be identical, we need to get to this url some other way.
> Ideally, we could upload a text file or enter some type of global variable
> in the Report Manager that all the reports could access. The contents of
> that variable/file would then be used to get the value for the "Jump To"
> field of the hyperlink.
> Does anyone have any ideas on the best way to accomplish this? Thanks!|||That worked great - thanks!
"Bruce L-C [MVP]" wrote:
> First off, you would want to use Jump to URL, not Jump to Report. The URL
> can be an expression.
> You could have a Report Parameter but that gets a little cumbersome. What I
> suggest is having a dataset that has a single value that determines whether
> it is test, dev, production. It could query a table that returns this value.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "David" <dilworth@.newsgroups.nospam> wrote in message
> news:F8AD4F01-FB63-44EE-9900-E72F8A2845BD@.microsoft.com...
> > We are using Reporting Services 2000. We have several reports that all
> > contain an "Edit" hyperlink that goes to a URL not related to the report
> > server url. We cannot directly enter this URL into the "Jump To" section,
> > because the url is different depending on the environment (dev, test,
> > production). So, because the reports uploaded in dev, test, and
> > production
> > need to be identical, we need to get to this url some other way.
> >
> > Ideally, we could upload a text file or enter some type of global variable
> > in the Report Manager that all the reports could access. The contents of
> > that variable/file would then be used to get the value for the "Jump To"
> > field of the hyperlink.
> >
> > Does anyone have any ideas on the best way to accomplish this? Thanks!
>
>

Global Variable

Hi All,

I tried to get a global variable in my task scritp by using "Dts.Variables("myVar").Value", every time I've got an error

The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.

I've seen some examples online to get global varaibles in task script and all of them display the same code

Any idea

Franck

To access variable this way it has to be included in the list of ReadOnlyVariables or ReadWriteVariables on your script task properties SCRIPT properties section. Remember also that variables names are case sensitive.

Global Variable

Dear All!
I want to know that how can i declare a global variable in database, assign some value to it, then using it in multiple triggers and procedure then deallocating that.
Please provide a smal example.
Regards,
Shabber.Create a User-Defined Function that returns the value?|||And deallocate it?

What the heck are you doing?

UDF is the only way to simulate a global variable, but if you then wipe out your UDF it will break your sprocs.|||I'd assume Shabber has no big need for deallocating. It's probably just that if there would have been such a thing as a global variable, then it would have been a good habit to deallocate once it wasn't needed anymore.|||Thanks for replying.

But how function will provide the functionality of Global Variables. A bit confusing.

Regards,
Shabber.|||You call the function, which will return the value you're after.|||Thanks for replying.

But how function will provide the functionality of Global Variables. A bit confusing.

Regards,
Shabber.

CREATE TABLE MyParameter (
SiteID int IDENTITY (1,1) NOT NULL,
SiteName varchar(255) NOT NULL
)

CREATE FUNCTION udfMyFunction
(@.p1 int)
RETURNS varchar(255)
AS
BEGIN
DECLARE @.sTemp varchar(255)

SELECT @.sTemp = SiteName FROM MyParameter WHERE SiteID = @.p1
RETURN @.sTemp
END

Example Data and Usage:

insert into MyParameter(SiteName) Values ('Foo')
insert into MyParameter(SiteName) Values ('Bar')

SELECT dbo.udfMyFunction (2)

I don't know that this example is all that useful, but maybe it will give you some ideas.

Regards,

hmscott