Differing results on identical systems.

I have a staging environment and a developement environment set up on the same machine.
Both environments consist of two databases, a staging area and a data warehouse.

On the staging system when I run a query of the form:
select
count(*)
from [StagingArea].[dbo].[saTable]
join [DataWarehouse].[dbo].[dwView] on saTable.col = dwView.col
join [DataWarehouse].[dbo].[dwTable1] on dwTable1.col = dwView.col
join [DataWarehouse].[dbo].[dwTable2] on (dwTable1.col = dwTable2.col AND dwTable2.col = saTable.col)
I get an answer returned in 2 seconds.

When I run the same query on the developement system it never comes back with an answer (after 3+ hours).

The data in both systems is identical. But it would appear that the query optimizer is behaving differently on the two identical systems.

I am looking for a way to enforce the same behavior in both systems. Any tips would be appreciated.

Thanks.
-PMP

have u checked statistics on development , there may be lot of insert/update/delete action going on Develpment server for testing purpose. So reindex the tables invoved and run sp_updatestats.

Madhu

Different Server Names

Hello,

I am using ADO controls for MSSQL Database. The server name on my machine is different from the server name on the machine where i am running my application. When i make changes to my application, i have to rebuild the connection on my ADO Control. Is there a way to create a server name on my developement machine to match the server name where i run my application?

Would appreciate any help.Hi Ed

Welcome to DBForums :D

Don't bother buggering about with instance names. Just create some control procedure in your front end code that works out "If I am running in development mode on this machine connect to server A, otherwise connect to server B".|||hello pootle flump

thanks for the reply.

i have created a variable which contains the datasource string. i change the datasource string when on development with my machines servername and switch the servername to the working machine. it displays a message that it cant find the server. is there something i missed?|||Which one displays the message? What is your connection string? Are either of the instances SQL Server Express? Have you installed Visual Studio 2005\ 2003 on your workstation?|||pDataSource$ = "Development"
pUserId$ = "sa"

pConnectionString$ = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" + pUserId$ + ";Initial Catalog=Ormon;Data Source=" + pDataSource$

ADOControl.ConnectionString = pConnectionString$

when i want to run the application in the actual environment i simply change the pDataSource$ value to "Actual"

unfortunately, the "server cannot be found" error occurs|||Can you connect to either? You are not specifying a password for sa. Also, sa is a very poor choice for a login for an application.

Please also look at my questions again - you missed a couple.

Different kind of connection issue

Was able to solve my connection issues with my local (test) machine. However, now I've got a much bigger issue on my intranet machine (W2K Server that acts as a domain controller).

When I use essentially the same set up for connecting as I did for the other machine (adjusted for machine name, etc), I get the following message:

Login failed for user '[username]'. The user is not associated with a trusted SQL Server connection.

I went into the SQL Server Express Config, and discovered under "SQL Server 2005 Services>SQL Server(SQLEXPRESS)>Properties" that this service is set up to login as the Local System built-in account. When I try to change this to Network Service, I get the following popup error message:

Cannot perform this operation on built-in accounts. [0x8007055b]

1) This happens regardless of wheter I stop the service and try to reset, apply and restart, or simply try to change it while it's running

2) I can find nothing on the error code above anywhere on the Web (Google came up EMPTY).

Anyone have any ideas about this one?

Seems like your SQL Server is setup for WIndows Authentication only. So if you want to use mixed (and therefore also SQL Server authentication) you have to configure the Server for this, in the administion console or with the registry settings:

--
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q285097
--
INF: How to Change the Default Login Authentication Mode to SQL While
Installing SQL Server 2000 Desktop Engine by Using Windows Installer
--
<snip>
Another way to change the security mode after installation is to stop
SQL Server and set the appropriate registry key for your installation:

Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode

to 2 for mixed-mode or 1 for integrated. (Integrated is the default
setup for the SQL Server 2000 Data Engine.)
</snip>

-URL-

If you don′t wanna use mixed authentication and only Windows Auth. you have to provide the "Integrated Authentication=True" in the connectionstring rather the UserId / Pwd.

HTH, Jens Suessmeyer.

|||

Changing the registry key worked.

One minor issue though - I tried the "Integrated Authentication=True" solution before I tried the registry key solution, and I got an error message saying that this was not a valid keyword or some such. Any idea why? Just Curious.

|||

Sotty mixed the two thing up, its "Trusted Connection=true" or Integrated Security=True, try these one, the connection string (just ti keep this in mind for you) can be found here:

http://www.connectionstrings.com

HTH; Jens Suessmeyer.

Different Default Date format for 2 SQL Server Instances

Hi,
We recently had a new environment created. The servers were all installed
as separate instances on the same physical machine.
For my first instance INST1
When I execute the following query exec getMyData '1964-11-19'
in query analyser everything is fine
from my (ASP) website everything is fine
For my second instance INST2
in query analyser everything is fine
from my asp website I get varchar cannot be converted to datetime.
I can only believe that the default settings for the server were different
when each of the SQL Server Installs were performed.
I cannot change the way we pass dates in our website because it is a massive
re-write of everytihg if I do.
Is there some why of changing the default settings of the server after
installing?
I have tried using
sp_configure
SET language
sp_defaultlanguage
and all these methods did not fix my problem.
Any ideas?
JayneK wrote:
> Hi,
> We recently had a new environment created. The servers were all
> installed as separate instances on the same physical machine.
> For my first instance INST1
> When I execute the following query exec getMyData '1964-11-19'
> in query analyser everything is fine
> from my (ASP) website everything is fine
> For my second instance INST2
> in query analyser everything is fine
> from my asp website I get varchar cannot be converted to datetime.
> I can only believe that the default settings for the server were
> different when each of the SQL Server Installs were performed.
> I cannot change the way we pass dates in our website because it is a
> massive re-write of everytihg if I do.
> Is there some why of changing the default settings of the server after
> installing?
> I have tried using
> sp_configure
> SET language
> sp_defaultlanguage
> and all these methods did not fix my problem.
> Any ideas?
When working with dates in character format, you should only ever use a
portable format. Two formats are supported that will never cause
problems related to the server's regional settings:
yyyy-mm-ddThh:mm:ss.mmm (no spaces)
yyyymmdd
What is probably occurring is that one server is using MDY format and
the other is using DMY.
For example:
SET NOCOUNT ON
SET DATEFORMAT MDY
SELECT CAST('1964-11-19' as DATETIME)
SET DATEFORMAT DMY
SELECT CAST('1964-11-19' as DATETIME)
-- Results
1964-11-19 00:00:00.000
Server: Msg 242, Level 16, State 3, Line 9
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
David Gugick - SQL Server MVP
Quest Software
|||David,
Unfortunately they all have all their language setting exactly the same. So
they are all set to us_english as default language, yet one server acts
differently to the other.
How can we fix this? do we have to uninstall and re-install, can't we hack
a file or something?
"David Gugick" wrote:

> JayneK wrote:
> When working with dates in character format, you should only ever use a
> portable format. Two formats are supported that will never cause
> problems related to the server's regional settings:
> yyyy-mm-ddThh:mm:ss.mmm (no spaces)
> yyyymmdd
>
> What is probably occurring is that one server is using MDY format and
> the other is using DMY.
> For example:
> SET NOCOUNT ON
> SET DATEFORMAT MDY
> SELECT CAST('1964-11-19' as DATETIME)
> SET DATEFORMAT DMY
> SELECT CAST('1964-11-19' as DATETIME)
> -- Results
> 1964-11-19 00:00:00.000
> Server: Msg 242, Level 16, State 3, Line 9
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
>
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
|||JayneK wrote:[vbcol=seagreen]
> David,
> Unfortunately they all have all their language setting exactly the
> same. So they are all set to us_english as default language, yet one
> server acts differently to the other.
> How can we fix this? do we have to uninstall and re-install, can't
> we hack a file or something?
> "David Gugick" wrote:
The ASP web site client is likely set up different. Go to that PC, open
up QA, and run the example above. The problem is that you are not using
a portable date format and are bound to run into these types of
problems. If you get rid of the hyphens in the date parameter, that will
probably fix the issue. Since you cannot easily change the code
executing the date procedure you wrote, why not just change the
procedure itself to strip the hyphens out using Set @.MyDate =
REPLACE(@.MyDate, '-', '')
David Gugick - SQL Server MVP
Quest Software