Problem returning a datarow

Hi,

I have a client/server app. that uses a windows service for the server and asp.net web pages for the client side. My server class has 3 methods that Fill, Add a new record and Update a record. The Fill and Add routines work as expected but unfortunately the update request falls at the 1st hurdle.

I pass two params to the remote(server) method for the update, one is the unique ID and the other is a string that is the name of the table in the database. See code below. I need the SelectedRow method to return a datarow that will then populate textbox's on another page. When the method is called I get an 'internal system error....please turn on custom errors in the web.config file on the server for more info.(unfortunately my server is not s web server so I don't have a web.config file!!).

Can anyone see anything obvious.

Cheers. >
Calling routine:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

System.Threading.Thread.CurrentThread.CurrentCultu re = New CultureInfo("en-GB")

hsc = CType(Activator.GetObject(GetType(IHelpSC), _
"tcp://192.168.2.3:1234/HelpSC"), IHelpSC)

Dim drEdit As DataRow
Dim intRow As Integer = CInt(Request.QueryString("item"))

strDiscipline = Request.QueryString("discipline")
drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote method
strRecord = drEdit.Item(0)
txtLogged.Text = drEdit(1)
txtEngineer.Text = drEdit.Item(3)

End Sub

Remote Class Function:

Public Function SelectedRow(ByVal id As Integer, ByVal discipline As String) As System.Data.DataRow Implements IHelpSC.SelectedRow

strDiscipline = Trim(discipline)
Dim cmdSelect As SqlCommand = sqlcnn.CreateCommand
Dim drResult As DataRow
Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
" WHERE CallID=" & id

cmdSelect.CommandType = CommandType.Text
cmdSelect.CommandText = strQuery

sqlda = New SqlDataAdapter
sqlda.SelectCommand = cmdSelect

ds = New DataSet
sqlda.Fill(ds, "Results")
drResult = ds.Tables(0).Rows(0)

Return drResult

End FunctionPhil (Phil@.nospam.com) writes:
> I have a client/server app. that uses a windows service for the server
> and asp.net web pages for the client side. My server class has 3 methods
> that Fill, Add a new record and Update a record. The Fill and Add
> routines work as expected but unfortunately the update request falls at
> the 1st hurdle.
> I pass two params to the remote(server) method for the update, one is
> the unique ID and the other is a string that is the name of the table in
> the database. See code below. I need the SelectedRow method to return a
> datarow that will then populate textbox's on another page. When the
> method is called I get an 'internal system error....please turn on
> custom errors in the web.config file on the server for more
> info.(unfortunately my server is not s web server so I don't have a
> web.config file!!).

I don't really have an idea, but the error message does not look
like it comes from SQL Server. Maybe you should try an ADO .Net group.

>Dim intRow As Integer = CInt(Request.QueryString("item"))
>strDiscipline = Request.QueryString("discipline")
>drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote method
> Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
> " WHERE CallID=" & id

I don't know what this Request.QueryString implies, but this is any
sorr of user input, you have a major hole here. What if the user
specifies a table that does not exist? What if he specifies
"tbl; DROP DATABASE important; --"? This is called SQL injection,
and is a popular way for intruders to get access to things they should
have access to.

I don't know why you pass the table name as a parameter, but it's
not likely to be good design. For the CallID you should in any case
use a parameter:

Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
" WHERE CallID=@.id"
cmdSelect.AddParameter(@.id, SqlInt, Id)

(With all reservations for the exact syntax.) Parameterizing your
SQL statements protects you from SQL injection.

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

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

Thanks for your response. Although we haven't found my problem I will just
comment on your response FWIW :_)

The QueryString property of the HTTPRequest class adds two, lets call them
parameters are passed from the calling page. These params are 'hard-coded'
items in a dropdownlist and selected row from a datagrid. So, I utterly
agree with your concerns regarding SQL injection but 'hopefully' in this
instance I'm ok...!!! The other two method calls to the database do in fact
use parameterised stored procedures (if that absolves me in any way :-).

My problem/puzzlement is that if I run the client app. with the data layer
class (with no changes, ie. still accesses the remote server), it works
perfectly. Just to clarify.....the class with the data layer (ie.
interfacing directly with the dB via direct sql calls or parameterised
stored procs) normally resides on the server and the client communicates
with this class using .NET remoting. Just to remember, I have 3 methods. The
Fill method is called when the client page is 1st loaded and populates a
datagrid...this works. I also have a button on the same page as the datagrid
that calls the AddNew method to add a new record to the db, this also works
fine. Finally, the datagrid has a button column that is for edit/update of
the selected record. This is where I receive the error BUT.........it
works if I 'move' the data layer class to the client side and call the
method ...GGGrrrr...it's very frustrating!!

Thanks for your help.

Phil

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns964E55D31DC2Yazorman@.127.0.0.1...
> Phil (Phil@.nospam.com) writes:
>> I have a client/server app. that uses a windows service for the server
>> and asp.net web pages for the client side. My server class has 3 methods
>> that Fill, Add a new record and Update a record. The Fill and Add
>> routines work as expected but unfortunately the update request falls at
>> the 1st hurdle.
>>
>> I pass two params to the remote(server) method for the update, one is
>> the unique ID and the other is a string that is the name of the table in
>> the database. See code below. I need the SelectedRow method to return a
>> datarow that will then populate textbox's on another page. When the
>> method is called I get an 'internal system error....please turn on
>> custom errors in the web.config file on the server for more
>> info.(unfortunately my server is not s web server so I don't have a
>> web.config file!!).
> I don't really have an idea, but the error message does not look
> like it comes from SQL Server. Maybe you should try an ADO .Net group.
>>Dim intRow As Integer = CInt(Request.QueryString("item"))
>>
>>strDiscipline = Request.QueryString("discipline")
>>drEdit = hsc.SelectedRow(intRow, strDiscipline) <<Call the remote method
>>
>> Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
>> " WHERE CallID=" & id
> I don't know what this Request.QueryString implies, but this is any
> sorr of user input, you have a major hole here. What if the user
> specifies a table that does not exist? What if he specifies
> "tbl; DROP DATABASE important; --"? This is called SQL injection,
> and is a popular way for intruders to get access to things they should
> have access to.
> I don't know why you pass the table name as a parameter, but it's
> not likely to be good design. For the CallID you should in any case
> use a parameter:
> Dim strQuery As String = "SELECT * FROM " & strDiscipline & _
> " WHERE CallID=@.id"
> cmdSelect.AddParameter(@.id, SqlInt, Id)
> (With all reservations for the exact syntax.) Parameterizing your
> SQL statements protects you from SQL injection.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Phil (Phil@.nospam.com) writes:
> The QueryString property of the HTTPRequest class adds two, lets call
> them parameters are passed from the calling page. These params are
> 'hard-coded' items in a dropdownlist and selected row from a datagrid.
> So, I utterly agree with your concerns regarding SQL injection but
> 'hopefully' in this instance I'm ok...!!!

It it was a Windows Forms client, it would be safe I guess. But you
have a web client, right? Somehow the information on what the user
select must be passed over the network. The obvious case is when the
parameter appears in a URL. But anything which is over a network port
over which an intruder has full control of his end could be susceptible.

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

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

Problem report xp_MSADEnabled

Hello,
I have detected a problem with the stored procedure xp_MSADEnabled. I am using Windows 2000 (workstation, 5.00.2195, SP4) and SQLServer 2000 Developer (Product version 8.00.858 SP3). Calling xp_MSADEnabled results in an unspecified error (-2147467259).
If I call the same procedure on a Windows 2003 Server machine, it works fine.
This procedure is called in several other system procedures like sp_addmergepublication and sp_dropmergepublication.
I am developing the replication functionality of a database application. I discovered the problem when I wanted to call sp_addmergepublication from my ADO.NET application. This resulted in the SqlException "A severe error occurred on the current command.
The results, if any, should be discarded". Calling this procedure from the SQL Query Analyzer did not result in the error.
greeting, Marco
Hi
Looking at other people having problems with this xp_MSADEnabled tend to
point to service account problems such as duplicate account entries in the
AD or insufficient permissions to access it.
John
"Marco Broenink" <marco.broenink@.ict.nl> wrote in message
news:0EB1A879-F364-4646-AE5B-28C3754194AD@.microsoft.com...
> Hello,
> I have detected a problem with the stored procedure xp_MSADEnabled. I am
using Windows 2000 (workstation, 5.00.2195, SP4) and SQLServer 2000
Developer (Product version 8.00.858 SP3). Calling xp_MSADEnabled results in
an unspecified error (-2147467259).
> If I call the same procedure on a Windows 2003 Server machine, it works
fine.
> This procedure is called in several other system procedures like
sp_addmergepublication and sp_dropmergepublication.
> I am developing the replication functionality of a database application. I
discovered the problem when I wanted to call sp_addmergepublication from my
ADO.NET application. This resulted in the SqlException "A severe error
occurred on the current command. The results, if any, should be discarded".
Calling this procedure from the SQL Query Analyzer did not result in the
error.
> greeting, Marco

Problem report xp_MSADEnabled

Hello
I have detected a problem with the stored procedure xp_MSADEnabled. I am using Windows 2000 (workstation, 5.00.2195, SP4) and SQLServer 2000 Developer (Product version 8.00.858 SP3). Calling xp_MSADEnabled results in an unspecified error (-2147467259)
If I call the same procedure on a Windows 2003 Server machine, it works fine
This procedure is called in several other system procedures like sp_addmergepublication and sp_dropmergepublication.
I am developing the replication functionality of a database application. I discovered the problem when I wanted to call sp_addmergepublication from my ADO.NET application. This resulted in the SqlException "A severe error occurred on the current command. The results, if any, should be discarded". Calling this procedure from the SQL Query Analyzer did not result in the error
greeting, MarcoHi
Looking at other people having problems with this xp_MSADEnabled tend to
point to service account problems such as duplicate account entries in the
AD or insufficient permissions to access it.
John
"Marco Broenink" <marco.broenink@.ict.nl> wrote in message
news:0EB1A879-F364-4646-AE5B-28C3754194AD@.microsoft.com...
> Hello,
> I have detected a problem with the stored procedure xp_MSADEnabled. I am
using Windows 2000 (workstation, 5.00.2195, SP4) and SQLServer 2000
Developer (Product version 8.00.858 SP3). Calling xp_MSADEnabled results in
an unspecified error (-2147467259).
> If I call the same procedure on a Windows 2003 Server machine, it works
fine.
> This procedure is called in several other system procedures like
sp_addmergepublication and sp_dropmergepublication.
> I am developing the replication functionality of a database application. I
discovered the problem when I wanted to call sp_addmergepublication from my
ADO.NET application. This resulted in the SqlException "A severe error
occurred on the current command. The results, if any, should be discarded".
Calling this procedure from the SQL Query Analyzer did not result in the
error.
> greeting, Marcosql

Problem report xp_MSADEnabled

Hello,
I have detected a problem with the stored procedure xp_MSADEnabled. I am using Windows 2000 (workstation, 5.00.2195, SP4) and SQLServer 2000 Developer (Product version 8.00.858 SP3). Calling xp_MSADEnabled results in an unspecified error (-2147467259).
If I call the same procedure on a Windows 2003 Server machine, it works fine.
This procedure is called in several other system procedures like sp_addmergepublication and sp_dropmergepublication.
I am developing the replication functionality of a database application. I discovered the problem when I wanted to call sp_addmergepublication from my ADO.NET application. This resulted in the SqlException "A severe error occurred on the current command.
The results, if any, should be discarded". Calling this procedure from the SQL Query Analyzer did not result in the error.
greeting, Marco
This procedure test to see if the particular SQL Server is Active Directory enabled.
In your code are you publishing your publication to Active Directory? IE in the proc sp_addpublication are you setting
@.add_to_active_directory to true?
Do you need this functionality?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Thanks for reply.
I don't need active directory functionality. I set the @.add_to_active_directory to false but the sp_addmergepublication still calls xp_MSADEnabled.
-- Hilary Cotter wrote: --
This procedure test to see if the particular SQL Server is Active Directory enabled.
In your code are you publishing your publication to Active Directory? IE in the proc sp_addpublication are you setting
@.add_to_active_directory to true?
Do you need this functionality?
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hi
Looking at other people having problems with this xp_MSADEnabled tend to
point to service account problems such as duplicate account entries in the
AD or insufficient permissions to access it.
John
"Marco Broenink" <marco.broenink@.ict.nl> wrote in message
news:0EB1A879-F364-4646-AE5B-28C3754194AD@.microsoft.com...
> Hello,
> I have detected a problem with the stored procedure xp_MSADEnabled. I am
using Windows 2000 (workstation, 5.00.2195, SP4) and SQLServer 2000
Developer (Product version 8.00.858 SP3). Calling xp_MSADEnabled results in
an unspecified error (-2147467259).
> If I call the same procedure on a Windows 2003 Server machine, it works
fine.
> This procedure is called in several other system procedures like
sp_addmergepublication and sp_dropmergepublication.
> I am developing the replication functionality of a database application. I
discovered the problem when I wanted to call sp_addmergepublication from my
ADO.NET application. This resulted in the SqlException "A severe error
occurred on the current command. The results, if any, should be discarded".
Calling this procedure from the SQL Query Analyzer did not result in the
error.
> greeting, Marco

problem pushing subscriber updates to publisher in transactional replication -

I have a primary and secondary servers both running Windows 2000 SP3 with SQL 2000 SP3. I have set up transactional replication with the primary server as publisher and the secondary server has the distributor and subscriber DB. I am testing the scenerio where my primary server goes down and I have to make updates to the secondary server until my primary server comes back up. I am able to update my subscriber database and the transactions go into the MSreplication_queue table to be pushed back to the primary when it comes back up. When I bring the primary server back up and start the queue agent job it starts pushing the transactions over and then stops after 4 or 5 transactions with the error "Failed while applying queued message to publisher". I have attached part of the log file for the agent below

dbserver2.Old_Processing: {? = call dbo.sp_getsqlqueueversion (?, ?, ?, ?)}
dbserver2.Old_Processing: {? = call dbo.sp_replsqlqgetrows (N'DBSERVER', N'Old_Processing', N'Old_Processing')}
[4/15/2004 3:59:47 PM]dbserver2.distribution: exec dbo.sp_helpdistpublisher @.publisher = N'DBSERVER'
Connecting to DBSERVER 'DBSERVER.Old_Processing'
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 21)}
SQL Command : <exec [dbo].[sp_MSsync_ins_IQ2KProcSystem_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', 1>
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 15)}
SQL Command : <exec [dbo].[sp_MSsync_ins_NightlyProcess_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', '2004-04-15 15:56:44.623000000', 'Begin ProcessIQ2KSystem', 'AB14E5D7-C81D-4A39-A8F5-51F1C48227B0', '17E5D98F-EDF0-41D0-9991-97511B850720', 1>
SQL Command : <exec [dbo].[sp_MSsync_upd_IQ2KProcSystem_1] N'dbserver2', N'Old_Processing', '072175', '2004-03-19 00:00:00.000', 1>
DBSERVER.Old_Processing: {? = call dbo.sp_getqueuedarticlesynctraninfo (N'Old_Processing', 7)}
SQL Command : <exec [dbo].[sp_MSsync_upd_TheatreProcess_1] N'dbserver2', N'Old_Processing', '072175', 1, '2004-03-19 00:00:00.000', 1, NULL, '79A114D6-FF31-4E37-AC2D-90C0A0114F40', '072175', 1, '2004-03-19 00:00:00.000', 0, NULL, 'AF61A098-44A3-45D7-B25B-E8EA9CD464A1', 0x2800, 1>
Failed while applying queued message to publisher
Disconnecting from DBSERVER 'DBSERVER'
Worker Thread 692 : Task Failed
Disconnecting from dbserver2 'dbserver2'
Processed 3 queued trans, 3 cmds, 0 conflicts
Queue Reader aborting

In the sql server logs I am getting this message:
Replication-Replication Transaction Queue Reader Subsystem: agent Repl Queue Reader failed. Failed while applying queued message to publisher.
Error: 14151, Severity: 18, State: 1

Any help would be greatly appreciatedFew suggestions:
Refer to SQLAgent log for further information.
Meanwhile, stopping and restarting SQLServerAgent may allow you to temporarily resolve the problem that you are experiencing.

Did you try to forcefully terminate any replication agents in task manager by any chance?

Finally, running the snapshot agent from the command line may allow you to determine whether it was the snapshot agent that crashed unexpectedly.|||Originally posted by Satya
Few suggestions:
Refer to SQLAgent log for further information.
Meanwhile, stopping and restarting SQLServerAgent may allow you to temporarily resolve the problem that you are experiencing.

Did you try to forcefully terminate any replication agents in task manager by any chance?

Finally, running the snapshot agent from the command line may allow you to determine whether it was the snapshot agent that crashed unexpectedly.

The SQLAgent log did not have any errors in it

Stopping and restarting SQLServerAgent did not allow me to temporarily resolve this problem. I still get the same error.

I did not forcefully terminate any replication agents in task manager.

Should I run the snapshot agent from the command line or the queue agent since the queue agent is what is failing?

I tried running the queue agent from the command line and it failed with the same error as above|||Check whether the KBA [http://support.microsoft.com/default.aspx?scid=kb;EN-US;294970] is any good to you.|||I don't think that is my problem. The only way I have been able to get the queue agent to work again is to either drop and readd the subscription or reinitialize the subscription. Of course when I do this it wipes out any updates that have been done to the subscriber.

Problem passing a parameter in URL to a report

Hi all.
I have that problem. I need to pass a parameter to a report. That parameter
is a user in a windows domain, so I have the problem that is stored in
"DOMAIN\USER" format, and I can't pass the backslash to the url.
How can I solve that?
--
Regards,
Diego F.I ask myself. I can encode \ with %5C.
I'm embarrased for that stupid question...
--
Regards,
Diego F.
"Diego F." <diegofrNO@.terra.es> escribió en el mensaje
news:OUA34HxMGHA.344@.TK2MSFTNGP11.phx.gbl...
> Hi all.
> I have that problem. I need to pass a parameter to a report. That
> parameter is a user in a windows domain, so I have the problem that is
> stored in "DOMAIN\USER" format, and I can't pass the backslash to the url.
> How can I solve that?
> --
> Regards,
> Diego F.
>
>|||Also, you might not even need to do this. Look at the global variables in
the expression builder User!UserID, it is the user running the report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Diego F." <diegofrNO@.terra.es> wrote in message
news:OTaydMxMGHA.720@.TK2MSFTNGP14.phx.gbl...
>I ask myself. I can encode \ with %5C.
> I'm embarrased for that stupid question...
> --
> Regards,
> Diego F.
>
> "Diego F." <diegofrNO@.terra.es> escribió en el mensaje
> news:OUA34HxMGHA.344@.TK2MSFTNGP11.phx.gbl...
>> Hi all.
>> I have that problem. I need to pass a parameter to a report. That
>> parameter is a user in a windows domain, so I have the problem that is
>> stored in "DOMAIN\USER" format, and I can't pass the backslash to the
>> url.
>> How can I solve that?
>> --
>> Regards,
>> Diego F.
>>
>|||... or you can use POST instead of GET. In other words, you can pass
parameters in a form instead of passing by querystring.

Problem Opening Databases In ASP.NET

I Have A Problem With Opening A Database With "SQLOLEDB" Provider In Web Application Projects!
The Same Database Opens In Windows Application Projects But When I Try TO Open It (Manually Or During A DataAdapter.Fill Method) In Web APPS I Get An Error Message : "Login failed for user 'NT AUTHORITY\NETWORK SERVICE'."
I Use Windows NT Authentication In Connections,
I Have SQL Server 2000 Enterprise Edition (With Default Installation) , Windows .Net Server 2003 Enterprise Edition (IIS 6) , And VS.NET 2003 Enterprise Edition (.Net Framework 1.1)
Can Anyone Tell Me What's Wrong?You need to give the ASPNET user (or the user under which the worker process is running) the appropriate rights to your database.|||Thanks For Your Advice
I Solved That Problem By Adding A New User And Settting Permissions For It
By The Way , IT Came To My Mind That I Can Use ASPNET Account , But I Don't Know The Password , It Is'nt Blank And I Did'nt Set It Myself , n I Don't Know If I Change The Password Using Computer Management / Local Users , Would Be Anything Affected?
OR ASPNET Account Is Only For This Particular Use?

---

Note from moderator SomeNewKid:
Please refrain from capitalizing every word. Not only does it make your post hard to read,
it also means we cannot distinguish Class names and members.|||Typically, the ASPNET user has a system generated account and is local to the machine that the website is on. At work, we use a domain-level account for our web farm and that user then has rights to our app server tier for our file share for uploading/downloading files.

I was thinking of giving your ASPNET windows user rights to the database and using integrated security...

Your database user configuration is more portable and should work just as well.

Problem on Saving Data to one of the Database but work normal on other DB

Hi All,
I have some trouble on supporting a Windows Base System wrote by DELPHI
language implemented in my Company
Environment :
There are two Windows Server 2003 Std Ed servers
Server1 is Application Server
Server2 is Database Server(MS SQL2000 Std SP3a)
Hardware:
IBM Dual Xeon CPU
4GB Memory
Raid5 with 137GB Harddisk Space
Networking:
Server1 exposed to the Internet with firewall protected
A cross over cable connected between Server1 and Server2
Our Case:
We setup the Application on Server1
And all Workstations using the Windows Base Interface to use the program
If some user work outside Office, they will use the program through a remote
connection named TAXXI(http://www.taxxi.com).
Now we met a problem, on the SQL Server, we have created 3-5 database for
different group of users.
One of the database named D on the SQL Server always hang without any error
log.
It suddenly hang when user save their Data.
They can get the data from the System ,
only Save the data will make the screen freeze and lost of data after the
system back to normal in a hrs later.
We can't find any strange log on the EVENT VIEWER or SQL Server Log
From the Performance Monitor, we just found the Page Fault/sec was high and
always reach the Peak on Server1.
For Server2, only Write to Physical Disk was high.
If we login to other database in the time database D freeze the user screen
when save,
it works normally with input or save data to the system.
Plz help and give some suggestions for us to solve the problem.
Thx
Jack
Have you checked for database blocking during the save operation?
Hope this helps.
Dan Guzman
SQL Server MVP
"J" <j@.j> wrote in message news:%23hqYryrMFHA.2384@.tk2msftngp13.phx.gbl...
> Hi All,
> I have some trouble on supporting a Windows Base System wrote by DELPHI
> language implemented in my Company
> Environment :
> There are two Windows Server 2003 Std Ed servers
> Server1 is Application Server
> Server2 is Database Server(MS SQL2000 Std SP3a)
> Hardware:
> IBM Dual Xeon CPU
> 4GB Memory
> Raid5 with 137GB Harddisk Space
> Networking:
> Server1 exposed to the Internet with firewall protected
> A cross over cable connected between Server1 and Server2
> Our Case:
> We setup the Application on Server1
> And all Workstations using the Windows Base Interface to use the program
> If some user work outside Office, they will use the program through a
> remote
> connection named TAXXI(http://www.taxxi.com).
> Now we met a problem, on the SQL Server, we have created 3-5 database for
> different group of users.
> One of the database named D on the SQL Server always hang without any
> error
> log.
> It suddenly hang when user save their Data.
> They can get the data from the System ,
> only Save the data will make the screen freeze and lost of data after the
> system back to normal in a hrs later.
> We can't find any strange log on the EVENT VIEWER or SQL Server Log
> From the Performance Monitor, we just found the Page Fault/sec was high
> and
> always reach the Peak on Server1.
> For Server2, only Write to Physical Disk was high.
> If we login to other database in the time database D freeze the user
> screen
> when save,
> it works normally with input or save data to the system.
> Plz help and give some suggestions for us to solve the problem.
> Thx
> Jack
>
|||I agree with Dan, it sounds like a locking problem... when things are hung
up, use sp_who or Sp_who2, and check the blocking field. If it is non-zero,
then the corresponding spid is being blocked by the spid in the blocking
field...
This problem is generally caused by long-running trasnactions.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"J" <j@.j> wrote in message news:%23hqYryrMFHA.2384@.tk2msftngp13.phx.gbl...
> Hi All,
> I have some trouble on supporting a Windows Base System wrote by DELPHI
> language implemented in my Company
> Environment :
> There are two Windows Server 2003 Std Ed servers
> Server1 is Application Server
> Server2 is Database Server(MS SQL2000 Std SP3a)
> Hardware:
> IBM Dual Xeon CPU
> 4GB Memory
> Raid5 with 137GB Harddisk Space
> Networking:
> Server1 exposed to the Internet with firewall protected
> A cross over cable connected between Server1 and Server2
> Our Case:
> We setup the Application on Server1
> And all Workstations using the Windows Base Interface to use the program
> If some user work outside Office, they will use the program through a
> remote
> connection named TAXXI(http://www.taxxi.com).
> Now we met a problem, on the SQL Server, we have created 3-5 database for
> different group of users.
> One of the database named D on the SQL Server always hang without any
> error
> log.
> It suddenly hang when user save their Data.
> They can get the data from the System ,
> only Save the data will make the screen freeze and lost of data after the
> system back to normal in a hrs later.
> We can't find any strange log on the EVENT VIEWER or SQL Server Log
> From the Performance Monitor, we just found the Page Fault/sec was high
> and
> always reach the Peak on Server1.
> For Server2, only Write to Physical Disk was high.
> If we login to other database in the time database D freeze the user
> screen
> when save,
> it works normally with input or save data to the system.
> Plz help and give some suggestions for us to solve the problem.
> Thx
> Jack
>

Problem on Saving Data to one of the Database but work normal on other DB

Hi All,
I have some trouble on supporting a Windows Base System wrote by DELPHI
language implemented in my Company
Environment :
There are two Windows Server 2003 Std Ed servers
Server1 is Application Server
Server2 is Database Server(MS SQL2000 Std SP3a)
Hardware:
IBM Dual Xeon CPU
4GB Memory
Raid5 with 137GB Harddisk Space
Networking:
Server1 exposed to the Internet with firewall protected
A cross over cable connected between Server1 and Server2
Our Case:
We setup the Application on Server1
And all Workstations using the Windows Base Interface to use the program
If some user work outside Office, they will use the program through a remote
connection named TAXXI(http://www.taxxi.com).
Now we met a problem, on the SQL Server, we have created 3-5 database for
different group of users.
One of the database named D on the SQL Server always hang without any error
log.
It suddenly hang when user save their Data.
They can get the data from the System ,
only Save the data will make the screen freeze and lost of data after the
system back to normal in a hrs later.
We can't find any strange log on the EVENT VIEWER or SQL Server Log
From the Performance Monitor, we just found the Page Fault/sec was high and
always reach the Peak on Server1.
For Server2, only Write to Physical Disk was high.
If we login to other database in the time database D freeze the user screen
when save,
it works normally with input or save data to the system.
Plz help and give some suggestions for us to solve the problem.
Thx
JackHave you checked for database blocking during the save operation?
Hope this helps.
Dan Guzman
SQL Server MVP
"J" <j@.j> wrote in message news:%23hqYryrMFHA.2384@.tk2msftngp13.phx.gbl...
> Hi All,
> I have some trouble on supporting a Windows Base System wrote by DELPHI
> language implemented in my Company
> Environment :
> There are two Windows Server 2003 Std Ed servers
> Server1 is Application Server
> Server2 is Database Server(MS SQL2000 Std SP3a)
> Hardware:
> IBM Dual Xeon CPU
> 4GB Memory
> Raid5 with 137GB Harddisk Space
> Networking:
> Server1 exposed to the Internet with firewall protected
> A cross over cable connected between Server1 and Server2
> Our Case:
> We setup the Application on Server1
> And all Workstations using the Windows Base Interface to use the program
> If some user work outside Office, they will use the program through a
> remote
> connection named TAXXI(http://www.taxxi.com).
> Now we met a problem, on the SQL Server, we have created 3-5 database for
> different group of users.
> One of the database named D on the SQL Server always hang without any
> error
> log.
> It suddenly hang when user save their Data.
> They can get the data from the System ,
> only Save the data will make the screen freeze and lost of data after the
> system back to normal in a hrs later.
> We can't find any strange log on the EVENT VIEWER or SQL Server Log
> From the Performance Monitor, we just found the Page Fault/sec was high
> and
> always reach the Peak on Server1.
> For Server2, only Write to Physical Disk was high.
> If we login to other database in the time database D freeze the user
> screen
> when save,
> it works normally with input or save data to the system.
> Plz help and give some suggestions for us to solve the problem.
> Thx
> Jack
>|||I agree with Dan, it sounds like a locking problem... when things are hung
up, use sp_who or Sp_who2, and check the blocking field. If it is non-zero,
then the corresponding spid is being blocked by the spid in the blocking
field...
This problem is generally caused by long-running trasnactions.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"J" <j@.j> wrote in message news:%23hqYryrMFHA.2384@.tk2msftngp13.phx.gbl...
> Hi All,
> I have some trouble on supporting a Windows Base System wrote by DELPHI
> language implemented in my Company
> Environment :
> There are two Windows Server 2003 Std Ed servers
> Server1 is Application Server
> Server2 is Database Server(MS SQL2000 Std SP3a)
> Hardware:
> IBM Dual Xeon CPU
> 4GB Memory
> Raid5 with 137GB Harddisk Space
> Networking:
> Server1 exposed to the Internet with firewall protected
> A cross over cable connected between Server1 and Server2
> Our Case:
> We setup the Application on Server1
> And all Workstations using the Windows Base Interface to use the program
> If some user work outside Office, they will use the program through a
> remote
> connection named TAXXI(http://www.taxxi.com).
> Now we met a problem, on the SQL Server, we have created 3-5 database for
> different group of users.
> One of the database named D on the SQL Server always hang without any
> error
> log.
> It suddenly hang when user save their Data.
> They can get the data from the System ,
> only Save the data will make the screen freeze and lost of data after the
> system back to normal in a hrs later.
> We can't find any strange log on the EVENT VIEWER or SQL Server Log
> From the Performance Monitor, we just found the Page Fault/sec was high
> and
> always reach the Peak on Server1.
> For Server2, only Write to Physical Disk was high.
> If we login to other database in the time database D freeze the user
> screen
> when save,
> it works normally with input or save data to the system.
> Plz help and give some suggestions for us to solve the problem.
> Thx
> Jack
>

Problem on Saving Data to one of the Database but work normal on other DB

Hi All,
I have some trouble on supporting a Windows Base System wrote by DELPHI
language implemented in my Company
Environment :
There are two Windows Server 2003 Std Ed servers
Server1 is Application Server
Server2 is Database Server(MS SQL2000 Std SP3a)
Hardware:
IBM Dual Xeon CPU
4GB Memory
Raid5 with 137GB Harddisk Space
Networking:
Server1 exposed to the Internet with firewall protected
A cross over cable connected between Server1 and Server2
Our Case:
We setup the Application on Server1
And all Workstations using the Windows Base Interface to use the program
If some user work outside Office, they will use the program through a remote
connection named TAXXI(http://www.taxxi.com).
Now we met a problem, on the SQL Server, we have created 3-5 database for
different group of users.
One of the database named D on the SQL Server always hang without any error
log.
It suddenly hang when user save their Data.
They can get the data from the System ,
only Save the data will make the screen freeze and lost of data after the
system back to normal in a hrs later.
We can't find any strange log on the EVENT VIEWER or SQL Server Log
From the Performance Monitor, we just found the Page Fault/sec was high and
always reach the Peak on Server1.
For Server2, only Write to Physical Disk was high.
If we login to other database in the time database D freeze the user screen
when save,
it works normally with input or save data to the system.
Plz help and give some suggestions for us to solve the problem.
Thx
JackHave you checked for database blocking during the save operation?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"J" <j@.j> wrote in message news:%23hqYryrMFHA.2384@.tk2msftngp13.phx.gbl...
> Hi All,
> I have some trouble on supporting a Windows Base System wrote by DELPHI
> language implemented in my Company
> Environment :
> There are two Windows Server 2003 Std Ed servers
> Server1 is Application Server
> Server2 is Database Server(MS SQL2000 Std SP3a)
> Hardware:
> IBM Dual Xeon CPU
> 4GB Memory
> Raid5 with 137GB Harddisk Space
> Networking:
> Server1 exposed to the Internet with firewall protected
> A cross over cable connected between Server1 and Server2
> Our Case:
> We setup the Application on Server1
> And all Workstations using the Windows Base Interface to use the program
> If some user work outside Office, they will use the program through a
> remote
> connection named TAXXI(http://www.taxxi.com).
> Now we met a problem, on the SQL Server, we have created 3-5 database for
> different group of users.
> One of the database named D on the SQL Server always hang without any
> error
> log.
> It suddenly hang when user save their Data.
> They can get the data from the System ,
> only Save the data will make the screen freeze and lost of data after the
> system back to normal in a hrs later.
> We can't find any strange log on the EVENT VIEWER or SQL Server Log
> From the Performance Monitor, we just found the Page Fault/sec was high
> and
> always reach the Peak on Server1.
> For Server2, only Write to Physical Disk was high.
> If we login to other database in the time database D freeze the user
> screen
> when save,
> it works normally with input or save data to the system.
> Plz help and give some suggestions for us to solve the problem.
> Thx
> Jack
>|||I agree with Dan, it sounds like a locking problem... when things are hung
up, use sp_who or Sp_who2, and check the blocking field. If it is non-zero,
then the corresponding spid is being blocked by the spid in the blocking
field...
This problem is generally caused by long-running trasnactions.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"J" <j@.j> wrote in message news:%23hqYryrMFHA.2384@.tk2msftngp13.phx.gbl...
> Hi All,
> I have some trouble on supporting a Windows Base System wrote by DELPHI
> language implemented in my Company
> Environment :
> There are two Windows Server 2003 Std Ed servers
> Server1 is Application Server
> Server2 is Database Server(MS SQL2000 Std SP3a)
> Hardware:
> IBM Dual Xeon CPU
> 4GB Memory
> Raid5 with 137GB Harddisk Space
> Networking:
> Server1 exposed to the Internet with firewall protected
> A cross over cable connected between Server1 and Server2
> Our Case:
> We setup the Application on Server1
> And all Workstations using the Windows Base Interface to use the program
> If some user work outside Office, they will use the program through a
> remote
> connection named TAXXI(http://www.taxxi.com).
> Now we met a problem, on the SQL Server, we have created 3-5 database for
> different group of users.
> One of the database named D on the SQL Server always hang without any
> error
> log.
> It suddenly hang when user save their Data.
> They can get the data from the System ,
> only Save the data will make the screen freeze and lost of data after the
> system back to normal in a hrs later.
> We can't find any strange log on the EVENT VIEWER or SQL Server Log
> From the Performance Monitor, we just found the Page Fault/sec was high
> and
> always reach the Peak on Server1.
> For Server2, only Write to Physical Disk was high.
> If we login to other database in the time database D freeze the user
> screen
> when save,
> it works normally with input or save data to the system.
> Plz help and give some suggestions for us to solve the problem.
> Thx
> Jack
>

problem on registering linked server in SQL2005

I have an InterBase 5 database, which has been registered as an ODBC System Data Source in my Windows 2000. When I try to add it as a linked server in SQL server 2005, the Add Linked Server page make me so frustrated. I can select the ODBC driver, but I don't know how to specify values for the fields such as Product Name, Database Source, and Provider string. I tried many combinations on these field values, all unsucceed . Anybody can tell me how to determine the values of these fields? Any reference articles about this? Please help me!There are examples in Books On Line have you looked at these?

Problem on Connecting to SQL Server on Network thru ODBC DSN

am using Sql Server 7 as my database and it resides on a server ruinning on
Windows XP.
I have used the ODBC DSN for sql server and it worked on stand alone
perfectly but
when i tried to reconfigure the ODBC DNS for other client syustems to
connect to the SERVer it is giving me this error
Connection Failed:
SQLstate:01000
SQL Server error:10061
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen (Connect())
Connection failed:
SQLstate:08001
SQL Server error: 17
[Microsoft][ODBC SQL Server DriverSQL Server does not exists or access denied.
Pls am highly frustrated and helpless, pls help me i need this thing to work
pls help me i need ur help
I have checked the Windows Firewall seetings and its even off, so what
exactly is blocking the connection or i dont know , pls just help me out.
I will be expecting ur reply earnestly
--
sammy dot comHello sammydotcom,
If you are connectin using the name of the SQL Server, make sure that Named
Pipes is turned on.
If you are connecting using an IP address, then IP access needs to be turned
on.
There should be a client tools in your SQL Server menu (off of All Programs)
that will allow you to configure the connection type. Additionaly, double
check the SQL Server setup itself and make sure that it can accept Remote
Connections.
Chris Anderson VB-MVP
> am using Sql Server 7 as my database and it resides on a server
> ruinning on
> Windows XP.
> I have used the ODBC DSN for sql server and it worked on stand alone
> perfectly but
> when i tried to reconfigure the ODBC DNS for other client syustems to
> connect to the SERVer it is giving me this error
> Connection Failed:
> SQLstate:01000
> SQL Server error:10061
> [Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen
> (Connect())
> Connection failed:
> SQLstate:08001
> SQL Server error: 17
> [Microsoft][ODBC SQL Server DriverSQL Server does not exists or access
> denied.
> Pls am highly frustrated and helpless, pls help me i need this thing
> to work
> pls help me i need ur help
> I have checked the Windows Firewall seetings and its even off, so what
> exactly is blocking the connection or i dont know , pls just help me
> out.
> I will be expecting ur reply earnestly
>

Problem on collation on SQL Server Express with Windows Mobile 5.0

I am now writing application to connect SQL Server Express in Windows Mobile 5.0.

While running the code, I got error "PlatformNotSupportedException". I realized that it is a problem on different locale on the PDA and the SQL Server. So I tried to re-install the SQL Server for another collation, which is Latin1_General_CI_AI. I have also set the collation to Latin1_General_CI_AI at database-level.

Unfortunately, in the Visual Studio Debugger, I found that the error message is

mscorlib.dll!System.Globalization.CultureInfo.CultureInfo(int culture = 3076, bool useUserOverride = true) + 0xc8 bytes

where the 3076 means Chinese (Hong Kong SAR, PRC) locale from MSDN.

Seems to me that I cannot really change the collation in this Express Edition.

How can I solve it?

Thanks

Hi Billy,

You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer.

Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum.

Mike

|||

Thanks Mike!

"You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer."

-> Yes, I understand that I cannot change Server Setting for Collation after installation. So I DID remove the whole SQL Server Express, and then install it again. At that moment, I selected "SQL_Latin1_General_CP1_CI_AI", that's also what I can see from the SQL Server Management window. It is the same for the database collation setting, which was set to "SQL_Latin1_General_CP1_CI_AI"

So, the problem comes that the actual running in the debugger shows a different collation (Chinese Hong Kong) while it run sqlclient code in the PDA. => That is for sure a contradiction with the server setting. So, what's the problem on this case and how to solve it?

"Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum."

I am using SQL Express in a local PC. And I try to write app using VB on PDA which connects to the PC SQL server.

|||

Hi Billy,

What code are you running to show the collation? Please provide a sample. Do you see similar results if you take the PDA out of the scenario and run code directly on the machine where SQL is installed?

Mike

|||

Hi Mike,

Well, below is the code I run.

Dim sqlConnection1 AsNew SqlConnection("Data Source=BILLY\SQLEXPRESS;Initial Catalog=rfidcps;Persist Security Info=True;User ID=*****; Password=******;")

Dim cmd AsNew SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM Vehicle WHERE MainTagID = '" & "434" & "'"

Dim i AsInteger = 0

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()

And, the error comes out from executing "reader = cmd.ExecuteReader()", which shows something like this

System.PlatformNotSupportedException was unhandled
Message="PlatformNotSupportedException"
StackTrace:
at System.Globalization.CultureInfo..ctor()
at System.Globalization.CultureInfo..ctor()
at System.Data.SqlClient.TdsParser.GetCodePage()
at System.Data.SqlClient.TdsParser.ProcessEnvChange()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlInternalConnection.Login()
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor()
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at DeviceApplication1.rfidcpsDataSetTableAdapters.VehicleTableAdapter.Fill()
at DeviceApplication1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at DeviceApplication1.Form1.Main()

I asked similar question here and I found the collation requested by the server is actually not the one I set to the server

I tried to disconnect connection with the PDA and the server and it showed error in "sqlConnection1.Open()", which was absolutely the right things.

So, I drawed conclusion that the PDA can open the connection to SQL server but just failed to run "reader = cmd.ExecuteReader()"

Thanks for your help~

Best regards,

Billy

|||

I'm running this by a few folks I know to see if they have any ideas.

Mike

Problem on collation on SQL Server Express with Windows Mobile 5.0

I am now writing application to connect SQL Server Express in Windows Mobile 5.0.

While running the code, I got error "PlatformNotSupportedException". I realized that it is a problem on different locale on the PDA and the SQL Server. So I tried to re-install the SQL Server for another collation, which is Latin1_General_CI_AI. I have also set the collation to Latin1_General_CI_AI at database-level.

Unfortunately, in the Visual Studio Debugger, I found that the error message is

mscorlib.dll!System.Globalization.CultureInfo.CultureInfo(int culture = 3076, bool useUserOverride = true) + 0xc8 bytes

where the 3076 means Chinese (Hong Kong SAR, PRC) locale from MSDN.

Seems to me that I cannot really change the collation in this Express Edition.

How can I solve it?

Thanks

Hi Billy,

You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer.

Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum.

Mike

|||

Thanks Mike!

"You can not change the Server setting for Collation order once SQL Express is installed, but you can set collation on a per database basis when you created it. I'm not sure if you can change the database collation order after it's created, but if you look up ALTER DATABASE in Books Online, I'm sure you'll find the answer."

-> Yes, I understand that I cannot change Server Setting for Collation after installation. So I DID remove the whole SQL Server Express, and then install it again. At that moment, I selected "SQL_Latin1_General_CP1_CI_AI", that's also what I can see from the SQL Server Management window. It is the same for the database collation setting, which was set to "SQL_Latin1_General_CP1_CI_AI"

So, the problem comes that the actual running in the debugger shows a different collation (Chinese Hong Kong) while it run sqlclient code in the PDA. => That is for sure a contradiction with the server setting. So, what's the problem on this case and how to solve it?

"Beyond that, where exactly are you installing SQL Express again? SQL Express wouldn't install on Windows Mobile 5.0 so you must be writing an app that connects to a server running SQL Express, or you are actually using SQL Compact Edition. If you are using SQLce, then you should post this question in the SQLce forum."

I am using SQL Express in a local PC. And I try to write app using VB on PDA which connects to the PC SQL server.

|||

Hi Billy,

What code are you running to show the collation? Please provide a sample. Do you see similar results if you take the PDA out of the scenario and run code directly on the machine where SQL is installed?

Mike

|||

Hi Mike,

Well, below is the code I run.

Dim sqlConnection1 As New SqlConnection("Data Source=BILLY\SQLEXPRESS;Initial Catalog=rfidcps;Persist Security Info=True;User ID=*****; Password=******;")

Dim cmd As New SqlCommand

Dim reader As SqlDataReader

cmd.CommandText = "SELECT * FROM Vehicle WHERE MainTagID = '" & "434" & "'"

Dim i As Integer = 0

cmd.CommandType = CommandType.Text

cmd.Connection = sqlConnection1

sqlConnection1.Open()

reader = cmd.ExecuteReader()

And, the error comes out from executing "reader = cmd.ExecuteReader()", which shows something like this

System.PlatformNotSupportedException was unhandled
Message="PlatformNotSupportedException"
StackTrace:
at System.Globalization.CultureInfo..ctor()
at System.Globalization.CultureInfo..ctor()
at System.Data.SqlClient.TdsParser.GetCodePage()
at System.Data.SqlClient.TdsParser.ProcessEnvChange()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.TdsParser.Run()
at System.Data.SqlClient.SqlInternalConnection.Login()
at System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
at System.Data.SqlClient.SqlInternalConnection..ctor()
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen()
at System.Data.Common.DbDataAdapter.FillInternal()
at System.Data.Common.DbDataAdapter.Fill()
at System.Data.Common.DbDataAdapter.Fill()
at DeviceApplication1.rfidcpsDataSetTableAdapters.VehicleTableAdapter.Fill()
at DeviceApplication1.Form1.Form1_Load()
at System.Windows.Forms.Form.OnLoad()
at System.Windows.Forms.Form._SetVisibleNotify()
at System.Windows.Forms.Control.set_Visible()
at System.Windows.Forms.Application.Run()
at DeviceApplication1.Form1.Main()

I asked similar question here and I found the collation requested by the server is actually not the one I set to the server

I tried to disconnect connection with the PDA and the server and it showed error in "sqlConnection1.Open()", which was absolutely the right things.

So, I drawed conclusion that the PDA can open the connection to SQL server but just failed to run "reader = cmd.ExecuteReader()"

Thanks for your help~

Best regards,

Billy

|||

I'm running this by a few folks I know to see if they have any ideas.

Mike

Problem ODBC connection....

I have a porblem which windows 2000 couldn't connect to SQL server 2005 through ODBC. But I have no problem with the rest of my windows XP pro. Please help. thanksWhich error do you get ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||I have done the same step to connect to SQL through ODBC on windows XP pro. But I have 4 other PC which is run under windows 2000 that unable to connect the SQL server. It just counldn't find the server at all.|||

Hi GAN2006,

Like Jens asked, you'll need to post the exact error message that you're receiving. Also, providing your connection string and indicating which driver you're using (SQL Native client, MDAC) would be helpful. Please take a look at this guideline for insight on the type of information that'll make problem determination easier: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

Thanks,
Il-Sung.

|||

I think I have faced the same problem.

All you need to do is to install MDAC 2.8. This is because SQL 2000 connector in windows 2000 is not there or not working properly. Install MDAC 2.8 and it shall work. For windows xp, you dont have to because by nature it already have mdac 2.8 components.

Problem Moving and Restoring a Large database

We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
have requested the Net Admin to take the last full backup of this database,
move it to another server and restore it on that server.
The problem we seem to be having is taking the backup (which is on a NAS)
and copying it to another server, the job takes forever, slowing things
down so the process ends up getting killed. The Net admin is now (I should
say for the past several days) zipping up the .bak in chunks of about 100
mb, but even this process never seems to end.
The whole process has been started and stopped, for one reason or another,
several times in the past 2 weeks. I have never had to do this myself, but
it just seems to me that this process should not be so time consuming or
painful.
Any one have any ideas on what we might be doing wrong? Or a better way to
do it?
Any ideas appreciated.
TIA,
Nancy Lytle"Nancy Lytle" <lytlen@.mdon-line.com> wrote in message
news:%23ByrI%23k2FHA.476@.TK2MSFTNGP15.phx.gbl...
> We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
> have requested the Net Admin to take the last full backup of this
> database, move it to another server and restore it on that server.
> The problem we seem to be having is taking the backup (which is on a NAS)
> and copying it to another server, the job takes forever, slowing things
> down so the process ends up getting killed. The Net admin is now (I
> should say for the past several days) zipping up the .bak in chunks of
> about 100 mb, but even this process never seems to end.
> The whole process has been started and stopped, for one reason or another,
> several times in the past 2 weeks. I have never had to do this myself,
> but it just seems to me that this process should not be so time consuming
> or painful.
> Any one have any ideas on what we might be doing wrong?
Sounds like your NAS solution is insufficient. If you can't move your
backup file to a new server in a reasonable amount of time, how would you
ever recover in case of a disaster?
David|||That is a good question, one I have asked also. We currently have a 2 node
active passive cluster (I know very little about the network side of things,
and I think the net admin likes it that way).
The NAS size is 450 gb for "everything", all the databases, etc. It isn't a
fibre channel but he says the throughput (?) is comparable, so that
shouldn't be the bottleneck.
I forgot to mention that the production server and NAS are in one domain,
(on the West coast) to a server on another domain (we have several domains
because the production domain is W2k3 and the others are W2k) at the office
here on the East Coast.
All this was done shortly before I came on board, we have no DBA, I am
serving as SQL DBA/Developer as well as the Access developer.
Any ideas on a better solution?
Nancy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>
> Sounds like your NAS solution is insufficient. If you can't move your
> backup file to a new server in a reasonable amount of time, how would you
> ever recover in case of a disaster?
>
> David
>|||One or two questions you may not be able to answer:
Are both the source and target servers using the same NAS? If so, is there
an NAS utility that can duplicate the files? (Split a mirror and re-attach
the mirror to the other server, for example--a technique we used to move a
multi-terabyte db.)
Is your problem in the backup or the transfer? If it is the transfer, how
are you doing the transfer, ie, are you on a private,dedicated link or using
the Internet (since your source and destination are 3K miles apart.)
What is unacceptable timing? For a 50GB database, I would not be surprised
at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
handshaking, etc).
If you have a high capacity tape drive, it might be faster to backit up to
tape and then overnight the tape to the destination.
Joseph R.P. Maloney, CSP,CCP,CDP
"Nancy Lytle" wrote:
> That is a good question, one I have asked also. We currently have a 2 node
> active passive cluster (I know very little about the network side of things,
> and I think the net admin likes it that way).
> The NAS size is 450 gb for "everything", all the databases, etc. It isn't a
> fibre channel but he says the throughput (?) is comparable, so that
> shouldn't be the bottleneck.
> I forgot to mention that the production server and NAS are in one domain,
> (on the West coast) to a server on another domain (we have several domains
> because the production domain is W2k3 and the others are W2k) at the office
> here on the East Coast.
> All this was done shortly before I came on board, we have no DBA, I am
> serving as SQL DBA/Developer as well as the Access developer.
> Any ideas on a better solution?
> Nancy
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
> >
> >
> > Sounds like your NAS solution is insufficient. If you can't move your
> > backup file to a new server in a reasonable amount of time, how would you
> > ever recover in case of a disaster?
> >
> >
> > David
> >
>
>|||The source is on a NAS, the destination is not.
The backup is fine, it is the transfer.
As to the time involved, I'm guessing it was longer than 36-48 hours because
the method he is using now is to compress the .bak file using winrar and he
expects that to take 13 hours total to compress the database and chop it up
into 100MB chunks using winrar. He has paused this process until off hours
since winrar is very cpu intensive. He expects this to be completely
archived in 2 days. Then he is going to transfer (so I am guessing the
winrar is being done on the NAS) the files via cable modem (since it is
about 7 times faster than the office T1 and will not interfere with our
email or phones during business hours). He is then going to burn the file
to a CD and bring it in for me.
As I mentioned earlier, there's just something about this that doesn't seem
quite right. I requested this be done over 2 weeks ago, and the request has
still not been completed.
Thanks for you insight.
Nancy
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
> One or two questions you may not be able to answer:
> Are both the source and target servers using the same NAS? If so, is
> there
> an NAS utility that can duplicate the files? (Split a mirror and re-attach
> the mirror to the other server, for example--a technique we used to move a
> multi-terabyte db.)
> Is your problem in the backup or the transfer? If it is the transfer, how
> are you doing the transfer, ie, are you on a private,dedicated link or
> using
> the Internet (since your source and destination are 3K miles apart.)
> What is unacceptable timing? For a 50GB database, I would not be
> surprised
> at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> handshaking, etc).
> If you have a high capacity tape drive, it might be faster to backit up to
> tape and then overnight the tape to the destination.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Nancy Lytle" wrote:
>> That is a good question, one I have asked also. We currently have a 2
>> node
>> active passive cluster (I know very little about the network side of
>> things,
>> and I think the net admin likes it that way).
>> The NAS size is 450 gb for "everything", all the databases, etc. It
>> isn't a
>> fibre channel but he says the throughput (?) is comparable, so that
>> shouldn't be the bottleneck.
>> I forgot to mention that the production server and NAS are in one domain,
>> (on the West coast) to a server on another domain (we have several
>> domains
>> because the production domain is W2k3 and the others are W2k) at the
>> office
>> here on the East Coast.
>> All this was done shortly before I came on board, we have no DBA, I am
>> serving as SQL DBA/Developer as well as the Access developer.
>> Any ideas on a better solution?
>> Nancy
>> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
>> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>> >
>> >
>> > Sounds like your NAS solution is insufficient. If you can't move your
>> > backup file to a new server in a reasonable amount of time, how would
>> > you
>> > ever recover in case of a disaster?
>> >
>> >
>> > David
>> >
>>|||Why don't you try moving the file using FTP rather than copy. If you have a
destination disk that can accomodate the file size then you can do the
following.
1. Install FTP (IIS) on the destination server if not already done. You can
set this to allow anonymouse connections.
2. Backup the database to a flat file using TRANSACT-SQL
BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
From a client computer initiate an FTP session
such as
C:\> open servername.domain.com
(Username) anonymous
(Password) admin@.domain.com
cd to the directory on the destination server where you want to put the file.
put (filename) where filename is the name of the database file.
FTP is much faster than a standard copy. I have a database that is 11GB and
FTP takes about 20 minutes to move the file. So you can cut the time down to
80 minutes or so, depending on the system.
Then you can restore the file to the new destination and use the move
command to tell the system where to move the file. You will need to
understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
Hope this helps.
--
Thanks,
David
"Nancy Lytle" wrote:
> The source is on a NAS, the destination is not.
> The backup is fine, it is the transfer.
> As to the time involved, I'm guessing it was longer than 36-48 hours because
> the method he is using now is to compress the .bak file using winrar and he
> expects that to take 13 hours total to compress the database and chop it up
> into 100MB chunks using winrar. He has paused this process until off hours
> since winrar is very cpu intensive. He expects this to be completely
> archived in 2 days. Then he is going to transfer (so I am guessing the
> winrar is being done on the NAS) the files via cable modem (since it is
> about 7 times faster than the office T1 and will not interfere with our
> email or phones during business hours). He is then going to burn the file
> to a CD and bring it in for me.
> As I mentioned earlier, there's just something about this that doesn't seem
> quite right. I requested this be done over 2 weeks ago, and the request has
> still not been completed.
> Thanks for you insight.
> Nancy
> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
> > One or two questions you may not be able to answer:
> > Are both the source and target servers using the same NAS? If so, is
> > there
> > an NAS utility that can duplicate the files? (Split a mirror and re-attach
> > the mirror to the other server, for example--a technique we used to move a
> > multi-terabyte db.)
> >
> > Is your problem in the backup or the transfer? If it is the transfer, how
> > are you doing the transfer, ie, are you on a private,dedicated link or
> > using
> > the Internet (since your source and destination are 3K miles apart.)
> >
> > What is unacceptable timing? For a 50GB database, I would not be
> > surprised
> > at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> > handshaking, etc).
> >
> > If you have a high capacity tape drive, it might be faster to backit up to
> > tape and then overnight the tape to the destination.
> >
> >
> > --
> > Joseph R.P. Maloney, CSP,CCP,CDP
> >
> >
> > "Nancy Lytle" wrote:
> >
> >> That is a good question, one I have asked also. We currently have a 2
> >> node
> >> active passive cluster (I know very little about the network side of
> >> things,
> >> and I think the net admin likes it that way).
> >> The NAS size is 450 gb for "everything", all the databases, etc. It
> >> isn't a
> >> fibre channel but he says the throughput (?) is comparable, so that
> >> shouldn't be the bottleneck.
> >> I forgot to mention that the production server and NAS are in one domain,
> >> (on the West coast) to a server on another domain (we have several
> >> domains
> >> because the production domain is W2k3 and the others are W2k) at the
> >> office
> >> here on the East Coast.
> >> All this was done shortly before I came on board, we have no DBA, I am
> >> serving as SQL DBA/Developer as well as the Access developer.
> >>
> >> Any ideas on a better solution?
> >> Nancy
> >> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> >> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
> >> >
> >> >
> >> > Sounds like your NAS solution is insufficient. If you can't move your
> >> > backup file to a new server in a reasonable amount of time, how would
> >> > you
> >> > ever recover in case of a disaster?
> >> >
> >> >
> >> > David
> >> >
> >>
> >>
> >>
>
>|||I would also try downloading a trial version of SQL Litespeed from
imceda.com, and installing it on both your originating server &
destination servers. Their compression rates are incredible. You can
probably get your 48gb down to 8-10gb. Dump it locally, then move.|||Sorry,
Mis-typed.
C:\> ftp
ftp> open servername.domain.com
username
password
cd (destination directory)
put filename
ftp>bye
C:\> exit
--
Thanks,
David
"david" wrote:
> Why don't you try moving the file using FTP rather than copy. If you have a
> destination disk that can accomodate the file size then you can do the
> following.
> 1. Install FTP (IIS) on the destination server if not already done. You can
> set this to allow anonymouse connections.
> 2. Backup the database to a flat file using TRANSACT-SQL
> BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
> From a client computer initiate an FTP session
> such as
> C:\> open servername.domain.com
> (Username) anonymous
> (Password) admin@.domain.com
> cd to the directory on the destination server where you want to put the file.
> put (filename) where filename is the name of the database file.
> FTP is much faster than a standard copy. I have a database that is 11GB and
> FTP takes about 20 minutes to move the file. So you can cut the time down to
> 80 minutes or so, depending on the system.
> Then you can restore the file to the new destination and use the move
> command to tell the system where to move the file. You will need to
> understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
> Hope this helps.
> --
> Thanks,
> David
>
> "Nancy Lytle" wrote:
> > The source is on a NAS, the destination is not.
> > The backup is fine, it is the transfer.
> > As to the time involved, I'm guessing it was longer than 36-48 hours because
> > the method he is using now is to compress the .bak file using winrar and he
> > expects that to take 13 hours total to compress the database and chop it up
> > into 100MB chunks using winrar. He has paused this process until off hours
> > since winrar is very cpu intensive. He expects this to be completely
> > archived in 2 days. Then he is going to transfer (so I am guessing the
> > winrar is being done on the NAS) the files via cable modem (since it is
> > about 7 times faster than the office T1 and will not interfere with our
> > email or phones during business hours). He is then going to burn the file
> > to a CD and bring it in for me.
> >
> > As I mentioned earlier, there's just something about this that doesn't seem
> > quite right. I requested this be done over 2 weeks ago, and the request has
> > still not been completed.
> >
> > Thanks for you insight.
> >
> > Nancy
> > "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> > news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
> > > One or two questions you may not be able to answer:
> > > Are both the source and target servers using the same NAS? If so, is
> > > there
> > > an NAS utility that can duplicate the files? (Split a mirror and re-attach
> > > the mirror to the other server, for example--a technique we used to move a
> > > multi-terabyte db.)
> > >
> > > Is your problem in the backup or the transfer? If it is the transfer, how
> > > are you doing the transfer, ie, are you on a private,dedicated link or
> > > using
> > > the Internet (since your source and destination are 3K miles apart.)
> > >
> > > What is unacceptable timing? For a 50GB database, I would not be
> > > surprised
> > > at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> > > handshaking, etc).
> > >
> > > If you have a high capacity tape drive, it might be faster to backit up to
> > > tape and then overnight the tape to the destination.
> > >
> > >
> > > --
> > > Joseph R.P. Maloney, CSP,CCP,CDP
> > >
> > >
> > > "Nancy Lytle" wrote:
> > >
> > >> That is a good question, one I have asked also. We currently have a 2
> > >> node
> > >> active passive cluster (I know very little about the network side of
> > >> things,
> > >> and I think the net admin likes it that way).
> > >> The NAS size is 450 gb for "everything", all the databases, etc. It
> > >> isn't a
> > >> fibre channel but he says the throughput (?) is comparable, so that
> > >> shouldn't be the bottleneck.
> > >> I forgot to mention that the production server and NAS are in one domain,
> > >> (on the West coast) to a server on another domain (we have several
> > >> domains
> > >> because the production domain is W2k3 and the others are W2k) at the
> > >> office
> > >> here on the East Coast.
> > >> All this was done shortly before I came on board, we have no DBA, I am
> > >> serving as SQL DBA/Developer as well as the Access developer.
> > >>
> > >> Any ideas on a better solution?
> > >> Nancy
> > >> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> > >> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
> > >> >
> > >> >
> > >> > Sounds like your NAS solution is insufficient. If you can't move your
> > >> > backup file to a new server in a reasonable amount of time, how would
> > >> > you
> > >> > ever recover in case of a disaster?
> > >> >
> > >> >
> > >> > David
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >

Problem Moving and Restoring a Large database

We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
have requested the Net Admin to take the last full backup of this database,
move it to another server and restore it on that server.
The problem we seem to be having is taking the backup (which is on a NAS)
and copying it to another server, the job takes forever, slowing things
down so the process ends up getting killed. The Net admin is now (I should
say for the past several days) zipping up the .bak in chunks of about 100
mb, but even this process never seems to end.
The whole process has been started and stopped, for one reason or another,
several times in the past 2 weeks. I have never had to do this myself, but
it just seems to me that this process should not be so time consuming or
painful.
Any one have any ideas on what we might be doing wrong? Or a better way to
do it?
Any ideas appreciated.
TIA,
Nancy Lytle
"Nancy Lytle" <lytlen@.mdon-line.com> wrote in message
news:%23ByrI%23k2FHA.476@.TK2MSFTNGP15.phx.gbl...
> We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
> have requested the Net Admin to take the last full backup of this
> database, move it to another server and restore it on that server.
> The problem we seem to be having is taking the backup (which is on a NAS)
> and copying it to another server, the job takes forever, slowing things
> down so the process ends up getting killed. The Net admin is now (I
> should say for the past several days) zipping up the .bak in chunks of
> about 100 mb, but even this process never seems to end.
> The whole process has been started and stopped, for one reason or another,
> several times in the past 2 weeks. I have never had to do this myself,
> but it just seems to me that this process should not be so time consuming
> or painful.
> Any one have any ideas on what we might be doing wrong?
Sounds like your NAS solution is insufficient. If you can't move your
backup file to a new server in a reasonable amount of time, how would you
ever recover in case of a disaster?
David
|||That is a good question, one I have asked also. We currently have a 2 node
active passive cluster (I know very little about the network side of things,
and I think the net admin likes it that way).
The NAS size is 450 gb for "everything", all the databases, etc. It isn't a
fibre channel but he says the throughput (?) is comparable, so that
shouldn't be the bottleneck.
I forgot to mention that the production server and NAS are in one domain,
(on the West coast) to a server on another domain (we have several domains
because the production domain is W2k3 and the others are W2k) at the office
here on the East Coast.
All this was done shortly before I came on board, we have no DBA, I am
serving as SQL DBA/Developer as well as the Access developer.
Any ideas on a better solution?
Nancy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>
> Sounds like your NAS solution is insufficient. If you can't move your
> backup file to a new server in a reasonable amount of time, how would you
> ever recover in case of a disaster?
>
> David
>
|||One or two questions you may not be able to answer:
Are both the source and target servers using the same NAS? If so, is there
an NAS utility that can duplicate the files? (Split a mirror and re-attach
the mirror to the other server, for example--a technique we used to move a
multi-terabyte db.)
Is your problem in the backup or the transfer? If it is the transfer, how
are you doing the transfer, ie, are you on a private,dedicated link or using
the Internet (since your source and destination are 3K miles apart.)
What is unacceptable timing? For a 50GB database, I would not be surprised
at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
handshaking, etc).
If you have a high capacity tape drive, it might be faster to backit up to
tape and then overnight the tape to the destination.
Joseph R.P. Maloney, CSP,CCP,CDP
"Nancy Lytle" wrote:

> That is a good question, one I have asked also. We currently have a 2 node
> active passive cluster (I know very little about the network side of things,
> and I think the net admin likes it that way).
> The NAS size is 450 gb for "everything", all the databases, etc. It isn't a
> fibre channel but he says the throughput (?) is comparable, so that
> shouldn't be the bottleneck.
> I forgot to mention that the production server and NAS are in one domain,
> (on the West coast) to a server on another domain (we have several domains
> because the production domain is W2k3 and the others are W2k) at the office
> here on the East Coast.
> All this was done shortly before I came on board, we have no DBA, I am
> serving as SQL DBA/Developer as well as the Access developer.
> Any ideas on a better solution?
> Nancy
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>
>
|||The source is on a NAS, the destination is not.
The backup is fine, it is the transfer.
As to the time involved, I'm guessing it was longer than 36-48 hours because
the method he is using now is to compress the .bak file using winrar and he
expects that to take 13 hours total to compress the database and chop it up
into 100MB chunks using winrar. He has paused this process until off hours
since winrar is very cpu intensive. He expects this to be completely
archived in 2 days. Then he is going to transfer (so I am guessing the
winrar is being done on the NAS) the files via cable modem (since it is
about 7 times faster than the office T1 and will not interfere with our
email or phones during business hours). He is then going to burn the file
to a CD and bring it in for me.
As I mentioned earlier, there's just something about this that doesn't seem
quite right. I requested this be done over 2 weeks ago, and the request has
still not been completed.
Thanks for you insight.
Nancy
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...[vbcol=seagreen]
> One or two questions you may not be able to answer:
> Are both the source and target servers using the same NAS? If so, is
> there
> an NAS utility that can duplicate the files? (Split a mirror and re-attach
> the mirror to the other server, for example--a technique we used to move a
> multi-terabyte db.)
> Is your problem in the backup or the transfer? If it is the transfer, how
> are you doing the transfer, ie, are you on a private,dedicated link or
> using
> the Internet (since your source and destination are 3K miles apart.)
> What is unacceptable timing? For a 50GB database, I would not be
> surprised
> at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> handshaking, etc).
> If you have a high capacity tape drive, it might be faster to backit up to
> tape and then overnight the tape to the destination.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Nancy Lytle" wrote:
|||Why don't you try moving the file using FTP rather than copy. If you have a
destination disk that can accomodate the file size then you can do the
following.
1. Install FTP (IIS) on the destination server if not already done. You can
set this to allow anonymouse connections.
2. Backup the database to a flat file using TRANSACT-SQL
BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
From a client computer initiate an FTP session
such as
C:\> open servername.domain.com
(Username) anonymous
(Password) admin@.domain.com
cd to the directory on the destination server where you want to put the file.
put (filename) where filename is the name of the database file.
FTP is much faster than a standard copy. I have a database that is 11GB and
FTP takes about 20 minutes to move the file. So you can cut the time down to
80 minutes or so, depending on the system.
Then you can restore the file to the new destination and use the move
command to tell the system where to move the file. You will need to
understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
Hope this helps.
Thanks,
David
"Nancy Lytle" wrote:

> The source is on a NAS, the destination is not.
> The backup is fine, it is the transfer.
> As to the time involved, I'm guessing it was longer than 36-48 hours because
> the method he is using now is to compress the .bak file using winrar and he
> expects that to take 13 hours total to compress the database and chop it up
> into 100MB chunks using winrar. He has paused this process until off hours
> since winrar is very cpu intensive. He expects this to be completely
> archived in 2 days. Then he is going to transfer (so I am guessing the
> winrar is being done on the NAS) the files via cable modem (since it is
> about 7 times faster than the office T1 and will not interfere with our
> email or phones during business hours). He is then going to burn the file
> to a CD and bring it in for me.
> As I mentioned earlier, there's just something about this that doesn't seem
> quite right. I requested this be done over 2 weeks ago, and the request has
> still not been completed.
> Thanks for you insight.
> Nancy
> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
>
>
|||I would also try downloading a trial version of SQL Litespeed from
imceda.com, and installing it on both your originating server &
destination servers. Their compression rates are incredible. You can
probably get your 48gb down to 8-10gb. Dump it locally, then move.
|||Sorry,
Mis-typed.
C:\> ftp
ftp> open servername.domain.com
username
password
cd (destination directory)
put filename
ftp>bye
C:\> exit
Thanks,
David
"david" wrote:
[vbcol=seagreen]
> Why don't you try moving the file using FTP rather than copy. If you have a
> destination disk that can accomodate the file size then you can do the
> following.
> 1. Install FTP (IIS) on the destination server if not already done. You can
> set this to allow anonymouse connections.
> 2. Backup the database to a flat file using TRANSACT-SQL
> BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
> From a client computer initiate an FTP session
> such as
> C:\> open servername.domain.com
> (Username) anonymous
> (Password) admin@.domain.com
> cd to the directory on the destination server where you want to put the file.
> put (filename) where filename is the name of the database file.
> FTP is much faster than a standard copy. I have a database that is 11GB and
> FTP takes about 20 minutes to move the file. So you can cut the time down to
> 80 minutes or so, depending on the system.
> Then you can restore the file to the new destination and use the move
> command to tell the system where to move the file. You will need to
> understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
> Hope this helps.
> --
> Thanks,
> David
>
> "Nancy Lytle" wrote:

Problem Moving and Restoring a Large database

We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
have requested the Net Admin to take the last full backup of this database,
move it to another server and restore it on that server.
The problem we seem to be having is taking the backup (which is on a NAS)
and copying it to another server, the job takes forever, slowing things
down so the process ends up getting killed. The Net admin is now (I should
say for the past several days) zipping up the .bak in chunks of about 100
mb, but even this process never seems to end.
The whole process has been started and stopped, for one reason or another,
several times in the past 2 weeks. I have never had to do this myself, but
it just seems to me that this process should not be so time consuming or
painful.
Any one have any ideas on what we might be doing wrong? Or a better way to
do it?
Any ideas appreciated.
TIA,
Nancy Lytle"Nancy Lytle" <lytlen@.mdon-line.com> wrote in message
news:%23ByrI%23k2FHA.476@.TK2MSFTNGP15.phx.gbl...
> We have a 48 gb production database (SQL 2000 sp4 and Windows 2003) and I
> have requested the Net Admin to take the last full backup of this
> database, move it to another server and restore it on that server.
> The problem we seem to be having is taking the backup (which is on a NAS)
> and copying it to another server, the job takes forever, slowing things
> down so the process ends up getting killed. The Net admin is now (I
> should say for the past several days) zipping up the .bak in chunks of
> about 100 mb, but even this process never seems to end.
> The whole process has been started and stopped, for one reason or another,
> several times in the past 2 weeks. I have never had to do this myself,
> but it just seems to me that this process should not be so time consuming
> or painful.
> Any one have any ideas on what we might be doing wrong?
Sounds like your NAS solution is insufficient. If you can't move your
backup file to a new server in a reasonable amount of time, how would you
ever recover in case of a disaster?
David|||That is a good question, one I have asked also. We currently have a 2 node
active passive cluster (I know very little about the network side of things,
and I think the net admin likes it that way).
The NAS size is 450 gb for "everything", all the databases, etc. It isn't a
fibre channel but he says the throughput (?) is comparable, so that
shouldn't be the bottleneck.
I forgot to mention that the production server and NAS are in one domain,
(on the West coast) to a server on another domain (we have several domains
because the production domain is W2k3 and the others are W2k) at the office
here on the East Coast.
All this was done shortly before I came on board, we have no DBA, I am
serving as SQL DBA/Developer as well as the Access developer.
Any ideas on a better solution?
Nancy
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>
> Sounds like your NAS solution is insufficient. If you can't move your
> backup file to a new server in a reasonable amount of time, how would you
> ever recover in case of a disaster?
>
> David
>|||One or two questions you may not be able to answer:
Are both the source and target servers using the same NAS? If so, is there
an NAS utility that can duplicate the files? (Split a mirror and re-attach
the mirror to the other server, for example--a technique we used to move a
multi-terabyte db.)
Is your problem in the backup or the transfer? If it is the transfer, how
are you doing the transfer, ie, are you on a private,dedicated link or using
the Internet (since your source and destination are 3K miles apart.)
What is unacceptable timing? For a 50GB database, I would not be surprised
at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
handshaking, etc).
If you have a high capacity tape drive, it might be faster to backit up to
tape and then overnight the tape to the destination.
Joseph R.P. Maloney, CSP,CCP,CDP
"Nancy Lytle" wrote:

> That is a good question, one I have asked also. We currently have a 2 nod
e
> active passive cluster (I know very little about the network side of thing
s,
> and I think the net admin likes it that way).
> The NAS size is 450 gb for "everything", all the databases, etc. It isn't
a
> fibre channel but he says the throughput (?) is comparable, so that
> shouldn't be the bottleneck.
> I forgot to mention that the production server and NAS are in one domain,
> (on the West coast) to a server on another domain (we have several domains
> because the production domain is W2k3 and the others are W2k) at the offic
e
> here on the East Coast.
> All this was done shortly before I came on board, we have no DBA, I am
> serving as SQL DBA/Developer as well as the Access developer.
> Any ideas on a better solution?
> Nancy
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:Oo7pxDl2FHA.1292@.TK2MSFTNGP12.phx.gbl...
>
>|||The source is on a NAS, the destination is not.
The backup is fine, it is the transfer.
As to the time involved, I'm guessing it was longer than 36-48 hours because
the method he is using now is to compress the .bak file using winrar and he
expects that to take 13 hours total to compress the database and chop it up
into 100MB chunks using winrar. He has paused this process until off hours
since winrar is very cpu intensive. He expects this to be completely
archived in 2 days. Then he is going to transfer (so I am guessing the
winrar is being done on the NAS) the files via cable modem (since it is
about 7 times faster than the office T1 and will not interfere with our
email or phones during business hours). He is then going to burn the file
to a CD and bring it in for me.
As I mentioned earlier, there's just something about this that doesn't seem
quite right. I requested this be done over 2 weeks ago, and the request has
still not been completed.
Thanks for you insight.
Nancy
"jrpm" <jrpm@.discussions.microsoft.com> wrote in message
news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...[vbcol=seagreen]
> One or two questions you may not be able to answer:
> Are both the source and target servers using the same NAS? If so, is
> there
> an NAS utility that can duplicate the files? (Split a mirror and re-attach
> the mirror to the other server, for example--a technique we used to move a
> multi-terabyte db.)
> Is your problem in the backup or the transfer? If it is the transfer, how
> are you doing the transfer, ie, are you on a private,dedicated link or
> using
> the Internet (since your source and destination are 3K miles apart.)
> What is unacceptable timing? For a 50GB database, I would not be
> surprised
> at a 36-48 hour transfer time (500KBytes per second=1Gbyte per hour with
> handshaking, etc).
> If you have a high capacity tape drive, it might be faster to backit up to
> tape and then overnight the tape to the destination.
>
> --
> Joseph R.P. Maloney, CSP,CCP,CDP
>
> "Nancy Lytle" wrote:
>|||Why don't you try moving the file using FTP rather than copy. If you have a
destination disk that can accomodate the file size then you can do the
following.
1. Install FTP (IIS) on the destination server if not already done. You can
set this to allow anonymouse connections.
2. Backup the database to a flat file using TRANSACT-SQL
BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
From a client computer initiate an FTP session
such as
C:\> open servername.domain.com
(Username) anonymous
(Password) admin@.domain.com
cd to the directory on the destination server where you want to put the file
.
put (filename) where filename is the name of the database file.
FTP is much faster than a standard copy. I have a database that is 11GB and
FTP takes about 20 minutes to move the file. So you can cut the time down to
80 minutes or so, depending on the system.
Then you can restore the file to the new destination and use the move
command to tell the system where to move the file. You will need to
understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
Hope this helps.
--
Thanks,
David
"Nancy Lytle" wrote:

> The source is on a NAS, the destination is not.
> The backup is fine, it is the transfer.
> As to the time involved, I'm guessing it was longer than 36-48 hours becau
se
> the method he is using now is to compress the .bak file using winrar and h
e
> expects that to take 13 hours total to compress the database and chop it u
p
> into 100MB chunks using winrar. He has paused this process until off hour
s
> since winrar is very cpu intensive. He expects this to be completely
> archived in 2 days. Then he is going to transfer (so I am guessing the
> winrar is being done on the NAS) the files via cable modem (since it is
> about 7 times faster than the office T1 and will not interfere with our
> email or phones during business hours). He is then going to burn the file
> to a CD and bring it in for me.
> As I mentioned earlier, there's just something about this that doesn't see
m
> quite right. I requested this be done over 2 weeks ago, and the request h
as
> still not been completed.
> Thanks for you insight.
> Nancy
> "jrpm" <jrpm@.discussions.microsoft.com> wrote in message
> news:1EB18358-280F-4DCD-B513-64685666F6DE@.microsoft.com...
>
>|||I would also try downloading a trial version of SQL Litespeed from
imceda.com, and installing it on both your originating server &
destination servers. Their compression rates are incredible. You can
probably get your 48gb down to 8-10gb. Dump it locally, then move.|||Sorry,
Mis-typed.
C:\> ftp
ftp> open servername.domain.com
username
password
cd (destination directory)
put filename
ftp>bye
C:\> exit
--
Thanks,
David
"david" wrote:
[vbcol=seagreen]
> Why don't you try moving the file using FTP rather than copy. If you have
a
> destination disk that can accomodate the file size then you can do the
> following.
> 1. Install FTP (IIS) on the destination server if not already done. You ca
n
> set this to allow anonymouse connections.
> 2. Backup the database to a flat file using TRANSACT-SQL
> BACKUP DATABASE (dbname) TO DISK=N'C:\destination\db.bak' with init
> From a client computer initiate an FTP session
> such as
> C:\> open servername.domain.com
> (Username) anonymous
> (Password) admin@.domain.com
> cd to the directory on the destination server where you want to put the fi
le.
> put (filename) where filename is the name of the database file.
> FTP is much faster than a standard copy. I have a database that is 11GB an
d
> FTP takes about 20 minutes to move the file. So you can cut the time down
to
> 80 minutes or so, depending on the system.
> Then you can restore the file to the new destination and use the move
> command to tell the system where to move the file. You will need to
> understand the RESTORE HEADERONLY command and the RESTORE COMMAND.
> Hope this helps.
> --
> Thanks,
> David
>
> "Nancy Lytle" wrote:
>