MSDE 2000 Release A

I want to export or import from MSDE 200 Rel A to Access2003/2000. How can I
do ? Thank alot
hi Quang Hanh,
Quang Hanh wrote:
> I want to export or import from MSDE 200 Rel A to Access2003/2000.
> How can I do ? Thank alot
you can have a look at sp_addlinkedserver system stored procedure...
you can add a linked server pointing to the Access database like following,
where a pesudo copy of the original JET table is created an populated from
the original data
SET NOCOUNT ON
USE master
GO
-- adding linked server
EXEC sp_addlinkedserver
@.server = 'my_JET_db',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'OLE DB Provider for Jet',
@.datasrc = 'D:\NWIND.MDB' -- point to Nortwind JET database
GO
-- use your database.. here just define a new table in tempdb
USE tempdb
GO
CREATE TABLE dbo.MSDE_customers (
CustomerID VARCHAR(60) PRIMARY KEY ,
CompanyName VARCHAR(60) ,
ContactName VARCHAR(60) ,
ContactTitle VARCHAR(60) ,
Address VARCHAR(60) ,
City VARCHAR(60) ,
Region VARCHAR(60) ,
PostalCode VARCHAR(60) ,
Country VARCHAR(60) ,
Phone VARCHAR(60) ,
Fax VARCHAR(60)
)
GO
PRINT 'SELECT from the linked server Jet database'
SELECT TOP 10 * FROM my_JET_db...Customers
PRINT 'Import rows to the MSDE database via INSERT INTO'
INSERT INTO dbo.MSDE_customers SELECT * FROM my_JET_db...Customers
PRINT ''
PRINT 'SELECT from the MSDE database'
SELECT TOP 10 * FROM dbo.MSDE_customers
GO
-- cleanup
DROP TABLE dbo.MSDE_customers
GO
USE master
go
EXEC sp_dropserver 'my_JET_db', 'droplogins'
further information and relative synopsis about sp_addlinkedserver system
stored procedure can be found at
http://msdn.microsoft.com/library/de..._adda_8gqa.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.10.0 - DbaMgr ver 0.56.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

MSDE - Problems connecting using SQL Authentication

I'm trying to use the import/export part of MSDE. When I choose to connect to the server via Windows NT Authentication, everything works fine, assuming I have a connection to the server open (ie VPN connection).

When I chooose SQL Server Authentication, I get the following error:

Client unable to establish connection [CBNMPNTW] ConnectionOpen(CreateFile()).

I need to be able to connect using sql server authentication. Can anyone help me?

Thanks,
StephanieRE:
I'm trying to use the import/export part of MSDE. When I choose to connect to the server via Windows NT Authentication, everything works fine, assuming I have a connection to the server open (ie VPN connection). When I chooose SQL Server Authentication, I get the following error: Client unable to establish connection [CBNMPNTW] ConnectionOpen(CreateFile()). I need to be able to connect using sql server authentication. Can anyone help me?
Thanks,
Stephanie

Q1 Can anyone help me?
A1 Possibly, more information would be helpful and is probably quite necessary. You may well have more than one issue, i.e.(Are you using DSNs, do you have a local DSN(s) configured to use TCP/IP instead of Named Pipes, are you able to tracert / ping your MSDE server with and / or without Windows NT Authentication via your VPN, is your VPN Win NT authentication based, etc., etc.,?).

MSDE - moving a copy of a database and reloading onto another PC

I need to the best way to make a copy(Backup/export) of the data base put in on a CD or e-mail it. Have instructions to import or restore the database onto another PC.
Also in the C:\Program Files\Microsoft SQL Server\80\Tools\Binn>
What and how does dtsrun.exe work?
Thanks
These articles might help ..
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||These articles might help ..
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

MSDE - moving a copy of a database and reloading onto another PC

I need to the best way to make a copy(Backup/export) of the data base put in on a CD or e-mail it. Have instructions to import or restore the database onto another PC.
Also in the C:\Program Files\Microsoft SQL Server\80\Tools\Binn>
What and how does dtsrun.exe work?
Thanks
Please refer to these articles that might help -
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Please refer to these articles that might help -
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

msdb or file system

When I log into Integrated Services on my SQL server, I see [Stored Package] -- File System and MSDB.

When I deploy/import my SSIS packages which should it go under? Is there a difference and if so what the difference?

thanks

HAHAHA! You asked the exact same question as someone else and within something like two hours.

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

Since that question is unanswered, here's the deal.

Where you ultimately choose to store your packages, is up to you. They each do the same thing, but with some differences.

With File System storage, you can simply map a network drive to the server and just copy your packages over to the server. With SQL Server, you need to import into MSDB.

Some like to use File System, but to me, the permissions are harder to work with. Because SSIS does not save passwords, you'll have to use a configuration file to specify passwords for your connection managers. Either that, or you'll have to use EncryptSensitiveWithPassword and then specify a decryption password with the command line.

SQL Server has the same problems unless you tell it to use "SQL Server Roles and Storage" when importing. This is what I choose to use. Access is controlled via SQL Server roles/security.

Search this forum for other ideas regarding file system versus MSDB.|||

LOL

thanks, I'm not a big fan of using the file system for much and due to the permissions within the tool then going through the act of congress to get permissions setup on a network drive to use, isn't worth the hassle.

It would take me longer to get that setup (just from the network side) then it would for me to create a package, test it, import it and roll it out to production and create 10 more.

So I think I'll go the MSDB route.

|||More information:

http://blogs.conchango.com/jamiethomson/archive/2006/02/20/SSIS_3A00_-Deploy-to-file-system-or-SQL-Server.aspx

http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/05/05/13523.aspx|||

thanks, I was just reading the sqljunkies.com one actually.

So far I'm not finding a clear cut answer, I guess it really depends on the person deploying the packages.

its kind of like, what language is better C# or VB.NET? no clear answer, its developer preference.

thanks again