Showing posts with label mssql. Show all posts
Showing posts with label mssql. Show all posts

Wednesday, March 28, 2012

How to transfer backup from client to server machine (MSSQL, SQL Server)

MSSQL, Microsoft SQL Server:
I succeeded in transferring a backup file from the SQL Server machine
to the client using OPENROWSET.
I used this way because it is not (always easy) possible to restore a
file residing on the client machine on a SQL Server.
One has to share drives on the client machine for remote SQL Server
access or better granting access on the SQL Server machine for the
client.
In my line of bussiness it is not possible to help every client on this
issue (several thousand users)
What I need to achieve is transferring backup to the server by using
SQL Server to do it and restoring it with SQL Server.
A solution for SQL 2005 would be perfect (it is not necessary to solve
this in SQL Server 2000). Using Named pipes is not an option because it
is no longer supported in SQL2005
Hi
"Oz" wrote:

> MSSQL, Microsoft SQL Server:
> I succeeded in transferring a backup file from the SQL Server machine
> to the client using OPENROWSET.
I am not sure why you are doing this, if you are actually talking about a
database backup. I assume this is a CSV or XML file? If so why not use the
BULK INSERT command? This will take a UNC name for the file if necessary.

> I used this way because it is not (always easy) possible to restore a
> file residing on the client machine on a SQL Server.
> One has to share drives on the client machine for remote SQL Server
> access or better granting access on the SQL Server machine for the
> client.
> In my line of bussiness it is not possible to help every client on this
> issue (several thousand users)
> What I need to achieve is transferring backup to the server by using
> SQL Server to do it and restoring it with SQL Server.
Have you looked at SSIS and loading the file using FTP either through a
package or User Interface (e.g
http://classicasp.aspfaq.com/general/how-do-i-ftp-files-from-asp.html ) onto
the SQL Server or somewhere which can be seen by the SQL Server and then
loading it from a package?

> A solution for SQL 2005 would be perfect (it is not necessary to solve
> this in SQL Server 2000). Using Named pipes is not an option because it
> is no longer supported in SQL2005
>
John

Monday, March 26, 2012

How to track down lock escalation

Hi,
using MSSQL 2000 SP3 on an Win2003 Server. My DB has 60 highly concurrent
users. Occasionlly I see heavy lock escalation (performance monitor) which
leads to blocking situations. Althoug I tried to track down the blocking
statements, I was not able to figure out which statements lead to the
escalation.
Any idea, what I can trie to identify the statements causing the locks?
TIA
Bernd
Hi,
I'd try to determine which functions or actions of the application are leading to the lock escalation. You can use SP_LOCK to locate the blocking locks, and hence the tables invloved. You might also want to use DBCC INPUTBUFFER on the SpId which is doing
the blocking, as this might give you clues as to what action the user is performing when the problem occurs. It depends on your application as to how useful this will be.
Once you have the action, examining the query plans from performing the actions with Query Analyiser should give you the problem statment. If you are getting a lock escalating to be a table lock, then you will be looking for Table Scans or Clustered Inde
x Scans.
There is also Profiler, which has a Lock:Escalation event, but once you start adding in all the event to collect all the SQL Statements, Profiler can start dropping event, especially when it's getting busy, which is just the bit you want. But if you can g
et the system to yourself and know the problem action, there shouldn't be any problem.

How to track down lock escalation

Hi,
using MSSQL 2000 SP3 on an Win2003 Server. My DB has 60 highly concurrent
users. Occasionlly I see heavy lock escalation (performance monitor) which
leads to blocking situations. Althoug I tried to track down the blocking
statements, I was not able to figure out which statements lead to the
escalation.
Any idea, what I can trie to identify the statements causing the locks?
TIA
BerndHi
I'd try to determine which functions or actions of the application are leading to the lock escalation. You can use SP_LOCK to locate the blocking locks, and hence the tables invloved. You might also want to use DBCC INPUTBUFFER on the SpId which is doing the blocking, as this might give you clues as to what action the user is performing when the problem occurs. It depends on your application as to how useful this will be.
Once you have the action, examining the query plans from performing the actions with Query Analyiser should give you the problem statment. If you are getting a lock escalating to be a table lock, then you will be looking for Table Scans or Clustered Index Scans
There is also Profiler, which has a Lock:Escalation event, but once you start adding in all the event to collect all the SQL Statements, Profiler can start dropping event, especially when it's getting busy, which is just the bit you want. But if you can get the system to yourself and know the problem action, there shouldn't be any problem.sql

How to track down lock escalation

Hi,
using MSSQL 2000 SP3 on an Win2003 Server. My DB has 60 highly concurrent
users. Occasionlly I see heavy lock escalation (performance monitor) which
leads to blocking situations. Althoug I tried to track down the blocking
statements, I was not able to figure out which statements lead to the
escalation.
Any idea, what I can trie to identify the statements causing the locks?
TIA
BerndHi,
I'd try to determine which functions or actions of the application are leadi
ng to the lock escalation. You can use SP_LOCK to locate the blocking locks,
and hence the tables invloved. You might also want to use DBCC INPUTBUFFER
on the SpId which is doing
the blocking, as this might give you clues as to what action the user is per
forming when the problem occurs. It depends on your application as to how us
eful this will be.
Once you have the action, examining the query plans from performing the acti
ons with Query Analyiser should give you the problem statment. If you are ge
tting a lock escalating to be a table lock, then you will be looking for Tab
le Scans or Clustered Inde
x Scans.
There is also Profiler, which has a Lock:Escalation event, but once you star
t adding in all the event to collect all the SQL Statements, Profiler can st
art dropping event, especially when it's getting busy, which is just the bit
you want. But if you can g
et the system to yourself and know the problem action, there shouldn't be an
y problem.

Monday, March 12, 2012

How to switch off automatic blanking of table views in enterprise manager ?

Hi Sqlserver experts,

I use the SQL Server enterprise manager of MSSQL 2000 regularly.
I'm often annoyed by the automatic blanking of the table views.

If this happens then mostly with the hint :
"The Results pane have been cleared to conserve server resources.
To re-establish the result set, run query again."

Is there any possibility to switch that blanking off ?

Best regards,

Daniel WetzlerCan't be done. Microsoft has gone as far as to put out a Knowledge
Base article on this feature, with instructions to click 'No' when the
pop-up comes up. Not all that helpful, really.

They've fixed the problem in the new version, so it looks like you'll
remain out of luck.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/|||Thank you very much although its really bad news :-(((

Jason Kester schrieb:

Quote:

Originally Posted by

Can't be done. Microsoft has gone as far as to put out a Knowledge
Base article on this feature, with instructions to click 'No' when the
pop-up comes up. Not all that helpful, really.
>
They've fixed the problem in the new version, so it looks like you'll
remain out of luck.
>
Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
>
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/

|||Daniel Wetzler wrote:

Quote:

Originally Posted by

Thank you very much although its really bad news :-(((
>
>
Jason Kester schrieb:
>

Quote:

Originally Posted by

Can't be done. Microsoft has gone as far as to put out a Knowledge
Base article on this feature, with instructions to click 'No' when the
pop-up comes up. Not all that helpful, really.

They've fixed the problem in the new version, so it looks like you'll
remain out of luck.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/
--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/


EM locks data when it reads it. That is why MS releases the result set
after X amount of time. You should really use Query Analyzer to look at
data that you do not intend to change.

IMHO -- Mark D Powell --|||Mark D Powell wrote:

Quote:

Originally Posted by

EM locks data when it reads it.


I don't think that's the case. I've just created a test table, filled
it with some data and viewed it in EM. I could still update one of the
viewed rows using QA. Once I re-ran the EM query using the exclamation
icon again, the changed row reflected its new state.

Surely the result set clearing is because of the resources consumed by
the open connection, rather than any locking? If I'm wrong, somebody
stop me - to quote Jim Carey.

Quote:

Originally Posted by

You should really use Query Analyzer


I agree! It's a much better tool for actually executing queries.|||Bobbo wrote:

Quote:

Originally Posted by

Mark D Powell wrote:
>

Quote:

Originally Posted by

EM locks data when it reads it.


>
I don't think that's the case. I've just created a test table, filled
it with some data and viewed it in EM. I could still update one of the
viewed rows using QA. Once I re-ran the EM query using the exclamation
icon again, the changed row reflected its new state.
>
Surely the result set clearing is because of the resources consumed by
the open connection, rather than any locking? If I'm wrong, somebody
stop me - to quote Jim Carey.
>

Quote:

Originally Posted by

You should really use Query Analyzer


>
I agree! It's a much better tool for actually executing queries.


We have traced data access problems to users accessing data via EM. MS
may have modified some of the logic with SP1, SP2, SP3, and/or SP4 so
that EM works more effectively.

Quote:

Originally Posted by

Quote:

Originally Posted by

>>


>From the SQL Server Documentation: Optimizing Query Performance -


SQL Server is essentially a puppet of the client application. The
client application has almost total control over (and responsibility
for) the locks acquired on the server. Although the SQL Server lock
manager automatically uses locks to protect transactions, this is
directly instigated by the query type sent from the client application
and the way the results are processed. Therefore, resolution of most
blocking problems involves inspecting the client application.

A blocking problem frequently requires both the inspection of the exact
SQL statements submitted by the application and the exact behavior of
the application regarding connection management, processing of all
result rows, and so on. If the development tool does not allow explicit
control over connection management, query time-out, processing of
results, and so on, blocking problems may not be resolvable.

Possible cause of problems listed in the above article (these are
things to avoid)

Submitting queries with long execution times

Canceling queries that were not committed or rolled back. [The
application needs to issue a rollback or commit after a query is
cancelled in order to release held locks!]

Applications that are not processing all results to completion

Distributed client/server deadlocks

Locks are held for the length of time needed to protect the resource at
the level requested:
<<

So it depends on how the clients works if one user can cause more
contention than is reasonable. Cursor and especially select for update
cursors can result in unexpected locks waiting. Like I said we tested
and identified a problem, but it was a couple of SP ago.

-- Mark D Powell --

Friday, February 24, 2012

How to store actual millisecond in datetime?

Hello. I've seached the this forum and others for this question of mine but couldn't get any thing. Everyone know that in MSSQL, the precision of datetime is currently limited to 1/300th of a second. My question is, is there any way to store the actual millisecond in the a table? Also, later, able to pull it up from the table? What are the options?

Please advise.

Thanks,

Rick..

Running the following script worked for me in storing milliseconds. You can review BOL under "Date and Time Formats" for a bit more clarity.

create table test1..t2(c1 datetime, c2 dec(17,12));

insert t2(c1)
select convert(DATETIME, '12/21/07 03:04:05.300');

insert t2(c1)
select getdate();

select c1, convert(dec(17,12), c1) from t2;

update t2
set c2 = convert(dec(17,12), c1);

|||

Hi Zach. Thank for replying. Actually, the problem is still there. If you replace the date you are storing to '10/25/2006 23:59:59.991' and run the script. You will get '10/25/2006 23:59:59.990'. I need to be able to store the exact millisecond being written. This rounding being done to the millisecond is causing use a lot of headache.

Any other idea?

Thanks again.

Rick..

|||Your problem is the Windows clock, which is what you are reading, is only updated every 55 ms. You will never get the EXACT ms something ran. The time is not that acurate.

Whatever you are trying to do, you will need to find another method to determine the order or timing.|||

Hi. I am not sure if your statement was true. I don't think it has anything to do with windows clock. Reason I am saying that was, the date time value I am saving into the table is hardcoded. Everyone know its how Microsoft implemented it. But I need to save the actual value without MSSQL truncating the value before storing it in the database.

Thanks for your comment though.

Rick..

|||I think that it is more of an issue of how Sybase originally defined datetime in SQL Server -and Microsoft has never corrected the 'mistake'.|||

The problem is that you never see the clock value but only the value converted to the binary format of a SQL datetime (which has the level of precision inherent in its structure).

If you really want to get at the unaltered clock value then I think that you need to use code to access it directly. If you are using SQL Server 2005 then you could write CLR code to access the System.DateTime structure. You could probably even create a new data type. Of course you need some way to hold and retrieve the information you want (possibly the ISO 8601 string format yyyy-mm-ddThh:mm:ss.mmm). I don't know the details of attempting this but those in the ".NET Framework inside SQL Server" forum could probably help.

If you are using an earlier version of SQL Server then you probably need to look at extended stored procedures.

|||Open a query window and repeat this statement 500 times:

PRINT CONVERT(varchar(30),getdate(),109)

Run this and see what you get.

You will get the exact same ms until it instantly changes to another set.

It is also a problem, someone mentioned, the significant digits of the time does not allow you to get proper ms.

You still need to find another way to do what you are trying to do.|||

Unfortunately, SQL Server is doing rounding with the datetime data type. Therefore, you won't be able to use this data type. Even if you store the value in a decimal(p,s) data type, you probably won't be able to cast it into a datetime data type without incurring problems.

Oracle implemented true precision, user configurable up to nine (9) decimal places, when it released its TIMESTAMP data type in 9i.

|||

Oddly enough try running the following entries:

select convert(DATETIME, '12/21/07 03:04:05.302')

select convert(DATETIME, '12/21/07 03:04:05.303')

select convert(DATETIME, '12/21/07 03:04:05.304')

select convert(DATETIME, '12/21/07 03:04:05.305')

You will find the following results:

2007-12-21 03:04:05.303

2007-12-21 03:04:05.303

2007-12-21 03:04:05.303

2007-12-21 03:04:05.307

So the next question is, what is going on? This is some mighty weird rounding problems. What we believe to be happening is milliseconds are translated into ticks at 3 ms increments.

Moral of the story, if you really need to save that low then you need to create an int column separate of the datetime column and save the milliseconds there.

|||That is not strange at all. That is caused by the precision of the time in the datetime field.

See this very good explanation:

http://www.sql-server-performance.com/bs_date_time.asp

You are NEVER going to get 1 ms resolution in a datetime field.|||

This MSDN article specifically states an accuracy of 3.33 milliseconds. I only meant it was strange in that it happens because of converting from varchar like '3/28/2007 11:30:00:001' to a datetime value.

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

How to store actual millisecond in datetime?

Hello. I've seached the this forum and others for this question of mine but couldn't get any thing. Everyone know that in MSSQL, the precision of datetime is currently limited to 1/300th of a second. My question is, is there any way to store the actual millisecond in the a table? Also, later, able to pull it up from the table? What are the options?

Please advise.

Thanks,

Rick..

Running the following script worked for me in storing milliseconds. You can review BOL under "Date and Time Formats" for a bit more clarity.

create table test1..t2(c1 datetime, c2 dec(17,12));

insert t2(c1)
select convert(DATETIME, '12/21/07 03:04:05.300');

insert t2(c1)
select getdate();

select c1, convert(dec(17,12), c1) from t2;

update t2
set c2 = convert(dec(17,12), c1);

|||

Hi Zach. Thank for replying. Actually, the problem is still there. If you replace the date you are storing to '10/25/2006 23:59:59.991' and run the script. You will get '10/25/2006 23:59:59.990'. I need to be able to store the exact millisecond being written. This rounding being done to the millisecond is causing use a lot of headache.

Any other idea?

Thanks again.

Rick..

|||Your problem is the Windows clock, which is what you are reading, is only updated every 55 ms. You will never get the EXACT ms something ran. The time is not that acurate.

Whatever you are trying to do, you will need to find another method to determine the order or timing.

|||

Hi. I am not sure if your statement was true. I don't think it has anything to do with windows clock. Reason I am saying that was, the date time value I am saving into the table is hardcoded. Everyone know its how Microsoft implemented it. But I need to save the actual value without MSSQL truncating the value before storing it in the database.

Thanks for your comment though.

Rick..

|||I think that it is more of an issue of how Sybase originally defined datetime in SQL Server -and Microsoft has never corrected the 'mistake'.|||

The problem is that you never see the clock value but only the value converted to the binary format of a SQL datetime (which has the level of precision inherent in its structure).

If you really want to get at the unaltered clock value then I think that you need to use code to access it directly. If you are using SQL Server 2005 then you could write CLR code to access the System.DateTime structure. You could probably even create a new data type. Of course you need some way to hold and retrieve the information you want (possibly the ISO 8601 string format yyyy-mm-ddThh:mm:ss.mmm). I don't know the details of attempting this but those in the ".NET Framework inside SQL Server" forum could probably help.

If you are using an earlier version of SQL Server then you probably need to look at extended stored procedures.

|||Open a query window and repeat this statement 500 times:

PRINT CONVERT(varchar(30),getdate(),109)

Run this and see what you get.

You will get the exact same ms until it instantly changes to another set.

It is also a problem, someone mentioned, the significant digits of the time does not allow you to get proper ms.

You still need to find another way to do what you are trying to do.

|||

Unfortunately, SQL Server is doing rounding with the datetime data type. Therefore, you won't be able to use this data type. Even if you store the value in a decimal(p,s) data type, you probably won't be able to cast it into a datetime data type without incurring problems.

Oracle implemented true precision, user configurable up to nine (9) decimal places, when it released its TIMESTAMP data type in 9i.

|||

Oddly enough try running the following entries:

select convert(DATETIME, '12/21/07 03:04:05.302')

select convert(DATETIME, '12/21/07 03:04:05.303')

select convert(DATETIME, '12/21/07 03:04:05.304')

select convert(DATETIME, '12/21/07 03:04:05.305')

You will find the following results:

2007-12-21 03:04:05.303

2007-12-21 03:04:05.303

2007-12-21 03:04:05.303

2007-12-21 03:04:05.307

So the next question is, what is going on? This is some mighty weird rounding problems. What we believe to be happening is milliseconds are translated into ticks at 3 ms increments.

Moral of the story, if you really need to save that low then you need to create an int column separate of the datetime column and save the milliseconds there.

|||That is not strange at all. That is caused by the precision of the time in the datetime field.

See this very good explanation:

http://www.sql-server-performance.com/bs_date_time.asp

You are NEVER going to get 1 ms resolution in a datetime field.
|||

This MSDN article specifically states an accuracy of 3.33 milliseconds. I only meant it was strange in that it happens because of converting from varchar like '3/28/2007 11:30:00:001' to a datetime value.

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

Sunday, February 19, 2012

How to stop mssql

Hi.
I need to stop mssql service via command row.
How can I do?
Tnk,
Luca"Luca" <NOSPAMprong2k@.hotmail.com> wrote in message
news:chpn2i$rk7$1@.atlantis.cu.mi.it...
> Hi.
> I need to stop mssql service via command row.

net stop mssqlserver

or net stop mssqlserver /y

That will also stop the SQL Server agent w/o prompting.

(net stop sqlserveragent stops the agent.)

> How can I do?
> Tnk,
> Luca

How to stop execution of DTS package (during loop)

I have a MSSQL DTS Package where it needs to loop/execute 3 times because the main task is to import data from 3 excel files (different location) into 1 SQL table. I used a global variable vCounter and I use an ActiveX Script.

ActiveX Script 1

Option Explicit

Function Main()

Dim vDate, vCounter, vBranchCode, vPath

vDate="011207"

vCounter=DTSGlobalVariables("gVarCounter").Value

IF vCounter<=3 THEN

IF vCounter=1 THEN
vBranchCode="ALB"
vPath= "D:\PROJECTS\HRIS\ALB\"
ELSEIF vCounter=2 THEN
vBranchCode="MOA"
vPath= "D:\PROJECTS\HRIS\MOA\"
ELSEIF vCounter=3 THEN
vBranchCode="PSQ"
vPath= "D:\PROJECTS\HRIS\PSQ\"
END IF

DTSGlobalVariables("gVarPath").Value=vPath & vDate & "_" & vBranchCode & ".xls"

Main = DTSTaskExecResult_Success

ELSE
<This is where i will initialize the global variable gVarCounter, so in the next execution..the value should be back to 1>
DTSGlobalVariables("gVarCounter").Value=1
<DTS Process should stop execution...how is this?>
END IF

End Function

After excel to sql dts
ActiveX Script2

Function Main()

IF gVarCounter<=3 then
DTSGlobalVariables("gVarCounter").Value=DTSGlobalVariables("gVarCounter").Value+1
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
END IF

End Function

Thanks a lot.there are many ways to stop a DTS. u can simply write

Main = DTSTaskExecResult_Failure

in the ELSE part and link the next step with "on success" workflow. or u can even write a blank ActiveX step and redirect the flow to that step in the ELSE part with "...DTSStepExecStat_Waiting" as u r already doing|||sorry, i pressed the save button twice... and the same thing got posted twice... there sould be an option to delete a post....|||hi upalsen,

thanks a lot. it's now working. great!
god bless.|||hi LimaCharlie,

thanks for your acknowledgement. many here do not acknowledge the solution they have accepted. an acknowledgement is (1) a recognition for support (2) helps in closing the post (3) helps a third user who is viewing the post at a later date (or coming from search engine) to understand what the final solution could be.