how to construct dynamic sql in stored procedure

Hi Guys.

I want to construct a SQL in stored procedure in the following way. Please guide me how to achive this

CREATE PROCEDURE P_SAMPLE

(

@.P_ONE NVARCHAR(240)

)

AS

BEGIN

DECLARE

@.TMP_CNT INT

BEGIN

EXEC ('SELECT @.TMP_CNT = 1')

-- PRINT @.TMP_CNT

END

END

Regards

Mani

Use sp_executesql to pass/retrive the data to/from dynamic sql. Exec won’t help you here.

Code Snippet

DECLARE @.TMP_CNT INT

DECLARE @.SQL as NVarchar(4000)

DECLARE @.Params as NVarchar(4000)

Set @.SQL = N'SELECT @.TMP_CNT = 1'

Set @.Params = N'@.TMP_CNT as Int OUTPUT'

Exec sp_executesql @.SQL, @.Params, @.TMP_CNT OUTPUT

Select @.TMP_CNT

|||

here is one of the best resource in Dynamic sql

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

Madhu

How to configure Email Delivery to send out message in Plain/Text format?

Hi, guys,
I have an application which only accept Plain/Text format email, but the
email sent out by Reporting Services Email Delivery was wrap in HTML format.
Is it possible to configure Email Delivery to send out Plain/Text format
message?
Many thanks!
David ZengThe reports server should populate both the plain text and html parts of the
message. Are you saying that you can not have the html portion present? If
so then RS does not support this. You could have RS drop the files to a
local share and then have an app which stripped out the html part before
sending the message on.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Zeng" <dzeng@.pembrooke.com> wrote in message
news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> Hi, guys,
> I have an application which only accept Plain/Text format email, but the
> email sent out by Reporting Services Email Delivery was wrap in HTML
format.
> Is it possible to configure Email Delivery to send out Plain/Text format
> message?
> Many thanks!
> David Zeng
>|||Daniel,
That's right! I do not want the HTML portion present.
Are there any ways to configure Reporting Services to export report into CSV
format without header?
I am in the middle of one urgent project. I very much appreciate your help!
Thanks,
David Zeng
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:uBIS04vpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> The reports server should populate both the plain text and html parts of
the
> message. Are you saying that you can not have the html portion present?
If
> so then RS does not support this. You could have RS drop the files to a
> local share and then have an app which stripped out the html part before
> sending the message on.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "David Zeng" <dzeng@.pembrooke.com> wrote in message
> news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > Hi, guys,
> >
> > I have an application which only accept Plain/Text format email, but the
> > email sent out by Reporting Services Email Delivery was wrap in HTML
> format.
> > Is it possible to configure Email Delivery to send out Plain/Text format
> > message?
> >
> > Many thanks!
> > David Zeng
> >
> >
>|||Daniel,
That's right! I do not want the HTML portion present.
Are there any ways to configure Reporting Services to export report into CSV
format without header?
I am in the middle of one urgent project. I very much appreciate your help!
Thanks,
David Zeng
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:uBIS04vpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> The reports server should populate both the plain text and html parts of
the
> message. Are you saying that you can not have the html portion present?
If
> so then RS does not support this. You could have RS drop the files to a
> local share and then have an app which stripped out the html part before
> sending the message on.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "David Zeng" <dzeng@.pembrooke.com> wrote in message
> news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > Hi, guys,
> >
> > I have an application which only accept Plain/Text format email, but the
> > email sent out by Reporting Services Email Delivery was wrap in HTML
> format.
> > Is it possible to configure Email Delivery to send out Plain/Text format
> > message?
> >
> > Many thanks!
> > David Zeng
> >
> >
>|||No, you can not change the way the Viewer renders the report on export, nor
set any device info for subscriptions. You could write a Delivery extension
fairly quickly that would call the renderer with what ever device info you
want. Then just have it drop it to a file share. Would this solve your
problem?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Zeng" <dzeng@.pembrooke.com> wrote in message
news:#nukQ2wpEHA.3244@.tk2msftngp13.phx.gbl...
> Daniel,
> That's right! I do not want the HTML portion present.
> Are there any ways to configure Reporting Services to export report into
CSV
> format without header?
> I am in the middle of one urgent project. I very much appreciate your
help!
> Thanks,
> David Zeng
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:uBIS04vpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> > The reports server should populate both the plain text and html parts of
> the
> > message. Are you saying that you can not have the html portion present?
> If
> > so then RS does not support this. You could have RS drop the files to a
> > local share and then have an app which stripped out the html part before
> > sending the message on.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "David Zeng" <dzeng@.pembrooke.com> wrote in message
> > news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > > Hi, guys,
> > >
> > > I have an application which only accept Plain/Text format email, but
the
> > > email sent out by Reporting Services Email Delivery was wrap in HTML
> > format.
> > > Is it possible to configure Email Delivery to send out Plain/Text
format
> > > message?
> > >
> > > Many thanks!
> > > David Zeng
> > >
> > >
> >
> >
>

how to configure database settings from config file?

Hi guys,
I am very new to Reporting Services, so please bear with me here. I am
curious about how we can configure the database server, database name, and
login data using data from a properties file so that when Reporting Services
and the Report Server start up, they have all the data they need to connect
to the appropriate database. Has anyone tried this yet? If so how would it
be possible?
Thanks a bunch,
Carolyn
cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.comPlease take a look at RSConfig.exe tool. You should be able to provide these
settings as command line parameters to this tool.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Carolyn Vo" <cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com> wrote in
message news:OYdWxHGhEHA.3320@.TK2MSFTNGP11.phx.gbl...
> Hi guys,
> I am very new to Reporting Services, so please bear with me here. I am
> curious about how we can configure the database server, database name, and
> login data using data from a properties file so that when Reporting
Services
> and the Report Server start up, they have all the data they need to
connect
> to the appropriate database. Has anyone tried this yet? If so how would
it
> be possible?
> Thanks a bunch,
> Carolyn
> cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com
>|||You may find the following posting useful:
http://prologika.com/blog/archive/2004/08/20/161.aspx
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
---
"Carolyn Vo" <cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com> wrote in
message news:OYdWxHGhEHA.3320@.TK2MSFTNGP11.phx.gbl...
> Hi guys,
> I am very new to Reporting Services, so please bear with me here. I am
> curious about how we can configure the database server, database name, and
> login data using data from a properties file so that when Reporting
Services
> and the Report Server start up, they have all the data they need to
connect
> to the appropriate database. Has anyone tried this yet? If so how would
it
> be possible?
> Thanks a bunch,
> Carolyn
> cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com
>

How to clear all database connection using SQLDMO?

Hello guys! I'm using SQLDMO to restore a database backup. The problem is, the program cannot restore the backup if there are active connections using the database. Is there are way to clear all database connections using SQLDMO (or other libraries)? Thanks in advance

You could create a storedproc in master that kills any existing spids per db, then run that proc from DMO as part of the restore job.. something like this

CREATE procedure sp_KillOldspids

@.dbName varchar (30) = NULL
AS

set nocount on

declare @.currentspid int,@.cmdstring varchar(30)

dECLARE OPENSPIDS CURSOR FOR SELECT [SPID] from [MASTER].[DBO].[SYSPROCESSES] WHERE DBID = db_id (@.dbName)
OPEN OPENSPIDS

FETCH NEXT FROM OPENSPIDS INTO @.CURRENTSPID

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.cmdString = 'kill ' + convert(varchar(10), @.CURRENTspid)
IF @.@.SPID <> @.CURRENTSPID
BEGIN
EXEC (@.cmdString)
END
FETCH NEXT FROM OPENSPIDS INTO @.CURRENTSPID

END

CLOSE CURRENTSPIDS
DEALLOCATE CURRENTSPIDS

hopes this helps.