reset IDENTITY after table data import?

I have a remote DB I am wokring with at present. The DBA has provided me with a non owner LOGIN so I can't copy tables from the live to the staged DB as objects I can only copy tables and data.

The PKEY and IDENTITY COLUMNS get reset to just regular columns on each table. I can restore the PKEY constraint and have come across the DBCC CHECKIDENT to get the new ident value. I just can't figure out how to set a column to be an identity. The ALTER TABLE command isn't having any of it.

I am obviously missing the right bit on Books online

any suggestions?

many thanks

SteveThere isn't any way I know to add the IDENTITY property to an existing column. I'm pretty sure that it must be done at the time of the table creation.

My advice would be to script the table from the production DB, change the script to use IDENTITY, then copy the data from production to your working table.

-PatP

Reserved Error importing Access 2003 Report into VS2005 Report Module, How do I troublesho

Hi,
I'm trying to Import an existing Access 2003 report into into an existing
VS2005 Report Project, I am getting the following error. Is there a way to
troubleshoot what is causing this error?
Importing reports from 'C:\db2.mdb'...
Error : An error occurred while the report Test Report Form was being
imported: Reserved ErrorWe ran across a few reports with this issue. I reviewed my notes but didn't
come up with anything.
I found this in a group:
2 From: Albert Yen [MSFT] - view profile
Date: Tues, Jul 26 2005 3:51 pm
Email: "Albert Yen [MSFT]" <albe...@.online.microsoft.com>
Groups: microsoft.public.sqlserver.reportingsvcs
Not yet ratedRating:
show options
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse | Find messages by this author
The first error could be coming from Access. Could you try the following?
Open your database in Microsoft Access. Create a new Module and paste the
following code into it:
Sub Export()
Application.ExportXML ObjectType:=acExportReport, _
DataSource:="DailySales ", _
ImageTarget:=".", _
PresentationTarget:="DailySales .xsl", _
OtherFlags:=acPersistReportML
End Sub
Set the cursor at the beginning of the Sub and click the Run button. If
ExportXML succeeds, it should create a file called "DailySales_report.xml"
in your "My Documents" folder. If you can send the file to me, I'll look at
it. If ExportXML fails, then it's an Access problem. You could try the
Access newsgroups for help.
Albert Yen
SQL Server Reporting Services
Steve MunLeeuw
"Rob Dob" <robdob20012002@.yahoo.com> wrote in message
news:u8Pq0zJ7GHA.4708@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I'm trying to Import an existing Access 2003 report into into an existing
> VS2005 Report Project, I am getting the following error. Is there a way
> to troubleshoot what is causing this error?
> Importing reports from 'C:\db2.mdb'...
> Error : An error occurred while the report Test Report Form was being
> imported: Reserved Error
>

Repost + Alternative

Hi,
Haven't found an answer yet but I'm still hoping. When we try to import data
using the Other (ODBC Data Source) and select the DSN created for the Excel
spreadsheet then no data is listed. However, if we choose "Microsoft Excel
97-2000" as the Data Source and select the appropriate excel file when
importing data then the Excel data is listed. What underlying technique is
used when selecting "Microsoft Excel 97-2000" and can we use it in Transact
SQL?
Thanks,
Alain
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server
tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
> any information about the error.].
>
> 2) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driver] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling
that
> some setting (registry) is missing or is incorrect or that a file is
missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> Cortex
Alan,
this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
Not sure if that error is generic but I get that error when I leave the File
open in Excel
Also see KB below on possible issue with version of Jet DLL
http://support.microsoft.com/default...b;en-us;818182
Andy Ball
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Haven't found an answer yet but I'm still hoping. When we try to import
data
> using the Other (ODBC Data Source) and select the DSN created for the
Excel
> spreadsheet then no data is listed. However, if we choose "Microsoft Excel
> 97-2000" as the Data Source and select the appropriate excel file when
> importing data then the Excel data is listed. What underlying technique is
> used when selecting "Microsoft Excel 97-2000" and can we use it in
Transact[vbcol=seagreen]
> SQL?
> Thanks,
> Alain
>
> "Alain Sienaert" wrote:
> tables.
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
provider[vbcol=seagreen]
give[vbcol=seagreen]
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
application[vbcol=seagreen]
run[vbcol=seagreen]
data.
> that
> missing
>
|||Alain,
Thanks for your suggestions however they do not solve the problem.
1) I checked the MSJET40.dll and this is the latest one available. I also
created a Test.udl file with the following properties:
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
When pressing Test Connection the program replies with Test connection
succeeded.
2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
pressing Test Connection the program also replies with Test connection
succeeded.
3) When we include one of our sql statements that fail when run from the
Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
Package without any problems.
Provider:
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Alan,
> this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
> Not sure if that error is generic but I get that error when I leave the
File[vbcol=seagreen]
> open in Excel
> Also see KB below on possible issue with version of Jet DLL
> http://support.microsoft.com/default...b;en-us;818182
>
> Andy Ball
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> data
> Excel
Excel[vbcol=seagreen]
is[vbcol=seagreen]
> Transact
MS[vbcol=seagreen]
> (Windows
> Excel
> provider
> give
MS[vbcol=seagreen]
> (Windows
> Excel
Cannot[vbcol=seagreen]
> application
IDBInitialize::Initialize[vbcol=seagreen]
> run
> data.
files).
>
|||Andy,
You can tell that it is getting late (about 12:45 AM). I call you Alain and
forget to close my message with a greeting.
Kind regards,
Alain
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Alain,
> Thanks for your suggestions however they do not solve the problem.
> 1) I checked the MSJET40.dll and this is the latest one available. I also
> created a Test.udl file with the following properties:
> - Provider: Microsoft OLE DB Provider for ODBC Drivers
> - Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
> When pressing Test Connection the program replies with Test connection
> succeeded.
>
> 2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
> pressing Test Connection the program also replies with Test connection
> succeeded.
>
> 3) When we include one of our sql statements that fail when run from the
> Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
> Package without any problems.
>
>
> Provider:
> "Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
> news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
covers.[vbcol=seagreen]
> File
import[vbcol=seagreen]
> Excel
technique[vbcol=seagreen]
> is
> MS
> MS
> Cannot
> IDBInitialize::Initialize
and[vbcol=seagreen]
to[vbcol=seagreen]
feeling
> files).
>
|||you can call me betty ..:-)
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
What happens if you run the following from QA :
EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
cheers,
Andy.
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> Andy,
> You can tell that it is getting late (about 12:45 AM). I call you Alain
and[vbcol=seagreen]
> forget to close my message with a greeting.
> Kind regards,
> Alain
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
also[vbcol=seagreen]
when[vbcol=seagreen]
the[vbcol=seagreen]
> covers.
the[vbcol=seagreen]
> import
the[vbcol=seagreen]
when[vbcol=seagreen]
> technique
server[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
not[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
> and
Excel[vbcol=seagreen]
> to
the[vbcol=seagreen]
> feeling
is
>
|||Dear Betty ,
Thanks for your assistance so far...
Please find my answers below.
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
Yes, I did run the DTS from a Client workstation. Running it in Design Mode
directly on server does not work. Same error as before:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
did not give any information about the error. OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned
0x80004005: The provider did
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
No, running the command on the workstation does work. It fails when running
it on the server. Please note that the client workstation account has less
access rights than the account used when running the tests on the server
(administrator account). I guess the client workstation is using its own
configuration (OLE DB objects) to access the appropriate file?
What happens if you run the following from QA : EXEC master.dbo.xp_cmdshell
'dir \\server\file.xls'
It returns the expected information such as Volume, Volume Serial Number,
Date and Timestamp, etc...
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
The SQL Server runs under a domain account with a password that never
expires (functional account). This account has administrator rights on the
sql server and is used when running the tests on the server. Please note
that the Excel file is on the same server.
I have the feeling that something is wrong in the registry or so, since I am
able to access Excel 4.0 files.
Cheers,
Alain
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ux30zW4XEHA.3972@.TK2MSFTNGP12.phx.gbl...
> you can call me betty ..:-)
> 1. So when u run in DTS u are in Design Mode on a Client workstation ?
When[vbcol=seagreen]
> happens if you schedule same package under SQLAgent - does it still work ?
> 2. When you run from SQL Server QA and it fails you are also running from
> the workstation right ?
> What happens if you run the following from QA :
> EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
> 3. What account does SQL Server Service start under ? System or a domain
> account ? When using QA it will used the Service Account credentials to
> access the spreadsheet (presuming you are SA equivalent)
> cheers,
> Andy.
>
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> and
> also
> when
the[vbcol=seagreen]
> the
> the
> the
"Microsoft[vbcol=seagreen]
> when
> server
> SP6a -
is[vbcol=seagreen]
> an
> not
> SP6a -
Driver[vbcol=seagreen]
is[vbcol=seagreen]
> an
Driver][vbcol=seagreen]
format[vbcol=seagreen]
> Excel
seem[vbcol=seagreen]
> the
file
> is
>

Repost + Alternative

Hi,
Haven't found an answer yet but I'm still hoping. When we try to import data
using the Other (ODBC Data Source) and select the DSN created for the Excel
spreadsheet then no data is listed. However, if we choose "Microsoft Excel
97-2000" as the Data Source and select the appropriate excel file when
importing data then the Excel data is listed. What underlying technique is
used when selecting "Microsoft Excel 97-2000" and can we use it in Transact
SQL?
Thanks,
Alain
"Alain Sienaert" wrote:

> Hi,
> We are trying to import Excel data (Excel 2002) into some SQL server
tables.
> However, we have tried several things but they all seem to fail.
> 1) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 8.0;Database=\\server\file.xls', [Sheet1$])
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
> did not give any information about the error.
> OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The provider did not give
> any information about the error.].
>
> 2) The following command on our production server (Windows NT SP6a - MS
SQL
> Server 2000 SP3) fails when we run it in Query Analyzer:
> select * from OpenRowset('MSDASQL', 'Driver=Microsoft Excel Driver
> (*.xls);DBQ=\\server\file.xls', 'select * from [Sheet1$]')
> However when we run this command in Query Analyzer on our Client (Windows
> XP - MS SQL Server 2000 SP3) and refer to the SAME file, which is an Excel
> file on our production server, we get the Excel data.
> The error returned on our production server is:
>
> Server: Msg 7399, Level 16, State 1, Line 8
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: [Microsoft][ODBC Excel Driv
er] Cannot
> open database '(unknown)'. It may not be a database that your application
> recognizes, or the file may be corrupt.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initializ
e
> returned 0x80004005: ].
>
> 3) If I save the Excel file in Excel 4.0 (single worksheet) format and run
> the following command on our production server then we get the Excel data.
> select * from OpenRowset('Microsoft.Jet.OLEDB.4.0', 'Excel
> 4.0;Database=\\server\file.xls', [file$])
>
> I've checked several sources on the internet and most of them seem to
> conclude that this error is permission related but I'm sure that the
> production server does have access to the Excel file. I have a feeling
that
> some setting (registry) is missing or is incorrect or that a file is
missing
> or has the wrong version (it seem that we can access older Excel files).
> All help is appreciated,
> Alain Sienaert
> CortexAlan,
this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
Not sure if that error is generic but I get that error when I leave the File
open in Excel
Also see KB below on possible issue with version of Jet DLL
http://support.microsoft.com/defaul...kb;en-us;818182
Andy Ball
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Haven't found an answer yet but I'm still hoping. When we try to import
data
> using the Other (ODBC Data Source) and select the DSN created for the
Excel
> spreadsheet then no data is listed. However, if we choose "Microsoft Excel
> 97-2000" as the Data Source and select the appropriate excel file when
> importing data then the Excel data is listed. What underlying technique is
> used when selecting "Microsoft Excel 97-2000" and can we use it in
Transact
> SQL?
> Thanks,
> Alain
>
> "Alain Sienaert" wrote:
>
> tables.
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
provider[vbcol=seagreen]
give[vbcol=seagreen]
> SQL
(Windows[vbcol=seagreen]
Excel[vbcol=seagreen]
application[vbcol=seagreen]
run[vbcol=seagreen]
data.[vbcol=seagreen]
> that
> missing
>|||Alain,
Thanks for your suggestions however they do not solve the problem.
1) I checked the MSJET40.dll and this is the latest one available. I also
created a Test.udl file with the following properties:
- Provider: Microsoft OLE DB Provider for ODBC Drivers
- Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
When pressing Test Connection the program replies with Test connection
succeeded.
2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
pressing Test Connection the program also replies with Test connection
succeeded.
3) When we include one of our sql statements that fail when run from the
Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
Package without any problems.
Provider:
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
> Alan,
> this is using the OLEDB driver for Access/JET OLEDB 4.0 under the covers.
> Not sure if that error is generic but I get that error when I leave the
File
> open in Excel
> Also see KB below on possible issue with version of Jet DLL
> http://support.microsoft.com/defaul...kb;en-us;818182
>
> Andy Ball
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:umn6V$hXEHA.952@.TK2MSFTNGP10.phx.gbl...
> data
> Excel
Excel[vbcol=seagreen]
is[vbcol=seagreen]
> Transact
MS[vbcol=seagreen]
> (Windows
> Excel
> provider
> give
MS[vbcol=seagreen]
> (Windows
> Excel
Cannot[vbcol=seagreen]
> application
IDBInitialize::Initialize[vbcol=seagreen
]
> run
> data.
files).[vbcol=seagreen]
>|||Andy,
You can tell that it is getting late (about 12:45 AM). I call you Alain and
forget to close my message with a greeting.
Kind regards,
Alain
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
> Alain,
> Thanks for your suggestions however they do not solve the problem.
> 1) I checked the MSJET40.dll and this is the latest one available. I also
> created a Test.udl file with the following properties:
> - Provider: Microsoft OLE DB Provider for ODBC Drivers
> - Connection Source: the DNS used in the Query Analyzer (XLS_ODBC)
> When pressing Test Connection the program replies with Test connection
> succeeded.
>
> 2) When using the Microsoft Jet 4.0 OLE DB Provider in Test.udl then when
> pressing Test Connection the program also replies with Test connection
> succeeded.
>
> 3) When we include one of our sql statements that fail when run from the
> Query Analyzer in an Execute SQL Task of a DTS Package then we can run the
> Package without any problems.
>
>
> Provider:
> "Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
> news:ebz3cVsXEHA.3888@.TK2MSFTNGP10.phx.gbl...
covers.[vbcol=seagreen]
> File
import[vbcol=seagreen]
> Excel
technique[vbcol=seagreen]
> is
> MS
> MS
> Cannot
> IDBInitialize::Initialize
and[vbcol=seagreen]
to[vbcol=seagreen]
feeling[vbcol=seagreen]
> files).
>|||you can call me betty ..:-)
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
What happens if you run the following from QA :
EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
cheers,
Andy.
"Alain Sienaert" <asienaert@.hotmail.com> wrote in message
news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> Andy,
> You can tell that it is getting late (about 12:45 AM). I call you Alain
and
> forget to close my message with a greeting.
> Kind regards,
> Alain
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:edOmkKvXEHA.2448@.TK2MSFTNGP09.phx.gbl...
also[vbcol=seagreen]
when[vbcol=seagreen]
the[vbcol=seagreen]
> covers.
the[vbcol=seagreen]
> import
the[vbcol=seagreen]
when[vbcol=seagreen]
> technique
server[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
not[vbcol=seagreen]
SP6a -[vbcol=seagreen]
an[vbcol=seagreen]
> and
Excel[vbcol=seagreen]
> to
the[vbcol=seagreen]
> feeling
is[vbcol=seagreen]
>|||Dear Betty ,
Thanks for your assistance so far...
Please find my answers below.
1. So when u run in DTS u are in Design Mode on a Client workstation ? When
happens if you schedule same package under SQLAgent - does it still work ?
Yes, I did run the DTS from a Client workstation. Running it in Design Mode
directly on server does not work. Same error as before:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider
did not give any information about the error. OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned
0x80004005: The provider did
2. When you run from SQL Server QA and it fails you are also running from
the workstation right ?
No, running the command on the workstation does work. It fails when running
it on the server. Please note that the client workstation account has less
access rights than the account used when running the tests on the server
(administrator account). I guess the client workstation is using its own
configuration (OLE DB objects) to access the appropriate file?
What happens if you run the following from QA : EXEC master.dbo.xp_cmdshell
'dir \\server\file.xls'
It returns the expected information such as Volume, Volume Serial Number,
Date and Timestamp, etc...
3. What account does SQL Server Service start under ? System or a domain
account ? When using QA it will used the Service Account credentials to
access the spreadsheet (presuming you are SA equivalent)
The SQL Server runs under a domain account with a password that never
expires (functional account). This account has administrator rights on the
sql server and is used when running the tests on the server. Please note
that the Excel file is on the same server.
I have the feeling that something is wrong in the registry or so, since I am
able to access Excel 4.0 files.
Cheers,
Alain
"Andy Ball" <andy.ball@.remove4spam_greenfell.com> wrote in message
news:ux30zW4XEHA.3972@.TK2MSFTNGP12.phx.gbl...
> you can call me betty ..:-)
> 1. So when u run in DTS u are in Design Mode on a Client workstation ?
When
> happens if you schedule same package under SQLAgent - does it still work ?
> 2. When you run from SQL Server QA and it fails you are also running from
> the workstation right ?
> What happens if you run the following from QA :
> EXEC master.dbo.xp_cmdshell 'dir \\server\file.xls'
> 3. What account does SQL Server Service start under ? System or a domain
> account ? When using QA it will used the Service Account credentials to
> access the spreadsheet (presuming you are SA equivalent)
> cheers,
> Andy.
>
>
> "Alain Sienaert" <asienaert@.hotmail.com> wrote in message
> news:%23D4lWRvXEHA.3476@.tk2msftngp13.phx.gbl...
> and
> also
> when
the[vbcol=seagreen]
> the
> the
> the
"Microsoft[vbcol=seagreen]
> when
> server
> SP6a -
is[vbcol=seagreen]
> an
> not
> SP6a -
Driver[vbcol=seagreen]
is[vbcol=seagreen]
> an
Driver][vbcol=seagreen]
format[vbcol=seagreen]
> Excel
seem[vbcol=seagreen]
> the
file[vbcol=seagreen]
> is
>