MSDE and DTS packages

Hi all
I am new to the msde and have worked with SQL for a little while. I have developed my database on my SQL server and now have transfered it to MSDE with only one problem.
I have exported the dts package to a text file, but when I import it into MSDE there is nothing there.
My question is can I (and how) create a DTS package through vbscript or osql? I have looked in MSDN and found the dtswiz with command line switchs, but I can not find an example using a text file. I unbderstand that there are a few more switches to tell
it the delimitation.
Any help would be very appreciated.
Thanks for your help in advance.
I'm guessing you want to import dts packages into MSDE (if not my
appologies).
you can save the package as a structured storage file and use a little vb
app to load the package in memory and then save it to the msde using
SaveToSQLServer function of the package class ?
If you have a SQL Server then you can also use enterprise manager to simply
save the DTS packes directly to your msde instance.
Hope this helps.
Ranjeet.
"whiggins" <anonymous@.discussions.microsoft.com> wrote in message
news:8A03C082-9B78-4106-83EF-F2DC7FBD65E6@.microsoft.com...
> Hi all
> I am new to the msde and have worked with SQL for a little while. I have
developed my database on my SQL server and now have transfered it to MSDE
with only one problem.
> I have exported the dts package to a text file, but when I import it into
MSDE there is nothing there.
> My question is can I (and how) create a DTS package through vbscript or
osql? I have looked in MSDN and found the dtswiz with command line switchs,
but I can not find an example using a text file. I unbderstand that there
are a few more switches to tell it the delimitation.
> Any help would be very appreciated.
> Thanks for your help in advance.

MSDE 2000A inner join problems

Hi All
I am using VB6 and MSDE 2000
I previously used Access 2000 and used the following ado sql to create a
temp table in access
(Invoice table contains Invoice data such as Invoice number, date etc.
Idetail table contains details of the invoice individual items e.g product
code, Invoice Number etc) The link is the Invoice Number field in each table
(Master/Detail)
sql = "SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
idetail.price INTO idetailtemp IN '" & App.path & "\data\tramcarstemp.mdb'
FROM idetail inner join [invoice] on idetail.inv_num = invoice.inv_num where
invoice.inv_date between #" & Format(DT1.Value, "m-d-yyyy") & "# and #" &
Format(DT2.Value, "m-d-yyyy") & "#"
cn.execute sql
This worked fine in Access 2000
I am upgrading to MSDE2000 and using the following code in TSql
Private Sub maketable()
Dim a, fso As New FileSystemObject
Dim x As Integer
Set a = fso.CreateTextFile(App.path & "\sql\BackupTramcars.sql", True)
a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy', 'true'")
a.WriteLine ("GO")
a.WriteLine ("USE " & "Tramcars")
a.WriteLine ("SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
idetail.price INTO idetailtemp")
a.WriteLine ("FROM idetail inner join [invoice] on idetail.inv_num =
invoice.inv_num")
a.WriteLine ("where invoice.inv_date between '" &
Format(frmorderhistory.DT1.Value, "m-d-yyyy") & "' and '" &
Format(frmorderhistory.DT2.Value, "m-d-yyyy") & "'")
a.WriteLine ("GO")
a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy', 'false'")
a.WriteLine ("GO")
a.Close
Set a = Nothing
End Sub
The table is created but the inserted invoice dates are only ever 1 of 2
dates
Also when I read from the table to graph the data with
sql = "Select inv_date, sum(qty) as amount from [idetailtemp] where
[prod_code] = '" &
frmorderhistory.maingrid.TextMatrix(frmorderhistor y.maingrid.Row, 0) & "'
group by [inv_date]"
rs.Open sql, cn, adOpenDynamic, adLockReadOnly
There are several records in the recordset but calling rs.recordcount = -1,
which didn't happen in Access 2K
Any ideas
Steve
hi Steve,
steve wrote:
> Hi All
> ...
> Private Sub maketable()
> Dim a, fso As New FileSystemObject
> Dim x As Integer
> Set a = fso.CreateTextFile(App.path & "\sql\BackupTramcars.sql", True)
> a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy',
> 'true'") a.WriteLine ("GO")
> a.WriteLine ("USE " & "Tramcars")
> a.WriteLine ("SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
> idetail.price INTO idetailtemp")
> a.WriteLine ("FROM idetail inner join [invoice] on idetail.inv_num =
> invoice.inv_num")
> a.WriteLine ("where invoice.inv_date between '" &
> Format(frmorderhistory.DT1.Value, "m-d-yyyy") & "' and '" &
> Format(frmorderhistory.DT2.Value, "m-d-yyyy") & "'")
> a.WriteLine ("GO")
> a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy',
> 'false'") a.WriteLine ("GO")
> a.Close
>
> The table is created but the inserted invoice dates are only ever 1
> of 2 dates
please use the ISO format when referencing dates... dates can be confusing,
but if you refer to them in the 'YYYYMMDD' or 'YYYY-MM-DD' format you will
be out of troubles.. please have a look at
http://www.windowsitpro.com/SQLServe...9147/9147.html ,
http://www.karaszi.com/SQLServer/info_datetime.asp for further info..

> Also when I read from the table to graph the data with
> sql = "Select inv_date, sum(qty) as amount from [idetailtemp] where
> [prod_code] = '" &
> frmorderhistory.maingrid.TextMatrix(frmorderhistor y.maingrid.Row, 0)
> & "' group by [inv_date]"
> rs.Open sql, cn, adOpenDynamic, adLockReadOnly
> There are several records in the recordset but calling rs.recordcount
> = -1, which didn't happen in Access 2K
this depends on the MDAC recordset type... the recordcount value will be
available when the recordset has been fully populated, that's to say as soon
as you perform a .MoveLast operation.. (BTW... .Movelast can be time
consuming as all rows must be fetched and transferred)..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea
Thanks. Worked a treat
Steve
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3k76maFt35b8U1@.individual.net...
> hi Steve,
> steve wrote:
> please use the ISO format when referencing dates... dates can be
> confusing, but if you refer to them in the 'YYYYMMDD' or 'YYYY-MM-DD'
> format you will be out of troubles.. please have a look at
> http://www.windowsitpro.com/SQLServe...9147/9147.html ,
> http://www.karaszi.com/SQLServer/info_datetime.asp for further info..
>
> this depends on the MDAC recordset type... the recordcount value will be
> available when the recordset has been fully populated, that's to say as
> soon as you perform a .MoveLast operation.. (BTW... .Movelast can be time
> consuming as all rows must be fetched and transferred)..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

MSDE 2000 changing disablenetworkprotocols after installation

Hi All
I wasn't aware of the security feature of MSDE, whereby it doesn't allow the
TCP/IP(?) network protocol to work and allow a networked PC to connect to an
SQL DB running on a MSDE installation on another PC. It only allows the
'host' PC to connect to the SQL DB.
I've sussed this in my MSDE installer so that it enables the network feature
when it installs, but I have a user who already has MSDE installed and
running with the protocol disabled and now that they are in the process of
connecting and using a 2nd PC I don't want to ask them to detach their db,
re-install MSDE with the right flag and then re-attach their db, as our
relationship is strained as it is.
Is there anyway to re-set/patch this flag on an installed version?
Thanks
Robbie
hi Robbie,
Astra wrote:
>..
> I've sussed this in my MSDE installer so that it enables the network
> feature when it installs, but I have a user who already has MSDE
> installed and running with the protocol disabled and now that they
> are in the process of connecting and using a 2nd PC I don't want to
> ask them to detach their db, re-install MSDE with the right flag and
> then re-attach their db, as our relationship is strained as it is.
> Is there anyway to re-set/patch this flag on an installed version?
please have a look at
http://support.microsoft.com/default...b;en-us;285097 in the part
regarding the Windows registry modification..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

MSDE 2000 backups and restores

Hi All
I am new to MSDE (from access)
I have chosen simple recovery mode for MSDE Database and do a full backup
each day then subsequent backups within the same day using differential
backups
The problem is I don't really understand how the differential backup should
work
1. Do you use a different file name for the Full and Differential backups?
e.g
BACKUP DATABASE TRAMCARS TO DISK = N'C:\Backup\Tramcars.bak' WITH INIT,
NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT, NAME = 'Tramcars Diff Backup'
GO
BACKUP DATABASE TRAMCARS TO DISK = N'C:\Backup\Tramcarsdiff.bak' WITH INIT,
differential,
NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT,
NAME = 'Tramcars Diff Backup'
GO
2 Do you use different file names for each differential backups during the
day or append each diff backup to the previous?
Because the literature shows TSQL code for Restoring includes reference to a
File = 1 (or 2 etc)
Regards
Steve
hi Steve,
> The problem is I don't really understand how the differential backup
> should work
> 1. Do you use a different file name for the Full and Differential
> backups? e.g
> BACKUP DATABASE TRAMCARS TO DISK = N'C:\Backup\Tramcars.bak' WITH
> INIT, NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT, NAME = 'Tramcars Diff
> Backup'
> GO
> BACKUP DATABASE TRAMCARS TO DISK = N'C:\Backup\Tramcarsdiff.bak'
> WITH INIT, differential,
> NOUNLOAD, NOSKIP, STATS = 10, NOFORMAT,
> NAME = 'Tramcars Diff Backup'
> GO
correct... use 2 different files OR "init" the backup set with the full
backup and append to it the successive differential..
if you choos to have only 1 backup file, when restoring you have to specify
the
RESTORE DATABASE [xxx] FROM DISK = N'C:\xxxx.bak'
WITH FILE = n ,
......
where n is 1 for the very first (complete full backup) and then apply the
differential
WITH FILE = n , -- where n=2

> 2 Do you use different file names for each differential backups
> during the day or append each diff backup to the previous?
> Because the literature shows TSQL code for Restoring includes
> reference to a File = 1 (or 2 etc)
this is up to you... on how you "store" backup sets, how long you have to
store them, your internal policies, the actual media hosting the backups...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea
Again thanks for your reliable input
Steve
Regards
Steve
"Andrea Montanari" wrote:

> hi Steve,
> correct... use 2 different files OR "init" the backup set with the full
> backup and append to it the successive differential..
> if you choos to have only 1 backup file, when restoring you have to specify
> the
> RESTORE DATABASE [xxx] FROM DISK = N'C:\xxxx.bak'
> WITH FILE = n ,
> ......
> where n is 1 for the very first (complete full backup) and then apply the
> differential
> WITH FILE = n , -- where n=2
>
> this is up to you... on how you "store" backup sets, how long you have to
> store them, your internal policies, the actual media hosting the backups...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>
sql