Showing posts with label t-sql. Show all posts
Showing posts with label t-sql. Show all posts

Wednesday, March 28, 2012

How to Using Variable in OPENROWSET T-SQL

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 + ')'
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 t-sql to email and attachment

I am new to sql and want to write a stored procedure to email a database group an attachment of a report that was created. Can someone please point me in the write direction thanks.WHat do you mean by report ? Is it a report like from Reporting Services or is it just a results set from a query ? DO you want to swnd it via SQL Mail or SMTP ? DO you use SQL 2k or SQL 2k5 ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||I want to send an export via smtp using sql 2005. Is there a built-in stored procedure that will allow me to send an smtp message, and attach my export csv.|||You could use the new Database Mail feature. But for your needs it is easier to use a SSIS package since you can export the data from table(s) to file and email it using built-in tasks. You could do the same within SQL Server also but you have to xp_cmdshell to run BCP out and that poses security risks among other things.

Friday, March 23, 2012

How to use switch - case statement in T-SQL..?

Hi,

I want to use switch - case statement in T-SQL stored procedure.

Can any one help regarding the same..?

for e.g.

switch (exp)

{

case 1 : stmt 1; break;

case 2 : stmt 2; break;

case 3 : stmt 3; break;

& so on.......

}

Hi,

See the following example

DECLARE @.TestVal int
SET @.TestVal = 3

SELECT
CASE @.TestVal
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Other'
END

Monday, March 19, 2012

How to use regular expressions on T-SQL?

as subjectCreate a CLR-based function - assuming you have SQL Server 2005.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"ABC" <abc@.abc.com> wrote in message
news:OY8pTyGOGHA.2012@.TK2MSFTNGP14.phx.gbl...
as subject|||We use SQL Server 2005. How to write a CLR-based function? Is there any
example?
"Tom Moreau" <tom@.dont.spam.me.cips.ca>
'?:urVZz%23GOGHA.1760@.TK2MSFTNGP10.phx.gbl...
> Create a CLR-based function - assuming you have SQL Server 2005.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "ABC" <abc@.abc.com> wrote in message
> news:OY8pTyGOGHA.2012@.TK2MSFTNGP14.phx.gbl...
> as subject
>|||Did you check the SQL Server 2005 Books online? There are several examples
you can find in various websites ( MSDN etc. ), blogs etc.
Anith

Friday, February 24, 2012

How to use Case or If statement in Inner Join ?


Hi All,

I am in need for to write the following query for a stored procedure in SQL Server 2000. Please observe the T-SQL code first.
Please disregard the numbers and Product Numbers, they are not the correct data. I used them for ease of understanding. But the query is identical.

Code Block

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
CASE WHEN @.vchSubscription = 'Weekly' THEN
INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' ) -- Category and SubCategory
OR PM.chProductNumber IN (
'weekly1', 'Weekly2', 'Weekly3', 'Weekly4' )
)
AND C.dtInsertDate > = @.dtIssueDate

CASE WHEN @.vchSubscription = 'Monthly' THEN

INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' )
OR PM.chProductNumber IN (
'Jan', 'Feb', 'Mar', ....'Dec')
)
AND C.dtInsertDate > = @.dtIssueDate

END

GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8


my requirement is join the same table, but depending on the subscription type i have to join to different product numbers.
I hope you understand. I have been trying this since yesterday, but no luck.

Any help would be greatly appreciated.Maybe this:

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
INNER JOIN ProductMaster PM
ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' ) -- Category and SubCategory
OR
( @.vchSubscription = 'Weekly' AND PM.chProductNumber IN (
'weekly1', 'Weekly2', 'Weekly3', 'Weekly4' )
)
OR
( @.vchSubscription = 'Monthly' AND PM.chProductNumber IN (
'Jan', 'Feb', 'Mar', ....'Dec')
)
)
AND C.dtInsertDate > = @.dtIssueDate
GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8|||

MadhuBabu,

You can use different statements or dynamic sql. You can not use "case" to control program flow. It is a function, so it has to return something when used.

if @.vchSubscription = 'Weekly'

select ...

else

if @.vchSubscription = 'Monthly'

select ...

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB|||

Hi Dale J Smile

Thank you very much for the answer. Well, just before 30 minutes I figured another less impressive method which is agreed on. following is the code for that

Code Block

DECLARE @.dtIssueDate DATETIME
SET @.dtIssueDate = '2007-10-01'

SELECT
C.iOwnerid,
MAX (C.DtInsertDate) AS [dtLastIssueDate]
INTO #Tble
FROM CustomerProduct C
INNER JOIN ProductMaster PM ON PM.chProductNumber = C.chProductNumber
AND ( ( PM.vchUser7='101557' AND PM.vchUser8='101557' )
OR PM.chProductNumber IN (
'week1', 'week2', .. 'week4' ,
'Jan', 'Feb', ... , 'Dec'
)
)
AND PM.tiRecordStatus = 1
AND C.dtInsertDate > = @.dtIssueDate
GROUP BY C.iOwnerid, PM.vchUser7, PM.vchUser8

/*
Remove Weekly Subscribers
*/
IF @.vchJournal = 'Monthly'
BEGIN
DELETE FROM #Tble WHERE iOwnerId IN (
SELECT DISTINCT C.iOwnerId FROM CustomerProduct C
INNER JOIN ProductMaster PM ON pm.chProductNumber = C.chProductNumber
AND PM.chProductNumber IN ('week1', 'week2', .., 'week4')
AND PM.tiRecordStatus = 1
AND C.dtInsertDate > = @.dtIssueDate
)
END
ELSE IF @.vchJournal = 'Weekly' /* Remove Monthly Subscribers */
BEGIN
DELETE FROM #Tble WHERE iOwnerId IN (
SELECT DISTINCT C.iOwnerId FROM CustomerProduct C
INNER JOIN ProductMaster PM ON pm.chProductNumber = C.chProductNumber
AND PM.chProductNumber IN ('Jan', 'Feb', ... , 'Dec')
AND PM.tiRecordStatus = 1
AND C.dtInsertDate > = @.dtIssueDate
)
END


But for sure, your code is more optimal, and am implementing it now...
Many Many Thanks.