ITW wont save script file

Hi all,

I am running the ITW against several trace files that were created from a single trace file. I only have the following problem when attempting to save the script file from the analysis of any one of the multiple trace files. I have used the ITW against single trace files with no problem.

Here's the problem:
Everything works fine until I select the "Save Script File" option. After selecting this option, designating a file name to save to, and then clicking the "Next" button, the application appears to hang up. It displays an hourglass pointer, as though it is working, but just sits there forever. Also, when I look at the task manager this application reports that it is "not responding". I have allowed it to sit there in this state for several hours before finaly killing the process. Is there some additional configuration or method required to analyze multiple trace files from a single trace session?

Any suggestions would be greatly appreciated.

Thanks,
RickThis could be a bug. You might want to contact PSS.

ITW Errors

Hi,
I'm trying to use the ITW for one of our German servers. I have created an
input file using Profiler that captures the SQL:BatchCompleted and the
RPC:Completed events. When I run the ITW using the input file I recieve the
following error:
"The workload does not contain any events or queries that can be tuned
against the current database. Check to see that the trace contains valid
events or that the SQL script contains valid queries and you are tuning
against the right database"
If I select only certain tables to analyze I recieve the following error:
"There are no events in the workload. Either the trace file contained no
SQL batch or RPC events or the SQL script contained no SQL queries.
I have run a broad trace and a narrow trace using the specific database id
as a filter.
Both using a file and a table return the same errors.
If I use the Query Analyzer Selection option with the ITW with a specific
query from the input file then the ITW does work properly. Also, the size
of the input file is < 100 rows (just a test at this point).
It looks as though the ITW has an issue with the code of one or more of the
statements in the input file. Is this an accurate assumption? Does anyone
know how to resolve this issue so that I can use ITW with many statements as
opposed to using only a few at a time?
Thanks
Jerry
Jerry Spivey wrote:
> Hi,
> I'm trying to use the ITW for one of our German servers. I have
> created an input file using Profiler that captures the
> SQL:BatchCompleted and the RPC:Completed events. When I run the ITW
> using the input file I recieve the following error:
> "The workload does not contain any events or queries that can be tuned
> against the current database. Check to see that the trace contains
> valid events or that the SQL script contains valid queries and you
> are tuning against the right database"
> If I select only certain tables to analyze I recieve the following
> error:
> "There are no events in the workload. Either the trace file
> contained no SQL batch or RPC events or the SQL script contained no
> SQL queries.
> I have run a broad trace and a narrow trace using the specific
> database id as a filter.
> Both using a file and a table return the same errors.
> If I use the Query Analyzer Selection option with the ITW with a
> specific query from the input file then the ITW does work properly.
> Also, the size of the input file is < 100 rows (just a test at this
> point).
> It looks as though the ITW has an issue with the code of one or more
> of the statements in the input file. Is this an accurate assumption?
> Does anyone know how to resolve this issue so that I can use ITW with
> many statements as opposed to using only a few at a time?
> Thanks
> Jerry
There used to be a problem with SQL Server 7.0 with running the ITW on a
client PC using a different locale than the one on the server. Have you
tried running the ITW on the server itself (off-hours)?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||David,
I saw the KB article that referenced that issue but it looked as though it
was fixed in SP1 of Version 7. We're running SP3 of Version 2000. I used
the client utilities from a separate client machine here in the states. I
had a colleague remote desktop to the SQL Server itself running the tools on
the local SQL Server and he received the same errors that I'm getting.
Jerry
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uMu8ZbIjFHA.3784@.tk2msftngp13.phx.gbl...
> Jerry Spivey wrote:
> There used to be a problem with SQL Server 7.0 with running the ITW on a
> client PC using a different locale than the one on the server. Have you
> tried running the ITW on the server itself (off-hours)?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
|||Jerry Spivey wrote:
> David,
> I saw the KB article that referenced that issue but it looked as
> though it was fixed in SP1 of Version 7. We're running SP3 of
> Version 2000. I used the client utilities from a separate client
> machine here in the states. I had a colleague remote desktop to the
> SQL Server itself running the tools on the local SQL Server and he
> received the same errors that I'm getting.
> Jerry
>
Have you examined the contents of the workload file to see what was
actually captured? If so, what happens if you copy one of the commands
and tune is manually from Query Analyzer. Does it work? How many
workload queries did you tell the ITW to examine in the workload file?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Yea that is my approach right now. I'm copy and pasting individual
statements into QA using the QAS option. Haven't found the culprit yet -
about 90 left to go.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eOlxUkIjFHA.2904@.tk2msftngp13.phx.gbl...
> Jerry Spivey wrote:
> Have you examined the contents of the workload file to see what was
> actually captured? If so, what happens if you copy one of the commands and
> tune is manually from Query Analyzer. Does it work? How many workload
> queries did you tell the ITW to examine in the workload file?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
sql

ITW Errors

Hi,
I'm trying to use the ITW for one of our German servers. I have created an
input file using Profiler that captures the SQL:BatchCompleted and the
RPC:Completed events. When I run the ITW using the input file I recieve the
following error:
"The workload does not contain any events or queries that can be tuned
against the current database. Check to see that the trace contains valid
events or that the SQL script contains valid queries and you are tuning
against the right database"
If I select only certain tables to analyze I recieve the following error:
"There are no events in the workload. Either the trace file contained no
SQL batch or RPC events or the SQL script contained no SQL queries.
I have run a broad trace and a narrow trace using the specific database id
as a filter.
Both using a file and a table return the same errors.
If I use the Query Analyzer Selection option with the ITW with a specific
query from the input file then the ITW does work properly. Also, the size
of the input file is < 100 rows (just a test at this point).
It looks as though the ITW has an issue with the code of one or more of the
statements in the input file. Is this an accurate assumption? Does anyone
know how to resolve this issue so that I can use ITW with many statements as
opposed to using only a few at a time?
Thanks
JerryJerry Spivey wrote:
> Hi,
> I'm trying to use the ITW for one of our German servers. I have
> created an input file using Profiler that captures the
> SQL:BatchCompleted and the RPC:Completed events. When I run the ITW
> using the input file I recieve the following error:
> "The workload does not contain any events or queries that can be tuned
> against the current database. Check to see that the trace contains
> valid events or that the SQL script contains valid queries and you
> are tuning against the right database"
> If I select only certain tables to analyze I recieve the following
> error:
> "There are no events in the workload. Either the trace file
> contained no SQL batch or RPC events or the SQL script contained no
> SQL queries.
> I have run a broad trace and a narrow trace using the specific
> database id as a filter.
> Both using a file and a table return the same errors.
> If I use the Query Analyzer Selection option with the ITW with a
> specific query from the input file then the ITW does work properly.
> Also, the size of the input file is < 100 rows (just a test at this
> point).
> It looks as though the ITW has an issue with the code of one or more
> of the statements in the input file. Is this an accurate assumption?
> Does anyone know how to resolve this issue so that I can use ITW with
> many statements as opposed to using only a few at a time?
> Thanks
> Jerry
There used to be a problem with SQL Server 7.0 with running the ITW on a
client PC using a different locale than the one on the server. Have you
tried running the ITW on the server itself (off-hours)?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
I saw the KB article that referenced that issue but it looked as though it
was fixed in SP1 of Version 7. We're running SP3 of Version 2000. I used
the client utilities from a separate client machine here in the states. I
had a colleague remote desktop to the SQL Server itself running the tools on
the local SQL Server and he received the same errors that I'm getting.
Jerry
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uMu8ZbIjFHA.3784@.tk2msftngp13.phx.gbl...
> Jerry Spivey wrote:
>> Hi,
>> I'm trying to use the ITW for one of our German servers. I have
>> created an input file using Profiler that captures the
>> SQL:BatchCompleted and the RPC:Completed events. When I run the ITW
>> using the input file I recieve the following error:
>> "The workload does not contain any events or queries that can be tuned
>> against the current database. Check to see that the trace contains
>> valid events or that the SQL script contains valid queries and you
>> are tuning against the right database"
>> If I select only certain tables to analyze I recieve the following
>> error:
>> "There are no events in the workload. Either the trace file
>> contained no SQL batch or RPC events or the SQL script contained no
>> SQL queries.
>> I have run a broad trace and a narrow trace using the specific
>> database id as a filter.
>> Both using a file and a table return the same errors.
>> If I use the Query Analyzer Selection option with the ITW with a
>> specific query from the input file then the ITW does work properly. Also,
>> the size of the input file is < 100 rows (just a test at this
>> point).
>> It looks as though the ITW has an issue with the code of one or more
>> of the statements in the input file. Is this an accurate assumption?
>> Does anyone know how to resolve this issue so that I can use ITW with
>> many statements as opposed to using only a few at a time?
>> Thanks
>> Jerry
> There used to be a problem with SQL Server 7.0 with running the ITW on a
> client PC using a different locale than the one on the server. Have you
> tried running the ITW on the server itself (off-hours)?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Jerry Spivey wrote:
> David,
> I saw the KB article that referenced that issue but it looked as
> though it was fixed in SP1 of Version 7. We're running SP3 of
> Version 2000. I used the client utilities from a separate client
> machine here in the states. I had a colleague remote desktop to the
> SQL Server itself running the tools on the local SQL Server and he
> received the same errors that I'm getting.
> Jerry
>
Have you examined the contents of the workload file to see what was
actually captured? If so, what happens if you copy one of the commands
and tune is manually from Query Analyzer. Does it work? How many
workload queries did you tell the ITW to examine in the workload file?
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yea that is my approach right now. I'm copy and pasting individual
statements into QA using the QAS option. Haven't found the culprit yet -
about 90 left to go.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eOlxUkIjFHA.2904@.tk2msftngp13.phx.gbl...
> Jerry Spivey wrote:
>> David,
>> I saw the KB article that referenced that issue but it looked as
>> though it was fixed in SP1 of Version 7. We're running SP3 of
>> Version 2000. I used the client utilities from a separate client
>> machine here in the states. I had a colleague remote desktop to the
>> SQL Server itself running the tools on the local SQL Server and he
>> received the same errors that I'm getting.
>> Jerry
> Have you examined the contents of the workload file to see what was
> actually captured? If so, what happens if you copy one of the commands and
> tune is manually from Query Analyzer. Does it work? How many workload
> queries did you tell the ITW to examine in the workload file?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

ITW Errors

Hi,
I'm trying to use the ITW for one of our German servers. I have created an
input file using Profiler that captures the SQL:BatchCompleted and the
RPC:Completed events. When I run the ITW using the input file I recieve the
following error:
"The workload does not contain any events or queries that can be tuned
against the current database. Check to see that the trace contains valid
events or that the SQL script contains valid queries and you are tuning
against the right database"
If I select only certain tables to analyze I recieve the following error:
"There are no events in the workload. Either the trace file contained no
SQL batch or RPC events or the SQL script contained no SQL queries.
I have run a broad trace and a narrow trace using the specific database id
as a filter.
Both using a file and a table return the same errors.
If I use the Query Analyzer Selection option with the ITW with a specific
query from the input file then the ITW does work properly. Also, the size
of the input file is < 100 rows (just a test at this point).
It looks as though the ITW has an issue with the code of one or more of the
statements in the input file. Is this an accurate assumption? Does anyone
know how to resolve this issue so that I can use ITW with many statements as
opposed to using only a few at a time?
Thanks
JerryJerry Spivey wrote:
> Hi,
> I'm trying to use the ITW for one of our German servers. I have
> created an input file using Profiler that captures the
> SQL:BatchCompleted and the RPC:Completed events. When I run the ITW
> using the input file I recieve the following error:
> "The workload does not contain any events or queries that can be tuned
> against the current database. Check to see that the trace contains
> valid events or that the SQL script contains valid queries and you
> are tuning against the right database"
> If I select only certain tables to analyze I recieve the following
> error:
> "There are no events in the workload. Either the trace file
> contained no SQL batch or RPC events or the SQL script contained no
> SQL queries.
> I have run a broad trace and a narrow trace using the specific
> database id as a filter.
> Both using a file and a table return the same errors.
> If I use the Query Analyzer Selection option with the ITW with a
> specific query from the input file then the ITW does work properly.
> Also, the size of the input file is < 100 rows (just a test at this
> point).
> It looks as though the ITW has an issue with the code of one or more
> of the statements in the input file. Is this an accurate assumption?
> Does anyone know how to resolve this issue so that I can use ITW with
> many statements as opposed to using only a few at a time?
> Thanks
> Jerry
There used to be a problem with SQL Server 7.0 with running the ITW on a
client PC using a different locale than the one on the server. Have you
tried running the ITW on the server itself (off-hours)?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
I saw the KB article that referenced that issue but it looked as though it
was fixed in SP1 of Version 7. We're running SP3 of Version 2000. I used
the client utilities from a separate client machine here in the states. I
had a colleague remote desktop to the SQL Server itself running the tools on
the local SQL Server and he received the same errors that I'm getting.
Jerry
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uMu8ZbIjFHA.3784@.tk2msftngp13.phx.gbl...
> Jerry Spivey wrote:
> There used to be a problem with SQL Server 7.0 with running the ITW on a
> client PC using a different locale than the one on the server. Have you
> tried running the ITW on the server itself (off-hours)?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||Jerry Spivey wrote:
> David,
> I saw the KB article that referenced that issue but it looked as
> though it was fixed in SP1 of Version 7. We're running SP3 of
> Version 2000. I used the client utilities from a separate client
> machine here in the states. I had a colleague remote desktop to the
> SQL Server itself running the tools on the local SQL Server and he
> received the same errors that I'm getting.
> Jerry
>
Have you examined the contents of the workload file to see what was
actually captured? If so, what happens if you copy one of the commands
and tune is manually from Query Analyzer. Does it work? How many
workload queries did you tell the ITW to examine in the workload file?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Yea that is my approach right now. I'm copy and pasting individual
statements into QA using the QAS option. Haven't found the culprit yet -
about 90 left to go.
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eOlxUkIjFHA.2904@.tk2msftngp13.phx.gbl...
> Jerry Spivey wrote:
> Have you examined the contents of the workload file to see what was
> actually captured? If so, what happens if you copy one of the commands and
> tune is manually from Query Analyzer. Does it work? How many workload
> queries did you tell the ITW to examine in the workload file?
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com

It's like MS hates us...

So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
nothing fancy. The only "oddity", one is shares with _every_ other file I
have to work with, is that it has two header rows, one that has the filename
and date, and a second that has the column headers.
So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
driver (actually most of them) has a many-year-old bug that makes
FirstRowHasNames=0 not work, so it keeps trying to use the row with the
filename as the columns. This results in the columns being name
"thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't SELECT
on a column called "NoName". Sweeeeet!
This would be easy to work-around if there was some way to refer to columns
by their ordinal position, but of course, there isn't. And since it's a temp
table, there's no catalog entry, so you can't query the resultset to see what
its made of and try to get the-column-name-for-today.
I also tried the OLE DB provider instead of the ODBC one, but every attempt
results in:
[OLE/DB provider returned message: Could not find installable ISAM.]
No, this problem is not caused by any of the bugs and/or problems listed on
the MS page.
*sigh*
Maury
Maury,
Here is a kludge of an idea, provided you can get it implemented.
1. Put a one-line file on disk with the column header names that you want,
such as:
FileName, FileDate, UserName, SQLLogin, etc
2. Get a process to create your file to be imported by concatenating your
header file with the file to be imported. (Essentially):
COPY MyHDR.CSV+MyData.CSV MyImport.CSV
3. You read MyImport.CSV which has your header line concatenated on the
front with the column names you will use, then all the rest is data.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury
|||Why don't you bulk insert the file into a working table, setting FIRSTROW=3,
and then join against that?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury
|||Have you tried to create a link server to .TXT file and operate with it as a
table?
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury
|||On May 8, 3:49 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Why don't you bulk insert the file into a working table, setting FIRSTROW=3,
> and then join against that?
Only works on 2005. I just went through a hair-raising upgrade to
2000SP3 in order to fix a single bug (interior inner joins on derived
tables), so I'm hesitant to move to 2005 at this point.
Maury
|||On May 9, 2:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Have you tried to create a link server to .TXT file and operate with it as a
> table?
Yes, this approach suffers from the same bugs. Not surprising really,
I'm sure it all forks through the same code.
It would be really nice if I could get the OLE DB approach working,
because it *seems* that it doesn't have the FirstRow bug, and I'm also
pretty familiar with the OLE DB model and feel pretty confident using
it. But I just can't figure out what's wrong. It's also odd that this
doesn't work:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:\temp;Extended
Properties=""text;HDR=No;FMT=Delimited"";"
This connection works fine on my workstation, but not the SQL Server.
I assume this newer ACE driver gets installed with some other piece of
software (Office 2003?).
Maury
|||>> Why don't you bulk insert the file into a working table, setting
> Only works on 2005.
BULK INSERT only works on 2005? What are you talking about?

It's like MS hates us...

So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
nothing fancy. The only "oddity", one is shares with _every_ other file I
have to work with, is that it has two header rows, one that has the filename
and date, and a second that has the column headers.
So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
driver (actually most of them) has a many-year-old bug that makes
FirstRowHasNames=0 not work, so it keeps trying to use the row with the
filename as the columns. This results in the columns being name
"thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't SELEC
T
on a column called "NoName". Sweeeeet!
This would be easy to work-around if there was some way to refer to columns
by their ordinal position, but of course, there isn't. And since it's a temp
table, there's no catalog entry, so you can't query the resultset to see wha
t
its made of and try to get the-column-name-for-today.
I also tried the OLE DB provider instead of the ODBC one, but every attempt
results in:
[OLE/DB provider returned message: Could not find installable ISAM.]
No, this problem is not caused by any of the bugs and/or problems listed on
the MS page.
*sigh*
MauryMaury,
Here is a kludge of an idea, provided you can get it implemented.
1. Put a one-line file on disk with the column header names that you want,
such as:
FileName, FileDate, UserName, SQLLogin, etc
2. Get a process to create your file to be imported by concatenating your
header file with the file to be imported. (Essentially):
COPY MyHDR.CSV+MyData.CSV MyImport.CSV
3. You read MyImport.CSV which has your header line concatenated on the
front with the column names you will use, then all the rest is data.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||Why don't you bulk insert the file into a working table, setting FIRSTROW=3,
and then join against that?
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||Have you tried to create a link server to .TXT file and operate with it as a
table?
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||On May 8, 3:49 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Why don't you bulk insert the file into a working table, setting FIRSTROW=
3,
> and then join against that?
Only works on 2005. I just went through a hair-raising upgrade to
2000SP3 in order to fix a single bug (interior inner joins on derived
tables), so I'm hesitant to move to 2005 at this point.
Maury|||On May 9, 2:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Have you tried to create a link server to .TXT file and operate with it as
a
> table?
Yes, this approach suffers from the same bugs. Not surprising really,
I'm sure it all forks through the same code.
It would be really nice if I could get the OLE DB approach working,
because it *seems* that it doesn't have the FirstRow bug, and I'm also
pretty familiar with the OLE DB model and feel pretty confident using
it. But I just can't figure out what's wrong. It's also odd that this
doesn't work:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:\temp;Extended
Properties=""text;HDR=No;FMT=Delimited"";"
This connection works fine on my workstation, but not the SQL Server.
I assume this newer ACE driver gets installed with some other piece of
software (Office 2003?).
Maury|||>> Why don't you bulk insert the file into a working table, setting
> Only works on 2005.
BULK INSERT only works on 2005? What are you talking about?sql

It's like MS hates us...

So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
nothing fancy. The only "oddity", one is shares with _every_ other file I
have to work with, is that it has two header rows, one that has the filename
and date, and a second that has the column headers.
So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
driver (actually most of them) has a many-year-old bug that makes
FirstRowHasNames=0 not work, so it keeps trying to use the row with the
filename as the columns. This results in the columns being name
"thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't SELECT
on a column called "NoName". Sweeeeet!
This would be easy to work-around if there was some way to refer to columns
by their ordinal position, but of course, there isn't. And since it's a temp
table, there's no catalog entry, so you can't query the resultset to see what
its made of and try to get the-column-name-for-today.
I also tried the OLE DB provider instead of the ODBC one, but every attempt
results in:
[OLE/DB provider returned message: Could not find installable ISAM.]
No, this problem is not caused by any of the bugs and/or problems listed on
the MS page.
*sigh*
MauryMaury,
Here is a kludge of an idea, provided you can get it implemented.
1. Put a one-line file on disk with the column header names that you want,
such as:
FileName, FileDate, UserName, SQLLogin, etc
2. Get a process to create your file to be imported by concatenating your
header file with the file to be imported. (Essentially):
COPY MyHDR.CSV+MyData.CSV MyImport.CSV
3. You read MyImport.CSV which has your header line concatenated on the
front with the column names you will use, then all the rest is data.
RLF
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||Why don't you bulk insert the file into a working table, setting FIRSTROW=3,
and then join against that?
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||Have you tried to create a link server to .TXT file and operate with it as a
table?
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:2D91F485-2104-4FE0-BB47-2A9104477B8D@.microsoft.com...
> So all I'm trying to do is a JOIN against a text file. It's a simple CSV,
> nothing fancy. The only "oddity", one is shares with _every_ other file I
> have to work with, is that it has two header rows, one that has the
> filename
> and date, and a second that has the column headers.
> So I'm trying ot use OPENROWSET. This is basically impossible. The ODBC
> driver (actually most of them) has a many-year-old bug that makes
> FirstRowHasNames=0 not work, so it keeps trying to use the row with the
> filename as the columns. This results in the columns being name
> "thefilename", "todaysdate", "NoName", "NoName" etc. And no, you can't
> SELECT
> on a column called "NoName". Sweeeeet!
> This would be easy to work-around if there was some way to refer to
> columns
> by their ordinal position, but of course, there isn't. And since it's a
> temp
> table, there's no catalog entry, so you can't query the resultset to see
> what
> its made of and try to get the-column-name-for-today.
> I also tried the OLE DB provider instead of the ODBC one, but every
> attempt
> results in:
> [OLE/DB provider returned message: Could not find installable ISAM.]
> No, this problem is not caused by any of the bugs and/or problems listed
> on
> the MS page.
> *sigh*
> Maury|||On May 8, 3:49 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> Why don't you bulk insert the file into a working table, setting FIRSTROW=3,
> and then join against that?
Only works on 2005. I just went through a hair-raising upgrade to
2000SP3 in order to fix a single bug (interior inner joins on derived
tables), so I'm hesitant to move to 2005 at this point.
Maury|||On May 9, 2:36 am, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Have you tried to create a link server to .TXT file and operate with it as a
> table?
Yes, this approach suffers from the same bugs. Not surprising really,
I'm sure it all forks through the same code.
It would be really nice if I could get the OLE DB approach working,
because it *seems* that it doesn't have the FirstRow bug, and I'm also
pretty familiar with the OLE DB model and feel pretty confident using
it. But I just can't figure out what's wrong. It's also odd that this
doesn't work:
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:\temp;Extended
Properties=""text;HDR=No;FMT=Delimited"";"
This connection works fine on my workstation, but not the SQL Server.
I assume this newer ACE driver gets installed with some other piece of
software (Office 2003?).
Maury|||>> Why don't you bulk insert the file into a working table, setting
>> FIRSTROW=3,
>> and then join against that?
> Only works on 2005.
BULK INSERT only works on 2005? What are you talking about?

Iterate through all Excel files and all their sheets

hello,

i need to transfer (migrate ) the data from xl sheet to sqlserver but actually the thing is if the source excel file has different sheets, in each sheet i have the data

and i need to move the entire data( all the data that is present in all sheets of the excel file) to a single table into sql server

like wise i have many xl files ( which have many sheets ) .

for eg:

excel file 1:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 2:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 3:

-> sheet 1

-> sheet 2

-> sheet 3

now i need to get the data from all of the files and i need to insert into a single table ( sql server) in ssis package

so plz help me by giving the solution asap.

thanks

B L Rao

hello ,

while i am trying to transfer the data from xl file to table in sql server by using ssis package it is giving error saying that primary key violation and cant insert duplicate value.

i understood that there is some duplicate data but can i find where that duplicate data exists i mean in which row ? because it contains thousands of records.

thanks and regards

B L Rao.

|||You may accomplish this by using 2 nested Loops: One fairly simple, a foreach loop to iterate through all excel files; a second one to iterate through each excel sheet. I am not sure how to implement the second one; perhaps if the number and name of the sheets is always the same you could built a list of values in a variable and then have the excel component to get the table name from a variable. Just an Idea, you would need to figure out the details.

ItemTypeEnum = Resource question

I have a Resource file that is a htm file in a sub-folder on reporting
services. Using the web service I am getting the catalog items using
ListChildren. I want to be able to extract out the contents of the htm file.
It is stored as a text/html MIMEType. I don't know if this is the correct
forum for this question or not but any assistance is appreciated.
JamesI found the answer myself and will post for reference:
byte[] resourceDefinition = null;
string mimeType = null;
string res = null;
resourceDefinition = rs.GetResourceContents(strReportName, out mimeType);
System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
res = enc.GetString(resourceDefinition);
"James Tow" wrote:
> I have a Resource file that is a htm file in a sub-folder on reporting
> services. Using the web service I am getting the catalog items using
> ListChildren. I want to be able to extract out the contents of the htm file.
> It is stored as a text/html MIMEType. I don't know if this is the correct
> forum for this question or not but any assistance is appreciated.
> James

item master and office stock details (was "Query Problem")

Hi,

I am having problem in getting result out of two table, one table is Item Mater which stores global items for all offices and other is stock file which stores office wise stock items as follows:

ITEM MASTER
-----
NCODE ITEMNAME
1 A
2 B
3 C
4 D
5 E

STOCKDETAILS
-----------
NCODE ITEMCODE OFFICEID
1 1 1
2 2 1
3 3 1
4 1 2
5 2 2
6 4 2
7 5 3

I want office wise stock details which inludes items found in stock file and remaining itmes from item master. example for office 1

--------------
FOR OFFICE - 1
--------------
ITEMCODE ITEMNAME OFFICEID
--------------
1 A 1
2 B 1
3 C 1
4 D NULL
5 E NULL

i want a single view from which i can select data like i shown above, any kind of help is highly appriciated, what i tried is , i created union of both tables and tried to get data out of union view but result is not up to desire.

Thanks in advanceWelcome to the Forum.
Check this...

SELECT dbo.[ITEM MASTER].NCODE,
BB.ITEMNAME,
BB.OFFICEID
FROM dbo.[ITEM MASTER]
INNER JOIN
(SELECT dbo.[ITEM MASTER].ITEMNAME,
AA.OFFICEID
FROM
dbo.[ITEM MASTER] LEFT JOIN
(SELECT * FROM STOCKDETAILS
WHERE dbo.STOCKDETAILS.OFFICEID = '1') AA
ON AA.NCODE = [ITEM MASTER].NCODE) BB ON
dbo.[ITEM MASTER].ITEMNAME = BB.ITEMNAME|||select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = 1|||Hi Rudra !

how to get such all result in view and then i will query for officeid to get my desired output.|||Hi r937 !

I am getting result through posting by Rudra but i want all data in view and then i want to query my view for office wise to get desired output. How to acheive this ?|||Hi r937 !

I am getting result through posting by Rudra but i want all data in view and then i want to query my view for office wise to get desired output. How to acheive this ?
Why don't you use stored proc and pass parameter using OFFICEID ? I think that would be a better way to deal with your problem.But I am not sure what your requirement is...|||select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = 1

hmm,always ahead...;)|||Hi Rudra !
i want to gether itemmaster and stockdetails data in to one view and then i want to query the view through office id, is this possible ?

if i use SP then how to return o/p rows of query from SP?
if i return Table with data from SP as o/p paramater then it wil be catechble in .net or dataset ?

my first preference is to gether all data in view then query the view for office,
what if we union itemmaster and office wise stock file and then queryfor office ?|||Hi Rudra !
i want to gether itemmaster and stockdetails data in to one view and then i want to query the view through office id, is this possible ?

if i use SP then how to return o/p rows of query from SP?
if i return Table with data from SP as o/p paramater then it wil be catechble in .net or dataset ?

my first preference is to gether all data in view then query the view for office,
what if we union itemmaster and office wise stock file and then queryfor office ?

I suggest you to use stored proc,its always good to use stored proc
in your case.Just write this...

CREATE PROCEDURE dbo.StockView(
@.officeid VARCHAR(20)

AS

--Use mine or Rudy's one
--this is mine
SELECT dbo.[ITEM MASTER].NCODE,
BB.ITEMNAME,
BB.OFFICEID
FROM dbo.[ITEM MASTER]
INNER JOIN
(SELECT dbo.[ITEM MASTER].ITEMNAME,
AA.OFFICEID
FROM
dbo.[ITEM MASTER] LEFT JOIN
(SELECT * FROM STOCKDETAILS
WHERE dbo.STOCKDETAILS.OFFICEID = @.officeid) AA
ON AA.NCODE = [ITEM MASTER].NCODE) BB ON
dbo.[ITEM MASTER].ITEMNAME = BB.ITEMNAME

--OR use Rudy's one

select M.ncode as itemcode
, M.itemname
, S.officeid
from ItemMaster as M
left outer
join StockDetails as S
on S.itemcode = M.ncode
and S.officeid = @.officeid

Go

And check BOL to use Stored proc in dataset .Its very easy man and better to use in many respect
Hope this will help you.sql

Issues with Export to Excel file

I need to do a simple task. I have a oledb source which pulls data from sql server, creates an excel file abc.xls and dumps it with the Excel destination at remote location.

I need to create this file at runtime. However, when i give the location, the Excel Destination expects the file to be there for the Mapping of the source columns with destination ones.

My tasks are

1. Creating a table for the worksheet in Excel connection

2. Select from source table and write in Excel Destination

Will appreciate your response.

You can create the xls file at run time without problem; but the file need to exists at design time as SSIS needs to get the metedata from it.

I have a couple of posts that show some examples using excel files; I hope you find them helpful

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

Issues upgrading MSDE to SP4

Hello I'm having some issues upgrading MSDE sp3 to MSDE sp4. I don't
see anything in the log file of any importance, no return value 3 just
an occasional 2 which is interesting because I'm not canceling. The
only error I can find is in the application log:
Event Type:Warning
Event Source:MsiInstaller
Event Category:None
Event ID:1015
Date:6/14/2005
Time:1:41:48 PM
User:domain\user
Computer:servername
Description:
Failed to connect to server. Error: 0x800401F0
I am part of the system administrators role so should be able to
contact the database. I did stop all of the services prior to
beginning the install so that I would not have to reboot in order for
the upgrade to go through. This is a production machine and I can't
afford downtime nor any reboots.
Thanks in advance for your help.
Also this is not a named instance and I have already attempted to use
the proper MSP file to accomodate the upgrade.
|||hi,
Kewlhand wrote:
> Hello I'm having some issues upgrading MSDE sp3 to MSDE sp4. I don't
> see anything in the log file of any importance, no return value 3 just
> an occasional 2 which is interesting because I'm not canceling. The
> only error I can find is in the application log:
> Event Type: Warning
> Event Source: MsiInstaller
> Event Category: None
> Event ID: 1015
> Date: 6/14/2005
> Time: 1:41:48 PM
> User: domain\user
> Computer: servername
> Description:
> Failed to connect to server. Error: 0x800401F0
> I am part of the system administrators role so should be able to
> contact the database. I did stop all of the services prior to
> beginning the install so that I would not have to reboot in order for
> the upgrade to go through. This is a production machine and I can't
> afford downtime nor any reboots.
> Thanks in advance for your help.
please have a look at http://tinyurl.com/bwrvv if helps
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||none of those keys exist with those parameters. Thanks for the info
though. If you've got anything else I'd love to hear it.
Thanks.
Kewlhand
|||I too am experiencing this issue. I've found that on two systems
running XP SP2 that I can connect to the SQL server using SQLDMO, but
not using Enterprise Manager over a remote TCP/IP connection.
I also am seeing this on both systems' Application Event Logs when MSDE
starts:
SuperSocket info: ConnectionListen(Shared-Memory (LPC)) : Error 5.
and
SuperSocket info: (SpnRegister) : Error 1355.
Both systems report the same MSDE version:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)
I'm pressed for time, so I'll be backing up data and reinstalling MSDE
then SP4 on the two machines, but any tips for the future would be
greatly appreciated.
Kind regards,
Jordan.
Kewlhand wrote:
> none of those keys exist with those parameters. Thanks for the info
> though. If you've got anything else I'd love to hear it.
> Thanks.
> Kewlhand

issues about foreach loop

I defined Foreach File Enumerator,there are more than 2 files needed to deal with,it donesn't work if i don't specify filename in flat file connection manager editor,however if i specify filename then doesn't foreach files

what should i do? thanks

and i tried to use varialbes ,but it seems not supported in Flat File connection manager editor?|||

andrew knight wrote:

I defined Foreach File Enumerator,there are more than 2 files needed to deal with,it donesn't work if i don't specify filename in flat file connection manager editor,however if i specify filename then doesn't foreach files

what should i do? thanks

Did you set an expression on the ConnectionString property of the connection manager?

-Jamie

|||

get it

thanks in advance

Issue with xp_cmdshell in SQL2005

Folks,

I would like to make you aware in my App, we use xp_cmdshell to get a list of file names (using dir into a temp table) from a specific directory and process these files one after another to insert into Database through the Job.

In SQL2000 xp_cmdshell was enabled by default so never had an issue, with us migrating to SQL2005 xp_cmdshell needs to be enabled through the Configuration manager (i know how we do that) as it is off by default which is a security concern as suggested by our DBA and hence not allowed. Also, xp_cmdshell could only be executed by SA user or other user using proxy account with appropraite rights.

I looked at the alternative susggested by some about using SQL Agent Proxies so only certain users can access it.

Can somebody walk me through the steps of making it work for my SQL 2005 certain users using proxy account. i have tried but in vain... i'm not so good with creating windows users and domain policies(stuff)

p.s. Note that we will not be allowed to have xp_cmdshell enabled on the server by the DBA. so the solution has to not enable the xp_cmdshell from the SQL Configuration manager.

i also tried using SQL-CLR method of creating stored procedure but since we may have to use EXTERNAL_ACCESS or UNSAFE type while creating assembly as we use kernel.dll etc. to access file system through code it cannto be SAFE. we are back to square one because of security concern.

i also tried using xplog70.dll to create assembly which is the file used under the wrapper xp_cmdshell but it suggested it needs a .NET assembly and the dll is of previous version or something...

Any other ideas are welcome.

please help... my deadline is fast approaching...

my email id : sunnyny2003@.hotmail.com

Thanks.

Sunny.

I know at least 2 ways to solve this problem.

1. Create a job with ActiveX script step. In it, you may use standard VBS statements like Dir() and so on, so it will be relatively easy to obtain list of files, create ADODB.Connection object and insert the entire list into your table. You have only to decide who will be the owner of the job and how you will start it from your T-SQL code (there is a system stored procedure for this, sp_start_job, but you may run into security problems here, too).

2. You may use virtually the same approach as in the previous solution, but this time code it directly in T-SQL. There is a bunch of stored procedures to work with COM objects - sp_OACreate, sp_OAMethod and so on. Look here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ff16a833-01fe-4877-8aa6-55b72603ec2e.htm
for further details.
The only difference here is that you need to use a Scripting.FileSystemObject in order to reach Dir functionality through COM.|||

A SQL Agent Proxy account won't give a non-sysadmin access to use xp_cmdshell as part of a job step. A SQL Agent Proxy Account will only allow a non-syadmin to use a job step with the type of operating system.

In order to do what you need to do following what appear to be the same guidelines that I lay out for my developers.

Use xp_dirtree 'C:\Folder\With\Files\', 1, 1 to get a list of files within the folder that you specify. The second parameter tells it how many levels deep to scan. 1 tells the procedure to only use the current level. The third parameter tells it to include files. If the third parameter is not included then only folders are shown.

Then use the BULK INSERT command to load the files. This can all be done without needing access to xp_cmdshell or to the file system.

Let me know if this does or doesn't do the trick for you.

Thanks,

Denny

|||

Why don't u use sqlserver integration services (SSIS).

That would do the job perfectly.

You just have to create a "for loop container" which loops through the set of files you have on a directory and import them.

all these functionality is in SSIS.

|||

w.r.t to your suggestions

1. i cannot go that route as files are dynamic... i don't know the files beforehand... In this Architechture there is no UI involved, everything is supposed to be done on the sql server itself, also users install the app and use it creating database on the fly...

2. I did earlier tries sp_OAMethod in SQl2000 but with SQL2005 there is an issue that it doesn't not destroy the object once created because of which one cannot delete the database, which is a prime requirement in my case.

I'll need some solution driven more towards the agent proxy ...

|||

Denny,

Appreciate your response,

using xp_dirtree would work for me to gather file names from the folder to process...

this resolves one issue..

i also use xp_cmdshelll to copy, delete and rename these files after processing....

do you have some other xp_..... for these?

also, is there a way we can execute some exe's like we have one to retrieve the dos path for the folder with spaces etc.

Thanks.

Sunny

|||we cannto use ssis as we don't know before hand what files we have to create the process..|||

Sunnyny wrote:

w.r.t to your suggestions

1. i cannot go that route as files are dynamic... i don't know the files beforehand... In this Architechture there is no UI involved, everything is supposed to be done on the sql server itself, also users install the app and use it creating database on the fly...

Of course they are dynamic. Or you mean that exact directory you need to scan is dynamic also? Well, create a special table in your DB so user will insert there a path to the directory to scan. When SSIS package is started, it can retrieve required path from this table and scan it, as usual. Don't see anything too difficult here.

Sunnyny wrote:

2. I did earlier tries sp_OAMethod in SQl2000 but with SQL2005 there is an issue that it doesn't not destroy the object once created because of which one cannot delete the database, which is a prime requirement in my case.

I'll need some solution driven more towards the agent proxy ...

Don't understand - do you really need to drop database from your code?|||

yes, the folder is dynamic too and also sometimes the path is UNC path...

I'll look into SSIS..

do you have any samples or point me.. where we could have example of substituition of filenames etc?

sometimes users uninstall our database and start afresh and because of multiple connections created due to job running every minute and using sp_OAmethod and not destorying that object... Database is unable to be dropped.

|||

If you wanted to use SSIS to you could use a .NET script block to get the list name list for processing and then process it via the Loop that was mentioned above.

You can use xp_delete_file to delete files from the hard drive.

There aren't any extended stored procedures which are nativly installed with Microsoft SQL Server which can rename or copy files. You would need to write these in C++ and attach them to the SQL Server. Your DBA may not allow this to happen either. You may need to change your processing reuirements so that you process and delete, and have the process which gets the files back a backup of the file at that time, instead of while the import is processing. Or before you start the import process use a Command Line Job Step to copy any and all files in the folder to another place for backup purposes.

mrdenny

|||

... except xp_delete_file is notoriously flakey - it reports success even when it's not deleted what it's supposed to.

Search +xp_delete_file +problem - loads of stuff out there about how it simply doesn't work and no one has an answer to it.

Issue with xp_cmdshell in SQL2005

Folks,

I would like to make you aware in my App, we use xp_cmdshell to get a list of file names (using dir into a temp table) from a specific directory and process these files one after another to insert into Database through the Job.

In SQL2000 xp_cmdshell was enabled by default so never had an issue, with us migrating to SQL2005 xp_cmdshell needs to be enabled through the Configuration manager (i know how we do that) as it is off by default which is a security concern as suggested by our DBA and hence not allowed. Also, xp_cmdshell could only be executed by SA user or other user using proxy account with appropraite rights.

I looked at the alternative susggested by some about using SQL Agent Proxies so only certain users can access it.

Can somebody walk me through the steps of making it work for my SQL 2005 certain users using proxy account. i have tried but in vain... i'm not so good with creating windows users and domain policies(stuff)

p.s. Note that we will not be allowed to have xp_cmdshell enabled on the server by the DBA. so the solution has to not enable the xp_cmdshell from the SQL Configuration manager.

i also tried using SQL-CLR method of creating stored procedure but since we may have to use EXTERNAL_ACCESS or UNSAFE type while creating assembly as we use kernel.dll etc. to access file system through code it cannto be SAFE. we are back to square one because of security concern.

i also tried using xplog70.dll to create assembly which is the file used under the wrapper xp_cmdshell but it suggested it needs a .NET assembly and the dll is of previous version or something...

Any other ideas are welcome.

please help... my deadline is fast approaching...

my email id : sunnyny2003@.hotmail.com

Thanks.

Sunny.

I know at least 2 ways to solve this problem.

1. Create a job with ActiveX script step. In it, you may use standard VBS statements like Dir() and so on, so it will be relatively easy to obtain list of files, create ADODB.Connection object and insert the entire list into your table. You have only to decide who will be the owner of the job and how you will start it from your T-SQL code (there is a system stored procedure for this, sp_start_job, but you may run into security problems here, too).

2. You may use virtually the same approach as in the previous solution, but this time code it directly in T-SQL. There is a bunch of stored procedures to work with COM objects - sp_OACreate, sp_OAMethod and so on. Look here:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/ff16a833-01fe-4877-8aa6-55b72603ec2e.htm
for further details.
The only difference here is that you need to use a Scripting.FileSystemObject in order to reach Dir functionality through COM.|||

A SQL Agent Proxy account won't give a non-sysadmin access to use xp_cmdshell as part of a job step. A SQL Agent Proxy Account will only allow a non-syadmin to use a job step with the type of operating system.

In order to do what you need to do following what appear to be the same guidelines that I lay out for my developers.

Use xp_dirtree 'C:\Folder\With\Files\', 1, 1 to get a list of files within the folder that you specify. The second parameter tells it how many levels deep to scan. 1 tells the procedure to only use the current level. The third parameter tells it to include files. If the third parameter is not included then only folders are shown.

Then use the BULK INSERT command to load the files. This can all be done without needing access to xp_cmdshell or to the file system.

Let me know if this does or doesn't do the trick for you.

Thanks,

Denny

|||

Why don't u use sqlserver integration services (SSIS).

That would do the job perfectly.

You just have to create a "for loop container" which loops through the set of files you have on a directory and import them.

all these functionality is in SSIS.

|||

w.r.t to your suggestions

1. i cannot go that route as files are dynamic... i don't know the files beforehand... In this Architechture there is no UI involved, everything is supposed to be done on the sql server itself, also users install the app and use it creating database on the fly...

2. I did earlier tries sp_OAMethod in SQl2000 but with SQL2005 there is an issue that it doesn't not destroy the object once created because of which one cannot delete the database, which is a prime requirement in my case.

I'll need some solution driven more towards the agent proxy ...

|||

Denny,

Appreciate your response,

using xp_dirtree would work for me to gather file names from the folder to process...

this resolves one issue..

i also use xp_cmdshelll to copy, delete and rename these files after processing....

do you have some other xp_..... for these?

also, is there a way we can execute some exe's like we have one to retrieve the dos path for the folder with spaces etc.

Thanks.

Sunny

|||we cannto use ssis as we don't know before hand what files we have to create the process..|||

Sunnyny wrote:

w.r.t to your suggestions

1. i cannot go that route as files are dynamic... i don't know the files beforehand... In this Architechture there is no UI involved, everything is supposed to be done on the sql server itself, also users install the app and use it creating database on the fly...

Of course they are dynamic. Or you mean that exact directory you need to scan is dynamic also? Well, create a special table in your DB so user will insert there a path to the directory to scan. When SSIS package is started, it can retrieve required path from this table and scan it, as usual. Don't see anything too difficult here.

Sunnyny wrote:

2. I did earlier tries sp_OAMethod in SQl2000 but with SQL2005 there is an issue that it doesn't not destroy the object once created because of which one cannot delete the database, which is a prime requirement in my case.

I'll need some solution driven more towards the agent proxy ...

Don't understand - do you really need to drop database from your code?|||

yes, the folder is dynamic too and also sometimes the path is UNC path...

I'll look into SSIS..

do you have any samples or point me.. where we could have example of substituition of filenames etc?

sometimes users uninstall our database and start afresh and because of multiple connections created due to job running every minute and using sp_OAmethod and not destorying that object... Database is unable to be dropped.

|||

If you wanted to use SSIS to you could use a .NET script block to get the list name list for processing and then process it via the Loop that was mentioned above.

You can use xp_delete_file to delete files from the hard drive.

There aren't any extended stored procedures which are nativly installed with Microsoft SQL Server which can rename or copy files. You would need to write these in C++ and attach them to the SQL Server. Your DBA may not allow this to happen either. You may need to change your processing reuirements so that you process and delete, and have the process which gets the files back a backup of the file at that time, instead of while the import is processing. Or before you start the import process use a Command Line Job Step to copy any and all files in the folder to another place for backup purposes.

mrdenny

|||

... except xp_delete_file is notoriously flakey - it reports success even when it's not deleted what it's supposed to.

Search +xp_delete_file +problem - loads of stuff out there about how it simply doesn't work and no one has an answer to it.

issue with XML and Schema namespace

Hi I am having a hard time with validation of my XML file with given schema.
Here are the files:
roboerrorlog.xsd
<robomss:schema xmlns:robomss="http://www.w3.org/2001/XMLSchema"
xmlns="robomss"
elementFormDefault="qualified" targetNamespace="robomss">
<robomss:element name="roboErrorLog" type="errorLogType"/>
<robomss:complexType name="errorLogType">
<robomss:sequence maxOccurs="unbounded">
<robomss:element name="error" type="errorType"/>
</robomss:sequence>
</robomss:complexType>
<robomss:complexType name="errorType">
<robomss:sequence>
<robomss:element name="user" type="robomss:string"/>
<robomss:element name="datetime" type="robomss:string"/>
<robomss:element name="message" type="robomss:string"/>
<robomss:element name="stack" type="stackType"/>
</robomss:sequence>
</robomss:complexType>
<robomss:complexType name="stackType">
<robomss:sequence>
<robomss:element name="functionName" type="robomss:string"/>
<robomss:element name="lineNumber" type="robomss:string"/>
</robomss:sequence>
</robomss:complexType>
</robomss:schema>
roboerrorlog.xml
<?xml version="1.0" encoding="utf-8"?>
<roboErrorLog xmlns:robomss="http://www.w3.org/2001/XMLSchema">
<error>
<user>steve</user>
<datetime>12/18/2007 2:31:33 PM</datetime>
<message>Null reference error!</message>
<stack>
<functionName>Void btnSearch_Click(System.Object,
System.EventArgs)</functionName>
<lineNumber>300</lineNumber>
</stack>
</error>
<error>
<user>steve</user>
<datetime>12/18/2007 2:31:38 PM</datetime>
<message>Null reference error!</message>
<stack>
<functionName>Void btnSearch_Click(System.Object,
System.EventArgs)</functionName>
<lineNumber>300</lineNumber>
</stack>
</error>
<error>
<user>steve</user>
<datetime>12/18/2007 2:31:43 PM</datetime>
<message>Null reference error!</message>
<stack>
<functionName>Void btnSearch_Click(System.Object,
System.EventArgs)</functionName>
<lineNumber>300</lineNumber>
</stack>
</error>
<error>
<user>steve</user>
<datetime>12/18/2007 3:22:37 PM</datetime>
<message>Null reference error!</message>
<stack>
<functionName>Void btnSearch_Click(System.Object,
System.EventArgs)</functionName>
<lineNumber>300</lineNumber>
</stack>
</error>
<error>
<user>steve</user>
<datetime>12/18/2007 3:22:38 PM</datetime>
<message>Null reference error!</message>
<stack>
<functionName>Void btnSearch_Click(System.Object,
System.EventArgs)</functionName>
<lineNumber>300</lineNumber>
</stack>
</error>
</roboErrorLog>
When I pass these files to a validator it has an issue with the roboErrorLog
declaration.
Now the weird thing is if I change the namespace in the opening tag to
xmlns="robomss" than the validator sees it as valid
So the valid tag looks like <roboErrorLog xmlns="robomss"> rather than
<roboErrorLog xmlns:robomss="http://www.w3.org/2001/XMLSchema">
If anyone knows what the issue is I'd appreciate your help.Durango2008 wrote:

> <robomss:schema xmlns:robomss="http://www.w3.org/2001/XMLSchema"
> xmlns="robomss"
> elementFormDefault="qualified" targetNamespace="robomss">
As the targetNamespace of your schema is "robomss" and as
elementFormDefault is "qualified" the schema defines all its elements in
the namespace "robomss".
Thus for an XML instance document to be valid against the above schema
you need to have
<roboErrorLog xmlns="robomss">
or use some prefix (e.g. "rb")
<rb:roboErrorLog xmlns:rb="robomss">

> <roboErrorLog xmlns:robomss="http://www.w3.org/2001/XMLSchema">
Why do you think that the XML instance document needs to use the XSD
schema namespace "http://www.w3.org/2001/XMLSchema"? You need that in
the schema to indicate you are authoring a schema in the W3C schema
language but using that namespace in the instance document is not
meaningful.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/

issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine can
not
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody ha
s
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal
.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
ChristineA few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:

>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine ca
nnot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my deskto
p
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file
,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody h
as
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great dea
l.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through N
T
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:

> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
>
>|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through
NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa'
,
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server
)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote:
>

issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody has
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
Christine
A few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:

>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my desktop
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody has
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great deal.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>
|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through NT
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:

> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
>
>
|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa',
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote:

issue with linked server connecting to Access

Hi,
I have a linked server setup to connect to an access file from a filer
server. If I am on the server computer logged in using Windows NT
authencation, the connections works. However, if I use a native SQL login
(with sys admin rights) it gave the following error:
Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot
open the file '\\filer-la03p\Mbs\Credit Mortgage
Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: ].
The linked server has security mapping as remote user:"admin" with password
null.
I also have noticed that if I connect to the server using EM from my desktop
using NT authencation, the error ocurrs as well. In this case I will say it
is a "double-hopping" issue. But why when using a native SQL account will
have a problem?
From my understanding when using linked server to connect to an access file,
it is using the service account that starts SQL Server. I had setup all
privilages required for the SQL Service account. And as I inicated earlier,
it connects ok if I am at the server using NT authencation to log in.
I rearch online and it seems like I am not the only one who has problems
with linked server for access from other computers. Unfortunately, nobody has
given the resolutions that works for the requester.
Someone please help, we are a microsoft shop and everything is microsoft.
This problem has stopped our development for the business units a great deal.
Our clients were just bought in to convert a lot of their stuff to SQL
Server. Now they are going to back off!
Thanks if someone can respond quickly.
ChristineA few suggestions:
--Make sure that the Access mdb (and mdw) reside on a file share that
the Windows account has read/write/delete ACLs on for the files and
folders.
--Make sure that the Access mdb is totally unsecured (check the owner
property -- it should NOT say 'unknown').
--Make sure that the login you are using is Admin with an empty string
(''), not null.
--Use filemon (www.sysinternals.com) to check to see if SQLS is
getting access to the files/folders.
FWIW, linked servers to Access are a real PITA. You'd have a much
easier time if you worked it the other way around, linking to SQL
Server from the Access .mdb.
--Mary
On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
C@.discussions.microsoft.com> wrote:
>Hi,
>I have a linked server setup to connect to an access file from a filer
>server. If I am on the server computer logged in using Windows NT
>authencation, the connections works. However, if I use a native SQL login
>(with sys admin rights) it gave the following error:
>Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>open the file '\\filer-la03p\Mbs\Credit Mortgage
>Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>exclusively by another user, or you need permission to view its data.]
>OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>IDBInitialize::Initialize returned 0x80004005: ].
>The linked server has security mapping as remote user:"admin" with password
>null.
>I also have noticed that if I connect to the server using EM from my desktop
>using NT authencation, the error ocurrs as well. In this case I will say it
>is a "double-hopping" issue. But why when using a native SQL account will
>have a problem?
>From my understanding when using linked server to connect to an access file,
>it is using the service account that starts SQL Server. I had setup all
>privilages required for the SQL Service account. And as I inicated earlier,
>it connects ok if I am at the server using NT authencation to log in.
>I rearch online and it seems like I am not the only one who has problems
>with linked server for access from other computers. Unfortunately, nobody has
>given the resolutions that works for the requester.
>Someone please help, we are a microsoft shop and everything is microsoft.
>This problem has stopped our development for the business units a great deal.
>Our clients were just bought in to convert a lot of their stuff to SQL
>Server. Now they are going to back off!
>Thanks if someone can respond quickly.
>Christine
>|||Mary,
Thanks for replying.
All the checklists you suggested are all there and correct, otherwise it
wouldn't have worked for some cases. It would just not work AT ALL.
I have found the fix for one issue now, but not another:
1. After I added the spn for the server to the AD account that started SQL
Server, I was able to connect from my desktop to the linked server through NT
authencation.
Before this is only possible if I am at the server.
2. If I connect to SQL Server using SQL accounts, it still doesn't work on
either from the server or my desktop. So I was wondering if I login as 'sa',
what kind of security context that was verified against the file server on
the network? After it gets through the file system then the security check
will use "Admin" to open Access as specified in my linked server security
tab. But what happened before then?
I would assume either way (login as SQL or NT authencation to my SQL Server)
it should use the service account that starts SQL Server, but apparently it
is not the case.
I am really puzzled now...
"Mary Chipman" wrote:
> A few suggestions:
> --Make sure that the Access mdb (and mdw) reside on a file share that
> the Windows account has read/write/delete ACLs on for the files and
> folders.
> --Make sure that the Access mdb is totally unsecured (check the owner
> property -- it should NOT say 'unknown').
> --Make sure that the login you are using is Admin with an empty string
> (''), not null.
> --Use filemon (www.sysinternals.com) to check to see if SQLS is
> getting access to the files/folders.
> FWIW, linked servers to Access are a real PITA. You'd have a much
> easier time if you worked it the other way around, linking to SQL
> Server from the Access .mdb.
> --Mary
> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
> C@.discussions.microsoft.com> wrote:
> >Hi,
> >
> >I have a linked server setup to connect to an access file from a filer
> >server. If I am on the server computer logged in using Windows NT
> >authencation, the connections works. However, if I use a native SQL login
> >(with sys admin rights) it gave the following error:
> >
> >Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
> >
> >OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
> >
> >[OLE/DB provider returned message: The Microsoft Jet database engine cannot
> >open the file '\\filer-la03p\Mbs\Credit Mortgage
> >Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
> >exclusively by another user, or you need permission to view its data.]
> >
> >OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
> >IDBInitialize::Initialize returned 0x80004005: ].
> >
> >The linked server has security mapping as remote user:"admin" with password
> >null.
> >I also have noticed that if I connect to the server using EM from my desktop
> >using NT authencation, the error ocurrs as well. In this case I will say it
> >is a "double-hopping" issue. But why when using a native SQL account will
> >have a problem?
> >
> >From my understanding when using linked server to connect to an access file,
> >it is using the service account that starts SQL Server. I had setup all
> >privilages required for the SQL Service account. And as I inicated earlier,
> >it connects ok if I am at the server using NT authencation to log in.
> >
> >I rearch online and it seems like I am not the only one who has problems
> >with linked server for access from other computers. Unfortunately, nobody has
> >given the resolutions that works for the requester.
> >
> >Someone please help, we are a microsoft shop and everything is microsoft.
> >This problem has stopped our development for the business units a great deal.
> >Our clients were just bought in to convert a lot of their stuff to SQL
> >Server. Now they are going to back off!
> >
> >Thanks if someone can respond quickly.
> >
> >Christine
> >
>|||What did filemon show?
--Mary
On Thu, 14 Oct 2004 16:31:02 -0700, "Christine C"
<ChristineC@.discussions.microsoft.com> wrote:
>Mary,
>Thanks for replying.
>All the checklists you suggested are all there and correct, otherwise it
>wouldn't have worked for some cases. It would just not work AT ALL.
>I have found the fix for one issue now, but not another:
>1. After I added the spn for the server to the AD account that started SQL
>Server, I was able to connect from my desktop to the linked server through NT
>authencation.
>Before this is only possible if I am at the server.
>2. If I connect to SQL Server using SQL accounts, it still doesn't work on
>either from the server or my desktop. So I was wondering if I login as 'sa',
>what kind of security context that was verified against the file server on
>the network? After it gets through the file system then the security check
>will use "Admin" to open Access as specified in my linked server security
>tab. But what happened before then?
>I would assume either way (login as SQL or NT authencation to my SQL Server)
>it should use the service account that starts SQL Server, but apparently it
>is not the case.
>I am really puzzled now...
>
>"Mary Chipman" wrote:
>> A few suggestions:
>> --Make sure that the Access mdb (and mdw) reside on a file share that
>> the Windows account has read/write/delete ACLs on for the files and
>> folders.
>> --Make sure that the Access mdb is totally unsecured (check the owner
>> property -- it should NOT say 'unknown').
>> --Make sure that the login you are using is Admin with an empty string
>> (''), not null.
>> --Use filemon (www.sysinternals.com) to check to see if SQLS is
>> getting access to the files/folders.
>> FWIW, linked servers to Access are a real PITA. You'd have a much
>> easier time if you worked it the other way around, linking to SQL
>> Server from the Access .mdb.
>> --Mary
>> On Thu, 14 Oct 2004 11:35:04 -0700, "Christine C" <Christine
>> C@.discussions.microsoft.com> wrote:
>> >Hi,
>> >
>> >I have a linked server setup to connect to an access file from a filer
>> >server. If I am on the server computer logged in using Windows NT
>> >authencation, the connections works. However, if I use a native SQL login
>> >(with sys admin rights) it gave the following error:
>> >
>> >Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 20
>> >
>> >OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
>> >
>> >[OLE/DB provider returned message: The Microsoft Jet database engine cannot
>> >open the file '\\filer-la03p\Mbs\Credit Mortgage
>> >Group\Programs\Databases\Credit MBS Data.mdb'. It is already opened
>> >exclusively by another user, or you need permission to view its data.]
>> >
>> >OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
>> >IDBInitialize::Initialize returned 0x80004005: ].
>> >
>> >The linked server has security mapping as remote user:"admin" with password
>> >null.
>> >I also have noticed that if I connect to the server using EM from my desktop
>> >using NT authencation, the error ocurrs as well. In this case I will say it
>> >is a "double-hopping" issue. But why when using a native SQL account will
>> >have a problem?
>> >
>> >From my understanding when using linked server to connect to an access file,
>> >it is using the service account that starts SQL Server. I had setup all
>> >privilages required for the SQL Service account. And as I inicated earlier,
>> >it connects ok if I am at the server using NT authencation to log in.
>> >
>> >I rearch online and it seems like I am not the only one who has problems
>> >with linked server for access from other computers. Unfortunately, nobody has
>> >given the resolutions that works for the requester.
>> >
>> >Someone please help, we are a microsoft shop and everything is microsoft.
>> >This problem has stopped our development for the business units a great deal.
>> >Our clients were just bought in to convert a lot of their stuff to SQL
>> >Server. Now they are going to back off!
>> >
>> >Thanks if someone can respond quickly.
>> >
>> >Christine
>> >
>>