Is there a performance hit if I run with a db on compatibility level 80 in sql server 2005?

The database in the sql 2005 test environment is running quicker than in the prod of sql server 2000 (same server setups) with compatibility level 90 but due to some programming bugs we will need to go back to level 80.

Is there a performance hit if I run with a db on compatibility level 80 in sql server 2005?

Thanks,

Vin

I was told this by someone :

You may see some difference depends on the usage...
When you change cmpt to 8.0, sql engine has to be changed...new sql engine features will not work..

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

|||In my experience with actual benchmarks, I have not seen a performance difference between running in 8.0 and 9.0 mode. You lose access to many of the new features of 2005 (such as DMV's) when you are in 8.0 mode.sql

Is there a more efficient way to perform this process?

My e-commerce site is currently running the following process when items are shipped from the manufacturer:

1. Manufacturer sends a 2-column CSV to the retailer containing an Order Number and its Shipping Tracking Number.

2. Through the web admin panel I've built, a retail staff member uploads the CSV to the server.

3. In code, the CSV is parsed. The tracking number is saved to the database attached to the Order Number.

4. After a tracking # is saved, each item in that order has its status updated to Shipped.

5. The customer is sent an email using ASPEmail from Persits which contains their tracking #.

The process seems to work without a problem so long as the CSV contains roughly 50 tracking #'s or so. The retailer has gotten insanely busy and wants to upload 3 or 4 thousand tracking #'s in a single CSV, but the process times out, even with large server timeout values being set in code. Is there a way to streamline the process to make this work more efficiently? I can provide the code if that helps.

It really depends on how your database backend works. SQL Server? Transactions?

|||

SQL Server 2000. I am not using Transactions. Any suggestions you have as to how I can improve the performance he would be appreciated. Here's what the code looks like. Basically I am building a giant SQL string (multiple EXEC calls on the same procedure) with a stringbuilder and executing it all at once at the bottom:

objOleDb.Open()
objCmd = New OleDbCommand("SELECT * FROM TrackingImport.csv", objOleDb)
objRdr = objCmd.ExecuteReader()

While objRdr.Read()
If Len(Trim(objRdr(1).ToString)) > 0 And Len(Trim(objRdr(0).ToString)) > 0 Then
If IsNumeric(objRdr(0)) Then
If CLng(objRdr(0)) > 0 Then
strSQL.Append("EXEC spImportTracking '" & objRdr(1) & "'," & objRdr(0) & ";")
EmailTracking(objRdr(0), objRdr(1))
i = i + 1
End If
End If
End If
End While

'Update orders with tracking numbers
dbobj.Open()
objSQLCmd = New SqlCommand(strSQL.ToString(), dbobj)
objSQLCmd.ExecuteNonQuery()

The stored procedure called above looks like this:

AlterPROCEDUREspImportTracking

@.TrackingNumberNVARCHAR(50)=NULL,

@.OrderNumberINT=NULL

AS

DECLARE@.OrderAddressIDINT,

@.OrderIDINT,

@.OrderItemIDINT,

@.CountryVARCHAR(2)

/*1. Get OrderID*/

SELECT@.OrderID=(SELECTuid

FROMOrders

WHEREOrderNumber=@.OrderNumber)

/*2. Check Country*/

SELECT@.Country=(SELECTTOP1Country

FROMOrderAddresses

WHEREOrderID=@.OrderID

ANDType=3)

IF@.Country='CA'ANDLEFT(@.TrackingNumber,2)='1Z'

BEGIN

SELECT dbo.Orders.uid,dbo.Orders.OrderNumber,dbo.Customers.FirstName,dbo.Customers.LastName,dbo.Customers.EMail,

'Invalid'AS'TrackingNo'

FROM dbo.CustomersINNERJOIN

dbo.OrdersONdbo.Customers.uid=dbo.Orders.CustomerID

WHEREdbo.Orders.OrderNumber=@.OrderNumber

END

ELSE

BEGIN

/*3. Insert to Tracking*/

INSERTINTOOrderTracking(BackOrderFlag,TrackingNumber,TrackingMessage)

VALUES(0,@.TrackingNumber,@.OrderNumber)

/*2. Get all unpaid (full or partial) orders for thiscustomer*/

DECLAREOrder_CursorCURSORFOR

SELECT OrderItems.uid

FROM OrderItems

WHERE OrderItems.OrderID=@.OrderID

OPENOrder_Cursor

FETCHNEXTFROMOrder_Cursor

INTO@.OrderItemID

WHILE@.@.FETCH_STATUS=0

BEGIN

/*2. Mark All Items in this Order as Shipped*/

EXECspUpdateProductionStatus@.OrderItemID,100

FETCHNEXTFROMOrder_Cursor

INTO@.OrderItemID

END

CLOSEOrder_Cursor

DEALLOCATEOrder_Cursor

/*Return Customer information for confirmation Email*/

SELECT dbo.Orders.uid,dbo.Orders.OrderNumber,dbo.Customers.FirstName,dbo.Customers.LastName,dbo.Customers.EMail,@.TrackingNumberAS'TrackingNo'

FROM dbo.CustomersINNERJOIN

dbo.OrdersONdbo.Customers.uid=dbo.Orders.CustomerID

WHEREdbo.Orders.OrderNumber=@.OrderNumber

END

SETNOCOUNTON

The EmailTracking method that gets called for each record looks like this:

PrivateSubEmailTracking(ByVal intOrderNumberAsLong,ByVal strTrackingNoAsString)

Dim objEmailAsNew CEmail()

Dim strBodyAsNewStringBuilder()

Dim dbobjAsNewSqlConnection(ConfigurationSettings.AppSettings("strConn"))

Dim objCmdAsSqlCommand

Dim objRdrAs SqlDataReader

Dim strBillCountryAsString =""

Try

dbobj.Open()

objCmd =NewSqlCommand("spGetShippingAddresses", dbobj)

objCmd.CommandType = CommandType.StoredProcedure

objCmd.Parameters.Add("@.OrderNumber",intOrderNumber)

objRdr = objCmd.ExecuteReader()

objEmail.strFrom = "shipping@.mydomain.com"

objEmail.strFromName = "Shipping Department"

objEmail.strSubject = "Ship Notification, TrackingNumber " & strTrackingNo

strBody.Append(strIntro)

While objRdr.Read

strBillCountry =objRdr("BillCountry").ToString

objEmail.strTo = objRdr("Email").ToString

strBody.Append("<b>Ship To:</b><br>"& objRdr("Recipient").ToString & "<br>" &_

objRdr("Address").ToString &"<br>" & objRdr("City").ToString &"<br>" & objRdr("State").ToString & _

"<br>" &objRdr("Zip").ToString & "<br>" &objRdr("Country").ToString & "<hr>")

IfobjRdr("WSClient").ToString <> ""Then

UpdatePhotoMgr(objRdr("WSClient").ToString,objRdr("PO#").ToString, strTrackingNo)

EndIf

EndWhile

strBody.Append("<p><b>Tracking Number:</b>" & strTrackingNo & "</p>" & _

"<p><b>Reference Order #:</b>" & intOrderNumber & "</p><p>"& _

SetTrackingLink(strTrackingNo))

strBody.Append(strEnd)

objEmail.strMessage = strBody.ToString

If strBillCountry ="US"Or strBillCountry = ""Or (strBillCountry = "CA"And Left(strTrackingNo, 1) = "D")Then

IfLen(objEmail.strTo) > 0Then

objEmail.SendMail()

Else

Response.Write("<script language='javascript'>alert('AddressMissing for Order " & _

intOrderNumber.ToString &"')</script>")

EndIf

EndIf

Catch objErrorAs Exception

lblError.Text = "Error occurred when sending to" & objEmail.strTo & ": " & objError.Message & _

". Please try re-loading the page, or contact technical support with adescription of this problem."

'Notify tech support

Dim objErrAsNew CEmail()

objErr.SendErrorMessage(objError, "Tracking EmailError", objEmail.strTo, Session("CompanyID"),Session("StoreID"))

objErr =Nothing

context.ClearError()

ExitTry

Finally

objRdr.Close()

objCmd.Dispose()

dbobj.Close()

EndTry

EndSub

1 thought I had would be rather than going to the database each time to retrieve the customer info for email purposes, I could grab all the customer info once in a dataset and then filter with a dataview 1 record at a time to send the emails. Not sure if that would help or not.

|||

1. Using the OleDbConnection to read the file is cool, but I think you could just as well just use a plain StreamReader, get each rows and split the string by the comma.

2. Don't use plain SQL strings. An evil CSV file could wipe out the entire database (consider the row "abc,1;DROP TABLE Orders")

3. Execute everything within a transaction. Not only could it help performance, it's also vital since this operation must be atomic. All the orders should be updated, and if something fails, nothing should happen.

4. Use several calls to spImportTracking (properly parametrized) and execute them each with its own ExecuteNonQuery.

5. Treat the email sending as a separate process. It could fail, and the orders need to know this. Add a status column to the Order table to indicate that an email should be sent, and process it afterwards. Don't return anything from spImportTracking.

6. What does spUpdateProductionStatus do? If it's just setting a status the entire cursor block should be replaced with someting like:

UPDATE OrderItems SET ProductionStatus=100 WHERE OrderID = @.OrderID

Extra:

Log each file upload (who, what and when) in a table with some kind of identifier. Store that identfier with each order (e.g add a column called "batchFileId" referring to the log table). It's good for tracking down errors or mistakes and can also use it when sending the emails (e.g send all emails relating to batchFileid=345)

Don't prefix your stored procedures with "sp". That prefix is reserved for system stored procedures.

An alternative would be to import the actual file contents into a table, and then make one single massive update by relating to that table (the import could be done using a DataAdapter, the SqlBulkUpdate class or just plain inserts (what the adapter would do anyway))|||

Thanks for such a thoughtful reply. I don't usually use SQL strings, but figured appending the Exec statements together and calling ExecuteNonQuery once was more efficient than doing in a loop. I will try that as you've suggested. I will test it out with the StreamReader, and yes, #6 makes a lot more sense. The UpdateProductionStatus procedure has a lot of logic it in to determine the order status which I guess I don't need in this case since I know what the status should be already (and the cursor isn't needed either).

|||

The StreamReader thing was really a minor issue, so don't wast too much energy on fixing that. Remember to use transactions, i.e

BeginTransaction

A lot of ExecuteNonQueries

If success then

Commit

Else

Rollback

End If

Is there a Cluster command line I can run which will stop my "SQL Agent Service"?

Gurus,
Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
Cluster command line I can run which will stop my "SQL Agent Service"?
Spin
Hi
How about NET STOP?
John
"Spin" <Spin@.invalid.com> wrote in message
news:656t5iF2eq3dbU1@.mid.individual.net...
> Gurus,
> Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
> Cluster command line I can run which will stop my "SQL Agent Service"?
> --
> Spin
>
|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e2yjJedkIHA.5368@.TK2MSFTNGP04.phx.gbl...
> Hi
> How about NET STOP?
No can do John. NET STOP stops the service outside of the Cluster
environment. What will happen is the Windows Cluster service will detect
the service down then automatically restart it! What I need is a Cluster
service command line tool which gracefully stops the service per "knowledge"
if you will, of the Cluster service.
|||For the default instance:
cluster.exe /cluster:<cluster name> resource "SQL Server Agent" /offline
For a named instance (assuming that the instance name is SQL2):
cluster.exe /cluster:<cluster name> resource "SQL Server Agent (SQL2)"
/offline
Linchi
"Spin" wrote:

> Gurus,
> Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
> Cluster command line I can run which will stop my "SQL Agent Service"?
> --
> Spin
>
>
|||Thanks! I'll shout back on Monday if this works...
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C9EF5727-A88B-4261-B445-266B2C45990C@.microsoft.com...[vbcol=seagreen]
> For the default instance:
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent" /offline
> For a named instance (assuming that the instance name is SQL2):
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent (SQL2)"
> /offline
> Linchi
> "Spin" wrote:
|||"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C9EF5727-A88B-4261-B445-266B2C45990C@.microsoft.com...
> For the default instance:
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent" /offline
> For a named instance (assuming that the instance name is SQL2):
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent (SQL2)"
> /offline
> Linchi
Worked!!!!! You are a Gentleman and a Scholar!
Spin

Is there a Cluster command line I can run which will stop my "SQL Agent Service"?

Gurus,
Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
Cluster command line I can run which will stop my "SQL Agent Service"?
--
SpinHi
How about NET STOP?
John
"Spin" <Spin@.invalid.com> wrote in message
news:656t5iF2eq3dbU1@.mid.individual.net...
> Gurus,
> Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
> Cluster command line I can run which will stop my "SQL Agent Service"?
> --
> Spin
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:e2yjJedkIHA.5368@.TK2MSFTNGP04.phx.gbl...
> Hi
> How about NET STOP?
No can do John. NET STOP stops the service outside of the Cluster
environment. What will happen is the Windows Cluster service will detect
the service down then automatically restart it! What I need is a Cluster
service command line tool which gracefully stops the service per "knowledge"
if you will, of the Cluster service.|||For the default instance:
cluster.exe /cluster:<cluster name> resource "SQL Server Agent" /offline
For a named instance (assuming that the instance name is SQL2):
cluster.exe /cluster:<cluster name> resource "SQL Server Agent (SQL2)"
/offline
Linchi
"Spin" wrote:
> Gurus,
> Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
> Cluster command line I can run which will stop my "SQL Agent Service"?
> --
> Spin
>
>|||Thanks! I'll shout back on Monday if this works...
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C9EF5727-A88B-4261-B445-266B2C45990C@.microsoft.com...
> For the default instance:
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent" /offline
> For a named instance (assuming that the instance name is SQL2):
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent (SQL2)"
> /offline
> Linchi
> "Spin" wrote:
>> Gurus,
>> Running SQL Server 2005 SP2 on a Windows Server 2003 Cluster. Is there a
>> Cluster command line I can run which will stop my "SQL Agent Service"?
>> --
>> Spin
>>|||"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:C9EF5727-A88B-4261-B445-266B2C45990C@.microsoft.com...
> For the default instance:
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent" /offline
> For a named instance (assuming that the instance name is SQL2):
> cluster.exe /cluster:<cluster name> resource "SQL Server Agent (SQL2)"
> /offline
> Linchi
Worked!!!!! You are a Gentleman and a Scholar!
--
Spin

Is there a better (faster) way?

I am running this query on MS SQL 2000 that is deleting records on an AS400
table. Extremely slow. Is there a different way I should/could do this?
\
Background - Data comes into COMMON. I read the data into #LoadSwipes,
process the data, and then want to delete the processed records from COMMON.
delete KRONOS.RSIGL.TIMECUSTOM.COMMON
from #LoadSwipes ls
inner join KRONOS.RSIGL.TIMECUSTOM.COMMON k
on ls.Area = k.Area and
ls.TimeSt = k.TimeSt and
ls.EmpNum = k.EmpNum and
ls.Clock = k.Clock and
ls.EmpNu9 = k.EmpNu9 and
ls.ActIn = k.ActIn
Thanks
EricHi Eric,
This might not apply to you situation, but one thing that you may consider
is copying the record that you want to keep into a seperate temp table, then
truncate the original table, and finally copy the records from the temp table
back to the original.
This would be useful if the number of records that you want to keep is
dramatically less than the number of records that you want to delete.
--
MG
"Eric Stewart" wrote:
> I am running this query on MS SQL 2000 that is deleting records on an AS400
> table. Extremely slow. Is there a different way I should/could do this?
> \
> Background - Data comes into COMMON. I read the data into #LoadSwipes,
> process the data, and then want to delete the processed records from COMMON.
> delete KRONOS.RSIGL.TIMECUSTOM.COMMON
> from #LoadSwipes ls
> inner join KRONOS.RSIGL.TIMECUSTOM.COMMON k
> on ls.Area = k.Area and
> ls.TimeSt = k.TimeSt and
> ls.EmpNum = k.EmpNum and
> ls.Clock = k.Clock and
> ls.EmpNu9 = k.EmpNu9 and
> ls.ActIn = k.ActIn
>
> Thanks
> Eric
>
>|||Thanks for the suggestion MG. However, I don't think that applies here. The
table on the AS400 is continually receiving data. I read data from that
table into a SQL temp table and process the records in the temp table.
Meantime, more records are flowing into the AS400 table. The temp table
serves as my means of knowing which records can be deleted from the AS400
table.
Eric
"MGeles" <michael.geles@.thomson.com> wrote in message
news:34A0DE2D-FE23-4484-918C-F32E52A09DA6@.microsoft.com...
> Hi Eric,
> This might not apply to you situation, but one thing that you may consider
> is copying the record that you want to keep into a seperate temp table,
then
> truncate the original table, and finally copy the records from the temp
table
> back to the original.
> This would be useful if the number of records that you want to keep is
> dramatically less than the number of records that you want to delete.
> --
> MG
>
> "Eric Stewart" wrote:
> > I am running this query on MS SQL 2000 that is deleting records on an
AS400
> > table. Extremely slow. Is there a different way I should/could do this?
> >
> > \
> >
> > Background - Data comes into COMMON. I read the data into #LoadSwipes,
> > process the data, and then want to delete the processed records from
COMMON.
> >
> > delete KRONOS.RSIGL.TIMECUSTOM.COMMON
> > from #LoadSwipes ls
> > inner join KRONOS.RSIGL.TIMECUSTOM.COMMON k
> > on ls.Area = k.Area and
> > ls.TimeSt = k.TimeSt and
> > ls.EmpNum = k.EmpNum and
> > ls.Clock = k.Clock and
> > ls.EmpNu9 = k.EmpNu9 and
> > ls.ActIn = k.ActIn
> >
> >
> > Thanks
> > Eric
> >
> >
> >

Is there a better (faster) way?

I am running this query on MS SQL 2000 that is deleting records on an AS400
table. Extremely slow. Is there a different way I should/could do this?
\
Background - Data comes into COMMON. I read the data into #LoadSwipes,
process the data, and then want to delete the processed records from COMMON.
delete KRONOS.RSIGL.TIMECUSTOM.COMMON
from #LoadSwipes ls
inner join KRONOS.RSIGL.TIMECUSTOM.COMMON k
on ls.Area = k.Area and
ls.TimeSt = k.TimeSt and
ls.EmpNum = k.EmpNum and
ls.Clock = k.Clock and
ls.EmpNu9 = k.EmpNu9 and
ls.ActIn = k.ActIn
Thanks
Eric
Hi Eric,
This might not apply to you situation, but one thing that you may consider
is copying the record that you want to keep into a seperate temp table, then
truncate the original table, and finally copy the records from the temp table
back to the original.
This would be useful if the number of records that you want to keep is
dramatically less than the number of records that you want to delete.
MG
"Eric Stewart" wrote:

> I am running this query on MS SQL 2000 that is deleting records on an AS400
> table. Extremely slow. Is there a different way I should/could do this?
> \
> Background - Data comes into COMMON. I read the data into #LoadSwipes,
> process the data, and then want to delete the processed records from COMMON.
> delete KRONOS.RSIGL.TIMECUSTOM.COMMON
> from #LoadSwipes ls
> inner join KRONOS.RSIGL.TIMECUSTOM.COMMON k
> on ls.Area = k.Area and
> ls.TimeSt = k.TimeSt and
> ls.EmpNum = k.EmpNum and
> ls.Clock = k.Clock and
> ls.EmpNu9 = k.EmpNu9 and
> ls.ActIn = k.ActIn
>
> Thanks
> Eric
>
>
|||Thanks for the suggestion MG. However, I don't think that applies here. The
table on the AS400 is continually receiving data. I read data from that
table into a SQL temp table and process the records in the temp table.
Meantime, more records are flowing into the AS400 table. The temp table
serves as my means of knowing which records can be deleted from the AS400
table.
Eric
"MGeles" <michael.geles@.thomson.com> wrote in message
news:34A0DE2D-FE23-4484-918C-F32E52A09DA6@.microsoft.com...
> Hi Eric,
> This might not apply to you situation, but one thing that you may consider
> is copying the record that you want to keep into a seperate temp table,
then
> truncate the original table, and finally copy the records from the temp
table[vbcol=seagreen]
> back to the original.
> This would be useful if the number of records that you want to keep is
> dramatically less than the number of records that you want to delete.
> --
> MG
>
> "Eric Stewart" wrote:
AS400[vbcol=seagreen]
COMMON.[vbcol=seagreen]

Is there a better (faster) way?

I am running this query on MS SQL 2000 that is deleting records on an AS400
table. Extremely slow. Is there a different way I should/could do this?
\
Background - Data comes into COMMON. I read the data into #LoadSwipes,
process the data, and then want to delete the processed records from COMMON.
delete KRONOS.RSIGL.TIMECUSTOM.COMMON
from #LoadSwipes ls
inner join KRONOS.RSIGL.TIMECUSTOM.COMMON k
on ls.Area = k.Area and
ls.TimeSt = k.TimeSt and
ls.EmpNum = k.EmpNum and
ls.Clock = k.Clock and
ls.EmpNu9 = k.EmpNu9 and
ls.ActIn = k.ActIn
Thanks
EricHi Eric,
This might not apply to you situation, but one thing that you may consider
is copying the record that you want to keep into a seperate temp table, then
truncate the original table, and finally copy the records from the temp tabl
e
back to the original.
This would be useful if the number of records that you want to keep is
dramatically less than the number of records that you want to delete.
--
MG
"Eric Stewart" wrote:

> I am running this query on MS SQL 2000 that is deleting records on an AS40
0
> table. Extremely slow. Is there a different way I should/could do this?
> \
> Background - Data comes into COMMON. I read the data into #LoadSwipes,
> process the data, and then want to delete the processed records from COMMO
N.
> delete KRONOS.RSIGL.TIMECUSTOM.COMMON
> from #LoadSwipes ls
> inner join KRONOS.RSIGL.TIMECUSTOM.COMMON k
> on ls.Area = k.Area and
> ls.TimeSt = k.TimeSt and
> ls.EmpNum = k.EmpNum and
> ls.Clock = k.Clock and
> ls.EmpNu9 = k.EmpNu9 and
> ls.ActIn = k.ActIn
>
> Thanks
> Eric
>
>|||Thanks for the suggestion MG. However, I don't think that applies here. The
table on the AS400 is continually receiving data. I read data from that
table into a SQL temp table and process the records in the temp table.
Meantime, more records are flowing into the AS400 table. The temp table
serves as my means of knowing which records can be deleted from the AS400
table.
Eric
"MGeles" <michael.geles@.thomson.com> wrote in message
news:34A0DE2D-FE23-4484-918C-F32E52A09DA6@.microsoft.com...
> Hi Eric,
> This might not apply to you situation, but one thing that you may consider
> is copying the record that you want to keep into a seperate temp table,
then
> truncate the original table, and finally copy the records from the temp
table[vbcol=seagreen]
> back to the original.
> This would be useful if the number of records that you want to keep is
> dramatically less than the number of records that you want to delete.
> --
> MG
>
> "Eric Stewart" wrote:
>
AS400[vbcol=seagreen]
COMMON.[vbcol=seagreen]

Is the SQL Server Service Running?

Can Enterprise Manager tell us *definitively* if the SQL Server service is
running/started on a particular SQL Server (where the SQL Server is not the
local machine on which EM is running)?
What is the most reliable way to determine if the SQL Server service is in
fact up and running on any SQL server (assuming local admin access to the
server is possible)?
Thanks!Sometimes MMC answer slowly in such cases and you was thinking that
everything goes fine but you was wrong. I don't know which is the best way
for that,open the QA and try do 'use db'...
"Guadala Harry" wrote:

> Can Enterprise Manager tell us *definitively* if the SQL Server service is
> running/started on a particular SQL Server (where the SQL Server is not th
e
> local machine on which EM is running)?
> What is the most reliable way to determine if the SQL Server service is in
> fact up and running on any SQL server (assuming local admin access to the
> server is possible)?
> Thanks!
>
>|||Guadala,
1. Enterprise Manager
2. SQL Server Service Manager
3. Admin Tools --> Services --> connect to another machine
4. NET
5. SQL-DMO
6. WMI
7. WSH
HTH
Jerry
"Guadala Harry" <GMan@.BeansAndTacos.org> wrote in message
news:Odxvj5Y1FHA.464@.TK2MSFTNGP15.phx.gbl...
> Can Enterprise Manager tell us *definitively* if the SQL Server service is
> running/started on a particular SQL Server (where the SQL Server is not
> the local machine on which EM is running)?
> What is the most reliable way to determine if the SQL Server service is in
> fact up and running on any SQL server (assuming local admin access to the
> server is possible)?
> Thanks!
>|||or using terminal services or VNC program
"Jerry Spivey" wrote:

> Guadala,
> 1. Enterprise Manager
> 2. SQL Server Service Manager
> 3. Admin Tools --> Services --> connect to another machine
> 4. NET
> 5. SQL-DMO
> 6. WMI
> 7. WSH
> HTH
> Jerry
> "Guadala Harry" <GMan@.BeansAndTacos.org> wrote in message
> news:Odxvj5Y1FHA.464@.TK2MSFTNGP15.phx.gbl...
>
>|||Thanks Jerry and enric... But I was hoping to get your perspective on how we
can know *for sure* (thus my hilighting of *definitively*) in the OP!
Let me put it another way. Suppose someone put a loaded gun to your head and
said they'd pull the trigger if you answered this question incorrectly: "Is
the SQL Server service running/started on our SQL Server?"
In that situation, which of the 7 methods you listed would you use?
Remember, you'd get shot in the head if the method you chose told you the
wrong answer.
- I hope this clarifies what I'm after!
Thanks!
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:eyRm1%23Y1FHA.3720@.TK2MSFTNGP14.phx.gbl...
> Guadala,
> 1. Enterprise Manager
> 2. SQL Server Service Manager
> 3. Admin Tools --> Services --> connect to another machine
> 4. NET
> 5. SQL-DMO
> 6. WMI
> 7. WSH
> HTH
> Jerry
> "Guadala Harry" <GMan@.BeansAndTacos.org> wrote in message
> news:Odxvj5Y1FHA.464@.TK2MSFTNGP15.phx.gbl...
>|||It's ok. Very clear...
One solution would be the following:
-Open a DOS session
-Launch NET START statement
-Retrieves the list
-Look for 'MSSQLSERVER'
If appears, it's running.
Of course, these above steps could be done from a stored procedure or a
snippet of VB code, WMI, etc...
Regards,
Enric
"Guadala Harry" wrote:

> Thanks Jerry and enric... But I was hoping to get your perspective on how
we
> can know *for sure* (thus my hilighting of *definitively*) in the OP!
> Let me put it another way. Suppose someone put a loaded gun to your head a
nd
> said they'd pull the trigger if you answered this question incorrectly: "I
s
> the SQL Server service running/started on our SQL Server?"
> In that situation, which of the 7 methods you listed would you use?
> Remember, you'd get shot in the head if the method you chose told you the
> wrong answer.
> - I hope this clarifies what I'm after!
> Thanks!
>
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eyRm1%23Y1FHA.3720@.TK2MSFTNGP14.phx.gbl...
>
>|||Create a VBScript which makes an ADODB connection to the server.
Send an execute "SELECT 1 AS Test" and return the results to the recordset.
If you get
Test
1
Then your server is up.
Here's a sample script, obviously there is no error handling and you can do
all the nice funky COM things if you get an error or when it works (emails,
fileI/O etc)
Dim cnn
Dim rs
Dim sServerName
Dim sDatabase
sServerName = "MyServer"
sDatabase = "Master"
Set cnn = CreateObject("ADODB.Connection")
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security Info=False;Initial Catalog=" & sDatabase & ";Data Source=" &
sServerName
set rs = cnn.Execute( "SELECT 1 AS TEST" )
msgbox rs.fields("Test")
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
"Guadala Harry" <GMan@.BeansAndTacos.org> wrote in message
news:uQYDRTZ1FHA.1040@.TK2MSFTNGP14.phx.gbl...
> Thanks Jerry and enric... But I was hoping to get your perspective on how
we
> can know *for sure* (thus my hilighting of *definitively*) in the OP!
> Let me put it another way. Suppose someone put a loaded gun to your head
and
> said they'd pull the trigger if you answered this question incorrectly:
"Is
> the SQL Server service running/started on our SQL Server?"
> In that situation, which of the 7 methods you listed would you use?
> Remember, you'd get shot in the head if the method you chose told you the
> wrong answer.
> - I hope this clarifies what I'm after!
> Thanks!
>
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:eyRm1%23Y1FHA.3720@.TK2MSFTNGP14.phx.gbl...
to
>|||Your test could easily yield "false negatives". The ADODB connection could
fail for any number of reasons (network card is dead, network cable
unplugged, etc), AND the SQL Server service could be in fact running on your
server. So, in the "gun to your head" scenario, if the ADODB connection
failed, you'd say "geeze - guess the SQL Server servics isn't
started/running" when in fact it could be.
I'd hate to see what happens next : )
Still looking for a method to get the definitive answer to the question: Is
the SQL Server service running/started on our SQL Server?"
Please note that I'm not asking if the SQL Server is available on the
network. I'm asking about the SQL Server service, itself.
Thanks!
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4357c486$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
> Create a VBScript which makes an ADODB connection to the server.
> Send an execute "SELECT 1 AS Test" and return the results to the
> recordset.
> If you get
> Test
>
> 1
> Then your server is up.
> Here's a sample script, obviously there is no error handling and you can
> do
> all the nice funky COM things if you get an error or when it works
> (emails,
> fileI/O etc)
>
> Dim cnn
> Dim rs
> Dim sServerName
> Dim sDatabase
> sServerName = "MyServer"
> sDatabase = "Master"
> Set cnn = CreateObject("ADODB.Connection")
> cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist
> Security Info=False;Initial Catalog=" & sDatabase & ";Data Source=" &
> sServerName
> set rs = cnn.Execute( "SELECT 1 AS TEST" )
> msgbox rs.fields("Test")
> rs.Close
> cnn.Close
> Set rs = Nothing
> Set cnn = Nothing
>
> "Guadala Harry" <GMan@.BeansAndTacos.org> wrote in message
> news:uQYDRTZ1FHA.1040@.TK2MSFTNGP14.phx.gbl...
> we
> and
> "Is
> to
>|||You pointed out one of the methods I didn't think about.
Thanks!
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:DB62D4E5-2588-4FDF-9D47-0FF3DEDABE74@.microsoft.com...
> It's ok. Very clear...
> One solution would be the following:
> -Open a DOS session
> -Launch NET START statement
> -Retrieves the list
> -Look for 'MSSQLSERVER'
> If appears, it's running.
> Of course, these above steps could be done from a stored procedure or a
> snippet of VB code, WMI, etc...
> Regards,
> Enric
> "Guadala Harry" wrote:
>|||yes,
-Open a DOS session
-Launch NET START statement
-Retrieves the list
-Look for 'MSSQLSERVER'
"Guadala Harry" wrote:

> Your test could easily yield "false negatives". The ADODB connection could
> fail for any number of reasons (network card is dead, network cable
> unplugged, etc), AND the SQL Server service could be in fact running on yo
ur
> server. So, in the "gun to your head" scenario, if the ADODB connection
> failed, you'd say "geeze - guess the SQL Server servics isn't
> started/running" when in fact it could be.
> I'd hate to see what happens next : )
> Still looking for a method to get the definitive answer to the question: I
s
> the SQL Server service running/started on our SQL Server?"
> Please note that I'm not asking if the SQL Server is available on the
> network. I'm asking about the SQL Server service, itself.
> Thanks!
>
> "Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
> news:4357c486$0$135$7b0f0fd3@.mistral.news.newnet.co.uk...
>
>

Is subscription running?

Hi,
I'm Roby Eisenbraun Martins, I'm a C++, VB, C# and ASP .NET developer.
I would like to know if is possible to identify if a subscription is
being synchronized from the subscriber's database, without accessing the
publisher's database.
Thank you,
Roby Eisenbraun Martins
Roby,
if it's a pull subscription, you could check to see the job status. This
script is similar to what you could use:
http://www.replicationanswers.com/Do...unningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Is Stored Procedure and 'in' broken in MSDE 2000/SQL 2000 SP4?

This is not the command that im running but demonstrates the problem just
fine, basically 'in' using statored procedures seems to be performing as 'in'
or 'is' where as sending a query direct (identical) only performs 'in' as
expected
Example
Use msdb
Select * From Sysalerts Where database_name in (Select
convert(sysname(128),null))
returns nothing
but put the select command in a stored proceedure and it will return all the
rows with database_name that is null
Am i not seeing/setting something?
Simon
On Tue, 21 Jun 2005 08:36:05 -0700, "Tuner Fich" <Tuner
Fich@.discussions.microsoft.com> wrote:

>This is not the command that im running but demonstrates the problem just
>fine, basically 'in' using statored procedures seems to be performing as 'in'
>or 'is' where as sending a query direct (identical) only performs 'in' as
>expected
>Example
>Use msdb
>Select * From Sysalerts Where database_name in (Select
>convert(sysname(128),null))
>returns nothing
>but put the select command in a stored proceedure and it will return all the
>rows with database_name that is null
>Am i not seeing/setting something?
>Simon
Hi Simon,
I guess that you normally have the setting SET ANSI_NULLS ON (which is
fine, as it makes SQL Server treat NULLS as defined in the ANSI
standard, making your code more portable). However, when creating stored
procedure, someow the setting gets changed to SET ANSI_NULLS OFF (which
is definitely NOT fine, as it makes SQL Server treat NULLS in a
non-standard way that might appear logical at first glance but is not,
and that will make other database programmers fail to understand your
code).
With the ANSI standard ebhaviour for NULLS, logical expressions use
three-valued logic (True, False and Unknown) and all comparisons to NULL
will always return Unknown. The only valid way to copmpare a column or
variable to NULL is to use "WHERE column IS [NOT] NULL".
Also, stop using [NOT] IN with a subselect, as they are a source of
confusion with ANSI standard settings, and they can always be
transformed into a [NOT] EXISTS subquery that usually performs better as
well.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Is SqlServer Agent running?

Hi,

Can anyone give me some clues as to how I programmatically determine if Sql Server Agent is running. I'm using Sql 2005 and c#.

I have found the JobServer property in the SqlServer object but this still doesn't tell me is the service is running!!

Thanks for your help

Graham

Take a look at Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer. This class has a ServiceCollection property which represents all SQL Server services on a target machine. Find your service and check it's state.

WBR, Evergray -- Words mean nothing...|||

this is really a useful class. but do u know how to obtain the machine name from SQL server name?

cos in my application, the user just provoide server name. how can I get the machine name to configure the services on the machine?

|||

Server name is usually machine name (default instance) or machine_name\instance_name for named instances of SQL Server, so it's not a problem.

But anyway you can determine machine name using host_name() system function, if you're already connected to server.

WBR, Evergray -- Words mean nothing...|||

yup. thanks for the reply.

I found that I can use SMO to find it machine name as well.

|||how do you determine the machine name using objects in the SMO namespace?

Is SqlServer Agent running?

Hi,

Can anyone give me some clues as to how I programmatically determine if Sql Server Agent is running. I'm using Sql 2005 and c#.

I have found the JobServer property in the SqlServer object but this still doesn't tell me is the service is running!!

Thanks for your help

Graham

Take a look at Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer. This class has a ServiceCollection property which represents all SQL Server services on a target machine. Find your service and check it's state.

WBR, Evergray

--

Words mean nothing...|||

this is really a useful class. but do u know how to obtain the machine name from SQL server name?

cos in my application, the user just provoide server name. how can I get the machine name to configure the services on the machine?

|||

Server name is usually machine name (default instance) or machine_name\instance_name for named instances of SQL Server, so it's not a problem.

But anyway you can determine machine name using host_name() system function, if you're already connected to server.

WBR, Evergray

--

Words mean nothing...|||

yup. thanks for the reply.

I found that I can use SMO to find it machine name as well.

|||how do you determine the machine name using objects in the SMO namespace?

Is SqlServer Agent running?

Hi,

Can anyone give me some clues as to how I programmatically determine if Sql Server Agent is running. I'm using Sql 2005 and c#.

I have found the JobServer property in the SqlServer object but this still doesn't tell me is the service is running!!

Thanks for your help

Graham

Take a look at Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer. This class has a ServiceCollection property which represents all SQL Server services on a target machine. Find your service and check it's state.

WBR, Evergray -- Words mean nothing...|||

this is really a useful class. but do u know how to obtain the machine name from SQL server name?

cos in my application, the user just provoide server name. how can I get the machine name to configure the services on the machine?

|||

Server name is usually machine name (default instance) or machine_name\instance_name for named instances of SQL Server, so it's not a problem.

But anyway you can determine machine name using host_name() system function, if you're already connected to server.

WBR, Evergray -- Words mean nothing...|||

yup. thanks for the reply.

I found that I can use SMO to find it machine name as well.

|||how do you determine the machine name using objects in the SMO namespace?

Is SQL Server Beta 2 64 bit?

I have the SQL Server 2005 Beta 2 and would like to
install on an HP 585 AMD 64 bit server running Win2003 64
bit AMD(beta). Will the CD I have give me the 64 bit?
Thanks in advance.
Joe
Hi
SQL 2005 Beta 2 supports 32 bit plus AMD-64 and IA-64.
Just make sure you use the correct CD as each edition is on a separate one
and read the associated setup instructions about limitations.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"joeK" <joeK@.discussions.microsoft.com> wrote in message
news:6623A9A5-A326-4EE9-9F46-900257348F95@.microsoft.com...
> I have the SQL Server 2005 Beta 2 and would like to
> install on an HP 585 AMD 64 bit server running Win2003 64
> bit AMD(beta). Will the CD I have give me the 64 bit?
> Thanks in advance.
> Joe
>

Is SQL Server Beta 2 64 bit?

I have the SQL Server 2005 Beta 2 and would like to
install on an HP 585 AMD 64 bit server running Win2003 64
bit AMD(beta). Will the CD I have give me the 64 bit?
Thanks in advance.
JoeHi
SQL 2005 Beta 2 supports 32 bit plus AMD-64 and IA-64.
Just make sure you use the correct CD as each edition is on a separate one
and read the associated setup instructions about limitations.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"joeK" <joeK@.discussions.microsoft.com> wrote in message
news:6623A9A5-A326-4EE9-9F46-900257348F95@.microsoft.com...
> I have the SQL Server 2005 Beta 2 and would like to
> install on an HP 585 AMD 64 bit server running Win2003 64
> bit AMD(beta). Will the CD I have give me the 64 bit?
> Thanks in advance.
> Joe
>

Is SQL Server Beta 2 64 bit?

I have the SQL Server 2005 Beta 2 and would like to
install on an HP 585 AMD 64 bit server running Win2003 64
bit AMD(beta). Will the CD I have give me the 64 bit?
Thanks in advance.
JoeHi
SQL 2005 Beta 2 supports 32 bit plus AMD-64 and IA-64.
Just make sure you use the correct CD as each edition is on a separate one
and read the associated setup instructions about limitations.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"joeK" <joeK@.discussions.microsoft.com> wrote in message
news:6623A9A5-A326-4EE9-9F46-900257348F95@.microsoft.com...
> I have the SQL Server 2005 Beta 2 and would like to
> install on an HP 585 AMD 64 bit server running Win2003 64
> bit AMD(beta). Will the CD I have give me the 64 bit?
> Thanks in advance.
> Joe
>

Is running SQL on VMWare supported by MS ?

I know virtualization helps when ones cutting costs so that multiple
dev/test environments can be hosted on one single server
But is it supported when used in production ? Will MS support it ?I would think that poor performance would be enough to deter anyone
from using VMWare or VirtualPC for anything but demos or dev
environments. From what I remember, MS will support the individual
products, but not the system as a whole - not much different from their
regular support IMO.
To really run well made virtual environment, you'll spend as much as
you would on separete servers and get about 1/3 of the performance.
Hassan wrote:
> I know virtualization helps when ones cutting costs so that multiple
> dev/test environments can be hosted on one single server
> But is it supported when used in production ? Will MS support it ?|||Hassan wrote:
> I know virtualization helps when ones cutting costs so that multiple
> dev/test environments can be hosted on one single server
> But is it supported when used in production ? Will MS support it ?
Yes. Your virtual servers must be properly licensed of course. See:
http://www.microsoft.com/sql/howtob...ualization.mspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
Fully supported by Microsoft
:
VMWare, No.
Microsoft Virtual PC and Virtual Server, Yes.
http://www.support.microsoft.com/kb/897615/
http://www.support.microsoft.com/kb/897613
http://www.support.microsoft.com/kb/897614
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1149512310.263776.52780@.f6g2000cwb.googlegroups.com...
> Hassan wrote:
> Yes. Your virtual servers must be properly licensed of course. See:
> http://www.microsoft.com/sql/howtob...ualization.mspx
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Michael Epprecht [MSFT] wrote:
> Hi
> Fully supported by Microsoft
> :
> VMWare, No.
> Microsoft Virtual PC and Virtual Server, Yes.
> http://www.support.microsoft.com/kb/897615/
> http://www.support.microsoft.com/kb/897613
> http://www.support.microsoft.com/kb/897614
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
Thanks for the clarification. I hadn't seen that before.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Is running SQL on VMWare supported by MS ?

I know virtualization helps when ones cutting costs so that multiple
dev/test environments can be hosted on one single server
But is it supported when used in production ? Will MS support it ?I would think that poor performance would be enough to deter anyone
from using VMWare or VirtualPC for anything but demos or dev
environments. From what I remember, MS will support the individual
products, but not the system as a whole - not much different from their
regular support IMO.
To really run well made virtual environment, you'll spend as much as
you would on separete servers and get about 1/3 of the performance.
Hassan wrote:
> I know virtualization helps when ones cutting costs so that multiple
> dev/test environments can be hosted on one single server
> But is it supported when used in production ? Will MS support it ?|||Hassan wrote:
> I know virtualization helps when ones cutting costs so that multiple
> dev/test environments can be hosted on one single server
> But is it supported when used in production ? Will MS support it ?
Yes. Your virtual servers must be properly licensed of course. See:
http://www.microsoft.com/sql/howtobuy/virtualization.mspx
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
Fully supported by Microsoft
:
VMWare, No.
Microsoft Virtual PC and Virtual Server, Yes.
http://www.support.microsoft.com/kb/897615/
http://www.support.microsoft.com/kb/897613
http://www.support.microsoft.com/kb/897614
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1149512310.263776.52780@.f6g2000cwb.googlegroups.com...
> Hassan wrote:
>> I know virtualization helps when ones cutting costs so that multiple
>> dev/test environments can be hosted on one single server
>> But is it supported when used in production ? Will MS support it ?
> Yes. Your virtual servers must be properly licensed of course. See:
> http://www.microsoft.com/sql/howtobuy/virtualization.mspx
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Michael Epprecht [MSFT] wrote:
> Hi
> Fully supported by Microsoft
> :
> VMWare, No.
> Microsoft Virtual PC and Virtual Server, Yes.
> http://www.support.microsoft.com/kb/897615/
> http://www.support.microsoft.com/kb/897613
> http://www.support.microsoft.com/kb/897614
> --
> Mike
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
Thanks for the clarification. I hadn't seen that before.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Is restore or Detach/Attach better?

Hello, everyone:

I want to move a database instance (about 50 user databases) to another server, both are running SQL Server 2000. Which method is better, retore from backup files or detach/attach? Some papers said restore amybe cause incorrect login and password transfer. Is it true?

Thanks

ZYTAll databases store the SID (Security Identifier) of the logins that have permissions on that database. When you restore or attach a database to a new instance, these users may become orphaned, if there is no login with the corresponding SID. When you create the SQL Authenticated logins on the new server, make sure you specify the SID that they should have. Windows logins have their SIDs specified by the Domain Controllers, and need not be specified in the create login statement.|||MCrowley:

Thanks for the reply. Can DTS transfer login with SID? What I am concerning is to transfer login from old server to new one.

Thanks

ZY

All databases store the SID (Security Identifier) of the logins that have permissions on that database. When you restore or attach a database to a new instance, these users may become orphaned, if there is no login with the corresponding SID. When you create the SQL Authenticated logins on the new server, make sure you specify the SID that they should have. Windows logins have their SIDs specified by the Domain Controllers, and need not be specified in the create login statement.|||Google "sp_help_revlogin" for some help to transfer sql accounts|||when the db is restored run (from the restored db)

If you do not bring logins over and create them manually

-- to view broken users
sp_change_users_login 'report' -- will give you list of "broken" users.

you then run sp_change_users_login auto_fix, 'username' for each user:

I just use this little sql script after restoring a db (make db current):

set nocount on
declare @.v_dbuser varchar(255)
declare @.sql nvarchar(255)
declare c1 cursor for
select a.name from sysusers a, master..syslogins b
where a.name = b.name
open c1
fetch c1 into @.v_dbuser
while (@.@.FETCH_STATUS <> -1)
BEGIN
print 'Fixing User ' + @.v_dbuser
set @.sql = 'sp_change_users_login ''auto_fix'',' + @.v_dbuser
exec sp_executesql @.sql
fetch c1 into @.v_dbuser
END
CLOSE C1
DEALLOCATE C1|||Nice little script! I have always done that manually...now next time we create or sync a dev database, I'll whip out your little script and be the instant envy of my peers!

Yeah, I know...I need a new set of peers.

One could probably also just add a "check every database" loop outside of this one and handle all the databases on a new server at once...though I suppose that is a pretty infrequent occurrence and probably not a justifiable expenditure of effort relative to the creation of a script to do it.

Is Replication the solution?

We have serveral office of which have there own SQL Databases running in
MSDE and SQL Server Enterprise running at the Corp HQ. At HQ there are
tables for say Office 1 (Ofc1) in a central database. The central database
has a shema for each office in the central database ie ofc1.tbl_contacts,
ofc2.tbl_contacts, etc... Not all of the fields residing on the office
servers will be replacated to the HQ central db. But
updates/additions/deletions need to be synched at least on a daily basis.
I have already writting some of my own jobs to handle this with stored
procedures. I have createdDate, modifiedDate(updated via trigger) fields
that are used to determine what data needs to be transferred. I
I was wondering if SQL Replication Services would be a help in this area?
Is there some other method that would be better suited for this task, or is
coding it by hand (which I already started) pretty much the better solution?
Thanks for the help,
--Micah
Replication is ideal for something like this. You don't really mention is
data is going both ways or only to the branch offices.
If it is one way you should be using transactional replication with filters;
if it is bi-directional you should be using merge replication.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Micah Miller" <micah.miller@.privacy.me> wrote in message
news:OQyEUPXtEHA.3884@.TK2MSFTNGP11.phx.gbl...
> We have serveral office of which have there own SQL Databases running in
> MSDE and SQL Server Enterprise running at the Corp HQ. At HQ there are
> tables for say Office 1 (Ofc1) in a central database. The central
database
> has a shema for each office in the central database ie ofc1.tbl_contacts,
> ofc2.tbl_contacts, etc... Not all of the fields residing on the office
> servers will be replacated to the HQ central db. But
> updates/additions/deletions need to be synched at least on a daily basis.
> I have already writting some of my own jobs to handle this with stored
> procedures. I have createdDate, modifiedDate(updated via trigger) fields
> that are used to determine what data needs to be transferred. I
> I was wondering if SQL Replication Services would be a help in this area?
> Is there some other method that would be better suited for this task, or
is
> coding it by hand (which I already started) pretty much the better
solution?
> Thanks for the help,
> --Micah
>
>