Wednesday, March 28, 2012
How to Using Variable in OPENROWSET T-SQL
How to use variable as parameter in stored procedure using OPENROWSET
'
I try the query below, but get an error. Please help me.
DECLARE @.job varchar(5)
SET @.job='RefreshTLMReport'
SELECT *
FROM
OPENROWSET('sqloledb'
, 'server=reportsvr;trusted_connection=yes
'
, 'set fmtonly off exec msdb..sp_help_job @.job_name='
+ @.job + ')'
ThanksResant
declare @.par int,@.sql varchar(8000)
set @.par=1
set @.sql ='exec mysp ' + cast(@.par as varchar(10))+''''
EXEC ('select *
from
OPENROWSET(''SQLOLEDB'',''SERVER=name;DA
TABASE=pubs;UID=sa;PWD=pass;'',''set
fmtonly off; ' + @.sql+')')
"Resant" <resant_v@.yahoo.com> wrote in message
news:1131512827.293294.303190@.g44g2000cwa.googlegroups.com...
> Hi,
> How to use variable as parameter in stored procedure using OPENROWSET
> '
> I try the query below, but get an error. Please help me.
> DECLARE @.job varchar(5)
> SET @.job='RefreshTLMReport'
> SELECT *
> FROM
> OPENROWSET('sqloledb'
> , 'server=reportsvr;trusted_connection=yes
'
> , 'set fmtonly off exec msdb..sp_help_job @.job_name='
> + @.job + ')'
>
> Thanks
>|||Hi
sp_help_job will not return a single resultset therefore you can't use it in
OPENQUERY. Your code is also truncating the jobname to 5 characters. Try
DECLARE @.job sysname
SET @.job='RefreshTLMReport'
EXEC reportsvr.msdb..sp_help_job @.job_name=@.job
John
"Resant" wrote:
> Hi,
> How to use variable as parameter in stored procedure using OPENROWSET
> '
> I try the query below, but get an error. Please help me.
> DECLARE @.job varchar(5)
> SET @.job='RefreshTLMReport'
> SELECT *
> FROM
> OPENROWSET('sqloledb'
> , 'server=reportsvr;trusted_connection=yes
'
> , 'set fmtonly off exec msdb..sp_help_job @.job_name='
> + @.job + ')'
>
> Thanks
>|||I think it's much better, but thanks all.
DECLARE @.job varchar(5)
SET @.job='RefreshTLMReport'
SELECT *
FROM
OPENROWSET('sqloledb'
, 'server=reportsvr;trusted_connection=yes
'
, 'set fmtonly off exec msdb..sp_help_job')
WHERE name=@.job|||Hi Resant
This will produce different results to running it directly and specifying
the @.job_name!
You have still declared @.job as varchar(5) instead of a sysname.
John
"Resant" wrote:
> I think it's much better, but thanks all.
> DECLARE @.job varchar(5)
> SET @.job='RefreshTLMReport'
> SELECT *
> FROM
> OPENROWSET('sqloledb'
> , 'server=reportsvr;trusted_connection=yes
'
> , 'set fmtonly off exec msdb..sp_help_job')
> WHERE name=@.job
>sql
Monday, March 26, 2012
how to use variable to create index
Hi ,
I would like to create index for a table and that index name must be random generated.
How to do this?
declare @.value varchar(50)
set @.value = rand()
set @.value = @.value + 'index-name'
create index @.value on tablename(variables)
Here it is:IF OBJECT_ID('T1') IS NOT NULL
DROP TABLE T1;
CREATE TABLE T1(
id int primary key,
[Name] sysname,
code sysname
)
GO
declare @.value varchar(50);
set @.value = rand()
set @.value = @.value + 'index-name'
declare @.cmd sysname;
SET @.cmd = 'CREATE INDEX [' + @.value + '] ON T1([Name])';
EXEC(@.cmd);
Thanks,
Zuomin
Sunday, February 19, 2012
How to use a SP parameter in a where in clause?
I am trying to do a 'WHERE IN' clause by taking the value of a paremeter and using it as the value in the WHERE IN clause such as:
DECLARE @.parm NVARCHAR(1000) = 'IBM, MOT, MSFT'
SELECT * FROM SomeStockTable WHERE TickerSymbol IN (@.parm)
I would like to avoid using an EXEC statement and string-building this whole query, since that is ugle and hard to maintain.
ANy ideas?
TYVM
See the discussion on tmorton's fnSplit:
http://forums.asp.net/304221/ShowPost.aspx
although she recommends using it as part of a join, I'm lazy. I normally do somehting like this (Which should turn out to be fairly equivalent, but easier for me to read):
SELECT *
FROM sometable
WHERE ID IN (SELECT ID FROM dbo.Split(@.param,DEFAULT))
-- My Split function, which is similair to tmorton's has only one field named id, and the default for the second parameter is a comma. I have found the performance to be more than fine, even on very large and complex queries. Some of which tie together 10+ tables, of which have 10 million+ rows.