Stored Procs not doing the same thing on different servers

Stored Procs not doing the same thing on different servers

Post by Michael Loga » Tue, 24 Jul 2001 03:26:11



This SP works on one server but not another SQL Server. It is used to pull a
random row based on a UniqueIdentifier Column called AdID
Both are SQL Server 7.0, both base tables are exactly the same.  I have
tried dropping and recreating the table on the new server.

Here is the code.


AS
set nocount on
create table #ads (AdID uniqueidentifier)

insert #ads select AdID from internalads order by newid()
set rowcount 0
update InternalAds SET Shows = Shows + 1 Where AdID in (select AdID from
#ads)
select FilePath, AdLink, AltText,AdID, adWidth, AdHeight from InternalAds
where AdID in (select AdID from #ads) order by newid()
drop table #ads
set nocount off

TIA

Your help is greatly appreciated and most urgent

Mike

 
 
 

Stored Procs not doing the same thing on different servers

Post by Michael Loga » Tue, 24 Jul 2001 05:00:41


I keep getting the same row everytime

TIA

Mike

> This SP works on one server but not another SQL Server. It is used to pull
a
> random row based on a UniqueIdentifier Column called AdID
> Both are SQL Server 7.0, both base tables are exactly the same.  I have
> tried dropping and recreating the table on the new server.

> Here is the code.

> Create proc GetAds

> AS
> set nocount on
> create table #ads (AdID uniqueidentifier)

> insert #ads select AdID from internalads order by newid()
> set rowcount 0
> update InternalAds SET Shows = Shows + 1 Where AdID in (select AdID from
> #ads)
> select FilePath, AdLink, AltText,AdID, adWidth, AdHeight from InternalAds
> where AdID in (select AdID from #ads) order by newid()
> drop table #ads
> set nocount off

> TIA

> Your help is greatly appreciated and most urgent

> Mike


 
 
 

Stored Procs not doing the same thing on different servers

Post by Dan Guzma » Tue, 24 Jul 2001 02:10:47


Are both servers running the same OS?  I believe there is a difference in
behavior between with NEWID between NT 4.0 and Windows 2000.

Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------


> I keep getting the same row everytime

> TIA

> Mike


> > This SP works on one server but not another SQL Server. It is used to
pull
> a
> > random row based on a UniqueIdentifier Column called AdID
> > Both are SQL Server 7.0, both base tables are exactly the same.  I have
> > tried dropping and recreating the table on the new server.

> > Here is the code.

> > Create proc GetAds

> > AS
> > set nocount on
> > create table #ads (AdID uniqueidentifier)

> > insert #ads select AdID from internalads order by newid()
> > set rowcount 0
> > update InternalAds SET Shows = Shows + 1 Where AdID in (select AdID from
> > #ads)
> > select FilePath, AdLink, AltText,AdID, adWidth, AdHeight from
InternalAds
> > where AdID in (select AdID from #ads) order by newid()
> > drop table #ads
> > set nocount off

> > TIA

> > Your help is greatly appreciated and most urgent

> > Mike

 
 
 

Stored Procs not doing the same thing on different servers

Post by Michael Loga » Tue, 24 Jul 2001 05:19:49


Yes, both are running the same OS win2k.  I don't know what it could be.

> Are both servers running the same OS?  I believe there is a difference in
> behavior between with NEWID between NT 4.0 and Windows 2000.

> Hope this helps.

> -----------------------
> SQL FAQ links (courtesy  Neil Pike):

>  http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
>  (faqxxx.zip in lib 7)
>  or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
>  or www.sqlserverfaq.com
>  or www.mssqlserver.com/faq
> -----------------------



> > I keep getting the same row everytime

> > TIA

> > Mike


> > > This SP works on one server but not another SQL Server. It is used to
> pull
> > a
> > > random row based on a UniqueIdentifier Column called AdID
> > > Both are SQL Server 7.0, both base tables are exactly the same.  I
have
> > > tried dropping and recreating the table on the new server.

> > > Here is the code.

> > > Create proc GetAds

> > > AS
> > > set nocount on
> > > create table #ads (AdID uniqueidentifier)

> > > insert #ads select AdID from internalads order by newid()
> > > set rowcount 0
> > > update InternalAds SET Shows = Shows + 1 Where AdID in (select AdID
from
> > > #ads)
> > > select FilePath, AdLink, AltText,AdID, adWidth, AdHeight from
> InternalAds
> > > where AdID in (select AdID from #ads) order by newid()
> > > drop table #ads
> > > set nocount off

> > > TIA

> > > Your help is greatly appreciated and most urgent

> > > Mike

 
 
 

Stored Procs not doing the same thing on different servers

Post by BP Margoli » Tue, 24 Jul 2001 04:10:40


Michael,

While a number of people have suggested the ORDER BY NEWID ( ), both
Umachandar Jayachandran and myself have argued against it. You are depending
upon undocumented behavior of the NEWID ( ) function. Depending upon
undocumented behavior is never a good thing.

A few comments ...

1. Since a table is by definition an unordered set of rows, adding the ORDER
BY NEWID ( ) to the INSERT statement is just a waste of CPU cycles.

2. If you want to return information in a random sequence, then try
something like:

select *
from {table}
order by rand(1000.0 * AdID * datepart(ms, current_timestamp))

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> This SP works on one server but not another SQL Server. It is used to pull
a
> random row based on a UniqueIdentifier Column called AdID
> Both are SQL Server 7.0, both base tables are exactly the same.  I have
> tried dropping and recreating the table on the new server.

> Here is the code.

> Create proc GetAds

> AS
> set nocount on
> create table #ads (AdID uniqueidentifier)

> insert #ads select AdID from internalads order by newid()
> set rowcount 0
> update InternalAds SET Shows = Shows + 1 Where AdID in (select AdID from
> #ads)
> select FilePath, AdLink, AltText,AdID, adWidth, AdHeight from InternalAds
> where AdID in (select AdID from #ads) order by newid()
> drop table #ads
> set nocount off

> TIA

> Your help is greatly appreciated and most urgent

> Mike

 
 
 

Stored Procs not doing the same thing on different servers

Post by Dan Guzma » Tue, 24 Jul 2001 04:10:09


You might take a look at the procedure execution plans on each server to see
if there is a difference in the INSERT plan.

Hope this helps.


Quote:> Yes, both are running the same OS win2k.  I don't know what it could be.

 
 
 

Stored Procs not doing the same thing on different servers

Post by Russell Field » Tue, 24 Jul 2001 11:21:12


Michael,

        This may be a little off the wall, but I seem to remember some
problem with GUIDs on an NT4 machine if there was no network card in it.
What I remember does not exactly match your description, but does this
describe the
failing machine?

Russell Fields, SQL Server MVP


>This SP works on one server but not another SQL Server. It is used to pull
a
>random row based on a UniqueIdentifier Column called AdID
>Both are SQL Server 7.0, both base tables are exactly the same.  I have
>tried dropping and recreating the table on the new server.

>Here is the code.

>Create proc GetAds

>AS
>set nocount on
>create table #ads (AdID uniqueidentifier)

>insert #ads select AdID from internalads order by newid()
>set rowcount 0
>update InternalAds SET Shows = Shows + 1 Where AdID in (select AdID from
>#ads)
>select FilePath, AdLink, AltText,AdID, adWidth, AdHeight from InternalAds
>where AdID in (select AdID from #ads) order by newid()
>drop table #ads
>set nocount off

>TIA

>Your help is greatly appreciated and most urgent

>Mike

 
 
 

1. Stored Procs: Comparing across different physical servers

Hi,

I'm trying to coordinate three SQL Server 6.5 databases that exist on
different
physical NT 4.0 servers, running SQL 6.5 SP4. The names of the servers are
dev,qa, production.

Each database contains 50+ stored procs.
I'm looking for an easy way to compare the stored procs on each database.
I want to be able to validate that the stored procs that exist on each
database are exactly the same,
as far as executable SQL goes.
I want it to ignore comments, mainly because when stored procs are installed
or modified,
SQL Server inserts a comment in the middle of it.

 Kind of like a 'WINDIFF' for SQL Server 6.5.

Anybody know of a good tool, or way of doing this?

2. OCP DBA

3. ADO, Parameters and SQL Strings (not stored procs) in SQL Server

4. Verifying a tables LOCK MODE

5. SQL Server doing wierd things...

6. Error Trapping

7. Things look different on different PCs ????

8. paradox 7--drop list problems

9. Doing a restore to a different machine name on SQL Server

10. Stored Procs work in isql, but not in ADO

11. When are stored procs not the best solution?

12. Deferred name resolution in stored procs and triggers but not views

13. Help !!! Stored Procs are not returning output parameters