How to develop a report to use a global datasource?

Hi all..
By using report manager, I can configure a datasource that can be global for
all reports (stored in "/" path).
When I develop the report in VS.NET, I can set the report to use a shared
datasource or a private datasource. When I use a shared datasource, a new
datasource is created under the report path when I deploy it. That way I
always have to manually change connection information of the report using
report manager.
Is there a way to point to the global datasource at design time?
thanks
JaimeHi,
use <DataSourceReference>DSN path</DataSourceReference>
inside the <DataSource> tag for each report, to use a gloal datasource.
Thanks
Bava
"Jaime Stuardo" wrote:
> Hi all..
> By using report manager, I can configure a datasource that can be global for
> all reports (stored in "/" path).
> When I develop the report in VS.NET, I can set the report to use a shared
> datasource or a private datasource. When I use a shared datasource, a new
> datasource is created under the report path when I deploy it. That way I
> always have to manually change connection information of the report using
> report manager.
> Is there a way to point to the global datasource at design time?
> thanks
> Jaime

How to determine connection pool from SQL Server?

I was going thru Enterprise Manager and can't see connection count, I can
see User connected, but that doesn't give me an actual connection count.
Is there a way to determine my connection pool count from Enterprise Manager
and/or some other tool?
Thanks, Rob.
'Connection Pool' is a feature of the client side library, if you are using
the .net provider for sql server then you should be able to look at the
following perfmon object:
".NET data provider for SQL Server"
hth
Vikram Vamshi
Eclipsys Corporation
"Rob R. Ainscough" <robains@.pacbell.net> wrote in message
news:u%230vD7dQFHA.576@.TK2MSFTNGP15.phx.gbl...
>I was going thru Enterprise Manager and can't see connection count, I can
>see User connected, but that doesn't give me an actual connection count.
> Is there a way to determine my connection pool count from Enterprise
> Manager and/or some other tool?
> Thanks, Rob.
>
|||I ended up using the SQL Profiler -- Session -- not perferct (does show
counts), but gave me an idea.
Where does one find the ".NET data provider for SQL Server" performance mon
object?
"Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
news:OQxQ%23WfQFHA.4020@.tk2msftngp13.phx.gbl...
> 'Connection Pool' is a feature of the client side library, if you are
> using the .net provider for sql server then you should be able to look at
> the following perfmon object:
> ".NET data provider for SQL Server"
> hth
> --
> Vikram Vamshi
> Eclipsys Corporation
> "Rob R. Ainscough" <robains@.pacbell.net> wrote in message
> news:u%230vD7dQFHA.576@.TK2MSFTNGP15.phx.gbl...
>
|||It is bundled with the .net runtime /sdk
Vikram Vamshi
Eclipsys Corporation
"Rob R. Ainscough" <robains@.pacbell.net> wrote in message
news:%23a4CINgQFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I ended up using the SQL Profiler -- Session -- not perferct (does show
>counts), but gave me an idea.
> Where does one find the ".NET data provider for SQL Server" performance
> mon object?
> "Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
> news:OQxQ%23WfQFHA.4020@.tk2msftngp13.phx.gbl...
>
sql

How to determine connection pool from SQL Server?

I was going thru Enterprise Manager and can't see connection count, I can
see User connected, but that doesn't give me an actual connection count.
Is there a way to determine my connection pool count from Enterprise Manager
and/or some other tool?
Thanks, Rob.'Connection Pool' is a feature of the client side library, if you are using
the .net provider for sql server then you should be able to look at the
following perfmon object:
".NET data provider for SQL Server"
hth
--
Vikram Vamshi
Eclipsys Corporation
"Rob R. Ainscough" <robains@.pacbell.net> wrote in message
news:u%230vD7dQFHA.576@.TK2MSFTNGP15.phx.gbl...
>I was going thru Enterprise Manager and can't see connection count, I can
>see User connected, but that doesn't give me an actual connection count.
> Is there a way to determine my connection pool count from Enterprise
> Manager and/or some other tool?
> Thanks, Rob.
>|||I ended up using the SQL Profiler -- Session -- not perferct (does show
counts), but gave me an idea.
Where does one find the ".NET data provider for SQL Server" performance mon
object?
"Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
news:OQxQ%23WfQFHA.4020@.tk2msftngp13.phx.gbl...
> 'Connection Pool' is a feature of the client side library, if you are
> using the .net provider for sql server then you should be able to look at
> the following perfmon object:
> ".NET data provider for SQL Server"
> hth
> --
> Vikram Vamshi
> Eclipsys Corporation
> "Rob R. Ainscough" <robains@.pacbell.net> wrote in message
> news:u%230vD7dQFHA.576@.TK2MSFTNGP15.phx.gbl...
>|||It is bundled with the .net runtime /sdk
Vikram Vamshi
Eclipsys Corporation
"Rob R. Ainscough" <robains@.pacbell.net> wrote in message
news:%23a4CINgQFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I ended up using the SQL Profiler -- Session -- not perferct (does show
>counts), but gave me an idea.
> Where does one find the ".NET data provider for SQL Server" performance
> mon object?
> "Vikram Vamshi" <vikram.vamshi@.nospam.com> wrote in message
> news:OQxQ%23WfQFHA.4020@.tk2msftngp13.phx.gbl...
>

how to deploy the reports to their own folders?

here is what i did, from either Report Manager or SQL Server Management
Studio i created a folder, then i move the reports to this folder, but
from Microsoft Visual Studio, as long as i deploy the project, it still
save my moved reports outside of the folder i created. i wonder how do
i let the system deploy the projects and still save inside the folder i
created. thanks.Use the project properties of the report project to specify the folder you
want the reports and data sources to go to. If you created it in "/My
Reports That Are Cool" then you specify the same folder name in the report
project properties.
-Tim
"Wang Xiaoning" <wang_xiaoning@.hotmail.com> wrote in message
news:1148590710.476371.266730@.g10g2000cwb.googlegroups.com...
> here is what i did, from either Report Manager or SQL Server Management
> Studio i created a folder, then i move the reports to this folder, but
> from Microsoft Visual Studio, as long as i deploy the project, it still
> save my moved reports outside of the folder i created. i wonder how do
> i let the system deploy the projects and still save inside the folder i
> created. thanks.
>|||Tim, i want to group the reports, saying, sales report go to sales
folder, finance report got o finance, then for end user, as long as
they see local/reports, they will know how to navigate to his own
folders and reports.|||do you mean i have to create tow projects(one for sales, one for
fiance)?

How to deploy reports using only report manager?

Hello!
I'm having problems deploying reports...
I have a development environment where I use Visual Studio to deploy reports
- All reports deployed have been manually assigned to shared data sources
instead of the one attached to the project. This works great.
However, when I need to deploy data sources and reports to a new instance of
reporting services in a production environment - I only want to use report
manager for this. I know I can't upload data sources, so I have manually
created those I have. The problem is that the reports I upload have a data
source attached in the XML that has a different ID than the one I just
created - So report manager complaint about the data sources being missing.
Now I want to access the reports and manually point them to the new shared
data sources, but the Browse button for data sources doesn't work - Nothing
happens when pushed.
So how am I supposed to assign the reports to the new data sources? - I have
manually tried to change the XML without luck, but I don't want to do this
every time I have to update a report.
HELP!
--
Cheers,
Daniel AHi Daniel:
Instead of using the report manager, you might consider a small .RSS
script.
In the ReportingServices/Samples/Scripts directory is a file called
PublishSampleReports.rss that programatically creates a datasource and
uploads rdl files using the source. Copying this file and building on
it gives you a base to build on.
This approach will also give you something you can test out in
development and then take to production with confidence it will work.
I always try to script as much as I can because I forget steps as soon
as I step in front of a prod machine and try to do stuff by hand.
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Wed, 27 Oct 2004 06:59:08 -0700, "Daniel A"
<DanielA@.discussions.microsoft.com> wrote:
>Hello!
>I'm having problems deploying reports...
>I have a development environment where I use Visual Studio to deploy reports
>- All reports deployed have been manually assigned to shared data sources
>instead of the one attached to the project. This works great.
>However, when I need to deploy data sources and reports to a new instance of
>reporting services in a production environment - I only want to use report
>manager for this. I know I can't upload data sources, so I have manually
>created those I have. The problem is that the reports I upload have a data
>source attached in the XML that has a different ID than the one I just
>created - So report manager complaint about the data sources being missing.
>Now I want to access the reports and manually point them to the new shared
>data sources, but the Browse button for data sources doesn't work - Nothing
>happens when pushed.
>So how am I supposed to assign the reports to the new data sources? - I have
>manually tried to change the XML without luck, but I don't want to do this
>every time I have to update a report.
>HELP!|||Thanks Scott
I will investigate this method closer...
Daniel
"Scott Allen" wrote:
> Hi Daniel:
> Instead of using the report manager, you might consider a small .RSS
> script.
> In the ReportingServices/Samples/Scripts directory is a file called
> PublishSampleReports.rss that programatically creates a datasource and
> uploads rdl files using the source. Copying this file and building on
> it gives you a base to build on.
> This approach will also give you something you can test out in
> development and then take to production with confidence it will work.
> I always try to script as much as I can because I forget steps as soon
> as I step in front of a prod machine and try to do stuff by hand.
> --
> Scott
> http://www.OdeToCode.com/blogs/scott/
> On Wed, 27 Oct 2004 06:59:08 -0700, "Daniel A"
> <DanielA@.discussions.microsoft.com> wrote:
> >Hello!
> >
> >I'm having problems deploying reports...
> >
> >I have a development environment where I use Visual Studio to deploy reports
> >- All reports deployed have been manually assigned to shared data sources
> >instead of the one attached to the project. This works great.
> >
> >However, when I need to deploy data sources and reports to a new instance of
> >reporting services in a production environment - I only want to use report
> >manager for this. I know I can't upload data sources, so I have manually
> >created those I have. The problem is that the reports I upload have a data
> >source attached in the XML that has a different ID than the one I just
> >created - So report manager complaint about the data sources being missing.
> >Now I want to access the reports and manually point them to the new shared
> >data sources, but the Browse button for data sources doesn't work - Nothing
> >happens when pushed.
> >
> >So how am I supposed to assign the reports to the new data sources? - I have
> >manually tried to change the XML without luck, but I don't want to do this
> >every time I have to update a report.
> >
> >HELP!
>|||Hi Scott
It's pretty easy to script the deployment, but the report is still pointing
to a data source that doesn't exist and for some reason the browse button to
point to another data source doesn't work - So it didn't really solve my
problem :o(
Daniel
"Scott Allen" wrote:
> Hi Daniel:
> Instead of using the report manager, you might consider a small .RSS
> script.
> In the ReportingServices/Samples/Scripts directory is a file called
> PublishSampleReports.rss that programatically creates a datasource and
> uploads rdl files using the source. Copying this file and building on
> it gives you a base to build on.
> This approach will also give you something you can test out in
> development and then take to production with confidence it will work.
> I always try to script as much as I can because I forget steps as soon
> as I step in front of a prod machine and try to do stuff by hand.
> --
> Scott
> http://www.OdeToCode.com/blogs/scott/
> On Wed, 27 Oct 2004 06:59:08 -0700, "Daniel A"
> <DanielA@.discussions.microsoft.com> wrote:
> >Hello!
> >
> >I'm having problems deploying reports...
> >
> >I have a development environment where I use Visual Studio to deploy reports
> >- All reports deployed have been manually assigned to shared data sources
> >instead of the one attached to the project. This works great.
> >
> >However, when I need to deploy data sources and reports to a new instance of
> >reporting services in a production environment - I only want to use report
> >manager for this. I know I can't upload data sources, so I have manually
> >created those I have. The problem is that the reports I upload have a data
> >source attached in the XML that has a different ID than the one I just
> >created - So report manager complaint about the data sources being missing.
> >Now I want to access the reports and manually point them to the new shared
> >data sources, but the Browse button for data sources doesn't work - Nothing
> >happens when pushed.
> >
> >So how am I supposed to assign the reports to the new data sources? - I have
> >manually tried to change the XML without luck, but I don't want to do this
> >every time I have to update a report.
> >
> >HELP!
>|||Well I solved it - As a part of the scripted deployment I used the
SetReportDataSources method to update the data source of the report once
uploaded - This works great...
Required a bit of work, but it's totally easy to deploy the reports now -
Just execute a batch file :o)
Daniel
"Daniel A" wrote:
> Hi Scott
> It's pretty easy to script the deployment, but the report is still pointing
> to a data source that doesn't exist and for some reason the browse button to
> point to another data source doesn't work - So it didn't really solve my
> problem :o(
>
> Daniel
>
> "Scott Allen" wrote:
> > Hi Daniel:
> >
> > Instead of using the report manager, you might consider a small .RSS
> > script.
> >
> > In the ReportingServices/Samples/Scripts directory is a file called
> > PublishSampleReports.rss that programatically creates a datasource and
> > uploads rdl files using the source. Copying this file and building on
> > it gives you a base to build on.
> >
> > This approach will also give you something you can test out in
> > development and then take to production with confidence it will work.
> > I always try to script as much as I can because I forget steps as soon
> > as I step in front of a prod machine and try to do stuff by hand.
> >
> > --
> > Scott
> > http://www.OdeToCode.com/blogs/scott/
> >
> > On Wed, 27 Oct 2004 06:59:08 -0700, "Daniel A"
> > <DanielA@.discussions.microsoft.com> wrote:
> >
> > >Hello!
> > >
> > >I'm having problems deploying reports...
> > >
> > >I have a development environment where I use Visual Studio to deploy reports
> > >- All reports deployed have been manually assigned to shared data sources
> > >instead of the one attached to the project. This works great.
> > >
> > >However, when I need to deploy data sources and reports to a new instance of
> > >reporting services in a production environment - I only want to use report
> > >manager for this. I know I can't upload data sources, so I have manually
> > >created those I have. The problem is that the reports I upload have a data
> > >source attached in the XML that has a different ID than the one I just
> > >created - So report manager complaint about the data sources being missing.
> > >Now I want to access the reports and manually point them to the new shared
> > >data sources, but the Browse button for data sources doesn't work - Nothing
> > >happens when pushed.
> > >
> > >So how am I supposed to assign the reports to the new data sources? - I have
> > >manually tried to change the XML without luck, but I don't want to do this
> > >every time I have to update a report.
> > >
> > >HELP!
> >
> >|||Cool!
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 29 Oct 2004 02:03:02 -0700, "Daniel A"
<DanielA@.discussions.microsoft.com> wrote:
>Well I solved it - As a part of the scripted deployment I used the
>SetReportDataSources method to update the data source of the report once
>uploaded - This works great...
>Required a bit of work, but it's totally easy to deploy the reports now -
>Just execute a batch file :o)
>
>Daniel
>
>"Daniel A" wrote:
>> Hi Scott
>> It's pretty easy to script the deployment, but the report is still pointing
>> to a data source that doesn't exist and for some reason the browse button to
>> point to another data source doesn't work - So it didn't really solve my
>> problem :o(
>>
>> Daniel
>>
>> "Scott Allen" wrote:
>> > Hi Daniel:
>> >
>> > Instead of using the report manager, you might consider a small .RSS
>> > script.
>> >
>> > In the ReportingServices/Samples/Scripts directory is a file called
>> > PublishSampleReports.rss that programatically creates a datasource and
>> > uploads rdl files using the source. Copying this file and building on
>> > it gives you a base to build on.
>> >
>> > This approach will also give you something you can test out in
>> > development and then take to production with confidence it will work.
>> > I always try to script as much as I can because I forget steps as soon
>> > as I step in front of a prod machine and try to do stuff by hand.
>> >
>> > --
>> > Scott
>> > http://www.OdeToCode.com/blogs/scott/
>> >
>> > On Wed, 27 Oct 2004 06:59:08 -0700, "Daniel A"
>> > <DanielA@.discussions.microsoft.com> wrote:
>> >
>> > >Hello!
>> > >
>> > >I'm having problems deploying reports...
>> > >
>> > >I have a development environment where I use Visual Studio to deploy reports
>> > >- All reports deployed have been manually assigned to shared data sources
>> > >instead of the one attached to the project. This works great.
>> > >
>> > >However, when I need to deploy data sources and reports to a new instance of
>> > >reporting services in a production environment - I only want to use report
>> > >manager for this. I know I can't upload data sources, so I have manually
>> > >created those I have. The problem is that the reports I upload have a data
>> > >source attached in the XML that has a different ID than the one I just
>> > >created - So report manager complaint about the data sources being missing.
>> > >Now I want to access the reports and manually point them to the new shared
>> > >data sources, but the Browse button for data sources doesn't work - Nothing
>> > >happens when pushed.
>> > >
>> > >So how am I supposed to assign the reports to the new data sources? - I have
>> > >manually tried to change the XML without luck, but I don't want to do this
>> > >every time I have to update a report.
>> > >
>> > >HELP!
>> >
>> >|||> In the ReportingServices/Samples/Scripts directory is a file called
> PublishSampleReports.rss
I don't see any such directory or file on my machine - where exactly is it located'|||> In the ReportingServices/Samples/Scripts directory is a file called
> PublishSampleReports.rss
Ewwww - never mind my other post, I found the file

how to deploy reports on report server other then using rerport mg

hey, Is there any way to making a setup of report project, to deploy reports
on server.
I have used both vs.net and report manager, but in both ways i have to has
access to report server.
I want to deply reports on the server on which i am not having access.
How to make a setup.
thanksSee if Reporting Services Scripter helps.
http://www.sqldbatips.com/showarticle.asp?ID=62
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"vibha" <vibha@.discussions.microsoft.com> wrote in message
news:89560410-2E89-469D-85DF-695657E57E8F@.microsoft.com...
> hey, Is there any way to making a setup of report project, to deploy
> reports
> on server.
> I have used both vs.net and report manager, but in both ways i have to has
> access to report server.
> I want to deply reports on the server on which i am not having access.
> How to make a setup.
> thanks|||Even with the scripter you will have to have some sort of Access. VS.net is
using web services to deploy. As long as your account has the rights in RS
to deploy you will be able to do this. Your account does not have to be
admin on the server (if it is then it automatically has deploy rights).
I suggest you look at how RS implement roles.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"vibha" <vibha@.discussions.microsoft.com> wrote in message
news:89560410-2E89-469D-85DF-695657E57E8F@.microsoft.com...
> hey, Is there any way to making a setup of report project, to deploy
> reports
> on server.
> I have used both vs.net and report manager, but in both ways i have to has
> access to report server.
> I want to deply reports on the server on which i am not having access.
> How to make a setup.
> thanks

How to deploy msde database in web matrix

Dear Sir,
How do I deploy database to remote server in web matrix. It does not have enterprise manager.
I have to use osql.exe command line that runs msde statements that are exuceted against database. Can you
please let me know how to use osql.exe.
Regards,
Farhan

Web Matrix is free so is MSDE so you can buy the Developer edition for $37 to manage MSDE or test drive SQL Server 2005 from the first link below. I would do the former. Hope this helps.
http://www.microsoft.com/sql/downloads/trial-software.mspx

http://www.provantage.com/buy-22053391-microsoft-backoffice-sql-server-2000-developer-edition-shopping.htm

How to delete/drop all the tables from SQL Server Database without using Enterprise Manager?

How to delete/drop all the tables from SQL Server Database without using Enterprise Manager?

I tried using DROP Tables, Truncate Database, Delete and many more but it is not working. I want to delete all tables using Query Analyzer, i.e. through SQL Query.

Please help me out in this concern.

Nishith Shah

hi Nishith Shah

try this

EXEC sp_MSforeachtable @.command1 = "DROP TABLE ?"

this is a hidden SP in sql server, this will be executed for each table in the database you connected (you cant rollback this)

if u want to delete it from the command prompt try this

EXEC xp_cmdshell 'SQLCMD -U <user> -P <password> -Q 'EXEC sp_MSforeachtable @.command1 = "DROP TABLE ?" ' ,no_output

Best of luck.

Gurpreet S. Gill

|||Hi Gurpreet,
it worked man.......... thanx a lot for your reply!

Nishith Shah|||

Thanks man

|||Hi Gurpreet! once again.

you have shown me the perfect way to delete/drop all table using single SQL statement.

tell me if i just want to truncate/delete all the tables then how can i?

pls reply

Nishith|||

Hay man what you are asking for,if you just check my reply, the answere is there

ok, try this, this will delete/truncate all the Data from each table for in the database you connected

EXEC sp_MSforeachtable @.command1 = "DELETE FROM ?"

EXEC sp_MSforeachtable @.command1 = "TRUNCATE TABLE ?"

I too explain it now, as sp_MSforeachtable is Stored Procedure, that will execute for all the tables for database & @.command1 is variable which will run against each table for connected database, now whatever you will write in the double quotes, that will be act as a command for each table, where '?' is the name of the table.

try this, it will clear your comcepts

EXEC sp_MSforeachtable @.command1 = "SELECT * FROM ?" -- Selects all the rows form all the table

EXEC sp_MSforeachtable @.command1 = "PRINT '?'" --Just print the tables names with owner(dbo)

For more understanding, go for the MSDN or google, this is the right way.

If still you are confused do call me any time(I am an Indian, 24x7) at +91-99495-60051

Regards,

Thanks.

Gurpreet S. Gill

|||Hello Gurpreet,

thanks a lot for helping man and giving your cell # also. It worked again...
So, where r u working? as a?

do contact me anyhow on me.poison@.gmail.com or nishith82@.hotmail.com
atleast send me a blank email, i will understand its u.

thanks,
Nishith|||

You won't be able to run TRUNCATE against all tables if you have foreign keys references

Here is one way to circumvent that

-- First disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO

-- Now enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

Thanks Denis, ya these things need to consider, before applying delete/truncate command.

Regards,

Thanks.

Gurpreet S. Gill

|||

HI people

I want to do this in MS Access database . Delete all tables . Is there a hidden SP here also ? or some other way . .

Plz help

|||

hi

i cant say anything about this, better to go for the MS-Access forum.

or

if you know the visual basic you can write the macro for that.

just check this link

http://www.codecomments.com/message725983.html

Regards,

thanks.

Gurpreet S. Gill

How to delete the uploaded files in Report Manager

Hi,

Is there a way to delete uploaded the report projects in http:/Localhost/ReportServer

Thanks,

Zixing Wang

Switch to Detail view|||

Note: the Detail view is only available in report manager (.../reports), not through the /reportserver virtual root.

-- Robert

How to delete an Analysis Service DB?

Hi,

when i try do deploy an AS-Projekt, i get follwing error Massage:

Error 1 Errors in the metadata manager. The dimension with ID of 'v Kundeneigenschaft', Name of 'v Kundeneigenschaft' referenced by the 'v Kundeneigenschaft ~MC' cube, does not exist. Errors in the metadata manager. An error occurred when loading the v Kundeneigenschaft ~MC cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Analysis Services Project3.0.db\v Kundeneigenschaft ~MC.3.cub.xml'. 0 0

By the way, the related Cube is not Part of the AS-Projekt i wish to deploy. This Error above occurs on everything i do: Try to delete this Analysis Service Database, ReDeploy another AS-Projekt, Build and Deploy a new projekt and so on...

Cleanup (delete) of C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data doesn't help, some more error messages appear.

Anybody an idea?

Greetings, Torsten

Make sure you restart your Analysis Server after deleting files from the C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data folder.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

Yes, thanks. Thought i've tried that - but it seem that not...

Now it works!

How to define the column size in the query

I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?
On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows?
|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:

>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?

How to define the column size in the query

I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:
>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?

How to define the column size in the query

I use SQL Server 2000 Enterprise manager to exporting some data out
from database to a text file. I specified in DTS, about the look of
text file. but my output is like this:
0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
but mine looks like:
0000500628;J;7200;J;Scania Beers
B.V. ;Smaragdweg
0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
How can I archive this ?On Feb 22, 3:06 pm, superb...@.gmail.com wrote:
> I use SQL Server 2000 Enterprise manager to exporting some data out
> from database to a text file. I specified in DTS, about the look of
> text file. but my output is like this:
> 0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> but mine looks like:
> 0000500628;J;7200;J;Scania Beers
> B.V. ;Smaragdweg
> 0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
> How can I archive this ?
Nobody knows'|||If I understand the question, the first two lines are what you want to
get, but the other lines - where the data splits - is what you are
actually getting.
The only reason I can think for that split is that there is a carriage
return CHAR(13), a linefeed CHAR(10), or both CHAR(13 + CHAR(10),
embedded in the column of the second row. Run a query to check for
this:
SELECT *
FROM TableName
WHERE ColumnName like '%[' + char(13) + char(10) + ']%'
Since carriage return/linefeed is the standard row terminator for text
files I do not believe you will be able to extract the table to a text
file without first removing them from any data strings. REPLACE can
do this, though you will have to nest two executions for the two
characters. If the data in the table can not be changed, but the data
written to the text file can be, remove them on the fly as you
extract.
Roy Harvey
Beacon Falls, CT
On 22 Feb 2007 06:06:54 -0800, superbaby@.gmail.com wrote:

>I use SQL Server 2000 Enterprise manager to exporting some data out
>from database to a text file. I specified in DTS, about the look of
>text file. but my output is like this:
>0000500628;J;7200;J;Scania Beers B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>but mine looks like:
>0000500628;J;7200;J;Scania Beers
>B.V. ;Smaragdweg
>0000500630;J;7200;J;Scania Beers B.V. ;De Overmaat
>
>How can I archive this ?