problem with sqlserver 2005 express

I am using VS2005 with sql server 2005 express.
I have database with atached datafile. when I am using Select command from
created table, there is not case sensitive in the result. I mean I have in
the table the row with value "Disp1" in col1, and when I am selecting with
filter Where col1="disp1" , I amd getting this one row where value is
"Disp1".
I saw in server explorer window, that connection have parameter Case
Sensitive, which value is False, but it is desabled and I can't change.
How to solve this problem.
Thanks
Collation is an attribute of the column in the table. A database has a default collation which is
applied if you don't specify a collation in the CREATE TABLE statement. Also, if you want a query to
be resolved with a different collation than the data is stored with, you can use COLLATE keyword in
the query.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vaidas" <Vaidas@.discussions.microsoft.com> wrote in message
news:8E0BF60C-1A43-49F3-A41B-E1E05E30BE23@.microsoft.com...
>I am using VS2005 with sql server 2005 express.
> I have database with atached datafile. when I am using Select command from
> created table, there is not case sensitive in the result. I mean I have in
> the table the row with value "Disp1" in col1, and when I am selecting with
> filter Where col1="disp1" , I amd getting this one row where value is
> "Disp1".
> I saw in server explorer window, that connection have parameter Case
> Sensitive, which value is False, but it is desabled and I can't change.
> How to solve this problem.
> Thanks
>

problem with sqlserver 2005 express

I am using VS2005 with sql server 2005 express.
I have database with atached datafile. when I am using Select command from
created table, there is not case sensitive in the result. I mean I have in
the table the row with value "Disp1" in col1, and when I am selecting with
filter Where col1="disp1" , I amd getting this one row where value is
"Disp1".
I saw in server explorer window, that connection have parameter Case
Sensitive, which value is False, but it is desabled and I can't change.
How to solve this problem.
Thanks
Either change the collation of the column or specify the collation
explicitly in the WHERE clause. You should read about collations in the
documentation first so that you understand how they work and the
implications of mixed collations, the impact on indexing, etc.
Although this question isn't specific to the version in general you
should post questions about 2005 to the 2005 newsgroups only:
http://communities.microsoft.com/new...=sqlserver2005
David Portas
SQL Server MVP

problem with sqlserver 2005 express

I am using VS2005 with sql server 2005 express.
I have database with atached datafile. when I am using Select command from
created table, there is not case sensitive in the result. I mean I have in
the table the row with value "Disp1" in col1, and when I am selecting with
filter Where col1="disp1" , I amd getting this one row where value is
"Disp1".
I saw in server explorer window, that connection have parameter Case
Sensitive, which value is False, but it is desabled and I can't change.
How to solve this problem.
ThanksCollation is an attribute of the column in the table. A database has a default collation which is
applied if you don't specify a collation in the CREATE TABLE statement. Also, if you want a query to
be resolved with a different collation than the data is stored with, you can use COLLATE keyword in
the query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Vaidas" <Vaidas@.discussions.microsoft.com> wrote in message
news:8E0BF60C-1A43-49F3-A41B-E1E05E30BE23@.microsoft.com...
>I am using VS2005 with sql server 2005 express.
> I have database with atached datafile. when I am using Select command from
> created table, there is not case sensitive in the result. I mean I have in
> the table the row with value "Disp1" in col1, and when I am selecting with
> filter Where col1="disp1" , I amd getting this one row where value is
> "Disp1".
> I saw in server explorer window, that connection have parameter Case
> Sensitive, which value is False, but it is desabled and I can't change.
> How to solve this problem.
> Thanks
>

problem with sqlserver 2005 express

I am using VS2005 with sql server 2005 express.
I have database with atached datafile. when I am using Select command from
created table, there is not case sensitive in the result. I mean I have in
the table the row with value "Disp1" in col1, and when I am selecting with
filter Where col1="disp1" , I amd getting this one row where value is
"Disp1".
I saw in server explorer window, that connection have parameter Case
Sensitive, which value is False, but it is desabled and I can't change.
How to solve this problem.
ThanksUsing a case-sensitive collation such as Latin1_General_CS_AS for col1 when you create your table, or for the "=" comparison in your query should solve your problem. CS stands for "case sensitive" and AS for "Accent Sensitive." See
Working With Collations (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/61cdbb6b-3ca1-4d73-938b-22e4f06f75ea.htm) in SQL Server 2005 books online.

Eric

problem with sqlserver 2005 express

I am using VS2005 with sql server 2005 express.
I have database with atached datafile. when I am using Select command from
created table, there is not case sensitive in the result. I mean I have in
the table the row with value "Disp1" in col1, and when I am selecting with
filter Where col1="disp1" , I amd getting this one row where value is
"Disp1".
I saw in server explorer window, that connection have parameter Case
Sensitive, which value is False, but it is desabled and I can't change.
How to solve this problem.
ThanksEither change the collation of the column or specify the collation
explicitly in the WHERE clause. You should read about collations in the
documentation first so that you understand how they work and the
implications of mixed collations, the impact on indexing, etc.
Although this question isn't specific to the version in general you
should post questions about 2005 to the 2005 newsgroups only:
http://communities.microsoft.com/ne...p=sqlserver2005
David Portas
SQL Server MVP
--sql

Problem with SQL string using MS Access and OleDbConnection (ASP .NET)

The string concatination below works in the Query builder built-in to MS Access, but when I try it as an OleDbCommand it doesn't work.

SELECT ID, LastName + ', ' + FirstName AS Names FROM AgentNames;

Is there any other ways to return a combination like this using an OleDbCommand?

Thanks,

GrierOriginally posted by grier_allen
The string concatination below works in the Query builder built-in to MS Access, but when I try it as an OleDbCommand it doesn't work.

SELECT ID, LastName + ', ' + FirstName AS Names FROM AgentNames;

Is there any other ways to return a combination like this using an OleDbCommand?

Thanks,

Grier

Shot in the dark here but try [LastName + ',' + FirstName] as Names. If not, I dont know why that won't work.

Problem with SQL Service Pack 4

I upgraded from SQL 2000 Service Pack 3 to SQL 2000 Service Pack 4. The
install says it completed but after I rebooted and did a select @.@.version it
still shows SP3. I only have a default instance so I know that it installed
on the right instance. Could anyone help me out with this?You can review the setup log for any errors (file called sqlsp.log in the
windows directory). It is perfectly safe to run SP4 again to try and make it
stick (assuming you have the system database backups just in case you need
to regress)
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Leon Parker" <u23479@.uwe> wrote in message news:625d255c1f101@.uwe...
>I upgraded from SQL 2000 Service Pack 3 to SQL 2000 Service Pack 4. The
> install says it completed but after I rebooted and did a select @.@.version
> it
> still shows SP3. I only have a default instance so I know that it
> installed
> on the right instance. Could anyone help me out with this?|||I tried it several times but got the same result.
I've attached a copy of my sqlsp.log file. I looked at the sqlsp.log file
and it only listed one non-fatal error...I can't see this being the problem
considering it was nonfatal.
Jasper Smith wrote:
>You can review the setup log for any errors (file called sqlsp.log in the
>windows directory). It is perfectly safe to run SP4 again to try and make it
>stick (assuming you have the system database backups just in case you need
>to regress)
>>I upgraded from SQL 2000 Service Pack 3 to SQL 2000 Service Pack 4. The
>> install says it completed but after I rebooted and did a select @.@.version
>> it
>> still shows SP3. I only have a default instance so I know that it
>> installed
>> on the right instance. Could anyone help me out with this?|||What does the following query return?
SELECT SERVERPROPERTY('ProductLevel')
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Leon Parker" <u23479@.uwe> wrote in message news:625d255c1f101@.uwe...
>I upgraded from SQL 2000 Service Pack 3 to SQL 2000 Service Pack 4. The
> install says it completed but after I rebooted and did a select @.@.version
> it
> still shows SP3. I only have a default instance so I know that it
> installed
> on the right instance. Could anyone help me out with this?|||SP3....
Dan Guzman wrote:
>What does the following query return?
> SELECT SERVERPROPERTY('ProductLevel')
>>I upgraded from SQL 2000 Service Pack 3 to SQL 2000 Service Pack 4. The
>> install says it completed but after I rebooted and did a select @.@.version
>> it
>> still shows SP3. I only have a default instance so I know that it
>> installed
>> on the right instance. Could anyone help me out with this?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1sql

Problem with SQL select with duplicate field names

I am converting a large application so that it can run with SQL server and
Access via ADO into Delphi.
There are many places where it does "select table1.*, table2.* etc"
With Access if there are duplicate field names (e.g. "description") they are
returned fully qualified and you can reference them as "table1.description"
and "table2.description".
I have just discovered to my horror that SQL server will not qualify the
names for you and returns these field names as "description" and
"description1".
Is there any way of altering this behaviour to return the fully qualified
field names as with Access ?
I know I should rewrite the queries to give aliases to the fields but there
maybe 1000's of places in the code and I want to avoid this.
Thanks for any helpHi Andrew,
I am not sure I understood your issue.
Could you place DDL + Sample Data?
What I did not understand is where are the queries writen? In Access Queries
? In Delphi code? In Stored Procedures?
I hope I can help you if I get a better idea of the issue.
Bye,|||you must do it manually (and you always should in SQL)
Access protects the users from themselves. SQL doesnt protect you as much.
You can alias any field name with the "AS" key word:
SELECT MyField AS ThisName
Hope this helps
Greg Jackson
PDX, Oregonsql

Problem with SQL select with duplicate field names

I am converting a large application so that it can run with SQL server and
Access via ADO into Delphi.
There are many places where it does "select table1.*, table2.* etc"
With Access if there are duplicate field names (e.g. "description") they are
returned fully qualified and you can reference them as "table1.description"
and "table2.description".
I have just discovered to my horror that SQL server will not qualify the
names for you and returns these field names as "description" and
"description1".
Is there any way of altering this behaviour to return the fully qualified
field names as with Access ?
I know I should rewrite the queries to give aliases to the fields but there
maybe 1000's of places in the code and I want to avoid this.
Thanks for any helpyou must do it manually (and you always should in SQL)
Access protects the users from themselves. SQL doesnt protect you as much.
You can alias any field name with the "AS" key word:
SELECT MyField AS ThisName
Hope this helps
Greg Jackson
PDX, Oregon

Problem with SQL in ASP.NET...

Hello,

I have the following SELECT statement I am using in asp.net page:

SELECT MonthName(Month([TransEntryDate])) AS MonthListed, Sum(Transactions.ListingPrice) AS SumOfListingPrice FROM Transactions GROUP BY MonthName(Month([TransEntryDate]));

I get the following error:

Undefined function 'MonthName' in expression.

This used to work with classic ASP, but I'm not sure how to form this to work with ASP.NET. I am using this because my results are showing months numerically and I'd like them to be returned with the actual month name (january, february, etc.)...

Any help would be greatly appreciated!MonthName is not a SQL Server keyword, its only support in Analysis Services. Is you .net project pointing to the same database as your classic asp?|||Yes pointing to same db... I'm not sure how to get the results I'm looking for though...|||Yes...pointing to same db, but not sure how to make this work for the results I need...|||I think that what I'm looking for is an example of how I could use the CASE Expression within my SELECT statement to return the month name (not the numeric value)... PLEASE HELP!!!!|||ah now why didn't you say so ;)

SELECT DATENAME(month, getdate()) AS 'Month Name'

|||I hate to be a pain...I'm new to this... Could you show me how to use this with my SELECT statement in my first post? Thank you!!!|||

SELECT DateName(month,[TransEntryDate]) AS MonthListed, Sum(Transactions.ListingPrice) AS SumOfListingPrice FROM Transactions GROUP BY [TransEntryDate]

NB Simply using "Group By" doesn't guarantee a sorted order.

Problem with SQL generated from MS Access

SELECT DISTINCT product.product_id AS Expr1
FROM [SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like "*coaster*") AND
((product.display)="y"))]. AS [Alias]

This is the error I get:

The identifier that starts with 'SELECT product.product_id...' is too
long. Maximum lenght is 128

Incorrect syntax near the keyword 'AS'

Any ideas?

ThanksReplace [...]. with (...)
Replace " with '

SELECT DISTINCT product.product_id AS Expr1
FROM (SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like '*coaster*') AND
((product.display)='y'))) AS [Alias]

--
David Portas
SQL Server MVP
--|||That worked great...

Thanks for the help

Problem with SQL from Access

SELECT DISTINCT product.product_id AS Expr1
FROM [SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like "*coaster*") AND
((product.display)="y"))]. AS [Alias]
This is the error I get:
The identifier that starts with 'SELECT product.product_id...' is too
long. Maximum lenght is 128
Incorrect syntax near the keyword 'AS'
Any ideas?
Thanks
FROM [SELECT product.product...
...
((product.display)="y"))]. AS ...
Should be:
FROM (SELECT product.product...
...
((product.display)="y"))). AS ...
Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
for how you enclose a derived table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Allan" <proflicker@.hotmail.com> wrote in message
news:7b5b0602.0409271247.5a340b82@.posting.google.c om...
> SELECT DISTINCT product.product_id AS Expr1
> FROM [SELECT product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
> ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
> coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
> dbo_ssc_product.product_id = product.product_id
> GROUP BY product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> HAVING (((product.item_name) Like "*coaster*") AND
> ((product.display)="y"))]. AS [Alias]
> This is the error I get:
> The identifier that starts with 'SELECT product.product_id...' is too
> long. Maximum lenght is 128
> Incorrect syntax near the keyword 'AS'
>
> Any ideas?
> Thanks
|||Hi Tibor,
thanks for your help, you're right about the parenthesis and I also
found that you need to also remove the dot.
the code below ended up working:
FROM (SELECT product.product...
...
((product.display)="y"))) AS ...
without the dot.
Thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<OCMZv4SpEHA.536@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> FROM [SELECT product.product...
> ...
> ((product.display)="y"))]. AS ...
> Should be:
> FROM (SELECT product.product...
> ...
> ((product.display)="y"))). AS ...
> Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
> for how you enclose a derived table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Allan" <proflicker@.hotmail.com> wrote in message
> news:7b5b0602.0409271247.5a340b82@.posting.google.c om...
sql

Problem with SQL from Access

SELECT DISTINCT product.product_id AS Expr1
FROM [SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like "*coaster*") AND
((product.display)="y"))]. AS [Alias]
This is the error I get:
The identifier that starts with 'SELECT product.product_id...' is too
long. Maximum lenght is 128
Incorrect syntax near the keyword 'AS'
Any ideas?
ThanksFROM [SELECT product.product...
...
((product.display)="y"))]. AS ...
Should be:
FROM (SELECT product.product...
...
((product.display)="y"))). AS ...
Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
for how you enclose a derived table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Allan" <proflicker@.hotmail.com> wrote in message
news:7b5b0602.0409271247.5a340b82@.posting.google.com...
> SELECT DISTINCT product.product_id AS Expr1
> FROM [SELECT product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
> ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
> coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
> dbo_ssc_product.product_id = product.product_id
> GROUP BY product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> HAVING (((product.item_name) Like "*coaster*") AND
> ((product.display)="y"))]. AS [Alias]
> This is the error I get:
> The identifier that starts with 'SELECT product.product_id...' is too
> long. Maximum lenght is 128
> Incorrect syntax near the keyword 'AS'
>
> Any ideas?
> Thanks|||Hi Tibor,
thanks for your help, you're right about the parenthesis and I also
found that you need to also remove the dot.
the code below ended up working:
FROM (SELECT product.product...
...
((product.display)="y"))) AS ...
without the dot.
Thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<OCMZv4SpEHA.536@.TK2MSFTNGP11.phx.gbl>...
> FROM [SELECT product.product...
> ...
> ((product.display)="y"))]. AS ...
> Should be:
> FROM (SELECT product.product...
> ...
> ((product.display)="y"))). AS ...
> Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
> for how you enclose a derived table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Allan" <proflicker@.hotmail.com> wrote in message
> news:7b5b0602.0409271247.5a340b82@.posting.google.com...
> > SELECT DISTINCT product.product_id AS Expr1
> > FROM [SELECT product.product_id, product.item_name, product.display,
> > product.selling_price, product.smallpicture, product.main_size,
> > product.main_dinnerware, dbo_coupon_special.coupon_id,
> > dbo_coupon_special.special_price, coupon.effective_date,
> > coupon.expiration_date, dbo_ssc_product.mc_id
> > FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
> > ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
> > coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
> > dbo_ssc_product.product_id = product.product_id
> > GROUP BY product.product_id, product.item_name, product.display,
> > product.selling_price, product.smallpicture, product.main_size,
> > product.main_dinnerware, dbo_coupon_special.coupon_id,
> > dbo_coupon_special.special_price, coupon.effective_date,
> > coupon.expiration_date, dbo_ssc_product.mc_id
> > HAVING (((product.item_name) Like "*coaster*") AND
> > ((product.display)="y"))]. AS [Alias]
> >
> > This is the error I get:
> >
> > The identifier that starts with 'SELECT product.product_id...' is too
> > long. Maximum lenght is 128
> >
> > Incorrect syntax near the keyword 'AS'
> >
> >
> >
> > Any ideas?
> >
> > Thanks

problem with split function to select multiple values

When running the split function from my data pane within ssrs, or when
running it from sql server directly everything works fine.
But..when running the report in preview I receive dbo.split too many
arguments specified. I assume reporting services in some way passes it
like a string value once running the report. Otherwise it's also
noticeable the join function works fine within
code i'm using is like this within my dataset
Select myfield
from table
where field in
(
select item
from dbo.split(@.par,',')
)
The parameter passed to the split function has an 'integer value'
specified for the 'parameter value' . The split value is the well
known generic split function.If @.Par is a multivalued parameter, then try joining the items together in a
string:
Select myfield
from table
where field in
(
select item
from dbo.split(join(@.par,','),',')
)
"koopmans.johan@.hccnet.nl" wrote:
> When running the split function from my data pane within ssrs, or when
> running it from sql server directly everything works fine.
> But..when running the report in preview I receive dbo.split too many
> arguments specified. I assume reporting services in some way passes it
> like a string value once running the report. Otherwise it's also
> noticeable the join function works fine within
> code i'm using is like this within my dataset
> Select myfield
> from table
> where field in
> (
> select item
> from dbo.split(@.par,',')
> )
> The parameter passed to the split function has an 'integer value'
> specified for the 'parameter value' . The split value is the well
> known generic split function.
>|||Update to my previous post, the parameter to the split function must be a
varchar parameter as this will be sent through as a single comma separated
string list to the function.
"koopmans.johan@.hccnet.nl" wrote:
> When running the split function from my data pane within ssrs, or when
> running it from sql server directly everything works fine.
> But..when running the report in preview I receive dbo.split too many
> arguments specified. I assume reporting services in some way passes it
> like a string value once running the report. Otherwise it's also
> noticeable the join function works fine within
> code i'm using is like this within my dataset
> Select myfield
> from table
> where field in
> (
> select item
> from dbo.split(@.par,',')
> )
> The parameter passed to the split function has an 'integer value'
> specified for the 'parameter value' . The split value is the well
> known generic split function.
>|||Branden, sorry for taking your time...I've been using a direct query
within SSRS so there was no need for the split (this explains the
error). I tend to use SP's or cubes regularly and confused the way
they handle parameters with a direct query i'm using now. So this is
the simple solution
Select myfield
from table
where field in
(
@.par
)

Problem with sp_executesql

I try to write query that use sp_executesql to query data by Like operation with 1 parameter like below:
execute sp_executesql N'SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE @.au_lname
',
N'@.au_lname nVarChar',
@.au_lname = N'%Cas%'

but It return all rows regardless of changing condition to any value.

But if i don't use sp_executesql like below:

SELECT DISTINCT au_id,
au_lname,au_fname
FROM authors
WHERE au_lname LIKE N'%Cas%'

It's correct!

Can anyone tell me why?

ThanksChange your code as follows:

N'@.au_lname nVarChar', -->>> N'@.au_lname nVarChar(5)',|||Thank you very much for feedback!

Problem with SP

Hello All!
CREATE PROCEDURE dbo.PlbetII (@.UserID NvarChar(50),
@.Wager Nvarchar(50))
AS
IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@.UserID)
begin
Update BankRoll
Set Wager = Wager + CAST(@.Wager as Nvarchar)
where userID = @.userID
return -1
end
ELSE
begin
Insert Bankroll (Wager) Values (@.Wager)
RETURN @.@.IDENTITY
end
GO
Having a little problem with the SP above. I'm trying to take a value from
the a "Wage" textbox, and update the value in the table. The userID number
is a value in the userID texbox, and already created in the SQL table. I
need to make sure the the wage value will go in the right row, where the use
r
ID matches. I get an error in SQL, "Server: Msg 515, Level 16, State 2,
Procedure PlbetII, Line 13
Cannot insert the value NULL into column 'UserID', table
'FootBet.dbo.Bankroll'; column does not allow nulls. INSERT fails.
The statement has been terminated.
The 'PlbetII' procedure attempted to return a status of NULL, which is not
allowed. A status of 0 will be returned instead."
I'm not sure why it's inserting the user ID. I see at "Set Wager = Wager +
CAST(@.Wager as Nvarchar)
where userID = @.userID" might be my problem, but I thought it's just
returning the value for user id and selecting it. I'm so .
Any help would be great!!!
TIA!!
RudyThe identity value (or autoincrement) for the column userId is not set,
hence the impossibility of inserting a new record at line 13 without
specifying the value for UserId. When creating a new record, you have two
choice: specify the value for UserId or ask SQL-Server to automatically
create a new value each time by setting the identity property.
From you piece of code, it's impossible to tell which one of these two
methods you should use.
Also, I'm not sure if the line Set Wager = Wager + CAST(@.Wager as
Nvarchar) will do what you are expecting it to do.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Rudy" <Rudy@.discussions.microsoft.com> wrote in message
news:B0D0D405-F131-4106-8602-E5F77D684B4D@.microsoft.com...
> Hello All!
> CREATE PROCEDURE dbo.PlbetII (@.UserID NvarChar(50),
> @.Wager Nvarchar(50))
> AS
> IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@.UserID)
> begin
> Update BankRoll
> Set Wager = Wager + CAST(@.Wager as Nvarchar)
> where userID = @.userID
> return -1
> end
> ELSE
> begin
> Insert Bankroll (Wager) Values (@.Wager)
> RETURN @.@.IDENTITY
> end
> GO
> Having a little problem with the SP above. I'm trying to take a value
> from
> the a "Wage" textbox, and update the value in the table. The userID
> number
> is a value in the userID texbox, and already created in the SQL table. I
> need to make sure the the wage value will go in the right row, where the
> user
> ID matches. I get an error in SQL, "Server: Msg 515, Level 16, State 2,
> Procedure PlbetII, Line 13
> Cannot insert the value NULL into column 'UserID', table
> 'FootBet.dbo.Bankroll'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> The 'PlbetII' procedure attempted to return a status of NULL, which is not
> allowed. A status of 0 will be returned instead."
> I'm not sure why it's inserting the user ID. I see at "Set Wager = Wager
> +
> CAST(@.Wager as Nvarchar)
> where userID = @.userID" might be my problem, but I thought it's just
> returning the value for user id and selecting it. I'm so .
> Any help would be great!!!
> TIA!!
> Rudy
>
>|||Rudy (Rudy@.discussions.microsoft.com) writes:
> CREATE PROCEDURE dbo.PlbetII (@.UserID NvarChar(50),
> @.Wager Nvarchar(50))
> AS
> IF EXISTS (SELECT 1 FROM Bankroll WHERE UserID=@.UserID)
> begin
> Update BankRoll
> Set Wager = Wager + CAST(@.Wager as Nvarchar)
> where userID = @.userID
> return -1
> end
> ELSE
> begin
> Insert Bankroll (Wager) Values (@.Wager)
> RETURN @.@.IDENTITY
> end
> GO
> Having a little problem with the SP above. I'm trying to take a value
> from the a "Wage" textbox, and update the value in the table. The
> userID number is a value in the userID texbox, and already created in
> the SQL table. I need to make sure the the wage value will go in the
> right row, where the user ID matches. I get an error in SQL,
> "Server: Msg 515, Level 16, State 2, Procedure PlbetII, Line 13
> Cannot insert the value NULL into column 'UserID', table
> 'FootBet.dbo.Bankroll'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> The 'PlbetII' procedure attempted to return a status of NULL, which is not
> allowed. A status of 0 will be returned instead."
> I'm not sure why it's inserting the user ID.
Obviously the table does not have the IDENTITY property. If I am to
believe your procedure, the UserID column is nvarchar(50), why IDENTITY
is completely off-track.
Just say:
Insert Bankroll (UserID, Wager) Values (@.UserID, @.Wager)
By the way, the RETURN statement is mainly used to indicate
success/failure, with success being 0 and anything else means an
error. The regular way to return a value is to use an OUTPUT
parameter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you Erland and Sylvain!
I did change things up a little bit per you sugestions. Still having
problems, but I suspect something with the table itself. I deleted the table
,
and built it again, and now it works fine.
Thanks again!
Rudy
"Erland Sommarskog" wrote:

> Rudy (Rudy@.discussions.microsoft.com) writes:
> Obviously the table does not have the IDENTITY property. If I am to
> believe your procedure, the UserID column is nvarchar(50), why IDENTITY
> is completely off-track.
> Just say:
> Insert Bankroll (UserID, Wager) Values (@.UserID, @.Wager)
> By the way, the RETURN statement is mainly used to indicate
> success/failure, with success being 0 and anything else means an
> error. The regular way to return a value is to use an OUTPUT
> parameter.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Can run the following query and post the output.
select COLUMNPROPERTY( object_id('Bankroll') , 'pub_id' , 'AllowsNull' ) as
nullable,
COLUMNPROPERTY( object_id('Bankroll') , 'userID' , 'IsIdentity' ) as
[autogenID]
And a few observations:
Why is UserID nvarchar?
and why do you do this step?
Wager + CAST(@.Wager as Nvarchar)
when @.wager is already nvarchar.. and are you trying to append the string
wager or add the wager to the existing value? Is Wager a numeric value or ha
s
characters like the dollar and pounds.
It will be better to answer your question if you give table definition and
test data
so that we can reproduce the error here.

Problem with SELECT...FOR XML in SQL 2000

Hi,
I have a problem with a SELECT..FOR XML Statement. I would like to do
something like:
DECLARE @.DeletedXml VARCHAR(8000)
SET @.DeletedXml = (SELECT * from deleted AS Employee where Employee.Id = @.Id
FOR XML AUTO, ELEMENTS)
But I get this error when I try to run: Incorrect syntax near 'XML. I have
then consulted the manual, and I can see to my big surprise that this is not
supported. Is there any way I can solve this? I want to save the @.DeletedXml
variable in a table.
Thanks
HenrikYou'll be able to do that in SQL Server 2005, with FOR XML TYPE. In SQL
Server 2000, you'll have to save it as native SQL datatypes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:%23YLgXvz$FHA.228@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a problem with a SELECT..FOR XML Statement. I would like to do
> something like:
> DECLARE @.DeletedXml VARCHAR(8000)
> SET @.DeletedXml = (SELECT * from deleted AS Employee where Employee.Id =
> @.Id FOR XML AUTO, ELEMENTS)
> But I get this error when I try to run: Incorrect syntax near 'XML. I have
> then consulted the manual, and I can see to my big surprise that this is
> not supported. Is there any way I can solve this? I want to save the
> @.DeletedXml variable in a table.
> Thanks
> Henrik
>|||it's a bit messy, but you can
1. Put the FOR XML command as a stoerd proc
2. In .NET, execute the stored proc w/ the SqlXmlCommand object
3. Store the results of the stored proc in a stream -- Dim strm As
IO.Stream = xmlCommand.ExecuteStream()
4. Write the plain-text contents of the stream to the Database|||Hi Tom,
How can I save it as native SQL types?
Thanks Henrik
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uvXWp1z$FHA.4012@.TK2MSFTNGP10.phx.gbl...
> You'll be able to do that in SQL Server 2005, with FOR XML TYPE. In SQL
> Server 2000, you'll have to save it as native SQL datatypes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
> news:%23YLgXvz$FHA.228@.TK2MSFTNGP12.phx.gbl...
>|||Thank you very much for your reply. You are right this is a bit messy :-)
I unfortunately can't do it in a stored procedure because I am using the
deleted and inserted tables.
And I would like if possible to do it all in SQL.
<scottstein@.gmail.com> wrote in message
news:1134406828.872432.69030@.g14g2000cwa.googlegroups.com...
> it's a bit messy, but you can
> 1. Put the FOR XML command as a stoerd proc
> 2. In .NET, execute the stored proc w/ the SqlXmlCommand object
> 3. Store the results of the stored proc in a stream -- Dim strm As
> IO.Stream = xmlCommand.ExecuteStream()
> 4. Write the plain-text contents of the stream to the Database
>|||You'll have to save the columns of the inserted/deleted tables to the
columns of another table or you can concatenate the values into a string and
save that - as long as the total string length is <= 8000 bytes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:uLTNXc1$FHA.1028@.TK2MSFTNGP11.phx.gbl...
> Hi Tom,
> How can I save it as native SQL types?
> Thanks Henrik
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uvXWp1z$FHA.4012@.TK2MSFTNGP10.phx.gbl...
>|||Hi Tom,
Again thanks.
Ok, that is of course an idea, to skip the FOR XML attribute and generate
the XML myself. I guess that is what you mean? The 8k barrier is no problem.
Henrik
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OZiO421$FHA.516@.TK2MSFTNGP15.phx.gbl...
> You'll have to save the columns of the inserted/deleted tables to the
> columns of another table or you can concatenate the values into a string
> and save that - as long as the total string length is <= 8000 bytes.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
> news:uLTNXc1$FHA.1028@.TK2MSFTNGP11.phx.gbl...
>|||Yeah. basically, do an INSERT SELECT * FROM deleted. When you go to SQL
2005, you can then store it as XML.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Henrik Skak Pedersen" <skak@.community.nospam> wrote in message
news:%236J6x$1$FHA.264@.tk2msftngp13.phx.gbl...
> Hi Tom,
> Again thanks.
> Ok, that is of course an idea, to skip the FOR XML attribute and generate
> the XML myself. I guess that is what you mean? The 8k barrier is no
> problem.
> Henrik
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OZiO421$FHA.516@.TK2MSFTNGP15.phx.gbl...
>

Problem with SELECT, GROUP BY and aggregate function

Hi all,

I have a problem with an SQL-query and I don't know what the best solution would be to solve the problem.

/*INSERT INTO WERKS (
WERKS.Z8601,
WERKS.Z8602,
WERKS.Z8603,
WERKS.Z8604,
WERKS.Z8605,
WERKS.Z8606,
WERKS.Z8607,
WERKS.Z8608,
WERKS.Z8609,
WERKS.Z8610,
WERKS.Z8611,
WERKS.Z8621,
WERKS.Z8622,
WERKS.Z8623,
WERKS.Z8624,
WERKS.Z8625,
WERKS.Z8626,
WERKS.Z8627,
WERKS.Z8628,
WERKS.Z8629,
WERKS.Z8630,
WERKS.Z8631,
WERKS.Z8632) */
SELECT
0,
Stati.z4414,
Stati.z4402,
'',
'',
'',
Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), ''),
Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), ''),
'',
'',
'',
sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),
sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)
FROM STATI
WHERE
(Stati.z4402 Between '40000' AND 'ZONE6') AND
(Stati.z4414 Between '2005028' AND '2005028') AND
(Stati.z4417 = 'A')
GROUP BY Stati.z4414, Stati.z4402

I get the following error:

Msg 8120, Level 16, State 1, Line 25
Column 'STATI.Z4400' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know it has something todo with the select on the table SRTAS, but what's the best way to solve this problem without the chance of getting a wrong result.

The SELECT on SRTAS is to get the "description" of STATI.Z4414 who's stored in the table SRTAS.
I only want to group on the fields that will be inserted in WERKS.Z8602, WERKS.Z8603, WERKS.Z8604, WERKS.Z8605, WERKS.Z8606. So adding STATI.Z4400 to the GROUP BY would give me wrong results?

This query is dynamicly generated from within my program from what the user selected.

Also, if there are better ways to write the query, I would be happy to get some hints and tips, but if possible without stored procedures.

Thanks in advance!

If you really want to use that dynamic query, I would use a Subquery to use the column names to group, otherwise it often the case that you use the overview over the statement.

SELECT col1,col2
FROM
(
Your dynamic query here
) Subquery
Group by col1, col2

HTH, Jens K. Suessmeyer.

http://www.sqlserver205.de|||Assuming the value of STAI.Z4400 is always the same for the group do this:
.
.
.
MAX(Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), '')),
MAX(Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), '')),
.
.
.

That should fix it.

In this case, it would be easier to JOIN the STAI file intead of doing the subquery. Then just do

CASE WHEN Srtas.z02 IS NULL THEN '' ELSE Srtas.z02 END,
CASE WHEN Klant.z0102 IS NULL THEN '' ELSE Klant.z0102 END,

|||Thank you both for your opinion and help on this problem.

Reading your posts gave me some new ideas so I started changing the query like this:

SELECT

0,

Stati.z4414,

Stati.z4402,

'',

'',

'',

Max(Srtas.z02),

Max(Klant.z0102),

'',

'',

'',

sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End),

sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End),

sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End)

FROM STATI

LEFT JOIN KLANT ON STATI.Z4400 = KLANT.Z0100 AND STATI.Z4402 = KLANT.Z0101

LEFT JOIN SRTAS ON STATI.Z4400 = SRTAS.Z00 AND STATI.Z4414 = SRTAS.Z01

WHERE

(Stati.z4402 Between '40000' AND 'ZONE6') AND

(Stati.z4414 Between '2005028' AND '2005028') AND

(Stati.z4417 = 'A')

GROUP BY Stati.z4414, Stati.z4402

This query seems to work.

I have to admit that there's a huge pile of dust ontop of my MSSQL-knowledge. Been a while since I last used it.

So if any of you have any comments on me doing something wrong, I would be pleased to hear it :-)

Problem with Select Top ....(incorrect syntax near '@p')

hello,
i want to return a nr of records depending on the parameter @.nrRecords
I tryed to execute this in the queryanalyser - but i get a error, what is
wrong?
Declare @.nrRecords int
SET @.nrRecords=10
SELECT TOP @.p companyName
FROM myTable
the error is
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '@.nrRecords'.
thanksexamnotes (td1369@.discussions.microsoft.com) writes:
> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
> the error is
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '@.nrRecords'.
In SQL 2000 you must specify a constant with TOP. In SQL 2005 you can
use an expression, but it must be in parens:
SELECT TOP(@.nrRecords)
The best alternative in SQL 2000, is to use SET ROWCOUNT instead:
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
SET ROWCOUNT 0
By the way, TOP or SET ROWCOUNT without an ORDER BY is not really
meaningful, is it is not defined which rows you get.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Da ich sch=E4tze, da=DF Du mit einem SQL Server 2k unterwegs bist, mu=DF
ich Dir leider sagen, da=DF dies die variable TOP Deklaration erst ab
SQL2k5 funtkioniert, anonsten bleibt Dir nur noch Dynamic SQL =FCbrig.
http://www.sommarskog.se/dynamic_sql.html
HTH, jens Suessmeyer.|||Sorry posted in the wrong language:
I assume that your base is a SQL 2k, unfortunately this doesn=B4t work
with this, this declaration only works with SQL2k, therefore onyl
dynamic SQL remains as a solution for you.
http://www.sommarskog.se/dynamic_sql.html=20
HTH, jens Suessmeyer.|||> i want to return a nr of records depending on the parameter @.nrRecords
> I tryed to execute this in the queryanalyser - but i get a error, what is
> wrong?
> Declare @.nrRecords int
> SET @.nrRecords=10
> SELECT TOP @.p companyName
> FROM myTable
Two problems.
(1) in SQL Server 2000, TOP does not take a parameter.
(2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
meaningless without ORDER BY. SQL Server is free to return the rows in any
order it deems fit, which means your result can change completely from one
execution to the next.
I'll assume you meant to put an ORDER BY clause, and your solution might
look like this:
DECLARE @.nrRecords INT
SET @.nrRecords = 10
SET ROWCOUNT @.nrRecords
SELECT companyName
FROM myTable
ORDER BY companyName
SET ROWCOUNT 0|||good idea with the ROWCOUNT ...,
thanks - it works
best resgards
"Aaron Bertrand [SQL Server MVP]" wrote:

> Two problems.
> (1) in SQL Server 2000, TOP does not take a parameter.
> (2) what on earth does your TOP mean? You don't have an ORDER BY! TOP is
> meaningless without ORDER BY. SQL Server is free to return the rows in an
y
> order it deems fit, which means your result can change completely from one
> execution to the next.
> I'll assume you meant to put an ORDER BY clause, and your solution might
> look like this:
> DECLARE @.nrRecords INT
> SET @.nrRecords = 10
> SET ROWCOUNT @.nrRecords
> SELECT companyName
> FROM myTable
> ORDER BY companyName
> SET ROWCOUNT 0
>
>

Problem with select to another server.

Hi Arnie!

Thanks for the info!! So I got that join to work, but now I need to take that data from "SAHTest" and update Pharm Local Test. I'm not sure what is the best and quickest way. Do I make a temp table, or can i grab it from "inserted"? Any way, here was my shot.

DECLARE @.Acct nvarchar(50)

SET @.Acct ='L1234'

BEGIN

SELECT x.account, x.Fname, x.lname, x.dob

FROM [MKE01-Demo-01].SAHPharm.dbo.testact AS x JOIN

[Pharm Test Local].dbo.Active_Orders AS t ON t.Account_Number = x.account

WHERE(x.account = @.Acct)

END

BEGIN

UPDATE [Pharm Test Local].dbo.Active_Orders AS t

SET Account = t.Account_Number, Fname = t.First_Name, LName = t.Last_Name,

dob = t.DOB

FROM [MKE01-Demo-01].SAHPharm.dbo.testact

WHERE [MKE01-Demo-01].SAHPharm.dbo.testact.account = t.Account_Number

End

Any ideas?

Thanks!

Rudy

You should be able to combine the two queries into one.

Code Snippet

UPDATE [Pharm Test Local].dbo.Active_Orders
SET
l.Account = t.Account_Number,
l.Fname = t.First_Name,
l.LName = t.Last_Name,
l.dob = t.DOB
FROM [Pharm Test Local].dbo.Active_Orders l
JOIN [MKE01-Demo-01].SAHPharm.dbo.testact t
ON l.Account_Number = t.Account
WHEREt.Account_Number = @.Acct

|||

Hi Arnie!

Thank you for the relpy. I tried your above solution, but it doesn't work. It seems I can't use JOIN on a SQL 2005 box. My Local server is SQL 2005, and SQL 2000 is on my target. So I'm trying to figure out the new OUTER APPLY that I discovered. I'm getting this error when I run the above statement in you post,

"

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "l.Account_Number" could not be bound."

I'll keep you updated in my findings, but if there is anybody who can enlighten me, I'm all ears!

Thanks!

Rudy

|||

It seems I can't use JOIN on a SQL 2005 box

Now that totally doesn't make sense. JOIN is so basic to SQL, it's like air, you can't exists without it. ALL versions of SQL Server support JOIN.

So I'm trying to figure out the new OUTER APPLY that I discovered

NOW that definitely will not work on a SQL 2000 server.

If you would carefully examine the statement I suggested, you would see that I made a transposition error in this line:

ON l.Account_Number = t.Account

I believe it should be: ON l.Account = t.Account_Number instead.

|||

Hi Arnie!

I did catch that. I have another guy here baffled as well. He told me that the JOIN will work, I'm just not sure why I'm getting that error. I'll keep plugging away.

Thanks

Rudy

|||

Hello Arnie!

After a little digging, I figured it out. Heres what I got!

Thanks again for all your help on this!

Rudy

UPDATE [Pharm Test Local].dbo.Active_Orders

SET

Account_Number = t.Account,

First_Name = t.Fname,

Last_Name = t.Lname,

dob = t.DOB

FROM [MKE01-DEMO-01].SAHPharm.dbo.testact t

WHERE Account_Number = t.Account