Create a txt file and rename the file from a SP

I can populate a txt file using DTS.
How do i Rename the file using data from the table using
DTS or a stored procedure?You could rename the file using DTS :-
http://www.sqldts.com/default.aspx?292
You could also use the xp_cmdshell stored proc
--
HTH
Ryan Waight, MCDBA, MCSE
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:092701c3a51d$bc93bfe0$a401280a@.phx.gbl...
> I can populate a txt file using DTS.
> How do i Rename the file using data from the table using
> DTS or a stored procedure?

Create a SP that will launch a DTS package

Hi all. How can I create a Stored Procedure that will launch a DTS package that I have already built dynamically ?

I have a string already built and I need to put it in a SP because my application is php (linux) and the database is SQL Server on a Windows machine.

I will use php to execute the stored proc, which is the only way to access it.

My string looks like this :
C:\Progra~1\Micros~3\80\Tools\Binn\ISQL.EXE -S [MyServer] -U [Username] -P [Password] -Q "ISQL_Batch 'D:\DDFIImporte\IMPICAFI.bat [user] [Schema] [Pwd] '" -n -d [database]

(Words in [] are only to show that I will put other values)

Thanks

CFGillesxp_cmdshell but it will have to execute with sysadmin permissions.|||Hi,

Can you be more specific ?? I'm a near-newby in SP building... Maybe some pseudo-code, some links or examples ?

Thanks

CFGillessql

Create a DTS package to retrieve records from db & schedule to send the email at

hi,
i would like to create a DTS package to retrieve records from database , this records i retrieve is from the error log table ( ERROR_LOG_TB),the scheduler will run at 9 am daily and will retrieve the records if there is a error and the error information will be capsulate and sent through email.
Can i know how to know how to graphically do in DTS ? i am running SQL Server 2000.The thing that you are looking for can be done thru a "execute sql task" and using xp_sendmail. do you have any prior experience with DTS?
-rohit|||i got some prior experience in DTS but jus not sure how to do this in graphically . programming wise i know how to do it , i just want to get some knowledge how to do it in DTS|||Get in touch with http://www.sqldts.com website which has got plenty of DTS resources and code examples.

Also refer to books online for information about DTS as a first hand help.sql

Create a Condition for a DTS Task

I simply want an email to go out if any records in a table exist, else no email. I have other steps completed in this DTS job but this is the last step. Any ideas?
ddaveNever mind. I decided it be best to drop it into a stored procedure.

ddave

crashing DTS on table import

Hi
I am having some trouble importing certain tables from an odbc database
(Sage Line 50). Most of the tables import without any trouble whatsoever,
however there are 2 tables (SOP_items and POP_items) that always crash dts
when it tries to import them. The DTS screen simply dissappears from the
screen with no error. I realise that it is beyond the scope of this
newsgroup to ask about the specific odbc database. However I was wondering
is there a log somewhere which might help me to shed light on what in the
tables is making it crash. To note I can import and link to the tables in
access with no trouble whatsoever. Thanks for your time.
Dan
There will be no DTS log because it sounds as though something is crashing
DTS. Have you looked in Event Viewer?
Have you tried SPing (What SP are you running?)?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
> wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>
|||Thanks,
It has SP3 on it, just looking at the mdac 2.7 sp1, see if this sorts it.
Dan
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eVWcPREwEHA.2568@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> There will be no DTS log because it sounds as though something is crashing
> DTS. Have you looked in Event Viewer?
> Have you tried SPing (What SP are you running?)?
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
in
>
|||Change of plan, going to install Microsoft Data Access Components (MDAC)
2.8, didn't see the newer version at first. I already have SP3 of SQL, it
does advise that SP3a is only for versions below SP3, and as such I
shouldn't install it? Looked in the event log to see if there is any log of
an error, but there is nothing whatsoever. Thanks.
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>
|||MDAC 2.8 doesn't seem to have made any difference. Any ideas?
Thanks
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
> Change of plan, going to install Microsoft Data Access Components (MDAC)
> 2.8, didn't see the newer version at first. I already have SP3 of SQL, it
> does advise that SP3a is only for versions below SP3, and as such I
> shouldn't install it? Looked in the event log to see if there is any log
of[vbcol=seagreen]
> an error, but there is nothing whatsoever. Thanks.
> Dan
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
> wondering
the[vbcol=seagreen]
in
>
|||Since this is an ODBC data source, you can try to turn on
ODBC tracing on the box where the package runs. Make sure to
turn the trace off after you have crashed with tracing
turned on. You can then go through the tracing log file and
look for ODBC errors.
-Sue
On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
<it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:

>MDAC 2.8 doesn't seem to have made any difference. Any ideas?
>Thanks
>Dan
>"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
>news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
>of
>whatsoever,
>dts
>the
>the
>in
>
|||Hi
Thanks for the responses, I think that I have worked out where the problem
was occuring. There were date columns in those two tables which were
causing the crash. The dates were in the form dd/mm/yyyy as well as
mm/dd/yyyy, so no wonder it had trouble with the data - what a wonderful
accounting package, if only it used SQL as the native database! Thanks for
all of your help
Dan
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:gu1fo0h2h1n4j4f3pnkgh8u2lfvl3h3tth@.4ax.com... [vbcol=seagreen]
> Since this is an ODBC data source, you can try to turn on
> ODBC tracing on the box where the package runs. Make sure to
> turn the trace off after you have crashed with tracing
> turned on. You can then go through the tracing log file and
> look for ODBC errors.
> -Sue
> On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
> <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:
(MDAC)[vbcol=seagreen]
it[vbcol=seagreen]
log[vbcol=seagreen]
database[vbcol=seagreen]
crash[vbcol=seagreen]
tables
>

crashing DTS on table import

Hi
I am having some trouble importing certain tables from an odbc database
(Sage Line 50). Most of the tables import without any trouble whatsoever,
however there are 2 tables (SOP_items and POP_items) that always crash dts
when it tries to import them. The DTS screen simply dissappears from the
screen with no error. I realise that it is beyond the scope of this
newsgroup to ask about the specific odbc database. However I was wondering
is there a log somewhere which might help me to shed light on what in the
tables is making it crash. To note I can import and link to the tables in
access with no trouble whatsoever. Thanks for your time.
DanThere will be no DTS log because it sounds as though something is crashing
DTS. Have you looked in Event Viewer?
Have you tried SPing (What SP are you running?)?
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
> wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>|||Thanks,
It has SP3 on it, just looking at the mdac 2.7 sp1, see if this sorts it.
Dan
"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message
news:eVWcPREwEHA.2568@.TK2MSFTNGP11.phx.gbl...
> There will be no DTS log because it sounds as though something is crashing
> DTS. Have you looked in Event Viewer?
> Have you tried SPing (What SP are you running?)?
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> www.konesans.com - Consultancy from the people who know
>
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
>|||Change of plan, going to install Microsoft Data Access Components (MDAC)
2.8, didn't see the newer version at first. I already have SP3 of SQL, it
does advise that SP3a is only for versions below SP3, and as such I
shouldn't install it? Looked in the event log to see if there is any log of
an error, but there is nothing whatsoever. Thanks.
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
> Hi
> I am having some trouble importing certain tables from an odbc database
> (Sage Line 50). Most of the tables import without any trouble whatsoever,
> however there are 2 tables (SOP_items and POP_items) that always crash dts
> when it tries to import them. The DTS screen simply dissappears from the
> screen with no error. I realise that it is beyond the scope of this
> newsgroup to ask about the specific odbc database. However I was
wondering
> is there a log somewhere which might help me to shed light on what in the
> tables is making it crash. To note I can import and link to the tables in
> access with no trouble whatsoever. Thanks for your time.
> Dan
>|||MDAC 2.8 doesn't seem to have made any difference. Any ideas?
Thanks
Dan
"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
> Change of plan, going to install Microsoft Data Access Components (MDAC)
> 2.8, didn't see the newer version at first. I already have SP3 of SQL, it
> does advise that SP3a is only for versions below SP3, and as such I
> shouldn't install it? Looked in the event log to see if there is any log
of
> an error, but there is nothing whatsoever. Thanks.
> Dan
> "IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
> news:OgXbKTBwEHA.3976@.TK2MSFTNGP09.phx.gbl...
whatsoever,[vbcol=seagreen]
dts[vbcol=seagreen]
the[vbcol=seagreen]
> wondering
the[vbcol=seagreen]
in[vbcol=seagreen]
>|||Since this is an ODBC data source, you can try to turn on
ODBC tracing on the box where the package runs. Make sure to
turn the trace off after you have crashed with tracing
turned on. You can then go through the tracing log file and
look for ODBC errors.
-Sue
On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
<it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:

>MDAC 2.8 doesn't seem to have made any difference. Any ideas?
>Thanks
>Dan
>"IT Dep" <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote in message
>news:e6wXQ2LwEHA.1296@.TK2MSFTNGP10.phx.gbl...
>of
>whatsoever,
>dts
>the
>the
>in
>|||Hi
Thanks for the responses, I think that I have worked out where the problem
was occuring. There were date columns in those two tables which were
causing the crash. The dates were in the form dd/mm/yyyy as well as
mm/dd/yyyy, so no wonder it had trouble with the data - what a wonderful
accounting package, if only it used SQL as the native database! Thanks for
all of your help
Dan
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:gu1fo0h2h1n4j4f3pnkgh8u2lfvl3h3tth@.
4ax.com...
> Since this is an ODBC data source, you can try to turn on
> ODBC tracing on the box where the package runs. Make sure to
> turn the trace off after you have crashed with tracing
> turned on. You can then go through the tracing log file and
> look for ODBC errors.
> -Sue
> On Tue, 2 Nov 2004 09:34:59 -0000, "IT Dep"
> <it@.isslimREMOVE_TO_REPLYited.co.uk> wrote:
>
(MDAC)[vbcol=seagreen]
it[vbcol=seagreen]
log[vbcol=seagreen]
database[vbcol=seagreen]
crash[vbcol=seagreen]
tables[vbcol=seagreen]
>

counting records

Hi all,
I have a DTS package that gets information out of a Pervasive db and
drops it into SQL.
I need a count on how many distinct loan number are associated with
specific actions (ie Closing Date, Funded Date etc).
I have 2 tables: 1 contains a reference number and description (ie 170
= Closing Cost, 210 = Funded Date etc) and another table that contains
the reference number and loan number.
So I need a result set that says 20 loan have a closing date, 400 loans
have a funded date etc. There are over 590 reference numbers and over
1.5 million records.
Any help/advice would be immensely apprieciated!!
Thanks,
TonyTry,
select r.ref_desc, count(*) as cnt
from reference as r inner join loan as l
on r.ref_id = l.ref_id
group by r.ref_desc
go
AMB
"Tony" wrote:

> Hi all,
> I have a DTS package that gets information out of a Pervasive db and
> drops it into SQL.
> I need a count on how many distinct loan number are associated with
> specific actions (ie Closing Date, Funded Date etc).
> I have 2 tables: 1 contains a reference number and description (ie 170
> = Closing Cost, 210 = Funded Date etc) and another table that contains
> the reference number and loan number.
> So I need a result set that says 20 loan have a closing date, 400 loans
> have a funded date etc. There are over 590 reference numbers and over
> 1.5 million records.
> Any help/advice would be immensely apprieciated!!
> Thanks,
> Tony
>|||Hi Tony
You don't post ddl but you can do something like (untested):
SELECT [loan number], SUM(CASE WHEN [Closing Date] IS NOT NULL THEN 1 ELSE 0
END) AS NumClosed
SUM(CASE WHEN [Funded Date]IS NOT NULL THEN 1 ELSE 0 END) AS NumFunded
FROM #MyTable
GROUP BY [loan number]
John
"Tony" wrote:

> Hi all,
> I have a DTS package that gets information out of a Pervasive db and
> drops it into SQL.
> I need a count on how many distinct loan number are associated with
> specific actions (ie Closing Date, Funded Date etc).
> I have 2 tables: 1 contains a reference number and description (ie 170
> = Closing Cost, 210 = Funded Date etc) and another table that contains
> the reference number and loan number.
> So I need a result set that says 20 loan have a closing date, 400 loans
> have a funded date etc. There are over 590 reference numbers and over
> 1.5 million records.
> Any help/advice would be immensely apprieciated!!
> Thanks,
> Tony
>|||Hi John,
I thought of this but I have over 590 reference fields...don't feel
like writing a sum/case for each one|||Hi John,
I thought of this but I have over 590 reference fields...don't feel
like writing a sum/case for each one|||On 26 Oct 2005 12:23:47 -0700, Tony wrote:

>Hi John,
>I thought of this but I have over 590 reference fields...don't feel
>like writing a sum/case for each one
Hi Tony,
Did you try Allejandro's suggestion?
If that doesn't help you, then please refer to www.aspfaq.com/5006 to
find out what extra information you need to post in order to help us
help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi
590 columns in a table seems a large number. It is already causing you
problems by creating unwillingness try a solution!
You can use the information_schema.columns to create the sql for you.
e.g.
USE NORTHWIND
SELECT 'CASE WHEN ' + QUOTENAME (COLUMN_NAME) + ' IS NULL THEN 0 ELSE 1
END AS [SUM_' + COLUMN_NAME + '],'
from informatioN_schema.columns
where table_name = 'orders'
ORDER BY ORDINAL_POSITION
John
Tony wrote:
> Hi John,
> I thought of this but I have over 590 reference fields...don't feel
> like writing a sum/case for each one