Null values: exec

Showing posts with label exec. Show all posts

Monday, March 26, 2012

Rights running Exec (SQL)

Hi all.
It looks like I need READ rights on the involved tables using Exec (SQL)
within a prosedure instead of SELECT TableName and so on.It is not enough to
have EXEC rights on the prosedure itself. Is there any way around this or do
I need go grant read rights on all the tables in the SQL string.
If so, is there a easy way within the prosedure to check if the user have
this rights and if not grant it on the fly?
Thanx all.
gh> It looks like I need READ rights on the involved tables using Exec (SQL) within a prosedu
re
> instead of SELECT TableName and so on.
I assume that you wanted to say that you need SELECT permissions on the tabl
e, that EXEC procname
isn't enough. Yes, this is one of the drawbacks of using dynamic SQL. First
step is to see if you
really need that dynamic SQL. Then see what the new features in 2005 can do
for you:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/grantperm.html
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geir Holme" <geir@.multicase.no> wrote in message news:%23YXZ7i0mGHA.3928@.TK2MSFTNGP03.phx.
gbl...
> Hi all.
> It looks like I need READ rights on the involved tables using Exec (SQL) w
ithin a prosedure
> instead of SELECT TableName and so on.It is not enough to have EXEC rights
on the prosedure
> itself. Is there any way around this or do I need go grant read rights on
all the tables in the
> SQL string.
> If so, is there a easy way within the prosedure to check if the user have
this rights and if not
> grant it on the fly?
> Thanx all.
> gh
>|||Hi Tibor.
Thanx for the document. We are close to SQL2005, but not yet there.
I ended up with creating av view with the tables in question and runned the
EXEC on this view. That worked and the tables are still "safe" and hidden
for this user.
Thank you again for your interest in this.
-gh
"Geir Holme" <geir@.multicase.no> wrote in message
news:%23YXZ7i0mGHA.3928@.TK2MSFTNGP03.phx.gbl...
> Hi all.
> It looks like I need READ rights on the involved tables using Exec (SQL)
> within a prosedure instead of SELECT TableName and so on.It is not enough
> to have EXEC rights on the prosedure itself. Is there any way around this
> or do I need go grant read rights on all the tables in the SQL string.
> If so, is there a easy way within the prosedure to check if the user have
> this rights and if not grant it on the fly?
> Thanx all.
> gh
>

Monday, March 12, 2012

reusing temp table across different procs

Is there any way I can put the following consolidated statement into a proc
or function for reuse'
I have to use dynamic exec statement because filename,extension, or linked
server could change
<consolidated>
create table #t1(
c1 int,
c2 int,
c3 int,
c4 int,
c5 int
)
insert into #t1
EXEC('SELECT
c1,
c2,
c3,
c4,
c5
FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension + ']
')
select * from #t1
</consolidated>
And then I could call this consolidate code in different procs and do some
thing like this
inside proc1...
insert into dbo.mytbl1(c2,c3)
select c2,c3 from <consolidated #t1>
inside proc2...
insert into dbo.mytbl2(c4,c5)
select c4,c5 from <consolidated #t1>
Please let me know if this needs more clarification and TIA..what about global temp tables? prefixed with a double pound sign
http://sqlservercode.blogspot.com/|||If you create the #temp table in the "parent" proc, then execute other procs
from the parent, you can.
USE model;
GO
CREATE PROCEDURE dbo.foo2
AS
BEGIN
SET NOCOUNT ON;
INSERT #foo SELECT 1 UNION ALL SELECT 2;
END;
GO
CREATE PROCEDURE dbo.foo1
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #foo(a INT);
EXEC dbo.foo2;
SELECT * FROM #foo;
DROP TABLE #foo;
END;
GO
EXEC dbo.foo1;
GO
DROP PROCEDURE dbo.foo1, dbo.foo2;
GO
I've read your requirements and <consolidated> but I still don't quite
understand the actual goal and whether the above meets your requirements.
If you need to have independent stored procedures called separately and
still have access to the temp table, I think you are up the wrong tree.
http://www.sommarskog.se/share_data.html
"sqlster" <nospam@.nospam.com> wrote in message
news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
> Is there any way I can put the following consolidated statement into a
> proc
> or function for reuse'
> I have to use dynamic exec statement because filename,extension, or linked
> server could change
> <consolidated>
> create table #t1(
> c1 int,
> c2 int,
> c3 int,
> c4 int,
> c5 int
> )
> insert into #t1
> EXEC('SELECT
> c1,
> c2,
> c3,
> c4,
> c5
> FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension +
> ']')
> select * from #t1
> </consolidated>
> And then I could call this consolidate code in different procs and do some
> thing like this
> inside proc1...
> insert into dbo.mytbl1(c2,c3)
> select c2,c3 from <consolidated #t1>
>
> inside proc2...
> insert into dbo.mytbl2(c4,c5)
> select c4,c5 from <consolidated #t1>
> Please let me know if this needs more clarification and TIA..
>|||Sorry, I missed the cross-server bit.
I don't think that will be possible because the #temp table will probably
belong to a different session. Again, I don't think you can use #temp
tables for this.
While I'll admit I haven't tried this extensively, I don't think global temp
tables will help either, because the session won't necessarily be maintained
across servers, and the calling proc's session dictates the life of the
global temp table.
A
"sqlster" <nospam@.nospam.com> wrote in message
news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
> Is there any way I can put the following consolidated statement into a
> proc
> or function for reuse'
> I have to use dynamic exec statement because filename,extension, or linked
> server could change
> <consolidated>
> create table #t1(
> c1 int,
> c2 int,
> c3 int,
> c4 int,
> c5 int
> )
> insert into #t1
> EXEC('SELECT
> c1,
> c2,
> c3,
> c4,
> c5
> FROM '+@.mylinked_server + '...['+@.myfile_name + '#' + @.myfile_extension +
> ']')
> select * from #t1
> </consolidated>
> And then I could call this consolidate code in different procs and do some
> thing like this
> inside proc1...
> insert into dbo.mytbl1(c2,c3)
> select c2,c3 from <consolidated #t1>
>
> inside proc2...
> insert into dbo.mytbl2(c4,c5)
> select c4,c5 from <consolidated #t1>
> Please let me know if this needs more clarification and TIA..
>|||I would like to avoid global temp tables
"SQL" wrote:

> what about global temp tables? prefixed with a double pound sign
> http://sqlservercode.blogspot.com/
>|||Aaron,
I would like to import data from a csv file into separate tables. To pull
rows into a table format in query analyzer, I am using linked server.
Temp table gives me staging table functionality. I would like to pull all
the data into a central location and then reuse that central location in
different procs to populate or process that data.
Please let me know if this clarifies the overall approach and thanks again
in advance.
"Aaron Bertrand [SQL Server MVP]" wrote:

> If you create the #temp table in the "parent" proc, then execute other pro
cs
> from the parent, you can.
> USE model;
> GO
> CREATE PROCEDURE dbo.foo2
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT #foo SELECT 1 UNION ALL SELECT 2;
> END;
> GO
> CREATE PROCEDURE dbo.foo1
> AS
> BEGIN
> SET NOCOUNT ON;
> CREATE TABLE #foo(a INT);
> EXEC dbo.foo2;
> SELECT * FROM #foo;
> DROP TABLE #foo;
> END;
> GO
> EXEC dbo.foo1;
> GO
> DROP PROCEDURE dbo.foo1, dbo.foo2;
> GO
>
> I've read your requirements and <consolidated> but I still don't quite
> understand the actual goal and whether the above meets your requirements.
> If you need to have independent stored procedures called separately and
> still have access to the temp table, I think you are up the wrong tree.
> http://www.sommarskog.se/share_data.html
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
>
>|||I hate procs that reference temporary tables created by other procs. 8-[
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uKthgGKEGHA.3920@.tk2msftngp13.phx.gbl...
> Sorry, I missed the cross-server bit.
> I don't think that will be possible because the #temp table will probably
> belong to a different session. Again, I don't think you can use #temp
> tables for this.
> While I'll admit I haven't tried this extensively, I don't think global
> temp tables will help either, because the session won't necessarily be
> maintained across servers, and the calling proc's session dictates the
> life of the global temp table.
> A
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:4E26B406-7C49-4EE3-8A4B-AD5C3C91D04E@.microsoft.com...
>|||i a different proc references a "temp" table, it's not really a temp
table.
but for this problem you're having, you can use tempdb. just drop and
create table as you please. for cross server, you can link the servers
together or use opendatasource. try not to do too much cross server
queries.|||>I hate procs that reference temporary tables created by other procs. 8-[
Ok. They can be useful for others, in spite of your hatred for them.
http://www.aspfaq.com/2248
Yes, full of bad practices, but useful nonetheless
PS I hate slow drivers in the left lane, but they still exist, and I still
have to deal with them on my commute.
A|||> but for this problem you're having, you can use tempdb. just drop and
> create table as you please.
Except if the proc is called by two sessions at the same time, they will
both try CREATE TABLE dbo.MyTable, oops, one of them wins, one of them
loses.
A

Wednesday, March 7, 2012

returning success of exec(@sql) into a variable

i'm trying to build up the following dynamically
EXEC @.result =master..xp_cmdshell 'dir c:\test\D0004' , no_output
print @.result
so far i have got this..
set @.strsql = 'exec master..xp_cmdshell '+"'"+' dir c:\test\'+@.foldername +
+"'"+' , no_output'
exec(strsql)
this work fine to execute, but i cannot find anyway of returning the
success/failure into the variable @.result
Please helpYou can use sp_executesql with an output parameter for this...
declare @.strsql nvarchar(200)
declare @.result int
set @.strsql = 'exec @.result = master..xp_cmdshell '+"'"+' dir
c:\test\'+@.foldername +
+"'"+' , no_output'
exec sp_executesql @.strsql, '@.Result int OUTPUT', @.Result OUTPUT
print @.result
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"mat" <mat@.discussions.microsoft.com> wrote in message
news:ADCB55FF-0912-4AFA-9747-BAEF494F6CAD@.microsoft.com...
> i'm trying to build up the following dynamically
> EXEC @.result =master..xp_cmdshell 'dir c:\test\D0004' , no_output
> print @.result
> so far i have got this..
> set @.strsql = 'exec master..xp_cmdshell '+"'"+' dir c:\test\'+@.foldername
+
> +"'"+' , no_output'
> exec(strsql)
> this work fine to execute, but i cannot find anyway of returning the
> success/failure into the variable @.result
> Please help|||Thanks, I'm running this and butt i'm getting the error message
Server: Msg 214, Level 16, State 3, Procedure sp_executesql, Line 38
Procedure expects parameter '@.parameters' of type 'ntext/nchar/nvarchar'.
"Adam Machanic" wrote:
> You can use sp_executesql with an output parameter for this...
> declare @.strsql nvarchar(200)
> declare @.result int
> set @.strsql = 'exec @.result = master..xp_cmdshell '+"'"+' dir
> c:\test\'+@.foldername +
> +"'"+' , no_output'
> exec sp_executesql @.strsql, '@.Result int OUTPUT', @.Result OUTPUT
> print @.result
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "mat" <mat@.discussions.microsoft.com> wrote in message
> news:ADCB55FF-0912-4AFA-9747-BAEF494F6CAD@.microsoft.com...
> > i'm trying to build up the following dynamically
> > EXEC @.result =master..xp_cmdshell 'dir c:\test\D0004' , no_output
> > print @.result
> >
> > so far i have got this..
> >
> > set @.strsql = 'exec master..xp_cmdshell '+"'"+' dir c:\test\'+@.foldername
> +
> > +"'"+' , no_output'
> >
> > exec(strsql)
> >
> > this work fine to execute, but i cannot find anyway of returning the
> > success/failure into the variable @.result
> >
> > Please help
>
>|||Should have used the 'national' designator for the @.parameters string
literal:
exec sp_executesql @.strsql, N'@.Result int OUTPUT', @.Result OUTPUT
That tells SQL Server that the string is Unicode.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mat" <Mat@.discussions.microsoft.com> wrote in message
news:02ACC243-14F2-4A23-A5E4-C88CFE09EF74@.microsoft.com...
> Thanks, I'm running this and butt i'm getting the error message
> Server: Msg 214, Level 16, State 3, Procedure sp_executesql, Line 38
> Procedure expects parameter '@.parameters' of type 'ntext/nchar/nvarchar'.
> "Adam Machanic" wrote:
> > You can use sp_executesql with an output parameter for this...
> >
> > declare @.strsql nvarchar(200)
> > declare @.result int
> >
> > set @.strsql = 'exec @.result = master..xp_cmdshell '+"'"+' dir
> > c:\test\'+@.foldername +
> > +"'"+' , no_output'
> >
> > exec sp_executesql @.strsql, '@.Result int OUTPUT', @.Result OUTPUT
> >
> > print @.result
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "mat" <mat@.discussions.microsoft.com> wrote in message
> > news:ADCB55FF-0912-4AFA-9747-BAEF494F6CAD@.microsoft.com...
> > > i'm trying to build up the following dynamically
> > > EXEC @.result =master..xp_cmdshell 'dir c:\test\D0004' , no_output
> > > print @.result
> > >
> > > so far i have got this..
> > >
> > > set @.strsql = 'exec master..xp_cmdshell '+"'"+' dir
c:\test\'+@.foldername
> > +
> > > +"'"+' , no_output'
> > >
> > > exec(strsql)
> > >
> > > this work fine to execute, but i cannot find anyway of returning the
> > > success/failure into the variable @.result
> > >
> > > Please help
> >
> >
> >

Monday, March 26, 2012

Rights running Exec (SQL)

Monday, March 12, 2012

reusing temp table across different procs

Wednesday, March 7, 2012

returning success of exec(@sql) into a variable

returning success of exec(@sql) into a variable

returning success of exec(@sql) into a variable

Null values

Blog Archive

About Me