Architecture

Hello,
I want to kow if the following architecture is good :
Disque 1&2 ( Raid 1)
c: OS
d: sqlserver + system tables + log files
Disque 3&4&5 (Raid 5)
e: dataIf you don't plan on having any volume it would be fine. Otherwise, it would suck. A 3 disk RAID array is the slowest RAID 5 configuration possible. In addition, if you want high performance you should have the tempdb and/or the log files on seperate disks.

If you only have five disks though, this should work.

Architectural question

Hello all, I have a general architecture question.

We have a system that essentially does multiple processes/operations
against a single table.

The objective is to increase performance (i.e. amount of throughput)
and to reduce latency. I can sacrifice accuracy/consistency if
necessary. Batches of about 100-1000 records at a time hit the table
at a time, are read and sent to another app for processing, are
retrieved and the results are updated to the same table, which is then
read again by yet another process.

In essence, whenever work is done against the information in that
table, the working process will update a status column in the table.
Unfortunately, this leads to a lot of contention as multiple procs are
attempting to update data sets and stepping on each other. The system
generated a lot of deadlocks until various forced nolock, rowlock, and
deadlock hints were added to the code. Now, of course, I'm seeing a
lot of server resources being burned up by locking efforts, and
duration is increased due to blocking on occasion, not to mention
various headaches and timeouts due to using these locking hints.

To fix, this, I have split up the central table vertically and removed
the need for various processes to update the table, now the system does
more reads and more inserts, but less updates. In essence, it moves
the key from table to table, and whether or not it is in a table
determines its status (and thus what next needs to be done)

So, First question: I believe that inserts take less time than updates,
but I don't know by how much. Can anyone point me towards something
that talks to this? I know, for example, that locks become much less
of a liability, but I'm a little concerned that the writes will cost
more than I save from removing the locking.

Second, It became clear that in moving to this kind of architecture it
will be necessary to delete records from one of the tables in realtime.
I'm shooting for a capacity of 100 million records/day, and one of
the tables needs to be short when comparing to the other table to get
it's status. So, given that the table isn't ever going to be more
than 8 pages long, will it being continually inserted into and then
deleted from matter? Can I expect to see lock contention in this
scenario (i.e, multiple sprocs inserting into, with another sproc
reading from then deleting from the table)?Hi,
I am not sure if you vertically split the tables the original problem
will go away. I think when these types of issues arise you might
consider partitioning the database (which is horizontal). That might
increase performance etc and solve locking problems too.

Amaxen1@.gmail.com wrote:

Quote:

Originally Posted by

Hello all, I have a general architecture question.
>
We have a system that essentially does multiple processes/operations
against a single table.
>
The objective is to increase performance (i.e. amount of throughput)
and to reduce latency. I can sacrifice accuracy/consistency if
necessary. Batches of about 100-1000 records at a time hit the table
at a time, are read and sent to another app for processing, are
retrieved and the results are updated to the same table, which is then
read again by yet another process.
>
>
In essence, whenever work is done against the information in that
table, the working process will update a status column in the table.
Unfortunately, this leads to a lot of contention as multiple procs are
attempting to update data sets and stepping on each other. The system
generated a lot of deadlocks until various forced nolock, rowlock, and
deadlock hints were added to the code. Now, of course, I'm seeing a
lot of server resources being burned up by locking efforts, and
duration is increased due to blocking on occasion, not to mention
various headaches and timeouts due to using these locking hints.
>
>
To fix, this, I have split up the central table vertically and removed
the need for various processes to update the table, now the system does
more reads and more inserts, but less updates. In essence, it moves
the key from table to table, and whether or not it is in a table
determines its status (and thus what next needs to be done)
>
So, First question: I believe that inserts take less time than updates,
but I don't know by how much. Can anyone point me towards something
that talks to this? I know, for example, that locks become much less
of a liability, but I'm a little concerned that the writes will cost
more than I save from removing the locking.
>
Second, It became clear that in moving to this kind of architecture it
will be necessary to delete records from one of the tables in realtime.
I'm shooting for a capacity of 100 million records/day, and one of
the tables needs to be short when comparing to the other table to get
it's status. So, given that the table isn't ever going to be more
than 8 pages long, will it being continually inserted into and then
deleted from matter? Can I expect to see lock contention in this
scenario (i.e, multiple sprocs inserting into, with another sproc
reading from then deleting from the table)?

sql

Appropriate Use of End Conversation

I have a system that will post a message to a queue, but does not need to wait for a response - just needs to make sure the message arrived properly in the queue, not that is was processed at the receiving end. A second service will poll the queue to retrieve outstanding messages and will then move the message to an outside system. The movement of the message to the outside system will be wrapped in a transaction and if the process is successful, then the transaction will be commited otherwise it will be rolled back.

1) is it appropriate for the service that posts the message to send an END CONVERSATION ? This way the sending service will not be waiting for a response.

2) in the data movement phase, is it appropriate to issue and END CONVERSATION when commiting and not issue when ROLLBACK occurs. Or should ROLLBACK occur with a following END CONVERSATION with error message?

Can you define 'need to make sure the message arrived properly'?

In general a Service Broker application is guaranteed that the message send was properly delivered when the SEND statement was commited. That's the whole purpose of the broker, to take this responsibily out of the app programmer hands and offer it as 'plumbing'. A sent message should be considered 'delivered'. However, the application should not make assumptions about the timeframe of the said delivery. If the target service is down (i.e. for maintenance), if there is a bad configuration or simply transient errors, the message might stay 'in traffic' for seconds, minutes, maybe days. If the application needs a certain timeframe for delivery, then it should specify it as a conversation lifetime option in the BEGIN DIALOG statement.

And specific to your questions:

1) No. This is the dradded 'fire-and-forget' pattern. See this post here for details: http://blogs.msdn.com/remusrusanu/archive/2006/04/06/570578.aspx
The appropiate sequence of actions is for initiator to send the message and commit, then go on on its merry ways w/o waiting for a response. The target receives the message, process it and then ends the conversation. The EndDialog message is sent from the target to initiator and the initiator ends its side in reponse. The initiator ending of the conversation can be done by an activated procedure, so there's no need for initiator app to be hanging around waiting for a response.

2) None. You should never intentionally rollback receives from a queue. This is guaranteed to trigger poison message detection mechanism, which will deactivate the queue in response. An applications houls always be able to handle any message received. In case of an error, it should end the conversation with error then commit.

BTW, rather than use pooling on the target service, you should consider activation.

HTH,
~ Remus

|||

Both endpoints of a dialog (the initiator as well as the target) must end the dialog. It does not matter which service ends it first. The general rule is that whichever service has finished processing the dialog and is no longer interested in any messages the other side could send, should end the dialog first. On ending the dialog, the service will automatically send an EndDialog message to the other service so that it knows that the dialog has been closed at the remote end. On RECEIVing this message, the service would normally end the dialog on its end.

1) If your initiating service ends the conversation, it will not be able to receive any responses even asynchronously. Even if you do not have application-level responses, the response could be something as simple as ending the dialog at the target. There is no reason why you couldn't SEND your message from the initiating service and return without waiting for responses. You could setup an activated procedure on the initiator to simply receive EndDialog messages and end those dialogs. This would run asychronous to the thread doing the SEND.

2) All Service Broker statement (SEND, END CONVERATION, BEGIN DIALOG, etc) are transactional. That is, if you issue an END CONVERSATION and then rollback the transaction, the effect is as if the END CONVERSATION was never issued at all. In other words, if you want your END CONVERSATION to actually take effect, you must commit the transaction under which it is issued.

Finally, your app should not be relying on rolling back transactions for expected errors. For example, if you RECEIVE messages and then do some transactional work to an outside system which could fail, then rolling back the RECEIVE could have undesirable consequences. When you rollback a receive, the messages received are immediately put back in the queue and become available. If your app then loops around and re-issues a RECEIVE, it will receive the same set of messages. Trying to process the same message(s) may fail again. If this repeats 5 times, we detect a poisoned message and disable the queue. A better approach to handle expected errors is to remove the messages from the queue, saving them to a table and using some sort of retry mechanism (eg> conversation timers) to try again later.

|||

Thanks, I was unaware that the actual SEND message was transactional. That makes sense and helps me put everything into context. I'm still trying to get my mind around the service broker.

One question I do still have, in my case, the receiver will run several processes and then call a mySQL based system. If the mySQL based system is not available, then I would close the conversation with error. I guess I'd then place the message info into a staging table where further processing could be done on it. However, what if the info could not be placed into the staging table? I am confused as to how I could then regain access to this message to insure it gets ultimately fixed and written out to the correct system. Since its already been read successfully and a end conversation has been issued, the message is no longer available, correct?

|||

>> On ending the dialog, the service will automatically send an EndDialog message to the other service so that it knows that the dialog has been closed at the remote end. On RECEIVing this message, the service would normally end the dialog on its end.

Is the ending of the dialog automatic when a service receives a message that the other end of the conversation has ended the dialog or is this something that must explicitly be done after receiving the EndDialog message?

|||The END CONVERSATION must be issued explicitly by both services.

Appointment Finder Query...TSQL Expert Required!

We are designing a booking system and have a list of appointments in a single table with the start and end time of the appointment.

Is there any way to write a query to return the available time gaps, I'm thinking here that we would need to generate a temporary table in the query and return the data from this table that doesn't join with data in the appointment table but I'm guessing and was wondering whether there's an easier way..

The table definition is

CREATE TABLE [dbo].[SALON_Appointment] (
[AppointmentID] [int] IDENTITY (1, 1) NOT NULL ,
[Subject] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Notes] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
[StylistID] [int] NOT NULL ,
[Recurrency] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstStartTime] [datetime] NOT NULL ,
[FirstEndTime] [datetime] NOT NULL ,
[UserID] [int] NOT NULL
) ON [PRIMARY]
GO

Thanks in advance

Hi

You don't say what version of SQL Server you are using and there are a number of pieces of information/constraints missing.

These question include:

What is the length of the working day - does midnight to 07:00 counts as free time? Does the system need to allow for breaks such as lunch time? You could book them as dummy appointments (probably need an appointment type field to allow them to be stripped out under certain circumstances). What is the minimum length for an appointment - is 09:30 to 09:35 a valid gap? What days do you need this for: All Days; Working Days; Days for which appointments exist.|||

I assume that you wana find the gap based on stylistID.

Code Snippet

SELECT A1.StylistID,

DATEDIFF(mi,A1.EndTime, A2.StartTime) AS Gap

FROM Appointment A1 JOIN Appointment A2 ON (A1.StylistID=A2.StylistID)

WHERE (A1.EndTime=

(SELECT MAX(A3.EndTime)

FROM Appointment A3

WHERE (A3.StylistID = A1.StylistID)

AND (A3.EndTime <= A2.StartTime)))

Hope this helps,

Paraclete

|||Hi,

Thanks for the reply.
I think I've worked a solution out which is similar to your method. I create a temporary and fill it with psuedo appointments, I then do a select which uses a sub query with NOT EXISTS to select items from the pseudo table that don't exist in the real appointment table and hence returns a list of available slots.

It looks like a little less code than your method but I've not tested it fully so I'm not sure whether its 100% yet.

Applying Initial Snapshot

Hi,
I have configured my system as a Publisher & Distributor. I have also
added another machine in my network as the Subscriber. I have used
'Transactional Replication' method. So when I am trying to initialize the
Replication, the Subscriber, in Status says that, 'Searching for the Initial
Snapshot' and after that fails. Then I later came to know that, to apply
Transactional Snapshot, initially we need to apply the Snapshot Replication.
But how to do this.
Now I have created both Snapshot and Transactional Replication in my
system. But when I am trying to apply the Snapshot, the subscriber is not
updated and instead in the Status, it says that 'FAILED'. I have given the
full rights to the Replication Folders.
So how can I solve this? I want to update the subscribers.
If you did a normal transactional publication and selected the option Yes
Initialize the Schema and Data a snapshot will be generated and distributed.
What is the error message you are getting?
"Sheshadrinath R" <SheshadrinathR@.discussions.microsoft.com> wrote in
message news:07B0439D-AAF2-445E-8DA5-F2B765C06979@.microsoft.com...
> Hi,
> I have configured my system as a Publisher & Distributor. I have also
> added another machine in my network as the Subscriber. I have used
> 'Transactional Replication' method. So when I am trying to initialize the
> Replication, the Subscriber, in Status says that, 'Searching for the
> Initial
> Snapshot' and after that fails. Then I later came to know that, to apply
> Transactional Snapshot, initially we need to apply the Snapshot
> Replication.
> But how to do this.
> Now I have created both Snapshot and Transactional Replication in my
> system. But when I am trying to apply the Snapshot, the subscriber is not
> updated and instead in the Status, it says that 'FAILED'. I have given the
> full rights to the Replication Folders.
> So how can I solve this? I want to update the subscribers.
>

Applying Full Text on a document whose path is stored in the database

Hi,

Can we create full-text index on a document that resides on a file system and we have got the path to that document in the database.

Thanks and regards
Salil

I don't think you can do this in current versions of SQL, though i have seen it suggested for future versions. Whether it will be in 2008 or not is probably a question for the Katmai forum.

In saying that, I think a possible workaround is to use Index Server to index your documents which can then be queried via a linked server.

Of course, you are able to store the documents in an IMAGE/VARBINARY(MAX) column which can be fulltext indexed.

HTH!

Applying Full Text on a document whose path is stored in the database

Hi,

Can we create full-text index on a document that resides on a file system and we have got the path to that document in the database.

Thanks and regards
Salil

I don't think you can do this in current versions of SQL, though i have seen it suggested for future versions. Whether it will be in 2008 or not is probably a question for the Katmai forum.

In saying that, I think a possible workaround is to use Index Server to index your documents which can then be queried via a linked server.

Of course, you are able to store the documents in an IMAGE/VARBINARY(MAX) column which can be fulltext indexed.

HTH!

Applying business rules to the database

Hi Experts,
My system stores events into a large table. I would like to apply some
rules on that table and possibly take action if the rules were satisfied.
For example if a certain event arrives 2 times in 60 seconds then perform an
action. What is the best way to apply rules to data in the database? If it
cannot be done with the database itself a recommendation of a third party
tool will be great. However please not that I cannot use any GUI. I need
to declare the rules and get the result of the rule into my application
which can be in C++ or C#
Thanks for all the help,
AviHave you looked at streaming databases? Google up BitStream.|||You could use triggers in SQL but if you need to invoke external code
against the database then it's better to put this in a middle-tier
application. In your middle-tier code, poll the table regularly for the
changes based on a date-time stamp column.
David Portas
SQL Server MVP
--

application with critical database

Dear all

I am a pretty new in the development world fresh from uni. I am doing development on a system that has a security database. Access to the data in that database is pretty important. So in case the main server where the database is stored for soem reason fails or gets compromised i need to have a second copy with the most recent data in that database and keep the application up and running. The data i have is stored in a SQL 2005 database. What are the recomended aproaches for acheiving this needed reliability?

Would running the SQL Agent every 2 minutes do the trick? And replicate the database to another server and then have asecondary deployment on that server running as a backup? Or are there any other means?

Any advice is apreciated.

Sincerely

See if below links can be useful.

http://msdn2.microsoft.com/en-us/library/ms151247.aspx

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Application is slow after upgrade to windows server 2003

My SQL-Server application is slow after upgrade of the operating system from
windows server 2000 to 2003. Any suggestions? Thanks.Run performance monitor first
http://sqlservercode.blogspot.com/
"Heini" wrote:

> My SQL-Server application is slow after upgrade of the operating system fr
om
> windows server 2000 to 2003. Any suggestions? Thanks.|||Heini,
How do you define slow? What part of the application is slow? Task
Manager, System Monitor and Profiler can all be used to see what is
happening. Detailed analysis of individual queries can be performed by
using execution plans in Query Analyzer.
HTH
Jerry
"Heini" <Heini@.discussions.microsoft.com> wrote in message
news:F86D1153-55B1-4431-8B25-19211B553E36@.microsoft.com...
> My SQL-Server application is slow after upgrade of the operating system
> from
> windows server 2000 to 2003. Any suggestions? Thanks.

Application is slow after upgrade to windows server 2003

My SQL-Server application is slow after upgrade of the operating system from
windows server 2000 to 2003. Any suggestions? Thanks.
Run performance monitor first
http://sqlservercode.blogspot.com/
"Heini" wrote:

> My SQL-Server application is slow after upgrade of the operating system from
> windows server 2000 to 2003. Any suggestions? Thanks.
|||Heini,
How do you define slow? What part of the application is slow? Task
Manager, System Monitor and Profiler can all be used to see what is
happening. Detailed analysis of individual queries can be performed by
using execution plans in Query Analyzer.
HTH
Jerry
"Heini" <Heini@.discussions.microsoft.com> wrote in message
news:F86D1153-55B1-4431-8B25-19211B553E36@.microsoft.com...
> My SQL-Server application is slow after upgrade of the operating system
> from
> windows server 2000 to 2003. Any suggestions? Thanks.

Application is slow after upgrade to windows server 2003

My SQL-Server application is slow after upgrade of the operating system from
windows server 2000 to 2003. Any suggestions? Thanks.Run performance monitor first
http://sqlservercode.blogspot.com/
"Heini" wrote:
> My SQL-Server application is slow after upgrade of the operating system from
> windows server 2000 to 2003. Any suggestions? Thanks.|||Heini,
How do you define slow? What part of the application is slow? Task
Manager, System Monitor and Profiler can all be used to see what is
happening. Detailed analysis of individual queries can be performed by
using execution plans in Query Analyzer.
HTH
Jerry
"Heini" <Heini@.discussions.microsoft.com> wrote in message
news:F86D1153-55B1-4431-8B25-19211B553E36@.microsoft.com...
> My SQL-Server application is slow after upgrade of the operating system
> from
> windows server 2000 to 2003. Any suggestions? Thanks.

application failover error

We currently have a system that uses sql server 2005 mirroring. In testing the application, we fail over the database in the middle of a 2.0 .NET web application. The failover partner is specified in the connection string.

The next request to the web server results in an error page popping up with the message "A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

This message only occurs for the first request to the web server, all subsequent requests sucessfully access the new failed over database.

Thanks for any suggestions.

I'm having the same problem and haven't yet found anything. I know that if you leave the app alone for at 1.5 minutes and then try to execute it again it will connect just fine. Some timeout is occurring that is then allowing the app to connect and I'm trying to find a way to force that timeout after a failover condition.

Let me know if you have any luck. :-)

|||This confused me also. From what I can tell, this is by

design. If you want the user to see no errors, the application

needs to trap the error and re-open the connection. The

documentation says

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Assume a client is successfully connected to the principal, and a database

mirroring failover (automatic, manual, or forced) occurs. The next time

the application attempts to use the connection, the ADO.NET or SQL Native

Client driver will detect that the connection to the old principal has failed,

and will automatically retry connecting to the new principal as specified in

the failover partner name.

This message talks more about it:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=752968&SiteId=1|||

Could I get some feedback from a Microsoft person if this is supposed to be a "feature" or a "bug" so I could go back to the customer with an answer?

Thanks.

|||

Yes, this is expected behavior. Your application needs to handle these errors and retry the connection.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||

I'm not sure if that's true.

I'm catching the error using a TRY CATCH statement and once I try to connect again after catching the error I still am unable to connect. The only way I'll be able to connect is if I leave my app alone for about 60 seconds and then try to connect. At that point it will work just fine.

This is a sample of my code:

Dim constring As String = "Server=SQL01.eqa\Fantabet;Failover Partner=SQL02.eqa\Fantabet;" _
& "Database=test;" _
& "UID=FailTest2;PWD=test;Network=dbmssocn"

Using con As New SqlConnection(constring)

Using cmd As New SqlCommand
Try 'First TRY statement to catch errors when opening the connection
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter','" & con.DataSource & "')"
Try 'Second Try statement only for executing the SQL Command
cmd.ExecuteNonQuery()
Catch ex As SqlException
lblresult.Text = "Error"

Catch ex As SqlException
con.Close()
lblresult.Text = lblresult.Text & "<BR>Con.open error<BR>Error code = " & ex.ErrorCode & "<BR>Error Description = " & ex.Message
End Try

lblprimary.Text = "Current Primary Database = " & con.DataSource

con.Close()
con.Dispose()
End Using
End Using

So what my code does is that it tries to open the connection and if everything goes smooth a SQL command is executed but if an error is encountered while opening the connection object then the sub procedure finishes and changes the text of a label to "Error". The SQLConnection object is closed manually using a con.close but also by design since I'm using a "Using" statement.

Yet when I try to execute the procedure afterwards it still cannot connect. I must wait about 60 seconds to the procedure will work again.

|||

Matt,

Thanks for your response. My understanding based on Microsoft’s documentation, was that there were no modification required in the application code.

In the event of a fail-over, the sqlclient would try the partner database. In Explicit mode you can even provide the partner in the connection string.

Can you point me to MS documentation that supports your response?

Thank you

application failover error

We currently have a system that uses sql server 2005 mirroring. In testing the application, we fail over the database in the middle of a 2.0 .NET web application. The failover partner is specified in the connection string.

The next request to the web server results in an error page popping up with the message "A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

This message only occurs for the first request to the web server, all subsequent requests sucessfully access the new failed over database.

Thanks for any suggestions.

I'm having the same problem and haven't yet found anything. I know that if you leave the app alone for at 1.5 minutes and then try to execute it again it will connect just fine. Some timeout is occurring that is then allowing the app to connect and I'm trying to find a way to force that timeout after a failover condition.

Let me know if you have any luck. :-)

|||This confused me also. From what I can tell, this is by

design. If you want the user to see no errors, the application

needs to trap the error and re-open the connection. The

documentation says

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Assume a client is successfully connected to the principal, and a database

mirroring failover (automatic, manual, or forced) occurs. The next time

the application attempts to use the connection, the ADO.NET or SQL Native

Client driver will detect that the connection to the old principal has failed,

and will automatically retry connecting to the new principal as specified in

the failover partner name.

This message talks more about it:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=752968&SiteId=1|||

Could I get some feedback from a Microsoft person if this is supposed to be a "feature" or a "bug" so I could go back to the customer with an answer?

Thanks.

|||

Yes, this is expected behavior. Your application needs to handle these errors and retry the connection.

Regards,

Matt Hollingsworth

Sr. Program Manager

SQL Server High Availability

|||

I'm not sure if that's true.

I'm catching the error using a TRY CATCH statement and once I try to connect again after catching the error I still am unable to connect. The only way I'll be able to connect is if I leave my app alone for about 60 seconds and then try to connect. At that point it will work just fine.

This is a sample of my code:

Dim constring As String = "Server=SQL01.eqa\Fantabet;Failover Partner=SQL02.eqa\Fantabet;" _
& "Database=test;" _
& "UID=FailTest2;PWD=test;Network=dbmssocn"

Using con As New SqlConnection(constring)

Using cmd As New SqlCommand
Try 'First TRY statement to catch errors when opening the connection
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter','" & con.DataSource & "')"
Try 'Second Try statement only for executing the SQL Command
cmd.ExecuteNonQuery()
Catch ex As SqlException
lblresult.Text = "Error"

Catch ex As SqlException
con.Close()
lblresult.Text = lblresult.Text & "<BR>Con.open error<BR>Error code = " & ex.ErrorCode & "<BR>Error Description = " & ex.Message
End Try

lblprimary.Text = "Current Primary Database = " & con.DataSource

con.Close()
con.Dispose()
End Using
End Using

So what my code does is that it tries to open the connection and if everything goes smooth a SQL command is executed but if an error is encountered while opening the connection object then the sub procedure finishes and changes the text of a label to "Error". The SQLConnection object is closed manually using a con.close but also by design since I'm using a "Using" statement.

Yet when I try to execute the procedure afterwards it still cannot connect. I must wait about 60 seconds to the procedure will work again.

|||

Matt,

Thanks for your response. My understanding based on Microsoft’s documentation, was that there were no modification required in the application code.

In the event of a fail-over, the sqlclient would try the partner database. In Explicit mode you can even provide the partner in the connection string.

Can you point me to MS documentation that supports your response?

Thank you

Application error

System always popup this message:

Application popup: oprd.exe - Application Error : The application failed to initialize properly (0xc0000142). Click on OK to terminate the application.

I recommend contacting Veritas for help with their application.

Append Query Problems

Hi

I am doing an A level database project. I need to build a system to deal with ordering and selling products.

I am trying to make an append query that will append all the products bought in my "invoices" table to my "archive" table. I want invoices that are three months old to be transferred the the "archive" table. I have been trying to put a criteria in the query for the Date field but I haven't managed to find an expression that works.

I'm sorry if this is too simple and you guys think im just being stupid.
I figured it was worth asking on here!! It's really bugging me.Show us what you got so far. Also ... look up dateadd and datediff in BOL.|||Sorry, what is BOL?
"Date"<=#01/01/2006#

Is that what you mean by show? I don't know how to show the whole query :-s|||Can you write a SELECT statement to retrieve the rows that you want to archive?

-PatP|||Thanks but I don't know what a select statement is! Is it the same as a select query? there's something about that in one of my books.|||Is this project in Access? You may need to ask over in the Access forum...|||Yes it is. I realised after I posted here that there is an access forum too. I have posted in there now thanks!

Appears to load 3.0 in emulator instead of 3.1

Here's what I did to a VS2005 Smart Device application.

1. Removed the reference to System.Data.SqlServerCe.dll.

2. Set a reference to C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\bin\wce500\ System.Data.SqlServerCe.dll.

While it’s loading in the emulator I notice that it appears to still be loading SQLCE3.0 instead of 3.1.

Deploying 'C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\wce500\ARMV4i\sqlce30.ppc.wce5.armv4i.CAB'

Deploying 'C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\wce500\ARMV4i\sqlce30.repl.ppc.wce5.armv4i.CAB'

Deploying 'C:\Program Files\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\wce500\ARMV4i\sqlce30.dev.enu.ppc.wce5.armv4i.CAB'

Do I need to set something else?

SQL Server CE 3.1 is a minor upgrade to 3.0. So, you wont see any change in the file names and file versions. If you are having file version as 3.0.5300, then you can be assured that you are using 3.1

Thanks,

Laxmi

Apparent System hang during intensive long running script: SQL Server 2000 Best Server Set

Hi,
Our db server appeared to hang during a long running script last
night. Our windows guys rebooted the server. However my suspicion is
that the script was consuming all systems resources but was still
running.
The SQL server is set to use all CPUs (4) and max all memory. It also
has raised priority on Windows. Does this sound like a possibility? If
so, what are the best settings for a db server, just so that we can
keep access during intensive queries?
When the server came back up, sql spent a great deal of time
recovering this database, presumably because it was mid-transaction.
Cheers,
James
MCDBAYou might want to limit memory a bit (sp_configure, max server memory), definitely remove higher
priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to other
SQL users. But also look at how the batch is written, as part of the problem might have been
blocking. For instance, perhaps you can split up some modifications over several transactions
instead of one big truncations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
> Hi,
> Our db server appeared to hang during a long running script last
> night. Our windows guys rebooted the server. However my suspicion is
> that the script was consuming all systems resources but was still
> running.
> The SQL server is set to use all CPUs (4) and max all memory. It also
> has raised priority on Windows. Does this sound like a possibility? If
> so, what are the best settings for a db server, just so that we can
> keep access during intensive queries?
> When the server came back up, sql spent a great deal of time
> recovering this database, presumably because it was mid-transaction.
> Cheers,
> James
> MCDBA
>|||On Mar 29, 11:20 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> You might want to limit memory a bit (sp_configure, max server memory), definitely remove higher
> priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to other
> SQL users. But also look at how the batch is written, as part of the problem might have been
> blocking. For instance, perhaps you can split up some modifications over several transactions
> instead of one big truncations.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
>
> > Hi,
> > Our db server appeared to hang during a long running script last
> > night. Our windows guys rebooted the server. However my suspicion is
> > that the script was consuming all systems resources but was still
> > running.
> > The SQL server is set to use all CPUs (4) and max all memory. It also
> > has raised priority on Windows. Does this sound like a possibility? If
> > so, what are the best settings for a db server, just so that we can
> > keep access during intensive queries?
> > When the server came back up, sql spent a great deal of time
> > recovering this database, presumably because it was mid-transaction.
> > Cheers,
> > James
> > MCDBA- Hide quoted text -
> - Show quoted text -
Thanks Tibor, A great help. On the windows priority, is that a well
known no-no? What problems does it cause?
Yes the script was a pile of erm ...
I wanted to add a new column but not as the last column so I scripted
out of EM. So million of records into a temp table, rename etc,
reindex etc etc. AND I put a transaction round the whole thing... Not
clever. Plus transaction backups were happening at the same time...
So I'm rewriting to simply add a NULLABLE column to the end of the
table using ALTER TABLE and removing the wrapping transaction. Should
be a lot faster...
And I thought I was a reasonable DBA... :o(
When doing my testing I was on the dev server using Simple Recovery
model so didn't hit the same problems.
Many thanks,
James|||On Mar 29, 12:56 pm, "JimLad" <jamesdbi...@.yahoo.co.uk> wrote:
> On Mar 29, 11:20 am, "Tibor Karaszi"
>
>
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > You might want to limit memory a bit (sp_configure, max server memory), definitely remove higher
> > priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to other
> > SQL users. But also look at how the batch is written, as part of the problem might have been
> > blocking. For instance, perhaps you can split up some modifications over several transactions
> > instead of one big truncations.
> > --
> > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualityle...
> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> >news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
> > > Hi,
> > > Our db server appeared to hang during a long running script last
> > > night. Our windows guys rebooted the server. However my suspicion is
> > > that the script was consuming all systems resources but was still
> > > running.
> > > The SQL server is set to use all CPUs (4) and max all memory. It also
> > > has raised priority on Windows. Does this sound like a possibility? If
> > > so, what are the best settings for a db server, just so that we can
> > > keep access during intensive queries?
> > > When the server came back up, sql spent a great deal of time
> > > recovering this database, presumably because it was mid-transaction.
> > > Cheers,
> > > James
> > > MCDBA- Hide quoted text -
> > - Show quoted text -
> Thanks Tibor, A great help. On the windows priority, is that a well
> known no-no? What problems does it cause?
> Yes the script was a pile of erm ...
> I wanted to add a new column but not as the last column so I scripted
> out of EM. So million of records into a temp table, rename etc,
> reindex etc etc. AND I put a transaction round the whole thing... Not
> clever. Plus transaction backups were happening at the same time...
> So I'm rewriting to simply add a NULLABLE column to the end of the
> table using ALTER TABLE and removing the wrapping transaction. Should
> be a lot faster...
> And I thought I was a reasonable DBA... :o(
> When doing my testing I was on the dev server using Simple Recovery
> model so didn't hit the same problems.
> Many thanks,
> James- Hide quoted text -
> - Show quoted text -
Is it bad practice to temporarily switch to SIMPLE recovery mode when
doing these kinds of operations? Everyone will be off the database at
the time... It's taking forever!|||> Thanks Tibor, A great help. On the windows priority, is that a well
> known no-no? What problems does it cause?
I believe it is still considered a no-no. I recall at some version that a combination of settings
would lift SQL Server to real-time priority. I don't think it can happen anymore, but in some
extreme cases, you might starve OS. Also, if you don't have anything else on the machine, then what
would SQL Server be higher than? I suggest you Google to see if you can find more official words on
this.
> I wanted to add a new column but not as the last column so I scripted
> out of EM.
Ugh. Yes, I can see why that happened. We all learn from our mistakes. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1175169365.880312.134100@.y80g2000hsf.googlegroups.com...
> On Mar 29, 11:20 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> You might want to limit memory a bit (sp_configure, max server memory), definitely remove higher
>> priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to
>> other
>> SQL users. But also look at how the batch is written, as part of the problem might have been
>> blocking. For instance, perhaps you can split up some modifications over several transactions
>> instead of one big truncations.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
>> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
>> news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
>>
>> > Hi,
>> > Our db server appeared to hang during a long running script last
>> > night. Our windows guys rebooted the server. However my suspicion is
>> > that the script was consuming all systems resources but was still
>> > running.
>> > The SQL server is set to use all CPUs (4) and max all memory. It also
>> > has raised priority on Windows. Does this sound like a possibility? If
>> > so, what are the best settings for a db server, just so that we can
>> > keep access during intensive queries?
>> > When the server came back up, sql spent a great deal of time
>> > recovering this database, presumably because it was mid-transaction.
>> > Cheers,
>> > James
>> > MCDBA- Hide quoted text -
>> - Show quoted text -
> Thanks Tibor, A great help. On the windows priority, is that a well
> known no-no? What problems does it cause?
> Yes the script was a pile of erm ...
> I wanted to add a new column but not as the last column so I scripted
> out of EM. So million of records into a temp table, rename etc,
> reindex etc etc. AND I put a transaction round the whole thing... Not
> clever. Plus transaction backups were happening at the same time...
> So I'm rewriting to simply add a NULLABLE column to the end of the
> table using ALTER TABLE and removing the wrapping transaction. Should
> be a lot faster...
> And I thought I was a reasonable DBA... :o(
> When doing my testing I was on the dev server using Simple Recovery
> model so didn't hit the same problems.
> Many thanks,
> James
>|||> Is it bad practice to temporarily switch to SIMPLE recovery mode when
> doing these kinds of operations?
That depends on that backups you do for the database. Switching to simple will break the log backup
sequence, so talk to the backup administrator first!
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:1175178098.755362.269210@.b75g2000hsg.googlegroups.com...
> On Mar 29, 12:56 pm, "JimLad" <jamesdbi...@.yahoo.co.uk> wrote:
>> On Mar 29, 11:20 am, "Tibor Karaszi"
>>
>>
>> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> > You might want to limit memory a bit (sp_configure, max server memory), definitely remove
>> > higher
>> > priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to
>> > other
>> > SQL users. But also look at how the batch is written, as part of the problem might have been
>> > blocking. For instance, perhaps you can split up some modifications over several transactions
>> > instead of one big truncations.
>> > --
>> > Tibor Karaszi, SQL Server
>> > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualityle...
>> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
>> >news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
>> > > Hi,
>> > > Our db server appeared to hang during a long running script last
>> > > night. Our windows guys rebooted the server. However my suspicion is
>> > > that the script was consuming all systems resources but was still
>> > > running.
>> > > The SQL server is set to use all CPUs (4) and max all memory. It also
>> > > has raised priority on Windows. Does this sound like a possibility? If
>> > > so, what are the best settings for a db server, just so that we can
>> > > keep access during intensive queries?
>> > > When the server came back up, sql spent a great deal of time
>> > > recovering this database, presumably because it was mid-transaction.
>> > > Cheers,
>> > > James
>> > > MCDBA- Hide quoted text -
>> > - Show quoted text -
>> Thanks Tibor, A great help. On the windows priority, is that a well
>> known no-no? What problems does it cause?
>> Yes the script was a pile of erm ...
>> I wanted to add a new column but not as the last column so I scripted
>> out of EM. So million of records into a temp table, rename etc,
>> reindex etc etc. AND I put a transaction round the whole thing... Not
>> clever. Plus transaction backups were happening at the same time...
>> So I'm rewriting to simply add a NULLABLE column to the end of the
>> table using ALTER TABLE and removing the wrapping transaction. Should
>> be a lot faster...
>> And I thought I was a reasonable DBA... :o(
>> When doing my testing I was on the dev server using Simple Recovery
>> model so didn't hit the same problems.
>> Many thanks,
>> James- Hide quoted text -
>> - Show quoted text -
> Is it bad practice to temporarily switch to SIMPLE recovery mode when
> doing these kinds of operations? Everyone will be off the database at
> the time... It's taking forever!
>|||On Mar 29, 4:39 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > Is it bad practice to temporarily switch to SIMPLE recovery mode when
> > doing these kinds of operations?
> That depends on that backups you do for the database. Switching to simple will break the log backup
> sequence, so talk to the backup administrator first!
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:1175178098.755362.269210@.b75g2000hsg.googlegroups.com...
>
> > On Mar 29, 12:56 pm, "JimLad" <jamesdbi...@.yahoo.co.uk> wrote:
> >> On Mar 29, 11:20 am, "Tibor Karaszi"
> >> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> > You might want to limit memory a bit (sp_configure, max server memory), definitely remove
> >> > higher
> >> > priority. For the batch, consider using MAXDOP options for your queries, leaving some CPUs to
> >> > other
> >> > SQL users. But also look at how the batch is written, as part of the problem might have been
> >> > blocking. For instance, perhaps you can split up some modifications over several transactions
> >> > instead of one big truncations.
> >> > --
> >> > Tibor Karaszi, SQL Server
> >> > MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualityle...
> >> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> >> >news:1175162797.522975.64110@.p77g2000hsh.googlegroups.com...
> >> > > Hi,
> >> > > Our db server appeared to hang during a long running script last
> >> > > night. Our windows guys rebooted the server. However my suspicion is
> >> > > that the script was consuming all systems resources but was still
> >> > > running.
> >> > > The SQL server is set to use all CPUs (4) and max all memory. It also
> >> > > has raised priority on Windows. Does this sound like a possibility? If
> >> > > so, what are the best settings for a db server, just so that we can
> >> > > keep access during intensive queries?
> >> > > When the server came back up, sql spent a great deal of time
> >> > > recovering this database, presumably because it was mid-transaction.
> >> > > Cheers,
> >> > > James
> >> > > MCDBA- Hide quoted text -
> >> > - Show quoted text -
> >> Thanks Tibor, A great help. On the windows priority, is that a well
> >> known no-no? What problems does it cause?
> >> Yes the script was a pile of erm ...
> >> I wanted to add a new column but not as the last column so I scripted
> >> out of EM. So million of records into a temp table, rename etc,
> >> reindex etc etc. AND I put a transaction round the whole thing... Not
> >> clever. Plus transaction backups were happening at the same time...
> >> So I'm rewriting to simply add a NULLABLE column to the end of the
> >> table using ALTER TABLE and removing the wrapping transaction. Should
> >> be a lot faster...
> >> And I thought I was a reasonable DBA... :o(
> >> When doing my testing I was on the dev server using Simple Recovery
> >> model so didn't hit the same problems.
> >> Many thanks,
> >> James- Hide quoted text -
> >> - Show quoted text -
> > Is it bad practice to temporarily switch to SIMPLE recovery mode when
> > doing these kinds of operations? Everyone will be off the database at
> > the time... It's taking forever!- Hide quoted text -
> - Show quoted text -
So, in the end I did the following:
This solution relies on single user access to database, i.e kicking
everybody off. Also it relies on changing recovery model and is not
transaction safe. It is for large multi -million row updates. Smaller
updates don't need this stuff.
Stop log backups
Take FULL BACKUP
Script:
ALTER DATABASE audit_trail SET RECOVERY SIMPLE -- transaction log gets
truncated after transactions have completed. Stops log growing.
GO
-- Add the extra column as nullable
ALTER TABLE dbo.change_keys
ADD ordinal_position tinyint NULL -- NULLABLE means v little cost.
GO
-- Run big update in batches. test for appropriate batch size. At some
point performance will go bad.
-- i.e. in this case batches of 300000 took a ~1s, batches of 500000
took > 1min.
-- Update all change_keys records to set ordinal_position to 1. Do so
in optimised batches of 300,000 records.
SET NOCOUNT ON
DECLARE @.maxid int
DECLARE @.minid int
DECLARE @.currentid int
DECLARE @.step int
DECLARE @.count int
SET @.maxid = (SELECT MAX(change_id) FROM change_keys)
SET @.minid = (SELECT MIN(change_id) FROM change_keys)
SET @.currentid = @.minid
SET @.step = 300000
SET @.count = 0
WHILE @.currentid <= @.maxid
BEGIN
UPDATE change_keys WITH (TABLOCK) -- tablock useful as only user on
database - stops page locks having to escalate.
SET ordinal_position = 1
WHERE change_id BETWEEN @.currentid AND @.currentid + @.step - 1
SET @.count = @.count + @.@.rowcount
PRINT @.count
SET @.currentid = @.currentid + @.step
END
PRINT 'Total updated records: ' + CONVERT(varchar(15), @.count)
GO
-- Now make ordinal_position NOT NULL, if necessary
ALTER TABLE dbo.change_keys
ALTER COLUMN ordinal_position tinyint NOT NULL
GO
-- Put db back into FULL recovery mode.
ALTER DATABASE audit_trail SET RECOVERY FULL
GO
I have simplified from what I needed to do, but hopefully this is
helpful to someone...
Thanks again, Tibor.
Cheers,
James

Anytime I connect to an Excel file from SSIS05, I start getting endemic system.outofmemoryexcept

This is my error text... how do I get this nonsense to stop?!

===================================

Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft Visual Studio)

Program Location:

at System.String.GetStringForStringBuilder(String value, Int32 startIndex, Int32 length, Int32 capacity)
at System.Text.StringBuilder.GetNewString(String currentString, Int32 requiredLength)
at System.Text.StringBuilder.Append(Char value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BufferTextWriter.Write(Char ch)
at System.IO.TextWriter.Write(Char[] buffer, Int32 index, Int32 count)
at System.IO.TextWriter.Write(Char[] buffer)
at System.IO.TextWriter.Write(String value)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializePackage(IDesignerSerializationManager manager, Package package, TextWriter textWriter)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)

Can we get some more information? Is this when you connect to any Excel file, or to one particular one?

If this only happens when connecting to a specific file, how much data is being returned?

Is there any information in task manager that you can give us to help? How much memory is available, or in use? Do you see memory usage spiking in a particular process when connecting to Excel?

Donald

Anything that you find in SQL object scripts, you can also find them in system tables?

I tried all the INFORMATION_SCHEMA on SQL 2000 and
I see that the system tables hold pretty much everything I am
interested in: Objects names (columns, functions, stored procedures, ...)
stored procedure statements in syscomments table.

My questions are:

If you script your whole database everything you end up having
in the text sql scripts, are those also located in the system tables?
That means i could simply read those system tables to get any information
I would normally look in the sql script files?

Can i quickly generate a SQL statement of all the indexes on my database?

I read many places that Microsoft
says not to modify anything in those tables and not query them since their
structure might change in future SQL versions.

Is it safe to use and rely the system tables?

I basically want to do at least fetching of information i want from the
system tables rather than the SQL script files.
I also want to know if it's pretty safe for me to make changes in these
tables.
Can i rename an object name for example an Index name, a Stored Procedure
name?
Can i add a new column in the syscolumns table for a user table?

Thank youThe SQLDMO script method can be used to script any SQL Server object. You
can also generate scripts from Enterprise, Query Analyzer or from the
command line using the scptxfr.exe utility.

> Is it safe to use and rely the system tables?

Yes and no. The INFORMATION_SCHEMA views are a better source for lots of the
metadata and they are usually recommended as the preferred method whenever
possible. However, the information schema doesn't cover everything (no
indexes for example) so you may still need to make use of system tables for
some things. If you rely only on the features documented in Books Online and
avoid referencing the stuff that isn't explained or that's marked as
"reserved" then you should be fairly safe. Be sensible though and don't use
system tables when you don't have to. Check out the "Meta Data Functions"
topic in Books Online for other alternatives.

In SQL Server 2005 the old system tables are superceded by a new set of
views that give you much more comprehensive and convenient view of the
metadata. The old-style system tables are still supported for backwards
compatibility although they aren't being extended to support new features.
In theory, most things will work in 2005 as in 2000 but it's not going to be
100% so assume some things may need to be fixed if and when you upgrade.

> I also want to know if it's pretty safe for me to make changes in these
> tables.

Never. Not if you value the integrity of your server and your database. All
the things you need to do are supported through procs and DDL statements.
That includes renaming objects and adding new columns. Don't mess with
updates against the system tables.

--
David Portas
SQL Server MVP
--|||>> Is it safe to use and rely the system tables?

> Yes and no. The INFORMATION_SCHEMA views are a better source for lots of
> the metadata and they are usually recommended as the preferred method
> whenever possible. However, the information schema doesn't cover
> everything (no indexes for example) so you may still need to make use of
> system tables for some things. If you rely only on the features documented
> in Books Online and avoid referencing the stuff that isn't explained or
> that's marked as "reserved" then you should be fairly safe. Be sensible
> though and don't use system tables when you don't have to. Check out the
> "Meta Data Functions" topic in Books Online for other alternatives.

I started reading about Meta Data last week in SQL Books Online and I
am interested to learn more. However I can only do one thing at a time so
I am trying to understand the system tables first and find out ways
how/when/why I could and should use them.
I'll check out "Meta Data Functions" to see what they do.

>> I also want to know if it's pretty safe for me to make changes in these
>> tables.

> Never. Not if you value the integrity of your server and your database.
> All the things you need to do are supported through procs and DDL
> statements. That includes renaming objects and adding new columns. Don't
> mess with updates against the system tables.

Understood. I won't make any changes in these tables. You are right if I can
use the supported procs and DDL statements then I'll use them.

Thank you|||>> Is it safe to use and rely the system tables?

> Yes and no. The INFORMATION_SCHEMA views are a better source for lots of
> the metadata and they are usually recommended as the preferred method
> whenever possible. However, the information schema doesn't cover
> everything (no indexes for example) so you may still need to make use of
> system tables for some things. If you rely only on the features documented
> in Books Online and avoid referencing the stuff that isn't explained or
> that's marked as "reserved" then you should be fairly safe. Be sensible
> though and don't use system tables when you don't have to. Check out the
> "Meta Data Functions" topic in Books Online for other alternatives.

I started reading about Meta Data last week in SQL Books Online and I
am interested to learn more. However I can only do one thing at a time so
I am trying to understand the system tables first and find out ways
how/when/why I could and should use them.
I'll check out "Meta Data Functions" to see what they do.

>> I also want to know if it's pretty safe for me to make changes in these
>> tables.

> Never. Not if you value the integrity of your server and your database.
> All the things you need to do are supported through procs and DDL
> statements. That includes renaming objects and adding new columns. Don't
> mess with updates against the system tables.

Understood. I won't make any changes in these tables. You are right if I can
use the supported procs and DDL statements then I'll use them.

Thank you|||serge (sergea@.nospam.ehmail.com) writes:
> I tried all the INFORMATION_SCHEMA on SQL 2000 and
> I see that the system tables hold pretty much everything I am
> interested in: Objects names (columns, functions, stored procedures, ...)
> stored procedure statements in syscomments table.
> My questions are:
> If you script your whole database everything you end up having
> in the text sql scripts, are those also located in the system tables?

Scripting does not place anything in system tables. Creating objects
does. The source code of stored procedures, views, functions, constraints
and a few more objects are in syscomments. However, they are stored in
8000 character slices, and non-trivial to decode.

Tables, indexes and user-defined types are not stored as text as such
in SQL Server, but as scattered pieces which the scripting tools
reassemble.

In any case, you should not really script your databases more than at
most once. View the database as a storage for binary objects, and keep
your source code under version control. (But if you started without
version control, you may need to script once to get yourself a baseline.)

> I read many places that Microsoft
> says not to modify anything in those tables and not query them since their
> structure might change in future SQL versions.
> Is it safe to use and rely the system tables?

As David said, there are new means for retrieving meta data in SQL 2005,
and the old system tables in SQL 2005 become views implemented on top
of these, known as "Compatibility views". They are also marked as
deperecated, and they will surely disappear from a future version of
SQL Server, but this is not likely to happen this decade.

Important is to only use documented columns, and only use columns what
they are documented for. A column which has the short description of
"Reserved", will very likely always have a value of 0 or NULL in SQL 2005.

Personally, I use only the system tables for meta-data access in SQL 2000,
and this is also what I recommend. The INFORMATION_SCHEMA views are not
whole-covering, so mixing both means that you must know two paradigms.
There are also some traps with the views which can cause you to not
get data you expect, or columns may have names which makes promises
they don't live up to. SQL Server MVP Tony Rogerson also liks to point
out that they have scalability problems. Finally, all that uppercase
is ugly. :-)

> I basically want to do at least fetching of information i want from the
> system tables rather than the SQL script files.
> I also want to know if it's pretty safe for me to make changes in these
> tables.

Never make changes directly to system tables, unless you are told to so
by a Microsoft support professional.

> Can i rename an object name for example an Index name, a Stored Procedure
> name?

To rename an index use sp_rename. To rename a stored procedure, drop
the procedure and reload it under the new name.

> Can i add a new column in the syscolumns table for a user table?

Absolutely not! You will corrupt your database.

Please keep in mind that there is a whole lot of internal structures
in SQL Server that are not exposed. When you add a column to a table,
SQL Server may need to update all pages in the tables, to create space
for the column. Query plans for procedures that has SELECT * may need
to be flushed etc.

In fact, in SQL 2005, you don't even have read access to the real system
tables. All you see is views.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp