execution progress: exec

Showing posts with label exec. Show all posts

Friday, March 30, 2012

How to get the windows current user currently logged from SQL Server

Hi I am using exec master..xp_cmdshell "ECHO %USERNAME%" to get the windows
user name currently logged in. But it gives me the NULL value. I even tried
exec master..xp_cmdshell "ECHO SET %USERNAME%" but it doesnt help me.
Does any body have idea how to get the windows user name who is currently lo
gged from SQL Server.
Thanks for you help.
HemanthTry suser_sname()
Ray Higdon MCSE, MCDBA, CCNA
--
"Hemanth" <kamishetty@.hotmail.com> wrote in message
news:8843EE8B-D217-4DA1-A47F-D7D19804C819@.microsoft.com...
> Hi I am using exec master..xp_cmdshell "ECHO %USERNAME%" to get the
windows user name currently logged in. But it gives me the NULL value. I
even tried exec master..xp_cmdshell "ECHO SET %USERNAME%" but it doesnt
help me.
> Does any body have idea how to get the windows user name who is currently
logged from SQL Server.
> Thanks for you help.
> Hemanth
>sql

Monday, March 26, 2012

How to get the result of an Exec (@Sql) into a temp table.

Hi all.
I am working on some crosstab logic and needs to get my result into a
temporay table for further use and joins later in the prosedyre. It is
dynamic crosstabs so I don't know the number of columns on beforehand. After
my logic I can get a result that looks nice in QueryAnalyzer usning the Exec
command.
EXEC (@.sql)
What I want i for that result to get into a ad hock created temp table for
further use and joins. Just like
INSERT Col1 INTO #tmpTable FROM Tablename
Looks like the Exec command runs in another "space" so I can't reache the
#tmpTable even if my @.sql is correct with the INTO clause. If I print the
SQL, copies it and runs it it works fine ofcause.
Any ideas
thanx all
geirTry creating your temp table outside the Exec statement.
create table #mytemp
(
a int,
b int
)
Exec('insert ... into #mytemp')
select * from #mytemp
"Geir Holme" <geir@.multicase.no> wrote in message
news:O9AHtDOGFHA.3824@.TK2MSFTNGP10.phx.gbl...
> Hi all.
> I am working on some crosstab logic and needs to get my result into a
> temporay table for further use and joins later in the prosedyre. It is
> dynamic crosstabs so I don't know the number of columns on beforehand.
After
> my logic I can get a result that looks nice in QueryAnalyzer usning the
Exec
> command.
> EXEC (@.sql)
> What I want i for that result to get into a ad hock created temp table for
> further use and joins. Just like
> INSERT Col1 INTO #tmpTable FROM Tablename
> Looks like the Exec command runs in another "space" so I can't reache the
> #tmpTable even if my @.sql is correct with the INTO clause. If I print the
> SQL, copies it and runs it it works fine ofcause.
> Any ideas
> thanx all
> geir
>|||Hi Jonny.
This works fine as long as you know the number of columns AND the name of
the columns. Since I am inserting a dynamic crosstab i don't know the name
of the columns and the number of columns. That's the big issue here.
Thank you for your interest so far. Mabe you have some more ideas?
regards
geir
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:OuFWsROGFHA.1044@.TK2MSFTNGP14.phx.gbl...
> Try creating your temp table outside the Exec statement.
> create table #mytemp
> (
> a int,
> b int
> )
> Exec('insert ... into #mytemp')
> select * from #mytemp
> "Geir Holme" <geir@.multicase.no> wrote in message
> news:O9AHtDOGFHA.3824@.TK2MSFTNGP10.phx.gbl...
> After
> Exec
>|||Perhaps create a physical table in tempdb and then drop it when not needed.
"Geir Holme" <geir@.multicase.no> wrote in message
news:uH3$GKPGFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi Jonny.
> This works fine as long as you know the number of columns AND the name of
> the columns. Since I am inserting a dynamic crosstab i don't know the name
> of the columns and the number of columns. That's the big issue here.
> Thank you for your interest so far. Mabe you have some more ideas?
>
> regards
> geir
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:OuFWsROGFHA.1044@.TK2MSFTNGP14.phx.gbl...
the
the
>|||If you are doing a dynamic query like this, you will have to create a
dynamic temporary table. Consider building a permanent table in tempdb,
using a guid for the table name.
declare @.tableName varchar(40)
set @.tableName = newid()
declare @.query varchar(1000)
set @.query = 'create table tempdb..[' + @.tableName + '] ( column1
varchar(10))'
exec (@.query)
exec ('insert into tempdb..[' + @.tablename + '] values (''hello'') ')
exec ('select * from tempdb..[' + @.tablename + ']')
exec ('drop table tempdb..[' + @.tablename + ']')
Ugly, but it will work. You could also use select into instead of creating
the table. The most important thing is to use a permanent table. The guid
name of the table will ensure no name clashes.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Geir Holme" <geir@.multicase.no> wrote in message
news:uH3$GKPGFHA.3728@.TK2MSFTNGP14.phx.gbl...
> Hi Jonny.
> This works fine as long as you know the number of columns AND the name of
> the columns. Since I am inserting a dynamic crosstab i don't know the name
> of the columns and the number of columns. That's the big issue here.
> Thank you for your interest so far. Mabe you have some more ideas?
>
> regards
> geir
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:OuFWsROGFHA.1044@.TK2MSFTNGP14.phx.gbl...
>|||Geir Holme wrote:
> Hi all.
> I am working on some crosstab logic and needs to get my result into a
> temporay table for further use and joins later in the prosedyre. It is
> dynamic crosstabs so I don't know the number of columns on beforehand. Aft
er
> my logic I can get a result that looks nice in QueryAnalyzer usning the Ex
ec
> command.
> EXEC (@.sql)
> What I want i for that result to get into a ad hock created temp table for
> further use and joins. Just like
> INSERT Col1 INTO #tmpTable FROM Tablename
> Looks like the Exec command runs in another "space" so I can't reache the
> #tmpTable even if my @.sql is correct with the INTO clause. If I print the
> SQL, copies it and runs it it works fine ofcause.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Perhaps, instead of a procedure you'd like to try a function?
use Northwind
go
create FUNCTION udf_getOrders()
returns table
as
return(select top 100 * from orders)
go
select *
into #t
from dbo.udf_getOrders()
go
select * from #t
go
drop table #t
drop function dbo.udf_getOrders
go
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQhv1KoechKqOuFEgEQLZcwCgyqhNQjMg+vPO
HFdAUtIi/AFH/5AAoJcq
LOei0tjH80SMhUbTd+uGEPfh
=EhCr
--END PGP SIGNATURE--

Friday, March 9, 2012

How to get remote query timeout value

Hi,
We can use
EXEC sp_configure 'remote query timeout', 6000
to set the remote query timeout value.
How can we get the value remote query timeout ?> How can we get the value remote query timeout ?
You can execute sp_configure without the @.configvalue parameter to show the
existing configuration value:
EXEC sp_configure 'remote query timeout'
If you need to retrieve the value into a variable using Transact-SQL, here's
one method:
IF OBJECT_ID('tempdb..#ConfigValues') IS NOT NULL
DROP TABLE #ConfigValues
DECLARE @.run_value int
CREATE TABLE #ConfigValues
(
name varchar (35) NOT NULL,
minimum int,
maximum int,
config_value int,
run_value int
)
INSERT INTO #ConfigValues
EXEC sp_configure 'remote query timeout'
SELECT @.run_value = run_value FROM #ConfigValues
DROP TABLE #ConfigValues
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:OpBzNAOyGHA.4548@.TK2MSFTNGP05.phx.gbl...
> Hi,
> We can use
> EXEC sp_configure 'remote query timeout', 6000
> to set the remote query timeout value.
> How can we get the value remote query timeout ?
>

how to get prepared statement of sp_excute?

Hi All,
In profile,We always see such infomation
"exec sp_execute 1, N'A001', N'A001'"
But who can tell me how to get out the prepared sql for "1",
Thanks.Look at the stmts before that one, you should be able to see one that says
something like
declare @.P1 int
set @.P1 = 1
exec sp_prepexec @.....
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"wy" <wy@.juyee.com> wrote in message
news:%230YWAJgSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> In profile,We always see such infomation
> "exec sp_execute 1, N'A001', N'A001'"
> But who can tell me how to get out the prepared sql for "1",
> Thanks.
>|||The event log is too large,and I can't find the stmt just before that
one,because the stmt may prepare far away from that one.
"Peter Yeoh" <nospam@.nospam.com> wrote in message
news:uETFSfgSEHA.3844@.TK2MSFTNGP11.phx.gbl...
> Look at the stmts before that one, you should be able to see one that says
> something like
> declare @.P1 int
> set @.P1 = 1
> exec sp_prepexec @.....
> Peter Yeoh
> http://www.yohz.com
> Need smaller backup files? Try MiniSQLBackup
>
> "wy" <wy@.juyee.com> wrote in message
> news:%230YWAJgSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > In profile,We always see such infomation
> > "exec sp_execute 1, N'A001', N'A001'"
> > But who can tell me how to get out the prepared sql for "1",
> > Thanks.
> >
> >
>|||How about using the Find feature in Profiler?
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"wy" <wy@.juyee.com> wrote in message
news:uB5AyaiSEHA.3872@.TK2MSFTNGP10.phx.gbl...
> The event log is too large,and I can't find the stmt just before that
> one,because the stmt may prepare far away from that one.
>
> "Peter Yeoh" <nospam@.nospam.com> wrote in message
> news:uETFSfgSEHA.3844@.TK2MSFTNGP11.phx.gbl...
> > Look at the stmts before that one, you should be able to see one that
says
> > something like
> >
> > declare @.P1 int
> > set @.P1 = 1
> > exec sp_prepexec @.....
> >
> > Peter Yeoh
> > http://www.yohz.com
> > Need smaller backup files? Try MiniSQLBackup
> >
> >
> > "wy" <wy@.juyee.com> wrote in message
> > news:%230YWAJgSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > >
> > > In profile,We always see such infomation
> > > "exec sp_execute 1, N'A001', N'A001'"
> > > But who can tell me how to get out the prepared sql for "1",
> > > Thanks.
> > >
> > >
> >
> >
>

Friday, March 30, 2012

How to get the windows current user currently logged from SQL Server

Monday, March 26, 2012

How to get the result of an Exec (@Sql) into a temp table.

Friday, March 9, 2012

How to get remote query timeout value

how to get prepared statement of sp_excute?

how to get prepared statement of sp_excute?

how to get prepared statement of sp_excute?

execution progress

Blog Archive

About Me