How to simpify the Deployment process of a SQL Server Report

Dear all,
I have 5 SQL Servers and totally around 100 databases (1 database for 1
client).
Whenever I finish a Report development on my development server, I will need
to repeat the same task on these 100 databases 100 times ?
Although I can copy the Report Definition File from the development server
to these 100 databases' folders, I still need to do the following tasks :
(1)Create 100 new Visual Studio projects
(2)Enter "Start Item" and "Target Server URL" 100 times
(3)Create 100 new Shared Data Sources
(4)Build and Deploy Visual Studio projects 100 times.
Are there any better or more efficient ways to deploy a new Report in a
multiple servers or multiple databases environment ?
Please help me.
Thanks a lot."cpchan" wrote:
> Dear all,
>
> I have 5 SQL Servers and totally around 100 databases (1 database for 1
> client).
>
> Whenever I finish a Report development on my development server, I will need
> to repeat the same task on these 100 databases 100 times ?
> Although I can copy the Report Definition File from the development server
> to these 100 databases' folders, I still need to do the following tasks :
>
> (1)Create 100 new Visual Studio projects
> (2)Enter "Start Item" and "Target Server URL" 100 times
> (3)Create 100 new Shared Data Sources
> (4)Build and Deploy Visual Studio projects 100 times.
>
> Are there any better or more efficient ways to deploy a new Report in a
> multiple servers or multiple databases environment ?
>
> Please help me.
> Thanks a lot.
>
Hi
I am not a reporting services expert, you may find more help in the
reporting services newsgroup microsoft.public.sqlserver.reportingsvcs
You can probably do what you require using multiple configurations for a
single project that deploys onto the different servers:
http://msdn2.microsoft.com/en-us/library/aa179464.aspx
http://msdn2.microsoft.com/en-us/library/aa237242(SQL.80).aspx
You could probably use the command prompt utilities and write a
parameterised script that will deploy to a server
John|||Thanks
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:679E4454-427D-4734-B5BB-7817C3C11BCB@.microsoft.com...
> "cpchan" wrote:
> > Dear all,
> >
> >
> > I have 5 SQL Servers and totally around 100 databases (1 database for 1
> > client).
> >
> >
> >
> > Whenever I finish a Report development on my development server, I will
need
> > to repeat the same task on these 100 databases 100 times ?
> > Although I can copy the Report Definition File from the development
server
> > to these 100 databases' folders, I still need to do the following tasks
:
> >
> >
> > (1)Create 100 new Visual Studio projects
> > (2)Enter "Start Item" and "Target Server URL" 100 times
> > (3)Create 100 new Shared Data Sources
> > (4)Build and Deploy Visual Studio projects 100 times.
> >
> >
> >
> > Are there any better or more efficient ways to deploy a new Report in a
> > multiple servers or multiple databases environment ?
> >
> >
> >
> > Please help me.
> > Thanks a lot.
> >
> Hi
> I am not a reporting services expert, you may find more help in the
> reporting services newsgroup microsoft.public.sqlserver.reportingsvcs
> You can probably do what you require using multiple configurations for a
> single project that deploys onto the different servers:
> http://msdn2.microsoft.com/en-us/library/aa179464.aspx
> http://msdn2.microsoft.com/en-us/library/aa237242(SQL.80).aspx
> You could probably use the command prompt utilities and write a
> parameterised script that will deploy to a server
> John

How to setup reporting database

Hi!
Our client is using sql2000 sp3a OLTP database. They are also using a lot of
reports that generate high utilization on the server.
We would like to move that load to secondary server. What is the easiest way
to do that without modifying the current database or application?
First thing that comes to my mind is log shipping but as far as I know, in
this case we would get at least 1 min delay between primary and secondary
server.
I think that's great for reports but our clients don't think so...They
request seconds...
Alternative would be transactional replication. Can I replicate the entire
database with all views, stored procedures...?
I also found out that it requires that all the tables have at least one
primary key. Off course NOT all tables have them. How to solve this? Can
PK's be added easy or does it mean that they would have to modify the
database and application? The goal is not to modify the database and
application if posible.
Any sugestions?
tomLog Shipping wouldnt work anyways because the db is in a load state until
you apply the final TLog since you are constantly restoring. Transactional
Replication is pretty common for this. Not having a PK on a table is pretty
rare. Put an Identity column on the tables that don't have one.
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:OQTJVlHqEHA.1644@.tk2msftngp13.phx.gbl...
> Hi!
> Our client is using sql2000 sp3a OLTP database. They are also using a lot
of
> reports that generate high utilization on the server.
> We would like to move that load to secondary server. What is the easiest
way
> to do that without modifying the current database or application?
> First thing that comes to my mind is log shipping but as far as I know, in
> this case we would get at least 1 min delay between primary and secondary
> server.
> I think that's great for reports but our clients don't think so...They
> request seconds...
> Alternative would be transactional replication. Can I replicate the entire
> database with all views, stored procedures...?
> I also found out that it requires that all the tables have at least one
> primary key. Off course NOT all tables have them. How to solve this? Can
> PK's be added easy or does it mean that they would have to modify the
> database and application? The goal is not to modify the database and
> application if posible.
>
> Any sugestions?
>
> tom
>|||P.S.
Yes, you can replicate View, Procs, etc. but then you need a new snapshot
whenever they are modified. I prefer to just copy and paste them personally.
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:OQTJVlHqEHA.1644@.tk2msftngp13.phx.gbl...
> Hi!
> Our client is using sql2000 sp3a OLTP database. They are also using a lot
of
> reports that generate high utilization on the server.
> We would like to move that load to secondary server. What is the easiest
way
> to do that without modifying the current database or application?
> First thing that comes to my mind is log shipping but as far as I know, in
> this case we would get at least 1 min delay between primary and secondary
> server.
> I think that's great for reports but our clients don't think so...They
> request seconds...
> Alternative would be transactional replication. Can I replicate the entire
> database with all views, stored procedures...?
> I also found out that it requires that all the tables have at least one
> primary key. Off course NOT all tables have them. How to solve this? Can
> PK's be added easy or does it mean that they would have to modify the
> database and application? The goal is not to modify the database and
> application if posible.
>
> Any sugestions?
>
> tom
>|||"ChrisR" <chris@.noemail.com> wrote in message
news:ODv4F0JqEHA.376@.TK2MSFTNGP14.phx.gbl...
> Log Shipping wouldnt work anyways because the db is in a load state until
> you apply the final TLog since you are constantly restoring. Transactional
> Replication is pretty common for this. Not having a PK on a table is
pretty
> rare. Put an Identity column on the tables that don't have one.
Well, for reporting you can put the receiver into read-only mode, except
when the log is being applied.
Of course if you do this every minute, you spend most of your time NOT in
read-only mode. ;-)
And yes, this is a case where Joe Celko not withstanding an Identity key can
be a useful PK if you don't have one already.

How to setup reporting database

Hi!
Our client is using sql2000 sp3a OLTP database. They are also using a lot of
reports that generate high utilization on the server.
We would like to move that load to secondary server. What is the easiest way
to do that without modifying the current database or application?
First thing that comes to my mind is log shipping but as far as I know, in
this case we would get at least 1 min delay between primary and secondary
server.
I think that's great for reports but our clients don't think so...They
request seconds...
Alternative would be transactional replication. Can I replicate the entire
database with all views, stored procedures...?
I also found out that it requires that all the tables have at least one
primary key. Off course NOT all tables have them. How to solve this? Can
PK's be added easy or does it mean that they would have to modify the
database and application? The goal is not to modify the database and
application if posible.
Any sugestions?
tom
Log Shipping wouldnt work anyways because the db is in a load state until
you apply the final TLog since you are constantly restoring. Transactional
Replication is pretty common for this. Not having a PK on a table is pretty
rare. Put an Identity column on the tables that don't have one.
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:OQTJVlHqEHA.1644@.tk2msftngp13.phx.gbl...
> Hi!
> Our client is using sql2000 sp3a OLTP database. They are also using a lot
of
> reports that generate high utilization on the server.
> We would like to move that load to secondary server. What is the easiest
way
> to do that without modifying the current database or application?
> First thing that comes to my mind is log shipping but as far as I know, in
> this case we would get at least 1 min delay between primary and secondary
> server.
> I think that's great for reports but our clients don't think so...They
> request seconds...
> Alternative would be transactional replication. Can I replicate the entire
> database with all views, stored procedures...?
> I also found out that it requires that all the tables have at least one
> primary key. Off course NOT all tables have them. How to solve this? Can
> PK's be added easy or does it mean that they would have to modify the
> database and application? The goal is not to modify the database and
> application if posible.
>
> Any sugestions?
>
> tom
>
|||P.S.
Yes, you can replicate View, Procs, etc. but then you need a new snapshot
whenever they are modified. I prefer to just copy and paste them personally.
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:OQTJVlHqEHA.1644@.tk2msftngp13.phx.gbl...
> Hi!
> Our client is using sql2000 sp3a OLTP database. They are also using a lot
of
> reports that generate high utilization on the server.
> We would like to move that load to secondary server. What is the easiest
way
> to do that without modifying the current database or application?
> First thing that comes to my mind is log shipping but as far as I know, in
> this case we would get at least 1 min delay between primary and secondary
> server.
> I think that's great for reports but our clients don't think so...They
> request seconds...
> Alternative would be transactional replication. Can I replicate the entire
> database with all views, stored procedures...?
> I also found out that it requires that all the tables have at least one
> primary key. Off course NOT all tables have them. How to solve this? Can
> PK's be added easy or does it mean that they would have to modify the
> database and application? The goal is not to modify the database and
> application if posible.
>
> Any sugestions?
>
> tom
>
|||"ChrisR" <chris@.noemail.com> wrote in message
news:ODv4F0JqEHA.376@.TK2MSFTNGP14.phx.gbl...
> Log Shipping wouldnt work anyways because the db is in a load state until
> you apply the final TLog since you are constantly restoring. Transactional
> Replication is pretty common for this. Not having a PK on a table is
pretty
> rare. Put an Identity column on the tables that don't have one.
Well, for reporting you can put the receiver into read-only mode, except
when the log is being applied.
Of course if you do this every minute, you spend most of your time NOT in
read-only mode. ;-)
And yes, this is a case where Joe Celko not withstanding an Identity key can
be a useful PK if you don't have one already.

How to set up tools so that reports can be designed on client machine

Sorry if this is really simple.

We will be having a SQL 2005 DB set up which we need developers to be able to design & deploy reports on. The developers will only have remote access to this server across a network. What (&how) is the simplest way to set up the relevant tools (and which specific tools are needed) on the developers machines so that they can design, build & deploy reports to the main server ?

Thanks for any help, Will

You need to have Business Developement Studio on the clients installed, thats all.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Cool.

Thanks

How to set transactions timeout?

Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.

I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.

I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

Thanks in advance.

If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772404&SiteID=1

|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.

How to set transactions timeout?

Is there a way for a client to tell a server to rollback a transaction after a certain time elapses?

The scenario: I execute an update statement in a RepeatableRead transaction, but the client may lose network connectivity before the Commit is issued. If this occurs after ExecuteNonQuery, then the process on the sql server is holding a large amount of locks, and the server takes several minutes before it releases those locks.

Meanwhile, other transactions are attempting to run, but are getting selected as the deadlock victims because they are waiting on resources that the disconnected client had locked. This really backs things up.

I have found some settings that can be done on the server side that will decrease the time it takes for a transaction WAITING on resources to timeout. I.E. this just makes those that are being blocked timeout faster. But it is the blockING process that I want rolledback sooner. It doesn't get selected as the deadlock victim because it has all the resources it needs. It is not waiting on any resources, but instead the server is simply waiting for another query or a commit, which it never gets because the client lost network connectivity. The command timeout doesn't seem to have an affect, because the command itself completes.

I tested this by having a client display a modal dialog box just before the commit statement, and then I unplugged the network cable. I then used activity monitor on the server to view the locks being held. I refreshed it several times, and I believe at least 5 minutes pasted before the locks were released.

In the production environment, users will be losing connectivity very often. I'm sure someone will suggest a different architecture where the database logic is server side, and data is sent to/from clients in another manner so that this situation can be handled by the server side app. Right now that is not an option. I am running the database in 2000, but sql server 2005 with 2000 compatibility mode is also possible.

Thanks in advance.

If you are using distributed transactions, the timeout can be configured via Control Panel->Administrative Tools->Component Services.

See also http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=772404&SiteID=1

|||I'm not using distributed transactions unless that's the default for ADO.NET, but I'll read up on them and see if maybe that's what I should use. Thanks.

how to set the default filename when exporting the report

Dear Anyone,

When exporting a report to another format, say excel; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to excel, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services as well as report builder?

Thanks,
JosephThere is no way to change the name of the export. You could set up a subscription to save the file off to a file. Not sure if that will sufficient or not for your scenario.

-Daniel|||

With Reporting Service 2005, you can create a data-driven subscription and set the FILENAME parameter.

Here is a sample query:

select'bogus - '+LEFT(DATENAME(month,getdate()),3)+' '+(CAST(DatePart(dd,GetDate())asnvarchar)+' '+CAST(DatePart(yyyy,GetDate())asnvarchar))asFILENAME

Hope this helps!

Thanks.

|||I am not sure, but I recall
Data-Driven Subscription is part of SQL Server/Report Server 2005Enterprise edition only|||

hi,

Can you help me on this issue,

My requirement is need to change the Report data file name appending with timestamp.

1. I created RDL
2. Opening the RDL data into my GWT application with new IE window
3. Exporting the data into excel/pdf.
4. Able to popup Download file Dialog.
5. Asking the File to save. ( Here by defualt it's giving the RDL File name only. I want to display with RDL file name with timestamp).

But what every you said , I created data-driven subscription, In Delivery Query Screen: Even i tried with A shared data source, able to validate the Command text. Finally not able to change the file name. Can you please tell what is the problem is.

best regards

bhushanam

how to set the default filename when exporting the report

Dear Anyone,

When exporting a report to another format, say excel; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to excel, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services as well as report builder?

Thanks,
JosephThere is no way to change the name of the export. You could set up a subscription to save the file off to a file. Not sure if that will sufficient or not for your scenario.

-Daniel|||

With Reporting Service 2005, you can create a data-driven subscription and set the FILENAME parameter.

Here is a sample query:

select 'bogus - ' + LEFT(DATENAME(month, getdate()),3) + ' ' + (CAST(DatePart(dd, GetDate()) as nvarchar) + ' ' + CAST(DatePart(yyyy, GetDate()) as nvarchar)) as FILENAME

Hope this helps!

Thanks.

|||I am not sure, but I recall
Data-Driven Subscription is part of SQL Server/Report Server 2005 Enterprise edition only|||

hi,

Can you help me on this issue,

My requirement is need to change the Report data file name appending with timestamp.

1. I created RDL
2. Opening the RDL data into my GWT application with new IE window
3. Exporting the data into excel/pdf.
4. Able to popup Download file Dialog.
5. Asking the File to save. ( Here by defualt it's giving the RDL File name only. I want to display with RDL file name with timestamp).

But what every you said , I created data-driven subscription, In Delivery Query Screen: Even i tried with A shared data source, able to validate the Command text. Finally not able to change the file name. Can you please tell what is the problem is.

best regards

bhushanam

how to set the default filename when exporting the report

Dear Anyone,

When exporting a report to another format, say excel; the file name is always set to the report name. Our client has a requirement where whenever a user exports a report to excel, the timestamp of when the data of the report was made should be appended in the filename. Is there a way to do this in Reporting Services as well as report builder?

Thanks,
JosephThere is no way to change the name of the export. You could set up a subscription to save the file off to a file. Not sure if that will sufficient or not for your scenario.

-Daniel|||

With Reporting Service 2005, you can create a data-driven subscription and set the FILENAME parameter.

Here is a sample query:

select 'bogus - ' + LEFT(DATENAME(month, getdate()),3) + ' ' + (CAST(DatePart(dd, GetDate()) as nvarchar) + ' ' + CAST(DatePart(yyyy, GetDate()) as nvarchar)) as FILENAME

Hope this helps!

Thanks.

|||I am not sure, but I recall
Data-Driven Subscription is part of SQL Server/Report Server 2005 Enterprise edition only

How to Set Record Length for the Flatfiles in SSIS

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?It means that the sum of the individual column lengths must add up to 80. No less, no more.

You need to find out, though, if there is supposed to be a line feed at the end of each row, in which case you'd want to use a "ragged-right" format. Otherwise, fixed width would work.|||so if i set the output column width of each column to 80, it is all set, as the client dont have further data available.....he does not knw about the line feed......|||No, the columns must ALL add up, in total, to 80.|||great thanks phil.....I think this answers my question for now....i will keep the thread open as i might come up with more questions on this topic|||

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

|||

B.Chintan wrote:

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

It depends. If you are outputting column names to the file, then yes. (Even that might not be true, but in general, it is.) If you are not outputting file names to the file, then you can make them however long you need.

|||

I tried the above stated solution, when the column name is not specified it works fine and when column name is specified, it does not work as it wants the names to be within 80 bytes limit.

The problem now is when i try to put this flat files back to database using oledbsource and lookups, I am not able to do that. i encounter problem with format.

In this case i have set the format to fixed width...so when i try to put it in db.....i can see just one column whereas when i converted it to flatfile it had 61 columns and now while reversing it i c everything in one column.

Any step by step procedure to solve this tht is from db to flatfile with fixed width row dilimiter and vice versa

Chintan Shah

|||Then make your column names fit into the 80 bytes length restriction.

Also, use "ragged right" when loading the resulting flat file. But why are you doing this anyway? Just to test the file?|||i need to submit the flat files to an reporting agency now they have certain standards which my client need to follow, putting the flat file back to db is just a test I am doing as tht will be done by the agency when they receive the flat files from us. I will try using ragged right....hope tht works fine.|||

the best solution i did was to transform the db file in 3 format using this

OLEDBSource>>>>MultiCast>>>>3 Diff flatfiles in 3 diff formats.

One important thing to remember is

If the column headings are to be submitted than they should comply to the set Byte Limit. If they are not than column headings can be as long as desired.

Thanks phil for all help.

I would like to know if you have any Knowledge about Microsoft Dynamics and is it a good line to enter or SQL Server is the better option.

Chintan

How to Set Record Length for the Flatfiles in SSIS

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?It means that the sum of the individual column lengths must add up to 80. No less, no more.

You need to find out, though, if there is supposed to be a line feed at the end of each row, in which case you'd want to use a "ragged-right" format. Otherwise, fixed width would work.|||so if i set the output column width of each column to 80, it is all set, as the client dont have further data available.....he does not knw about the line feed......|||No, the columns must ALL add up, in total, to 80.|||great thanks phil.....I think this answers my question for now....i will keep the thread open as i might come up with more questions on this topic|||

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

|||

B.Chintan wrote:

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

It depends. If you are outputting column names to the file, then yes. (Even that might not be true, but in general, it is.) If you are not outputting file names to the file, then you can make them however long you need.

|||

I tried the above stated solution, when the column name is not specified it works fine and when column name is specified, it does not work as it wants the names to be within 80 bytes limit.

The problem now is when i try to put this flat files back to database using oledbsource and lookups, I am not able to do that. i encounter problem with format.

In this case i have set the format to fixed width...so when i try to put it in db.....i can see just one column whereas when i converted it to flatfile it had 61 columns and now while reversing it i c everything in one column.

Any step by step procedure to solve this tht is from db to flatfile with fixed width row dilimiter and vice versa

Chintan Shah

|||Then make your column names fit into the 80 bytes length restriction.

Also, use "ragged right" when loading the resulting flat file. But why are you doing this anyway? Just to test the file?|||i need to submit the flat files to an reporting agency now they have certain standards which my client need to follow, putting the flat file back to db is just a test I am doing as tht will be done by the agency when they receive the flat files from us. I will try using ragged right....hope tht works fine.|||

the best solution i did was to transform the db file in 3 format using this

OLEDBSource>>>>MultiCast>>>>3 Diff flatfiles in 3 diff formats.

One important thing to remember is

If the column headings are to be submitted than they should comply to the set Byte Limit. If they are not than column headings can be as long as desired.

Thanks phil for all help.

I would like to know if you have any Knowledge about Microsoft Dynamics and is it a good line to enter or SQL Server is the better option.

Chintan

How to Set Record Lenght for the Flatfiles in SSIS

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?It means that the sum of the individual column lengths must add up to 80. No less, no more.

You need to find out, though, if there is supposed to be a line feed at the end of each row, in which case you'd want to use a "ragged-right" format. Otherwise, fixed width would work.|||so if i set the output column width of each column to 80, it is all set, as the client dont have further data available.....he does not knw about the line feed......|||No, the columns must ALL add up, in total, to 80.|||great thanks phil.....I think this answers my question for now....i will keep the thread open as i might come up with more questions on this topic|||

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

|||

B.Chintan wrote:

one thing more is, if have set the record length for all the columns (total = 80 bytes), now when i name the columns should that also comply with number of bits i have set for each column

for eg

if columnname is MyNumber itz datataype is Varchar and Length is 10 than do MyNumber also be set within 10 Bits limit

It depends. If you are outputting column names to the file, then yes. (Even that might not be true, but in general, it is.) If you are not outputting file names to the file, then you can make them however long you need.

|||

I tried the above stated solution, when the column name is not specified it works fine and when column name is specified, it does not work as it wants the names to be within 80 bytes limit.

The problem now is when i try to put this flat files back to database using oledbsource and lookups, I am not able to do that. i encounter problem with format.

In this case i have set the format to fixed width...so when i try to put it in db.....i can see just one column whereas when i converted it to flatfile it had 61 columns and now while reversing it i c everything in one column.

Any step by step procedure to solve this tht is from db to flatfile with fixed width row dilimiter and vice versa

Chintan Shah

|||Then make your column names fit into the 80 bytes length restriction.

Also, use "ragged right" when loading the resulting flat file. But why are you doing this anyway? Just to test the file?|||i need to submit the flat files to an reporting agency now they have certain standards which my client need to follow, putting the flat file back to db is just a test I am doing as tht will be done by the agency when they receive the flat files from us. I will try using ragged right....hope tht works fine.|||

the best solution i did was to transform the db file in 3 format using this

OLEDBSource>>>>MultiCast>>>>3 Diff flatfiles in 3 diff formats.

One important thing to remember is

If the column headings are to be submitted than they should comply to the set Byte Limit. If they are not than column headings can be as long as desired.

Thanks phil for all help.

I would like to know if you have any Knowledge about Microsoft Dynamics and is it a good line to enter or SQL Server is the better option.

Chintan

How to set Client Machine name when using ADO to connect to SQL Server 2000?

When you connect to SQL Server using SQLConnection, how to set client machine name(or IP address) so that you can monitor the process on Server side using Enterprise Manager?

Whatever IP address you provided in the connection string can be used to monitor along with the user id of sql in case mixed mode authentication is there.

Please post more details..

Satya

|||

In your connection string, you can add a name/value pair like

workstation id=MYCOMPUTERNAME

and now when you runsp_who you will see MYCOMPUTERNAME for the value in thehostname column.

You can also set a value for you application like

Application Name=Your Application

within your connection string.

How to separator the ODBC and SQL client

I have one SQL server 2000 , and some client connect to SQL server through ODBC Client and use ACCESS to query database ,some client can use SQL server 2000 client to manage the database. Now I don't want the ODBC client install SQL client and connect to
SQL server 2000. I have install a host firewall in SQL server,but because ODBC client and SQL client can use two way (name pipe and TCP port 1433),so I also can't reject ODBC client use SQL client. Does someone any suggestion.
Depending on your environment several possibilites come to mind.
You can take out named pipe connections all together and only allow TCP/IP connections to your SQL server. Client DSNs used by Access are frequently set up to used named pipe connections instead of TCP/IP.
You can reassing SQL to use some other than the standard port '1433' - some higher number preferably.
You can hide the SQL port all together (Using Server Network Utility) This will most certainly also cause disruption for the already existing SQL client connections. You can also just monitor the current connections to your SQL server and isolate the non
SQL clients one at a time by isolating their connection properties.
|||Many thanks Sassan!
I can disable the port 1433 or name pipe, but I must enable the client connect to the SQL Server use ODBC, but how can I limit the user only connect the SQL server through ODBC, and reject the sql client connect one the same computer.
|||There is not a method to do this. If a client can connect from one
application he can also connect form any other application if he has a
login and knows the name of the SQL Server machine.
Rand
This posting is provided "as is" with no warranties and confers no rights.

How to separator the ODBC and SQL client

I have one SQL server 2000 , and some client connect to SQL server through O
DBC Client and use ACCESS to query database ,some client can use SQL server
2000 client to manage the database. Now I don't want the ODBC client install
SQL client and connect to
SQL server 2000. I have install a host firewall in SQL server,but because OD
BC client and SQL client can use two way (name pipe and TCP port 1433),so I
also can't reject ODBC client use SQL client. Does someone any suggestion.Depending on your environment several possibilites come to mind.
You can take out named pipe connections all together and only allow TCP/IP c
onnections to your SQL server. Client DSNs used by Access are frequently set
up to used named pipe connections instead of TCP/IP.
You can reassing SQL to use some other than the standard port '1433' - some
higher number preferably.
You can hide the SQL port all together (Using Server Network Utility) This
will most certainly also cause disruption for the already existing SQL clien
t connections. You can also just monitor the current connections to your SQL
server and isolate the non
SQL clients one at a time by isolating their connection properties.|||Many thanks Sassan!
I can disable the port 1433 or name pipe, but I must enable the client conne
ct to the SQL Server use ODBC, but how can I limit the user only connect th
e SQL server through ODBC, and reject the sql client connect one the same co
mputer.|||There is not a method to do this. If a client can connect from one
application he can also connect form any other application if he has a
login and knows the name of the SQL Server machine.
Rand
This posting is provided "as is" with no warranties and confers no rights.

How to separator the ODBC and SQL client

I have one SQL server 2000 , and some client connect to SQL server through ODBC Client and use ACCESS to query database ,some client can use SQL server 2000 client to manage the database. Now I don't want the ODBC client install SQL client and connect to SQL server 2000. I have install a host firewall in SQL server,but because ODBC client and SQL client can use two way (name pipe and TCP port 1433),so I also can't reject ODBC client use SQL client. Does someone any suggestion.Depending on your environment several possibilites come to mind
You can take out named pipe connections all together and only allow TCP/IP connections to your SQL server. Client DSNs used by Access are frequently set up to used named pipe connections instead of TCP/IP
You can reassing SQL to use some other than the standard port '1433' - some higher number preferably
You can hide the SQL port all together (Using Server Network Utility) This will most certainly also cause disruption for the already existing SQL client connections. You can also just monitor the current connections to your SQL server and isolate the non SQL clients one at a time by isolating their connection properties.|||Many thanks Sassan
I can disable the port 1433 or name pipe, but I must enable the client connect to the SQL Server use ODBC, but how can I limit the user only connect the SQL server through ODBC, and reject the sql client connect one the same computer.|||There is not a method to do this. If a client can connect from one
application he can also connect form any other application if he has a
login and knows the name of the SQL Server machine.
Rand
This posting is provided "as is" with no warranties and confers no rights.

how to send the reports as an exe file

Hi,

can any one tell me how to send the reports as an exe file. Is there any possibility in ssrs. I have to send all my reports to my client along with the .net application. can any one help me out in this.

Thanks in advanceI doubt this is possible. Why not just deploy the reports on the client's PC?|||

You will probably have to look at building a .net app to run the RDL file, and distributing both the .exe for the .net app and the rdl file.

You may want to check out Project RDL.

http://www.codeproject.com/csharp/rdlproject.asp

The RDL Project is an open source (GPL) C# implementation of the Report Definition Language (RDL) for the .NET environment, by fyiReporting Software.

RDL is an XML based language created by Microsoft with the goal of promoting interoperability of reporting products. Microsoft implemented RDL as part of the Microsoft SQL Server Reporting Services

cheers,

Andrew

|||I have created all my reports in one application. I have to send this as an exe along with my .net project. so that they can install that in their machines. can u tell me What are the possible solutions for this?|||

Hi Andrew,

Thanks for u r reply, my requirement is how to send this as an exe along with .net project. So that my client will install in his machines. is there any possibility for this.

Thanks

Rahul

|||

So, your problem is how to deploy your reports to a client?

Your reports need to be published (deployed) to your client's reporting server. Sending the rdl files in an exe (i.e. as an install exe or a zip exe) will not achieve this. I suggest you either make an msi with a custom action to deploy the reports, or you use the report manager on your client's site to upload the reports to the server (the reports can be uploaded from your machine to your customer's reporting server in this way).

Did that make sense?

|||

Thanks for the reply.

I created MSI setup for deplying the reports. can any one tell me how to change the target server. Here I added a webreference of that. but in my client place target server name may be different. how can I make it dynamic. will there be any possiblity?

Thanks

Rahul