Import Excel Spreadsheet

I am using TransferSpreadsheet from an ADP file to import an Excel
spreadsheet into an MSDE database. On one computer, when I run the code to
import the spreadsheet, it prompts for an SQLServer login. The same user ca
n
try it on a different computer and it will work fine. Data in the tables of
the database can be viewed and edited on the bad computer. But the
spreadsheet cannot be imported. I even tried the Get External Data->Import
menu option and it prompts for SQLServer login also.
Any ideas on what to look at on this computer to determine the problem?
Or any suggestions on a better way to import a spreadsheet into an SQLServer
database.?
Thanks!
JerryJerry..
you have to tell us what is the error that you get when you import.
What I usually do (if its only a few thousand rows to import), then I go
forr opendatasource(ODS). Check out in BOL for ODS and they give an example
of select from excel. U can then insert into the table in SQL Server.
But to investigate on why this error is occuring, you need to give us more
info on why its not getting imported.
--
"JerryWendell" wrote:

> I am using TransferSpreadsheet from an ADP file to import an Excel
> spreadsheet into an MSDE database. On one computer, when I run the code t
o
> import the spreadsheet, it prompts for an SQLServer login. The same user
can
> try it on a different computer and it will work fine. Data in the tables
of
> the database can be viewed and edited on the bad computer. But the
> spreadsheet cannot be imported. I even tried the Get External Data->Impor
t
> menu option and it prompts for SQLServer login also.
> Any ideas on what to look at on this computer to determine the problem?
> Or any suggestions on a better way to import a spreadsheet into an SQLServ
er
> database.?
> Thanks!
> Jerry
>|||Thanks for the responds.
This only happens on one particular computer. It works fine on the 8 other
computers that access the database. They are all running Access 2003 and
Windows XP.
When I try to import the spreadsheet, I am prompted to enter an SQL Server
Login. When I enter a valid login (I know it is a valid login because it
works from the other computers) after about a minute I get the following
error messages:
Connection Failed
SQLState: '01S00'
SQLServerError: 0
[Microsoft][ODBC SQLServerDriver][dbnmpntw]Invalid Connection String Attribute
SQLState: '01000'
SQLServerError: 2
[Microsoft][ODBC SQLServerDriver][dbnmpntw]ConnectionOpen
(CreateFile())
SQLState: '08001'
SQLServerError: 6
[Microsoft][ODBC SQLServerDriver][dbnmpntw]Specified SQL Server not found
Thanks for your help.
Jerry
"Omnibuzz" wrote:
> Jerry..
> you have to tell us what is the error that you get when you import.
> What I usually do (if its only a few thousand rows to import), then I go
> forr opendatasource(ODS). Check out in BOL for ODS and they give an exampl
e
> of select from excel. U can then insert into the table in SQL Server.
> But to investigate on why this error is occuring, you need to give us more
> info on why its not getting imported.
> --
>
>
> "JerryWendell" wrote:
>sql

import excel files

how can I import excel files into msde 2000
Hi ,
MSDE does not support DTS and other advanced features supported by the
regular versions of sql server.
But a work around is to get a evaluation CD of sql server and then install
the client components of sql server . Once it is installed sucessfully you
can use the import export wizard to export data into the msde instance.
Hopefully this answers your question
Girish Sundaram
This posting is provided "AS IS" with no warranties, and confers no rights.
|||hi,
"TJS" <nospam@.here.com> ha scritto nel messaggio
news:10qa413mkh5j0c5@.corp.supernews.com
> how can I import excel files into msde 2000
you can set up a linked server to the xls file and get relevant data into
your MSDE table...
consider having a d:\Students.xls file with 2 columns, FirstName and
LastName ...
SET NOCOUNT ON
CREATE TABLE dbo.Students (
FirstName VARCHAR(10) ,
LastName VARCHAR(10)
)
GO
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\Students.xls',
NULL,
'Excel 5.0'
GO
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'sa', 'Admin', NULL
GO
SELECT * FROM ExcelSource...[Foglio1$] -- that's [Sheet1$] for English
version :D
-- import into your base table...
INSERT INTO dbo.Students SELECT * FROM ExcelSource...[Foglio1$] -- that's
[Sheet1$] for English version :D
SELECT * FROM dbo.Students
GO
-- final clean up, dropping the linked server and the base table
EXEC sp_dropserver 'ExcelSource', 'droplogins'
GO
DROP TABLE dbo.Students
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||How do you create the 'ExcelSource'
|||hi,
"TJS" <nospam@.here.com> ha scritto nel messaggio
news:10qhkoi9ittcl93@.corp.supernews.com
> How do you create the 'ExcelSource'
the 'ExcelSource' just defines a linked server, pointing to the file system
specified Excel file, using the JET OLE DB provider...
but perhaps I do not understand your question
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

import Data

Is it possible to import Access97 data into a MSDE database table?
Hi Munster,
See the article to load data using DTS:-
http://support.microsoft.com/kb/250616/EN-US/
Alternatevely, you could export the data from Access to a text file and use
BCP IN command to load into MSDE.
Thanks
Hari
SQL Server MVP
"Munster" <Munster@.discussions.microsoft.com> wrote in message
news:CD15C4D6-CCD2-49F5-BB66-3C9A2CA0B607@.microsoft.com...
> Is it possible to import Access97 data into a MSDE database table?
>

Import and Schedule DTS on MSDE

I have .dts file from another sql server. I would like to import and install
this file as a DTS package on MSDE and schedule it to run periodically.
How do I do this in MSDE 2000.
You can use DTSrun.exe
Look at
http://groups.google.it/groups?q=DTS...hx.gbl&rnum=18
"inquisite" <inquisite@.discussions.microsoft.com> wrote in message
news:392C38CD-4DB3-4D39-AF2A-0A14CED0D74F@.microsoft.com...
> I have .dts file from another sql server. I would like to import and
install
> this file as a DTS package on MSDE and schedule it to run periodically.
> How do I do this in MSDE 2000.
|||Will it be possible for anyone of you to help me with a example, for the syntax to use in command prompt with DTSrun.exe.
The situation is this. I need to uninstall SQL Server and install MSDE for my client. I have DTS running already in SQL Server. I need to take a back up, and make it run in MSDE. The package name is dtsPack.dts. I have it in the location "c:\"
Thanks for your help|||Here's the section from Books Online, including examples at the end:
The dtsrun utility executes a package created using Data Transformation Services (DTS). The DTS
package can be stored in the Microsoft SQL ServerT msdb database, a COM-structured storage file, or
SQL Server Meta Data Services.
Syntax
dtsrun
[/?] |
[
[
/[~]S server_name[\instance_name]
{ {/[~]U user_name [/[~]P password]} | /E }
]
{
{/[~]N package_name }
| {/[~]G package_guid_string}
| {/[~]V package_version_guid_string}
}
[/[~]M package_password]
[/[~]F filename]
[/[~]R repository_database_name]
[/A global_variable_name:typeid=value]
[/L log_file_name]
[/W NT_event_log_completion_status]
[/Z] [/!X] [/!D] [/!Y] [/!C]
]
Arguments/?
Displays the command prompt options.
~
Specifies that the parameter to follow is hexadecimal text representing the encrypted value of the
parameter. Can be used with the /S, /U, /P, /N, /G, /V, /M, /F, and /R options. Using encrypted
values increases the security of the command used to execute the DTS package because the server
name, password, and so on, are not visible. Use /!Y to determine the encrypted command.
/S server_name[\instance_name]
Specifies the instance of SQL Server to connect to. Specify server_name to connect to the default
instance of SQL Server on that server. Specify server_name\instance_name to connect to a named
instance of SQL Server 2000 on that server.
/U user_name
Is a login ID used to connect to an instance of SQL Server.
/P password
Is a user-specified password used with a login ID.
/E
Specifies a trusted connection (password not required).
/N package_name
Is the name of a DTS package assigned when the package was created.
/G package_guid_string
Is the package ID assigned to the DTS package when it was created. The package ID is a GUID.
/V package_version_guid_string
Is the version ID assigned to the DTS package when it was first saved or executed. A new version ID
is assigned to the DTS package each time it is modified. The version ID is a GUID.
/M package_password
Is an optional password assigned to the DTS package when it was created.
/F filename
Is the name of a structured storage file containing DTS packages. If server_name is also specified,
the DTS package retrieved from SQL Server is executed and that package is added to the structured
storage engine.
/R repository_database_name
Is the name of the repository database containing DTS packages. If no name is specified, the default
database name is used.
/A global_variable_name:typeid=value
Specifies a package global variable, where typeid = type identifier for the data type of the global
variable. The entire argument string can be quoted. This argument can be repeated to specify
multiple global variables. See the Remarks section for the different available type identifiers
available with global variables.
To set global variables with this command switch, you must have either Owner permission for the
package or the package must have been saved without DTS password protection enabled. If you do not
have Owner permission, you can specify global variables, but the values used will be those set in
the package, not those specified with the /A command switch.
/L log_file_name:
Specifies the name of the package log file.
/W Windows_Event_Log
Specifies whether or not to write the completion status of the package execution to the Windows
Application Log. Specify True or False.
/Z
Indicates that the command line for dtsrun is encrypted using SQL Server 2000 encryption.
/!X
Blocks execution of the selected DTS package. Use this command parameter when you want to create an
encrypted command line without executing the DTS package.
/!D
Deletes the DTS package from an instance of SQL Server. The package is not executed. It is not
possible to delete a specific DTS package from a structured storage file. The entire file needs to
be overwritten using the /F and /S options.
/!Y
Displays the encrypted command used to execute the DTS package without executing it.
/!C
Copies the command used to execute the DTS package to the Microsoft Windows clipboard. This option
can also be used in conjunction with /!X and /!Y.
Remarks
Security Note Batch files may contain credentials stored in plain text. Credentials may be echoed
to the user's screen during batch execution.
If you do not specify any command line switches, specify an incorrect command line switch, or your
command statement contains a syntax error, dtsrun returns error information and usage instructions.
If you enter dtsrunui on the command line without any command line switches, you will start the DTS
Run utility.
Spaces between command switches and values are optional. Embedded spaces in values must be embedded
between double quotation marks.
If an option is specified multiple times, the last occurrence takes precedence. One exception is the
/A command switch. Specifying more than one /A switch creates multiple global variables.
When specifying a global variable with the /A command switch, you must use a type identifier to
indicate the data type of the global variable.
A tilde (~) character after the forward slash (for example, /~Z) indicates that the parameter value
is encrypted and what follows is the hexadecimal text of the encrypted value.
The table shows the global variable data types and their IDs.
Data type Type ID
Integer (small) 2
Integer 3
Real (4-byte) 4
Real (8-byte) 5
Currency 6
Date 7
String 8
Boolean 11
Decimal 14
Integer (1-byte) 16
Unsigned int (1-byte) 17
Unsigned int (2-byte) 18
Unsigned int (4-byte) 19
Integer (8-byte) 20
Unsigned int (8-byte) 21
Int 22
Unsigned int 23
HRESULT 25
Pointer 26
LPSTR 30
LPWSTR 31
For information about where to find or how to run this utility, see Getting Started with Command
Prompt Utilities.
Examples
To execute a DTS package saved as a COM-structured storage file, use:
dtsrun /Ffilename /Npackage_name /Mpackage_password
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
To execute a DTS package saved in Meta Data Services, use:
dtsrun /Sserver_name /Uuser_nrame /Ppassword /Npackage_name /Rrepository_name
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ngaya" <ngaya.1pivm3@.mail.mcse.ms> wrote in message news:ngaya.1pivm3@.mail.mcse.ms...
> Will it be possible for anyone of you to help me with a example, for the
> syntax to use in command prompt with DTSrun.exe.
> The situation is this. I need to uninstall SQL Server and install MSDE
> for my client. I have DTS running already in SQL Server. I need to take
> a back up, and make it run in MSDE. The package name is dtsPack.dts. I
> have it in the location "c:\"
> Thanks for your help
>
> --
> ngaya
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1244207.html
>
|||THanks a lot for your time. I still have one more question to be clarified. Should i save the DTS in the database msdb? or will it automatically get saved? Ok if i save it, should i attach the database fresh to the MSDE?
Thanks in advance
Ngaya|||Thanks...I got it at last...I took a backup of msdb database and restored in MSDE, and tried
exec msdb..sp_enum_dtspackages
go
to see the list and it was there.
I was able to run the pacakge successfully using dtsrun.
Phew!...this took a lot of my timelah!... If i had known the simple thing that dts packages are stored in msdb database...this would have been over long back...thanks for that info..
ngaya|||You can also open a package on the originating server and save as file, copy file, open file on dest
server and save to msdb.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ngaya" <ngaya.1pm9u1@.mail.mcse.ms> wrote in message news:ngaya.1pm9u1@.mail.mcse.ms...
> Thanks...I got it at last...I took a backup of msdb database and
> restored in MSDE, and tried
> exec msdb..sp_enum_dtspackages
> go
> to see the list and it was there.
> I was able to run the pacakge successfully using dtsrun.
> Phew!...this took a lot of my timelah!... If i had known the simple
> thing that dts packages are stored in msdb database...this would have
> been over long back...thanks for that info..
> ngaya
>
> --
> ngaya
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1244207.html
>

Import Access DB into SQL with OSQL?

Hello all.

I was wondering if there is a way to import an Access database into an MS SQL (MSDE) database using OSQL? I need to write an updater to send out to some customers, that will accomplish this. The Access database and its corresponding SQL database have the same table structure, i just need to update the data. I would like to use OSQL so I can make this process automated, behind the scenes of my GUI updater, for the customer.

I would love to be able to do something like this:

osql -E -Q"IMPORT DATABASE blabla FROM DISK = 'C:\blabla.mdb' WITH REPLACE"

Any ideas? Thanks so much all for looking.

JnuwNo one has a way for me to import an access database through a script or using osql? Help please, thank you all for looking.

Import Access Database?

I am trying to upsize an Access database with the intent of using Access as
the front-end and MSDE as the back-end. I know MSDE allows you to import
DTS
packages, but I am not really familiar with DTS, so I decided to use the
Access wizard.
All in all, it seems pretty straight forward. I chose a default instance of
SQL Server that I created using the MSDE (residing on my local machine), set
a username and password, and told it to link the tables (I also tried using
..adp, but I got the same errors). I also told it to upsize the indexes, but
nothing else.
The program seemed to be working fine, but when it finished it gave me the
following error messages:
EXEC sp_addextendedproperty N'Attributes', N'2', N'user', N'dbo', N'table',
N'EntireSpreadsheet', N'column', N'ID'
EXEC sp_addextendedproperty N'CollatingOrder', N'1033', N'user', N'dbo',
N'table', N'EntireSpreadsheet', N'column', N'ID'
EXEC sp_addextendedproperty N'Type', N'10', N'user', N'dbo', N'table',
N'EntireSpreadsheet', N'column', N'ID'
...
and also:
Server Error 15233: Property cannot be added. Property 'Attributes' already
exists for 'dbo.EntireSpreadsheet.ID'.
I am not entirely sure what these errors mean, and all the data seems to be
displaying properly, but if anyone could offer any suggestions about how to
import the file to MSDE (even if it's using DTS) I would appreciate it.
Thanks,
Chris
Here's a resource that has links to various topics that address your
questions:
Migrating Microsoft Access Applications to SQL Server
http://www.microsoft.com/sql/accessmigration.mspx.
--Mary
On Mon, 10 Oct 2005 17:02:16 -0400, "Chris Burnette"
<chrisb@.publishamerica.com> wrote:

>
>I am trying to upsize an Access database with the intent of using Access as
>the front-end and MSDE as the back-end. I know MSDE allows you to import
>DTS
>packages, but I am not really familiar with DTS, so I decided to use the
>Access wizard.
>All in all, it seems pretty straight forward. I chose a default instance of
>SQL Server that I created using the MSDE (residing on my local machine), set
>a username and password, and told it to link the tables (I also tried using
>.adp, but I got the same errors). I also told it to upsize the indexes, but
>nothing else.
>The program seemed to be working fine, but when it finished it gave me the
>following error messages:
>EXEC sp_addextendedproperty N'Attributes', N'2', N'user', N'dbo', N'table',
>N'EntireSpreadsheet', N'column', N'ID'
>EXEC sp_addextendedproperty N'CollatingOrder', N'1033', N'user', N'dbo',
>N'table', N'EntireSpreadsheet', N'column', N'ID'
>EXEC sp_addextendedproperty N'Type', N'10', N'user', N'dbo', N'table',
>N'EntireSpreadsheet', N'column', N'ID'
>...
>and also:
>Server Error 15233: Property cannot be added. Property 'Attributes' already
>exists for 'dbo.EntireSpreadsheet.ID'.
>I am not entirely sure what these errors mean, and all the data seems to be
>displaying properly, but if anyone could offer any suggestions about how to
>import the file to MSDE (even if it's using DTS) I would appreciate it.
>Thanks,
>Chris
>