Concat ?

Hi

I'm not sure about the right 'term' of this question. I'm using MS SQL server 2000 standard edition in a Windows 2003 Server Enterprise edition.

I have two tables. One of this has several columns (Year,Month,Day,User,Message). The other one, have just one column called Messages.

I count the today's messages in the first table using the following syntax

SELECT COUNT(*) FROM Tab1 WHERE YY = (datepart(year, getdate())) AND MM = (datepart(month, getdate())) AND DD = (datepart(day, getdate()))

Now, I have to update the column Messages in Table2, but I must to add a text before the count result. I.E.

'Messages today = 25'

Hoy may I add the text before the SELECT COUNT query result ?? Is it possible ??

Thank you.Try this syntax

select emp_id, 'Messages today = ' + convert(varchar(10),count(1)) from table group by empid

Prasad

Composite Index and Query Optimizer

Hi
I have a question. If I have a composite primary key on table (in essence I
will have a composite clustered index as PK by default creates a clustered
index) when would the Query optimzer would not use the index on it?. Is it
like if the Query optimzer thinks that a table scan would be much faster tha
n
using a index, it would not use the index?.
MVPs, please enlighten me on this scenario.
Thanks
MIf you have a clustered index on a table, you wouldn't see a table scan, bu
t
a clustered index scan.
Whether the optimizer picks a scan of a s depends on a lot of things,
like what columns the select statement retreives, the nature of the WHERE
condition (exact matches are more likely to result in a s; operators like
LIKE or BETWEEN a scan), and the number of rows that statistics on the table
indicate would be returned by the SELECT. (For a query that statistics
indicate would return a large number of rows, the optimizer may choose an
index scan, even when you would think a s would be better).
If you also have nonclustered indexes on the table, you could also see a
s on the nonclustered index with a bookmark lookup to the clustered index
.
Again, where the optimizer chooses that path depends on the same things
mentioned above.
"Meher" wrote:

> Hi
> I have a question. If I have a composite primary key on table (in essence
I
> will have a composite clustered index as PK by default creates a clustered
> index) when would the Query optimzer would not use the index on it?. Is it
> like if the Query optimzer thinks that a table scan would be much faster t
han
> using a index, it would not use the index?.
> MVPs, please enlighten me on this scenario.
> Thanks
> M|||On Thu, 2 Feb 2006 12:20:28 -0800, Meher wrote:

>Hi
>I have a question. If I have a composite primary key on table (in essence I
>will have a composite clustered index as PK by default creates a clustered
>index) when would the Query optimzer would not use the index on it?. Is it
>like if the Query optimzer thinks that a table scan would be much faster th
an
>using a index, it would not use the index?.
Hi Meher,
Mark already gave some answers, but he was mistaken on one point and he
forgot one point.
Here's all you ever wanted to know but were afraid to ask:
- Search for equality (Col01 = 'Abdef'), and you get an index s for
an exact match. For a composite index, this can still result in more
than one row, so it's technically still an index s for a range of
rows.
- Search for a range, for instance with BETWEEN, with < and/or >
comparisons operator, with LIKE (but only if the first character is not
a wildcard - LIKE 'abc%' is good; LIKE '%abc' isn't), and you get an
index s for a range of matching values.
- An index scan will be used if the indexed value is embedded in an
expression (WHERE Col02 + 17 = 34), for a LIKE that starts with a
wildcard (LIKE '%abc' or LIKE '_abc'), and you get a clustered index
scan (which is basically the same as a table scan).
- Also, if you search for a value in the second column of a composite
index, a table scna has to be used. Compare this to finding everyone
living on Elm Street in a phone book that is sorted by last name, with
street as secondary sort order for people with equal last name.
Hugo Kornelis, SQL Server MVP

composite index

Hi

I have a table with a 3 column composite cluster index in sql 2005

table1 ( a uniqueidentifier, b uniqueidentifier, c int, d varchar(1000))

the composite index is on a,b,c

when I write a query

select * from table1 where a = 'asd' and b = 'afasddf'

the query analyzer uses cluster index seek

but if I use the table in a join

select * from table1 inner join table2 on table1.a = table2.a

and table1.b = 'sdsfds'

the query optimizer is using a cluster index scan on b, I can't limit the query on table1.b in a join?

thanks

Pauli

create an index on t1.b should do it.

e.g.

create table #t1(i uniqueidentifier, j uniqueidentifier, k int, primary key(i,j,k))
create table #t2(i uniqueidentifier, j uniqueidentifier, k int, primary key(i))
create index ix_ on #t1(j)
go
set showplan_text on
go

declare @.j uniqueidentifier
set @.j=newid()

select * from #t1 t1 join #t2 t2 on t1.i=t2.i and t1.j = @.j
go
set showplan_text off
go
drop table #t2,#t1

|||

paulixml wrote:

the query optimizer is using a cluster index scan on b, I can't limit the query on table1.b in a join?

Apparently in your particular case the optimizer has concluded that doing the join and then filtering the output is more expensive than first performing a table scan and filtering the intermediate output and then doing a join on a much smaller (hopefully) set.

It all really depends the existing indexes in BOTH your tables table1 and table2 (and not just the availability of the index on [a,b,c] in table1), AND, of course, on the actual data - hence, on the statistics available to the optimizer. One could easily cook up various column value distributions for your existing tables/indexes where the very same query will produce very different execution plans.

complicated Join - duplicate row problem

Hi
I had a similar problem awhile back and it was solved here. Now it has
gotten more complex. I have removed uneeded stuff here to keep this
simple.:
I am sure I know why I am getting the results I am, but don't know
how (or if) there is a way around it. If someone can help, I will be
mighty excited and impressed !
Here goes:
Given a table definition:
CREATE TABLE table1
(col1 varchar(20),
col2 varchar(20),
col3 integer,
CONSTRAINT PK_table1 PRIMARY KEY (col1,col2))
and then (assuming table2 exists)
ALTER TABLE table1
ADD CONSTRAINT FK_table1_table2
FOREIGN KEY (col1)
REFERENCES table2 (col1)
REQUIREMENT:
I would like to select some schema information about this table. Among
other things, I want the column name, data type, and Primary Key and
Foreign Key information. For the Primary Key and Foreign Key, all I
need to know is if one or both of these attributes applies to a
column.
Therefore, I would like my result records to look like this:
name | type | PK_col | FK_col
--+--+--+--
col1 | varchar | PK | FK
col2 | varchar | PK |
col3 | integer | |
My problem is getting the PK and FK on the same row. My results are
currently like this:
name | type | PK_col | FK_col
--+--+--+--
col1 | varchar | PK |
col1 | varchar | | FK
col2 | varchar | PK |
col3 | integer | |
HERE IS THE QUERY:
SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
PK_COL = case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
else '' END,
FK_Col = case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
else '' END
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
on cols.table_name = K.TABLE_NAME
and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
on k.table_name = t.table_name
and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
So, Col1 is duplicated because there are two (2) entries (PK & FK) in
key_column_usage for that column.
Is there some way to combine these two result rows together taking the
PK info from 1 and the FK from the other?
Thanks
JeffHi, Jeff
Did you read my response to your previous post ?
http://groups.google.com/group/micr...br />
a2bf3ce9
It's exactly what you are requesting now...
Razvan|||Sorry, I had missed your earlier reply because it didn't show up in my
news reader under the question. I see it now. My mistake.
This looks good and makes sense.
Thank you very very much !!
Jeff
On 12 Jan 2006 23:28:37 -0800, "Razvan Socol" <rsocol@.gmail.com>
wrote:

>Hi, Jeff
>Did you read my response to your previous post ?
>http://groups.google.com/group/micr...r />
9a2bf3ce9
>It's exactly what you are requesting now...
>Razvan|||On Fri, 13 Jan 2006 06:38:33 GMT, Jeff User wrote:
(snip)
>Therefore, I would like my result records to look like this:
>name | type | PK_col | FK_col
>--+--+--+--
> col1 | varchar | PK | FK
> col2 | varchar | PK |
> col3 | integer | |
>My problem is getting the PK and FK on the same row. My results are
>currently like this:
>name | type | PK_col | FK_col
>--+--+--+--
> col1 | varchar | PK |
> col1 | varchar | | FK
> col2 | varchar | PK |
> col3 | integer | |
(snip)
Hi Jeff,
Just a few simple changes to the query should suffice:
SELECT cols.COLUMN_NAME as name,cols.DATA_TYPE as type,
PK_COL = MAX(case when T.CONSTRAINT_TYPE = 'PRIMARY KEY' then 'PK'
else '' END),
FK_Col = MAX(case when T.CONSTRAINT_TYPE = 'FOREIGN KEY' then 'FK'
else '' END)
FROM Test.INFORMATION_SCHEMA.COLUMNS cols
left JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
on cols.table_name = K.TABLE_NAME
and cols.column_name = K.column_name
left join INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
on k.table_name = t.table_name
and K.CONSTRAINT_NAME = T.CONSTRAINT_NAME
and (T.CONSTRAINT_TYPE = 'PRIMARY KEY'
or T.CONSTRAINT_TYPE = 'FOREIGN KEY' or T.CONSTRAINT_TYPE = 'UNIQUE')
WHERE cols.TABLE_NAME = 'table1'
ORDER BY cols.ORDINAL_POSITION
GROUP BY cols.COLUMN_NAME, cols.DATA_TYPE, cols.ORDINAL_POSITION
Hugo Kornelis, SQL Server MVP

Compilation

Hi
I want to know how to compiled a report in crystal report 8.5 or 9.0??
In the previous version of crystal Report (Version 8.0) I could do this task, but in the newest version I can find the option...
Please help meHi
This file contains an add-in that enables you to compile and distribute reports in the English version of Crystal Reports 8 and 8.5. This is the instalation File scr8_distr_expert.exe

Thanks

compensating delete on replication database!

Hi
I have Merge Publication with just one Subscriber running on SQL 2000
which has been Service packed fully.
Looking at the previous posts I did uncheck 'Enforce relationship for
replication' on the Table Relationships hoping that the deletion of
records will not happen.
But for some unknown reason the records in the FK relationship is
deleted, which is causing all sorts of problems. There are no Triggers
on the database, and I could not find a pattern for the deletion it
happens randomly.
E.G. We have a 'Contacts' and 'Addresses' Table, one contact can have
more than 1 address. But for some reason the addresses are being deleted
for the contact, i am not sure where it is happening whether in
Subscriber or Publisher, but we loose the record.
Does any one has a good solution for this?
Thanks in advance.
Regards
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Do you have any user triggers at all?
Perhaps you are experiencing the compensating commands situation :
http://support.microsoft.com/default...Product=sql2k?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)!
|||Thanks Paul for the link. But I do not have any user triggers at all?
Since the 'Enforce relationship for replication' is unchecked, I am a
bit puzzled about how this is happening.
Can I apply the hotfix (the link you have sent) to the production
server? Is it safe?
Thanks once again.
Regards
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Paul
The Hotfix is available only in SP4 Beta and not sure when it is going
to be released. In the meantime the database is loosing the records all
the way through. Can you suggest any intermediate solutions? somethings
I need to check etc..
Thanks
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||The two suggestions weren't related - user triggers was just one other
posibility.
If it is a compensation issue, you should be able to reproduce. An address
added successfully at one site will be deleted if it cannot be applied at
the other due to a PK/FK error, constraint error etc. So, you need to check
that the schemas are identical wrt these tables (SQLCompare?). If you can
find an address that repeatedly gets removed then tracking/removing the
constraint should be simple.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)!
|||Hi Paul
Thanks for the reply. There is not pattern these records are deleted and
I am not able to re-create it as well.
The Addresses Table has 2 FK relationship one to the AddressType Table
and one to the Contact Table were the records are all present. It is
only the Address records that gets deleted.
Any other areas to look into, please...
Thanks
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Paul
I am really sorry, there is a Trigger created by another developer on
the Addresses Table, which maintaines the Ref. Intergerity. Could this
be the cause for this 'compensating deletion', if so how to overcome
this, do we need to remove the triggers?
Regards
Laks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Compatibility Mode 70 databases in SP2

Hi
I would not say that this would be deemed a major issue as it is simple to
write your own maintenance procedures, and there are examples if you search
the web.
It is also recommended that compatibility mode is mainly designed as a
transient part of an upgrade and should not normally be something to be
relied upon long term, unless there is no way you can upgrade change the
database. If the latter is the case then your buisiness may be at risk if you
are relying on such software.
John
"Axel Bender" wrote:

> Hi,
> Does anybody know if the creation of maintenance plans for Compatibility
> Mode 70 databases will be possible with SQL Server 2005 SP2?
> According to what I've heard so far, there is no chance to do that in
> SP0/1 installations of SQL Server 2005 (CM 70 databases won't show up in
> the wizard). I consider this a severe shortcoming in the product, which
> should be tackled by MS.
> Any opinions/workarounds?
>
Hi Axel
I suggest that you ship some standard jobs and procedures that will do this
for them and avoid the maintenance plans.
I don't really have any idea how widespread this issue is how much it would
actually take to implement this additional feature, but I can only assume
that it is not as big an issue as you think or MS is not aware of the
magnitude. If you wish to formally request a response and make them aware of
this issue I would raise a support call.
John
"Axel Bender" wrote:

> Thanks for the answer, John.
> Although I generally agree with you in saying that a compatibility mode
> should not be used on a long-term basis, I have to make some additional
> points on that topic:
> a) We have deployed a lot of CM 70 databases; changing the compatibility
> mode for them would not break our application, but it would give the
> users very slow response times in parts of the app (this is due to the
> change MS made to the query optimizer when it comes to selecting
> indexes). We know we have to make changes to the app, but frankly, we
> simply cannot afford the time for this now.
> b) Most of our customers are not able to write maintenance procedures
> (nor are all of our supporters).
> c) There is no common scheme that our users follow when it comes to
> backing up and checking their databases.
> d) S2k5 supports maintenance plans for CM 80 databases, why not for CM
> 70 ones?
> I think, that supporting CM 70 databases would not be a big deal for MS;
> for us it would be.
> Kind regards,
> Axel
>

Compatibility issues between Vista/XP?

HI

I have just started using SQL Server Compact Edition. I have developed a test application just to see if it fits my needs but found something extremely weird.

It's so strange that I guess I'm doing something wrong...

I create the database programmatically from my application if it doesn't exist or open the existing one in the same directory.

The question is that...
When I create a DB from Windows Vista, I can use it normally in that computer and on any other Vista OS computer... but this DB won't work on an XP or 2003!
The opposite applies as well, DBs generated from the same application running on XPs won't work when opening them on Vista. I.E. it seems that DBs generated in those OS are not compatible.

I'm using v3.1 from a c# .Net2 application. I'm not using encryption or anything, just the simplest ConnectionString.

The exception is thrown then I connect and its Message is "Attempted to read or write protected memory. This is often an indication that other memory is corrupt".

Have you ever seen this?
I there something I could test/change?

Thanks in advance.You could try to send me the SDF file and I will test (winxp sp2), if I can reproduce...|||You can download the database I generated on Windows Vista from here.

The Windows XP test machine is a Virtual PC with Windows XP SP2 and all the updates applied. Both computers have the same language settings and I've even made my app to select the CurrentThread's Culture to "en-US" to avoid possible language problems.|||Erik told me via email that he was able to open the DB in his WinXP using the SQL Management Studio.

So I installed a development environment in a WinXP virtual machine to see what happened... results:

After installing VS2005 + SQLServer2005 + SQLServerCE:
1) A connection cannot be stablished from a .Net application
2) The database can be opened from SQL Server Management Studio

but (and this is the most interesting part)

3) After opening the DB with the Management Studio, the database file is modified (checked size and date) and now it CAN be opened from the same .Net application.

so, the SQL Server Management Studio is "fixing" the database.

4) The "fixed" database now works in XP and Vista.

will continue investigating.

(for your information. The original Vista database was generated programatically creating everything using SQL commands).|||More info:

Repairing the database (SQLCeEngine.Repair) works more or less.

- If I repair the Vista-generated database in WixXP before opening it, it will work.

- If I repair it in Vista and then try to open it in XP it won't work.
- In the other hand... if I repair the DB in XP it won't work in Vista.

So, it seems like you have to repair the database before opening it on the other OS. And repairing it on one makes it incompatible on the other.

ajjjjjj!!!!|||

Ok, Fernando.

Using the supplied app and sdf and this connection string, it get the same error (on XP SP2)

Code Snippet

Data Source ="TestDBIbistic2007.sdf"; File Mode=Read Only; Temp Path = %temp%

It is fixed by changing the connection string to:

Code Snippet

Data Source ="TestDBIbistic2007.sdf"

So the sdf is in a bad state somehow...

|||The database will be located in a read-only drive, that's why I added the "FileMode=Read Only" thing (and therefore "Temp Path=%temp%").

I will test if I can open the database without the Read Only modifier from a read only folder.
Another solution is to copy the database (that shouldn't be too big) to the temp folder and use it from there...

Anyway

Thanks a lot for your help, Erik!

Compatibility issues between Vista/XP?

HI

I have just started using SQL Server Compact Edition. I have developed a test application just to see if it fits my needs but found something extremely weird.

It's so strange that I guess I'm doing something wrong...

I create the database programmatically from my application if it doesn't exist or open the existing one in the same directory.

The question is that...
When I create a DB from Windows Vista, I can use it normally in that computer and on any other Vista OS computer... but this DB won't work on an XP or 2003!
The opposite applies as well, DBs generated from the same application running on XPs won't work when opening them on Vista. I.E. it seems that DBs generated in those OS are not compatible.

I'm using v3.1 from a c# .Net2 application. I'm not using encryption or anything, just the simplest ConnectionString.

The exception is thrown then I connect and its Message is "Attempted to read or write protected memory. This is often an indication that other memory is corrupt".

Have you ever seen this?
I there something I could test/change?

Thanks in advance.You could try to send me the SDF file and I will test (winxp sp2), if I can reproduce...|||You can download the database I generated on Windows Vista from here.

The Windows XP test machine is a Virtual PC with Windows XP SP2 and all the updates applied. Both computers have the same language settings and I've even made my app to select the CurrentThread's Culture to "en-US" to avoid possible language problems.|||Erik told me via email that he was able to open the DB in his WinXP using the SQL Management Studio.

So I installed a development environment in a WinXP virtual machine to see what happened... results:

After installing VS2005 + SQLServer2005 + SQLServerCE:
1) A connection cannot be stablished from a .Net application
2) The database can be opened from SQL Server Management Studio

but (and this is the most interesting part)

3) After opening the DB with the Management Studio, the database file is modified (checked size and date) and now it CAN be opened from the same .Net application.

so, the SQL Server Management Studio is "fixing" the database.

4) The "fixed" database now works in XP and Vista.

will continue investigating.

(for your information. The original Vista database was generated programatically creating everything using SQL commands).|||More info:

Repairing the database (SQLCeEngine.Repair) works more or less.

- If I repair the Vista-generated database in WixXP before opening it, it will work.

- If I repair it in Vista and then try to open it in XP it won't work.
- In the other hand... if I repair the DB in XP it won't work in Vista.

So, it seems like you have to repair the database before opening it on the other OS. And repairing it on one makes it incompatible on the other.

ajjjjjj!!!!|||

Ok, Fernando.

Using the supplied app and sdf and this connection string, it get the same error (on XP SP2)

Code Snippet

Data Source ="TestDBIbistic2007.sdf"; File Mode=Read Only; Temp Path = %temp%

It is fixed by changing the connection string to:

Code Snippet

Data Source ="TestDBIbistic2007.sdf"

So the sdf is in a bad state somehow...

|||The database will be located in a read-only drive, that's why I added the "FileMode=Read Only" thing (and therefore "Temp Path=%temp%").

I will test if I can open the database without the Read Only modifier from a read only folder.
Another solution is to copy the database (that shouldn't be too big) to the temp folder and use it from there...

Anyway

Thanks a lot for your help, Erik!