Cached Data to Linked Server

I have SQL Server 7.0 set up to access a flat file FoxPro database sitting
on an NFS mount (using Services For Unix) as a linked server. Basic
functionality is working great, but I have a couple of nagging problems I'd
like to get solved before I move this into production.
One is that SQL Server seems to cache the FoxPro database for around 5
minutes if there's no activity, possibly longer (or forever?) if the FoxPro
database is being queried. I'm using a stored procedure to run a SELECT *
FROM OPENQUERY() if that makes a difference....
Also, if the NFS server is down and not responding, SQL doesn't timeout for
92 seconds. I've tried setting "remote login timeout", but it doesn't seem
to have any effect. If I set "remote query timeout", then OPENQUERY tells me
it can't set an OLEDB property.
Any ideas?
mbrackett@.bsd.ufl.edu
I don't believe that SQL Server does any caching for the data from a linked
server. You can specify a timeout value for connections to a linked server
by using the sp_serveroption stored procedure in conjunction with the
'connect timeout' option.
Michael Otey
"Mark Brackett" <mbrackett@.bsd.ufl.edu> wrote in message
news:eE9qWxFaEHA.4052@.TK2MSFTNGP10.phx.gbl...
> I have SQL Server 7.0 set up to access a flat file FoxPro database sitting
> on an NFS mount (using Services For Unix) as a linked server. Basic
> functionality is working great, but I have a couple of nagging problems
I'd
> like to get solved before I move this into production.
> One is that SQL Server seems to cache the FoxPro database for around 5
> minutes if there's no activity, possibly longer (or forever?) if the
FoxPro
> database is being queried. I'm using a stored procedure to run a SELECT *
> FROM OPENQUERY() if that makes a difference....
> Also, if the NFS server is down and not responding, SQL doesn't timeout
for
> 92 seconds. I've tried setting "remote login timeout", but it doesn't seem
> to have any effect. If I set "remote query timeout", then OPENQUERY tells
me
> it can't set an OLEDB property.
> Any ideas?
> mbrackett@.bsd.ufl.edu
>

Cached Data to Linked Server

I have SQL Server 7.0 set up to access a flat file FoxPro database sitting
on an NFS mount (using Services For Unix) as a linked server. Basic
functionality is working great, but I have a couple of nagging problems I'd
like to get solved before I move this into production.
One is that SQL Server seems to cache the FoxPro database for around 5
minutes if there's no activity, possibly longer (or forever?) if the FoxPro
database is being queried. I'm using a stored procedure to run a SELECT *
FROM OPENQUERY() if that makes a difference....
Also, if the NFS server is down and not responding, SQL doesn't timeout for
92 seconds. I've tried setting "remote login timeout", but it doesn't seem
to have any effect. If I set "remote query timeout", then OPENQUERY tells me
it can't set an OLEDB property.
Any ideas?
mbrackett@.bsd.ufl.eduI don't believe that SQL Server does any caching for the data from a linked
server. You can specify a timeout value for connections to a linked server
by using the sp_serveroption stored procedure in conjunction with the
'connect timeout' option.
Michael Otey
"Mark Brackett" <mbrackett@.bsd.ufl.edu> wrote in message
news:eE9qWxFaEHA.4052@.TK2MSFTNGP10.phx.gbl...
> I have SQL Server 7.0 set up to access a flat file FoxPro database sitting
> on an NFS mount (using Services For Unix) as a linked server. Basic
> functionality is working great, but I have a couple of nagging problems
I'd
> like to get solved before I move this into production.
> One is that SQL Server seems to cache the FoxPro database for around 5
> minutes if there's no activity, possibly longer (or forever?) if the
FoxPro
> database is being queried. I'm using a stored procedure to run a SELECT *
> FROM OPENQUERY() if that makes a difference....
> Also, if the NFS server is down and not responding, SQL doesn't timeout
for
> 92 seconds. I've tried setting "remote login timeout", but it doesn't seem
> to have any effect. If I set "remote query timeout", then OPENQUERY tells
me
> it can't set an OLEDB property.
> Any ideas?
> mbrackett@.bsd.ufl.edu
>

C2 SQL auditing

Hi,
I setup my SQL 2000 Server to use C2 auditing. It is working.
My only problem is that my trace file does not get populated while I am
inserting/updating/select data or anything.
However, when I stop SQL server then my trace file gets populated.
I cannot stop a production SQL server on and off just to collect data and
the trace file is only limited to 200MB so I can wait till the end of day.
I am trying to do a audit report every 2hrs.
Am I doing something wrong? Or this is how C2 works?
I would appreciate all the help!Don't use the C2 auditing. Just create a custom trace that collects the data
you want and you can start and stop it as you need to. Check out
sp_trace_Create in BooksOnLine.
Andrew J. Kelly SQL MVP
"SQL apprentice" <mssqlworld@.yahoo.com> wrote in message
news:OYlONfeVHHA.3500@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I setup my SQL 2000 Server to use C2 auditing. It is working.
> My only problem is that my trace file does not get populated while I am
> inserting/updating/select data or anything.
> However, when I stop SQL server then my trace file gets populated.
> I cannot stop a production SQL server on and off just to collect data and
> the trace file is only limited to 200MB so I can wait till the end of day.
> I am trying to do a audit report every 2hrs.
> Am I doing something wrong? Or this is how C2 works?
> I would appreciate all the help!
>|||Hi Andrew,
I had to setup C2 for my company just so they can see it in their own eyes.
I recommended them to use Server Side trace, it is more efficiency and
customizable to what they want to audit.
It would make my case even better when I tell them 3 SQL MVPs suggest not to
use C2.
Do you suggest any third party tools for SOX compliance? I am testing Idera
CM right now.
Eventually, I would like to off load the server side trace to the SOX team
since there are over 100 SQL Servers.
I would like to setup and manage so many traces.
Thanks again for the input...I greatly appreciated.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OqOIdLiVHHA.3592@.TK2MSFTNGP03.phx.gbl...
> Don't use the C2 auditing. Just create a custom trace that collects the
data
> you want and you can start and stop it as you need to. Check out
> sp_trace_Create in BooksOnLine.
> --
> Andrew J. Kelly SQL MVP
> "SQL apprentice" <mssqlworld@.yahoo.com> wrote in message
> news:OYlONfeVHHA.3500@.TK2MSFTNGP05.phx.gbl...
and[vbcol=seagreen]
day.[vbcol=seagreen]
>|||I don't have a specific recomendation as the SOX requirements are very loose
and open to interpitation. There are a number of 3rd party tools that
monitor the logs.
http://sqlserver2000.databases.aspf...t
a.html
http://sqlserver2000.databases.aspf...
log-files.html
Andrew J. Kelly SQL MVP
"SQL apprentice" <mssqlworld@.yahoo.com> wrote in message
news:ufl7fZpVHHA.1636@.TK2MSFTNGP02.phx.gbl...
> Hi Andrew,
> I had to setup C2 for my company just so they can see it in their own
> eyes.
> I recommended them to use Server Side trace, it is more efficiency and
> customizable to what they want to audit.
> It would make my case even better when I tell them 3 SQL MVPs suggest not
> to
> use C2.
> Do you suggest any third party tools for SOX compliance? I am testing
> Idera
> CM right now.
> Eventually, I would like to off load the server side trace to the SOX team
> since there are over 100 SQL Servers.
> I would like to setup and manage so many traces.
> Thanks again for the input...I greatly appreciated.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OqOIdLiVHHA.3592@.TK2MSFTNGP03.phx.gbl...
> data
> and
> day.
>

c2 audit ,about hostname coloum value

Hi!
When the audit mode is set to 1 and reconfigured ,
Sql Profiler can read audit file which have a colum called HostName record
the computer name of source Computer. How can I change this vaue to the
source computer's IP address.
And Can I change the audit's file to another directory ?
thanks .HI,
There is no data column named Host IP address in profiler, as well as you
cant change the
data columns for C2 Audit option.
Can I change the audit's file to another directory ?
No, you cant change the directory.
Thanks
Hari
MCDBA
"william.huang" <william.huang@.saturn.yzu.edu.tw> wrote in message
news:OBTku6kXEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Hi!
> When the audit mode is set to 1 and reconfigured ,
> Sql Profiler can read audit file which have a colum called HostName
record
> the computer name of source Computer. How can I change this vaue to the
> source computer's IP address.
> And Can I change the audit's file to another directory ?
> thanks .
>

C:\SQL.LOG File, And growing very large

We have a Micrsoft SQL Server 7.0, running on Windows 2000 Server SP 4. The
problem is while the SQL service is running it will create a file,
C:\SQL.LOG and it will grow very large. Over a 5 minute period it was about
1.5 M. As you can imagine, it doesn't take long for this file to grow into
Gigs. Any idea what is causing this file? here are some of the contents:
sqlagent 31c-4bc ENTER SQLAllocEnv
HENV * 410F6438
sqlagent 31c-4bc EXIT SQLAllocEnv with return code 0 (SQL_SUCCESS)
HENV * 0x410F6438 ( 0x00581540)
sqlagent 31c-81c ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 00CDE1D8
sqlagent 31c-81c EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 0x00CDE1D8 ( 0x005815e8)
sqlagent 31c-81c ENTER SQLSetEnvAttr
SQLHENV 005815E8
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetEnvAttr with return code 0
(SQL_SUCCESS)
SQLHENV 005815E8
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003 (BADMEM)
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 005815E8
SQLHANDLE * 00CDE1DC
sqlagent 31c-81c EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 005815E8
SQLHANDLE * 0x00CDE1DC ( 0x00581690)
sqlagent 31c-81c ENTER SQLSetConnectAttrW
SQLHDBC 00581690
SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
SQLPOINTER 0x0000001E
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 00581690
SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
SQLPOINTER 0x0000001E (BADMEM)
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLSetConnectAttrW
SQLHDBC 00581690
SQLINTEGER 1217 <unknown>
SQLPOINTER [Unknown attribute 1217]
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 00581690
SQLINTEGER 1217 <unknown>
SQLPOINTER [Unknown attribute 1217]
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLSetConnectAttrW
SQLHDBC 00581690
SQLINTEGER 112 <SQL_ATTR_PACKET_SIZE>
SQLPOINTER 0x00000000
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 00581690
SQLINTEGER 112 <SQL_ATTR_PACKET_SIZE>
SQLPOINTER 0x00000000
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLSetConnectAttrW
SQLHDBC 00581690
SQLINTEGER 1203 <unknown>
SQLPOINTER [Unknown attribute 1203]
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 00581690
SQLINTEGER 1203 <unknown>
SQLPOINTER [Unknown attribute 1203]
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLDriverConnectW
HDBC 00581690
HWND 00000000
WCHAR * 0x1F7C48DC [ -3] "******\ 0"
SWORD -3
WCHAR * 0x1F7C48DC
SWORD 8
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
sqlagent 31c-81c EXIT SQLDriverConnectW with return code 1
(SQL_SUCCESS_WITH_INFO)
HDBC 00581690
HWND 00000000
WCHAR * 0x1F7C48DC [ -3] "******\ 0"
SWORD -3
WCHAR * 0x1F7C48DC
SWORD 8
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed
database context to 'msdb'. (5701)
DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed
language setting to us_english. (5703)
There's more, but I'll spare you.
Ryan
Woops, sorry, forgot I turned on ODBC tracing. It was on the Tracing tab on
odbcad32
I turned it off and it went away.
"Ryan McAtee" <rmcatee_fwd@.verizon.net> wrote in message
news:%23TH%23KphGHHA.4688@.TK2MSFTNGP04.phx.gbl...
> We have a Micrsoft SQL Server 7.0, running on Windows 2000 Server SP 4.
> The problem is while the SQL service is running it will create a file,
> C:\SQL.LOG and it will grow very large. Over a 5 minute period it was
> about 1.5 M. As you can imagine, it doesn't take long for this file to
> grow into Gigs. Any idea what is causing this file? here are some of the
> contents:
>
> sqlagent 31c-4bc ENTER SQLAllocEnv
> HENV * 410F6438
> sqlagent 31c-4bc EXIT SQLAllocEnv with return code 0
> (SQL_SUCCESS)
> HENV * 0x410F6438 ( 0x00581540)
> sqlagent 31c-81c ENTER SQLAllocHandle
> SQLSMALLINT 1 <SQL_HANDLE_ENV>
> SQLHANDLE 00000000
> SQLHANDLE * 00CDE1D8
> sqlagent 31c-81c EXIT SQLAllocHandle with return code 0
> (SQL_SUCCESS)
> SQLSMALLINT 1 <SQL_HANDLE_ENV>
> SQLHANDLE 00000000
> SQLHANDLE * 0x00CDE1D8 ( 0x005815e8)
> sqlagent 31c-81c ENTER SQLSetEnvAttr
> SQLHENV 005815E8
> SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
> SQLPOINTER 0x00000003
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetEnvAttr with return code 0
> (SQL_SUCCESS)
> SQLHENV 005815E8
> SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
> SQLPOINTER 0x00000003 (BADMEM)
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLAllocHandle
> SQLSMALLINT 2 <SQL_HANDLE_DBC>
> SQLHANDLE 005815E8
> SQLHANDLE * 00CDE1DC
> sqlagent 31c-81c EXIT SQLAllocHandle with return code 0
> (SQL_SUCCESS)
> SQLSMALLINT 2 <SQL_HANDLE_DBC>
> SQLHANDLE 005815E8
> SQLHANDLE * 0x00CDE1DC ( 0x00581690)
> sqlagent 31c-81c ENTER SQLSetConnectAttrW
> SQLHDBC 00581690
> SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
> SQLPOINTER 0x0000001E
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 00581690
> SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
> SQLPOINTER 0x0000001E (BADMEM)
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLSetConnectAttrW
> SQLHDBC 00581690
> SQLINTEGER 1217 <unknown>
> SQLPOINTER [Unknown attribute 1217]
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 00581690
> SQLINTEGER 1217 <unknown>
> SQLPOINTER [Unknown attribute 1217]
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLSetConnectAttrW
> SQLHDBC 00581690
> SQLINTEGER 112 <SQL_ATTR_PACKET_SIZE>
> SQLPOINTER 0x00000000
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 00581690
> SQLINTEGER 112 <SQL_ATTR_PACKET_SIZE>
> SQLPOINTER 0x00000000
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLSetConnectAttrW
> SQLHDBC 00581690
> SQLINTEGER 1203 <unknown>
> SQLPOINTER [Unknown attribute 1203]
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 00581690
> SQLINTEGER 1203 <unknown>
> SQLPOINTER [Unknown attribute 1203]
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLDriverConnectW
> HDBC 00581690
> HWND 00000000
> WCHAR * 0x1F7C48DC [ -3] "******\ 0"
> SWORD -3
> WCHAR * 0x1F7C48DC
> SWORD 8
> SWORD * 0x00000000
> UWORD 0 <SQL_DRIVER_NOPROMPT>
> sqlagent 31c-81c EXIT SQLDriverConnectW with return code 1
> (SQL_SUCCESS_WITH_INFO)
> HDBC 00581690
> HWND 00000000
> WCHAR * 0x1F7C48DC [ -3] "******\ 0"
> SWORD -3
> WCHAR * 0x1F7C48DC
> SWORD 8
> SWORD * 0x00000000
> UWORD 0 <SQL_DRIVER_NOPROMPT>
> DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed
> database context to 'msdb'. (5701)
> DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed
> language setting to us_english. (5703)
>
> There's more, but I'll spare you.
> Ryan
>

C:\SQL.LOG File, And growing very large

We have a Micrsoft SQL Server 7.0, running on Windows 2000 Server SP 4. The
problem is while the SQL service is running it will create a file,
C:\SQL.LOG and it will grow very large. Over a 5 minute period it was about
1.5 M. As you can imagine, it doesn't take long for this file to grow into
Gigs. Any idea what is causing this file? here are some of the contents:
sqlagent 31c-4bc ENTER SQLAllocEnv
HENV * 410F6438
sqlagent 31c-4bc EXIT SQLAllocEnv with return code 0 (SQL_SUCCESS)
HENV * 0x410F6438 ( 0x00581540)
sqlagent 31c-81c ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 00CDE1D8
sqlagent 31c-81c EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 0x00CDE1D8 ( 0x005815e8)
sqlagent 31c-81c ENTER SQLSetEnvAttr
SQLHENV 005815E8
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetEnvAttr with return code 0
(SQL_SUCCESS)
SQLHENV 005815E8
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003 (BADMEM)
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 005815E8
SQLHANDLE * 00CDE1DC
sqlagent 31c-81c EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 005815E8
SQLHANDLE * 0x00CDE1DC ( 0x00581690)
sqlagent 31c-81c ENTER SQLSetConnectAttrW
SQLHDBC 00581690
SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
SQLPOINTER 0x0000001E
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 00581690
SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
SQLPOINTER 0x0000001E (BADMEM)
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLSetConnectAttrW
SQLHDBC 00581690
SQLINTEGER 1217 <unknown>
SQLPOINTER [Unknown attribute 1217]
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 00581690
SQLINTEGER 1217 <unknown>
SQLPOINTER [Unknown attribute 1217]
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLSetConnectAttrW
SQLHDBC 00581690
SQLINTEGER 112 <SQL_ATTR_PACKET_SIZE>
SQLPOINTER 0x00000000
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 00581690
SQLINTEGER 112 <SQL_ATTR_PACKET_SIZE>
SQLPOINTER 0x00000000
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLSetConnectAttrW
SQLHDBC 00581690
SQLINTEGER 1203 <unknown>
SQLPOINTER [Unknown attribute 1203]
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 00581690
SQLINTEGER 1203 <unknown>
SQLPOINTER [Unknown attribute 1203]
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLDriverConnectW
HDBC 00581690
HWND 00000000
WCHAR * 0x1F7C48DC [ -3] "******\ 0"
SWORD -3
WCHAR * 0x1F7C48DC
SWORD 8
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
sqlagent 31c-81c EXIT SQLDriverConnectW with return code 1
(SQL_SUCCESS_WITH_INFO)
HDBC 00581690
HWND 00000000
WCHAR * 0x1F7C48DC [ -3] "******\ 0"
SWORD -3
WCHAR * 0x1F7C48DC
SWORD 8
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed
database context to 'msdb'. (5701)
DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed
language setting to us_english. (5703)
There's more, but I'll spare you.
RyanWoops, sorry, forgot I turned on ODBC tracing. It was on the Tracing tab on
odbcad32
I turned it off and it went away.
"Ryan McAtee" <rmcatee_fwd@.verizon.net> wrote in message
news:%23TH%23KphGHHA.4688@.TK2MSFTNGP04.phx.gbl...
> We have a Micrsoft SQL Server 7.0, running on Windows 2000 Server SP 4.
> The problem is while the SQL service is running it will create a file,
> C:\SQL.LOG and it will grow very large. Over a 5 minute period it was
> about 1.5 M. As you can imagine, it doesn't take long for this file to
> grow into Gigs. Any idea what is causing this file? here are some of the
> contents:
>
> sqlagent 31c-4bc ENTER SQLAllocEnv
> HENV * 410F6438
> sqlagent 31c-4bc EXIT SQLAllocEnv with return code 0
> (SQL_SUCCESS)
> HENV * 0x410F6438 ( 0x00581540)
> sqlagent 31c-81c ENTER SQLAllocHandle
> SQLSMALLINT 1 <SQL_HANDLE_ENV>
> SQLHANDLE 00000000
> SQLHANDLE * 00CDE1D8
> sqlagent 31c-81c EXIT SQLAllocHandle with return code 0
> (SQL_SUCCESS)
> SQLSMALLINT 1 <SQL_HANDLE_ENV>
> SQLHANDLE 00000000
> SQLHANDLE * 0x00CDE1D8 ( 0x005815e8)
> sqlagent 31c-81c ENTER SQLSetEnvAttr
> SQLHENV 005815E8
> SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
> SQLPOINTER 0x00000003
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetEnvAttr with return code 0
> (SQL_SUCCESS)
> SQLHENV 005815E8
> SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
> SQLPOINTER 0x00000003 (BADMEM)
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLAllocHandle
> SQLSMALLINT 2 <SQL_HANDLE_DBC>
> SQLHANDLE 005815E8
> SQLHANDLE * 00CDE1DC
> sqlagent 31c-81c EXIT SQLAllocHandle with return code 0
> (SQL_SUCCESS)
> SQLSMALLINT 2 <SQL_HANDLE_DBC>
> SQLHANDLE 005815E8
> SQLHANDLE * 0x00CDE1DC ( 0x00581690)
> sqlagent 31c-81c ENTER SQLSetConnectAttrW
> SQLHDBC 00581690
> SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
> SQLPOINTER 0x0000001E
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 00581690
> SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
> SQLPOINTER 0x0000001E (BADMEM)
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLSetConnectAttrW
> SQLHDBC 00581690
> SQLINTEGER 1217 <unknown>
> SQLPOINTER [Unknown attribute 1217]
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 00581690
> SQLINTEGER 1217 <unknown>
> SQLPOINTER [Unknown attribute 1217]
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLSetConnectAttrW
> SQLHDBC 00581690
> SQLINTEGER 112 <SQL_ATTR_PACKET_SIZE>
> SQLPOINTER 0x00000000
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 00581690
> SQLINTEGER 112 <SQL_ATTR_PACKET_SIZE>
> SQLPOINTER 0x00000000
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLSetConnectAttrW
> SQLHDBC 00581690
> SQLINTEGER 1203 <unknown>
> SQLPOINTER [Unknown attribute 1203]
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 00581690
> SQLINTEGER 1203 <unknown>
> SQLPOINTER [Unknown attribute 1203]
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLDriverConnectW
> HDBC 00581690
> HWND 00000000
> WCHAR * 0x1F7C48DC [ -3] "******\ 0"
> SWORD -3
> WCHAR * 0x1F7C48DC
> SWORD 8
> SWORD * 0x00000000
> UWORD 0 <SQL_DRIVER_NOPROMPT>
> sqlagent 31c-81c EXIT SQLDriverConnectW with return code 1
> (SQL_SUCCESS_WITH_INFO)
> HDBC 00581690
> HWND 00000000
> WCHAR * 0x1F7C48DC [ -3] "******\ 0"
> SWORD -3
> WCHAR * 0x1F7C48DC
> SWORD 8
> SWORD * 0x00000000
> UWORD 0 <SQL_DRIVER_NOPROMPT>
> DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed
> database context to 'msdb'. (5701)
> DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed
> language setting to us_english. (5703)
>
> There's more, but I'll spare you.
> Ryan
>

C:\SQL.LOG File, And growing very large

We have a Micrsoft SQL Server 7.0, running on Windows 2000 Server SP 4. The
problem is while the SQL service is running it will create a file,
C:\SQL.LOG and it will grow very large. Over a 5 minute period it was about
1.5 M. As you can imagine, it doesn't take long for this file to grow into
Gigs. Any idea what is causing this file? here are some of the contents:
sqlagent 31c-4bc ENTER SQLAllocEnv
HENV * 410F6438
sqlagent 31c-4bc EXIT SQLAllocEnv with return code 0 (SQL_SUCCESS)
HENV * 0x410F6438 ( 0x00581540)
sqlagent 31c-81c ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 00CDE1D8
sqlagent 31c-81c EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 0x00CDE1D8 ( 0x005815e8)
sqlagent 31c-81c ENTER SQLSetEnvAttr
SQLHENV 005815E8
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetEnvAttr with return code 0
(SQL_SUCCESS)
SQLHENV 005815E8
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003 (BADMEM)
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 005815E8
SQLHANDLE * 00CDE1DC
sqlagent 31c-81c EXIT SQLAllocHandle with return code 0
(SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 005815E8
SQLHANDLE * 0x00CDE1DC ( 0x00581690)
sqlagent 31c-81c ENTER SQLSetConnectAttrW
SQLHDBC 00581690
SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
SQLPOINTER 0x0000001E
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 00581690
SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
SQLPOINTER 0x0000001E (BADMEM)
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLSetConnectAttrW
SQLHDBC 00581690
SQLINTEGER 1217 <unknown>
SQLPOINTER [Unknown attribute 1217]
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 00581690
SQLINTEGER 1217 <unknown>
SQLPOINTER [Unknown attribute 1217]
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLSetConnectAttrW
SQLHDBC 00581690
SQLINTEGER 112 <SQL_ATTR_PACKET_SIZE>
SQLPOINTER 0x00000000
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 00581690
SQLINTEGER 112 <SQL_ATTR_PACKET_SIZE>
SQLPOINTER 0x00000000
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLSetConnectAttrW
SQLHDBC 00581690
SQLINTEGER 1203 <unknown>
SQLPOINTER [Unknown attribute 1203]
SQLINTEGER -5
sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
(SQL_SUCCESS)
SQLHDBC 00581690
SQLINTEGER 1203 <unknown>
SQLPOINTER [Unknown attribute 1203]
SQLINTEGER -5
sqlagent 31c-81c ENTER SQLDriverConnectW
HDBC 00581690
HWND 00000000
WCHAR * 0x1F7C48DC [ -3] "******\ 0"
SWORD -3
WCHAR * 0x1F7C48DC
SWORD 8
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
sqlagent 31c-81c EXIT SQLDriverConnectW with return code 1
(SQL_SUCCESS_WITH_INFO)
HDBC 00581690
HWND 00000000
WCHAR * 0x1F7C48DC [ -3] "******\ 0"
SWORD -3
WCHAR * 0x1F7C48DC
SWORD 8
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Changed
database context to 'msdb'. (5701)
DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]
Changed
language setting to us_english. (5703)
There's more, but I'll spare you.
RyanWoops, sorry, forgot I turned on ODBC tracing. It was on the Tracing tab on
odbcad32
I turned it off and it went away.
"Ryan McAtee" <rmcatee_fwd@.verizon.net> wrote in message
news:%23TH%23KphGHHA.4688@.TK2MSFTNGP04.phx.gbl...
> We have a Micrsoft SQL Server 7.0, running on Windows 2000 Server SP 4.
> The problem is while the SQL service is running it will create a file,
> C:\SQL.LOG and it will grow very large. Over a 5 minute period it was
> about 1.5 M. As you can imagine, it doesn't take long for this file to
> grow into Gigs. Any idea what is causing this file? here are some of the
> contents:
>
> sqlagent 31c-4bc ENTER SQLAllocEnv
> HENV * 410F6438
> sqlagent 31c-4bc EXIT SQLAllocEnv with return code 0
> (SQL_SUCCESS)
> HENV * 0x410F6438 ( 0x00581540)
> sqlagent 31c-81c ENTER SQLAllocHandle
> SQLSMALLINT 1 <SQL_HANDLE_ENV>
> SQLHANDLE 00000000
> SQLHANDLE * 00CDE1D8
> sqlagent 31c-81c EXIT SQLAllocHandle with return code 0
> (SQL_SUCCESS)
> SQLSMALLINT 1 <SQL_HANDLE_ENV>
> SQLHANDLE 00000000
> SQLHANDLE * 0x00CDE1D8 ( 0x005815e8)
> sqlagent 31c-81c ENTER SQLSetEnvAttr
> SQLHENV 005815E8
> SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
> SQLPOINTER 0x00000003
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetEnvAttr with return code 0
> (SQL_SUCCESS)
> SQLHENV 005815E8
> SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
> SQLPOINTER 0x00000003 (BADMEM)
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLAllocHandle
> SQLSMALLINT 2 <SQL_HANDLE_DBC>
> SQLHANDLE 005815E8
> SQLHANDLE * 00CDE1DC
> sqlagent 31c-81c EXIT SQLAllocHandle with return code 0
> (SQL_SUCCESS)
> SQLSMALLINT 2 <SQL_HANDLE_DBC>
> SQLHANDLE 005815E8
> SQLHANDLE * 0x00CDE1DC ( 0x00581690)
> sqlagent 31c-81c ENTER SQLSetConnectAttrW
> SQLHDBC 00581690
> SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
> SQLPOINTER 0x0000001E
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 00581690
> SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>
> SQLPOINTER 0x0000001E (BADMEM)
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLSetConnectAttrW
> SQLHDBC 00581690
> SQLINTEGER 1217 <unknown>
> SQLPOINTER [Unknown attribute 1217]
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 00581690
> SQLINTEGER 1217 <unknown>
> SQLPOINTER [Unknown attribute 1217]
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLSetConnectAttrW
> SQLHDBC 00581690
> SQLINTEGER 112 <SQL_ATTR_PACKET_SIZE>
> SQLPOINTER 0x00000000
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 00581690
> SQLINTEGER 112 <SQL_ATTR_PACKET_SIZE>
> SQLPOINTER 0x00000000
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLSetConnectAttrW
> SQLHDBC 00581690
> SQLINTEGER 1203 <unknown>
> SQLPOINTER [Unknown attribute 1203]
> SQLINTEGER -5
> sqlagent 31c-81c EXIT SQLSetConnectAttrW with return code 0
> (SQL_SUCCESS)
> SQLHDBC 00581690
> SQLINTEGER 1203 <unknown>
> SQLPOINTER [Unknown attribute 1203]
> SQLINTEGER -5
> sqlagent 31c-81c ENTER SQLDriverConnectW
> HDBC 00581690
> HWND 00000000
> WCHAR * 0x1F7C48DC [ -3] "******\ 0"
> SWORD -3
> WCHAR * 0x1F7C48DC
> SWORD 8
> SWORD * 0x00000000
> UWORD 0 <SQL_DRIVER_NOPROMPT>
> sqlagent 31c-81c EXIT SQLDriverConnectW with return code 1
> (SQL_SUCCESS_WITH_INFO)
> HDBC 00581690
> HWND 00000000
> WCHAR * 0x1F7C48DC [ -3] "******\ 0"
> SWORD -3
> WCHAR * 0x1F7C48DC
> SWORD 8
> SWORD * 0x00000000
> UWORD 0 <SQL_DRIVER_NOPROMPT>
> DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Serv
er]Changed
> database context to 'msdb'. (5701)
> DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Serv
er]Changed
> language setting to us_english. (5703)
>
> There's more, but I'll spare you.
> Ryan
>

C#: Running DDL to Create Functions.

Greetings All, I was hoping that someone out there has seen a problem
similar to the one I am seeing. I have one file with several "create
function" statements in it. When I try to run it through C# I get
errors because it does not like the "GO" statements.

Second, when I break them up into individual files and then call them
from C# and try to execute them I get funky results if there is a
variable in an update statement. C# gets back to me that the variable
must be declared?

TFDBelow is a C# example that runs a SQL script file and parses for 'GO' batch
delimiters. It uses OleDb but can be modified to use SqlClient, if needed.

I'm not sure what the issue is in your second question. Is the variable
declared in the script? If not, it must be passed as a command parameter.

static void main()
{
string connectionString;

connectionString = "Provider=SQLOLEDB;" +
";Data Source=MyServer" +
";Initial Catalog=MyDatabase" +
";Integrated Security=SSPI;";
System.Data.OleDb.OleDbConnection oleDbConnection =
new System.Data.OleDb.OleDbConnection(connectionString );
oleDbConnection.Open();

executeSqlScriptFile("C:\\MySqlScripts\\SqlScriptFile.sql",
oleDbConnection);
}

static void executeSqlScriptFile(string sqlScriptFileName,
System.Data.OleDb.OleDbConnection oleDbConnection)
{
System.IO.StringWriter sqlBatchWriter;
System.IO.StreamReader sqlScriptFile =
new System.IO.StreamReader(sqlScriptFileName);
sqlBatchWriter = new System.IO.StringWriter();
string sqlScriptLine;

while(sqlScriptFile.Peek() > -1)
{
sqlScriptLine = sqlScriptFile.ReadLine();
if (string.Compare(sqlScriptLine.Trim(), "GO", true) == 0)
{
executeSqlScriptBatch(sqlBatchWriter.ToString(),
oleDbConnection);
sqlBatchWriter.Close();
sqlBatchWriter = new System.IO.StringWriter();
}
else
{
sqlBatchWriter.WriteLine(sqlScriptLine);
}
}

executeSqlScriptBatch(sqlBatchWriter.ToString(),
oleDbConnection);

sqlBatchWriter.Close();
}

static void executeSqlScriptBatch(string sqlBatch,
System.Data.OleDb.OleDbConnection oleDbConnection)
{
if (string.Compare(sqlBatch.Trim(), "", true) == 0)
return;
System.Data.OleDb.OleDbCommand oleDbCommand =
new System.Data.OleDb.OleDbCommand(sqlBatch,
oleDbConnection);
oleDbCommand.ExecuteNonQuery();
}

--
Hope this helps.

Dan Guzman
SQL Server MVP

"LineVoltageHalogen" <tropicalfruitdrops@.yahoo.com> wrote in message
news:1111452007.234077.258920@.z14g2000cwz.googlegr oups.com...
> Greetings All, I was hoping that someone out there has seen a problem
> similar to the one I am seeing. I have one file with several "create
> function" statements in it. When I try to run it through C# I get
> errors because it does not like the "GO" statements.
> Second, when I break them up into individual files and then call them
> from C# and try to execute them I get funky results if there is a
> variable in an update statement. C# gets back to me that the variable
> must be declared?
> TFD

C# code for saving data from excel to mssql database

Hello everyone,

I am trying to find some code or documentation that I can use to create a web page that will save data from an excel file to a mssql databaseAfter you save the Excel file, open it and read records from it using this article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934

And then use normal ADO.NET procedures to update the SQL Server database.|||Thanks a lot I will take a look at this article now|||Thanks for the tip all worked well on my local machine but when I uploaded it to an external server and tested I keep getting this error:

System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data. at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider() at System.Data.OleDb.OleDbConnection.Open() at summitPortal.controls.bulk.insertdata(String sSheetPath) at summitPortal.controls.bulk.ImageButton1_Click(Object sender, ImageClickEventArgs e) at System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) at System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain()

The file is definately not in use because I even restarted the computer and tried again.
I also checked the permissions and they seem to be ok.

Any ideas anyone?|||I'm not so sure about this but did you check yourExcel file is read-only or not? sometimes I get some error when I open read-only files. anyway, I will try what you're doin right now.|||Check out this Microsoft troubleshooting article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q306269

by vb.net create exe File to update sql server

is there any chance to create an exe file to update the sql server database by uisng windows schdule ?

for example this exe file will run to update my database, every night @. 12:00 AM.

this exe should be in vb.net

pllllzzzz help

What is your design for that ? Do you want to execute DDL or DML or just maintainance on the database ? You might check the option of the SQL Server Agent, which does Scheduling for SQL Server. More information would be helpful to help you.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

You can also VBScript from SQL Agent…

By Pass mode

Dear All,
I have a database of around 166gb and somehow I lost the
log file. Now its not enabling me to do any transaction as
no log file is avaialable.Database is attached.Now when I
create the log file thru DBCC command (DBCC rebuild_log),
it says "database must be put in Bypass recovery mode to
rebuild the log". Pls advice what's that?pls elaborate. Is
this right way to create the log or there is some
alternate option also to work with my database.
Immmediate solution will be highly appreciated.
Rgds
TriveniThat DBCC command is *not* a safe command. It might leave the database in an
indeterminate state. I suggest you restore from the latest database and all
subsequent transaction log backups.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Triveni" <anonymous@.discussions.microsoft.com> wrote in message
news:8ee401c4051f$5a7ea1f0$a501280a@.phx.gbl...
> Dear All,
> I have a database of around 166gb and somehow I lost the
> log file. Now its not enabling me to do any transaction as
> no log file is avaialable.Database is attached.Now when I
> create the log file thru DBCC command (DBCC rebuild_log),
> it says "database must be put in Bypass recovery mode to
> rebuild the log". Pls advice what's that?pls elaborate. Is
> this right way to create the log or there is some
> alternate option also to work with my database.
> Immmediate solution will be highly appreciated.
> Rgds
> Triveni|||The worst part is that, we do not have backup of this
database. pls suggest.
Rgds
Triveni

>--Original Message--
>That DBCC command is *not* a safe command. It might leave
the database in an
>indeterminate state. I suggest you restore from the
latest database and all
>subsequent transaction log backups.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Triveni" <anonymous@.discussions.microsoft.com> wrote in
message
>news:8ee401c4051f$5a7ea1f0$a501280a@.phx.gbl...
as
I
rebuild_log),
Is
>
>.
>|||I suggest you open a case with MS Support and have then help you through
this process. They might be able to get back the database and they might
even be able to assist in checking what damage is caused as DBCC REBUILD_LOG
is *not* a safe operation.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Triveni" <anonymous@.discussions.microsoft.com> wrote in message
news:915e01c4053b$8b1958c0$a501280a@.phx.gbl...
> The worst part is that, we do not have backup of this
> database. pls suggest.
> Rgds
> Triveni
>
>
> the database in an
> latest database and all
> message
> as
> I
> rebuild_log),
> Is

business logic in store procedure

Hey guys,
I have to import a csv file to my database and then addsome values to other fields based on these values. At present I use astore procedure that does a bulk insert and then I use an update andfetch inside the sql to perform these updates. I am not sure if this isthe best way. Is this bad, since some amount of busniess logic is inthe store procedure?

If it is, how can I do this better?
Thanks for the answer.

Yes, it's fine to do add values in the stored procedure. And if as you say, all the values that need to be added can be computed from existing values in the csv file (or the corresponding table), we can add computed column(s) to the table. Then no extra update is needed. For example, if we import csv file into a table T(firstName varchar(20), LastName varchar(20)), and we want to add a full name column based on the firstName and lastName, we can add a computed column:

alter table T add fullName as firstName+' '+lastName

Business Intelligence Development Studio Starrtup File

roper startup file for Business Intelligence Development Studio
2005?
My SQLServer 2005 installation went smoothly, incl. Reporting Services.
However, staretu icon for BI Studio refers to DISTRIB.EXE, and I just see a
brief DOS window flashing by when I try to start it.
Thanks for any help.
AlanWhere are you looking for business intelligence? You say startup Icon? It
should be found by going to start, all programs, sql server (doing this from
memory so exact verbiage might be different). The tools have to be selected
during installation. If you go to add/remove programs you should see the
tools separately (it will be the largest one there for sql server, something
like 500 mb). I have installed both VS Beta 2 and also the June CTP and had
no trouble with either one.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:OibRnF4nFHA.3036@.TK2MSFTNGP14.phx.gbl...
> roper startup file for Business Intelligence Development Studio
> 2005?
> My SQLServer 2005 installation went smoothly, incl. Reporting Services.
> However, staretu icon for BI Studio refers to DISTRIB.EXE, and I just see
> a
> brief DOS window flashing by when I try to start it.
> Thanks for any help.
> Alan
>
>

Business Intelligence Development Studio - Missing Feature

I am trying to use the Foreach Loop Container. When I open the container for edit on the Collection page, the Foreach Item and Foreach File enumerators are not listed in the drop down.

I have installed SQL Server 2005 Developer Edition. If there something else I need to install?

I appreciate any help provided

Jeff

Check if you are affected by issue discussed in this KB:
http://support.microsoft.com/default.aspx/kb/913817

Bulkloading external xml file

Can I call an external url which requests a xml file when executing a
BulkLoad operation? I'm getting the error message:
Error opening the datafile. Code:80004005.
There is no problem with my database connection and I am able to access the
url from an internet browser.
Below is my code:
set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=localhost;database=db0681;uid=sa;pwd=as"
objBL.ErrorLogFile = "d:\xml\error.log"
objBL.Execute "AddressSchema.xml",
"http://mypartnerurl.com/addresslookup.pce?postcode=AAA&userId=myUser&passw ord=pwd"
set objBL=Nothing
I do not believe that the execute method implements http resolution - you
will need to resolve and retrieve your data either into a stream or to a
local file and pass the stream or file reference to the execute method.
dlr
"Paulo" <paulo@.msdn.com> wrote in message
news:7FDD076C-0CA9-4FCD-A93B-45AF015D5917@.microsoft.com...
> Can I call an external url which requests a xml file when executing a
> BulkLoad operation? I'm getting the error message:
> Error opening the datafile. Code:80004005.
> There is no problem with my database connection and I am able to access
the
> url from an internet browser.
> Below is my code:
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
> objBL.ConnectionString = "provider=SQLOLEDB.1;data
> source=localhost;database=db0681;uid=sa;pwd=as"
> objBL.ErrorLogFile = "d:\xml\error.log"
> objBL.Execute "AddressSchema.xml",
>
"http://mypartnerurl.com/addresslookup.pce?postcode=AAA&userId=myUser&passw o
rd=pwd"
> set objBL=Nothing
|||I do not think that you can use a URL. It should be a file name that is
reachable from the machine.
Best regards
Michael
"Paulo" <paulo@.msdn.com> wrote in message
news:7FDD076C-0CA9-4FCD-A93B-45AF015D5917@.microsoft.com...
> Can I call an external url which requests a xml file when executing a
> BulkLoad operation? I'm getting the error message:
> Error opening the datafile. Code:80004005.
> There is no problem with my database connection and I am able to access
> the
> url from an internet browser.
> Below is my code:
> set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
> objBL.ConnectionString = "provider=SQLOLEDB.1;data
> source=localhost;database=db0681;uid=sa;pwd=as"
> objBL.ErrorLogFile = "d:\xml\error.log"
> objBL.Execute "AddressSchema.xml",
> "http://mypartnerurl.com/addresslookup.pce?postcode=AAA&userId=myUser&passw ord=pwd"
> set objBL=Nothing
|||Alright, but how can I retrieve it and save it to a local file? Would please
write a VBScript example?
"Dennis Redfield" wrote:

> I do not believe that the execute method implements http resolution - you
> will need to resolve and retrieve your data either into a stream or to a
> local file and pass the stream or file reference to the execute method.
> dlr
> "Paulo" <paulo@.msdn.com> wrote in message
> news:7FDD076C-0CA9-4FCD-A93B-45AF015D5917@.microsoft.com...
> the
> "http://mypartnerurl.com/addresslookup.pce?postcode=AAA&userId=myUser&passw o
> rd=pwd"
>
>
|||I have nothing in script (although I remember seeing a internet sample using
classic ASP script to resolve and convert to stream - look on the msdn site,
there MAY be an example in the BOL for SQLXML ).
Pretty straight forward in C# to do so.
do some research.
dlr
"Paulo" <paulo@.msdn.com> wrote in message
news:01E99E0B-5CCC-4049-B715-405B139E21B4@.microsoft.com...
> Alright, but how can I retrieve it and save it to a local file? Would
please[vbcol=seagreen]
> write a VBScript example?
> "Dennis Redfield" wrote:
you[vbcol=seagreen]
access[vbcol=seagreen]
"http://mypartnerurl.com/addresslookup.pce?postcode=AAA&userId=myUser&passw o[vbcol=seagreen]

bulkload xml file with declared external dtd file

I have a several xml files to import in a sql 2005 xml datatype column with the OPENROWSET BULKLOAD.
The xml files declares an external dtd.
I must use the CONVERT option 2 to avoid an internal dtd error.
When I try to read the column, sql throws a Lists of BinaryXml value tokens not supported error because xml use special entity characters was declared in dtd.
Is there a solution for resolve using the dtd like a schema?

Paolo

Paolo,

Regarding DTD processing for imposing constraints as XSD does, no, there is no option in SQL Server 2005 to cause this validation. The extent of our DTD processing is to verify that internal subsets are syntactically correct, perform entity expansion, and apply attribute default values. We do not perform validation of the xml document per the DTD constraints.

For the error you are receiving, I am unable to reproduce this with a simple test. Please let me know if the following example works for you. If so, perhaps you can elaborate on what is different with what you are attempting.

(1)Create file on disk (c:\temp\dtd1.xml) with contents like:

<!DOCTYPE DOC SYSTEM "C:\MyExternalDTD.xml" [<!ATTLIST elem1 attr1 CDATA "defVal1">]><elem1><childelem1/></elem1>

(2)Execute following tsql to load and query out the document:

CREATE TABLE t1 (xmlCol XML)

go

INSERT t1

SELECT

CONVERT(XML, BulkColumn, 2)

FROM

OPENROWSET (BULK 'c:\temp\dtd1.xml', SINGLE_BLOB) orset(BulkColumn)

go

SELECT * FROM t1

go

Regards,

Adrian Hains

Bulkload XML Code

I have several XML file's I need bulkload into SQL 2005.
I have all my code done, but now I ran into another issue.
One of the data tags <pdata> actually contains XML tags. When I try to load
the data I get a data mapping error: Data mapping to column 'pdata' was
already found in the data. Make sure that no two schema definitions map to
the same column.
Is there any way to allow XML tags to bulkload?
I'm using a string data type.
Thanks
Charles WIf you send me the schema and the data I can take a look.
Best regards,
Monica Frintu
"Charles W" wrote:

> I have several XML file's I need bulkload into SQL 2005.
> I have all my code done, but now I ran into another issue.
> One of the data tags <pdata> actually contains XML tags. When I try to loa
d
> the data I get a data mapping error: Data mapping to column 'pdata' was
> already found in the data. Make sure that no two schema definitions map to
> the same column.
> Is there any way to allow XML tags to bulkload?
> I'm using a string data type.
>
> Thanks
> Charles W
>
>

BulkLoad question

In order to upload data using SQLXML Bulkload, must the data and schema
reside in a file, or can you store the data to a variable, then use Bulkload
?The schema must be in a file. The data can be in a file or an input stream.
Andrew Conrad
Microsoft Corp

BulkLoad question

In order to upload data using SQLXML Bulkload, must the data and schema
reside in a file, or can you store the data to a variable, then use Bulkload
?
The schema must be in a file. The data can be in a file or an input stream.
Andrew Conrad
Microsoft Corp

BulkLoad and .xsd problem

I would like to load a xml file to a database. For reasons this I use the
BulkLoad
COM object model. The elements of the project are following:
The XML file:
--
<?xml version="1.0" encoding="ISO8859-2" ?>
<export>
<ceg id="0000000147">
<rovat id = "0">
<alrovat id = "1">
<mezo id = "bir">piros</mezo>
<mezo id = "cf">tarka</mezo>
</alrovat>
</rovat>
<rovat id = "2">
<alrovat id = "1">
<mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
</alrovat>
</rovat>
</ceg>
<ceg id="0000000153">
etc.
</ceg>
</export>
The columns of the tblExport table in the DB:
---
ceg_id char(10)
rovat_id varchar(10)
alrovat_id varchar(10)
mezo_id varchar(50)
mezo_text varchar(1000)
The following xsd file has been used:
--
<?xml version="1.0"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ceg" sql:relation="[tblExport]">
<xsd:complexType>
<xsd:choice>
<xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="alrovat" minOccurs="0"
maxOccurs="unbounded">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="mezo" sql:field="mezo_text"
minOccurs="0" maxOccurs="unbounded">
<xsd:complexType>
<xsd:attribute name="id"
sql:field="mezo_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="alrovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="id" sql:field="rovat_id"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:choice>
<xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
I've expected the following result set (and I would like to get same):
ceg_id rovat_id alrovat_id mezo_id mezo
_text
----
0000000147 0 1 bir piros
0000000147 0 1 cf tarka
0000000147 2 1 nev FA-MAG Ipari Kisszovetkezet
0000000153 etc.
But I've got this:
ceg_id rovat_id alrovat_id mezo_id mezo
_text
----
0000000147 Null Null Null Null
0000000153 Null Null Null Null
etc.
Why? I tried to change the xsd file at many places and many times. For
example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
expected a reletionship on 'rovat'. But I don't want to take an extra table.
If I use sql:is-constant annotation an error will be raised saying that
constant element has no attribute. In most cases I don't get error but no
records will be generated.
Has anybody a good suggestion? I would be grateful for any help.
Thanks.
D. AttilaYou have to use xsd:sequence instead of xsd:choice and specify
sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> I would like to load a xml file to a database. For reasons this I use the
> BulkLoad
> COM object model. The elements of the project are following:
> The XML file:
> --
> <?xml version="1.0" encoding="ISO8859-2" ?>
> <export>
> <ceg id="0000000147">
> <rovat id = "0">
> <alrovat id = "1">
> <mezo id = "bir">piros</mezo>
> <mezo id = "cf">tarka</mezo>
> </alrovat>
> </rovat>
> <rovat id = "2">
> <alrovat id = "1">
> <mezo id = "nev">FA-MAG Ipari Kisszovetkezet</mezo>
> </alrovat>
> </rovat>
> </ceg>
> <ceg id="0000000153">
> etc.
> </ceg>
> </export>
> The columns of the tblExport table in the DB:
> ---
> ceg_id char(10)
> rovat_id varchar(10)
> alrovat_id varchar(10)
> mezo_id varchar(50)
> mezo_text varchar(1000)
> The following xsd file has been used:
> --
> <?xml version="1.0"?>
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:element name="ceg" sql:relation="[tblExport]">
> <xsd:complexType>
> <xsd:choice>
> <xsd:element name="rovat" minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="alrovat" minOccurs="0"
> maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element name="mezo"
sql:field="mezo_text"
> minOccurs="0" maxOccurs="unbounded">
> <xsd:complexType>
> <xsd:attribute name="id"
> sql:field="mezo_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="alrovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:sequence>
> <xsd:attribute name="id" sql:field="rovat_id"
> type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:choice>
> <xsd:attribute name="id" sql:field="ceg_id" type="xsd:string" />
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> I've expected the following result set (and I would like to get same):
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 0 1 bir piros
> 0000000147 0 1 cf tarka
> 0000000147 2 1 nev FA-MAG Ipari Kisszovetkezet
> 0000000153 etc.
> But I've got this:
> ceg_id rovat_id alrovat_id mezo_id mezo_text
> ----
> 0000000147 Null Null Null Null
> 0000000153 Null Null Null Null
> etc.
> Why? I tried to change the xsd file at many places and many times. For
> example I changed <xsd:choice> to <xsd:sequence> but the BulkLoad
> expected a reletionship on 'rovat'. But I don't want to take an extra
table.
> If I use sql:is-constant annotation an error will be raised saying that
> constant element has no attribute. In most cases I don't get error but no
> records will be generated.
> Has anybody a good suggestion? I would be grateful for any help.
> Thanks.
> D. Attila
>
>|||Thanks, but I have tried it. I get the following error:
.....constant/fixed element cannot have attributes.....
But I need these attributes.
D.A.
"Bertan ARI [MSFT]" wrote:

> You have to use xsd:sequence instead of xsd:choice and specify
> sql:is-constant="1" on the elements 'rovat' and 'alrovat'.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:B08BA56A-FAD0-4DDE-A0B7-1FA57EA7C27B@.microsoft.com...
> sql:field="mezo_text"
> table.
>
>|||Sorry my mistake. I didn't see the attributes.
Unfortunately, your scenario is currently not supported by Bulkload.
Currently we do not allow attributes on constant elements and there are no
future plans to support it.
You may use XSLT to transform the Xml into a shape Bulkload can support or
you may use OpenXml which doesn't have this limitation.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...
> Thanks, but I have tried it. I get the following error:
> .....constant/fixed element cannot have attributes.....
> But I need these attributes.
> D.A.
> "Bertan ARI [MSFT]" wrote:
>
rights.
the
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
maxOccurs="unbounded">
sql:field="alrovat_id"
/>
> ----
> ----
that
no|||Thanks for your response.
D.A.
"Bertan ARI [MSFT]" wrote:

> Sorry my mistake. I didn't see the attributes.
> Unfortunately, your scenario is currently not supported by Bulkload.
> Currently we do not allow attributes on constant elements and there are no
> future plans to support it.
> You may use XSLT to transform the Xml into a shape Bulkload can support or
> you may use OpenXml which doesn't have this limitation.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
> news:CFB3D497-86E8-4207-97EA-F3EC42D2DEE5@.microsoft.com...
> rights.
> the
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> maxOccurs="unbounded">
> sql:field="alrovat_id"
> />
> that
> no
>
>