ODBC database slows down over time

Howdy,

I currently have an ODBC connection to a SQL server that exists on the other side of the country from me. I'm running queries in Access and I've found that over time, queries run slower and slower. If I create a new database and bring in the same tables, it runs quickly (relatively speaking) again. Why is this happening? Is there something I can do besides creating new databases all the time?

Thank you in advance.Is the problem that over time the query has more and more records to move through? Is the DB normalized?

ODBC data source + SQL 2000

Hello,
I'm running a SQL 2000 server on a win 2k3 machine.
I'm not familiar with Win 2k3 so I'm having a hard time looking for the
correct information.
I know my SQL server is up and running. I can connect to it from my
workstation through Enterprise Manager (EM) and the query analyzer (QA).
The funny thing is , when I login to the 2k3 machine and open up the
ODBC Data Source Administrator and try to create a system DSN, the 2k3
machine is not listed in the drop down menu.
It reads,
Which SQL Server do you wan tot connect to?
Server: <Dropdown menu>
I did just type the servername, winbox, and the test at the end was
sucessful. The reason I'm asking is because I'm having a hard time
getting my web app server (CF 5.0 on RH 9.0 w/Apache) creating a DSN
connection I can use to the SQL server.
Thanks.So what's the issue?
Linchi

quote:

---

>--Original Message--
>Hello,
>I'm running a SQL 2000 server on a win 2k3 machine.
>I'm not familiar with Win 2k3 so I'm having a hard time

---

looking for the

quote:

---

>correct information.
>I know my SQL server is up and running. I can connect to

---

it from my

quote:

---

>workstation through Enterprise Manager (EM) and the query

---

analyzer (QA).

quote:

---

>The funny thing is , when I login to the 2k3 machine and

---

open up the

quote:

---

>ODBC Data Source Administrator and try to create a system

---

DSN, the 2k3

quote:

---

>machine is not listed in the drop down menu.
>It reads,
>Which SQL Server do you wan tot connect to?
>Server: <Dropdown menu>
>I did just type the servername, winbox, and the test at

---

the end was

quote:

---

>sucessful. The reason I'm asking is because I'm having a

---

hard time

quote:

---

>getting my web app server (CF 5.0 on RH 9.0 w/Apache)

---

creating a DSN

quote:

---

>connection I can use to the SQL server.
>Thanks.
>.
>

---

|||I can't get my app server (MacroMedia CF server 5.0 running on RH 9) to
create an ODBC connection to SQL Server 2000 running on a 2k3 server.
I feel like I am taking crazy pills. I have done this config numerous
times with the same system architect. The only thing that is different
this time is that the SQL server is running on a 2k3 Machine.
CF server defaults the connection to 1433. I checked up the TCPIP
properties to make sure I did not close that port. It looks good.
Any ideas?
Linchi Shea wrote:

quote:

---

> So what's the issue?
> Linchi
>

---

sql

ODBC data source + SQL 2000

Hello,
I'm running a SQL 2000 server on a win 2k3 machine.
I'm not familiar with Win 2k3 so I'm having a hard time looking for the
correct information.
I know my SQL server is up and running. I can connect to it from my
workstation through Enterprise Manager (EM) and the query analyzer (QA).
The funny thing is , when I login to the 2k3 machine and open up the
ODBC Data Source Administrator and try to create a system DSN, the 2k3
machine is not listed in the drop down menu.
It reads,
Which SQL Server do you wan tot connect to?
Server: <Dropdown menu>
I did just type the servername, winbox, and the test at the end was
sucessful. The reason I'm asking is because I'm having a hard time
getting my web app server (CF 5.0 on RH 9.0 w/Apache) creating a DSN
connection I can use to the SQL server.
Thanks.So what's the issue?
Linchi
>--Original Message--
>Hello,
>I'm running a SQL 2000 server on a win 2k3 machine.
>I'm not familiar with Win 2k3 so I'm having a hard time
looking for the
>correct information.
>I know my SQL server is up and running. I can connect to
it from my
>workstation through Enterprise Manager (EM) and the query
analyzer (QA).
>The funny thing is , when I login to the 2k3 machine and
open up the
>ODBC Data Source Administrator and try to create a system
DSN, the 2k3
>machine is not listed in the drop down menu.
>It reads,
>Which SQL Server do you wan tot connect to?
>Server: <Dropdown menu>
>I did just type the servername, winbox, and the test at
the end was
>sucessful. The reason I'm asking is because I'm having a
hard time
>getting my web app server (CF 5.0 on RH 9.0 w/Apache)
creating a DSN
>connection I can use to the SQL server.
>Thanks.
>.
>|||I can't get my app server (MacroMedia CF server 5.0 running on RH 9) to
create an ODBC connection to SQL Server 2000 running on a 2k3 server.
I feel like I am taking crazy pills. I have done this config numerous
times with the same system architect. The only thing that is different
this time is that the SQL server is running on a 2k3 Machine.
CF server defaults the connection to 1433. I checked up the TCPIP
properties to make sure I did not close that port. It looks good.
Any ideas?
Linchi Shea wrote:
> So what's the issue?
> Linchi
>

ODBC Connection.

There is a problem with my ODBC connection. Usually after
about 30 mins, a connection time out occurs and the error
message displayed is as foolows:
Connection failed:
SQLState: 'S1000'
SQL Server Error: 0
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI
context
I'm currently using Microsoft Windows Server 2003 for
Small Business Servers as my back-end server while my
client is using Microsoft Office Access 2003.
This problem does not occur in certain computers,
regardless of the authenticated users.
Normally what i would do when this problem occurs, will
be to log off and re login to windows. However this is
quite time consuming and i was wondering if there is any
way that this problem can be solved.
If you have not seen this troubleshooting page, you'll want to give this a
read:
How to troubleshoot the "Cannot generate SSPI context" error message
http://support.microsoft.com/default...&Product=sql2k
Steve
"Nexus Quest" <anonymous@.discussions.microsoft.com> wrote in message
news:2066301c459c0$68371610$a601280a@.phx.gbl...
> There is a problem with my ODBC connection. Usually after
> about 30 mins, a connection time out occurs and the error
> message displayed is as foolows:
> Connection failed:
> SQLState: 'S1000'
> SQL Server Error: 0
> SQL Server Error: 0
> [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI
> context
> I'm currently using Microsoft Windows Server 2003 for
> Small Business Servers as my back-end server while my
> client is using Microsoft Office Access 2003.
> This problem does not occur in certain computers,
> regardless of the authenticated users.
> Normally what i would do when this problem occurs, will
> be to log off and re login to windows. However this is
> quite time consuming and i was wondering if there is any
> way that this problem can be solved.
|||I am having the same problem with ODBC timing out after 30 minutes. Did you find a way to correct the problem?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

ODBC Connection.

There is a problem with my ODBC connection. Usually after
about 30 mins, a connection time out occurs and the error
message displayed is as foolows:
Connection failed:
SQLState: 'S1000'
SQL Server Error: 0
SQL Server Error: 0
[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI
context
I'm currently using Microsoft Windows Server 2003 for
Small Business Servers as my back-end server while my
client is using Microsoft Office Access 2003.
This problem does not occur in certain computers,
regardless of the authenticated users.
Normally what i would do when this problem occurs, will
be to log off and re login to windows. However this is
quite time consuming and i was wondering if there is any
way that this problem can be solved.If you have not seen this troubleshooting page, you'll want to give this a
read:
How to troubleshoot the "Cannot generate SSPI context" error message
http://support.microsoft.com/defaul...9&Product=sql2k
Steve
"Nexus Quest" <anonymous@.discussions.microsoft.com> wrote in message
news:2066301c459c0$68371610$a601280a@.phx
.gbl...
> There is a problem with my ODBC connection. Usually after
> about 30 mins, a connection time out occurs and the error
> message displayed is as foolows:
> Connection failed:
> SQLState: 'S1000'
> SQL Server Error: 0
> SQL Server Error: 0
> [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI
> context
> I'm currently using Microsoft Windows Server 2003 for
> Small Business Servers as my back-end server while my
> client is using Microsoft Office Access 2003.
> This problem does not occur in certain computers,
> regardless of the authenticated users.
> Normally what i would do when this problem occurs, will
> be to log off and re login to windows. However this is
> quite time consuming and i was wondering if there is any
> way that this problem can be solved.|||I am having the same problem with ODBC timing out after 30 minutes. Did you
find a way to correct the problem?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.sql

ODBC Connection failure

My application (written in a proprietary old 4GL) is connecting to a SQL 2000 database via ODBC

This works fine nearly all the time, but occasionally, it throws the following error when trying to connect :

Connection Failed

SQLState '01000'

SQL Server Error: 10060

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Connection Open(Connect()).

Connection Failed:

SQLState '08001'

SQL Server Error: 17

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist or access denied.

This error is displayed in a Windows dialog box. When the OK button on this dialog is clicked, it throws up a login/password box. When this is filled in and submitted, the connection is usually made cleanly and the application continues.

The big problem is that the application is an unattended service, and this dialog can go unnoticed for quite a while.

If these dialogs are 'Cancelled' rather than 'OK'd, the 4GL receives the error and arranges to re-try the connection itself.

Is ODBC responsible for popping up the interactive dialog, and, if so, can I do anything to force it to return an error instead ?

Hi Steve,

Interesting situation. The error 10060 means this: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. So you are probably experiencing a timeout in one of the socket-relation ops, most probably connect() (different from the SQL netlib's Connect() mentioned above).

The login popup is implemented by the SQL ODBC driver. You are probably using SQLDriverConnect in ODBC? It has a parameter "DriverCompletion", which could be:

SQL_DRIVER_PROMPT
SQL_DRIVER_COMPLETE
SQL_DRIVER_COMPLETE_REQUIRED
SQL_DRIVER_NOPROMPT

You probably have passed the first one (SQL_DRIVER_PROMPT). In order to make sure the SQLDriverConnect fails without popping up the troublesome dialog, you need to pass the SQL_DRIVER_NOPROMPT. I truly hope you are able to modify the 4GL application?

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks a lot Jivko.

Unfortunately, the only parameter the 4GL allows me to specify is the DSN name.

I'll be on to the vendor's support desk rather forcefully first thing on Monday morning - thanks for the Ammo!

ODB Datareader time columns - updated 26th June

I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.

The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).

When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.

I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.

I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Does anyone know how to resolve this issue?

Regards

JLF

Try to put the Data Conversion transform after your source.

|||

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

|||

Mark Durley wrote:

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

Mark

Thanks for youi response, I have tried to map the DT_I8 to a 'bigint' but to no avail.

This is a sample of the output from the SSIS conversion:

id Converted / Real Time

486539355 342000000000 09:30

486539406 342000000000 09:30

469762220 342000000000 11:10

335544346 342000000000 13:20

452984873 342000000000 13:40

436207662 342000000000 14:00

486539294 342000000000 14:45

486539325 342000000000 15:30

268435517 342000000000 15:40

285212826 342000000000 16:00

469762134 342000000000 16:00

As you can see there is no correlation between the Real Time and the Converted Time.

Does anyone elese have any ideas how to get around this, otherwise it's back to good old DTS?

Regards

|||

So the value in the DT_I8 is always 342000000000, regardless of what the input was?

If you put a data viewer on the path from the data reader src, what values are you seeing in the DT_I8 columns there?

|||

Mark

Sorry for the misleading example, the values do vary as per this output:

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50
268435471 0 10:00
369098817 0 10:00
16777233 360000000000 10:00

I will try to put in a data view and let you know the results.

Cheers

John

|||

Mark

Data viewer gives the same results as per my last post.

There seems to be no correlation between the values in the data reader and the 'real' values.

John

|||

The data viewer will show exactly the same thing.

I took the first value there (354000000000) and applied the following calculation to it

354000000000 * 100 / 1000000000 / 60 / 60 = 9.8333333

Multiplying by 100 gives you the number of nanoseconds in the timespan

Dividing by 1000000000 gives you the number of seconds in the timespan

Dividing by 60 gives you the number of minutes in the timespan

Dividing by 60 again gives you the number of hours in the timespan = 9.8333333

You will notice that 9.833333 hours = 9h50m (or 9:50 in your notation)

So what Mark says is true. You need to do a calculation in order to derive the value. I have given you the basis of what you need to do. Note that you will need the modulus operator ('%') at some point.

An interesting little problem but its not that difficult!

-Jamie

|||

Jamie

Thanks for the input, but if you look at the first 5 results you can see there are 5 unique values ALL referring to the same time (9:50).

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50

I don't think I can get around this via a calculation, it looks as if I need to retain my old 2000 DTS package just to handle time fields.

Regards

John

|||

Has anyone any other ideas on how we can resolve this issue?

It would be a shame to have to revert back to our old DTS as SSIS is such a better product.

John

|||

Reposted to put back on the forum list.

Any ideas anyone?

ODB Datareader time columns - help needed

I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.

The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).

When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.

I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.

I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Does anyone know how to resolve this issue?

Regards

JLF

Try to put the Data Conversion transform after your source.

|||

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

|||

Mark Durley wrote:

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

Mark

Thanks for youi response, I have tried to map the DT_I8 to a 'bigint' but to no avail.

This is a sample of the output from the SSIS conversion:

id Converted / Real Time

486539355 342000000000 09:30

486539406 342000000000 09:30

469762220 342000000000 11:10

335544346 342000000000 13:20

452984873 342000000000 13:40

436207662 342000000000 14:00

486539294 342000000000 14:45

486539325 342000000000 15:30

268435517 342000000000 15:40

285212826 342000000000 16:00

469762134 342000000000 16:00

As you can see there is no correlation between the Real Time and the Converted Time.

Does anyone elese have any ideas how to get around this, otherwise it's back to good old DTS?

Regards

|||

So the value in the DT_I8 is always 342000000000, regardless of what the input was?

If you put a data viewer on the path from the data reader src, what values are you seeing in the DT_I8 columns there?

|||

Mark

Sorry for the misleading example, the values do vary as per this output:

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50
268435471 0 10:00
369098817 0 10:00
16777233 360000000000 10:00

I will try to put in a data view and let you know the results.

Cheers

John

|||

Mark

Data viewer gives the same results as per my last post.

There seems to be no correlation between the values in the data reader and the 'real' values.

John

|||

The data viewer will show exactly the same thing.

I took the first value there (354000000000) and applied the following calculation to it

354000000000 * 100 / 1000000000 / 60 / 60 = 9.8333333

Multiplying by 100 gives you the number of nanoseconds in the timespan

Dividing by 1000000000 gives you the number of seconds in the timespan

Dividing by 60 gives you the number of minutes in the timespan

Dividing by 60 again gives you the number of hours in the timespan = 9.8333333

You will notice that 9.833333 hours = 9h50m (or 9:50 in your notation)

So what Mark says is true. You need to do a calculation in order to derive the value. I have given you the basis of what you need to do. Note that you will need the modulus operator ('%') at some point.

An interesting little problem but its not that difficult!

-Jamie

|||

Jamie

Thanks for the input, but if you look at the first 5 results you can see there are 5 unique values ALL referring to the same time (9:50).

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50

I don't think I can get around this via a calculation, it looks as if I need to retain my old 2000 DTS package just to handle time fields.

Regards

John

|||

Has anyone any other ideas on how we can resolve this issue?

It would be a shame to have to revert back to our old DTS as SSIS is such a better product.

John

|||

Reposted to put back on the forum list.

Any ideas anyone?

ODB Datareader time columns

I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.

The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).

When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.

I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.

I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:

Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.

Does anyone know how to resolve this issue?

Regards

JLF

Try to put the Data Conversion transform after your source.

|||

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

|||

Mark Durley wrote:

You will not be able to change the data type of the output column on the Datareader Source, you will need a data conversion column as Bob suggested.

However, if you are seeing the time columns as DT_I8, this is likely because the Datareader Source maps the CLR type System.TimeSpan to DT_I8. The value in the column will be the number of 100 nanosecond intervals represented by the time value (same as the System.TimeSpan.Ticks property). You can do the math with a Derived Column transform expression to convert that back to a time string, or use a script component to ask the CLR to do it for it.

Mark

Thanks for youi response, I have tried to map the DT_I8 to a 'bigint' but to no avail.

This is a sample of the output from the SSIS conversion:

id Converted / Real Time

486539355 342000000000 09:30

486539406 342000000000 09:30

469762220 342000000000 11:10

335544346 342000000000 13:20

452984873 342000000000 13:40

436207662 342000000000 14:00

486539294 342000000000 14:45

486539325 342000000000 15:30

268435517 342000000000 15:40

285212826 342000000000 16:00

469762134 342000000000 16:00

As you can see there is no correlation between the Real Time and the Converted Time.

Does anyone elese have any ideas how to get around this, otherwise it's back to good old DTS?

Regards

|||

So the value in the DT_I8 is always 342000000000, regardless of what the input was?

If you put a data viewer on the path from the data reader src, what values are you seeing in the DT_I8 columns there?

|||

Mark

Sorry for the misleading example, the values do vary as per this output:

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50
268435471 0 10:00
369098817 0 10:00
16777233 360000000000 10:00

I will try to put in a data view and let you know the results.

Cheers

John

|||

Mark

Data viewer gives the same results as per my last post.

There seems to be no correlation between the values in the data reader and the 'real' values.

John

|||

The data viewer will show exactly the same thing.

I took the first value there (354000000000) and applied the following calculation to it

354000000000 * 100 / 1000000000 / 60 / 60 = 9.8333333

Multiplying by 100 gives you the number of nanoseconds in the timespan

Dividing by 1000000000 gives you the number of seconds in the timespan

Dividing by 60 gives you the number of minutes in the timespan

Dividing by 60 again gives you the number of hours in the timespan = 9.8333333

You will notice that 9.833333 hours = 9h50m (or 9:50 in your notation)

So what Mark says is true. You need to do a calculation in order to derive the value. I have given you the basis of what you need to do. Note that you will need the modulus operator ('%') at some point.

An interesting little problem but its not that difficult!

-Jamie

|||

Jamie

Thanks for the input, but if you look at the first 5 results you can see there are 5 unique values ALL referring to the same time (9:50).

452984895 354000000000 09:50
469762147 414000000000 09:50
469762246 426000000000 09:50
402653273 513000000000 09:50
452984915 522000000000 09:50

I don't think I can get around this via a calculation, it looks as if I need to retain my old 2000 DTS package just to handle time fields.

Regards

John

|||

Has anyone any other ideas on how we can resolve this issue?

It would be a shame to have to revert back to our old DTS as SSIS is such a better product.

John

|||

Reposted to put back on the forum list.

Any ideas anyone?

Occasional Very Slow Performance.. with SQL Server 2000

Hi I am using JDB with SQL Server to call Stored Procedures..
The problem is that it is working fine for some time.. But after a
certain period the time taken to execute rises to around 15 to 50 secs
from 1 sec .After restart it again starts working fine...
can anyone tell me the reason for this behaviortry to update the statistics of your tables or defrag your indexes
"Yogi" <sendpost@.gmail.com> wrote in message
news:1148306840.530834.91440@.j33g2000cwa.googlegroups.com...
> Hi I am using JDB with SQL Server to call Stored Procedures..
> The problem is that it is working fine for some time.. But after a
> certain period the time taken to execute rises to around 15 to 50 secs
> from 1 sec .After restart it again starts working fine...
> can anyone tell me the reason for this behavior
>|||Two things to check for:
See if you get different execution plans
See if you have any blocking
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogi" <sendpost@.gmail.com> wrote in message
news:1148306840.530834.91440@.j33g2000cwa.googlegroups.com...
> Hi I am using JDB with SQL Server to call Stored Procedures..
> The problem is that it is working fine for some time.. But after a
> certain period the time taken to execute rises to around 15 to 50 secs
> from 1 sec .After restart it again starts working fine...
> can anyone tell me the reason for this behavior
>

Occasional Very Slow Performance.. with SQL Server 2000

Hi I am using JDB with SQL Server to call Stored Procedures..
The problem is that it is working fine for some time.. But after a
certain period the time taken to execute rises to around 15 to 50 secs
from 1 sec .After restart it again starts working fine...
can anyone tell me the reason for this behaviortry to update the statistics of your tables or defrag your indexes
"Yogi" <sendpost@.gmail.com> wrote in message
news:1148306840.530834.91440@.j33g2000cwa.googlegroups.com...
> Hi I am using JDB with SQL Server to call Stored Procedures..
> The problem is that it is working fine for some time.. But after a
> certain period the time taken to execute rises to around 15 to 50 secs
> from 1 sec .After restart it again starts working fine...
> can anyone tell me the reason for this behavior
>|||Two things to check for:
See if you get different execution plans
See if you have any blocking
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Yogi" <sendpost@.gmail.com> wrote in message
news:1148306840.530834.91440@.j33g2000cwa.googlegroups.com...
> Hi I am using JDB with SQL Server to call Stored Procedures..
> The problem is that it is working fine for some time.. But after a
> certain period the time taken to execute rises to around 15 to 50 secs
> from 1 sec .After restart it again starts working fine...
> can anyone tell me the reason for this behavior
>

Occasional Error When Executing CLR Stored Procedure

This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.

Below is the section of code that fails:

<code>

Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader

Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()

_SQLCommandRequest.CommandText = _spReportParms

_SQLCommandRequest.CommandType = CommandType.StoredProcedure

' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader

' make sure that we have something first.
If Not drReportParameters Is Nothing Then

' find out if we have any rows returned.
If drReportParameters.HasRows Then

' read each of the rows looking for the respective value.
While drReportParameters.Read

' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then

' set the parameters for each of the following internal variables.
Select Case drReportParameters.Item("Code_ID").ToString
Case Is = "PDFP Path"
_FileLocationPDFP = drReportParameters.Item("Display_Text").ToString
Case Is = "PDF Temp Path"
_FileLocationPDFTemp = drReportParameters.Item("Display_Text").ToString
Case Is = "LogFile Path"
'''_SqlPipe.Send("Path from sys codes: " & drReportParameters.Item("Display_Text").ToString)
_FileLocationLogFile = Path.Combine(drReportParameters.Item("Display_Text").ToString, "SRSReportLog_" & Now.ToString("HHmmss") & ".txt")
Case Is = "LogFile Flag"
_swLogFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "Delete PDF"
_DeletePDFFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "PrintTool"
_PrintTool = drReportParameters.Item("Display_Text").ToString
Case Is = "BPP Path"
_BppPath = drReportParameters.Item("CharVar1").ToString
Case Is = "SendToPrinter"
_SendToPrinterFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Else
' ignore it.
End Select

End If

End While

End If

End If

Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)

Finally

' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If

End Try

End Sub

</code>

Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?

_SQLCommandRequest is a shared (that is, static) variable, right? You are seeing this error because your proceduce is being executed on multiple threads at the same time, and multiple threads are not allowed to use the same SqlConnection at the same time. (The error message is fixed in the next version of SQL Server to be more clear what the problem is).

It looks like you frequently use static variables within your SP, so even if you fix this problem by using a local, non-shared SqlCommand object, you will run into other problems due to multiple threads accessing the same shared state.

Steven
|||

Understood. Thanks for the explanation.

So all I have to do is make everything local, non-shared then right?

Except for the main sub procedure declaration below which has to remain shared right?:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Run_SRS_Report(ByVal ip_URL As String _

Am I on the right track?

|||

Yes, everything should be a local or class instance field, unless it is a constant, readonly value. Ideally, you should try to deploy the assembly under SAFE or EXTERNAL_ACCESS permission set, which disallows the use of static variables to prevent this type of problem.

|||

Thanks Steven. I've changed everything up so there are no static variables and re-deployed. Hopefully that will fix the problem. If not I'll post again.

Occasional Error When Executing CLR Stored Procedure

This CLR stored procedure executes without fail 99% of the time. However, occasionally it will fail with the following error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "Run_SRS_Report": System.Exception: Attempt to perform native server operation (AllocateNativeRequest) outside of its valid scope.

Below is the section of code that fails:

<code>

Private Shared Sub GetReportParameters()
Dim drReportParameters As SqlDataReader

Try
' set the parameters for the command request.
_SQLCommandRequest = _SQLConnection.CreateCommand()

_SQLCommandRequest.CommandText = _spReportParms

_SQLCommandRequest.CommandType = CommandType.StoredProcedure

' get the information for the data reader request.
drReportParameters = _SQLCommandRequest.ExecuteReader

' make sure that we have something first.
If Not drReportParameters Is Nothing Then

' find out if we have any rows returned.
If drReportParameters.HasRows Then

' read each of the rows looking for the respective value.
While drReportParameters.Read

' make sure that we can get the appropriate code id.
If (Not IsDBNull(drReportParameters.Item("Code_ID"))) AndAlso (Not IsDBNull(drReportParameters.Item("Display_Text"))) Then

' set the parameters for each of the following internal variables.
Select Case drReportParameters.Item("Code_ID").ToString
Case Is = "PDFP Path"
_FileLocationPDFP = drReportParameters.Item("Display_Text").ToString
Case Is = "PDF Temp Path"
_FileLocationPDFTemp = drReportParameters.Item("Display_Text").ToString
Case Is = "LogFile Path"
'''_SqlPipe.Send("Path from sys codes: " & drReportParameters.Item("Display_Text").ToString)
_FileLocationLogFile = Path.Combine(drReportParameters.Item("Display_Text").ToString, "SRSReportLog_" & Now.ToString("HHmmss") & ".txt")
Case Is = "LogFile Flag"
_swLogFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "Delete PDF"
_DeletePDFFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Is = "PrintTool"
_PrintTool = drReportParameters.Item("Display_Text").ToString
Case Is = "BPP Path"
_BppPath = drReportParameters.Item("CharVar1").ToString
Case Is = "SendToPrinter"
_SendToPrinterFlag = IIf(drReportParameters.Item("Display_Text").ToString = "False", False, True)
Case Else
' ignore it.
End Select

End If

End While

End If

End If

Catch ex As Exception
' throw a new exception to trip up the PrintReport() function.
Throw New Exception(ex.Message)

Finally

' make sure that we close out the datareader, regardless.
If Not drReportParameters Is Nothing Then
If Not drReportParameters.IsClosed Then drReportParameters.Close()
End If

End Try

End Sub

</code>

Any ideas why this would fail only occasionally (maybe 1% of the time it's executed)?

_SQLCommandRequest is a shared (that is, static) variable, right? You are seeing this error because your proceduce is being executed on multiple threads at the same time, and multiple threads are not allowed to use the same SqlConnection at the same time. (The error message is fixed in the next version of SQL Server to be more clear what the problem is).

It looks like you frequently use static variables within your SP, so even if you fix this problem by using a local, non-shared SqlCommand object, you will run into other problems due to multiple threads accessing the same shared state.

Steven
|||

Understood. Thanks for the explanation.

So all I have to do is make everything local, non-shared then right?

Except for the main sub procedure declaration below which has to remain shared right?:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub Run_SRS_Report(ByVal ip_URL As String _

Am I on the right track?

|||

Yes, everything should be a local or class instance field, unless it is a constant, readonly value. Ideally, you should try to deploy the assembly under SAFE or EXTERNAL_ACCESS permission set, which disallows the use of static variables to prevent this type of problem.

|||

Thanks Steven. I've changed everything up so there are no static variables and re-deployed. Hopefully that will fix the problem. If not I'll post again.

Obtaining the time from datetime field

From this value: 2002-11-22 00:00:22.000
I want to obtain in string type "00:00:22.000" including miliseconds
I try this but returns: 0:0:22:0
select
Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
from cargaprocesos
Can you help me?
Thanks a lot,Enric, try:
convert(varchar(12), dt, 114)
BG, SQL Server MVP
www.SolidQualityLearning.com
"Enric" wrote:

> From this value: 2002-11-22 00:00:22.000
> I want to obtain in string type "00:00:22.000" including miliseconds
> I try this but returns: 0:0:22:0
> select
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
> from cargaprocesos
> Can you help me?
> Thanks a lot,|||You should go with that:
Select Convert(varchar(12), GETDATE(), 114)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Enric" wrote:

> From this value: 2002-11-22 00:00:22.000
> I want to obtain in string type "00:00:22.000" including miliseconds
> I try this but returns: 0:0:22:0
> select
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(hh,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(2), DATEPART(mi,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ss,DURACIONULT)))) + ':' +
> Ltrim(Rtrim(CONVERT(CHAR(3), DATEPART(ms,duracionult))))
> from cargaprocesos
> Can you help me?
> Thanks a lot,|||Thanks Itzik, I had tried this:
select substring(convert(varchar(50),duracionul
t,113),13,12),
duracionult
from cargaprocesos
But it was failing.
regards,
"Itzik Ben-Gan" wrote:
> Enric, try:
> convert(varchar(12), dt, 114)
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Enric" wrote:
>|||A disign suggestion:
I would create a function for this logic.
Makes the code more readable and it makes the logic reusable aswell.
Hope this is helpfull.
Marcel van Eijkel
( www.vaneijkel.com )

Obtaining the time

dear all,
I'm looking for a better version of this stuff. I want to obtain the time of
a datetime field:
DuracionUlt is a datetime field.
SELECT DuracionUlt,
Ltrim(Rtrim(cast(datepart(hh,duracionult
) as char(2))))
+ ':' + Ltrim(Rtrim(cast(datepart(mi,duracionult
) as char(2)))) + ':' +
Ltrim(Rtrim(cast(datepart(ss,duracionult
) as char(2))))
+ ':' +
Ltrim(Rtrim(cast(datepart(ms,duracionult
) as char(3))))
FROM CARGAPROCESOS WHERE PROCESO like 'ARIES%'
AND datediff(dd,duracionult,getdate()) = 1
This query returns:
2006-03-28 00:00:00.000 = 0:0:0:0
2006-03-28 00:00:42.000 = 0:0:42:0
Any comment/suggestion/thought/advice would be very appreciated,
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)SELECT CONVERT(VARCHAR(30),GETDATE(),108)
select {fn extract(minute from getdate())}
Select {fn dayname( GetDate()) }
select {fn CURRENT_DATE()}
select {fn CURRENT_time()}
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:5FAE6527-EA24-490D-A25E-7093C222DF68@.microsoft.com...
> dear all,
> I'm looking for a better version of this stuff. I want to obtain the time
> of
> a datetime field:
> DuracionUlt is a datetime field.
> SELECT DuracionUlt,
> Ltrim(Rtrim(cast(datepart(hh,duracionult
) as char(2))))
> + ':' + Ltrim(Rtrim(cast(datepart(mi,duracionult
) as char(2)))) + ':' +
> Ltrim(Rtrim(cast(datepart(ss,duracionult
) as char(2))))
> + ':' +
> Ltrim(Rtrim(cast(datepart(ms,duracionult
) as char(3))))
> FROM CARGAPROCESOS WHERE PROCESO like 'ARIES%'
> AND datediff(dd,duracionult,getdate()) = 1
> This query returns:
> 2006-03-28 00:00:00.000 = 0:0:0:0
> 2006-03-28 00:00:42.000 = 0:0:42:0
> Any comment/suggestion/thought/advice would be very appreciated,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||thanks a lot
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:

> SELECT CONVERT(VARCHAR(30),GETDATE(),108)
> select {fn extract(minute from getdate())}
> Select {fn dayname( GetDate()) }
> select {fn CURRENT_DATE()}
> select {fn CURRENT_time()}
>
>
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:5FAE6527-EA24-490D-A25E-7093C222DF68@.microsoft.com...
>
>

Obtaining Audit Information

Hi People

We want one trigger which captures the data as mentioned below.

Table Name

Date and Time

User

type

Mode of modification

TABLE_ABC

03/07/2007 12:00:04

XCS\Raoa

Update

Procedure

TABLE_DEF

03/07/2007 12:00:34

XCS\Raoa

Insert

Class Integration SSIS Package

TABLE_GHI

03/07/2007 12:01:04

XCS\Raoa

Insert

Procedure

TABLE_GHI

03/07/2007 12:01:34

XCS\Raoa

Update

XCS\Raoa (Manual)

I am not sure about how to achieve the last column. I hope u understand what exactly is expected. The idea is that, one should be able to track how a particular table was manipulate; whether it was manipulated using procedure or SSIS package or manually.

Could someone help me achieving this?

Regards

Abhi

Hi Abhi,

I think you are looking for a DDL trigger. Here is my example. We are monitoring the changes on views, procedures, etc. We are using this:

Code Snippet

USE [databasename]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[T_DDL_DDLEventLog](

[EventDate] [datetime] NOT NULL,

[UserName] [sysname] NOT NULL,

[ObjectName] [sysname] NOT NULL,

[CommandText] [varchar](max) NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

USE [databasename]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [DDL_Alter_Audit]

ON DATABASE

FOR ALTER_TABLE, ALTER_PROCEDURE, ALTER_FUNCTION, ALTER_VIEW, ALTER_TRIGGER

AS

DECLARE @.eventData XML

SET @.eventData = eventdata()

INSERT T_DDL_DDLEventLog (EventDate, UserName, ObjectName, CommandText)

SELECT

GETDATE() AS EventDate,

@.eventData.value('data(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME')

AS UserName,

@.eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

AS ObjectName,

@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)') AS CommandText

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [DDL_Alter_Audit] ON DATABASE

Also, you can find more informationabout DDL triggers in the BOL.

I hope it helps.

Regards,

Janos

|||

Thanks for replying Janos.

What does following statement return?

@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)') AS CommandText

Anyways, I m looking for DML triggers. Whenever someone inserts/updates the table, I wanna know how that table was manipulated..was it manipulated using SSIS package or stored procedure or manually by some user. Is it possible to capture this information

|||

Hi,

CommandText returns the T-SQL script ran against the object. Eg.: In got a table called Table1. when I'm going to alter it, the Command text will contain the altering sql script, like ALTER TABLE Table1 .....

LoginName will return the credentila used to make the alter on the table and the ObjectName return Table1, in this case.

You can make this DDL trigger for all database events with DDL_DATABASE_LEVEL_EVENTS event, but this still monitors the objects, not monitoring your DML actions. You should write DML triggers for all your tables required audit.

Regards,

Janos

|||

Thanks for you reply Janos.. Cheers!!!

Obtaining Audit Information

Hi People

We want one trigger which captures the data as mentioned below.

Table Name

Date and Time

User

type

Mode of modification

TABLE_ABC

03/07/2007 12:00:04

XCS\Raoa

Update

Procedure

TABLE_DEF

03/07/2007 12:00:34

XCS\Raoa

Insert

Class Integration SSIS Package

TABLE_GHI

03/07/2007 12:01:04

XCS\Raoa

Insert

Procedure

TABLE_GHI

03/07/2007 12:01:34

XCS\Raoa

Update

XCS\Raoa (Manual)

I am not sure about how to achieve the last column. I hope u understand what exactly is expected. The idea is that, one should be able to track how a particular table was manipulate; whether it was manipulated using procedure or SSIS package or manually.

Could someone help me achieving this?

Regards

Abhi

Hi Abhi,

I think you are looking for a DDL trigger. Here is my example. We are monitoring the changes on views, procedures, etc. We are using this:

Code Snippet

USE [databasename]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[T_DDL_DDLEventLog](

[EventDate] [datetime] NOT NULL,

[UserName] [sysname] NOT NULL,

[ObjectName] [sysname] NOT NULL,

[CommandText] [varchar](max) NOT NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

USE [databasename]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [DDL_Alter_Audit]

ON DATABASE

FOR ALTER_TABLE, ALTER_PROCEDURE, ALTER_FUNCTION, ALTER_VIEW, ALTER_TRIGGER

AS

DECLARE @.eventData XML

SET @.eventData = eventdata()

INSERT T_DDL_DDLEventLog (EventDate, UserName, ObjectName, CommandText)

SELECT

GETDATE() AS EventDate,

@.eventData.value('data(/EVENT_INSTANCE/LoginName)[1]', 'SYSNAME')

AS UserName,

@.eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME')

AS ObjectName,

@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)') AS CommandText

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER [DDL_Alter_Audit] ON DATABASE

Also, you can find more informationabout DDL triggers in the BOL.

I hope it helps.

Regards,

Janos

|||

Thanks for replying Janos.

What does following statement return?

@.eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'VARCHAR(MAX)') AS CommandText

Anyways, I m looking for DML triggers. Whenever someone inserts/updates the table, I wanna know how that table was manipulated..was it manipulated using SSIS package or stored procedure or manually by some user. Is it possible to capture this information

|||

Hi,

CommandText returns the T-SQL script ran against the object. Eg.: In got a table called Table1. when I'm going to alter it, the Command text will contain the altering sql script, like ALTER TABLE Table1 .....

LoginName will return the credentila used to make the alter on the table and the ObjectName return Table1, in this case.

You can make this DDL trigger for all database events with DDL_DATABASE_LEVEL_EVENTS event, but this still monitors the objects, not monitoring your DML actions. You should write DML triggers for all your tables required audit.

Regards,

Janos

|||

Thanks for you reply Janos.. Cheers!!!

Obtaining a report with alters of a table

Thank in advance!
I need to know how Can I obtanin a report or log of alters and deletes in
some tables in a time period?Hi
Use sql profiler
Under the Filters tab
set Object ID equals to the id of the table you want to audit
set text data like 'alter%, 'delete%
Hope this help
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Nacho" <Nacho@.discussions.microsoft.com> wrote in message
news:93E9178F-6DC9-4C42-9309-760123EB7F78@.microsoft.com...
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?|||Is it for Audit trail? Or you just want to have to monitor it onces in a
while to check what is happening in the system?
If for audit, you can create a new table and populate it with the
information from the table affect with an After Update, Delete trigger on
table in question. This way you can history going far back as possible. As
in new table you can track time the delete/update happened and by whom.
If it's just onces in a while thing then VT's suggestion works ;-).
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Nacho" wrote:

> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?

Obtaining a report with alters of a table

Thank in advance!
I need to know how Can I obtanin a report or log of alters and deletes in
some tables in a time period?
Is it for Audit trail? Or you just want to have to monitor it onces in a
while to check what is happening in the system?
If for audit, you can create a new table and populate it with the
information from the table affect with an After Update, Delete trigger on
table in question. This way you can history going far back as possible. As
in new table you can track time the delete/update happened and by whom.
If it's just onces in a while thing then VT's suggestion works ;-).
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Nacho" wrote:

> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?

Obtaining a report with alters of a table

Thank in advance!
I need to know how Can I obtanin a report or log of alters and deletes in
some tables in a time period?Hi
Use sql profiler
Under the Filters tab
set Object ID equals to the id of the table you want to audit
set text data like 'alter%, 'delete%
Hope this help
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Nacho" <Nacho@.discussions.microsoft.com> wrote in message
news:93E9178F-6DC9-4C42-9309-760123EB7F78@.microsoft.com...
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?|||Is it for Audit trail? Or you just want to have to monitor it onces in a
while to check what is happening in the system?
If for audit, you can create a new table and populate it with the
information from the table affect with an After Update, Delete trigger on
table in question. This way you can history going far back as possible. As
in new table you can track time the delete/update happened and by whom.
If it's just onces in a while thing then VT's suggestion works ;-).
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Nacho" wrote:
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?