Linked Server Nolock Getdate Issue

Apr 14, 2008

Hi,

I am trying to retreive data using selet into query with nolock. Select query selects around 50,000 records from a linked server and inserting into my database. I have getdate() in my select list and it is returning different datetime in a single query which leads to a problem while taking distinct in later stages. Any info on why this nolock returns different datetime in a single getdate() statement will be a gr8 help.

Thanks in advance,
Senthil

View 4 Replies

Call Getdate From Linked Server

May 12, 2008

Hello,

i need get value of getdate function on local and oon linked server by one select something like this:

select

getdate() 'locladate'
, [SERVER2SQL05_01, 9999].getdate() 'remotedate'

first problem i dont know in which database is getdate stored, second is it possible call function from linked server in select statement?

Thanks Jakub.

View 6 Replies View Related

With Nolock In Sql Server Please Advice

Jan 27, 2008

I have read lots of article about with nolock, and have seen many projects using with nolock option.
Please advice me if i am right.

1.Normal select queries can use with no lock
2. Batch jobs should not use with no lock.

please guide me, am i right.

Thanks

View 6 Replies View Related

Getdate() >= Startdate And Getdate() <= Enddate

Oct 4, 2000

Please i need an exmple of ur solution, thanks :)

I'm using some files to show certain pages on certain date for an example

File name : aa.doc
start date: 10/02/00
end date : 10/03/00

But it expires on 10/02/00, here is the strored procedure:

Before the date comes, it expires the page
Here is my stored procedure:

"
SELECT startdate, enddate,archivedate
and (startdate is null or (getdate() >= startdate and getdate() <= enddate))
and (archivedate is null or (getdate() <= archivedate))
group by startdate, enddate order by startdate desc "

Thankx a lot

View 1 Replies View Related

SQL Server 2008 :: Update With Xlock / Rowlock And Read With Nolock

Feb 10, 2015

I have a stored procedure that updates a table. I also have an UDF that allows dirty reads (nolock).

What's the precedence level in SQL server? If I add xlock,rowlock to the update statement, will the dirty read wait for the update transaction to commit, or will it perform a dirty read regardless of the locking scheme in the update statement?

View 0 Replies View Related

Linked Server ( Not Able To Access Any Tables Under LINKED SERVER From My DESKTOP Enterprise Manager

Mar 25, 2002

Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"

if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)

And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP

Thanks

View 5 Replies View Related

DB Engine :: How To Point Linked Server To Specific Database / Rename Linked Server

Apr 24, 2015

I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.

I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.

How to point the linked server to a specific database? How to rename the Linked Server?

The following is the code that I am using right now:

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
    @server = N'Machine123Instance456',
    @srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'

View 6 Replies View Related

Scripting Stored Procedure Add With Linked Server Reference When Linked Server Is Not Available

Jul 18, 2006

Is there a way to bypass the syntax checking when adding a stored procedure via a script?

I have a script that has a LINKed server reference (see below) .

INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.

ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.

PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.

Thanks,

Terry

View 4 Replies View Related

SQL SERVER Invalid Use Of 'getdate' Within A Function.

Feb 5, 2004

alter function sp_insert_newCustomer
(
@CrmDbIDint,
@DealerIDint
)
returns int
--@AddressLastUpdatedatetime
as
begin

declare @date datetime
set @date = getdate()

insert into dbo.CRMDB values(
@DealerID,
@date --@CreatedDate,
)

return @@Identity
end
GO

View 3 Replies View Related

Sql Server 2005 GETDATE() Function

Apr 22, 2008

I have a small problem using the GETDATE() function from a JAVA program using the MS JDBC connector.

When I use the SQL Mangment Studio Express to execute this query:

SELECT GETDATE()
it returns 2008-04-22 16:25:03.690 which is OK !!

I want to get that same value using a Java program so I do that query but when I display it it shows as:

2008-04-22 and there is no time there !
I tried formatting the date to include time but it shows as: 04/22/2008 12:00:00 AM
So maybe the time isn�t there ?

This is the code i am using:

Statement st = con.createStatement();
String sql = "SELECT GETDATE()";
ResultSet result1 = st.executeQuery(sql);

result1.next();

Date today = result1.getDate(1);

Any help would be appreciated.

View 4 Replies View Related

How Does SQL Server Calculate GETDATE? (was Newbie Question)

Mar 8, 2006

My apologies if my question seems rather naive, but I'm looking for some info on how SQL Server (2K) calculates the GETDATE info. Is it from an internal algorithm of some type, or does it simply pull the info from the local server info?

Thanks to all.

View 3 Replies View Related

Select Convert(varchar(16), Getdate(), 101)+LEFT(REPLACE(convert(varchar, Getdate(), 108), ':', ''),4)

Sep 26, 2007

select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)

From above query I get

mmddyyyyhhmm

but it' s yyyy and hour can not be separated

04/12/200702:05

How can I separated the year and hour ?

Thanks
Daniel

View 2 Replies View Related

NOLOCK Or Not To NOLOCK

Mar 9, 2005

I need some help to under stand when the right time is for NOLOCK. I work in a small dev group and NOLOCK seams to be a buzz word and others are throwing it in all over for no apparent reason.

I read the thing from http://www.sql-server-performance.com/ and I am sure that our web and SQL servers are about 100x over sized for the application. While are ASP.Net (VB) app may demonstrate some hesitation from time to time I am more inclined to blame poor VB.Net coding techniques before slow SQL. The point being the NOLOCK is being added to SELECTS that are not part of a transaction and were using the SQL data adapter to return datasets or single column values.

Also I am not even sure it’s being used correctly. The OLM has the example:
SELECT au_lname FROM authors WITH (NOLOCK)

However I am seeing it formatted like this:
SELECT au_lname FROM authors (NOLOCK)

I am by no mean an expert, I follow what I read in books or from examples from others. And I have never read in a book go crazy with NOLOCK because it’s the bomb!

Any thoughts? I am trying to learn as much as I can before I raise my hand and say this might be a bad idea.

Thanks

View 4 Replies View Related

NOLOCk

Jan 29, 1999

Other than "dirty" reads, what are the cons of using NOLOCK throughout a query?

View 1 Replies View Related

DTS- WITH (NOLOCK)

Mar 7, 2001

Hi,
I have a job that runs 3 seperate DTS packages.

The first step imports a file and runs successfully.

The second step which is the 2nd DTS package is hanging in the execute mode until I manually stop the job. Apparently,We discovered a bulk insert that is blocking a select statement--both proccesses are within this second DTS package. I tried using the WITH (UNLOCK) on the tables but this DTS package is still failing.

Does anyone have any suggestion? It would be greatly appreciated.

View 1 Replies View Related

Db2, Nolock

Mar 4, 2004

Hi guys,

I am a new user for DB2.

In DB2 V8, i have a table with some no of columns.

table test
col1
col2
col3
col4

Now i want to issue a query like this.

select col1 from test with(NOLOCK) where col1 = <some value>

when i am executing this query on that time i am getting a error message like this...

SQL0158N The number of columns specified for "MT.TEST" is not the
same as the number of columns in the result table. SQLSTATE=42811

Anyone can help me???

i tried to find out.

I did the same thing with SQL server 2000.
I am not facing any problem there.

Thanks & Regards,
Muthu

View 1 Replies View Related

NOLOCK Help

Jun 6, 2006

Paul Randal,

There has been a discussion/debate going on this thread about the benefits and drawbacks of using the NOLOCK hint:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67294

It occurred to me that you might know more about this than any of us, or at least be able to point us to a white paper or knowledge base article that explains the subject in more detail. Any light you can shed on the subject would be a big help.

CODO ERGO SUM

View 1 Replies View Related

With (nolock)

Jan 8, 2008

is really helpful use "with (nolock)" sentence in selects??

100% useful?? disadvantages?

best regards

Joseph

View 4 Replies View Related

NOLOCK On Views

Jun 2, 1999

Hello,
Does anyone know if you place NOLOCK after a view in a select statement, if the effects trickle down to the tables in the view? Or does one have to add NOLOCK to each table within the view?

Thanks

View 1 Replies View Related

NOLOCK Sentence

Jun 23, 2004

Hello !!

I'm using the sentence NOLOCK for selects, but I have many sentences, Is there any way to set a parameter in the DBMS, to use NOLOCK parameter by default ???? I mean, I don't like to lock any table for selects.

Is It possible ???? How to do It (step by step) ?

Thanks !!

View 13 Replies View Related

NOLOCK On Views

Nov 1, 2005

Hey guys,

I came across a SQL statement, thought up by a developer, in which two views were joined with the NOLOCK hint:
SELECT v1.xxx, v2.yyy
FROM dbo.vw_SomeView v1 WITH (NOLOCK)
INNER JOIN dbo.vw_SomeOtherView WITH (NOLOCK) ON v1.id = v2.id
The views are not created the NOLOCK hint. So my question is: has the NOLOCK hint any effect here?

I've looked in the BOL and searched on the net but can't find anything on this particular topic.

Lex

PS. Personally I don't like to use views in JOINs. I've seen too many cases in which tables are joined twice just because they are part of both views. Further more I don't like the "random" use of NOLOCK because most people don't seem to understand the implications of it. But this is besides the point of my question ;)

View 3 Replies View Related

Still Getting Blocked Despite WITH (NOLOCK)

Apr 5, 2006

I'm running a heavy SELECT query using WITH (NOLOCK). This still causes other processes trying to INSERT in one of the tables to get blocked.
I thought the locking hint would prevent from blocking other processes?

View 4 Replies View Related

NOLOCK And No Resources?

Mar 31, 2008

What will happen if I'm running a query with the NOLOCK hint but the SQL Server has run out of resources?

Thanks!

View 2 Replies View Related

NOLOCK Option ?

May 12, 2008

Hi,

I was sreading about NOLOCK that it could prevent deadlocks but could return data which is not committed yet.
1) Should we use NOLOCK with select statements
2) If the transaction isolation level is set appropriately (e.g. Serializable)in the component (for e.g COM+ component) but NOLOCK is specified in the select then would it return uncommitted data. I mean if the transaction is controlled at hihger level then what will be the Pros and Cons of using NOLOCK.

Thanks

View 3 Replies View Related

What Is The Purpose Of Using WITH NOLOCK

Aug 12, 2013

What is the purpose of using WITH(NOLOCK)?

View 3 Replies View Related

Nolock Problem

Dec 28, 2007

Hi!

select * from rubberstock_tbl_shoppingcarttemp
It dosent take datas from the table but

select * from rubberstock_tbl_shoppingcarttemp (nolock)
This querey takes the data from the table

what can i do to retrieve records through first query.I dont know what
is happening please explain and also dont know about nolock

Thanks in advance!

View 3 Replies View Related

NOLOCK Clarifications

Feb 25, 2008

When the NOLOCK hint is included in a SELECT statement, no locks are taken when data is read. This gives a Dirty Read in a result set.
The data retrieved in the select statement may not be correct since some other process might have updated the data while it was taken in the select statement.

Now, Can I know what is the use of NOLOCK? In which way we can make efficient use of NOLOCK ???

View 8 Replies View Related

Select (nolock)

Mar 11, 2008

Hi, I get an error when select * from a table with nolock....please help.

Cannot specify an index or locking hint for a remote data source

View 3 Replies View Related

Question About NOLOCK

Mar 30, 2008

Hi,

I have never used NOLOCK, or ROWLOCK, or anything of that sort. It's something I thought was over my head when I first encountered it and as I am gaining a better grip on SQL I have decided I want to try and tackle it. Also, because I think its causing me some problems :)

I recently implemented a new query into my application.

The query below gets the COUNT(*) of certain tables. Some of these tables are tall (5 million plus rows)

The counts returned are not absolutely critical information, and we are not joining or doing any other transactions based on this data. I believe running these counts on these pretty active tables (selects,inserts) are possibly causing slowdowns ?

Would using NOLOCK be an appropriate situation for something like this ? I am looking for a performance increase hopefully, and hoping there isnt much of a downside? I'm also not sure what type of reliability to expect from NOLOCK.

Any help much appreciated !!

Thanks once again
mike123

create PROCEDURE [dbo].[select_UserStats_Admin_TEST]
(
@userID int
)
AS SET NOCOUNT ON

DECLARE @Mail_Sent INT, @Mail_Received INT, @Comments_Sent INT, @Comments_Received INT,

@friendsListCount INT

SELECT @Mail_Sent = count(*) FROM tblMessage WHERE messageFrom = @userID
SELECT @Mail_Received = count(*) FROM tblMessage WHERE messageTo = @userID

SELECT @Comments_Sent = count(*) FROM tblComment WHERE CommentFromID = @userID
SELECT @Comments_Received = count(*) FROM tblComment WHERE CommentTo = @userID

SELECT @friendsListCount = count(*) FROM tblFriends WHERE userID = @userID

SELECT @Mail_Sent as Mail_Sent, @Mail_Received as Mail_Received, @Comments_Sent as Comments_Sent,
@Comments_Received as Comments_Received, @friendsListCount as friendsListCount

View 5 Replies View Related

With Nolock Problem

Feb 17, 2006

Hi,Is there a way to catch every select comming to sql and change it toselect with nolock?or how to make database READ UNCOMMITTED permanent?any ideas?Richard

View 3 Replies View Related

Deadlocks And Use Of Nolock

Jul 20, 2005

I am getting lot of deadlocks in my application. As it is very complexti avoid deadlocks at this stage of application we have done few stepsto lessen the impact.We have added retries after deadlock is capturted.We have added select * from TABLE with (nolock) wherever possible.But interestingly second step is not working. I have few simple selectstatements where i am using nolock criteria still I am gettingdeadlock victim error. Any idead why it happening. I thought as soonas I put nolock in the query it will ignore all the locks.My sp isCREATE procedure sp_Check_denomination@supply_till_idint,@product_codechar(4),@iso_currency_codechar(3),@denominationmoneyasdeclare @product_id numeric(5)select @product_id = product_id from product with (nolock) whereproduct_code = @product_codeif exists (select *from transaction_inventory TI with (nolock),product_ccy_denom PCD with (nolock)where TI.supply_till_id = @supply_till_idand TI.product_id = @product_idand TI.iso_currency_code= @iso_currency_codeand TI.denomination = @denominationand TI.product_id = PCD.product_idand TI.iso_currency_code = PCD.iso_currency_codeand TI.denomination = PCD.denominationand PCD.product_id=@product_idand PCD.denomination = @denominationand PCD.iso_currency_code=@iso_currency_codeand PCD.tradeable = 1)beginreturn(1)endelsebeginreturn(0)endGO

View 1 Replies View Related

When To Use The NOLOCK Hint.

Jul 20, 2005

Background:I am currently working on a mission critical web based applicationthat is accessed 24 hours a day by users from just about every timezone. We use MS SQL Server as our database and we have lots ofproblems with time-outs. We used to have lots of problems with locksuntil my management decided that we would use the WITH (NOLOCK) hinton EVERY select statement and WITH (ROWLOCK) on EVERY updatestatement. I have argued since the beginning that the NOLOCK hintshould be the exception and not the rule. Meanwhile we continue tohave problems related to time-outs.Problem:I'm the one that they call when there are time-out errors.I am a programmer first and a DBA when I have to be. I'd really liketo hear from some of you who are the opposite. I realize that thereare many factors that contribute to slow response from a databaseserver (indexes, RAM, disk speed, etc.), but what I really need tohear from an expert is whether or not using NOLOCK on **EVERY** queryin a 30GB database that has 344 tables is a bad idea.Thanks in advance,Stephen McMahonJoin Bytes!

View 2 Replies View Related

NOLOCK Deprecated?

Dec 5, 2007

Is the NOLOCK optimizer hint being deprecated? Or does this apply only to use of NOLOCK in UPDATE and DELETE statements or what exactly?

I can see that in this article that there are circumstances in which the hint is deprecated; however, I have developers that are acting like NOLOCK is about to be dropped entirely. As far as I know, that is not the case but I would definitely like some feedback on this.

View 5 Replies View Related