Problems installing MSDE Desktop SP3a SQL server

Hi,
I'm trying to install MSDE Desktop SP3a SQL server on my Windows 2000 Professional Laptop.
Every time I try it nearly finishes the installation and then just rollsback.
Can anyone help? Is this a known issue?
Thanks in advance,
JockyHave you tried using the verbose logging switch when you install? You need to add something like this to the setup.exe you are running fromthe commandline:

/L*v c:\msde_install.log

|||The following article might help you setup MSDE from command line.
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q324998|||

That Microsoft article did the trick. Thanks y'all!

Jocky.

Problems Connecting to Database

I am having a hard time getting my registration form to connect to my database via a stored procedure. Any help would be appreciated. The code is listed below

protectedvoid submit_Click(object sender,EventArgs e)

{

SqlConnection connection =

newSqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);

SqlCommand command =

newSqlCommand("GE_sp_INSERT_USER", connection);

command.Parameters.Add("@.FirstName",SqlDbType.NVarChar).Value = txtFirstName.Text;

command.Parameters.Add("@.LastName",SqlDbType.NVarChar).Value = txtLastName.Text;

command.Parameters.Add("@.UserName",SqlDbType.NVarChar).Value = txtUserName.Text;

command.Parameters.Add("@.Password",SqlDbType.NVarChar).Value = txtPassword.Text;

command.Parameters.Add("@.PhoneNumber",SqlDbType.NVarChar).Value = txtPhoneNumber.Text;

command.Parameters.Add("@.EmailAddress",SqlDbType.NVarChar).Value = txtEmailAddress.Text;

command.Parameters.Add("@.Address",SqlDbType.NVarChar).Value = txtAddress.Text;

command.Parameters.Add("@.City",SqlDbType.NVarChar).Value = txtCity.Text;

command.Parameters.Add("@.State",SqlDbType.NVarChar).Value = txtState.Text;command.Parameters.Add("@.ZipCode",SqlDbType.NVarChar).Value = txtZipCode.Text;

connection.Open();

command.ExecuteNonQuery();

connection.Dispose();

}

ALTER PROCEDUREdbo.GE_sp_INSERT_USER

(

@.FirstNamenvarchar(50),

@.LastNamenvarchar(50),

@.UserNamenvarchar(50),

@.Passwordnvarchar(50),

@.PhoneNumbernvarchar(50),

@.EmailAddressnvarchar(50),

@.Addressnvarchar(50),

@.Citynvarchar(50),

@.Statenvarchar(50),@.ZipCodenvarchar(50)

)

AS

INSERT INTOUSERS

(FirstName, LastName, UserName, Password, PhoneNumber, EmailAddress, Address, City, State, ZipCode)

VALUES

(@.FirstName, @.LastName, @.UserName, @.Password, @.PhoneNumber, @.EmailAddress, @.Address, @.City, @.State, @.ZipCode)

RETURN

You say you are having a hard time "connecting" to your database. What exactly is the problem you're having? Are you getting an error?

|||

You need to set the CommandType:

command.CommandType =CommandType.StoredProcedure;

|||

I have added the code below to my code - I placed it right below my sqlCommand. - it still does not work. the error says it cannot find the stored procedure. The webconfig looks correct.

SqlCommand command =newSqlCommand("GE_sp_INSERT_USER", connection);

command.CommandType =CommandType.StoredProcedure;

thanks for your help|||

That error message has meaning.

Try this:

SqlCommand command =newSqlCommand("dbo.GE_sp_INSERT_USER", connection);

|||

That did not work either. Below is my webconfig - I may be missing something easy.

<connectionStrings>

<addname="myConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\gevjen.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

</connectionStrings>

thanks again for sticking with this to help me out...

|||

A few things

1) Is this connection string working somewhere else in your application?

2) If you connect to your db manually and execute the command do you get any result?

|||

hey i am too getting a hard time in making connection to sqlserver,like i have made a connection object and its working but when i reach the open function nothing works,i had been working over this since last 4 days!please guide me how to do it!

|||The database works elsewhere and the stored procedure works as well. I am missing something easy I believe within the connection or webconfig.|||

gevjen:

The database works elsewhere and the stored procedure works as well. I am missing something easy I believe within the connection or webconfig.

Could you copy and paste the actual error message, and the show the line that causes it?

|||

kamna:

hey i am too getting a hard time in making connection to sqlserver...

This should help you:http://www.mikesdotnetting.com/Article.aspx?ArticleID=69

sql

Problems calling ::fn_helpcollations() on MS SQL 7.0

Hi.
I have som date/time inconsistency between a MS SQL 7.0 srv. database which
I built up on a MS SQL 2000 srv. database. I was told to use the SELECT *
FROM ::fn_helpcollations() to find out the settings on my (old) 7.0
database, but it fails (incorrect syntax near ':'.). What's the correct
syntax on MS SQL 7.0?
Regards
Mr. SmithHi
There is no correct syntax for SQL Server 7.0 and Functions as Functions are
not supported in SQL Server 7.0.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:Olby%23$CxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi.
> I have som date/time inconsistency between a MS SQL 7.0 srv. database
> which I built up on a MS SQL 2000 srv. database. I was told to use the
> SELECT * FROM ::fn_helpcollations() to find out the settings on my (old)
> 7.0 database, but it fails (incorrect syntax near ':'.). What's the
> correct syntax on MS SQL 7.0?
> Regards
> Mr. Smith
>

Problems calling ::fn_helpcollations() on MS SQL 7.0

Hi.
I have som date/time inconsistency between a MS SQL 7.0 srv. database which
I built up on a MS SQL 2000 srv. database. I was told to use the SELECT *
FROM ::fn_helpcollations() to find out the settings on my (old) 7.0
database, but it fails (incorrect syntax near ':'.). What's the correct
syntax on MS SQL 7.0?
Regards
Mr. SmithHi
There is no correct syntax for SQL Server 7.0 and Functions as Functions are
not supported in SQL Server 7.0.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:Olby%23$CxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi.
> I have som date/time inconsistency between a MS SQL 7.0 srv. database
> which I built up on a MS SQL 2000 srv. database. I was told to use the
> SELECT * FROM ::fn_helpcollations() to find out the settings on my (old)
> 7.0 database, but it fails (incorrect syntax near ':'.). What's the
> correct syntax on MS SQL 7.0?
> Regards
> Mr. Smith
>

Problems calling ::fn_helpcollations() on MS SQL 7.0

Hi.
I have som date/time inconsistency between a MS SQL 7.0 srv. database which
I built up on a MS SQL 2000 srv. database. I was told to use the SELECT *
FROM ::fn_helpcollations() to find out the settings on my (old) 7.0
database, but it fails (incorrect syntax near ':'.). What's the correct
syntax on MS SQL 7.0?
Regards
Mr. Smith
Hi
There is no correct syntax for SQL Server 7.0 and Functions as Functions are
not supported in SQL Server 7.0.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:Olby%23$CxFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi.
> I have som date/time inconsistency between a MS SQL 7.0 srv. database
> which I built up on a MS SQL 2000 srv. database. I was told to use the
> SELECT * FROM ::fn_helpcollations() to find out the settings on my (old)
> 7.0 database, but it fails (incorrect syntax near ':'.). What's the
> correct syntax on MS SQL 7.0?
> Regards
> Mr. Smith
>

Problems backing up SQL DB.

Every time i try to back up a specific DB i get an error saying it can
not backup because the full text catalog is not permitted because it
is not online. It does not matter if i try to backup the db through
SQL of my Backup software. here is the sql log.

LogJob History (DB File level Backup.Subplan_1)

Step ID1
ServerServer Name
Job NameDB File level Backup.Subplan_1
Step NameSubplan_1
Duration00:00:01
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0

Message
Executed as user: Domain\USer. ...ecute Package Utility Version
9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All
rights reserved. Started: 7:07:43 AM Progress: 2007-06-01
07:07:44.14 Source: {B6285A6A-0066-416F-AD9F-F31ED5A68F14}
Executing query "DECLARE @.Guid UNIQUEIDENTIFIER EXECUTE
msdb..sp".: 100% complete End Progress Error: 2007-06-01
07:07:44.49 Code: 0xC002F210 Source: Back Up Database (Full)
Execute SQL Task Description: Executing the query "BACKUP DATABASE
[DBName] FILEGROUP = N'PRIMARY' TO DISK = N'E:\Program Files
\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DBName_200706010707.ba k'
WITH NOFORMAT, NOINIT, NAME = N'DBName_backup_20070601070744', SKIP,
REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "The
backup of full-text catalog 'ftcat_documentindex' is not permitted
because it is not online. Check errorlog file for the reason that full-
text catalog became offline and . The step failed.Bryan (bsockel@.gmail.com) writes:

Quote:

Originally Posted by

Every time i try to back up a specific DB i get an error saying it can
not backup because the full text catalog is not permitted because it
is not online. It does not matter if i try to backup the db through
SQL of my Backup software. here is the sql log.

Searching Books Online on "fulltext offline", I found this link:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlerrm9/html/592bd03e-7f46-4b88-9bd0-
7a0b32cbfd58.htm

It seems that there is a chance that you find the answer there.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Problems after SP4 - 100%CPU time

We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of the
databases runs at 100% CPU when ever a query it made. Nothing else has
changed! Before SP4 the queries are quite small and took only 1-2 seconds to
run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
If I copy the database to another server with SP3 on it, it works fine. If I
then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds.
The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN with
RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
temp, msdb, etc And monitoring them there seems to be no disk problems,
queues are short, etc
Can anyone help? either hot fix, uninstall (don't have SP3 backup of master,
etc), sp changes.
Cheers
nigelDid you run update_statistics , reindex database.
Maybe recompile stored procedures!
Greetz
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"Nigel" wrote:

> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of th
e
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
I
> then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds
.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN wit
h
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of maste
r,
> etc), sp changes.
> Cheers
> nigel|||Thanks.
I've run these manually and as part of the daily maintenance plan. The
database also has Auto update and create statistics.
At present the part where it all goes mad does not use any SP, but is called
via a JDBC Java client directly.
Task Manager view can be seen here.
http://www.callacomp.co.uk/sql/orion%20cpu.jpg
Performance monitor SP3 (live)
http://www.callacomp.co.uk/sql/sp3mon.jpg
Performance monitor SP4(test machine)
http://www.callacomp.co.uk/sql/sp4mon.jpg
Nigel
Underpaid and working for the NHS.
So help me and save lives!
"Hate_orphaned_users" wrote:

> Did you run update_statistics , reindex database.
> Maybe recompile stored procedures!
> Greetz|||Nigel wrote:
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of th
e
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
I
> then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds
.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN wit
h
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of maste
r,
> etc), sp changes.
> Cheers
> nigel
Have you reviewed the execution plan for one of the problem queries?
Did you update statistics (manually, not automatically) when you moved
the database?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It sounds like the query plan has changed so you may need to tune or change
that query slightly so that it gets a better plan.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:85602962-D3D7-4951-8E08-4C70D67C4855@.microsoft.com...
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of
> the
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
> to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
> I
> then install SP4 on this machine it now runs at 100% CPU for 30-50
> seconds.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN
> with
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of
> master,
> etc), sp changes.
> Cheers
> nigel|||I've updated statistics -sp_updatestats
I've updated all the indexes DBCC DBREINDEX(@.TableName,' ',90)
The process uses no SP all queries are suppled from the client using JDBC,
doesn't this mean that the query plan is updated as and when. Certainly afte
r
a reboot? Or am I wrong?
I've run PSSDiag but I'm no expert at looking at the traces files. Either a
little help or lots of reading to do?
Regatds
NIgel|||It may very well generate a new query plan each and every time you execute
it. But that does not mean it will generate a good one or even a different
one. If it generated a bad plan the last time you executed it and the
conditions or parameters have not changed chances are it will generate a bad
one again and again and again. Some times the conditions are such or the
data is such that the optimizer thinks it has the right plan but ends up
with a bad one for your conditions. By changing the query slightly ( an IN
to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a different
and in this case hopefully better plan.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:5B61E38E-18CF-4735-B15E-AC81C2460D35@.microsoft.com...
> I've updated statistics -sp_updatestats
> I've updated all the indexes DBCC DBREINDEX(@.TableName,' ',90)
> The process uses no SP all queries are suppled from the client using JDBC,
> doesn't this mean that the query plan is updated as and when. Certainly
> after
> a reboot? Or am I wrong?
> I've run PSSDiag but I'm no expert at looking at the traces files. Either
> a
> little help or lots of reading to do?
> Regatds
> NIgel|||OK Cheers, I start looking into this. So does this mean that SP4 has changed
how query plans are calculated?
--
Nigel
Underpaid and working for the NHS.
So help me and save lives!
"Andrew J. Kelly" wrote:

> It may very well generate a new query plan each and every time you execute
> it. But that does not mean it will generate a good one or even a different
> one. If it generated a bad plan the last time you executed it and the
> conditions or parameters have not changed chances are it will generate a b
ad
> one again and again and again. Some times the conditions are such or the
> data is such that the optimizer thinks it has the right plan but ends up
> with a bad one for your conditions. By changing the query slightly ( an IN
> to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a different
> and in this case hopefully better plan.
>
> --
> Andrew J. Kelly SQL MVP
>|||Every service pack and or edition has the potential to change query plans to
some extent and Sp4 is no exception. Most of the time it is due to fixing
bugs and occasionally a query plan may be affected by it. This does not mean
all of them will change.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:0DD973D6-5D6C-4633-B533-350115E0477C@.microsoft.com...[vbcol=seagreen]
> OK Cheers, I start looking into this. So does this mean that SP4 has
> changed
> how query plans are calculated?
> --
> Nigel
> Underpaid and working for the NHS.
> So help me and save lives!
>
> "Andrew J. Kelly" wrote:
>|||Hi, you need at least apply postSp hotfix (2187) and check again.
Regards,
Oleg.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uOSI3EpPHHA.1252@.TK2MSFTNGP02.phx.gbl...
> It may very well generate a new query plan each and every time you execute
> it. But that does not mean it will generate a good one or even a different
> one. If it generated a bad plan the last time you executed it and the
> conditions or parameters have not changed chances are it will generate a
> bad one again and again and again. Some times the conditions are such or
> the data is such that the optimizer thinks it has the right plan but ends
> up with a bad one for your conditions. By changing the query slightly ( an
> IN to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a
> different and in this case hopefully better plan.
>
> --
> Andrew J. Kelly SQL MVP
> "Nigel" <Nigel@.discussions.microsoft.com> wrote in message
> news:5B61E38E-18CF-4735-B15E-AC81C2460D35@.microsoft.com...
>

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmaz
themarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmazthemarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmazthemarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator

Problem: Global DB connection using web.config

I spent a lot of time trying to fix this. I followed example from the web and I ALWAYS GET THE SAME PROBLEM

System.InvalidOperationException: The ConnectionString property has not been initialized.

What I am missing? I really need to fix this. Can anyone help me?

Thanks

In my web.config
<appSettings
 <add key="connectionstring" value="server=homesvr02;uid=sa;pwd=database=Property"/>
</appSettings
In my asp.net page
<%@. Page Language="VB" %>
<%@. Import Namespace="System.Configuration" %>
<%@. import Namespace="System.IO" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SQLClient" %
 Dim strConn As String = ConfigurationSettings.AppSettings("ConnectionString")

MY ERROR

System.InvalidOperationException: The ConnectionString property has not been initialized.Please show the rest of your code.

Are you creating an instance of SqlConnection?

Are you setting it's ConnectionString property?|||Little error :-)

See this line:

::<add key="connectionstring"
::value="server=homesvr02;uid=sa;pwd=database=Property"/
and then this line:

::Dim strConn As String = ConfigurationSettings.AppSettings("ConnectionString")

I know you come from a VB background - in XML, though casing does matter.

connectionstring != ConnectionString.|||good call there thona. I would have missed that. I think everyone proper cases everything.|||1.
<thona>
::Dim strConn As String = ConfigurationSettings.AppSettings("ConnectionString")

I know you come from a VB background - in XML, though casing does matter.

connectionstring != ConnectionString.
</thona
<KraGiE>
good call there thona. I would have missed that. I think everyone proper cases everything.
</KraGiE
* Well, a quick test using VB.NET and C# said otherwise.

Tricky or Inconsistency. Who would expect that? :-D

* C#
Response.Write(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"]);

* VB.NET
Response.Write(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))

Both gives

server=homesvr02;uid=sa;pwd=database=Property

2. Can you guys do a quick test to see too?

3. web.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
 <appSettings>
 <add key="connectionstring" value="server=homesvr02;uid=sa;pwd=database=Property"/>
 </appSettings>

|||phouc is right. on both 2002 and 2003. impressive. twice in one day. remarkable

Problem: Trigger and multiple updates to the table

Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.

UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20

Error I am getting is :

Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

What is the best possible way to make it work? Thank you.Figure out which trigger is blowing up, and fix it? Knowing that there is more code than we can see, I can't for the life of me figure out a way to help you find out where the problem is, much less how to fix it.

-PatP|||I guess this is the problematic trigger. Should I use cursor to handle multiple inserts/updates?

CREATE trigger t_thickupdate on dbo.sched_lot_hdr
for update
as

-- declare local variables
declare
@.lotno int,
@.charthick varchar(10),
@.decthick decimal(6,5)

-- populate local variables
set @.lotno = (select lotno from inserted)
set @.charthick = (select matcharthick from inserted)
set @.decthick = (select matthick from inserted)

-- determine which was updated: character thickness or decimal thickness
if update(matthick)

-- decimal thickness was updated
begin
set @.charthick = (select thkfrac from prod_thk_stds where thkdec = @.decthick)
update sched_lot_hdr set matcharthick = @.charthick where lotno = @.lotno
end
else

-- character thickness was updated
begin
set @.decthick = (select thkdec from prod_thk_stds where thkfrac = @.charthick)
update sched_lot_hdr set matthick = @.decthick where lotno = @.lotno
end|||A trigger act on 1 or more rows...depending on the DML

inserted and/or deleted can have more than 1 row...

You need to al;ter your thinking and think more set based...|||Also looking at it...

Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense...|||I'd suggest using:CREATE TRIGGER t_thickupdate
ON dbo.sched_lot_hdr
FOR UPDATE
AS

-- determine which was updated: character thickness or decimal thickness
IF Update(matthick) -- decimal thickness was updated
UPDATE sched_lot_hdr
SET matcharthick = s.thkfrac
FROM inserted AS i
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)
JOIN prod_thk_stds AS s
ON (s.thkdec = i.matthick)
ELSE -- character thickness was updated
update sched_lot_hdr
set matthick = s.thkdec
FROM inserted AS i
JOIN prod_thk_stds AS s
ON (s.thkfrac = i.matcharthick)
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)

RETURNNote that you need to test this up one side and down the other before you put it into production, this was a quick paste up job with no opportunity for me to test it!

-PatP|||yeah :(

This already been developed. I came to this project recently and working on some enhancements. Thanks for the help. I think I understand the problem, I will resolve it.

Update: thats fast. Thanks Pat, I will test it.|||Originally posted by Brett Kaiser
A trigger act on 1 or more rows...depending on the DML

inserted and/or deleted can have more than 1 row...

You need to al;ter your thinking and think more set based... Yea vous! I'm with you on that!
Originally posted by Brett Kaiser
Also looking at it...

Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense... Nah, that happens all the time when folks convert from what I call "unit-record" code to a database. They find ways to get the database to "clean up" data coming from disparate sources that would have required application changes when the only thing that could reach the data was their application.

-PatP

problem with win2000 cluster and sql 2000 ent

Hi,
we have a win2000 adv cluster with sql server 2000 ent.
last time i had to move resources from active node to passive node, i
had big troubles.
Moving groups caused many sql db files damaged, having db marked
'suspect'
Why ? Do you know any possible reason ?
This cluster does not have a different drive for quorum disk and i
think that files needed by cluster service (log, checkpoint, ...) are
on the only shared disk, where are stored all db files.
Could be this the reason ?
Thanks in advance...
I'm not even going to ask how you got this to work in this configuration.
But, yes, it would be a problem.
Although, I suspect, that a bigger problem is that you have some form of
Disk Write Back Caching enabled.
I'd look through you server's System Event logs for various disk related
errors.
Normally, SQL Server REQUIRES a dedicated resource group, with an
independent Disk, IP Address, and Network Name resource, although the SQL
Server setup program will add the IP and Name resources for you (it will
actually bomb if those resources already exist in the group).
Sincerely,
Anthony Thomas

"The Bedroom" <thebedroom71@.gmail.com> wrote in message
news:1163973423.582387.314090@.m7g2000cwm.googlegro ups.com...
> Hi,
> we have a win2000 adv cluster with sql server 2000 ent.
> last time i had to move resources from active node to passive node, i
> had big troubles.
> Moving groups caused many sql db files damaged, having db marked
> 'suspect'
> Why ? Do you know any possible reason ?
> This cluster does not have a different drive for quorum disk and i
> think that files needed by cluster service (log, checkpoint, ...) are
> on the only shared disk, where are stored all db files.
> Could be this the reason ?
> Thanks in advance...
>

Problem with user prompt

I want to prompt the user for a specific ID number or use a * for all
records at the time of running the report
So in my criteria for the employee ID field I put "= @.empid"
In the report the input box is there. If I put in an ID number it
works great. However if I put in an asterisk "*" nothing shows up.
Any ideas?Your where clause should be written like this:
where
(isnull(@.EmpID, '*') = '*')
or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
this lets your users enter a value for EmpID (then the second part of the
where clause will deal with it)
or leave the parameter field empty - then all their records will be selected
You could also replace the '*' with a '' (empty string) - this way, if the
users leave the parameter field blank, it will behave like a '*'.
Andrei.
"Bruce Lawrence" <BL32375@.gmail.com> wrote in message
news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
>I want to prompt the user for a specific ID number or use a * for all
> records at the time of running the report
> So in my criteria for the employee ID field I put "= @.empid"
> In the report the input box is there. If I put in an ID number it
> works great. However if I put in an asterisk "*" nothing shows up.
> Any ideas?
>|||Ok you lost me a little bit.
Here is my current where clause.
WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX')) AND (pshstj.empid =@.ClockNo)
The "@.clockno" section is where I prompt them for a clock number.
If I put a * it doesn't work. If I put a good number in it works fine.
Where does your 'where' clause fit into this?
Andrei wrote:
> Your where clause should be written like this:
> where
> (isnull(@.EmpID, '*') = '*')
> or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
> this lets your users enter a value for EmpID (then the second part of the
> where clause will deal with it)
> or leave the parameter field empty - then all their records will be selected
> You could also replace the '*' with a '' (empty string) - this way, if the
> users leave the parameter field blank, it will behave like a '*'.
> Andrei.
>
> "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
> news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
> >I want to prompt the user for a specific ID number or use a * for all
> > records at the time of running the report
> >
> > So in my criteria for the employee ID field I put "= @.empid"
> >
> > In the report the input box is there. If I put in an ID number it
> > works great. However if I put in an asterisk "*" nothing shows up.
> >
> > Any ideas?
> >|||I'd write an IIF statement (either in a stored procedure, or in the
Report Query):
IF @.clockno = '*'
BEGIN
select ...
from ...
where (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX'))
END
ELSE
BEGIN
select ..
from ..
where (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX')) AND (pshstj.empid =@.ClockNo)
END
Bruce Lawrence wrote:
> Ok you lost me a little bit.
> Here is my current where clause.
> WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
> (pshstj.trn = 'RO1' OR
> pshstj.trn = 'WO1' OR
> pshstj.trn = 'XXX')) AND (pshstj.empid => @.ClockNo)
> The "@.clockno" section is where I prompt them for a clock number.
> If I put a * it doesn't work. If I put a good number in it works fine.
> Where does your 'where' clause fit into this?
>
> Andrei wrote:
> > Your where clause should be written like this:
> >
> > where
> > (isnull(@.EmpID, '*') = '*')
> > or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
> >
> > this lets your users enter a value for EmpID (then the second part of the
> > where clause will deal with it)
> > or leave the parameter field empty - then all their records will be selected
> >
> > You could also replace the '*' with a '' (empty string) - this way, if the
> > users leave the parameter field blank, it will behave like a '*'.
> >
> > Andrei.
> >
> >
> > "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
> > news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
> > >I want to prompt the user for a specific ID number or use a * for all
> > > records at the time of running the report
> > >
> > > So in my criteria for the employee ID field I put "= @.empid"
> > >
> > > In the report the input box is there. If I put in an ID number it
> > > works great. However if I put in an asterisk "*" nothing shows up.
> > >
> > > Any ideas?
> > >|||WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate)
AND (NOT
(pshstj.trn = 'RO1' OR
pshstj.trn = 'WO1' OR
pshstj.trn = 'XXX'))
AND
(
(isnull(@.ClockNo, '*') = '*')
or
( (isnull(@.ClockNo, '*') <> '*') and (pshstj.empid =lockNo) )
)
"Bruce Lawrence" <BL32375@.gmail.com> wrote in message
news:1164828482.158109.84050@.j44g2000cwa.googlegroups.com...
> Ok you lost me a little bit.
> Here is my current where clause.
> WHERE (pshstj.workday BETWEEN @.StartDate AND @.EndDate) AND (NOT
> (pshstj.trn = 'RO1' OR
> pshstj.trn = 'WO1' OR
> pshstj.trn = 'XXX')) AND (pshstj.empid => @.ClockNo)
> The "@.clockno" section is where I prompt them for a clock number.
> If I put a * it doesn't work. If I put a good number in it works fine.
> Where does your 'where' clause fit into this?
>
> Andrei wrote:
>> Your where clause should be written like this:
>> where
>> (isnull(@.EmpID, '*') = '*')
>> or ((isnull(@.EmpID, 0) <> 0) and (EmpID = @.EmpID))
>> this lets your users enter a value for EmpID (then the second part of the
>> where clause will deal with it)
>> or leave the parameter field empty - then all their records will be
>> selected
>> You could also replace the '*' with a '' (empty string) - this way, if
>> the
>> users leave the parameter field blank, it will behave like a '*'.
>> Andrei.
>>
>> "Bruce Lawrence" <BL32375@.gmail.com> wrote in message
>> news:1164824835.673840.167860@.80g2000cwy.googlegroups.com...
>> >I want to prompt the user for a specific ID number or use a * for all
>> > records at the time of running the report
>> >
>> > So in my criteria for the employee ID field I put "= @.empid"
>> >
>> > In the report the input box is there. If I put in an ID number it
>> > works great. However if I put in an asterisk "*" nothing shows up.
>> >
>> > Any ideas?
>> >
>|||Andrei,
I'm not sure how... but it works.
I love you

Problem with UNION ALL

Hi,
I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
Among them one SELECT query is making more time.
If the same query is run seperately, it works fine in much less time
and the row count in the execution plan is small with a inner loop
join.
But when used with other select statements using UNION ALL,
the row count goes in thousands resulting in a HASH join.
Could u suggest any possible reason?
Thanks,
Dutt.Dutt
Do the SELECTs have WHERE conditions? How big is the output? Can you post
am example here?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
> Hi,
> I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
> Among them one SELECT query is making more time.
> If the same query is run seperately, it works fine in much less time
> and the row count in the execution plan is small with a inner loop
> join.
> But when used with other select statements using UNION ALL,
> the row count goes in thousands resulting in a HASH join.
> Could u suggest any possible reason?
> Thanks,
> Dutt.
>|||Hi Uri,
Yeah, some SELECTs have WHERE conditions and other SELECTs are just
SELECT NULLs.
Output is 4line XML.(17 rows without XML).
XML option has nothing to do with it.
Thanks,
Dutt.
On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Dutt
> Do the SELECTs have WHERE conditions? How big is the output? Can you pos
t
> am example here?
> "Dutt" <Mr.D...@.gmail.com> wrote in message
> news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
>
>
> - Show quoted text -|||Dutt
As I see here you also have some JOIN within UNION ALL statement, have you
tried to created a nonclusterd indexes
on referenced tables (FK)?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1176189934.257427.236990@.o5g2000hsb.googlegroups.com...[vbcol=seagreen]
> Hi Uri,
> Yeah, some SELECTs have WHERE conditions and other SELECTs are just
> SELECT NULLs.
> Output is 4line XML.(17 rows without XML).
> XML option has nothing to do with it.
> Thanks,
> Dutt.
>
> On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>|||Yeah, Uri,
Here the joining key is Primary Key itself and no other key is
available between those tables.
And one doubt, is there any problem if we have too many UNION ALLs.
Thanks
Dutt
On Apr 10, 2:07 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Dutt>> > the row count goes in thousands resulting in a HASH join.
> As I see here you also have some JOIN within UNION ALL statement, have you
> tried to created a nonclusterd indexes
> on referenced tables (FK)?
> "Dutt" <Mr.D...@.gmail.com> wrote in message
> news:1176189934.257427.236990@.o5g2000hsb.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -

Problem with UNION ALL

Hi,
I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
Among them one SELECT query is making more time.
If the same query is run seperately, it works fine in much less time
and the row count in the execution plan is small with a inner loop
join.
But when used with other select statements using UNION ALL,
the row count goes in thousands resulting in a HASH join.
Could u suggest any possible reason?
Thanks,
Dutt.Dutt
Do the SELECTs have WHERE conditions? How big is the output? Can you post
am example here?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
> Hi,
> I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
> Among them one SELECT query is making more time.
> If the same query is run seperately, it works fine in much less time
> and the row count in the execution plan is small with a inner loop
> join.
> But when used with other select statements using UNION ALL,
> the row count goes in thousands resulting in a HASH join.
> Could u suggest any possible reason?
> Thanks,
> Dutt.
>|||Hi Uri,
Yeah, some SELECTs have WHERE conditions and other SELECTs are just
SELECT NULLs.
Output is 4line XML.(17 rows without XML).
XML option has nothing to do with it.
Thanks,
Dutt.
On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Dutt
> Do the SELECTs have WHERE conditions? How big is the output? Can you post
> am example here?
> "Dutt" <Mr.D...@.gmail.com> wrote in message
> news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
>
> > Hi,
> > I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
> > Among them one SELECT query is making more time.
> > If the same query is run seperately, it works fine in much less time
> > and the row count in the execution plan is small with a inner loop
> > join.
> > But when used with other select statements using UNION ALL,
> > the row count goes in thousands resulting in a HASH join.
> > Could u suggest any possible reason?
> > Thanks,
> > Dutt.- Hide quoted text -
> - Show quoted text -|||Dutt
>> > the row count goes in thousands resulting in a HASH join.
As I see here you also have some JOIN within UNION ALL statement, have you
tried to created a nonclusterd indexes
on referenced tables (FK)?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1176189934.257427.236990@.o5g2000hsb.googlegroups.com...
> Hi Uri,
> Yeah, some SELECTs have WHERE conditions and other SELECTs are just
> SELECT NULLs.
> Output is 4line XML.(17 rows without XML).
> XML option has nothing to do with it.
> Thanks,
> Dutt.
>
> On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
>> Dutt
>> Do the SELECTs have WHERE conditions? How big is the output? Can you
>> post
>> am example here?
>> "Dutt" <Mr.D...@.gmail.com> wrote in message
>> news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
>>
>> > Hi,
>> > I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
>> > Among them one SELECT query is making more time.
>> > If the same query is run seperately, it works fine in much less time
>> > and the row count in the execution plan is small with a inner loop
>> > join.
>> > But when used with other select statements using UNION ALL,
>> > the row count goes in thousands resulting in a HASH join.
>> > Could u suggest any possible reason?
>> > Thanks,
>> > Dutt.- Hide quoted text -
>> - Show quoted text -
>|||Yeah, Uri,
Here the joining key is Primary Key itself and no other key is
available between those tables.
And one doubt, is there any problem if we have too many UNION ALLs.
Thanks
Dutt
On Apr 10, 2:07 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Dutt>> > the row count goes in thousands resulting in a HASH join.
> As I see here you also have some JOIN within UNION ALL statement, have you
> tried to created a nonclusterd indexes
> on referenced tables (FK)?
> "Dutt" <Mr.D...@.gmail.com> wrote in message
> news:1176189934.257427.236990@.o5g2000hsb.googlegroups.com...
>
> > Hi Uri,
> > Yeah, some SELECTs have WHERE conditions and other SELECTs are just
> > SELECT NULLs.
> > Output is 4line XML.(17 rows without XML).
> > XML option has nothing to do with it.
> > Thanks,
> > Dutt.
> > On Apr 10, 12:12 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> >> Dutt
> >> Do the SELECTs have WHERE conditions? How big is the output? Can you
> >> post
> >> am example here?
> >> "Dutt" <Mr.D...@.gmail.com> wrote in message
> >>news:1176178291.856296.203170@.w1g2000hsg.googlegroups.com...
> >> > Hi,
> >> > I have a procedure that has 18 'SELECT' queries with 'UNION ALL'.
> >> > Among them one SELECT query is making more time.
> >> > If the same query is run seperately, it works fine in much less time
> >> > and the row count in the execution plan is small with a inner loop
> >> > join.
> >> > But when used with other select statements using UNION ALL,
> >> > the row count goes in thousands resulting in a HASH join.
> >> > Could u suggest any possible reason?
> >> > Thanks,
> >> > Dutt.- Hide quoted text -
> >> - Show quoted text -- Hide quoted text -
> - Show quoted text -