Multi Indexes - One Column

I ran accross something I had not seen before on a MSSQL 2000 db today. All
of the tables had a PK and an index for the PK. In addition there was also
created another Clustered Index on the same PK column. As I have never seen
this before I dropped all existing indexes and put a single Clustered Index
in place on the PK columns. Currently I am wondering if this double index is
something that may have caused some performance issues? All is working great
with the single clustered index.
Thanks, KimHi Kim
The Northwind database has some similar duplicate indexes. Usually, it is
not a good thing. Sometimes it doesn't hurt anything, but if you do a lot of
updates, all those indexes need to be updated along with the data, so it can
actually hurt performance. (Inserts and deletes could have similar
performance issues.)
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kman" <kman@.toocoolwebs.com> wrote in message
news:OPtQroA1DHA.2156@.TK2MSFTNGP12.phx.gbl...
> I ran accross something I had not seen before on a MSSQL 2000 db today.
All
> of the tables had a PK and an index for the PK. In addition there was also
> created another Clustered Index on the same PK column. As I have never
seen
> this before I dropped all existing indexes and put a single Clustered
Index
> in place on the PK columns. Currently I am wondering if this double index
is
> something that may have caused some performance issues? All is working
great
> with the single clustered index.
> Thanks, Kim
>||||
| I ran accross something I had not seen before on a MSSQL 2000 db today.
All
| of the tables had a PK and an index for the PK. In addition there was also
| created another Clustered Index on the same PK column. As I have never
seen
| this before I dropped all existing indexes and put a single Clustered
Index
| in place on the PK columns. Currently I am wondering if this double index
is
| something that may have caused some performance issues? All is working
great
| with the single clustered index.
--
Generally you should avoid duplication of indexes to avoid performance
degradation during updates.
--
Eric Cárdenas
SQL Server support|||Thank you Kalen and Eric
Regards,
Kim (Kman)|||If you join by the PK a lot in your queries it would be faster for the
queries to use a non-clustered index for the joining instead of the
clustered index as simply more entries could be stored in the pages of the
NCI. As the others have pointed out this would slow down your
updates/inserts but if you do join to this table a lot and on that column,
it might behoove you to keep a seemingly-redundant index.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Kman" <kman@.toocoolwebs.com> wrote in message
news:OPtQroA1DHA.2156@.TK2MSFTNGP12.phx.gbl...
> I ran accross something I had not seen before on a MSSQL 2000 db today.
All
> of the tables had a PK and an index for the PK. In addition there was also
> created another Clustered Index on the same PK column. As I have never
seen
> this before I dropped all existing indexes and put a single Clustered
Index
> in place on the PK columns. Currently I am wondering if this double index
is
> something that may have caused some performance issues? All is working
great
> with the single clustered index.
> Thanks, Kim
>|||"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:eg%23KeXE1DHA.2180@.TK2MSFTNGP12.phx.gbl...
> If you join by the PK a lot in your queries it would be faster for the
> queries to use a non-clustered index for the joining instead of the
> clustered index as simply more entries could be stored in the pages of the
> NCI. As the others have pointed out this would slow down your
> updates/inserts but if you do join to this table a lot and on that column,
> it might behoove you to keep a seemingly-redundant index.
>
Interesting - so for a table that does a lot of joins, you're saying that
having a NCI on the PK would actually produce faster results?
How much faster (or is that a how long is a piece of string type question) ?|||Best way to test is use the command "set statistics IO on"
Run the query with only the clustered index, look at the logical IO (which
is not the number of pages hit but the number of times a hit occurred to a
page)
Place a non-clustered index on the PK column, re-run the query and look at
the new logical IO. If you are only needing the PK in your query it would be
faster to hit the NCI data pages as they are more compact. If your query
looks for multiple columns from the PK table, it may be more sensible for
SQL to only hit the clustered index and ignore your NCI...so, it all depends
on how your queries are.
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Dan Boylett" <parc_erom@.crossdata.co.uk> wrote in message
news:ufeOGGF1DHA.484@.TK2MSFTNGP10.phx.gbl...
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:eg%23KeXE1DHA.2180@.TK2MSFTNGP12.phx.gbl...
> > If you join by the PK a lot in your queries it would be faster for the
> > queries to use a non-clustered index for the joining instead of the
> > clustered index as simply more entries could be stored in the pages of
the
> > NCI. As the others have pointed out this would slow down your
> > updates/inserts but if you do join to this table a lot and on that
column,
> > it might behoove you to keep a seemingly-redundant index.
> >
> Interesting - so for a table that does a lot of joins, you're saying that
> having a NCI on the PK would actually produce faster results?
> How much faster (or is that a how long is a piece of string type question)
?
>
>|||Hi Ray
This would only be true if the join was a SEMI-join, checking for existence
in of matching rows in the inner table. If you were doing a true inner join,
and you needed data from the matching rows, a clustered index would be a
better choice.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:eg#KeXE1DHA.2180@.TK2MSFTNGP12.phx.gbl...
> If you join by the PK a lot in your queries it would be faster for the
> queries to use a non-clustered index for the joining instead of the
> clustered index as simply more entries could be stored in the pages of the
> NCI. As the others have pointed out this would slow down your
> updates/inserts but if you do join to this table a lot and on that column,
> it might behoove you to keep a seemingly-redundant index.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Kman" <kman@.toocoolwebs.com> wrote in message
> news:OPtQroA1DHA.2156@.TK2MSFTNGP12.phx.gbl...
> > I ran accross something I had not seen before on a MSSQL 2000 db today.
> All
> > of the tables had a PK and an index for the PK. In addition there was
also
> > created another Clustered Index on the same PK column. As I have never
> seen
> > this before I dropped all existing indexes and put a single Clustered
> Index
> > in place on the PK columns. Currently I am wondering if this double
index
> is
> > something that may have caused some performance issues? All is working
> great
> > with the single clustered index.
> >
> > Thanks, Kim
> >
> >
>|||Kalen, I agree with you, not too many cases where you would really use that
scenario of duplicate indexes, with a clustered index already on the PK, it
would make more sense to have a NCI on a column that you might pull from the
table, which of course would include the clustered key already that you
could join on.
Thanks!
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:evf2SfJ1DHA.1916@.TK2MSFTNGP10.phx.gbl...
> Hi Ray
> This would only be true if the join was a SEMI-join, checking for
existence
> in of matching rows in the inner table. If you were doing a true inner
join,
> and you needed data from the matching rows, a clustered index would be a
> better choice.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:eg#KeXE1DHA.2180@.TK2MSFTNGP12.phx.gbl...
> > If you join by the PK a lot in your queries it would be faster for the
> > queries to use a non-clustered index for the joining instead of the
> > clustered index as simply more entries could be stored in the pages of
the
> > NCI. As the others have pointed out this would slow down your
> > updates/inserts but if you do join to this table a lot and on that
column,
> > it might behoove you to keep a seemingly-redundant index.
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Kman" <kman@.toocoolwebs.com> wrote in message
> > news:OPtQroA1DHA.2156@.TK2MSFTNGP12.phx.gbl...
> > > I ran accross something I had not seen before on a MSSQL 2000 db
today.
> > All
> > > of the tables had a PK and an index for the PK. In addition there was
> also
> > > created another Clustered Index on the same PK column. As I have never
> > seen
> > > this before I dropped all existing indexes and put a single Clustered
> > Index
> > > in place on the PK columns. Currently I am wondering if this double
> index
> > is
> > > something that may have caused some performance issues? All is working
> > great
> > > with the single clustered index.
> > >
> > > Thanks, Kim
> > >
> > >
> >
> >
>sql

MSSQLServerOLAPSerice will not start.

I setup a MSSQL 2005 Cluster running on top of Virtual Server 2005. Everthing was running fine until this morning, when suddenly Analysis Services failed to start on the Cluster. When I looked in the Event Log I see the following:

Source: MSSQLServerOLAPService
Category: (289)
Type: Error
Event ID: 0

Description:
The service cannot be started: The DOM parser failed to load and parse the stream. URL:'' Reason: 'A document must contain exactly one root element.

' Source:''. File position: 0. Line: 1. The DOM parser failed to load and parse the file '\\?\N:\Microsoft SQL Server\MSSQL.2\OLAP\Data\master.vmp'.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

Any idea on what I can do to resolve this problem.

Thanks.

Most likely you data became corrupted. If you can reproduce the scenario you should contact product support services to troubleshoot this problem.

To get Analysis Server to start again you delete all the files in "%AS installation folder%\data" folder.

You need to restore you databases you've backed up earlier. Alternatively you can re-create and re-process all the cubes and all dimensions you had.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Thanks for the reply. I ended up simply deleting and reinstalling the service.

MSSQL's Design View is 4 LAZY CODERS (LOSERS)

I'd just like to say to all you lazy donks out there who can't
"write" a query without the damn design viewer have made my life,
and I'm sure countless others, a perpetual annoyance! Is it so hard
to sit down and write nice and clean queries yourself? Think of how
many hours over a period of a couple months you could save your
co-workers when they have to debug your crap (yes givler/rusnak, your
shit is that bad) if you quit copying and pasting that horribly
formatted SQL code from that damn thing and actually WROTE IT YOURSELF!
I don't mind so much fixing your ill conceived design view
adventures, it's that I have to sit down and read the stuff later and
it's a jumbled concoction of pure laziness! Hell, you can't even
write nicely formatted code when you DO write it yourself you lazy
oafs. I NEVER use that damn thing and my SQL code is always
pretty-formatted and efficient, as you have been fond of noticing.
THUFPPPPTTTT!!!!! jerks
Thanks,
Parlous2112Everybody knows that, who are you talking to ?|||I was just blowing off steam here and giving fair warning to anyone I
may encounter in the future if I discover their nasty little secret -
BEWARE OF PARLOUS2112! This way, I don't have to murder any co-workers
:).|||Maybe you can post some of them here:
http://www.thedailywtf.com/
ML|||Just post a google link to this thread.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:88993E30-BC4F-4932-BC47-188236BD76E9@.microsoft.com...
> Maybe you can post some of them here:
> http://www.thedailywtf.com/
>
> ML|||:) Evil.
ML

mssql8 AND mssql 2005

I have both 8 and 2005 installed on a windows 2003 server. However from a remote machine I can only see the 2005 instance. Is there a configuration I have missed or is this not possible?Are you using SQL Server 2005 Management Studio to connect to a remote database engine? If so, you may wanna try to select <Browse for more...> option from the Server Name dropdown list and then switch to the Network Servers tab. Let me know how you're getting along.|||I can see both instances on the Windows 2003 system and can connect to the v8 thru sql server management as you suggest. My issue is a remote machine with only 8's client cannot connect to the mssql v8 instance.|||Are you getting any error at all? Try to quickly create a .udl file on the remote machine to see if you could make and test a connection to the server.|||I get a server not found message.|||How did you name your 2000 and 2005 instances on the same box?|||2005 = server
other is server\server2003|||And server\server2003 is not visible from 2000 client, right?|||Yes. I wonder if I should name it something else maybe it doesn;t like that name. Maybe OldSQL?|||

Quote:

Originally Posted by kitspid

Yes. I wonder if I should name it something else maybe it doesn;t like that name. Maybe OldSQL?

Try to name it without slash. I doubt though it'll help. I once experienced a very funny problem with my replication whereby the distributor wouldn't connect to the subscriber. I had to enable Naming Pipes on the subscriber via the SQL config console to fix it. Just a thought.|||Tried the name pipes didn;t help. Don;t think I can rename it as the machine is called server which is where that comes from shoulf just be server2003 to connect. Going to move this db to a different machine and not worry about it anymore. Chalk it up to a 2005 quirk I think.|||Thanx for your help anyway.|||Good luck to you. Sorry couldn't help you better. Try to search in MSDN troubleshooting articles.|||I had to change the port for the mssql8 version to get it to work.

MSSQL7 and Windows 2000

Dear All,
could you please tell me if MSSQL7 is compliant with Windows 2000 ?
Which Service Pack is needed from both MSSQL and W2000 ?
many thanks in advance for your help ;)it's fine... you should be using the latest service packs for each of them|||Ok, thanks for your reply :)

MSSQL2k retrive data from xml

Hello
I have mssql 2k there database and few XML files. I try get data from XML
files and insert into database. How to do that using DTS'
AJAHello AJA,

> I have mssql 2k there database and few XML files. I try get data
> from XML
> files and insert into database. How to do that using DTS'
Here's about a dozen ideas:
http://www.perfectxml.com/articles/xml/importxmlsql.asp
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||>> I have mssql 2k there database and few XML files. I try get data
> Here's about a dozen ideas:
> http://www.perfectxml.com/articles/xml/importxmlsql.asp
Yes i watched there and:
OPENXML does not work because to big file 10+MB or maybe anyone have idea
how to prepare_document using text variable? code which i found opened max
1,5MB xml file
Microsoft Visual Basic no have so can no make project using VB
I was intrested to use this one "DTS ActiveX Script & MSXML 4.0 DOM". I
tried, installed MSXML and copied code but it does not work.
Succesfully execution but no records in destination tables.
I have no idea what was wrong :(
Best Regards
AJA|||Hello AJA,
One thing that I'll suggest is that you get off of SQL Server 2000 DTS and
look seriously at SQL Server 2005's SSIS bits. Much better for working with
XML.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||> One thing that I'll suggest is that you get off of SQL Server 2000 DTS and
> look seriously at SQL Server 2005's SSIS bits. Much better for working
> with XML.
The problem is I can no change MSSQL to 2005 because its not my software.
So i'm looking how to solve it in MSSQL 2k.
Best Regards
AJA|||Hello,
Check out SQLXML technology
:http://msdn2.microsoft.com/en-us/library/aa225763(SQL.80).aspx.
I hope this helps.
Regards,
--
Monica Frintu
"AJA" wrote:

> Hello
> I have mssql 2k there database and few XML files. I try get data from XM
L
> files and insert into database. How to do that using DTS'
>
> AJA
>

MSSQL2k retrive data from xml

Hello
I have mssql 2k there database and few XML files. I try get data from XML
files and insert into database. How to do that using DTS?
AJA
Hello AJA,

> I have mssql 2k there database and few XML files. I try get data
> from XML
> files and insert into database. How to do that using DTS?
Here's about a dozen ideas:
http://www.perfectxml.com/articles/xml/importxmlsql.asp
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||>> I have mssql 2k there database and few XML files. I try get data
> Here's about a dozen ideas:
> http://www.perfectxml.com/articles/xml/importxmlsql.asp
Yes i watched there and:
OPENXML does not work because to big file 10+MB or maybe anyone have idea
how to prepare_document using text variable? code which i found opened max
1,5MB xml file
Microsoft Visual Basic no have so can no make project using VB
I was intrested to use this one "DTS ActiveX Script & MSXML 4.0 DOM". I
tried, installed MSXML and copied code but it does not work.
Succesfully execution but no records in destination tables.
I have no idea what was wrong
Best Regards
AJA
|||Hello AJA,
One thing that I'll suggest is that you get off of SQL Server 2000 DTS and
look seriously at SQL Server 2005's SSIS bits. Much better for working with
XML.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||> One thing that I'll suggest is that you get off of SQL Server 2000 DTS and
> look seriously at SQL Server 2005's SSIS bits. Much better for working
> with XML.
The problem is I can no change MSSQL to 2005 because its not my software.
So i'm looking how to solve it in MSSQL 2k.
Best Regards
AJA
|||Hello,
Check out SQLXML technology
:http://msdn2.microsoft.com/en-us/library/aa225763(SQL.80).aspx.
I hope this helps.
Regards,
Monica Frintu
"AJA" wrote:

> Hello
> I have mssql 2k there database and few XML files. I try get data from XML
> files and insert into database. How to do that using DTS?
>
> AJA
>

MSSQL2K and MSSQL side by side in clustered environment

I haven't been able to find any publications on Internet regarding my concern.
We have a MS SQL2K installation in a clustered environment, and would like
to migrate to MS SQL2K5 using a side-by-side installation. To my knowledge,
this should be possible. Could you please direct me to any documents
describing how to perform this task. Is it as easy as creating two new
resource groups (we're using two instances for the MS SQL2K installation
today), create the necessary resources (sql name, sql ip, services and
disks), and installing MS SQL2K5 to a different location than the current
installation? The operative system on the servers are Windows 2003 Enterprise
Edition and the MS SQL 2K5 Server edition we intend to use is the Enterprise
edition.
Yes, you can do this. You need to create a new group which has disks which
have not been used for any other SQL Server Virtual. Then install a new SQL
Server 2005 Virtual. (It will need a new name and new IP address.) Once
installed, you can finish the migration by using either attach/detach or
backup/restore. You can also minimize the amount of time the migration
takes by implementing either log shipping or replication from SQL Server
2000 to 2005 and then cutting over where they are synchronized.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Bjrn Pettersson" <BjrnPettersson@.discussions.microsoft.com> wrote in
message news:CF3CA7C9-DEE9-41DF-9C0C-FBF48B93156E@.microsoft.com...
>I haven't been able to find any publications on Internet regarding my
>concern.
> We have a MS SQL2K installation in a clustered environment, and would like
> to migrate to MS SQL2K5 using a side-by-side installation. To my
> knowledge,
> this should be possible. Could you please direct me to any documents
> describing how to perform this task. Is it as easy as creating two new
> resource groups (we're using two instances for the MS SQL2K installation
> today), create the necessary resources (sql name, sql ip, services and
> disks), and installing MS SQL2K5 to a different location than the current
> installation? The operative system on the servers are Windows 2003
> Enterprise
> Edition and the MS SQL 2K5 Server edition we intend to use is the
> Enterprise
> edition.
|||Thanks for your answer :D
I've successfully managed to create a new cluster resource group, and have
created a disk resource, an ip address resource and a network name resource
(is this what you referring to as SQL Server Virtual?). I manage to
failover/move group resources to the other node and back again. I am able to
ping the Network Name which responds well and translates to the correct IP
address, so the cluster seems to be fine. When I run the SQL Server 2005
Enterprise Edition setup program, I am required to install ASP .Net 2.0, SQL
Native Client + Setup Component files, this works fine. When I click next and
the installation program performs a system check, the application seem to
work for a while, and then I get the following error message:
There was an unexpected failure during the setup wizard. You may review the
setup logs and/or click the help button for more information.
For help, click:
http://go.microsoft.com/fwlink?LinkI...d Disks%400x2
The bootstrap log files contain some error messages, listed below:
Error: Action "LaunchLocalBootstrapAction" threw an exception during
execution. Error information reported during run:
"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe"
finished and returned: 3221225477
Aborting queue processing as nested installer has completed
Message pump returning: 3221225477
Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\sqlsval.dll Version:2005.90.1399.0
Error: Action "InvokeSqlSetupDllAction" threw an exception during execution.
Exception record 0
Exception Code 0xc0000005
Exception Flags 0
Exception Address 0x498E4A04
Number of parameters 2
Parameter 0: 0x0
Parameter 1: 0x4
Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\DbgHelp.dll Version:6.5.3.7
Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework 2.0.log" to cab
file : "C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework 2.0 LangPack.log"
to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework Upgrade
Advisor.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework Upgrade Advisor
LangPack.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework Windows
Installer.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework Windows Installer
LangPack.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_SCC.log" to cab file :
"C:\Program Files\Microsoft SQL Server\90\Setup
Bootstrap\LOG\SqlSetup0016.cab" Error Code : 32
Running: UploadDrWatsonLogAction at: 2006/0/4 9:56:22
Message pump returning: 3221225477
Does any of you have experienced the setup program experiencing a unexpected
failure? Any ideas that can solve my installation problems are welcome.
Thanks,
Bj?rn
"Michael Hotek" skrev:

> Yes, you can do this. You need to create a new group which has disks which
> have not been used for any other SQL Server Virtual. Then install a new SQL
> Server 2005 Virtual. (It will need a new name and new IP address.) Once
> installed, you can finish the migration by using either attach/detach or
> backup/restore. You can also minimize the amount of time the migration
> takes by implementing either log shipping or replication from SQL Server
> 2000 to 2005 and then cutting over where they are synchronized.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Bj?rn Pettersson" <BjrnPettersson@.discussions.microsoft.com> wrote in
> message news:CF3CA7C9-DEE9-41DF-9C0C-FBF48B93156E@.microsoft.com...
>
>
|||You need to start out with a disk resource in its own resource group. The
Network Name and IP address resource for the clustered SQL instance are
created by the installer. It appears that the installer sees a complete
virtual server, although without SQL, and is having problems installing to
that resource group.
Also, the installer will crash if there is any clustered resource currently
offline.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Bjrn Pettersson" <BjrnPettersson@.discussions.microsoft.com> wrote in
message news:5B0D9134-9B9A-4ABE-BE48-2564C3555079@.microsoft.com...[vbcol=seagreen]
> Thanks for your answer :D
> I've successfully managed to create a new cluster resource group, and have
> created a disk resource, an ip address resource and a network name
> resource
> (is this what you referring to as SQL Server Virtual?). I manage to
> failover/move group resources to the other node and back again. I am able
> to
> ping the Network Name which responds well and translates to the correct IP
> address, so the cluster seems to be fine. When I run the SQL Server 2005
> Enterprise Edition setup program, I am required to install ASP .Net 2.0,
> SQL
> Native Client + Setup Component files, this works fine. When I click next
> and
> the installation program performs a system check, the application seem to
> work for a while, and then I get the following error message:
> There was an unexpected failure during the setup wizard. You may review
> the
> setup logs and/or click the help button for more information.
> For help, click:
> http://go.microsoft.com/fwlink?LinkI...d Disks%400x2
> The bootstrap log files contain some error messages, listed below:
> Error: Action "LaunchLocalBootstrapAction" threw an exception during
> execution. Error information reported during run:
> "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe"
> finished and returned: 3221225477
> Aborting queue processing as nested installer has completed
> Message pump returning: 3221225477
> Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\sqlsval.dll Version:2005.90.1399.0
> Error: Action "InvokeSqlSetupDllAction" threw an exception during
> execution.
> Exception record 0
> Exception Code 0xc0000005
> Exception Flags 0
> Exception Address 0x498E4A04
> Number of parameters 2
> Parameter 0: 0x0
> Parameter 1: 0x4
> Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\DbgHelp.dll Version:6.5.3.7
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework 2.0.log" to cab
> file : "C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework 2.0
> LangPack.log"
> to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework Upgrade
> Advisor.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework Upgrade Advisor
> LangPack.log" to cab file : "C:\Program Files\Microsoft SQL
> Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework Windows
> Installer.log" to cab file : "C:\Program Files\Microsoft SQL
> Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework Windows
> Installer
> LangPack.log" to cab file : "C:\Program Files\Microsoft SQL
> Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_SCC.log" to cab file :
> "C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 32
> Running: UploadDrWatsonLogAction at: 2006/0/4 9:56:22
> Message pump returning: 3221225477
> Does any of you have experienced the setup program experiencing a
> unexpected
> failure? Any ideas that can solve my installation problems are welcome.
> Thanks,
> Bjrn
> "Michael Hotek" skrev:
|||You also want to run the .Net Framework 2.0 install on all nodes in the
cluster. It will REALLY speed up the install.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Bjrn Pettersson" <BjrnPettersson@.discussions.microsoft.com> wrote in
message news:5B0D9134-9B9A-4ABE-BE48-2564C3555079@.microsoft.com...[vbcol=seagreen]
> Thanks for your answer :D
> I've successfully managed to create a new cluster resource group, and have
> created a disk resource, an ip address resource and a network name
> resource
> (is this what you referring to as SQL Server Virtual?). I manage to
> failover/move group resources to the other node and back again. I am able
> to
> ping the Network Name which responds well and translates to the correct IP
> address, so the cluster seems to be fine. When I run the SQL Server 2005
> Enterprise Edition setup program, I am required to install ASP .Net 2.0,
> SQL
> Native Client + Setup Component files, this works fine. When I click next
> and
> the installation program performs a system check, the application seem to
> work for a while, and then I get the following error message:
> There was an unexpected failure during the setup wizard. You may review
> the
> setup logs and/or click the help button for more information.
> For help, click:
> http://go.microsoft.com/fwlink?LinkI...d Disks%400x2
> The bootstrap log files contain some error messages, listed below:
> Error: Action "LaunchLocalBootstrapAction" threw an exception during
> execution. Error information reported during run:
> "C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe"
> finished and returned: 3221225477
> Aborting queue processing as nested installer has completed
> Message pump returning: 3221225477
> Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\sqlsval.dll Version:2005.90.1399.0
> Error: Action "InvokeSqlSetupDllAction" threw an exception during
> execution.
> Exception record 0
> Exception Code 0xc0000005
> Exception Flags 0
> Exception Address 0x498E4A04
> Number of parameters 2
> Parameter 0: 0x0
> Parameter 1: 0x4
> Loaded DLL:C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\DbgHelp.dll Version:6.5.3.7
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework 2.0.log" to cab
> file : "C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework 2.0
> LangPack.log"
> to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework Upgrade
> Advisor.log" to cab file : "C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework Upgrade Advisor
> LangPack.log" to cab file : "C:\Program Files\Microsoft SQL
> Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework Windows
> Installer.log" to cab file : "C:\Program Files\Microsoft SQL
> Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_.NET Framework Windows
> Installer
> LangPack.log" to cab file : "C:\Program Files\Microsoft SQL
> Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 2
> Error: Failed to add file :"C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\Files\SQLSetup0016_KK-TST-DB1_SCC.log" to cab file :
> "C:\Program Files\Microsoft SQL Server\90\Setup
> Bootstrap\LOG\SqlSetup0016.cab" Error Code : 32
> Running: UploadDrWatsonLogAction at: 2006/0/4 9:56:22
> Message pump returning: 3221225477
> Does any of you have experienced the setup program experiencing a
> unexpected
> failure? Any ideas that can solve my installation problems are welcome.
> Thanks,
> Bjrn
> "Michael Hotek" skrev:
|||We have 3 disk resources in a cluster group dedicated to the SQL Server 2005
installation.
We did try the installation without having sql network name and ip address
preconfigured as resources, same result.
Does the installation crashing apply to other cluster groups (other
instances) having offline resources?
"Geoff N. Hiten" skrev:

> You need to start out with a disk resource in its own resource group. The
> Network Name and IP address resource for the clustered SQL instance are
> created by the installer. It appears that the installer sees a complete
> virtual server, although without SQL, and is having problems installing to
> that resource group.
> Also, the installer will crash if there is any clustered resource currently
> offline.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Bj?rn Pettersson" <BjrnPettersson@.discussions.microsoft.com> wrote in
> message news:5B0D9134-9B9A-4ABE-BE48-2564C3555079@.microsoft.com...
>
>
|||Ok, I thought the installation would take care of this for me. I will try
installing ASP .Net on the other node,
would it be a good thing to install the SQL native client on the other node
too?
Thanks,
Bj?rn
"Michael Hotek" skrev:

> You also want to run the .Net Framework 2.0 install on all nodes in the
> cluster. It will REALLY speed up the install.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> "Bj?rn Pettersson" <BjrnPettersson@.discussions.microsoft.com> wrote in
> message news:5B0D9134-9B9A-4ABE-BE48-2564C3555079@.microsoft.com...
>
>
|||Yes. If the cluster has any resource or resource group offline, the SQL
installer will crash. The SQL installer tries to enumerate resources and
properties during a cluster install and fails badly if anything is offline.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Bjrn Pettersson" <BjrnPettersson@.discussions.microsoft.com> wrote in
message news:F16A596E-7B06-47F9-B416-CF4FD897B0A8@.microsoft.com...[vbcol=seagreen]
> We have 3 disk resources in a cluster group dedicated to the SQL Server
> 2005
> installation.
> We did try the installation without having sql network name and ip address
> preconfigured as resources, same result.
> Does the installation crashing apply to other cluster groups (other
> instances) having offline resources?
> "Geoff N. Hiten" skrev:
|||I have installed the following environment:
MS Windows 2003 EE + MS SQL Server 2005 EE
Services not installed:
Analysis services
Reporting services
At the end of the first named instance installation I get the same
error/alert messages.
I have tried some SQL Server operations and tested the fail-over
cluster and everything seems to be working fine.
I will carry some more tests but If I didn't get any operational
errors I will passed to production environment at the end of the week.

mssql2005 and vs2003

Hi just wondering if mssql 2005 integrates ok with vs2003, thanks.
Paul G
Software engineer.
Absolutely.
"Paul" wrote:

> Hi just wondering if mssql 2005 integrates ok with vs2003, thanks.
> --
> Paul G
> Software engineer.
|||ok thanks for the information.
Paul G
Software engineer.
"mulhall" wrote:
[vbcol=seagreen]
> Absolutely.
> "Paul" wrote:

mssql2005 and vs2003

Hi just wondering if mssql 2005 integrates ok with vs2003, thanks.
--
Paul G
Software engineer.Absolutely.
"Paul" wrote:

> Hi just wondering if mssql 2005 integrates ok with vs2003, thanks.
> --
> Paul G
> Software engineer.|||ok thanks for the information.
--
Paul G
Software engineer.
"mulhall" wrote:
[vbcol=seagreen]
> Absolutely.
> "Paul" wrote:
>

mssql2005 and vs2003

Hi just wondering if mssql 2005 integrates ok with vs2003, thanks.
--
Paul G
Software engineer.Absolutely.
"Paul" wrote:
> Hi just wondering if mssql 2005 integrates ok with vs2003, thanks.
> --
> Paul G
> Software engineer.|||ok thanks for the information.
--
Paul G
Software engineer.
"mulhall" wrote:
> Absolutely.
> "Paul" wrote:
> > Hi just wondering if mssql 2005 integrates ok with vs2003, thanks.
> > --
> > Paul G
> > Software engineer.

msSQL2000 to mssql2005

I have set up a replication between mssql2000 to mssql2005
Mssql2000 is the publisher and mssql is subscriber.
And it work fine.
Then I remove the publication because I need to do spm Alter statement
against Tables in the database
When I try to add subscription
with @.sync_type = N'none' (In This case the data i alredy in the subscribers
databse)
I get theese error:
Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__328568A3'.
Cannot insert duplicate key in object '#3191446A'.
(Source: SRVIQDB03 (Data source); Error number: 2627)
Her is Add subscr.
use [Tellus_DB209]
exec sp_addsubscription @.publication = N'pub_Tellus_DB209_2005',
@.subscriber = N'RDASP21', @.destination_db = N'Tellus',
@.subscription_type = N'Push', @.sync_type = N'none',
@.article = N'all', @.update_mode = N'read only',
@.loopback_detection = N'True',
@.frequency_type = 64, @.frequency_interval = 0, @.frequency_relative_interval
= 0,
@.frequency_recurrence_factor = 0, @.frequency_subday = 0,
@.frequency_subday_interval = 0,
@.active_start_time_of_day = 0, @.active_end_time_of_day = 235959,
@.active_start_date = 20060202, @.active_end_date = 99991231, @.offloadagent =
0,
@.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
What is this about.
What sp is SQL 2000? You may need to check this link to fix the problem.
http://groups.google.com/group/micro...5?dmode=source
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Roger Nygrd" <roger@.askit.no> wrote in message
news:11u4op7pil83qf4@.corp.supernews.com...
>I have set up a replication between mssql2000 to mssql2005
> Mssql2000 is the publisher and mssql is subscriber.
> And it work fine.
> Then I remove the publication because I need to do spm Alter statement
> against Tables in the database
> When I try to add subscription
> with @.sync_type = N'none' (In This case the data i alredy in the
> subscribers databse)
> I get theese error:
> Violation of PRIMARY KEY constraint 'PK__@.snapshot_seqnos__328568A3'.
> Cannot insert duplicate key in object '#3191446A'.
> (Source: SRVIQDB03 (Data source); Error number: 2627)
> Her is Add subscr.
> use [Tellus_DB209]
> exec sp_addsubscription @.publication = N'pub_Tellus_DB209_2005',
> @.subscriber = N'RDASP21', @.destination_db = N'Tellus',
> @.subscription_type = N'Push', @.sync_type = N'none',
> @.article = N'all', @.update_mode = N'read only',
> @.loopback_detection = N'True',
> @.frequency_type = 64, @.frequency_interval = 0,
> @.frequency_relative_interval = 0,
> @.frequency_recurrence_factor = 0, @.frequency_subday = 0,
> @.frequency_subday_interval = 0,
> @.active_start_time_of_day = 0, @.active_end_time_of_day = 235959,
> @.active_start_date = 20060202, @.active_end_date = 99991231, @.offloadagent
> = 0,
> @.enabled_for_syncmgr = N'False', @.dts_package_location = N'Distributor'
> What is this about.
>

mssql: insert into syntax

Hello
Can anyone help me translate this from access so that it can work in mssql
(i need to get next value, but cannot use identity as if row is deleted,
another must get new next column number which would be same as deleted one)
Access;
INSERT INTO table
SELECT
(IIF(code<>Null,MAX(code)+1,1) AS code,
0 AS usercode
FROM table

I tried this in mssql but will not work:
INSERT INTO table
SELECT
CASE
WHEN code IS NULL THEN 1
ELSE MAX(code)+1
END
AS code,
0 AS usercode
FROM tableHi

You may be better of using an identity column. It is not guaranteed to be
contiguous but usually is the ordinal value that is required. This is
similar to the autoincrementing number in access. See the topic "Identity
(Property)" in books online for more information.

John

"Andre" <spam@.spam.com> wrote in message news:de9g0c$ih4$1@.ss405.t-com.hr...
> Hello
> Can anyone help me translate this from access so that it can work in mssql
> (i need to get next value, but cannot use identity as if row is deleted,
> another must get new next column number which would be same as deleted
> one)
> Access;
> INSERT INTO table
> SELECT
> (IIF(code<>Null,MAX(code)+1,1) AS code,
> 0 AS usercode
> FROM table
> I tried this in mssql but will not work:
> INSERT INTO table
> SELECT
> CASE
> WHEN code IS NULL THEN 1
> ELSE MAX(code)+1
> END
> AS code,
> 0 AS usercode
> FROM table|||On Sun, 21 Aug 2005 11:02:42 +0200, Andre wrote:

(snip)
>Access;
>INSERT INTO table
>SELECT
> (IIF(code<>Null,MAX(code)+1,1) AS code,
>0 AS usercode
>FROM table

Hi Andre,

As John says: Consider using IDENTITY (the SQL Server equivalent of what
Access calls "autonumber").

If there are reason's why you can't use IDENTITY, then use

SELECT COALESCE(MAX(code), 0) + 1 AS code
FROM table

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||will try this
thx

(by the way, i mentioned I cannot use identity as it would not preserve
correct order if a middle row is deleted
and it would not allow end-user to change it)
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:6fqgg1d3f7bpim4ct0bril93j6vkedhgek@.4ax.com...
> On Sun, 21 Aug 2005 11:02:42 +0200, Andre wrote:

> If there are reason's why you can't use IDENTITY, then use
> SELECT COALESCE(MAX(code), 0) + 1 AS code
> FROM table|||On Sun, 21 Aug 2005 14:19:39 +0200, Andre wrote:

>will try this
>thx
>(by the way, i mentioned I cannot use identity as it would not preserve
>correct order if a middle row is deleted
>and it would not allow end-user to change it)

Hi Andre,

That's a logical result of the "raison d'etre" of the IDENTITY
attribute. You should use IDENTITY only to generate a unique numeric
value that can be used in place of the "real" key in foreign key
relationships. For instance, if a Foo is identified by the combination
of FooName, FooDate and FooWeight, the tables Foo and Bar *could* look
like this:

CREATE TABLE Foo
(FooName varchar(35) NOT NULL,
FooDate datetime NOT NULL,
FooWeight numeric (15,7) NOT NULL,
-- other columns,
PRIMARY KEY (FooName, FooDate, FooWeight)
)
CREATE TABLE Bar
(BarNo int NOT NULL,
FooName varchar(35) NOT NULL,
FooDate datetime NOT NULL,
FooWeight numeric (15,7) NOT NULL,
-- other columns,
PRIMARY KEY (BarNo),
FOREIGN KEY (FooName, FooDate, FooWeight)
REFERENCES Foo (FooName, FooDate, FooWeight)
ON UPDATE CASCADE
ON DELETE NO ACTION
)

Or, you could use IDENTITY to create a surrogate key and have your
tables like this:

CREATE TABLE Foo
(FooID int NOT NULL IDENTITY,
FooName varchar(35) NOT NULL,
FooDate datetime NOT NULL,
FooWeight numeric (15,7) NOT NULL,
-- other columns,
PRIMARY KEY (FooID),
UNIQUE (FooName, FooDate, FooWeight)
)
CREATE TABLE Bar
(BarNo int NOT NULL,
FooID int NOT NULL IDENTITY,
-- other columns,
PRIMARY KEY (BarNo),
FOREIGN KEY (FooID) REFERENCES Foo (FooID)
ON DELETE NO ACTION
)

This gives Bar a smaller footprint, and will speed up te joins (but at
the expense of a higher number of required joins). Note that a Foo is
still identified by it's "real" key. Also note that you might just as
well keep the "real" key as PRIMARY KEY and declare the identity column
to be UNIQUE (that will affect how your indexes look, so this is a
choice that affects performance).

An important issue to keep in mind is that the end user never sees the
identity value in this case. The end user will only see the "real" key,
as determined when investigating the business' information needs.

Your mention of preserving order when rows are deleted makes me think
that you want to use IDENTITY to get a ranking. In that case: don't. The
only thing MS guarantees about IDENITY is that it will be a unique value
in it's table (proivided you never override the generated values or
reset the seed). If you need a rank, you can either:
a) Compute it whenever you query the data. Use a view if you don't want
to retype the same query logic over and over again, or
b) Compute and store it; recompute ranks after each modification; this
one is dangerous (one uncontrolled modification can ruin the scheme) and
can slow down modification operations - only use it if you query the
data (including the rank) much more often than you modify the data.

Your mention of end users changing the value makes me think that you
don't want a ranking after all - but if have no idea what you do want to
use it for. Can you explain the purpose of this? I'm asking partly out
of curiosity, partly because I have the feeling that you're about to
make an error that either you or your successor will regret - I might be
wrong (I hope so!), but if I'm not, you better change your plan now,
before it is too late!

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||I have an accounting program which I am moving to mssql.
Now, I have tables which contain various documents (bills, inputs/outputs
etc).
Each document in its group must be in order
1,2,3...
there can be no omissions.
Now, I must permit to some users to delete documents (if these have been
entered by mistake), but also to permit
them to change their numbers. But these numbers must be unique in their
respective tables.
And, when user is creating new document, program must give him next number
(serial number if you wish).

So, since user might delete a middle row, using identity would mean that he
could, later when he creates document, give it the
number he previously deleted. I could set identity to allow change, but I
don't want to.

My programs currently run on access and mysql. I am adding mssql but didn't
expect so much trouble with sql syntax
Coalesce was mentioned in previous post: it does not work
I need simple

INSERT INTO table SELECT ISNULL(MAX(fieldvalue)+1,1) AS fieldvalue FROM
table
or
INSERT INTO table SELECT MAX(fieldvalue)+1 AS fieldvalueFROM table

if this is not possible on mssql, I will have to create on insert trigger or
lock table while creating new entry and first get value, then insert it into
table (1 query, 1 insert - lock, since two users might at the same time
create new: while information fieldvalue+1 travels to first user, second
executes same query and gets same
number as first has not made insert yet)|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> Your mention of preserving order when rows are deleted makes me think
> that you want to use IDENTITY to get a ranking. In that case: don't. The
> only thing MS guarantees about IDENITY is that it will be a unique value

Eh, Andr says he does not want to use IDENTITY, so you tell him not to
use it?

Anyway, if you say:

INSERT tbl (...)
SELECT ...
ORDER BY ...

and tbl has an IDENTITY column, the message I have, is indeed that there
is a guarantee that the IDENTITY values will reflect the ORDER BY clause.

However, this does not apply to SELECT INTO.

In any case, it is obvious from Andre's description of his business problem
that he should stay away from IDENTITY.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Andre (spam@.spam.com) writes:
> Coalesce was mentioned in previous post: it does not work

Please defined "does not work". Do you get an error message, do you
get unexpected result, does heaven fall down on your or what?

In any case, this seem to work:

CREATE TABLE andre (id int NOT NULL PRIMARY KEY,
somedata varchar(230) NOT NULL)
go
INSERT andre (id, somedata)
SELECT coalesce(MAX(id), 0) + 1, 'This is some data'
FROM andre
INSERT andre (id, somedata)
SELECT coalesce(MAX(id), 0) + 1, 'This is some other data'
FROM andre
INSERT andre (id, somedata)
SELECT coalesce(MAX(id), 0) + 1, 'This is any data'
FROM andre
go
SELECT * FROM andre ORDER BY id
go
DROP TABLE andre

> I need simple
> INSERT INTO table SELECT ISNULL(MAX(fieldvalue)+1,1) AS fieldvalue FROM

Since isnull() is proprietary to SQL Server, while coalesce() is
ANSI-SQL and you support other DBMS's, coalesce() would be a better
choice.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

You can try it as

INSERT INTO table
SELECT ISNULL(MAX(code),0)+1, 0 FROM table

Please let me know if u have any questions

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||Hi Andre

>From your description it sounds like you have to re-order everything if
you remove an intermediate document. This will not lead to a very
scalable application.

John|||Thank you all for help.

Problem was somewhere else:

I tried
INSERT INTO table x AS fieldx, y AS fieldy FROM table
while correct (for MSSQL obviously) is:
INSERT table (fieldx,fieldy) SELECT x,y FROM table

again, thank you for your time

p.s.: I wonder why are there such differences between sql syntax for various
databases (as in: what is the point of standard which is ignored)|||Andre (spam@.spam.com) writes:
> Thank you all for help.
> Problem was somewhere else:
> I tried
> INSERT INTO table x AS fieldx, y AS fieldy FROM table
> while correct (for MSSQL obviously) is:
> INSERT table (fieldx,fieldy) SELECT x,y FROM table

As far as I know the latter is also compliant with ANSI standards.
(Save for the fact that ANSI mandates INTO, while this is optional in
MS SQL Server.) The first syntax is something I've never seen before.
Does it work anywhere?

> p.s.: I wonder why are there such differences between sql syntax for
> various databases (as in: what is the point of standard which is
> ignored)

Indeed, just because it is the standard, does not mean that it is
implemnented everywhere. However, the basics of a regular INSERT
statement is something I would expect to work everywhere.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sun, 21 Aug 2005 23:34:40 +0200, Andre wrote:

>I have an accounting program which I am moving to mssql.
>Now, I have tables which contain various documents (bills, inputs/outputs
>etc).
>Each document in its group must be in order
>1,2,3...
>there can be no omissions.
>Now, I must permit to some users to delete documents (if these have been
>entered by mistake), but also to permit
>them to change their numbers. But these numbers must be unique in their
>respective tables.
>And, when user is creating new document, program must give him next number
>(serial number if you wish).
(snip)

Hi Andre,

There are some conflicting requirements. If there may be no omissions,
than you must either not allow users to delete a document (or rather:
don't allow them to delete A ROW - if the corresponding document is
deleted, keep the row but set a column to indicate that the document is
deleted) - or you must renumber all documents each time a document is
deleted to make sure that there never are gaps. Of course, if these
numbers are visible to the user and used to identify the documents, then
renumbering them will wreak havoc to the ability to relate rows in the
database to the actual documents. And if the users don't see the
numbers, then why bother with trying to keep them without omissions?

Also, if you allow users to change the document number, you are actually
guaranteed to get omissions. Sooner or later, someone will type the
number 42 because he's a Douglas Adams fan.

If the actual requirement is to use a number that is PREFERABLY
increasing and without gaps, and that users can optionally change to
reuse the number of a deleted document, I'd say: do a SELECT MAX(..)
query in the front end; increase by one and prepopulate the number field
in your frontend with that number. The user can either accept this
default or type a different number. The number that is in the field when
the user submits his data entry is sent to the database in an INSERT ...
VALUES statement.

(snip)
>I could set identity to allow change, but I
>don't want to.

So instead, you try to create your own solution that behaves exactly as
IDENTITY after setting it to allow change, but less scalable?

(snip)
>Coalesce was mentioned in previous post: it does not work
>I need simple
>INSERT INTO table SELECT ISNULL(MAX(fieldvalue)+1,1) AS fieldvalue FROM
>table

This should work. If you replace ISNULL with COALESCE, it should still
work. If it doesn't then please provide more information.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Actually, it is only important to fetch new table entry as CODE+1
Gaps are OK if user makes them. Skipping order number of CODE by user is
also OK.
But program must always return CODE+1 when new row is added.
User may change the number to any he wishes (except existing one) and the
next new row will be +1
This, of course, is not my idea. I would forbid deleting documents (even law
requires them to be void, not deleted - we are talking about accounting
program)
but then nobody would purchase my program.

"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:ipfkg118so3erd23b2sqa65kcsbosi585f@.4ax.com...
> On Sun, 21 Aug 2005 23:34:40 +0200, Andre wrote:
> There are some conflicting requirements. If there may be no omissions,
> than you must either not allow users to delete a document (or rather:
> don't allow them to delete A ROW - if the corresponding document is
> deleted, keep the row but set a column to indicate that the document is
> deleted) - or you must renumber all documents each time a document is
> deleted to make sure that there never are gaps. Of course, if these
> numbers are visible to the user and used to identify the documents, then
> renumbering them will wreak havoc to the ability to relate rows in the
> database to the actual documents. And if the users don't see the
> numbers, then why bother with trying to keep them without omissions?
> Also, if you allow users to change the document number, you are actually
> guaranteed to get omissions. Sooner or later, someone will type the
> number 42 because he's a Douglas Adams fan.
> If the actual requirement is to use a number that is PREFERABLY
> increasing and without gaps, and that users can optionally change to
> reuse the number of a deleted document, I'd say: do a SELECT MAX(..)
> query in the front end; increase by one and prepopulate the number field
> in your frontend with that number. The user can either accept this
> default or type a different number. The number that is in the field when
> the user submits his data entry is sent to the database in an INSERT ...
> VALUES statement.|||On Tue, 23 Aug 2005 13:28:57 +0200, Andre wrote:

>Actually, it is only important to fetch new table entry as CODE+1
>Gaps are OK if user makes them. Skipping order number of CODE by user is
>also OK.
>But program must always return CODE+1 when new row is added.
>User may change the number to any he wishes (except existing one) and the
>next new row will be +1
>This, of course, is not my idea. I would forbid deleting documents (even law
>requires them to be void, not deleted - we are talking about accounting
>program)
>but then nobody would purchase my program.

Hi Andre,

I stick with my previous recommendation.

1. Fetch MAX(code)+1 with a non-locking query when opening the screen.
Either display it as default value in the code field, or keep the code
field blank.

2. When details are entered, attempt to insert the row, with code as
entered on the screen; if no value is entered, use the MAX(code)+1 from
the previous call.

3. If a row with the chosen key value exists, further action is decided
by the front end:

3a. If key was entered by user: error message.
3b. If user didn't override the default, re-insert row with MAX(code)+1
as new code; show warning message that code has been changed.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

mssql: case statements with multiple columns

A standard case statement in MSSQL server looks like:
...WHERE s.idMarket =
CASE @.Mode
WHEN 'Sales' THEN 1
ELSE 2
END
but I want a similar statement to use multiple values of 'idMarket' for
each value of '@.Mode'
e.g.
...WHERE s.idMarket =
CASE @.Mode
WHEN 'Sales' THEN 1, 3, 5, 6
ELSE 2, 4, 7, 8, 9
END
is this possible through an mssql query?
cheers.
fiddlewidawiddumYou can't use case that way.
For this may be you can rephrase the where clause this way..
WHERE (@.Mode = 'Sales' and s.idMarket in (1, 3, 5, 6)) or (@.Mode <> 'Sales'
and s.idMarket in (2, 4, 7, 8, 9))
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Stimp" wrote:

> A standard case statement in MSSQL server looks like:
> ...WHERE s.idMarket =
> CASE @.Mode
> WHEN 'Sales' THEN 1
> ELSE 2
> END
> but I want a similar statement to use multiple values of 'idMarket' for
> each value of '@.Mode'
> e.g.
> ...WHERE s.idMarket =
> CASE @.Mode
> WHEN 'Sales' THEN 1, 3, 5, 6
> ELSE 2, 4, 7, 8, 9
> END
> is this possible through an mssql query?
> cheers.
> --
> fiddlewidawiddum
>|||On Mon, 19 Jun 2006 Omnibuzz <Omnibuzz@.discussions.microsoft.com> wrote:
> You can't use case that way.
> For this may be you can rephrase the where clause this way..
> WHERE (@.Mode = 'Sales' and s.idMarket in (1, 3, 5, 6)) or (@.Mode <> 'Sales
'
> and s.idMarket in (2, 4, 7, 8, 9))
that actually works great.. thanks!
--
fiddlewidawiddum|||>> A standard case statement in MSSQL server looks like: <<
There is no CASE statement in SQL; there is a CASE expression. An
expression returns a scalar value of a known data type.
Again, you missed the concept "expression" and "scalar" so the
question is wrong. Then even if it would have worked, you would have
to use IN() and not = in the predicate. Besides not bothering to learn
SQL, why did you violate ISO-11179 by putting "id-" as a prefix? Why
did you use a vague nmae like "mode" --mode of what'
Try something like this:
..WHERE CASE
WHEN S.market_id IN (1, 3, 5, 6)
AND @.mode = 'sales'
THEN 'T'
WHEN S.market_id IN (2, 4, 7, 8, 9)
AND @.mode <> 'sales'--mode of what'
THEN 'T' ELSE 'F' END = 'T';

MSSQL/Server setup/running issue/question...

i have a client with a windows 2003 server with 4 processors & 32 GB RAM, then MSSQL 2000 SP3. our software seems to run fine on it until the Task Manager shows the MSSQL using 1.7GB of RAM. At this point the whole system seems to slow down to a snails pace. Any ideas as to why 1.7GB and it never goes over that amount no matter how much activity i run on it. Restart mssql and everything seems to run good up until we see the 1.7GB again.Quite simple: The server is installed with 32-bit edition of the OS and SQL Server 2000. You can increase the memory usage of SQL server to approx 2.7GB by using the /3GB switch in boot.ini, or use AWE to use memory above 4GB, but I won't recommend it since it works more or less like himem.sys and emm386 in the old days. What you should do is really reinstall the server, using 64-bit edition of both the OS and SQL Server software. Unless there is some piece of software or hardware which is not supported in the 64-bit edition.

MSSQL.3

On one server the report services folder exists here;
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer
On another server it exists here;
C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting
Services\ReportServer
Why MSSQL.3 on one and MSSQL.4 on the other?
Thanx in advance ,
GregOn Aug 29, 11:32 am, "SurferJoe" <Surfer...@.newsgroup.nospam> wrote:
> On one server the report services folder exists here;
> C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer
> On another server it exists here;
> C:\Program Files\Microsoft SQL Server\MSSQL.4\Reporting
> Services\ReportServer
> Why MSSQL.3 on one and MSSQL.4 on the other?
> Thanx in advance ,
> Greg
This is common when you have a different number of SQL Server Services
installed (i.e., if you have the SQL Server Database Engine,
Integration Services and Reporting Services installed on one server,
SSRS might be in MSSQL.3; whereas, if you have the SQL Server Database
Engine, Integration Services, Analysis Services and Reporting Services
installed on the other server, SSRS might be in MSSQL.4). Hope this
helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

MSSQL.1, .2, .3....?

For some reason Sql 2005, by default, creates different folder paths (such as MSSQL.1, MSSQL,2, etc) for different services.

1. Is there a utiltiy that can tell me what service is stored in what folder?

2. I noticed that Integraiton Servcies does not seem to have a seperate ("MSSQL.x") folder. Why?

Any help much appreciated,

Barkingdog

"Is there a utiltiy that can tell me what service is stored in what folder?"

Yes, SQL Server Configuration Manager. Select SQL Server 2005 Services after you start the tool. Right-click on a particular service and select properties. Select the Service tab and the path is in the Binary Path attribute.

" I noticed that Integraiton Servcies does not seem to have a seperate ("MSSQL.x") folder. Why?"

My guess is that the sql directory structure is this way because SSIS does not require several other OS files as do the other services. But thats a guess. It is what it is :)

HTH,

Derek

mssql,oracle reports or crystal reports

Hello
I am jumping between oracle and mssql
In oracle i use oracle reports which works fine
What do i use for MSSQL, can i use Oracle reports or should i buy some other one like crsytal reports
SEJCrystal reports would be the way to go without a doubt.

It can of course also be used to report against Oracle etc..

Mark

Originally posted by sjumma
Hello

I am jumping between oracle and mssql

In oracle i use oracle reports which works fine

What do i use for MSSQL, can i use Oracle reports or should i buy some other one like crsytal reports

SEJ|||Originally posted by mtracey
Crystal reports would be the way to go without a doubt.

It can of course also be used to report against Oracle etc..

Mark

where do i get the trial/free version is it part of multimedia suite

salim|||I don't know that you can get a free trial. It comes with Microsoft Visual Studio if you have it.

Crystal is a bit of an industry standard reporting tool.
Runs on MS platforms only though (unless they have some new products)

Here for more info.

http://www.crystaldecisions.com/products/crystalreports/default.asp

MSSQL, Access 2000, and ODBC Interplay

To date I've used nothing but MySQL and have loved it.
The company I work for uses MSSQL and Access for their
product database. Here is the situation:
Product Manager has created a product database Products.DBF
This file is saved on the same server as the SQL server.
The goal is to have the SQL server use this DBF file so
that she can update the DB via access.
More info: The purpose of this is such that, when people
visit a certain ASP page, that page queries the SQL server
and pulls the data from the DBF file. I'm assuming this
has to be done via ODBC some how, with which I have some
experience, and I'm sure I'm missing something obvious but
any help would be greatly appreciated."Philip" <phil@.fizur.net> wrote in message news:<021101c33f43$fefd55c0$a501280a@.phx.gbl>...
<<>>
> More info: The purpose of this is such that, when people
> visit a certain ASP page, that page queries the SQL server
> and pulls the data from the DBF file. I'm assuming this
> has to be done via ODBC some how, with which I have some
> experience, and I'm sure I'm missing something obvious but
> any help would be greatly appreciated.
Well.. to my mind there is something obvious...
Store the data in sql server.
Forget my mysql.
Or.
Obtain some odbc driver allows access to connect to mysql and forget sql server.

MSSQL Workgroup 5 CAL Licence

I do not really understand how MSSQL licensing works so I would like
to be sure to choose the right one. I have one server that is going to
host MSSQL and 2 web servers that will be client of MSSQL. As an
apache web server run multiple instances I do not know if it changes
something in the licensing system. Could you tell me if this
configuration would be ok :
- db server : Mono Xeon Dual Core + SQL Server 2005 Workgroup Edition
Win32 with 5-Clients (http://www.provantage.com/microsoft-
a5k-01017~7MCSB0EW.htm)
- 2 * apache web servers accessing to db server
you will be limited to 5 users connected at the same time. and these will be
only 5 unique named users.
you are in a multiplexing scenario and apache don't change anything here.
if your website is a public site (open to the internet) and you anticipate
thousands of users, then you have to pay for a by CPU license model to
support these users.
1 User CAL CANNOT be shared with multiple users, each user must have a valid
license.
CPU license give you unlimited access. and its only 1 CPU license in your
case (you have only 1 physical CPU)
read this:
http://www.microsoft.com/sql/howtobuy/sqlserverlicensing.mspx
"xav" <xavierdaull@.gmail.com> wrote in message
news:1181645853.286005.163070@.a26g2000pre.googlegr oups.com...
>I do not really understand how MSSQL licensing works so I would like
> to be sure to choose the right one. I have one server that is going to
> host MSSQL and 2 web servers that will be client of MSSQL. As an
> apache web server run multiple instances I do not know if it changes
> something in the licensing system. Could you tell me if this
> configuration would be ok :
> - db server : Mono Xeon Dual Core + SQL Server 2005 Workgroup Edition
> Win32 with 5-Clients (http://www.provantage.com/microsoft-
> a5k-01017~7MCSB0EW.htm)
> - 2 * apache web servers accessing to db server
>
|||"xav" <xavierdaull@.gmail.com> wrote in message
news:1181645853.286005.163070@.a26g2000pre.googlegr oups.com...
>I do not really understand how MSSQL licensing works so I would like
> to be sure to choose the right one. I have one server that is going to
> host MSSQL and 2 web servers that will be client of MSSQL. As an
> apache web server run multiple instances I do not know if it changes
> something in the licensing system. Could you tell me if this
> configuration would be ok :
> - db server : Mono Xeon Dual Core + SQL Server 2005 Workgroup Edition
> Win32 with 5-Clients (http://www.provantage.com/microsoft-
> a5k-01017~7MCSB0EW.htm)
> - 2 * apache web servers accessing to db server
>
If users outside of your company will be accessing your database server
through the website, then you need a per CPU license.
Note though that per-CPU means per-socket, not per-core, so dual-core CPUs
only need a single license per CPU.
|||There are no thousands of clients accessing to MSSQL but two computers
(web servers) accessing to one db server to generate pages. As each
computer (2) will access to MSSQL with the same login for each : is it
considered as 2 clients ? or each apache processus should have is own
CAL ?
Thanks for your help
|||no a client is a physical person on the net NOT a intermediate server.
you are in multiplexing mode, the CAL is associated to the user which access
the database through the web site.
so if you have 100 users you need 100 CALs.
so if you are connected to the internet... use the CPU license model where
the number of user is unlimited.
a CAL is NOT a login but controlled through the number of session opened on
the server. so the entire world can use the same login password but this
require 1 CAL by user...
"xav" <xavierdaull@.gmail.com> wrote in message
news:1181716272.288139.109540@.i13g2000prf.googlegr oups.com...
> There are no thousands of clients accessing to MSSQL but two computers
> (web servers) accessing to one db server to generate pages. As each
> computer (2) will access to MSSQL with the same login for each : is it
> considered as 2 clients ? or each apache processus should have is own
> CAL ?
> Thanks for your help
>
|||On 14 Jun, 03:11, "Jeje" <willg...@.hotmail.com> wrote:

> a CAL is NOT a login but controlled through the number of session opened on
> the server.
No. A CAL is per user or device. The number of sessions opened on the
server is irrelevant.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx