Is it possible to name the Excel sheets of an exported report ?

Hello,
Is there a way to name Excel sheets when I export a report ?
My report generate several sheets and I would like to name each one.
Thanks for your help.Unfortunately, this is not possible in this release of Reporting
Services.
Mike|||It gets asked for a lot. I'm still amazed that they haven't been willing to
add this feature. Many people have been asking for it since day one of RS
going live.
"Bassist695" wrote:
> Unfortunately, this is not possible in this release of Reporting
> Services.
> Mike
>sql

Is it possible to generate alter Table statements using SMO

Hi

I'm trying to modify existing tables in a database.

How can I create alter Table scripts using SMO/DMO

Thank you

Yep, you can use the following to either execute and capture, just execute (which is the default) or just capture the executed commands:

Server s = new Server(".");

s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteAndCaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteSql

//s.ConnectionContext.CapturedSql.Text; //Get the Text

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks again Jens

I'm trying the follwong code

Server server1 = new Server(".");

Database db= server1.Databases["master"];

server1.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

foreach (Table Tbl in db.Tables)

{

tabl.Alter ();

}

db1.Refresh();

//writing to a file

writeToFile(server1.ConnectionContext.CapturedSql.Text, "alter", "tables");

But it is not generating Alter statments.

But if I use Create(), in place of alter(), it's generating Create statments.

|||Hi,
if you do not change anything, what are you supposed to see in the ALTER script :-) ?

In this sample I added a column to the table resulting in a script with an ALTER Script and an ADD column command.

Server s = new Server(".");

s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql;

Table t = s.Databases["SMOTest"].Tables["TestTable"];

t.Columns.Add(new Column(t,"SomeSMOTest",DataType.DateTime));

t.Alter();

foreach (string st in s.ConnectionContext.CapturedSql.Text)

{

Console.WriteLine(st);

}

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens

Got it.

while comparing a table in one database to other table (identical) in other database,

if the Source table has some modified(altered) columns and need to be modified in the target table.

How to solve this problem.Any Idea.

I generated the alter scripts manually for each column.

Like

ALTER TABLE [dbo].[wo]

ADD [requested-time] varchar (8 ) NULL

Thank you

|||You will have to do this manually. Load the two schemas and compare the columns (if you just want to check the columns) with each other. Change the columns appropiately with SMO and get the script from the Context. if you want an integrated tool which can do this on its own use Visual Studio for database professionals, this does have a comparer and script generator for keeping the databases in sync.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks alot Jens.

I'll try for this

Is it possible to execute DTS in SP?

Hi, i have a DTS then generate a CSV file, is it possible to execute the DTS
within a stored procedure?
Moreover, is it possible to dynamic change the current database in SQL Query
Analyzer by execute a Transact-SQL? As i know,
Use DB1 <== this command can change the current database
but is it possible to do the following:
declare @.dbName as char(255)
set @.dbName = 'DB2'
use @.dbName <== currently, this is not valid
Many thanks
MartinIndirectly yes,
You can execute DTS from a console window meaning it's command based. Now
SQL also have xp_cmdshell to execute commands in console.
So if you combine xp_cmdshell and tell it to execute dtsrun you should get
it working.
Have a look at the dtsrun, and xp_cmdshell in books online(sql help)
Hope it's a pointer in the right direction.
"Atenza" wrote:

> Hi, i have a DTS then generate a CSV file, is it possible to execute the D
TS
> within a stored procedure?
> Moreover, is it possible to dynamic change the current database in SQL Que
ry
> Analyzer by execute a Transact-SQL? As i know,
> Use DB1 <== this command can change the current database
> but is it possible to do the following:
> declare @.dbName as char(255)
> set @.dbName = 'DB2'
> use @.dbName <== currently, this is not valid
>
> Many thanks
> Martin
>
>|||> is it possible to execute the DTS
> within a stored procedure?
http://www.sqldts.com/default.aspx?210

> is it possible to dynamic change the current database in SQL Query
> Analyzer by execute a Transact-SQL?
It's easier to do this in DTS with a parameterized SQL task or Dynamic
Properties task. In Transact SQL you would have to use Dynamic SQL (not
recommended) and wrap all your code in an EXEC statatement.
David Portas
SQL Server MVP
--|||Thanks!!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:A287DCD5-FC0C-48E6-8C6F-0A362941D6AD@.microsoft.com...
> http://www.sqldts.com/default.aspx?210
>
> It's easier to do this in DTS with a parameterized SQL task or Dynamic
> Properties task. In Transact SQL you would have to use Dynamic SQL (not
> recommended) and wrap all your code in an EXEC statatement.
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks!!
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:469E6F3C-B4A6-41B0-A041-1F43E6142197@.microsoft.com...
> Indirectly yes,
> You can execute DTS from a console window meaning it's command based. Now
> SQL also have xp_cmdshell to execute commands in console.
> So if you combine xp_cmdshell and tell it to execute dtsrun you should get
> it working.
> Have a look at the dtsrun, and xp_cmdshell in books online(sql help)
> Hope it's a pointer in the right direction.
> "Atenza" wrote:
>
DTS
Query

is case statement the only way

Hi

I need to generate a SQL report like below,its basically calculating the count of students

For

District Level

then for

Region Level

then for

Each School under a Region

Like the Display Below

District summary

Total

Male

Femal

Indian

White

Asian

--

--

--

Type1

22

33

22

11

11

11

23

11

13

Type2

2

…6

…7

;;;;

13

14

Region 1

Region 2...................Region 8

Each School

School1....School 15

Do I have to have

case for each Type for each Race

and then

for Each Levels

of

District

Region

And 500 Schools?

Please Help

Thanks

Could you post your DDL statements and some sample data? This will help us understand your problem and hopefully provide a solution....
|||

From what I understand you may be looking for the PIVOT operator which is available in SQL 2005.

http://technet.microsoft.com/en-us/library/ms177410.aspx

|||

SELECT distinct SCHOOL_REGION,SCHOOL_NUMBER,ETHNICITY,SCH.S_SCHL_NAME,

INTV.Intervention_ ID,

case

INTV.Intervention_ ID when '1' then

(CASE STDM.ETHNICITY when 'A'

then count(STDM.student_id )

END )as Asian,

(CASE STDM.ETHNICITY when 'B'

then count(STDM.student_id )

END )as Black,

(CASE STDM.ETHNICITY when 'H'

then count(STDM.student_id )

END )as Hispanic,

(CASE STDM.ETHNICITY when 'I'

then count(STDM.student_id )

END )as Indian,

(CASE STDM.ETHNICITY when 'M'

then count(STDM.student_id )

END )as Multiracial,

(CASE STDM.ETHNICITY when 'W'

then count(STDM.student_id )

END )as White

case

INTV.Intervention_ ID when '2' then

(CASE STDM.ETHNICITY when 'A'

then count(STDM.student_id )

END )as Asian,

(CASE STDM.ETHNICITY when 'B'

then count(STDM.student_id )

END )as Black,

(CASE STDM.ETHNICITY when 'H'

then count(STDM.student_id )

END )as Hispanic,

(CASE STDM.ETHNICITY when 'I'

then count(STDM.student_id )

END )as Indian,

(CASE STDM.ETHNICITY when 'M'

then count(STDM.student_id )

END )as Multiracial,

(CASE STDM.ETHNICITY when 'W'

then count(STDM.student_id )

END )as White

........

-

-

case

INTV.Intervention_ ID when....... '14' then

(CASE STDM.ETHNICITY when 'A'

then count(STDM.student_id )

END )as Asian,

(CASE STDM.ETHNICITY when 'B'

then count(STDM.student_id )

END )as Black,

(CASE STDM.ETHNICITY when 'H'

then count(STDM.student_id )

END )as Hispanic,

(CASE STDM.ETHNICITY when 'I'

then count(STDM.student_id )

END )as Indian,

(CASE STDM.ETHNICITY when 'M'

then count(STDM.student_id )

END )as Multiracial,

(CASE STDM.ETHNICITY when 'W'

then count(STDM.student_id )

END )as White

FROM STUDENT_DEMOGRAPHIC STDM

left join school_location SCH on STDM.school_number = SCH.S_SCHOOL_NUM

left join Meeting MTNG on STDM.student_id = MTNG.student_id

left join Meeting_Intervention MTGI on MTNG.Meeting_ID = MTGI.Meeting_ID

left join Intervention INTV on MTGI.Intervention_ID = INTV.Intervention_ID

where INTV.Intervention_ID not in ('')

group by SCHOOL_REGION,STDM.school_number,SCH.S_SCHL_NAME,Ethnicity

,INTV.Intervention_ID

ORDER BY SCHOOL_REGION,STDM.school_number,SCH.S_SCHL_NAME,Ethnicity

,INTV.Intervention_ID

P.S ...here the inner case statement is for the horizontal column names in the report

and I need the totals for the Type 1 ..type2 ...type 14 which is my vertical column in my report ( the INTV.Intervention_ID is this field) which i use in outer case statement above