GRANTING a variable

When exec.
GRANT SELECT ON @.variable_name to user_name
The above does not work due to the variable.
Is there no way of dynamically assigning the table-name?
Thks again.You have to create the complete statement as a string ad then execute it
with EXEC or sp_xecutesql. Read an excellent article at
http://www.algonet.se/~sommar/dynamic_sql.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Wayne" <anonymous@.discussions.microsoft.com> wrote in message
news:11f301c3fb91$454511d0$a401280a@.phx.gbl...
> When exec.
> GRANT SELECT ON @.variable_name to user_name
> The above does not work due to the variable.
> Is there no way of dynamically assigning the table-name?
> Thks again.|||Hi,
Yes you can do it, Look in to the below code
declare @.sql nvarchar(200)
declare @.tbl_name varchar(30)
set @.tbl_name='sysobjects'
set @.sql = 'grant select on '+ @.tbl_name + ' to public'
exec sp_executesql @.sql
Thanks
Hari
MCDBA
"Wayne" <anonymous@.discussions.microsoft.com> wrote in message
news:11f301c3fb91$454511d0$a401280a@.phx.gbl...
> When exec.
> GRANT SELECT ON @.variable_name to user_name
> The above does not work due to the variable.
> Is there no way of dynamically assigning the table-name?
> Thks again.sql

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 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

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

Giving Table name as a Variable

Hi guys,

I want to insert record to the table, the table name is a variable. I was tried like this,

declare @.Table_Name as varchar(255)

declare @.MM as varchar(2)

declare @.YY as varchar(4)

set @.MM = month(getdate())

set @.YY = year(getdate())

set @.Table_Name = 'tb_XXXX_' + @.MM + @.YY

INSERT INTO @.Table_Name (column1,....) VALUES (@.Column1,...)

If I print the @.Table_Name variable it shows the corret Table name. But while executing the Procedure it shows a error message

'Must declare the sclar variable @.Table_Name'

How do I implement this? Please anyone had a experience like this tell me a way. Thanks in advance.

Arun.

You have to use the dynamic SQL here. Here the sample to use the dynamic SQL – using sp_executesql or exec

Code Snippet

Create Table #TestDynSQL

(

Id int,

Name varchar(100),

DOB Datetime

);

Declare @.P_Id int, @.P_Name varchar(100), @.P_dob datetime

Declare @.SQL as Nvarchar(4000)

Declare @.ParamDecl as Nvarchar(4000)

Set @.SQL = N'Insert Into #TestDynSQL Values(@.id, @.name, @.dob)'

Set @.ParamDecl = N'@.id int, @.name varchar(100), @.DOB datetime'

Set @.P_id=1

Set @.P_Name='Mani'

Set @.P_Dob='1979-07-26'

--Highly recommanded to use the sp_executesql

Exec sp_executesql @.SQL, @.ParamDecl, @.P_id, @.P_name, @.P_dob

--or

--Beware of SQL injection while using the EXEC(STATEMENT)

Set @.SQL = 'Insert Into #TestDynSQL values(' + cast(@.P_Id as varchar) + ',''' + @.P_Name + ''',''' + Cast(@.P_Dob as varchar) + ''')'

Exec (@.SQL)

Select * from #TestDynSQL

|||

Mani gave you a good solution using dynamic SQL.

However, there are some issues about using dynamic SQL that you should be aware. There may be significant performance penalties and there may also be security issues. The following articles are good resources to learn and explore the use (and mis-use) of dynamic SQL.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx