generating Excel file every morning

Hi, I started playing with reporting services, followed the first 3 lessons
int the walkthrough, I find it a very interesting tool.
When viewing a report in IE, we have the possibility to export to excel
file. I woudl like to do a dynamic report, let's say generated on the fly at
6 o'clock morning and send it by mail as attachment. Is it possible with
reportin g services? if yes, any good articles or tutorials somewhere
Thanks for your helpYes, this is possible. Look for subscriptions and scheduling in the BOL.
You can also start here with the How Tos:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_overview_v1_46r7.asp
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"SalamElias" <eliassal@.online.nospam> wrote in message
news:19540F06-2F01-466E-A93E-28C19A763BA3@.microsoft.com...
> Hi, I started playing with reporting services, followed the first 3
lessons
> int the walkthrough, I find it a very interesting tool.
> When viewing a report in IE, we have the possibility to export to excel
> file. I woudl like to do a dynamic report, let's say generated on the fly
at
> 6 o'clock morning and send it by mail as attachment. Is it possible with
> reportin g services? if yes, any good articles or tutorials somewhere
> Thanks for your help
>|||Hi SalamElias,
I come across with the same situation as you mentioned in this post. Any
comment or idea you could provide? Could you share with me the
approaches/methods you taken for the task? Thanks a lot
"SalamElias" wrote:
> Hi, I started playing with reporting services, followed the first 3 lessons
> int the walkthrough, I find it a very interesting tool.
> When viewing a report in IE, we have the possibility to export to excel
> file. I woudl like to do a dynamic report, let's say generated on the fly at
> 6 o'clock morning and send it by mail as attachment. Is it possible with
> reportin g services? if yes, any good articles or tutorials somewhere
> Thanks for your help
>

generating excel file automatically every morning

Hi, I started playing with reporting services, followed the first 3 lessons
int the walkthrough, I find it a very interesting tool.
When viewing a report in IE, we have the possibility to export to excel
file. I woudl like to do a dynamic report, let's say generated on the fly at
6 o'clock morning and sent by mail as attachment. Is it possible with
reportin g services? if yes, any good articles or tutorials somewhere
Thanks for your help
What do you mean by dynamic?
You can schedule a report to run by using the Subscriptions "tab."
You can drop the report on a file share or you can email it.
By the way, you might want to address future questions related to Reporting
Services to the reporting services newsgroup
microsoft.public.sqlserver.reportingsvcs
Keith
"SalamElias" <eliassal@.online.nospam> wrote in message
news:59042B1C-ABC6-43D0-B0F7-BA82E442D298@.microsoft.com...
> Hi, I started playing with reporting services, followed the first 3
lessons
> int the walkthrough, I find it a very interesting tool.
> When viewing a report in IE, we have the possibility to export to excel
> file. I woudl like to do a dynamic report, let's say generated on the fly
at
> 6 o'clock morning and sent by mail as attachment. Is it possible with
> reportin g services? if yes, any good articles or tutorials somewhere
> Thanks for your help
|||By dynamic I mean, the report is executed for example 5 times, each time a
where clause containing a different value in order to generate 5 different
reports(I have to send an email for each provider with product I have sold
belonging to him)
In the doc on line, I saw we can generate we can have 2 datasets, the result
in the main table can be changed according to a parameter.
Thanks for your help. Next question I will post to it to the appropriate group
"Keith Kratochvil" wrote:

> What do you mean by dynamic?
> You can schedule a report to run by using the Subscriptions "tab."
> You can drop the report on a file share or you can email it.
> By the way, you might want to address future questions related to Reporting
> Services to the reporting services newsgroup
> microsoft.public.sqlserver.reportingsvcs
> --
> Keith
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:59042B1C-ABC6-43D0-B0F7-BA82E442D298@.microsoft.com...
> lessons
> at
>
|||If you have the Enterprise Edition of RS, use a data driven subscription for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:44BA40B6-897F-4963-998B-9A5E137664A5@.microsoft.com...[vbcol=seagreen]
> By dynamic I mean, the report is executed for example 5 times, each time a
> where clause containing a different value in order to generate 5 different
> reports(I have to send an email for each provider with product I have sold
> belonging to him)
> In the doc on line, I saw we can generate we can have 2 datasets, the result
> in the main table can be changed according to a parameter.
> Thanks for your help. Next question I will post to it to the appropriate group
> "Keith Kratochvil" wrote:

generating database schema thru command line

In informix there is a tool called dbschema which can generate a sql file
to create a database fully. I am aware that in the Enterprise Manager one
can do it easily. However i need the same functionality via command line
so that we can plug the command in the build tool.
TIAEM does it using sql-dmo, take a look a this api.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_g_11rm.asp
AMB
"rkusenet" wrote:
> In informix there is a tool called dbschema which can generate a sql file
> to create a database fully. I am aware that in the Enterprise Manager one
> can do it easily. However i need the same functionality via command line
> so that we can plug the command in the build tool.
> TIA
>|||You can also invoke the functionality of the EM wizards using SQL-NS API.
Look for SQL-NS constant SQLNS_CmdID_GENERATE_SCRIPTS and SQL-NS method
ExecuteCommandByID.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlns/ns_ref_78z5.asp
AMB
"Alejandro Mesa" wrote:
> EM does it using sql-dmo, take a look a this api.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqldmo/dmoref_m_g_11rm.asp
>
> AMB
> "rkusenet" wrote:
> > In informix there is a tool called dbschema which can generate a sql file
> > to create a database fully. I am aware that in the Enterprise Manager one
> > can do it easily. However i need the same functionality via command line
> > so that we can plug the command in the build tool.
> >
> > TIA
> >|||I've listed some options here:
http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"rkusenet" <usenet.rk@.gmail.com> wrote in message news:3d9nmvF6rhk35U1@.individual.net...
> In informix there is a tool called dbschema which can generate a sql file to create a database
> fully. I am aware that in the Enterprise Manager one can do it easily. However i need the same
> functionality via command line
> so that we can plug the command in the build tool.
> TIA|||Tibor,
Does SCPTXFR.EXE come with SQL Server 2000 also?
AMB
"rkusenet" wrote:
> In informix there is a tool called dbschema which can generate a sql file
> to create a database fully. I am aware that in the Enterprise Manager one
> can do it easily. However i need the same functionality via command line
> so that we can plug the command in the build tool.
> TIA
>|||you talked about doing a build, I'm wondering if this is part of your daily
or at least scheduled build process?
You may want to save yourself all the time and effort of re-inventing the
wheel...
http://www.dbghost.com
Build, compare, deploy.
"rkusenet" wrote:
> In informix there is a tool called dbschema which can generate a sql file
> to create a database fully. I am aware that in the Enterprise Manager one
> can do it easily. However i need the same functionality via command line
> so that we can plug the command in the build tool.
> TIA
>|||> Does SCPTXFR.EXE come with SQL Server 2000 also?
Seems so. In the upgrade directory :-).
C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:649EF740-EF67-4618-A363-B765823989DC@.microsoft.com...
> Tibor,
> Does SCPTXFR.EXE come with SQL Server 2000 also?
>
> AMB
> "rkusenet" wrote:
>> In informix there is a tool called dbschema which can generate a sql file
>> to create a database fully. I am aware that in the Enterprise Manager one
>> can do it easily. However i need the same functionality via command line
>> so that we can plug the command in the build tool.
>> TIA|||Thanks!!!
"Tibor Karaszi" wrote:
> > Does SCPTXFR.EXE come with SQL Server 2000 also?
> Seems so. In the upgrade directory :-).
> C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
> news:649EF740-EF67-4618-A363-B765823989DC@.microsoft.com...
> > Tibor,
> >
> > Does SCPTXFR.EXE come with SQL Server 2000 also?
> >
> >
> > AMB
> >
> > "rkusenet" wrote:
> >
> >> In informix there is a tool called dbschema which can generate a sql file
> >> to create a database fully. I am aware that in the Enterprise Manager one
> >> can do it easily. However i need the same functionality via command line
> >> so that we can plug the command in the build tool.
> >>
> >> TIA
> >>
>
>

Generate Store Procedure ?

I have a table in database !
I want to generate store procedure from that table by using tool of SQL server 2000( which include some statements : insert,delete, ....)
how can i do that ? thank you very much .

Try the link below and test drive the tool it was created by the best T-SQL programmers before the BMC bought them. Hope this helps.

http://www.bmc.com/products/proddocview/0,,0_0_0_8739,00.html

Generate SQL script and columns order

I am using Generate SQL script or scptxft tool for generartion creation
script for my database.
But unfortunally this script generates order for columns in creating
table not saticfying me. I need to have order exactly like I see in
Enterprise Manager, but the default order is other...
Can anybode give an advise how solve this problem?
Hi,
I think you can not.
Try doing a reverse engineering if you have Erwin data modeller or else you
have to arrange manually.
Thanks
Hari
SQL Server MVP
"Sergi Adamchuk" <adamchuk@.gmail.com> wrote in message
news:1126006864.707536.128230@.o13g2000cwo.googlegr oups.com...
>I am using Generate SQL script or scptxft tool for generartion creation
> script for my database.
> But unfortunally this script generates order for columns in creating
> table not saticfying me. I need to have order exactly like I see in
> Enterprise Manager, but the default order is other...
> Can anybode give an advise how solve this problem?
>
|||Sergi Adamchuk wrote:
> I am using Generate SQL script or scptxft tool for generartion
> creation script for my database.
> But unfortunally this script generates order for columns in creating
> table not saticfying me. I need to have order exactly like I see in
> Enterprise Manager, but the default order is other...
> Can anybode give an advise how solve this problem?
I just did a little digging into the SQL EM Generate SQL Scripts and the
Design Table functions to see how they were querying the list of columns
in the tables for display.
It appears they are both using the sp_MShelpcolumns system procedure
with the OrderBy column specified as 'id'. With that parameter, you
should be seeing the order of the columns as they are ordered by their
ColID in syscolumns.
I tested on SQL Server 2000 SP4 (Server and Client Tools). If you are
not seeing the same result from those two functions, can you tell me:
1- Are you running SP4 on the server and/or the client
2- What do you see as the column order when you query the syscolumns
table for the table in question and order by the ColID column? Which
version of the output is correct and which is incorrect?
You can use:
Select name from syscolumns where id = object_id('<table_name>') order
by colid
David Gugick
Quest Software
www.imceda.com
www.quest.com

Generate Scripts without the Collate statement

using SQL 2K
Just wondering if it is possible to generate scripts without a COLLATE
statement included from EM (I don't see how) or a tool which will allow me t
o
do it.
Currently I simply manually remove all Collate statements after the script
file is created.
KevinI believe there is a script options setting that you can use from EM to do
some global tailoring of your results.
You can set the Formatting Option "Only script 7.0 compatible features"
which will eliminate collation, but it will also drop UDFs and other SQL
2000 features.
RLF
"kevin" <kevin@.discussions.microsoft.com> wrote in message
news:4AFB6BC9-3FC1-41FD-B07E-BB43DD6285C6@.microsoft.com...
> using SQL 2K
> Just wondering if it is possible to generate scripts without a COLLATE
> statement included from EM (I don't see how) or a tool which will allow me
> to
> do it.
> Currently I simply manually remove all Collate statements after the script
> file is created.
> Kevin|||Rusell,
Since I am only interested in in Tables, that actually does the trick.
Thanks.
kevin.
"Russell Fields" wrote:

> I believe there is a script options setting that you can use from EM to do
> some global tailoring of your results.
> You can set the Formatting Option "Only script 7.0 compatible features"
> which will eliminate collation, but it will also drop UDFs and other SQL
> 2000 features.
> RLF
> "kevin" <kevin@.discussions.microsoft.com> wrote in message
> news:4AFB6BC9-3FC1-41FD-B07E-BB43DD6285C6@.microsoft.com...
>
>|||On Thu, 9 Mar 2006 07:30:31 -0800, kevin wrote:

>using SQL 2K
>Just wondering if it is possible to generate scripts without a COLLATE
>statement included from EM (I don't see how) or a tool which will allow me
to
>do it.
Hi Kevin,
You can't control this from EM, but you can control it if you use Query
Analyser and the Object Explorer (hit F8 while in QA) to script.
In QA, click Tools / Options / Script. Then check or unchekc the option
"Do not script the collation if source is an instance of SQL Server
version 7.0 or later".
Hugo Kornelis, SQL Server MVP|||Hugo,
After all these years, I never noticed that. Thanks
Kevin
"Hugo Kornelis" wrote:

> On Thu, 9 Mar 2006 07:30:31 -0800, kevin wrote:
>
> Hi Kevin,
> You can't control this from EM, but you can control it if you use Query
> Analyser and the Object Explorer (hit F8 while in QA) to script.
> In QA, click Tools / Options / Script. Then check or unchekc the option
> "Do not script the collation if source is an instance of SQL Server
> version 7.0 or later".
> --
> Hugo Kornelis, SQL Server MVP
>

Generate Scripts for existing records in a table

I can use Generate Scripts tool to get scripts for tables, procedures etc. But now I have records in tables which I want all "Insert into ..." queries from sqlserver. My co-worker said I have to manually type all these lines for my data.sql. Do I have to? I guess there must be a way...

Thanks,

LiliTry using DTS (Data Transformation services) which will allow you to recreate and / or rename the table and also lets you populate with original data.

The fastest way to recreate tables as you can recreate your entire database if you want to.

:p|||DTS is awsome :) but I need to deliver data.sql (of course with all other *.sql files) to the client so that they can install and drop database any time they mess it up :P|||It sounds like you want to give the client some base data. Could you not just BCP the data out into files and then create a command file that would truncate the tables and then BCP the data back in?

Or do you need to create the INSERT statements?
Example:INSERT myTbl (col1,col2,col3) VALUES ('xxx','aaa',123)|||Yes! base data. I need to insert statements. I just typed up some, but I'm wondering whether I can use a tool to make life easier :)

and guru, could you read my post about "Create My Own Log (for functionality) "? Thank you!

Lili|||Following sql statement will help you to generate INSERT STATEMENT for your base table

Table Name : BaseTable
fields
BName varchar(20)
dt smalldatetime
val int

select 'Insert into BaseTable VALUES (' + '''' + bname + '''' + ','
+ '''' + convert(varchar(10),dt,101) + '''' + ','
+ '''' + convert(varchar(10),val) + '''' + ')'
from basetable

handle NULL part by using ISNULL function

All the best|||Wow! Good idea. Silly me :p Thanks,

Lili|||You know sometimes I get carried away. I wrote a stored procedure that will generate all the INSERT statements for a given table. I've attached the proc, it worked for me on 2000.

Example on pubs
exec usp_CreateInsert discounts

OUTPUT:
INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount)
VALUES ('Initial Customer',NULL,NULL,NULL,10.50)
INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount)
VALUES ('Volume Discount',NULL,100,1000,6.70)
INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount)
VALUES ('Customer Discount','8042',NULL,NULL,5.00)|||//@.@. where is the stored procedure?|||It must have stoppped me because the extension of the file was '.sql', so I had to change it to '.txt'|||wow, you are real sql guru //admire

thank you for your help and sharing :)

Have a nice weekend!

Lili

Generate 2k compatible script of 2k5 DB?

Does anyone know of a tool that will generate backwards compatible
scripts of an SQL Server 2005 database (so that it can be run on SQL
Server 2000 to create the DB and add the data)? I have views, stored
proc's, triggers, a function, and some roles that need to go with the
tables and their data as well. I've tried generating a script from SQL
Server management studio, but there are tons of errors when I try to
run it on a 2k server, even after I fix the syntax ones. There *has*
to be an easier way of doing this. I apologize if this is something
that is very well known, but this is the first time I've had to do it,
and I haven't found anything that looked like it would do it by
searching.
Thanks,
Oliver Garraux
There is an option in the script wizard (script for server version) to
generate 2000 compatible code that you must check. But if you used some of
the 2005 features you may not be able to do this completely.
Andrew J. Kelly SQL MVP
"Oliver Garraux" <olrbengax@.gmail.com> wrote in message
news:1168220255.369875.244510@.s34g2000cwa.googlegr oups.com...
> Does anyone know of a tool that will generate backwards compatible
> scripts of an SQL Server 2005 database (so that it can be run on SQL
> Server 2000 to create the DB and add the data)? I have views, stored
> proc's, triggers, a function, and some roles that need to go with the
> tables and their data as well. I've tried generating a script from SQL
> Server management studio, but there are tons of errors when I try to
> run it on a 2k server, even after I fix the syntax ones. There *has*
> to be an easier way of doing this. I apologize if this is something
> that is very well known, but this is the first time I've had to do it,
> and I haven't found anything that looked like it would do it by
> searching.
> Thanks,
> Oliver Garraux
>
|||Thanks a bunch, I don't know why I didn't see that option in there. It
worked great for everything except a trigger and a view, and those
worked just fine with copying and pasting stuff. I appreciate your
help.
Oliver Garraux
Andrew J. Kelly wrote:
> There is an option in the script wizard (script for server version) to
> generate 2000 compatible code that you must check. But if you used some of
> the 2005 features you may not be able to do this completely.
> --
> Andrew J. Kelly SQL MVP

Generate 2k compatible script of 2k5 DB?

Does anyone know of a tool that will generate backwards compatible
scripts of an SQL Server 2005 database (so that it can be run on SQL
Server 2000 to create the DB and add the data)? I have views, stored
proc's, triggers, a function, and some roles that need to go with the
tables and their data as well. I've tried generating a script from SQL
Server management studio, but there are tons of errors when I try to
run it on a 2k server, even after I fix the syntax ones. There *has*
to be an easier way of doing this. I apologize if this is something
that is very well known, but this is the first time I've had to do it,
and I haven't found anything that looked like it would do it by
searching.
Thanks,
Oliver GarrauxThere is an option in the script wizard (script for server version) to
generate 2000 compatible code that you must check. But if you used some of
the 2005 features you may not be able to do this completely.
--
Andrew J. Kelly SQL MVP
"Oliver Garraux" <olrbengax@.gmail.com> wrote in message
news:1168220255.369875.244510@.s34g2000cwa.googlegroups.com...
> Does anyone know of a tool that will generate backwards compatible
> scripts of an SQL Server 2005 database (so that it can be run on SQL
> Server 2000 to create the DB and add the data)? I have views, stored
> proc's, triggers, a function, and some roles that need to go with the
> tables and their data as well. I've tried generating a script from SQL
> Server management studio, but there are tons of errors when I try to
> run it on a 2k server, even after I fix the syntax ones. There *has*
> to be an easier way of doing this. I apologize if this is something
> that is very well known, but this is the first time I've had to do it,
> and I haven't found anything that looked like it would do it by
> searching.
> Thanks,
> Oliver Garraux
>|||Thanks a bunch, I don't know why I didn't see that option in there. It
worked great for everything except a trigger and a view, and those
worked just fine with copying and pasting stuff. I appreciate your
help.
Oliver Garraux
Andrew J. Kelly wrote:
> There is an option in the script wizard (script for server version) to
> generate 2000 compatible code that you must check. But if you used some of
> the 2005 features you may not be able to do this completely.
> --
> Andrew J. Kelly SQL MVP

Generate 2k compatible script of 2k5 DB?

Does anyone know of a tool that will generate backwards compatible
scripts of an SQL Server 2005 database (so that it can be run on SQL
Server 2000 to create the DB and add the data)? I have views, stored
proc's, triggers, a function, and some roles that need to go with the
tables and their data as well. I've tried generating a script from SQL
Server management studio, but there are tons of errors when I try to
run it on a 2k server, even after I fix the syntax ones. There *has*
to be an easier way of doing this. I apologize if this is something
that is very well known, but this is the first time I've had to do it,
and I haven't found anything that looked like it would do it by
searching.
Thanks,
Oliver GarrauxThere is an option in the script wizard (script for server version) to
generate 2000 compatible code that you must check. But if you used some of
the 2005 features you may not be able to do this completely.
Andrew J. Kelly SQL MVP
"Oliver Garraux" <olrbengax@.gmail.com> wrote in message
news:1168220255.369875.244510@.s34g2000cwa.googlegroups.com...
> Does anyone know of a tool that will generate backwards compatible
> scripts of an SQL Server 2005 database (so that it can be run on SQL
> Server 2000 to create the DB and add the data)? I have views, stored
> proc's, triggers, a function, and some roles that need to go with the
> tables and their data as well. I've tried generating a script from SQL
> Server management studio, but there are tons of errors when I try to
> run it on a 2k server, even after I fix the syntax ones. There *has*
> to be an easier way of doing this. I apologize if this is something
> that is very well known, but this is the first time I've had to do it,
> and I haven't found anything that looked like it would do it by
> searching.
> Thanks,
> Oliver Garraux
>|||Thanks a bunch, I don't know why I didn't see that option in there. It
worked great for everything except a trigger and a view, and those
worked just fine with copying and pasting stuff. I appreciate your
help.
Oliver Garraux
Andrew J. Kelly wrote:
> There is an option in the script wizard (script for server version) to
> generate 2000 compatible code that you must check. But if you used some of
> the 2005 features you may not be able to do this completely.
> --
> Andrew J. Kelly SQL MVP