DTS from MS SQL to Excel Spreadsheet Issue

I'm getting an issue on a MS SQL DTS package that is doing a simple export from a MS SQL table to and Excel spreadsheet. I have three of these running but one is failing. Im using DTSRun to run all three of these DTS packages. The only recent change was to the DTS package to fix the first step to delete the data in the spreadsheet tab named Results. The process works correctly in development (on different servers). The same active directory ID is being used on all three DTS packages and all three do the same i.e. export data to an excel spreadsheet in the same file location but with different names. Ive Googled this but only came across access issues which does not make since since it is writing the other two spreadsheets just fine. Curious.

Error I See:

Running DTS package with passed variables
...
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1

DTSRun OnStart: Drop table Results Step

DTSRun OnError: Drop table Results Step, Error = -2147217911 (80040E09)

Error string: Cannot modify the design of table 'Results'. It is in a read-only database.

Error source: Microsoft JET Database Engine

Help file:

Help context: 5003027

Error Detail Records:

Error: -2147217911 (80040E09); Provider Error: -538642193 (DFE4F8EF)

Error string: Cannot modify the design of table 'Results'. It is in a read-only database.

Error source: Microsoft JET Database Engine
Help file:
Help context: 5003027

Any ideas would be great.

Thanks.
Jimright click on the spreadsheet and go to properties. what do you see?

the application dev team spent a week tossing something similar to this with Access for one their internal processes. I pointed it out a couple minutes after they asked me. I spent an hour laughing at my team of geniuses.

I am a joy to work with.|||Thanks for the idea. I thought of the read only flag as soon as I saw the read only error in the error log. Unfortunately that wasnt it. The issue was within the DTS package. If you open up the connection properties and click the top option to New Connection in an attempt to re-name the object, you actually create a new object with a new name leaving the older one in tacked but hidden in the background. This can only be seen if you go into Disconnected Edit under connections. There was an object names Connection1 and Connection2. These old connection objects where pointing to the development environment where the functional ID that was used to run the DTS did not have access to. Oops

Again thanks for the idea.

Jimsql

DTS from Hosting

I am trying to export from an SQL Server on a hosting company to my own computer using DTS. I'm new to SQL Server manipulations like this so please go easy on me...

I can't seem to manage to do this properly - If I select "Copy tables & data only" it will not copy the identity constraints and primary keys. If I select "copy all objects" it starts giving me permission issues which I cannot seem to resolve, because I guess the tables on the hosting server are owned by my username up there, but when they come to my computer they are orphant?

Isn't there an easy way to do this? It's a very simple database, tables and that's it, not even any stored procedures.Ah , you have made a common mistake of having - you - rather than dbo owning objects.

I would export all the data to the new machine like you are doing, then change ownership on EVERY object to be dbo.

Yes, its long winded and a hassle...but this shows the reason why its so easy to have broken ownership chains if dbo doesnt own all objects.

Cheers,

SG|||Thanks - I have actually done that meanwhile and it solved the problem.
However - I have no control under what user the objects are created in the remote database as it is hosted - and I have to login in the user they supplied me, therefore all objects will be created under that user name.

Or am I mistaken? Can I still override that and force the remote DB to create new objects under dbo?|||The user dictates the ownership of the object.

Unless you manually run a script to change owner to dbo - or - the user is sa level ( NOT recommended!!!! )...

Cheers,

SG.

DTS for Import Export TO And From EXCEL

Hi All,
I want to design a DTS Package that will read an EXCEL Document (One Data
Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
will have a JOIN from Both the source and Export the result to another Excel
Document.
How Can I perform that using DTS?
I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
And 3) Excel Connection for Export the Result.
My Requirement is to get the value from One of the column from one of the
Sheet and use that values to get a Joined Record from TWO tables of SQL
Server.
Ex: -
Sheet2$ : Having Column "EmployeeID" with 100 rows.
IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
I want to export the LIST of the Departments for the Employee that are in
the Excel Sheet2.
Please Suggest how can I do that or any Better solution using DTS.
Thanks
PrabhatYou could use OPENDATASOURCE
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended
properties=Excel 5.0')...xactions
Or you can create a linked server of the source XL spreadsheet from the
SQL Server. You then query that and export to XL destination.
You cannot use the Excel connections to do this ........Yet.
Allan
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:not_a_mail@.hotmail.com:

> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another Exc
el
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat|||306397 How To Use Excel with SQL Server Linked Servers and Distributed
Queries
http://support.microsoft.com/?id=306397
-Doug
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Prabhat" <not_a_mail@.hotmail.com> wrote in message
news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi All,
> I want to design a DTS Package that will read an EXCEL Document (One Data
> Source) and ONE SQL Server (2nd Data Source) and Execute one Query which
> will have a JOIN from Both the source and Export the result to another
> Excel
> Document.
> How Can I perform that using DTS?
> I have took 3 Connections 1) SQL Server 2) Excel -> These tow for Source
> And 3) Excel Connection for Export the Result.
> My Requirement is to get the value from One of the column from one of the
> Sheet and use that values to get a Joined Record from TWO tables of SQL
> Server.
> Ex: -
> Sheet2$ : Having Column "EmployeeID" with 100 rows.
> IN SQL Server I have 2 Tables. 1) Employee 2) Dept.
> I want to export the LIST of the Departments for the Employee that are in
> the Excel Sheet2.
> Please Suggest how can I do that or any Better solution using DTS.
>
> Thanks
> Prabhat
>|||"Douglas Laudenschlager [MS]" <douglasl@.online.microsoft.com> wrote in
message news:OOnZmB$XFHA.2884@.tk2msftngp13.phx.gbl...
> 306397 How To Use Excel with SQL Server Linked Servers and Distributed
> Queries
> http://support.microsoft.com/?id=306397
> -Doug
> --
> Douglas Laudenschlager
> Microsoft SQL Server documentation team
> Redmond, Washington, USA
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Prabhat" <not_a_mail@.hotmail.com> wrote in message
> news:%23iSD4RHXFHA.3464@.TK2MSFTNGP10.phx.gbl...
Data
the
in
>

DTS Export Wizard

Is there a way for me to globally select "Delete rows in destination table"
when performing an export via the Export Wizard?
It is not obviously visible to me, and I have a great deal of tables. I've
been clicking "Edit" for each one, and this isnt practical for obvious
reasons.
Can anyone point me in the right direction? Thanks.there is an option under 'select object...' dialog.
http://msdn.microsoft.com/library/e...elpwiz_0nci.asp
or you can create a dml task and delete the data before insert new ones.
-oj
"Elliot Rodriguez" <elliotrodriguezatgeemaildotcom> wrote in message
news:eAbKC$1TGHA.4608@.tk2msftngp13.phx.gbl...
> Is there a way for me to globally select "Delete rows in destination
> table" when performing an export via the Export Wizard?
> It is not obviously visible to me, and I have a great deal of tables. I've
> been clicking "Edit" for each one, and this isnt practical for obvious
> reasons.
> Can anyone point me in the right direction? Thanks.
>

DTS Export to TEXT File

Okay,

Here the issue...

I am exporting a table to a text file using DTS. Such an easy task.

This process I had in place worked, then I added a new column to the table. So I had to remap the destination and source column. When I try to do this Enterprise Manager just gives me some type of mme.exe error that relates to memory and closes.

This column made column number 22. Everytime I do this to any table with 21 columns or less it works fine, tried to do this against some tables with 22 or more and ran into the same problem.

I searched Microsoft and did not find this as a limitation. So does anyone have some insight.

Thanks for you assistance.http://support.microsoft.com/default.aspx?scid=KB;EN-US;814113

DTS Export to Excel Too many Recs?

Hi All,

I'm trying to export around 115,000 rows from ms sql 2000 into Excel 2000, using the manual process. I just need this one time dump.

I am able to successfully export around +- 65,000 rows, but the operation fails after that.

I need to be able to get all the rows out, so using TOP obviously doesn't work.

Is there some "version" or modified way to use TOP to get...say, rows 65,000 to 90,000 then 90,001 to 115,000 ?

This would'nt be an issue if the db I'm working with was MySQL...I'd just use the LIMIT function and pull out 3 different chucks. Is there anything similar to LIMIT...or something converse to TOP in MS SQL? Or perhaps another way to dump the table then export it all (or in portions) into Excel?

Thanks!You need to have a key field(s) that you can sort on. So lets say you have a unique id like excel_id numbered from 1 to 120000:

select top 65000 ... from table order by excel_id
select top 55000 ... from table order by excel_id desc|||If you want an inner subset like 65000-90000 then you would use something like:

select top 25000 ... from (select top 55000 from table order by field desc) order by field|||If you do have a key then you can use KEY BETWEEN 65001 and 90000, etc. ...and if you were using MySQL we wouldn't have even bothered to look at the question...In short, - DON'T USE IT!|||Are you using a standard DTS data pump to export the data? Or are you using something else?

If you are using a data pump then why not take care of the problem programaticly? Select all the records and keep an internal counter so you know when you reach 65000 and when you do change your target to a new spreadsheet zero the counter and continue on...

Just a thought,...|||There are plenty of other ways to do this as well, it just depends on the requirements for your export...|||With a programmatic solution, your performance will suffer. Allow the database to do its job.|||Thanks for all the replies/suggestions!

There is no key field.
I couldn't find a solution, so I just dumped the data, opened it in textpad, cut out three chunks (~35,000 rows) and imported them into 3 diff .xls files.

Interesting enough for those in the anti MySQL crowd ... chances are, this very forum almost certainly uses MySQL. I say this for two reasons. 1. PHP server side scripting. 2. This is a vBulletin forum.
Not to mention the growing interest in open source products vs proprietary!

Cheers!

DTS Export to Excel (How to Format Results in Excel)

Hi All

I've been googling this for a while now and can't seem to find any elegant answers.

I'm looking for an automated way to present a FORMATED Excel Spreadsheet to the Customer from a stored procedure output.

Can anyone advise me the best method of doing this - should I / can I assign an Excel Template to the DTS Task output ?

His mind is set on Excel and the formatting is basic and easy to write in a Macro which I've done, but this requires human interaction to finish the task (Automated Run Once on opening etc).

In an ideal world an individual would send an email to the Server with two formated parameters (@.FromDate & @.ToDate) and would be emailed back a ready formatted S/Sheet. But I believe he would be willing to just select the relevant SpreadSheet for the Daily / Weekly / Monthly periods dumped.

Thanks

GWTurn your logic inside out. Create the macro, and use the macro to retrieve the data into Excel then format it.

-PatP|||Thanks for the suggestion Pat

I eventually got MSQuery installed & got some simple External Data into a sheet in Excel - Formatted another sheet and the past linked the Data from source sheet to the new sheet which seemed to work really good.

Then I tried altering the External Data Call to include the @.FromDate & @.ToDate.

O worlds of pain - got as far as this err :-

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

There's no cast/convert in the SP so I assume it's the dreaded ODBC Driver again.

O why O Why did you guys insist on formatting your dates funny m/d/y instead of the correct way which is British English d/m/y (Flame Flame - tehe) which I assume maybe the problem.

I'll battle on - any suggestions gratefuly received.

GW

DTS Export to Excel

I'm looking for the best way to export the results of a parameterized stored procedure (SQL 2000) to excel. I can do this with DTS using global variables for the parameters, but each time I execute the package it appends the data below where the previous data was, leaving a bunch of blank rows. I need the data to always be appended to the 2nd row (replacing the old data) because I have a chart based on a dynamic named range in Excel. Is there an easy way to do this in DTS, or should I approach this another way (ADO, ActiveX Scripts, .NET, etc.)? Thanks,

Dave

Try putting the query directly into the spreadsheet. Use Data -> Import External Data -> Query. You can set up the query to take parameters that you are either prompted for or are taken from a cell. You can also configure the query to always replace the previous data.|||Thanks. I was thinking that using Microsoft Query as you described might be the way to go. I actually came up with something that works using DTS with ActiveX scripts but it's alot "clunkier" than using Microsoft Query from Excel would be.sql

DTS Export to Excel

I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.Hi Martin
"Martin" wrote:
> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was to
> use the Delete option in the Transformation window of the wizard. I rebuilt
> the DTS based on that idea, but get an error message about "Deleting data in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in the
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
> > I am using a DTS created by the Export Wizard to send data to an Excel
> > spreadsheet. The problem is that each time the DTS is run, the data gets
> > appended to the spreadsheet, rather than replaced. I used the Drop and
> > Create Destination Table option on the Transformation window.
> >
> > I found an earlier post regarding this same problem. The suggestion was to
> > use the Delete option in the Transformation window of the wizard. I rebuilt
> > the DTS based on that idea, but get an error message about "Deleting data in
> > a linked table is not supported by this ISAM".
> >
> > Can someone offer some ideas on how to get the data replaced in the Excel
> > spreadsheet?
> >
> > Thanks.
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that using
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way to
> http://www.sqldts.com/200.aspx
> John|||Hi Martin
"Martin" wrote:
> I cannot rename the spreadsheet because it is tied into other processes. I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is what
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in the
> range name. Subsequent runs of the DTS appear to be using the last line of
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the old
> data. The old data is cleared leaving blank rows, but the new data is still
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:
> Hi Martin
> "Martin" wrote:
> > I cannot rename the spreadsheet because it is tied into other processes. I
> > need to replace the data that already exists in the spreadsheet.
> >
> > If it helps, I did some research since my original posting and here is what
> > I found:
> > 1) When DTS creates the range name in the spreadsheet, it is only the
> > headings of the data. The data itself is not included in the range name.
> > Somewhere, the last line of data is being tracked versus the last line in the
> > range name. Subsequent runs of the DTS appear to be using the last line of
> > data, not the last line in the range.
> >
> > 2) If I manually expand the range name to include the last line of data,
> > then rerun the DTS, the new data is still appended to the bottom of the old
> > data. The old data is cleared leaving blank rows, but the new data is still
> > appended to the bottom; again based on the last line of data. The area
> > covered by the range name returns to being just the headings.
> >
> > Could the fact that Excel is not installed on the machine running the DTS
> > have any bearing?
> >
> > Thanks.
> >
> I don't think it is the lack of excel that does this, as this also occurs on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
> you package re-creates it the net effect should be ok.
> John|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/microsoft.public.development.device.drivers/browse_thread/thread/5d0d28403902b7d9/2b95633f7391968c?lnk=raot#2b95633f7391968c|||Hi Martin
"Martin" wrote:
> I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John

DTS Export to Excel

I want to run Stored procedure that is returning recordsets and is using cursors/ temporary tables (MS Sql 2000 Server).
The output of this SP is to be used to prepare an excel Report.

In the Transform Data Task Properties:

EXEC sp_ProductivityReport_ByDay '01/01/2005','02/01/2005'

It shows me the data in the Preview, but asks me to define transformations. Further on the transformations, it does not shows up the source columns (although they were populated in the preview)

When I perform the same task using DTS Export utility, i get the following error:

Error source: MS ole db provider for sql server
Error Desc : Null Accessors are not supported by this provider
context: error calling CreateAccessor. Your provider does not support all the interface/methods required by DTS

Please Help

ThanksPost the code for your procedure, which sounds a little suspicious...

Also, make sure you SET NOCOUNT ON at the beginning of your sproc to prevent spurious output that would confuse the DTS utility.|||Here goes the Stored Procedure

CREATE PROCEDURE sp_ProductivityReport_ByMonth

@.start_Date varchar(10),
@.end_Date varchar(10),
@.S_ID int = 25

AS

set nocount on

DECLARE @.@.total1 decimal(20,2)
DECLARE @.@.total2 decimal(20,2)
set @.@.total1 = 0
set @.@.total2 = 0

select B.T_FName as "First_Name", B.T_LName as "Last_Name",
Count(A.JobID) as "Reports_Changed",
CAST((CAST((ROUND(((Sum(A.Job_Length))/480000.00), 2)) AS varbinary(30))) AS decimal(15,2)) AS "Minutes_Changed ",
CAST((CAST((ROUND((((Sum(A.Job_Length))/480000.00)* 10), 2)) AS varbinary(40))) AS decimal(25,2)) as "Lines_Changed"

into #CustomTable1
from dbo.BillingInfo A (nolock), dbo.TInfo B (nolock)
where
A.T_ID = B.T_ID
AND A.SP_ID = @.S_ID
AND A.JDate between @.start_Date and @.end_Date
GROUP BY
A.T_ID, B.T_FName, B.T_LName

DECLARE Total_Cursor CURSOR Local
FOR SELECT Lines_Changed FROM #CustomTable1
OPEN Total_Cursor
FETCH next From Total_Cursor
INTO @.@.total2
while @.@.FETCH_STATUS = 0
BEGIN
SET @.@.total1 = @.@.total1 + @.@.total2
FETCH NEXT FROM Total_Cursor
INTO @.@.total2
END
CLOSE Total_Cursor
DEALLOCATE Total_Cursor

/**** Calculating Total*******/
DECLARE @.Reports_Total int
SELECT @.Reports_Total = sum(Reports_Changed) FROM #CustomTable1

DECLARE @.T_M_Total decimal(15,2)
SELECT @.T_M_Total = sum(Minutes_Changed) FROM #CustomTable1

select "xxx - Total" as First_Name, ' ' as Last_Name, @.Reports_Total as Reports_Changed,
@.T_M_Total as Minutes_Changed, @.@.total1 as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2

set nocount on

SELECT First_Name, Last_Name,Reports_Changed,
Minutes_Changed, Lines_Changed,
Convert(varchar, Cast((Lines_Changed * 100/@.@.total1) AS decimal(5,2))) + '%'
as "Percentage of Total (using Line Counts)"
from #CustomTable1
UNION
SELECT * FROM #CustomTable2
order by First_Name asc

DROP TABLE #CustomTable1

DROP TABLE #CustomTable2
GO

Post the code for your procedure, which sounds a little suspicious...

Also, make sure you SET NOCOUNT ON at the beginning of your sproc to prevent spurious output that would confuse the DTS utility.|||You really need to learn more about TSQL, and principles of database application design before attempting something like this. Why? Because you are taking the wrong approach to solving a problem that, really, you should not be trying to solve with SQL in the first place.

Lets begin...

Dump the cursor and learn how to write set-based logic. This entire sectionDECLARE @.@.total2 decimal(20,2)
set @.@.total2 = 0
.
.
.
DECLARE Total_Cursor CURSOR Local FOR
SELECT Lines_Changed
FROM #CustomTable1

OPEN Total_Cursor
FETCH next From Total_Cursor INTO @.@.total2

while @.@.FETCH_STATUS = 0
BEGIN
SET @.@.total1 = @.@.total1 + @.@.total2
FETCH NEXT FROM Total_Cursor INTO @.@.total2
END

CLOSE Total_Cursor
DEALLOCATE Total_Cursor...can be replace by one line:SET @.@.totall = sum(Lines_Changed) from #CustomTable1

Next, all of these lines and more...SET @.@.totall = sum(Line_Changed) from #CustomTable1
SELECT @.Reports_Total = sum(Reports_Changed) FROM #CustomTable1
SELECT @.T_M_Total = sum(Minutes_Changed) FROM #CustomTable1
.
.
.
select "xxx - Total" as First_Name,
' ' as Last_Name,
@.Reports_Total as Reports_Changed,
@.T_M_Total as Minutes_Changed,
@.@.total1 as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
...can be run as a single statementselect "xxx - Total" as First_Name,
' ' as Last_Name,
sum(Reports_Changed) as Reports_Changed,
sum(Minutes_Changed) as Minutes_Changed,
sum(Lines_Changed) as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
FROM #CustomTable1

Then, I have to ask, what is with the use of the double ampersands?

Lastly, it poor programming practice to be calculating subtotals to a report within SQL, as you are doing with CustomTable2. This is best handled by whatever reporting application you are using. Not because of a limitation within SQL, but because you are essentially mixing record types (raw and total) within a single dataset. Bad form.

So, re-read the Books Online sections on SELECT statements and aggregate queries. And if you find yourself using cursors again, be confident you are doing something wrong because you probably are.

But for the heck of it, go ahead and try this shortened code:CREATE PROCEDURE sp_ProductivityReport_ByMonth
@.start_Date varchar(10),
@.end_Date varchar(10),
@.S_ID int = 25

AS

set nocount on

select B.T_FName as "First_Name",
B.T_LName as "Last_Name",
Count(A.JobID) as "Reports_Changed",
CAST((CAST((ROUND(((Sum(A.Job_Length))/480000.00), 2)) AS varbinary(30))) AS decimal(15,2)) AS "Minutes_Changed ",
CAST((CAST((ROUND((((Sum(A.Job_Length))/480000.00)* 10), 2)) AS varbinary(40))) AS decimal(25,2)) as "Lines_Changed"
into #CustomTable1
from dbo.BillingInfo A (nolock),
dbo.TInfo B (nolock)
where A.T_ID = B.T_ID
AND A.SP_ID = @.S_ID
AND A.JDate between @.start_Date and @.end_Date
GROUP BY A.T_ID,
B.T_FName,
B.T_LName

/**** Calculating Total*******/
select "xxx - Total" as First_Name,
' ' as Last_Name,
sum(Reports_Changed) as Reports_Changed,
sum(Minutes_Changed) as Minutes_Changed,
sum(Lines_Changed) as Lines_Changed,
' ' as Percentage_of_Total
into #CustomTable2
FROM #CustomTable1

SELECT First_Name,
Last_Name,
Reports_Changed,
Minutes_Changed,
Lines_Changed,
Convert(varchar, Cast((Lines_Changed * 100/@.total1) AS decimal(5,2))) + '%' as "Percentage of Total (using Line Counts)"
from #CustomTable1
UNION
SELECT *
FROM #CustomTable2
order by First_Name asc

DROP TABLE #CustomTable1

DROP TABLE #CustomTable2
GO

DTS Export to Excel

I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.
Hi Martin
"Martin" wrote:

> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was to
> use the Delete option in the Transformation window of the wizard. I rebuilt
> the DTS based on that idea, but get an error message about "Deleting data in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John
|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in the
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that using
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way to
> http://www.sqldts.com/200.aspx
> John
|||Hi Martin
"Martin" wrote:

> I cannot rename the spreadsheet because it is tied into other processes. I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is what
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in the
> range name. Subsequent runs of the DTS appear to be using the last line of
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the old
> data. The old data is cleared leaving blank rows, but the new data is still
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John
|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
> I don't think it is the lack of excel that does this, as this also occurs on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
> you package re-creates it the net effect should be ok.
> John
|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/microsoft.public.development.device.drivers/browse_thread/thread/5d0d28403902b7d9/2b95633f7391968c?lnk=raot#2b95633f7391968c
|||Hi Martin
"Martin" wrote:

> I'm sorry to be picky, but I cannot drop the worksheet either. It is part of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John

DTS Export to Excel

I am using a DTS created by the Export Wizard to send data to an Excel
spreadsheet. The problem is that each time the DTS is run, the data gets
appended to the spreadsheet, rather than replaced. I used the Drop and
Create Destination Table option on the Transformation window.
I found an earlier post regarding this same problem. The suggestion was to
use the Delete option in the Transformation window of the wizard. I rebuilt
the DTS based on that idea, but get an error message about "Deleting data in
a linked table is not supported by this ISAM".
Can someone offer some ideas on how to get the data replaced in the Excel
spreadsheet?
Thanks.Hi Martin
"Martin" wrote:

> I am using a DTS created by the Export Wizard to send data to an Excel
> spreadsheet. The problem is that each time the DTS is run, the data gets
> appended to the spreadsheet, rather than replaced. I used the Drop and
> Create Destination Table option on the Transformation window.
> I found an earlier post regarding this same problem. The suggestion was t
o
> use the Delete option in the Transformation window of the wizard. I rebui
lt
> the DTS based on that idea, but get an error message about "Deleting data
in
> a linked table is not supported by this ISAM".
> Can someone offer some ideas on how to get the data replaced in the Excel
> spreadsheet?
> Thanks.
I usually want to rename the spreadsheets when I load data into excel,
therefore I copy/rename a template spreadsheet and then populate that using
activeX scripts see http://www.sqldts.com/292.aspx
If required you can also change the destination filename in a similar way to
http://www.sqldts.com/200.aspx
John|||I cannot rename the spreadsheet because it is tied into other processes. I
need to replace the data that already exists in the spreadsheet.
If it helps, I did some research since my original posting and here is what
I found:
1) When DTS creates the range name in the spreadsheet, it is only the
headings of the data. The data itself is not included in the range name.
Somewhere, the last line of data is being tracked versus the last line in th
e
range name. Subsequent runs of the DTS appear to be using the last line of
data, not the last line in the range.
2) If I manually expand the range name to include the last line of data,
then rerun the DTS, the new data is still appended to the bottom of the old
data. The old data is cleared leaving blank rows, but the new data is still
appended to the bottom; again based on the last line of data. The area
covered by the range name returns to being just the headings.
Could the fact that Excel is not installed on the machine running the DTS
have any bearing?
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I usually want to rename the spreadsheets when I load data into excel,
> therefore I copy/rename a template spreadsheet and then populate that usin
g
> activeX scripts see http://www.sqldts.com/292.aspx
> If required you can also change the destination filename in a similar way
to
> http://www.sqldts.com/200.aspx
> John|||Hi Martin
"Martin" wrote:

> I cannot rename the spreadsheet because it is tied into other processes.
I
> need to replace the data that already exists in the spreadsheet.
> If it helps, I did some research since my original posting and here is wha
t
> I found:
> 1) When DTS creates the range name in the spreadsheet, it is only the
> headings of the data. The data itself is not included in the range name.
> Somewhere, the last line of data is being tracked versus the last line in
the
> range name. Subsequent runs of the DTS appear to be using the last line o
f
> data, not the last line in the range.
> 2) If I manually expand the range name to include the last line of data,
> then rerun the DTS, the new data is still appended to the bottom of the ol
d
> data. The old data is cleared leaving blank rows, but the new data is sti
ll
> appended to the bottom; again based on the last line of data. The area
> covered by the range name returns to being just the headings.
> Could the fact that Excel is not installed on the machine running the DTS
> have any bearing?
> Thanks.
>
I don't think it is the lack of excel that does this, as this also occurs on
my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and if
you package re-creates it the net effect should be ok.
John|||I'm sorry to be picky, but I cannot drop the worksheet either. It is part o
f
a complicated multi-tab file.
Also, the link you provided indicates that Excel must be installed on the
machine running the DTS. I do not believe I can get that approved.
Thanks.
"John Bell" wrote:

> Hi Martin
> "Martin" wrote:
>
> I don't think it is the lack of excel that does this, as this also occurs
on
> my tests. You could drop the worksheet http://www.sqldts.com/245.aspx and
if
> you package re-creates it the net effect should be ok.
> John|||Hi Martin,May I spend your some time to look at the post? Please help
me about St16c550's driver.
You can find the post at:
http://groups.google.com/group/micr...b95633f7391968c|||Hi Martin
"Martin" wrote:

> I'm sorry to be picky, but I cannot drop the worksheet either. It is part
of
> a complicated multi-tab file.
> Also, the link you provided indicates that Excel must be installed on the
> machine running the DTS. I do not believe I can get that approved.
> Thanks.
>
If that is the case I don't think you can't do it with DTS.
I haven't tried an ODBC connection to see if that behaved differently.
If you could use SSIS for this, then it would work!
John

DTS export problem

Hello

i can 't use DTS export via entreprise manager betwen 2 identic server (same os ,same
sqlserver 7 ,same pack et...)

I have this message

The licensing mode don t let to use DTS export ...

Where i find a parameter ?
Where i find a tool to change the licensing mode ?

RegardsControl Panel/Licensingsql

DTS Export Object

I have created a DTS object to import a text. This works fine, however, I
need to find a way to 'generate a script' to be able to recreate this same
DTS in another places.
ArthurArthur,
when you Save As ... the package, in the Location dropdown you get 3
choices. You can save to a server, local or remote, as a structured storage
file or as a vb file. I have not worked with a vb file. But if you save as
a structured storage file, you can copy it to other place and there right
click Local Packages and choose Open.
hth
Quentin
"Arthur C" <arthur.christy@.tamut.edu.delete.me> wrote in message
news:O7gND6lmDHA.1884@.TK2MSFTNGP09.phx.gbl...
> I have created a DTS object to import a text. This works fine, however, I
> need to find a way to 'generate a script' to be able to recreate this same
> DTS in another places.
> Arthur
>

DTS Export format, please help

I have a table (tblstudent) with four fields, Fname, Lname, City, Zip.
I want to run a DTS export nightly that export the records out to a file.
The kick is that I would like the text file to list the fields on their own
line like:
Fname
Lname
City
Zip
Instead of across like: Fname, Lname, City, Zip.
How can I accomplish this?
Thanks for your time!
TonyHopefully this will solve your issue. write the code in Axtivex script in DT
S.
========================================
=========
'***************************************
*********************
' Visual Basic ActiveX Script
'***************************************
*********************
Function Main()
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "sqloledb"
Conn.Properties("Data Source").Value = "(local)"
Conn.Properties("Initial Catalog").Value = "Testing"
Conn.Properties("Integrated Security").Value = "SSPI"
Conn.CommandTimeout = 0
Conn.Open
'--get students record set
SqlQuery ="select * from tblStudent"
Set RsStudent = Conn.Execute(SQLQuery)
'--ourt vars
ourputString = ""
Path = "C:\test\"
'-- create output folder
Set objFSO = CreateObject("Scripting.FileSystemObject")
If Not objFSO.FolderExists(path) Then
objFSO.CreateFolder (path)
End If
'--Create Traget file
Set TargetFile = objFSO.CreateTextFile(Path &"Report.txt", True)
IF not RsStudent.eof Then
While not RsStudent.eof
ourputString=""
ourputString="Fisrt Name: " & RsStudent("FName") & vbcrlf & _
"Last Name:" & RsStudent("Lname") & vbcrlf & _
"City :" & RsStudent("City") & vbcrlf & _
"Zip :" & RsStudent("Zip") & vbcrlf & _
"----
--" & vbcrlf
TargetFile.WriteLine(ourputString)
RsStudent.moveNext
Wend
End If
Set TargetFile=Nothing
Set objFSO=Nothing
Set RsList =Nothing
Conn.close
set conn = nothing
Main = DTSTaskExecResult_Success
End Function
========================================
=========
"tony tuso" wrote:

> I have a table (tblstudent) with four fields, Fname, Lname, City, Zip.
> I want to run a DTS export nightly that export the records out to a file.
> The kick is that I would like the text file to list the fields on their ow
n
> line like:
> Fname
> Lname
> City
> Zip
> Instead of across like: Fname, Lname, City, Zip.
> How can I accomplish this?
> Thanks for your time!
> Tony
>
>

DTS export for MSDE

I need to create a DTS Export job that can be imported to
MSDE via script. But I need to be able to alter the
destination server in the script.
When I use DTS wizard to create the job and create a script
from it. I do not see how I can change the destination
server within the script, because it looks to be encoded.
Any Ideas?
You will need to use the DTS editor in Enterprise Manager to create a global
variable for your server name and make that a parameter to dtsrun. Use the
dtsrunui utility to create the command line with the global variable
parameter. Then add a Dynamic Properties task to your package that sets the
server name from the global variable and make this task the first thing that
runs in your package. Using this method you can set the names of servers,
databases and other values in the package dynamically.
Jim
"Jahnathan Cain" <cainj@.ritchie.disa.mil> wrote in message
news:2a23901c465ea$02b2ee50$a401280a@.phx.gbl...
> I need to create a DTS Export job that can be imported to
> MSDE via script. But I need to be able to alter the
> destination server in the script.
> When I use DTS wizard to create the job and create a script
> from it. I do not see how I can change the destination
> server within the script, because it looks to be encoded.
> Any Ideas?

DTS Export fails with "Invalid Object Name"

I have a database that used to have a table called MALL_Customers. The
table was renamed to tbl_Users, and a view was created called MALL_Customers
which simply selects all rows from tbl_Users, for backwards compatibility
with the web app accessing the database.
The database works fine; the web app works fine. All permissions are set
properly on all objects. The view is accessible via EM, as is the table.
However, when I attempt to run a DTS Export of this database to an empty
database on the same server, I get "invalid object name" on MALL_Customers.
Apparently somewhere, in some trigger, stored proc, or some other object,
there is a reference to MALL_Customers that doesn't recognize the VIEW as a
valid object, but is instead looking for a TABLE with that name. I cannot
find it anywhere, and I don't know where to start.
The overall question then is, how do I find all references to the text
"MALL_Customers" in the database schema so that I can track down why my DTS
package is failing. I have another database with a similar problem, and I
think the solution to this problem will also solve the other -- hidden
references somewhere in the database that cause "invalid object name" errors
when using DTS.
Where do I start?
Thanks in Advance,
Marcyou can search through the system table syscomments which, contains entries
for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint,
and stored procedure
Ex:
select * from syscomments where text like '%MALL_Customers%'
Also you can use stored procedure sp_depends to know dependencies of the
existing object.
Ex:
sp_depends 'MALL_Customers'
--
-Vishal
Marc Funaro <marc@.advantex.net> wrote in message
news:xyK7b.3731$nF5.632@.news02.roc.ny...
> I have a database that used to have a table called MALL_Customers. The
> table was renamed to tbl_Users, and a view was created called
MALL_Customers
> which simply selects all rows from tbl_Users, for backwards compatibility
> with the web app accessing the database.
> The database works fine; the web app works fine. All permissions are set
> properly on all objects. The view is accessible via EM, as is the table.
> However, when I attempt to run a DTS Export of this database to an empty
> database on the same server, I get "invalid object name" on
MALL_Customers.
> Apparently somewhere, in some trigger, stored proc, or some other object,
> there is a reference to MALL_Customers that doesn't recognize the VIEW as
a
> valid object, but is instead looking for a TABLE with that name. I cannot
> find it anywhere, and I don't know where to start.
> The overall question then is, how do I find all references to the text
> "MALL_Customers" in the database schema so that I can track down why my
DTS
> package is failing. I have another database with a similar problem, and I
> think the solution to this problem will also solve the other -- hidden
> references somewhere in the database that cause "invalid object name"
errors
> when using DTS.
> Where do I start?
> Thanks in Advance,
> Marc
>

DTS Export Data Pump appending file

Hi!!
I've a "little" problem...
I must generate a txt file with this structure:

- one header
- n details

Header and details have different structure and i must write header before details.. I've already a table with details.. i could create table for header, but this table design it's too different than details design.. and i can't use the union statement in the output query..
Can i write the header line and write details in append on a txt file??
with data pump i can create and replace file.. but not append??
I know that i could use the filesystem object to modify the file, but i would bypass it with tasks of SQL server..
it's possible?

Thanks!!!

PS: I hope that you understand my problems..
I know poor english.. ;-)Why not post your DDL and DML...

Check out

SELECT Data_Row FROM (
SELECT '"Owner","Job"' As Data_Row, 1 AS Row_Group
UNION ALL
SELECT '"' + l.Name+ '","' + j.Name + '"', 2 AS Row_Group
FROM msdb..sysjobs j
INNER JOIN master..syslogins l
ON l.sid = j.owner_sid
) AS XXX
ORDER BY Row_Group|||thanks..
i've already done in that mode... ;-)sql

DTS export (lincensing problem)

I have a problem with licencing mode in sqlserver when i use I export table
from database to another database on different servers
On the same server the dts export run correctely between 2 database
but on different servers i have this message
The servers are on sqlserver 7 with tha same configuration

The licensing mode of sqlserver don t permit to run the DTS export

Where i can change this parameter

RegardsCall your vendor and buy the licenses. It will automagically change for you.|||Hello

We have buy the licence but the problem is output ?

Dts Export

I am trying to export all the records from 6 different tables from sql server to a text file using the DTS wizard. I want all the info of the first table to appear first, then all the info of the second table, etc.
Any ideas on how to do this type of query?
thanks in advance.Quote
"I am trying to export all the registries from 6 different tables"

what do you mean by registries?|||I think I made the wrong translation...

I ment all the data in each table|||How about
Select 1 as 'Sort' , T1.* from table1 T1
Union
Select 2 as 'Sort' , T2.* from table1 T2
.
.
.
.

order by sort

??|||I get the following error trying to use the UNION operator:

"All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists."

The number and the order of the columns are not identical in all queries...