collation conflict

Hi, all
I am doing testing of my application, actualy one Store Procedure at the moment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in char columns.
When I change them to database default setting, the stored procedure works OK.
My question is:
how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happeni
ng.
I am very grateful for any helpful information.
TIA
Elizabeta
Elizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:

>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the moment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in char columns.
>When I change them to database default setting, the stored procedure works OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as objects, always the same problem. Is there any setting on SQL server to avoid this happen
ing.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>
|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:
[vbcol=seagreen]
> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "script
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
ening.
>

collation conflict

Hi, all
I am doing testing of my application, actualy one Store Procedure at the mo
ment.
I have development database.
I copy tables that I need for the testing.
The same queries that run on live database fail here wth the message
'cannot resolve collation conflict...'
I discovered that it is due to all copied tables have other collation in cha
r columns.
When I change them to database default setting, the stored procedure works O
K.
My question is:
how can I avoid to have collation in new copied tables, although the databas
es have it the same. I tried to copy using CREATE TABLE scripts or DTS servi
ces, copy as objects, always the same problem. Is there any setting on SQL s
erver to avoid this happeni
ng.
I am very grateful for any helpful information.
TIA
ElizabetaElizabeta,
If you script the CREATE TABLE scripts in Query Analyzer, you can go
to Tools|Options|[Script tab at far right] and check the box for "script
only 7.0 compatible features." If the collate clause is the only SQL
Server 2000 feature you are using, it might work for you. Otherwise,
assuming all the collations are the same, you can just do a search and
replace after scripting all the tables all at once from somewhere.
Steve Kass
Drew University
Elizabeta wrote:

>Hi, all
>I am doing testing of my application, actualy one Store Procedure at the m
oment.
>I have development database.
>I copy tables that I need for the testing.
>The same queries that run on live database fail here wth the message
>'cannot resolve collation conflict...'
>I discovered that it is due to all copied tables have other collation in ch
ar columns.
>When I change them to database default setting, the stored procedure works
OK.
>My question is:
>how can I avoid to have collation in new copied tables, although the databases have
it the same. I tried to copy using CREATE TABLE scripts or DTS services, copy as ob
jects, always the same problem. Is there any setting on SQL server to avoid this hap
pen
ing.
>I am very grateful for any helpful information.
>TIA
>Elizabeta
>|||Thanks, Steve,
it works from SQL query analyzer. I tick the option
'do not csript collation clause...'
Cheers
"Steve Kass" wrote:

> Elizabeta,
> If you script the CREATE TABLE scripts in Query Analyzer, you can go
> to Tools|Options|[Script tab at far right] and check the box for "scri
pt
> only 7.0 compatible features." If the collate clause is the only SQL
> Server 2000 feature you are using, it might work for you. Otherwise,
> assuming all the collations are the same, you can just do a search and
> replace after scripting all the tables all at once from somewhere.
> Steve Kass
> Drew University
> Elizabeta wrote:
>
ening.[vbcol=seagreen]
>sqlsql

Cognos to RS

Hi all!

I am taking a cognos report and created an identical one in RS. Can anyone tell me what this means and help figure out how to do it in RS?
cognos
TO_DATE(SUBSTR(DATE_TIME_STAMP,1,14),'YYYYMMDDHH24MISS')

RS (i am guessing)
CONVERT(SUBSTRING(DATE_TIME_STAMP,1,14),yyyyMMddhh?m?ss)

can anyone help?

Thanks in avance,
KerrieIs this a SQL function or a function in the report? If it is the latter, the function for SUBSTR is Mid() (http://whidbey.msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbalr/html/8b6caa3e-973a-4f18-b0e4-93956a9dee9b.asp). You probably want to use the Format() function (http://whidbey.msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbalr/html/c6f5bdda-a7c3-4f38-86cc-1cf47aa940b3.asp) in place of the TO_DATE.|||you could try :

http://www.sqlobserver.com

SQLobSERVER is a new independent forum specialising in BI/ETL including across the vendors (ie., Cognos)

Sorry about the blurb, but thought it might to be useful..

Cognos to RS

Hi all!

I am taking a cognos report and created an identical one in RS. Can anyone tell me what this means and help figure out how to do it in RS?
cognos
TO_DATE(SUBSTR(DATE_TIME_STAMP,1,14),'YYYYMMDDHH24MISS')

RS (i am guessing)
CONVERT(SUBSTRING(DATE_TIME_STAMP,1,14),yyyyMMddhh?m?ss)

can anyone help?

Thanks in avance,
KerrieIs this a SQL function or a function in the report? If it is the latter, the function for SUBSTR is Mid() (http://whidbey.msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbalr/html/8b6caa3e-973a-4f18-b0e4-93956a9dee9b.asp). You probably want to use the Format() function (http://whidbey.msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vbalr/html/c6f5bdda-a7c3-4f38-86cc-1cf47aa940b3.asp) in place of the TO_DATE.|||you could try :

http://www.sqlobserver.com

SQLobSERVER is a new independent forum specialising in BI/ETL including across the vendors (ie., Cognos)

Sorry about the blurb, but thought it might to be useful..

COALESCE help

I have a pulldown menu which has like 4 options

producta productb productc and all

I am trying to retrieve the maximum build number value for these products and display on the gridview as per some other conditions like user selected OS etc

Now clicking on All, I want to display the maximum build number values for productA,ProductB ,ProductC

and I am trying to use coalesce but unable to get my result.

I end up seeing only one value which is the maximum of everything.Instead I want the maximums of A B and C and display them concatenated with commas.

If I do the following with no max funciton, i see all the values but i just want max from each branch.

DECLARE @.buildListvarchar(100)

select @.buildlist=COALESCE(@.buildList+', ','')+convert(varchar(10),build)from results

where branchin('ProductA','Product B','ProductC')

select @.buildList

Please let me know how to do this.

Please post some sample data from the table and expected output..

|||

Package Branch maxBuildNumber

Package1 Product A 2001

Package1 Product B 3004

Package1 Product C 4003

I want it as

Package buildList

Package1 2001,3004,4003

or better yet

Package1 ProductA.2001,ProductB.3004,ProductC.4003

|||

Close...

Declare @.TTable (Packagevarchar(10), Branchvarchar(10), maxBuildNumberint)Insert into @.TSelect'Package1','ProductA', 2001unionallSelect'Package1','ProductB', 3004unionallSelect'Package1','ProductC', 4003DECLARE @.buildListvarchar(100)SELECT @.buildlist=COALESCE(@.buildList +', ','') +convert(varchar(10),branch) +'.' +convert(Varchar, T.maxBuildNumber )FROM @.T TWHERE T.Package ='Package1'Select @.buildList

|||

Hi ,

Thank you for the coalesce help.Now I have a small problem with in that.

I do not want the whole of the branch name to be displayed in my buildlist(name of my branches are pretty long ..so want to display a short name instead)

I have this coalesce in a scalar function where I am returning it as a varchar.

Now before returning it, is it possible to check this buildlist for a pattern and replace it ?

suppose it is being displayed as productA/xy/ABCD.301 ....i want to display it as ABCD.301.

The value coming for Branch from my results table is something like productA/xy/ABCD.

I tried using Contains and replace but do not seem to work on declared variables?

This is what is in my coalesce

SELECT @.buildlist=COALESCE(@.buildList+' ','')+convert(varchar(50),Branch)+'.'+convert(Varchar, v_allbranchinfo.MAXBuildNumber)

FROM v_allbranchinfoWHERE /*some where conditions*/

IF @.buildlistcontains(@.buildlist,"Orcas/pu/DDE")

replace(buildlist,"Orcas/pu/DDE","DDE")

Can you please with this?

|||

After getting the @.Buildlist you can do a replace..

IF CHARINDEX(@.buildlist,'Orcas/pu/DDE') > 0SET @.BuildList =REPLACE(@.buildlist,'Orcas/pu/DDE','DDE' )

|||

You are great!

Thank you very much!

|||

Another problem now with coalese, I am getting the achived result as having all the branch build numbers in one row but on my webpage when I am displaying these results, I use a hyper link which would show some addition info from the same results table such as runid,total etc corresponding to each build.Now when I have the buildlist, I am not sure on how to handle this .

My query now looks like this:

(SELECT T1.*, T1.BuildListAS dataFROM v_BuildListerAS T1INNERJOIN

(SELECT SKU, OS, OSLang, ProductLang, Branch,MAX(Build)AS MaxBuild

FROM dbo.ResultsGROUPBY SKU, OS, OSLang, ProductLang, Branch)AS T2ON T1.SKU= T2.SKUAND

T1.OS= T2.OSAND T1.OSLang= T2.OSLangAND T1.ProductLang= T2.ProductLang)AS T3ON

dbo.Results.ID= T3.IDWHERE(dbo.Results.OSArch='Intel')AND(dbo.Results.OSLang='English - United States')AND(dbo.Results.ProductLang='ENU'))

v_Buildlister is a view on results table and the view doesn't have this runid etc information.

Even if it does, since it is a buildlist and not single build...does not give me proper information.

previously my T1 was results table .

Any idea will be appreciated.

cmdsql - Create a text file without Informational Messages

Hello all

I have a sql file that I want execute by using the cmdsql command line. But when I create a text file I receive two Informational Messages:

1."Changed database context to 'DataBaseName'."

2.(2 row(s) affected)

How can I ignore these messages in my text file? there is a parametter or something elese to configure to avoir these Informational Messages?

Sorry is not cmdsql is Sqlcmd :)

Clustered Vrs Non Clustered Indexes

Dear All
I recently posted a message where I stated that a
clustered index is faster than a non clustered index, is
this correct or do I have my facts totally wrong ?
Thanks
Peter
Some definitions first:
An index doesn't have any speed to talk about; it isn't fast or slow. The optimizer generates
execution plans where those plans might use indexes in different ways. And when the execution engine
uses the plans, using an index or different index types influences performance.
A blanket statement like "clustered index are fasten than non-clustered indexes" is too much of a
generalization. There are cases where using a non-clustered index will result in fewer I/O
operations and shorter execution time compared to using a clustered index. One such example is where
a non-clustered index covers the query.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
> Dear All
> I recently posted a message where I stated that a
> clustered index is faster than a non clustered index, is
> this correct or do I have my facts totally wrong ?
> Thanks
> Peter
|||Thanks Tibor,
Ah well, wrong again ;)
Peter

>--Original Message--
>Some definitions first:
>An index doesn't have any speed to talk about; it isn't
fast or slow. The optimizer generates
>execution plans where those plans might use indexes in
different ways. And when the execution engine
>uses the plans, using an index or different index types
influences performance.
>
>A blanket statement like "clustered index are fasten than
non-clustered indexes" is too much of a
>generalization. There are cases where using a non-
clustered index will result in fewer I/O
>operations and shorter execution time compared to using a
clustered index. One such example is where
>a non-clustered index covers the query.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
>
>.
>
|||I wouldn't say "wrong", Pete. But perhaps an over-generalization... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks Tibor,
> Ah well, wrong again ;)
> Peter
>
> fast or slow. The optimizer generates
> different ways. And when the execution engine
> influences performance.
> non-clustered indexes" is too much of a
> clustered index will result in fewer I/O
> clustered index. One such example is where
> wrote in message
|||So far over its caused a capsize :-D

>--Original Message--
>I wouldn't say "wrong", Pete. But perhaps an over-
generalization... :-)
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...
than[vbcol=seagreen]
a[vbcol=seagreen]
is
>
>.
>
|||Peter, Peter, Peter...
Don't be so hard on yourself.
Don't you remember what Einstein said?
He would rather have a watch that had stopped completely, than one that lost
a second a day.
A watch that had stopped would still show the correct time twice every day.
If you just keep saying that clustered indexes are faster than nonclustered,
you will occasionally be right.
:-)
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0efe01c51509$798a6890$a401280a@.phx.gbl...[vbcol=seagreen]
> So far over its caused a capsize :-D
>
> generalization... :-)
> wrote in message
> than
> a
> is
|||Mark Wilden wrote:
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23r$pIsQFFHA.2600@.TK2MSFTNGP09.phx.gbl...
> I hope this is apocryphal or metaphorical or just a joke.
> Personally, I'd rather have a watch that lost a second a day.
Well, Einstein rode his bike to work every day because he was concerned
about all the moving parts in a car. He also had many copies of the same
suit to avoid having to make a decision about what suit to wear. He led
a simple life outside and a rather complex one inside. Most of us fall
somewhere in the middle. I'm with you on the watch.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oc5GKHVFFHA.228@.TK2MSFTNGP15.phx.gbl...
> Well, Einstein rode his bike to work every day because he was concerned
> about all the moving parts in a car. He also had many copies of the same
> suit to avoid having to make a decision about what suit to wear. He led
> a simple life outside and a rather complex one inside.
My favorite worst Einstein quote is the one about making things as simple as
possible, but no simpler. It's not really possible to make things simpler
than possible. You can replace "simple" in that quote with any "good"
quality and have an equally valid (and vacuous) statement.

Clustered Vrs Non Clustered Indexes

Dear All
I recently posted a message where I stated that a
clustered index is faster than a non clustered index, is
this correct or do I have my facts totally wrong ?
Thanks
PeterSome definitions first:
An index doesn't have any speed to talk about; it isn't fast or slow. The op
timizer generates
execution plans where those plans might use indexes in different ways. And w
hen the execution engine
uses the plans, using an index or different index types influences performan
ce.
A blanket statement like "clustered index are fasten than non-clustered inde
xes" is too much of a
generalization. There are cases where using a non-clustered index will resul
t in fewer I/O
operations and shorter execution time compared to using a clustered index. O
ne such example is where
a non-clustered index covers the query.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
> Dear All
> I recently posted a message where I stated that a
> clustered index is faster than a non clustered index, is
> this correct or do I have my facts totally wrong ?
> Thanks
> Peter|||Thanks Tibor,
Ah well, wrong again ;)
Peter

>--Original Message--
>Some definitions first:
>An index doesn't have any speed to talk about; it isn't
fast or slow. The optimizer generates
>execution plans where those plans might use indexes in
different ways. And when the execution engine
>uses the plans, using an index or different index types
influences performance.
>
>A blanket statement like "clustered index are fasten than
non-clustered indexes" is too much of a
>generalization. There are cases where using a non-
clustered index will result in fewer I/O
>operations and shorter execution time compared to using a
clustered index. One such example is where
>a non-clustered index covers the query.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
>
>.
>|||I wouldn't say "wrong", Pete. But perhaps an over-generalization... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks Tibor,
> Ah well, wrong again ;)
> Peter
>
> fast or slow. The optimizer generates
> different ways. And when the execution engine
> influences performance.
> non-clustered indexes" is too much of a
> clustered index will result in fewer I/O
> clustered index. One such example is where
> wrote in message|||So far over its caused a capsize :-D

>--Original Message--
>I wouldn't say "wrong", Pete. But perhaps an over-
generalization... :-)
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...
than[vbcol=seagreen]
a[vbcol=seagreen]
is[vbcol=seagreen]
>
>.
>|||Peter, Peter, Peter...
Don't be so hard on yourself.
Don't you remember what Einstein said?
He would rather have a watch that had stopped completely, than one that lost
a second a day.
A watch that had stopped would still show the correct time twice every day.
If you just keep saying that clustered indexes are faster than nonclustered,
you will occasionally be right.
:-)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0efe01c51509$798a6890$a401280a@.phx.gbl...[vbcol=seagreen]
> So far over its caused a capsize :-D
>
> generalization... :-)
> wrote in message
> than
> a
> is|||Mark Wilden wrote:
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23r$pIsQFFHA.2600@.TK2MSFTNGP09.phx.gbl...
> I hope this is apocryphal or metaphorical or just a joke.
> Personally, I'd rather have a watch that lost a second a day.
Well, Einstein rode his bike to work every day because he was concerned
about all the moving parts in a car. He also had many copies of the same
suit to avoid having to make a decision about what suit to wear. He led
a simple life outside and a rather complex one inside. Most of us fall
somewhere in the middle. I'm with you on the watch.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oc5GKHVFFHA.228@.TK2MSFTNGP15.phx.gbl...
> Well, Einstein rode his bike to work every day because he was concerned
> about all the moving parts in a car. He also had many copies of the same
> suit to avoid having to make a decision about what suit to wear. He led
> a simple life outside and a rather complex one inside.
My favorite worst Einstein quote is the one about making things as simple as
possible, but no simpler. It's not really possible to make things simpler
than possible. You can replace "simple" in that quote with any "good"
quality and have an equally valid (and vacuous) statement.