About a RS version and previous conditions of use

I own a Windows Small Bussiness 2003 license which includes SQL server 2000
Standard Edition and additionally came with a version of Reporting Services.
I want to learn and use it (Reporting Services) as a beginner but when I
try to install it a message appears indicating that I need to install or
configure previously two products:
a) Visual Studio .Net 2003
b) IIS 5.0
Do I need both of 'em just to begin doing simple reports?
I supposed a simple use like I could obtain through Crystal reports 7.0 or
so on.
Please, help me.
Probably next year we will migrate to a new version of Microsoft SBS
Is it worth to do efforts with the versions I own nowadays or not?
Thanks alot in advance.
--
sanpetusRS 2000 report designer require some copy of VS 2003 to be installed. In the
past VB.net 2003 was the cheapest way to do this (about $100). I don't know
now. Note that the VB 2005 will not work for this.
In RS 2005 it comes with a version of VS 2005 so no additional purchase is
necessary.
RS is a asp.net application and as such it needs IIS. IIS comes with all
servers. It might need to configured though.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sanpetus" <sanpetus@.discussions.microsoft.com> wrote in message
news:14FE57B4-A059-4B0F-8539-C9D64CBAA6B6@.microsoft.com...
>I own a Windows Small Bussiness 2003 license which includes SQL server 2000
> Standard Edition and additionally came with a version of Reporting
> Services.
> I want to learn and use it (Reporting Services) as a beginner but when I
> try to install it a message appears indicating that I need to install or
> configure previously two products:
> a) Visual Studio .Net 2003
> b) IIS 5.0
> Do I need both of 'em just to begin doing simple reports?
> I supposed a simple use like I could obtain through Crystal reports 7.0 or
> so on.
> Please, help me.
> Probably next year we will migrate to a new version of Microsoft SBS
> Is it worth to do efforts with the versions I own nowadays or not?
> Thanks alot in advance.
> --
> sanpetus

aba_lockinfo - new version available

If you are using my lock-monitoring procedure aba_lockinfo, there is now a
new version available at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
I recommend that you replace your existing version with this one.
Functionally, there are only minor difference, but the older version did
not have acceptable performance when there were a large number of locks.
This version is designed to be more lean on resources.
If you are using text mode from Query Analyzer, there is a new parameter
@.fancy that may be of interest to you. The default is zero, but if you
set it to 1, aba_lockinfo dynamically sets the column widths, and you also
get a blank line between processes. (This requires some extra steps, which
it is not the default.) In grid mode, @.fancy does not matter much.
The change applies to the versions for SQL7, SQL 2000 and SQL 2000 SP3. The
one for SQL 6.5 is untouched.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns949A1D178D27Yazorman@.127.0.0.1...
> If you are using my lock-monitoring procedure aba_lockinfo, there is now a
> new version available at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
> I recommend that you replace your existing version with this one.
...[trim]...
Excellent tool Erland!
Thanks for sharing it.
Pete Brown
Falls Creek
Oz
www.mountainman.com.au

aba_lockinfo - new version available

If you are using my lock-monitoring procedure aba_lockinfo, there is now a
new version available at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
I recommend that you replace your existing version with this one.

Functionally, there are only minor difference, but the older version did
not have acceptable performance when there were a large number of locks.
This version is designed to be more lean on resources.

If you are using text mode from Query Analyzer, there is a new parameter
@.fancy that may be of interest to you. The default is zero, but if you
set it to 1, aba_lockinfo dynamically sets the column widths, and you also
get a blank line between processes. (This requires some extra steps, which
it is not the default.) In grid mode, @.fancy does not matter much.

The change applies to the versions for SQL7, SQL 2000 and SQL 2000 SP3. The
one for SQL 6.5 is untouched.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns949A1D178D27Yazorman@.127.0.0.1...
> If you are using my lock-monitoring procedure aba_lockinfo, there is now a
> new version available at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
> I recommend that you replace your existing version with this one.

...[trim]...

Excellent tool Erland!
Thanks for sharing it.

Pete Brown
Falls Creek
Oz
www.mountainman.com.au

aba_lockinfo - new version available

If you are using my lock-monitoring procedure aba_lockinfo, there is now a
new version available at http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
I recommend that you replace your existing version with this one.
Functionally, there are only minor difference, but the older version did
not have acceptable performance when there were a large number of locks.
This version is designed to be more lean on resources.
If you are using text mode from Query Analyzer, there is a new parameter
@.fancy that may be of interest to you. The default is zero, but if you
set it to 1, aba_lockinfo dynamically sets the column widths, and you also
get a blank line between processes. (This requires some extra steps, which
it is not the default.) In grid mode, @.fancy does not matter much.
The change applies to the versions for SQL7, SQL 2000 and SQL 2000 SP3. The
one for SQL 6.5 is untouched.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns949A1D178D27Yazorman@.127.0.0.1...
> If you are using my lock-monitoring procedure aba_lockinfo, there is now a
> new version available at
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
> I recommend that you replace your existing version with this one.
...[trim]...
Excellent tool Erland!
Thanks for sharing it.
Pete Brown
Falls Creek
Oz
www.mountainman.com.au

AA_DB is this sql error?

Hi all,

Our version of application is throwing AA_DB error while working on it... at first start. we are using sql 2000 with sp3a on win 2003 platform. with 4 Xenon processors and 8 GB ram.

is this error is related with sql or something else...

I checked this on net and found..(actionapps or something related with authentication of user)

pls help to rectify if possible..

Best Regards,its a new version of application we launched... earlier in old versions this error was not there...|||could you clarify the error in more detail so that someone will be able to help you.
regards,
Harshal.|||i have two server's in domain, with same configuration sql 2000 sp3a same db name, same application package... but while working on one sever application...in one module its showing me aa_db error after 10 to 15 mins randomly... we have tested sql installation, service pack reinstalled, applicaiton installation tested.
though the applicaiton is very well tested by vendor side but here we are confuse that why we are facing it..|||What's the errorlog showing?

A->B, A->C relationships without using subreports

Hi all:

This is simplified version of my problem:

There are 3 tables A, B, and C. The relationships are: A(one)->B(many), A(one)->C(many). (there is no direct relationship between B and C)

I want to create a report to list each record in A, followed by records associated to the A’s record in B, then records associated to the A’s record in C. Both B, C records should be in a table format. Can I do it without using subreports? I have performance problems with subreports in a large report(thousand records in table A). RS documentation suggests replacing subreport with data region will help the performance.

Thanks in advance!

Here is an example:

Table definitions: (Pet table and Dependant table have not direct relationship)

Employee Table has column: EmpNo

Dependant Table has 2 columns:

EmpNo

DependantName

Pet Table has 2 columns:

EmpNo

PetName

Relationships: Employee(one) -> Dependant(Many)

Employee(one) -> Pet (Many)

Report Format:

EmpNo: ###

(this is a Reporting Service table)

Dependant Name 1 for EmpNo ###

Dependant Name 2 for EmpNo ###

Dependant Name 3 for EmpNo ###

Dependant Name 4 for EmpNo ###

(this is a Reporting Service table)

Pet Name 1 for EmpNo ###

Pet Name 2 for EmpNo ###

Not sure I completely understand your situation, but in order to use a nested data region you have to (unfortunately) use the same dataset as the parent data region, which means that you will have to combine the datasets for the report and subreport into one dataset/query. If you NEED to use a separate dataset you will have to use a subreport.

If you have performance problems using the dataset region method because of a large volume of records, then programming that can be a little tricky. I had a similar situation where, for performance reasons, I HAD to filter/parameterize the dataset before joining it because of the number of records (300,000+) in one of the tables. To pull this off I used a (sql server 2000) table type user-defined function (kinda a view that allows parameters) and joined that in the report. You will also have to rewrite your query to bring all the data together in one dataset.

A virture machine

How do I find out the machine is 32-bit or 64 version?
ThanksThe OS or SQL Server? Also if OS, what version of the OS?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"mecn" <mecn2002@.yahoo.com> wrote in message news:%239FKPPNtHHA.2752@.TK2MSFTNGP06.phx.gbl...
> How do I find out the machine is 32-bit or 64 version?
> Thanks
>|||win 2003 server
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:18187D2F-4D20-40B9-A952-906826D8F3EC@.microsoft.com...
> The OS or SQL Server? Also if OS, what version of the OS?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:%239FKPPNtHHA.2752@.TK2MSFTNGP06.phx.gbl...
>> How do I find out the machine is 32-bit or 64 version?
>> Thanks|||Type winmsd, and it should show the OS Name as "Windows Server 2003
Enterprise x64 Edition" if it is x64.
Linchi
"mecn" wrote:
> win 2003 server
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:18187D2F-4D20-40B9-A952-906826D8F3EC@.microsoft.com...
> > The OS or SQL Server? Also if OS, what version of the OS?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "mecn" <mecn2002@.yahoo.com> wrote in message
> > news:%239FKPPNtHHA.2752@.TK2MSFTNGP06.phx.gbl...
> >> How do I find out the machine is 32-bit or 64 version?
> >>
> >> Thanks
>
>|||thanks
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:F53D8D7B-72AA-4829-A0DE-71E296A37068@.microsoft.com...
> Type winmsd, and it should show the OS Name as "Windows Server 2003
> Enterprise x64 Edition" if it is x64.
> Linchi
> "mecn" wrote:
>> win 2003 server
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> message news:18187D2F-4D20-40B9-A952-906826D8F3EC@.microsoft.com...
>> > The OS or SQL Server? Also if OS, what version of the OS?
>> >
>> > --
>> > Tibor Karaszi, SQL Server MVP
>> > http://www.karaszi.com/sqlserver/default.asp
>> > http://sqlblog.com/blogs/tibor_karaszi
>> >
>> >
>> > "mecn" <mecn2002@.yahoo.com> wrote in message
>> > news:%239FKPPNtHHA.2752@.TK2MSFTNGP06.phx.gbl...
>> >> How do I find out the machine is 32-bit or 64 version?
>> >>
>> >> Thanks
>>

a slight improvement to a great solution...

This is a great solution! I've modified it a bit to make it a little more
manageable though for my own use. I decided to make a version where the row
colors could be centrally managed since you have to copy the expression to
every cell in the row... and in some reports that can be a lot of cells...
this way you can define the color scheme in one place.. and you can also use
row level formatting. Here is how i did this.
Custom Code as follows:
Dim Public bgColor1 As String = "White"
Dim Public bgColor2 As String = "WhiteSmoke"
Dim Public bgColor As String = bgColor2
Public Function getBgColor(switch As Boolean) As String
If switch
If bgColor = bgColor1
bgColor = bgColor2
else
bgColor = bgColor1
end if
end if
return bgColor
End Function
Highlight the ROW and put in the following expression for BackgroundColor
property:
=Code.getBgColor(false)
Then all you have to do is go into the FIRST cell of the row and change it to:
=Code.getBgColor(true)
and walla works great (just like the original) with centralize management of
the row colors...
just a little change on a great solution...And what happens with concurrent users generating the same report?
Since bgColor is a public shared variable your code will run into problems.
Have a look at: http://odetocode.com/Articles/130.aspx
[...]
While shared methods are recommended, shared fields are definitely not. For
instance, the following code will have problems.
Public Shared Function AddToCount(ByVal Value As Integer) As String
Count = Count + value
End Function
Shared Count As Integer = 0
First, we have no control over the lifetime of the variable Count. Secondly,
if multiple users are executing the report with this code at the same time,
both reports will be changing the same Count field (that is why it is a
shared field). You don't want to debug these sorts of interactions - stick
to shared functions using only local variables (variables passed ByVal or
declared in the function body).
[...]
"thejez" <thejez@.discussions.microsoft.com> escribió en el mensaje
news:48D9ED94-AE6B-47C3-B1A9-C35DE05D4E08@.microsoft.com...
> This is a great solution! I've modified it a bit to make it a little more
> manageable though for my own use. I decided to make a version where the
> row
> colors could be centrally managed since you have to copy the expression to
> every cell in the row... and in some reports that can be a lot of cells...
> this way you can define the color scheme in one place.. and you can also
> use
> row level formatting. Here is how i did this.
> Custom Code as follows:
> Dim Public bgColor1 As String = "White"
> Dim Public bgColor2 As String = "WhiteSmoke"
> Dim Public bgColor As String = bgColor2
> Public Function getBgColor(switch As Boolean) As String
> If switch
> If bgColor = bgColor1
> bgColor = bgColor2
> else
> bgColor = bgColor1
> end if
> end if
> return bgColor
> End Function
> Highlight the ROW and put in the following expression for BackgroundColor
> property:
> =Code.getBgColor(false)
> Then all you have to do is go into the FIRST cell of the row and change it
> to:
> =Code.getBgColor(true)
> and walla works great (just like the original) with centralize management
> of
> the row colors...
> just a little change on a great solution...|||hrmmm this was supposed to be a reply to a previous post... and now i cant
even find that original post anymore (think it was posted originally on
5/27/05)...
anyway here the original post:
"G" wrote:
> Ok, I found my workaround. Someone is bound to have this issue sometime in
> the future, so I'll put the workaround here.
> I created a little routine in the custom Code area of the report that simply
> toggles and returns an integer value:
> Dim Public bgColor As Integer = 0
> Public Function alternateColor As Integer
> If bgColor = 0
> bgColor = 1
> return bgColor
> else
> bgColor = 0
> return bgColor
> end if
> End Function
> When i put my method call in the background color on the entire table ROW,
> the result was alternating COLUMN colors. This is because the method was
> called for every cell (column) in the row. In order to get alternating ROW
> color, I only called the alternateColor routine in the FIRST column in the
> table row (iif(Code.alternateColor() = 0, "white", "grey")). Each subsequent
> column in the row would simply check the "Code.bgColor" value for its
> current value, and base its color on that (iif(Code.bgColor = 0, "white",
> "grey")).
> Maybe this will come in handy for someone else someday....
> Brian
> "G" wrote in message
> news:OSJrwjtYFHA.1152@.tk2msftngp13.phx.gbl...
> > Got a dataset that is used to populate a table. Want to alternate the
> > background color on every other row in the displayed detail group. Easy
> > enough right? Here's the catch: the output is grouped at display time. A
> > query output might be:
> >
> > KEY Value1 Value2
> > A 0 1
> > A 1 0
> > B 5 0
> > C 3 0
> > C 0 7
> >
> > etc...
> >
> > The DISPLAY output is grouped on the KEY, and the two values are summed to
> > give me a display such as:
> >
> > KEY Value1 Value2
> > A 1 1
> > B 5 0
> > C 3 7
> >
> > Problem. When I use the standard "=iif(RowNumber(Nothing) MOD 2, "White",
> > "Grey")", it counts EVERY row returned from the original query, not the
> > grouped output, so I don't get a uniform white-grey-white pattern. Anyone
> > know a workaround for this?
> >
> > TIA,
> >
> > Brian
> >
>
>

a simple question on compatibility

Hello,

I was wondering if there is a version conflict with SSRS for SQL 2000 and Visual Studio 2005? When I went to install the client it was looking for visual studio 2003 and well, I don't have that. Asides from stating it during the install, the client appeared to fully install and of course when I opened VS, no option for SSRS.

I was installing the client over a WAN and figured I should ask before I go on a bug hunt for what I did wrong.

Thanks

VS 2005 will produce 2k5 reports which can′t be deployed on a SQL 2k RS.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

A Simple Insert statement in European version of SQL

I have a client that uses my utility program to insert a record into a
table. Once my program receives the values, creates an insert statement
with comma separated values. Ie:
Insert into T (a,b) values (1.578, 2)
I now understand that those numeric values could have ',' in place of
decimal point for European version. So, the above values would look like
1,578 and 2.
How does the insert statement would know ',' is not a separator in this
case ? Ie:
Insert into T (a,b) values (1,578, 2) --> resulting
into syntax error
Should I be using a different value separator character ?
TIA.
MacI am not the one producing the values with ',' in place of decimal points.
It is SQL Server of European version that returns the data I am collecting
with embedded comma. So, when I submit a query of "Select a from T"
( a is defined to be a real type number), it returns 2,476 instead of
2.476. So, my question is how do I take these returned value with
embedded comma and insert them back into say another field in a table ? I
do not have such SQL version in my site to see what is going on and how to
accomplish such inserts.
- Mac
""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> Hi Mac,
> Please do not use comma as decimal separator. It will cause problems. Use
> period as decimal point.
> Character expressions being converted to an exact numeric data type must
> consist of digits, a decimal point, and an optional plus (+) or minus (-).
> Leading blanks are ignored. Comma separators (such as the thousands
> separator in 123,456.00) are not allowed in the string.
>
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --
> | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> | Newsgroups: microsoft.public.sqlserver.server
> | Subject: A Simple Insert statement in European version of SQL
> | Date: Mon, 25 Aug 2003 16:49:15 -0700
> | Organization: Unisys - Roseville, MN
> | Lines: 20
> | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> | NNTP-Posting-Host: 192.59.171.175
> | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> 2003 23:49:15 GMT)
> | X-Complaints-To: news@.rsvl.unisys.com
> | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> | Path:
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I have a client that uses my utility program to insert a record into a
> | table. Once my program receives the values, creates an insert
statement
> | with comma separated values. Ie:
> | Insert into T (a,b) values (1.578, 2)
> |
> | I now understand that those numeric values could have ',' in place of
> | decimal point for European version. So, the above values would look
> like
> | 1,578 and 2.
> |
> | How does the insert statement would know ',' is not a separator in this
> | case ? Ie:
> | Insert into T (a,b) values (1,578, 2) --> resulting
> | into syntax error
> |
> | Should I be using a different value separator character ?
> |
> | TIA.
> | Mac
> |
> |
> |
>|||Are you using Visual basic ?
VB does use the client-settings to format numbers, dates,...
You'll have to use a format function to convert to a propre string.
e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
"###0.00") & ")"
jobi
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:big51p$5vu$1@.si05.rsvl.unisys.com...
> I am not the one producing the values with ',' in place of decimal points.
> It is SQL Server of European version that returns the data I am collecting
> with embedded comma. So, when I submit a query of "Select a from T"
> ( a is defined to be a real type number), it returns 2,476 instead of
> 2.476. So, my question is how do I take these returned value with
> embedded comma and insert them back into say another field in a table ?
I
> do not have such SQL version in my site to see what is going on and how to
> accomplish such inserts.
> - Mac
>
> ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > Hi Mac,
> >
> > Please do not use comma as decimal separator. It will cause problems.
Use
> > period as decimal point.
> >
> > Character expressions being converted to an exact numeric data type must
> > consist of digits, a decimal point, and an optional plus (+) or minus
(-).
> > Leading blanks are ignored. Comma separators (such as the thousands
> > separator in 123,456.00) are not allowed in the string.
> >
> >
> >
> > Bill Cheng
> > Microsoft Online Partner Support
> >
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> > --
> > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > | Newsgroups: microsoft.public.sqlserver.server
> > | Subject: A Simple Insert statement in European version of SQL
> > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > | Organization: Unisys - Roseville, MN
> > | Lines: 20
> > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > | NNTP-Posting-Host: 192.59.171.175
> > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> > 2003 23:49:15 GMT)
> > | X-Complaints-To: news@.rsvl.unisys.com
> > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > | X-Priority: 3
> > | X-MSMail-Priority: Normal
> > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > | Path:
> >
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> >
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > |
> > | I have a client that uses my utility program to insert a record into a
> > | table. Once my program receives the values, creates an insert
> statement
> > | with comma separated values. Ie:
> > | Insert into T (a,b) values (1.578, 2)
> > |
> > | I now understand that those numeric values could have ',' in place of
> > | decimal point for European version. So, the above values would look
> > like
> > | 1,578 and 2.
> > |
> > | How does the insert statement would know ',' is not a separator in
this
> > | case ? Ie:
> > | Insert into T (a,b) values (1,578, 2) -->
resulting
> > | into syntax error
> > |
> > | Should I be using a different value separator character ?
> > |
> > | TIA.
> > | Mac
> > |
> > |
> > |
> >
>|||As jobi point out: You have to differentiate between input and output. Just because a client tool
formats something that SQL Server outputs with a comma doesn't mean that SQL Server accepts that as
a valid input format.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"jobi" <jobi@.reply2.group> wrote in message news:bihkck$boq$1@.reader08.wxs.nl...
> Are you using Visual basic ?
> VB does use the client-settings to format numbers, dates,...
> You'll have to use a format function to convert to a propre string.
> e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
> "###0.00") & ")"
> jobi
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:big51p$5vu$1@.si05.rsvl.unisys.com...
> > I am not the one producing the values with ',' in place of decimal points.
> > It is SQL Server of European version that returns the data I am collecting
> > with embedded comma. So, when I submit a query of "Select a from T"
> > ( a is defined to be a real type number), it returns 2,476 instead of
> > 2.476. So, my question is how do I take these returned value with
> > embedded comma and insert them back into say another field in a table ?
> I
> > do not have such SQL version in my site to see what is going on and how to
> > accomplish such inserts.
> >
> > - Mac
> >
> >
> > ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> > news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > > Hi Mac,
> > >
> > > Please do not use comma as decimal separator. It will cause problems.
> Use
> > > period as decimal point.
> > >
> > > Character expressions being converted to an exact numeric data type must
> > > consist of digits, a decimal point, and an optional plus (+) or minus
> (-).
> > > Leading blanks are ignored. Comma separators (such as the thousands
> > > separator in 123,456.00) are not allowed in the string.
> > >
> > >
> > >
> > > Bill Cheng
> > > Microsoft Online Partner Support
> > >
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > > --
> > > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > > | Newsgroups: microsoft.public.sqlserver.server
> > > | Subject: A Simple Insert statement in European version of SQL
> > > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > > | Organization: Unisys - Roseville, MN
> > > | Lines: 20
> > > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > > | NNTP-Posting-Host: 192.59.171.175
> > > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> > > 2003 23:49:15 GMT)
> > > | X-Complaints-To: news@.rsvl.unisys.com
> > > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > > | X-Priority: 3
> > > | X-MSMail-Priority: Normal
> > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > > | Path:
> > >
> >
> cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> > >
> >
> m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> > > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > > |
> > > | I have a client that uses my utility program to insert a record into a
> > > | table. Once my program receives the values, creates an insert
> > statement
> > > | with comma separated values. Ie:
> > > | Insert into T (a,b) values (1.578, 2)
> > > |
> > > | I now understand that those numeric values could have ',' in place of
> > > | decimal point for European version. So, the above values would look
> > > like
> > > | 1,578 and 2.
> > > |
> > > | How does the insert statement would know ',' is not a separator in
> this
> > > | case ? Ie:
> > > | Insert into T (a,b) values (1,578, 2) -->
> resulting
> > > | into syntax error
> > > |
> > > | Should I be using a different value separator character ?
> > > |
> > > | TIA.
> > > | Mac
> > > |
> > > |
> > > |
> > >
> >
> >
>|||Mac,
I've checked my vb-code again, and found this extra.
'Aparently the format still uses the client-setting for decimal point !!!
replace$(string, ",",".")
so you'll have to come up to this :
e.g. strsql = "insert into table1 (col1) values (" &
Replace$(Format(numcol, "###0.00"),",",".") & ")"
I guess you don't need the ###-part, in fact, you only need the format if
you want control of the format, else you can use the cstr-function.
Replace$(CStr(numcol), ",", ".")
jobi
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:bijdkf$2fsd$1@.si05.rsvl.unisys.com...
> Jobi,
> Yes. I am using VB. Also when I am collecting the data, I use Format
> function to make sure I only get 2 digits decimal point. The format
> sysntax I use is following:
> Format(numValue, "0.00").
> Is this not correct ? Do I need the ### in front of them ? More like
aVB
> question...
> By using the Format function I thought I am also forcing the decimal point
> to show up as decimal point despite the local setting of the computer.
This
> way then I can turn around and use the result in another insert statement
> without further formatting.
> Thanks for the input.
> Mac
> "jobi" <jobi@.reply2.group> wrote in message
> news:bihkck$boq$1@.reader08.wxs.nl...
> > Are you using Visual basic ?
> >
> > VB does use the client-settings to format numbers, dates,...
> > You'll have to use a format function to convert to a propre string.
> >
> > e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
> > "###0.00") & ")"
> >
> > jobi
> > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > news:big51p$5vu$1@.si05.rsvl.unisys.com...
> > > I am not the one producing the values with ',' in place of decimal
> points.
> > > It is SQL Server of European version that returns the data I am
> collecting
> > > with embedded comma. So, when I submit a query of "Select a from
T"
> > > ( a is defined to be a real type number), it returns 2,476 instead
of
> > > 2.476. So, my question is how do I take these returned value with
> > > embedded comma and insert them back into say another field in a table
?
> > I
> > > do not have such SQL version in my site to see what is going on and
how
> to
> > > accomplish such inserts.
> > >
> > > - Mac
> > >
> > >
> > > ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> > > news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > > > Hi Mac,
> > > >
> > > > Please do not use comma as decimal separator. It will cause
problems.
> > Use
> > > > period as decimal point.
> > > >
> > > > Character expressions being converted to an exact numeric data type
> must
> > > > consist of digits, a decimal point, and an optional plus (+) or
minus
> > (-).
> > > > Leading blanks are ignored. Comma separators (such as the thousands
> > > > separator in 123,456.00) are not allowed in the string.
> > > >
> > > >
> > > >
> > > > Bill Cheng
> > > > Microsoft Online Partner Support
> > > >
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > > --
> > > > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > > > | Newsgroups: microsoft.public.sqlserver.server
> > > > | Subject: A Simple Insert statement in European version of SQL
> > > > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > > > | Organization: Unisys - Roseville, MN
> > > > | Lines: 20
> > > > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > > > | NNTP-Posting-Host: 192.59.171.175
> > > > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25
> Aug
> > > > 2003 23:49:15 GMT)
> > > > | X-Complaints-To: news@.rsvl.unisys.com
> > > > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > > > | X-Priority: 3
> > > > | X-MSMail-Priority: Normal
> > > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > > > | Path:
> > > >
> > >
> >
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> > > >
> > >
> >
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > > > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > > > | Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.server:303093
> > > > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > > > |
> > > > | I have a client that uses my utility program to insert a record
into
> a
> > > > | table. Once my program receives the values, creates an insert
> > > statement
> > > > | with comma separated values. Ie:
> > > > | Insert into T (a,b) values (1.578, 2)
> > > > |
> > > > | I now understand that those numeric values could have ',' in
place
> of
> > > > | decimal point for European version. So, the above values would
> look
> > > > like
> > > > | 1,578 and 2.
> > > > |
> > > > | How does the insert statement would know ',' is not a separator
in
> > this
> > > > | case ? Ie:
> > > > | Insert into T (a,b) values (1,578, 2) -->
> > resulting
> > > > | into syntax error
> > > > |
> > > > | Should I be using a different value separator character ?
> > > > |
> > > > | TIA.
> > > > | Mac
> > > > |
> > > > |
> > > > |
> > > >
> > >
> > >
> >
> >
>

A problem with connecting to a Oct. CTP server

Hi, folks
I think I need a little help here. I uninstall the beta2 version and installed Oct. CTP. The server was using named-instance so I took it as it was. Installatoin went fine and attached privious databases in SQL Management Studio. That means, I am able to sign on the SQL server in the same box using SQL Management Studio. However, from developers (I believe it's beta2 version) could not register this server (my-dev02\sql2005) on their SQL Management Studio.

The error is
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (.Net SqlClient Data Provider)

I tried from another server (signed on as Domain Admin) using Windows Auth. I got same error msg. IPAddress is enabled, TCP/IP also enabled... Could not think of any more setting. Appreciate in advance.

SunnyFound a trick that is turn SQL Browser service on. Some how it disabled.|||As you found out, as a result of the security considerations, it's off by default for certain SKUs.

a ouple of questions about ms sql server

Hi,
I have a few questions about sql 2005 as follows:
1. Which MS SQL version (edtion) is good as database to
support a midium size web size?
2. I have old *.mdf and .ldf file from ms sql 2000.
Does it work if I just copy them (or just *.mdf file) to
2005 sql server (any edition).

TIA,
steveOn Tue, 16 Oct 2007 17:36:34 -0700, aaabbb16@.hotmail.com wrote:

Quote:

Originally Posted by

>Hi,
>I have a few questions about sql 2005 as follows:
>1. Which MS SQL version (edtion) is good as database to
support a midium size web size?
>2. I have old *.mdf and .ldf file from ms sql 2000.
Does it work if I just copy them (or just *.mdf file) to
2005 sql server (any edition).
>
>TIA,
>steve

Hi Steve,

You posted the same question to another group, where it has already been
answered.

May I kindly request that your future posts go to one group only? And if
you really feel that a question belongs in more than one group, would
you then please use the crossposting feature, so that answers
automatically show up in all groups as well? Many thanks in advance!

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On 10 18 , 5 19 , Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

On Tue, 16 Oct 2007 17:36:34 -0700, aaabb...@.hotmail.com wrote:

Quote:

Originally Posted by

Hi,
I have a few questions about sql 2005 as follows:
1. Which MS SQL version (edtion) is good as database to
support a midium size web size?
2. I have old *.mdf and .ldf file from ms sql 2000.
Does it work if I just copy them (or just *.mdf file) to
2005 sql server (any edition).

>

Quote:

Originally Posted by

TIA,
steve

>
Hi Steve,
>
You posted the same question to another group, where it has already been
answered.
>
May I kindly request that your future posts go to one group only? And if
you really feel that a question belongs in more than one group, would
you then please use the crossposting feature, so that answers
automatically show up in all groups as well? Many thanks in advance!
>
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Thanks!! I will.

A fix for slow running SP, but why?

OK, so we have these three SPs that are causing no problem.
We start developing version 2 of the database, and though we've made
minimal changes to these routines, changing parameter one from GUID to
Int, same as we've done to hundreds of routines, suddenly these
routines run 1000x more slowly (both reads and CPU) when called from
the app. The same commands taken from a trace log and run in QA run
at full speed. OK, this happens and is hard to track down, but here's
the kicker. Turns out parameter two was called @.foo_date but was
declared as varchar(32), the better to append a time of day to it when
passed in, I guess. Well, we redeclared it as the datetime it should
have been, and voila, speed is back to normal.
OK, I understand why this might fix things, but I do not understand
why it was not also broken in the V1 database, and not broken even in
V2 when called from QA.
Voodoo is a good explanation, but I'm open to others.
Could it be a change in the clients? Well, we actually invoked these
SPs from a couple of different clients, some were unchanged (well,
except for the new datatype of parameter one!). But that still
wouldn't really explain why it was still OK when called from QA.
Thanks!
JoshWhen you called it from the app you most likely have a parameter object that
tells it what the datatype is. In this case a varchar. When you run it
adhoc in QA it most likely did an implicit conversion to datetime as it
passed it in to the sp. You should always declare the parameters to be
exactly what they need to be to correspond to the columns you are matching
them to in the WHERE clause. Other wise you leave it up to chance as to
what you get.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:ecbvg1h8rrq1e8mc9rm42k98a40g064ac2@.
4ax.com...
> OK, so we have these three SPs that are causing no problem.
> We start developing version 2 of the database, and though we've made
> minimal changes to these routines, changing parameter one from GUID to
> Int, same as we've done to hundreds of routines, suddenly these
> routines run 1000x more slowly (both reads and CPU) when called from
> the app. The same commands taken from a trace log and run in QA run
> at full speed. OK, this happens and is hard to track down, but here's
> the kicker. Turns out parameter two was called @.foo_date but was
> declared as varchar(32), the better to append a time of day to it when
> passed in, I guess. Well, we redeclared it as the datetime it should
> have been, and voila, speed is back to normal.
> OK, I understand why this might fix things, but I do not understand
> why it was not also broken in the V1 database, and not broken even in
> V2 when called from QA.
> Voodoo is a good explanation, but I'm open to others.
> Could it be a change in the clients? Well, we actually invoked these
> SPs from a couple of different clients, some were unchanged (well,
> except for the new datatype of parameter one!). But that still
> wouldn't really explain why it was still OK when called from QA.
> Thanks!
> Josh
>|||jxstern (jxstern@.nowhere.xyz) writes:
> We start developing version 2 of the database, and though we've made
> minimal changes to these routines, changing parameter one from GUID to
> Int, same as we've done to hundreds of routines, suddenly these
> routines run 1000x more slowly (both reads and CPU) when called from
> the app. The same commands taken from a trace log and run in QA run
> at full speed. OK, this happens and is hard to track down, but here's
> the kicker. Turns out parameter two was called @.foo_date but was
> declared as varchar(32), the better to append a time of day to it when
> passed in, I guess. Well, we redeclared it as the datetime it should
> have been, and voila, speed is back to normal.
> OK, I understand why this might fix things, but I do not understand
> why it was not also broken in the V1 database, and not broken even in
> V2 when called from QA.
> Voodoo is a good explanation, but I'm open to others.
Voodoo is rarely an accurate explanation to these sort of problems. But
unfortunately, the way people present the problems, the amount of
information presented is so incomplete, that there is not much more to
suggest that just voodoo.
Andrew offered some speculations. I could offer a few more about clients
and Query Analyzer having different default settings for ARITHBORT ON.
But really, without the seeing the code, the tables and how it was called,
I really don't want to go into speculation.
But as a hint, performing problems in SQL Server are rarely resovled
by sticking pins into dolls of Bill Gates and Steve Ballmer.
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 Fri, 26 Aug 2005 21:11:41 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>When you called it from the app you most likely have a parameter object tha
t
>tells it what the datatype is. In this case a varchar. When you run it
>adhoc in QA it most likely did an implicit conversion to datetime as it
>passed it in to the sp. You should always declare the parameters to be
>exactly what they need to be to correspond to the columns you are matching
>them to in the WHERE clause. Other wise you leave it up to chance as to
>what you get.
The code in V2 is something like:
create procedure dbo.myfoov2(
myrowid int,
@.foo_date varchar(32)
)
as
begin
declare @.realdate datetime
set @.realdate = @.foo_date + ' 23.59.997'
...
end
The invocation is something like:
exec dbo.myfoov1 @.myrowid = 123, @.foo_date = '8/24/2005'
The declaration and code are the same in the V1 and V2 databases. I
can't see how QA or ODBC or whatever would know to coerce the string
one way or another.
Sure, *something* must be different, one would suspect some more
direct use of @.foo_date down in the body of the code that, in one
case, the optimizer recognizes as deterministic and in the other case
it does not.
About the only change I know of is that in V1 we were using GUIDs:
create procedure dbo.myfoov1(
myrowid uniqueidentifier, <--
@.foo_date varchar(32)
)
...
And of course the invocation used GUIDs not ints.
But why that should have a side-effect on the coercion farther down of
@.foo_date, ... does that question trigger anything for anybody?
Unlikely, I know, but if it were obvious I wouldn't be asking.
Thanks.
J.|||Well what was the query plan difference between the slow and fast ones? I
have to assume the fast one was using an index and the slow one wasn't.
It's hard to believe the datetime (or varchar) played too much a part since
it isn't used in the WHERE clause. Or at least I assume it doesn't since
you didn't actually show that part<g>. Knowing what the difference in the
plans will mainly dictate where to look.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:8j34h1lj1u80l8jtngtlnfkh4h67oq9onp@.
4ax.com...
> On Fri, 26 Aug 2005 21:11:41 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
> The code in V2 is something like:
> create procedure dbo.myfoov2(
> myrowid int,
> @.foo_date varchar(32)
> )
> as
> begin
> declare @.realdate datetime
> set @.realdate = @.foo_date + ' 23.59.997'
> ...
> end
> The invocation is something like:
> exec dbo.myfoov1 @.myrowid = 123, @.foo_date = '8/24/2005'
> The declaration and code are the same in the V1 and V2 databases. I
> can't see how QA or ODBC or whatever would know to coerce the string
> one way or another.
> Sure, *something* must be different, one would suspect some more
> direct use of @.foo_date down in the body of the code that, in one
> case, the optimizer recognizes as deterministic and in the other case
> it does not.
> About the only change I know of is that in V1 we were using GUIDs:
> create procedure dbo.myfoov1(
> myrowid uniqueidentifier, <--
> @.foo_date varchar(32)
> )
> ...
> And of course the invocation used GUIDs not ints.
> But why that should have a side-effect on the coercion farther down of
> @.foo_date, ... does that question trigger anything for anybody?
> Unlikely, I know, but if it were obvious I wouldn't be asking.
> Thanks.
> J.
>|||On Sun, 28 Aug 2005 19:08:18 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Well what was the query plan difference between the slow and fast ones? I
>have to assume the fast one was using an index and the slow one wasn't.
>It's hard to believe the datetime (or varchar) played too much a part since
>it isn't used in the WHERE clause. Or at least I assume it doesn't since
>you didn't actually show that part<g>. Knowing what the difference in the
>plans will mainly dictate where to look.
The actual routines were long and complex, and I'll have to break this
again (my luck, it won't break!) to get a bad plan to compare.
The question will remain, however, why the plans differed, given the
setup.
J.

A fix for slow running SP, but why?

OK, so we have these three SPs that are causing no problem.
We start developing version 2 of the database, and though we've made
minimal changes to these routines, changing parameter one from GUID to
Int, same as we've done to hundreds of routines, suddenly these
routines run 1000x more slowly (both reads and CPU) when called from
the app. The same commands taken from a trace log and run in QA run
at full speed. OK, this happens and is hard to track down, but here's
the kicker. Turns out parameter two was called @.foo_date but was
declared as varchar(32), the better to append a time of day to it when
passed in, I guess. Well, we redeclared it as the datetime it should
have been, and voila, speed is back to normal.
OK, I understand why this might fix things, but I do not understand
why it was not also broken in the V1 database, and not broken even in
V2 when called from QA.
Voodoo is a good explanation, but I'm open to others.
Could it be a change in the clients? Well, we actually invoked these
SPs from a couple of different clients, some were unchanged (well,
except for the new datatype of parameter one!). But that still
wouldn't really explain why it was still OK when called from QA.
Thanks!
Josh
When you called it from the app you most likely have a parameter object that
tells it what the datatype is. In this case a varchar. When you run it
adhoc in QA it most likely did an implicit conversion to datetime as it
passed it in to the sp. You should always declare the parameters to be
exactly what they need to be to correspond to the columns you are matching
them to in the WHERE clause. Other wise you leave it up to chance as to
what you get.
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:ecbvg1h8rrq1e8mc9rm42k98a40g064ac2@.4ax.com...
> OK, so we have these three SPs that are causing no problem.
> We start developing version 2 of the database, and though we've made
> minimal changes to these routines, changing parameter one from GUID to
> Int, same as we've done to hundreds of routines, suddenly these
> routines run 1000x more slowly (both reads and CPU) when called from
> the app. The same commands taken from a trace log and run in QA run
> at full speed. OK, this happens and is hard to track down, but here's
> the kicker. Turns out parameter two was called @.foo_date but was
> declared as varchar(32), the better to append a time of day to it when
> passed in, I guess. Well, we redeclared it as the datetime it should
> have been, and voila, speed is back to normal.
> OK, I understand why this might fix things, but I do not understand
> why it was not also broken in the V1 database, and not broken even in
> V2 when called from QA.
> Voodoo is a good explanation, but I'm open to others.
> Could it be a change in the clients? Well, we actually invoked these
> SPs from a couple of different clients, some were unchanged (well,
> except for the new datatype of parameter one!). But that still
> wouldn't really explain why it was still OK when called from QA.
> Thanks!
> Josh
>
|||jxstern (jxstern@.nowhere.xyz) writes:
> We start developing version 2 of the database, and though we've made
> minimal changes to these routines, changing parameter one from GUID to
> Int, same as we've done to hundreds of routines, suddenly these
> routines run 1000x more slowly (both reads and CPU) when called from
> the app. The same commands taken from a trace log and run in QA run
> at full speed. OK, this happens and is hard to track down, but here's
> the kicker. Turns out parameter two was called @.foo_date but was
> declared as varchar(32), the better to append a time of day to it when
> passed in, I guess. Well, we redeclared it as the datetime it should
> have been, and voila, speed is back to normal.
> OK, I understand why this might fix things, but I do not understand
> why it was not also broken in the V1 database, and not broken even in
> V2 when called from QA.
> Voodoo is a good explanation, but I'm open to others.
Voodoo is rarely an accurate explanation to these sort of problems. But
unfortunately, the way people present the problems, the amount of
information presented is so incomplete, that there is not much more to
suggest that just voodoo.
Andrew offered some speculations. I could offer a few more about clients
and Query Analyzer having different default settings for ARITHBORT ON.
But really, without the seeing the code, the tables and how it was called,
I really don't want to go into speculation.
But as a hint, performing problems in SQL Server are rarely resovled
by sticking pins into dolls of Bill Gates and Steve Ballmer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||On Fri, 26 Aug 2005 21:11:41 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>When you called it from the app you most likely have a parameter object that
>tells it what the datatype is. In this case a varchar. When you run it
>adhoc in QA it most likely did an implicit conversion to datetime as it
>passed it in to the sp. You should always declare the parameters to be
>exactly what they need to be to correspond to the columns you are matching
>them to in the WHERE clause. Other wise you leave it up to chance as to
>what you get.
The code in V2 is something like:
create procedure dbo.myfoov2(
myrowid int,
@.foo_date varchar(32)
)
as
begin
declare @.realdate datetime
set @.realdate = @.foo_date + ' 23.59.997'
...
end
The invocation is something like:
exec dbo.myfoov1 @.myrowid = 123, @.foo_date = '8/24/2005'
The declaration and code are the same in the V1 and V2 databases. I
can't see how QA or ODBC or whatever would know to coerce the string
one way or another.
Sure, *something* must be different, one would suspect some more
direct use of @.foo_date down in the body of the code that, in one
case, the optimizer recognizes as deterministic and in the other case
it does not.
About the only change I know of is that in V1 we were using GUIDs:
create procedure dbo.myfoov1(
myrowid uniqueidentifier, <--
@.foo_date varchar(32)
)
...
And of course the invocation used GUIDs not ints.
But why that should have a side-effect on the coercion farther down of
@.foo_date, ... does that question trigger anything for anybody?
Unlikely, I know, but if it were obvious I wouldn't be asking.
Thanks.
J.
|||Well what was the query plan difference between the slow and fast ones? I
have to assume the fast one was using an index and the slow one wasn't.
It's hard to believe the datetime (or varchar) played too much a part since
it isn't used in the WHERE clause. Or at least I assume it doesn't since
you didn't actually show that part<g>. Knowing what the difference in the
plans will mainly dictate where to look.
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:8j34h1lj1u80l8jtngtlnfkh4h67oq9onp@.4ax.com...
> On Fri, 26 Aug 2005 21:11:41 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
> The code in V2 is something like:
> create procedure dbo.myfoov2(
> myrowid int,
> @.foo_date varchar(32)
> )
> as
> begin
> declare @.realdate datetime
> set @.realdate = @.foo_date + ' 23.59.997'
> ...
> end
> The invocation is something like:
> exec dbo.myfoov1 @.myrowid = 123, @.foo_date = '8/24/2005'
> The declaration and code are the same in the V1 and V2 databases. I
> can't see how QA or ODBC or whatever would know to coerce the string
> one way or another.
> Sure, *something* must be different, one would suspect some more
> direct use of @.foo_date down in the body of the code that, in one
> case, the optimizer recognizes as deterministic and in the other case
> it does not.
> About the only change I know of is that in V1 we were using GUIDs:
> create procedure dbo.myfoov1(
> myrowid uniqueidentifier, <--
> @.foo_date varchar(32)
> )
> ...
> And of course the invocation used GUIDs not ints.
> But why that should have a side-effect on the coercion farther down of
> @.foo_date, ... does that question trigger anything for anybody?
> Unlikely, I know, but if it were obvious I wouldn't be asking.
> Thanks.
> J.
>
|||On Sun, 28 Aug 2005 19:08:18 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Well what was the query plan difference between the slow and fast ones? I
>have to assume the fast one was using an index and the slow one wasn't.
>It's hard to believe the datetime (or varchar) played too much a part since
>it isn't used in the WHERE clause. Or at least I assume it doesn't since
>you didn't actually show that part<g>. Knowing what the difference in the
>plans will mainly dictate where to look.
The actual routines were long and complex, and I'll have to break this
again (my luck, it won't break!) to get a bad plan to compare.
The question will remain, however, why the plans differed, given the
setup.
J.

A fix for slow running SP, but why?

OK, so we have these three SPs that are causing no problem.
We start developing version 2 of the database, and though we've made
minimal changes to these routines, changing parameter one from GUID to
Int, same as we've done to hundreds of routines, suddenly these
routines run 1000x more slowly (both reads and CPU) when called from
the app. The same commands taken from a trace log and run in QA run
at full speed. OK, this happens and is hard to track down, but here's
the kicker. Turns out parameter two was called @.foo_date but was
declared as varchar(32), the better to append a time of day to it when
passed in, I guess. Well, we redeclared it as the datetime it should
have been, and voila, speed is back to normal.
OK, I understand why this might fix things, but I do not understand
why it was not also broken in the V1 database, and not broken even in
V2 when called from QA.
Voodoo is a good explanation, but I'm open to others.
Could it be a change in the clients? Well, we actually invoked these
SPs from a couple of different clients, some were unchanged (well,
except for the new datatype of parameter one!). But that still
wouldn't really explain why it was still OK when called from QA.
Thanks!
JoshWhen you called it from the app you most likely have a parameter object that
tells it what the datatype is. In this case a varchar. When you run it
adhoc in QA it most likely did an implicit conversion to datetime as it
passed it in to the sp. You should always declare the parameters to be
exactly what they need to be to correspond to the columns you are matching
them to in the WHERE clause. Other wise you leave it up to chance as to
what you get.
--
Andrew J. Kelly SQL MVP
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:ecbvg1h8rrq1e8mc9rm42k98a40g064ac2@.4ax.com...
> OK, so we have these three SPs that are causing no problem.
> We start developing version 2 of the database, and though we've made
> minimal changes to these routines, changing parameter one from GUID to
> Int, same as we've done to hundreds of routines, suddenly these
> routines run 1000x more slowly (both reads and CPU) when called from
> the app. The same commands taken from a trace log and run in QA run
> at full speed. OK, this happens and is hard to track down, but here's
> the kicker. Turns out parameter two was called @.foo_date but was
> declared as varchar(32), the better to append a time of day to it when
> passed in, I guess. Well, we redeclared it as the datetime it should
> have been, and voila, speed is back to normal.
> OK, I understand why this might fix things, but I do not understand
> why it was not also broken in the V1 database, and not broken even in
> V2 when called from QA.
> Voodoo is a good explanation, but I'm open to others.
> Could it be a change in the clients? Well, we actually invoked these
> SPs from a couple of different clients, some were unchanged (well,
> except for the new datatype of parameter one!). But that still
> wouldn't really explain why it was still OK when called from QA.
> Thanks!
> Josh
>|||jxstern (jxstern@.nowhere.xyz) writes:
> We start developing version 2 of the database, and though we've made
> minimal changes to these routines, changing parameter one from GUID to
> Int, same as we've done to hundreds of routines, suddenly these
> routines run 1000x more slowly (both reads and CPU) when called from
> the app. The same commands taken from a trace log and run in QA run
> at full speed. OK, this happens and is hard to track down, but here's
> the kicker. Turns out parameter two was called @.foo_date but was
> declared as varchar(32), the better to append a time of day to it when
> passed in, I guess. Well, we redeclared it as the datetime it should
> have been, and voila, speed is back to normal.
> OK, I understand why this might fix things, but I do not understand
> why it was not also broken in the V1 database, and not broken even in
> V2 when called from QA.
> Voodoo is a good explanation, but I'm open to others.
Voodoo is rarely an accurate explanation to these sort of problems. But
unfortunately, the way people present the problems, the amount of
information presented is so incomplete, that there is not much more to
suggest that just voodoo.
Andrew offered some speculations. I could offer a few more about clients
and Query Analyzer having different default settings for ARITHBORT ON.
But really, without the seeing the code, the tables and how it was called,
I really don't want to go into speculation.
But as a hint, performing problems in SQL Server are rarely resovled
by sticking pins into dolls of Bill Gates and Steve Ballmer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||On Fri, 26 Aug 2005 21:11:41 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>When you called it from the app you most likely have a parameter object that
>tells it what the datatype is. In this case a varchar. When you run it
>adhoc in QA it most likely did an implicit conversion to datetime as it
>passed it in to the sp. You should always declare the parameters to be
>exactly what they need to be to correspond to the columns you are matching
>them to in the WHERE clause. Other wise you leave it up to chance as to
>what you get.
The code in V2 is something like:
create procedure dbo.myfoov2(
myrowid int,
@.foo_date varchar(32)
)
as
begin
declare @.realdate datetime
set @.realdate = @.foo_date + ' 23.59.997'
...
end
The invocation is something like:
exec dbo.myfoov1 @.myrowid = 123, @.foo_date = '8/24/2005'
The declaration and code are the same in the V1 and V2 databases. I
can't see how QA or ODBC or whatever would know to coerce the string
one way or another.
Sure, *something* must be different, one would suspect some more
direct use of @.foo_date down in the body of the code that, in one
case, the optimizer recognizes as deterministic and in the other case
it does not.
About the only change I know of is that in V1 we were using GUIDs:
create procedure dbo.myfoov1(
myrowid uniqueidentifier, <--
@.foo_date varchar(32)
)
...
And of course the invocation used GUIDs not ints.
But why that should have a side-effect on the coercion farther down of
@.foo_date, ... does that question trigger anything for anybody?
Unlikely, I know, but if it were obvious I wouldn't be asking.
Thanks.
J.|||Well what was the query plan difference between the slow and fast ones? I
have to assume the fast one was using an index and the slow one wasn't.
It's hard to believe the datetime (or varchar) played too much a part since
it isn't used in the WHERE clause. Or at least I assume it doesn't since
you didn't actually show that part<g>. Knowing what the difference in the
plans will mainly dictate where to look.
--
Andrew J. Kelly SQL MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:8j34h1lj1u80l8jtngtlnfkh4h67oq9onp@.4ax.com...
> On Fri, 26 Aug 2005 21:11:41 -0400, "Andrew J. Kelly"
> <sqlmvpnooospam@.shadhawk.com> wrote:
>>When you called it from the app you most likely have a parameter object
>>that
>>tells it what the datatype is. In this case a varchar. When you run it
>>adhoc in QA it most likely did an implicit conversion to datetime as it
>>passed it in to the sp. You should always declare the parameters to be
>>exactly what they need to be to correspond to the columns you are matching
>>them to in the WHERE clause. Other wise you leave it up to chance as to
>>what you get.
> The code in V2 is something like:
> create procedure dbo.myfoov2(
> myrowid int,
> @.foo_date varchar(32)
> )
> as
> begin
> declare @.realdate datetime
> set @.realdate = @.foo_date + ' 23.59.997'
> ...
> end
> The invocation is something like:
> exec dbo.myfoov1 @.myrowid = 123, @.foo_date = '8/24/2005'
> The declaration and code are the same in the V1 and V2 databases. I
> can't see how QA or ODBC or whatever would know to coerce the string
> one way or another.
> Sure, *something* must be different, one would suspect some more
> direct use of @.foo_date down in the body of the code that, in one
> case, the optimizer recognizes as deterministic and in the other case
> it does not.
> About the only change I know of is that in V1 we were using GUIDs:
> create procedure dbo.myfoov1(
> myrowid uniqueidentifier, <--
> @.foo_date varchar(32)
> )
> ...
> And of course the invocation used GUIDs not ints.
> But why that should have a side-effect on the coercion farther down of
> @.foo_date, ... does that question trigger anything for anybody?
> Unlikely, I know, but if it were obvious I wouldn't be asking.
> Thanks.
> J.
>|||On Sun, 28 Aug 2005 19:08:18 -0400, "Andrew J. Kelly"
<sqlmvpnooospam@.shadhawk.com> wrote:
>Well what was the query plan difference between the slow and fast ones? I
>have to assume the fast one was using an index and the slow one wasn't.
>It's hard to believe the datetime (or varchar) played too much a part since
>it isn't used in the WHERE clause. Or at least I assume it doesn't since
>you didn't actually show that part<g>. Knowing what the difference in the
>plans will mainly dictate where to look.
The actual routines were long and complex, and I'll have to break this
again (my luck, it won't break!) to get a bad plan to compare.
The question will remain, however, why the plans differed, given the
setup.
J.