Grid display: file

Showing posts with label file. Show all posts

Thursday, March 29, 2012

Get Available free space for data file

When I go to Shrink a file, the screen shows me the currently allocated and
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id => db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>> When I go to Shrink a file, the screen shows me the currently allocated
>> and
>> the free space available for the selected database. Is there a view or
>> system stored procedure that I can use to retrieve this information?
>> I am working with SQL 2005. I have been playing with the sp_spaceused
>> stored procedure but can't seem to get just the data file info.
>> Thanks,
>>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>> Hello,
>> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the
>> database
>> you need info.
>> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
>> UsedExtents int, Name sysname, FileName nchar(520))
>> Insert #tmpspc EXEC ('dbcc showfilestats')
>>
>> SELECT
>> s.name AS [Name],
>> s.physical_name AS [FileName],
>> s.size * CONVERT(float,8) AS [Size],
>> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
>> s.file_id AS [ID]
>> FROM
>> sys.filegroups AS g
>> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =>> db_id()
>> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
>> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
>> WHERE
>> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
>> ORDER BY
>> [ID] ASC
>> Thanks
>> Hari
>> "Tim Kelley" wrote:
>> When I go to Shrink a file, the screen shows me the currently allocated
>> and
>> the free space available for the selected database. Is there a view or
>> system stored procedure that I can use to retrieve this information?
>> I am working with SQL 2005. I have been playing with the sp_spaceused
>> stored procedure but can't seem to get just the data file info.
>> Thanks,
>>
>

Get Available free space for data file

When I go to Shrink a file, the screen shows me the currently allocated and
the free space available for the selected database. Is there a view or
system stored procedure that I can use to retrieve this information?
I am working with SQL 2005. I have been playing with the sp_spaceused
stored procedure but can't seem to get just the data file info.
Thanks,Hello,
You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
you need info.
create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
UsedExtents int, Name sysname, FileName nchar(520))
Insert #tmpspc EXEC ('dbcc showfilestats')
SELECT
s.name AS [Name],
s.physical_name AS [FileName],
s.size * CONVERT(float,8) AS [Size],
CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
s.file_id AS [ID]
FROM
sys.filegroups AS g
INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id = db_id()
and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
WHERE
(CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
ORDER BY
[ID] ASC
Thanks
Hari
"Tim Kelley" wrote:

> When I go to Shrink a file, the screen shows me the currently allocated an
d
> the free space available for the selected database. Is there a view or
> system stored procedure that I can use to retrieve this information?
> I am working with SQL 2005. I have been playing with the sp_spaceused
> stored procedure but can't seem to get just the data file info.
> Thanks,
>
>|||Will this work with SQL 2000 also?
Thanks,
"Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...[vbcol=seagreen]
> Hello,
> You could use DBCC SHOWFILESTATS. Run the the below TSQL from the database
> you need info.
> create table #tmpspc (Fileid int, FileGroup int, TotalExtents int,
> UsedExtents int, Name sysname, FileName nchar(520))
> Insert #tmpspc EXEC ('dbcc showfilestats')
>
> SELECT
> s.name AS [Name],
> s.physical_name AS [FileName],
> s.size * CONVERT(float,8) AS [Size],
> CAST(tspc.UsedExtents*convert(float,64) AS float) AS [UsedSpace],
> s.file_id AS [ID]
> FROM
> sys.filegroups AS g
> INNER JOIN sys.master_files AS s ON (s.type = 0 and s.database_id =
> db_id()
> and (s.drop_lsn IS NULL)) AND (s.data_space_id=g.data_space_id)
> INNER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
> WHERE
> (CAST(cast(g.name as varbinary(256)) AS sysname)=N'PRIMARY')
> ORDER BY
> [ID] ASC
> Thanks
> Hari
> "Tim Kelley" wrote:
>|||No.
For SQL 2000; just enable the profiler and open the shrink file window from
enterprise manager. This will give the commands which executes
from backend.
Thanks
Hari
"Tim Kelley" <tkelley@.company.com> wrote in message
news:ex6Mn6qLHHA.4992@.TK2MSFTNGP04.phx.gbl...
> Will this work with SQL 2000 also?
> Thanks,
>
> "Hari Prasad" <HariPrasad@.discussions.microsoft.com> wrote in message
> news:7F25723E-2D5C-4C9B-AC1A-16254059521A@.microsoft.com...
>

Tuesday, March 27, 2012

get a file From FTP site

I have to download a file from a ftp site using either SQL/T-Sql or ActiveX script. I'm using SQL Server 2000. Please help
Thanks in advancehttp://www.nigelrivett.net/s_ftp_PutFile.html

thanks to nigelrivett|||I wanted to say that :).

Get "Do you want to save this file?" when trying to launch Report Builder

This may be a simple setup issue on my XP client, but whenever I click "Report Builder" I get a "Do you want to save this file?" dialog box and nothing happens.

I believe Reporting Services is installed correctly on my Win Server 2003 because when I click "Report Builder" on the server itself it does the proper click-once stuff (checks to see if the app is there and downloads it if isn't) and launched Report Builder. Also, all other Reporting Services functions are working fine on both the server and XP clients - its just launching "Report Builder" on the client that is wierd.

So is there anything special I have to do for an XP client? It does have SP2. Its also probably worth noting that the server is not in our corporate domain - it is it's own test workgroup. I have set up anonymous access to the server for the Windows XP clients to access for testing, and that seems to be working fine for running all other types of reports.

Any suggestions appreciated. Thanks!Do you have the .NET framework 2.0 installed on the client machine? The ClickOnce technology that report builder uses is installed as part of it.

Thanks
Tudor|||DOH! That did it! Thanks!!!

I was under the (mistaken) understanding that .net 2.0 was auto-installed on a client as part of the click-once process.

get % distribution of invalid data?

We've got the requirement where we need to load data (usually dirty of course) from a flat file, and for every column, if the value is invalid, I'm putting a -1 in the field as it flows through the data stream - no problem. (I'm converting all invalids to -1 so they'll go into my eventual int column)

My question though is - at the end of the data flow, is there any smooth way to capture the % invalid values in each column. An example: If my table is as follows...where the # 1 signifies "clean data" and -1 signifies dirty.

col1 col2 col3 col4

1 1 1 1

1 -1 -1 1

-1 1 -1 1

I would want my % distribution query/ssis result to return:

col1 col2 col3 col4

.5 .25 .75 0

I do not want to do a select/ssis process for each column. Any way to do this all in one shot?

You could write a script component to track the number of good and bad values, and output them to an asynchronous output. Or execute a SQL statement after the data flow to get the statistics.|||

jwelch wrote:
execute a SQL statement after the data flow to get the statistics.

That's what I would try first....just to keep it simpler

Gerating a xml file from oracle 9i in a asp program

Hi, is any of the user of that list works with Oracle9i ? because i need to generate xml file from a intranet asp site ... so i don't know where to find information ...
In fact, i want to make a xml file from a sql reqest from oracle in a asp program ...is someone did that ? how i can read a recorset if i use sqlxml of oracle ?
thanks in advance
cheers
jp
With SQL Server you could use the FOR XML clause to get the data out in XML
format. If you need help for the same, with Oracle, then please post this to
an Oracle newsgroup.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jean-Paul" <Jean-Paul@.discussions.microsoft.com> wrote in message
news:20812732-5D8B-4374-8383-763B56F688AD@.microsoft.com...
Hi, is any of the user of that list works with Oracle9i ? because i need to
generate xml file from a intranet asp site ... so i don't know where to find
information ...
In fact, i want to make a xml file from a sql reqest from oracle in a asp
program ...is someone did that ? how i can read a recorset if i use sqlxml
of oracle ?
thanks in advance
cheers
jp

Monday, March 26, 2012

Generating XML Schema from db tables - including field lengths

I'm working on a C# project that essentially features a dump of the
database (on SQL Server 2005) into an XML file, with schema.
Basically we want the users to be able to take this data anywhere, as
easily as is possible. I initially selected the information "For XML
Auto" etc. etc. but found that when I tested simple imports of that
XML with the likes of Access and Excel, those programs didn't take
well to the schema at all.
Switching gears, I queried the databases normally and did the XML
conversion in C# using the dataset.writeXML function with schema,
which generated schema that made Access and Excel much happier. The
only thing is that Access, upon creating tables based on the schema,
took the string fields to be text, rather than memo. (i.e.
varchar(255) instead of text) ... likewise, SQL Server, when
wrestling to create tables based on the schema, behaves similarly.
Mind you, when the tables are created beforehand, and just populated
with the XML data, that's fine, it works great.
I do realize that's how it's supposed to work, but I'm being asked to
generate a schema that includes maxLength for varchar fields -- even
though I doubt any program that would be importing this data would
even be able to read that from the schema and use it appropriately.
I'm hoping someone here can tell me there's a nicer way of doing that
than SELECTing FOR XML RAW and drawing up an XSLT.
Takers?Did you use the xmlschema directive on FOR XML AUTO or RAW?
Best regards
Michael
"Matthew Dunphy" <leviathant@.gmail.com> wrote in message
news:1172246553.987853.109810@.j27g2000cwj.googlegroups.com...
> I'm working on a C# project that essentially features a dump of the
> database (on SQL Server 2005) into an XML file, with schema.
> Basically we want the users to be able to take this data anywhere, as
> easily as is possible. I initially selected the information "For XML
> Auto" etc. etc. but found that when I tested simple imports of that
> XML with the likes of Access and Excel, those programs didn't take
> well to the schema at all.
> Switching gears, I queried the databases normally and did the XML
> conversion in C# using the dataset.writeXML function with schema,
> which generated schema that made Access and Excel much happier. The
> only thing is that Access, upon creating tables based on the schema,
> took the string fields to be text, rather than memo. (i.e.
> varchar(255) instead of text) ... likewise, SQL Server, when
> wrestling to create tables based on the schema, behaves similarly.
> Mind you, when the tables are created beforehand, and just populated
> with the XML data, that's fine, it works great.
> I do realize that's how it's supposed to work, but I'm being asked to
> generate a schema that includes maxLength for varchar fields -- even
> though I doubt any program that would be importing this data would
> even be able to read that from the schema and use it appropriately.
> I'm hoping someone here can tell me there's a nicer way of doing that
> than SELECTing FOR XML RAW and drawing up an XSLT.
> Takers?
>|||Both -- when I used AUTO, it worked great except that it doesn't
output the maxlength for the varchar fields. Everything else it does
is kind of magical.
When I use RAW, it outputs each column, but again, nothing about the
(3000) for a row that is varchar(3000).
To be specific, the select statement that comes closest to what I want
looks basically like this:
SELECT *
FROM table
WHERE id=@.id
FOR XML AUTO, ELEMENTS, XMLDATA
(I do this for about a dozen tables)
In the resulting schema, the ElementType nodes have attributes for
name, content, model, and dt:type... is there some way to also specify
maxLength, based off the table schema in SQL server, so that I can do
a simple query like this -- or do I just have to bite the bullet and
write the schemas manually?
Thanks!
--Matt Dunphy
On Feb 24, 1:42 am, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:
> Did you use the xmlschema directive on FOR XML AUTO or RAW?
> Best regards
> Michael
> "Matthew Dunphy" <leviath...@.gmail.com> wrote in message
> news:1172246553.987853.109810@.j27g2000cwj.googlegroups.com...
>
>
>
>|||Actually... never mind that last post, I think you've pointed me in
the right direction. (That's what I get for posting first thing in
the morning!) Thanks for your help.
On Feb 26, 9:14 am, "Matthew Dunphy" <leviath...@.gmail.com> wrote:
> Both -- when I used AUTO, it worked great except that it doesn't
> output the maxlength for the varchar fields. Everything else it does
> is kind of magical.
> When I use RAW, it outputs each column, but again, nothing about the
> (3000) for a row that is varchar(3000).
> To be specific, the select statement that comes closest to what I want
> looks basically like this:
> SELECT *
> FROM table
> WHERE id=@.id
> FOR XML AUTO, ELEMENTS, XMLDATA
> (I do this for about a dozen tables)
> In the resulting schema, the ElementType nodes have attributes for
> name, content, model, and dt:type... is there some way to also specify
> maxLength, based off the table schema in SQL server, so that I can do
> a simple query like this -- or do I just have to bite the bullet and
> write the schemas manually?
> Thanks!
> --Matt Dunphy
>

Generating XML Schema from db tables - including field lengths

I'm working on a C# project that essentially features a dump of the
database (on SQL Server 2005) into an XML file, with schema.
Basically we want the users to be able to take this data anywhere, as
easily as is possible. I initially selected the information "For XML
Auto" etc. etc. but found that when I tested simple imports of that
XML with the likes of Access and Excel, those programs didn't take
well to the schema at all.
Switching gears, I queried the databases normally and did the XML
conversion in C# using the dataset.writeXML function with schema,
which generated schema that made Access and Excel much happier. The
only thing is that Access, upon creating tables based on the schema,
took the string fields to be text, rather than memo. (i.e.
varchar(255) instead of text) ... likewise, SQL Server, when
wrestling to create tables based on the schema, behaves similarly.
Mind you, when the tables are created beforehand, and just populated
with the XML data, that's fine, it works great.
I do realize that's how it's supposed to work, but I'm being asked to
generate a schema that includes maxLength for varchar fields -- even
though I doubt any program that would be importing this data would
even be able to read that from the schema and use it appropriately.
I'm hoping someone here can tell me there's a nicer way of doing that
than SELECTing FOR XML RAW and drawing up an XSLT.
Takers?
Did you use the xmlschema directive on FOR XML AUTO or RAW?
Best regards
Michael
"Matthew Dunphy" <leviathant@.gmail.com> wrote in message
news:1172246553.987853.109810@.j27g2000cwj.googlegr oups.com...
> I'm working on a C# project that essentially features a dump of the
> database (on SQL Server 2005) into an XML file, with schema.
> Basically we want the users to be able to take this data anywhere, as
> easily as is possible. I initially selected the information "For XML
> Auto" etc. etc. but found that when I tested simple imports of that
> XML with the likes of Access and Excel, those programs didn't take
> well to the schema at all.
> Switching gears, I queried the databases normally and did the XML
> conversion in C# using the dataset.writeXML function with schema,
> which generated schema that made Access and Excel much happier. The
> only thing is that Access, upon creating tables based on the schema,
> took the string fields to be text, rather than memo. (i.e.
> varchar(255) instead of text) ... likewise, SQL Server, when
> wrestling to create tables based on the schema, behaves similarly.
> Mind you, when the tables are created beforehand, and just populated
> with the XML data, that's fine, it works great.
> I do realize that's how it's supposed to work, but I'm being asked to
> generate a schema that includes maxLength for varchar fields -- even
> though I doubt any program that would be importing this data would
> even be able to read that from the schema and use it appropriately.
> I'm hoping someone here can tell me there's a nicer way of doing that
> than SELECTing FOR XML RAW and drawing up an XSLT.
> Takers?
>
|||Both -- when I used AUTO, it worked great except that it doesn't
output the maxlength for the varchar fields. Everything else it does
is kind of magical.
When I use RAW, it outputs each column, but again, nothing about the
(3000) for a row that is varchar(3000).
To be specific, the select statement that comes closest to what I want
looks basically like this:
SELECT *
FROM table
WHERE id=@.id
FOR XML AUTO, ELEMENTS, XMLDATA
(I do this for about a dozen tables)
In the resulting schema, the ElementType nodes have attributes for
name, content, model, and dt:type... is there some way to also specify
maxLength, based off the table schema in SQL server, so that I can do
a simple query like this -- or do I just have to bite the bullet and
write the schemas manually?
Thanks!
--Matt Dunphy
On Feb 24, 1:42 am, "Michael Rys [MSFT]" <m...@.online.microsoft.com>
wrote:[vbcol=seagreen]
> Did you use the xmlschema directive on FOR XML AUTO or RAW?
> Best regards
> Michael
> "Matthew Dunphy" <leviath...@.gmail.com> wrote in message
> news:1172246553.987853.109810@.j27g2000cwj.googlegr oups.com...
>
>
|||Actually... never mind that last post, I think you've pointed me in
the right direction. (That's what I get for posting first thing in
the morning!) Thanks for your help.
On Feb 26, 9:14 am, "Matthew Dunphy" <leviath...@.gmail.com> wrote:
> Both -- when I used AUTO, it worked great except that it doesn't
> output the maxlength for the varchar fields. Everything else it does
> is kind of magical.
> When I use RAW, it outputs each column, but again, nothing about the
> (3000) for a row that is varchar(3000).
> To be specific, the select statement that comes closest to what I want
> looks basically like this:
> SELECT *
> FROM table
> WHERE id=@.id
> FOR XML AUTO, ELEMENTS, XMLDATA
> (I do this for about a dozen tables)
> In the resulting schema, the ElementType nodes have attributes for
> name, content, model, and dt:type... is there some way to also specify
> maxLength, based off the table schema in SQL server, so that I can do
> a simple query like this -- or do I just have to bite the bullet and
> write the schemas manually?
> Thanks!
> --Matt Dunphy
>

Generating XML Schema for SQL Server Table

how can persist schema for a sql server table into an xml file from .net application?

thanks

If you already loaded the structure in a dataset, you can just use the WriteXMLSchema Method on the dataset object.

http://msdn2.microsoft.com/en-us/library/system.data.dataset.writexmlschema.aspx

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Friday, March 23, 2012

generating text file

I have a table(say tblUserInfo) on SQL Server. What I like to do is a text file will be generated on the hard drive which SQL Server sits on when a new record is inserted into tblUserInfo. The content of the text file comes from the table. Is there any way we can go for doing that?I'm not 100% clear on what you want to do here, but it seems to me what you want to do could be achieved with a trigger which does a callout to a COM object which then manipulates the text file. this is an off-the-top-of-the-head solution and there may be a better way, but that would require me hitting the book and it's too early in the morning here for that!|||The help you gave me was much appreciated. Could you please let me know more detail about that when you have a chance?

Regards,

Kevin Jin

Generating table from xsd file in SQL Server 2005

Hi there,

I would like to create a table in SQL Server 2005 from an xsd file. Is this possible? From what I've read it seems possible but I haven't found the way to do it yet...

Thanks!

Craig

Hello,

You can use SQLXMLBulkload object and add annotations to the xsd schema file to map elements/attributes to tables/columns.

Read more about this here: http://msdn2.microsoft.com/en-us/library/ms171993(SQL.90).aspx.

You have to set SchemaGen property to true and Bulkload property to false.

Hope this helps.

Regards,

Monica Frintu

Generating SQL Scripts

Hi,
How can I generate a script file for all the tables in my database with the data. The script file when run should create all the tables with the constraints and also have the data inserted into them.
Thanks in advance
P.C. VaidyanathanYou can generate the schema easily by going to the Enterprise Manager and right clicking on the dababase name "All Tasks","Generate SQL Scripts". I don't know of anything to script inserting the data. Can you use DTS? Not only will it create the schema and load the data but it will generate all the files used to create the schema.|||I agree with Paul Young, DTS would be your best option to create the tables, load the data nad maybe even do aditional preprocessing to your tables.

This is how we do it on a daily basis with fairly large tables all the time. Realizing size is relative, I am talking about databases as small as a few thousand rows to ones as large as 200 million.

generating sql logins

I've been given an excel file that lists:
userID's,
Names
Password
expiary dates.
I need to convert this list into sql server logins. Can this be done via a tsql statement? if so how, and if it cant be done via a statement then what other way can it be done.

Many thanks in advance.

Air:

I would probably use SSIS for this; however, you probably can also put together a script that uses BULK INSERT to get this done. This will be more simple if you can just ram and jam because none of these logins pre-exist.

Dave

|||

use master

declare @.loginame sysname

set @.loginame=N'DOMAIN\user'

exec sp_grantlogin @.loginame

exec sp_addsrvrolemember @.loginame, sysadmin

... most probably, NOT sysadmin, but whatever you need for that particular John or Jane Doe...

Wednesday, March 21, 2012

generating script with SMO

I am taking my table names and colums from xml file and then generating the script and executing it.I have problem creating the script for incerting table with multiple colums.Maybe the problem is that I am using a for loop to get all column names from a array string.Has anyone idea why it is not working? When I try insert of a table without loop it is wirking!Here is the code :
Server server = new Server(); // Create table in my personal database Database db = server.Databases["new"]; // Make Sure Constraint is Scripted ScriptingOptions so = new ScriptingOptions(); so.IncludeHeaders = true; so.SchemaQualify = true; // Create In-Memory Table Table newTable = new Table(db, Names[0].ToString()); for (int i = 1; i < Names.Length; i++) { Column newColumn = new Column(newTable,Names Idea ); newTable.Columns.Add(newColumn); } foreach (string s in newTable.Script(so)) /// Error generating the script { myTableScript = myTableScript + s; } db.ExecuteNonQuery(myTableScript); //disconnect form Database connection.Close();

Could you please metion the full error you are getting.

Thanks,
Kuntal

|||I fixed the problem.I haven't set the column format and that's why the script was not correctly generated.The loop is functioning now ok. Here is the edit line :Column newColumn = new Column(newTable, attr.Name, DataType.NVarChar(50));Now I can easily take the table and column names from the xml file and directly put them without using array strings.Thanks for yor time :) Have a nice day !

Generating Script

Hi,

I want generated script file of all data base objects as well as i want to keep the generated script file in CVS which will reflect the changes.

Thanks in Advance

Sathik, Take a look at the Generate Script wizard in Management Studio. You can read more about it in Books Online: http://msdn2.microsoft.com/en-us/library/ms178078(SQL.90).aspx

Cheers,
Dan

sql

Generating Multiple Reports

I have a report that I pass parameters of a clientID. I run the report and export it to a .pdf file. I would like to be able to do this for multiple clients without manually having to enter the ID each time and exporting it.

I have thought about creating a report that calls this report so that I can pass in the clientID one at a time. The problem is trying to export each one.

Does anyone have any ideas? Thanks in advance!

I beleive you can use the reportviewer.render method to export directly to a PDF. I've done a similar app going straight to printer.|||

Let me quote myself from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2048825&SiteID=1

Two methods come to mind:

1. You can run reports from the command line using the rs command (documentation) as described in the following SQLJunkies article:

http://sqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk

which doesn't seem to be up for me so here is the Google cache link:

http://64.233.167.104/search?q=cache:XvoyQYWNs50Jqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk&hl=en&gl=us&strip=1

Within the VB script that you run, query your tables and feed the parameters.

2. You could use a variant of a Data Driven Subscription. You will need the Enterprise version of SSRS to use Data Driven Subscriptions. Here is a tutorial on building one:

http://msdn2.microsoft.com/en-us/library/ms169673.aspx

I came accross this article by Jason Selburg which claims to allow you to build your own data driven subscriptions using Standard Edition:

http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp

and his follow up:

http://www.sqlservercentral.com/columnists/jselburg/2824.asp

and I believe either method will work for you here. If you need a more specific answer, let me know.

Good luck,

Larry Smithmier

Monday, March 19, 2012

Generating File names on the fly

Hi,

I want to create a package that can process a flat file based on the current data. i.e. name of the file contains current date and some predefined characters.

What is the best way to process it?Use a property expression on the ConnectionString property of your FlatFile connection manager to set it to the correct filename (containing the date).

-Jamie

generating Excel file every morning

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:

Thursday, March 29, 2012

Get Available free space for data file

Get Available free space for data file

Get Available free space for data file

Tuesday, March 27, 2012

get a file From FTP site

Get "Do you want to save this file?" when trying to launch Report Builder

get % distribution of invalid data?

Gerating a xml file from oracle 9i in a asp program

Monday, March 26, 2012

Generating XML Schema from db tables - including field lengths

Generating XML Schema from db tables - including field lengths

Generating XML Schema for SQL Server Table

Friday, March 23, 2012

generating text file

Generating table from xsd file in SQL Server 2005

Generating SQL Scripts

generating sql logins

Wednesday, March 21, 2012

generating script with SMO

Generating Script

Generating Multiple Reports

Monday, March 19, 2012

Generating File names on the fly

generating Excel file every morning

generating excel file automatically every morning

Grid display

Blog Archive

About Me