Combining Values

I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3

And combine them into this:

John Doe | Address 1; Address 2; Address 3

Do I need a cursor for this?imani_technology_spam@.yahoo.com wrote:

Quote:

Originally Posted by

I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:
>
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
>
And combine them into this:
>
John Doe | Address 1; Address 2; Address 3
>
>
Do I need a cursor for this?

Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.|||I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

On Jun 29, 6:55 pm, Ed Murphy <emurph...@.socal.rr.comwrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:

>

Quote:

Originally Posted by

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3

>

Quote:

Originally Posted by

And combine them into this:

>

Quote:

Originally Posted by

John Doe | Address 1; Address 2; Address 3

>

Quote:

Originally Posted by

Do I need a cursor for this?

>
Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.- Hide quoted text -
>
- Show quoted text -

|||If you cant do this in reports, refer this

http://sqljunkies.com/WebLog/amacha...px?Pending=true
Madhivanan

On Jun 30, 10:47 am, "imani_technology_s...@.yahoo.com"
<imani_technology_s...@.yahoo.comwrote:

Quote:

Originally Posted by

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.
>
On Jun 29, 6:55 pm, Ed Murphy <emurph...@.socal.rr.comwrote:
>
>
>

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

I need to do something that goes against normalization, but it is what
the client wants. Let's say one person has several addresses. I need
to be able to take each of those addresses and combine them into one
field. So I need to take this:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3

>

Quote:

Originally Posted by

Quote:

Originally Posted by

And combine them into this:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

John Doe | Address 1; Address 2; Address 3

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Do I need a cursor for this?

>

Quote:

Originally Posted by

Do this in the reporting layer (e.g. Crystal Reports) if at
all possible.- Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -- Hide quoted text -

>
- Show quoted text -

|||imani_technology_spam@.yahoo.com wrote:

Quote:

Originally Posted by

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

You have three options:

1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML

Option 1) is the safest method.

For option 2) details refer to:
http://groups.google.pl/group/micro...cab9fecb969f34/
Third method:
http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx
--
Best regards,
Marcin Guzowski
http://guzowski.info|||Here's where things get interesting:

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2

The results need to be

John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2

I have no idea how to pull this off.

On Jun 30, 6:39 am, "Marcin A. Guzowski"
<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

>
You have three options:
>
1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML
>
Option 1) is the safest method.
>
For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...
>
Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx
>
--
Best regards,
Marcin Guzowskihttp://guzowski.info

|||On Jul 2, 12:48 am, "imani_technology_s...@.yahoo.com"
<imani_technology_s...@.yahoo.comwrote:

Quote:

Originally Posted by

Here's where things get interesting:
>
John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2
>
The results need to be
>
John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2
>
I have no idea how to pull this off.
>
On Jun 30, 6:39 am, "Marcin A. Guzowski"
>
>
>
<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:

Quote:

Originally Posted by

I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

>

Quote:

Originally Posted by

You have three options:

>

Quote:

Originally Posted by

1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML

>

Quote:

Originally Posted by

Option 1) is the safest method.

>

Quote:

Originally Posted by

For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...

>

Quote:

Originally Posted by

Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx

>

Quote:

Originally Posted by

--
Best regards,
Marcin Guzowskihttp://guzowski.info- Hide quoted text -

>
- Show quoted text -

You could use a WHILE loop to select row by row for each person.
SELECT the COUNT of addresses for the current person into one variable
and build up a text string of the concatenated addresses into another
variable looping round until you've got them all. Then update the
column in the table with the value of the variables.|||Sorry, I'm a little rusty on cursors. How would I pull this off? Can
I (or should I) use nested WHILE loops?

On Jul 2, 3:28 am, Stephen2 <Step...@.mailinator.comwrote:

Quote:

Originally Posted by

On Jul 2, 12:48 am, "imani_technology_s...@.yahoo.com"
>
>
>
>
>
<imani_technology_s...@.yahoo.comwrote:

Quote:

Originally Posted by

Here's where things get interesting:

>

Quote:

Originally Posted by

John Doe | Address 1
John Doe | Address 2
John Doe | Address 3
Jane Smith | Address 1
Jane Smith | Address 2

>

Quote:

Originally Posted by

The results need to be

>

Quote:

Originally Posted by

John Doe | 3; Address 1; Address 2; Address 3
Jane Smith | 2; Address 1; Address 2

>

Quote:

Originally Posted by

I have no idea how to pull this off.

>

Quote:

Originally Posted by

On Jun 30, 6:39 am, "Marcin A. Guzowski"

>

Quote:

Originally Posted by

<tu_wstaw_moje_i...@.guzowski.infowrote:

Quote:

Originally Posted by

imani_technology_s...@.yahoo.com wrote:
I wish I could, but this is a data migration. I HAVE to put this
stuff in the table. The customer requires it. More specifically,
Commerce Server requires it.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

You have three options:

>

Quote:

Originally Posted by

Quote:

Originally Posted by

1) aggregate concatenation in cursor
2) aggregate concatenation in SELECT query
3) aggregate concatenation using FOR XML

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Option 1) is the safest method.

>

Quote:

Originally Posted by

Quote:

Originally Posted by

For option 2) details refer to:http://groups.google.pl/group/micro...er.programming/...

>

Quote:

Originally Posted by

Quote:

Originally Posted by

Third method:http://sqlblogcasts.com/blogs/tonyr.../07/06/871.aspx

>

Quote:

Originally Posted by

Quote:

Originally Posted by

--
Best regards,
Marcin Guzowskihttp://guzowski.info-Hide quoted text -

>

Quote:

Originally Posted by

- Show quoted text -

>
You could use a WHILE loop to select row by row for each person.
SELECT the COUNT of addresses for the current person into one variable
and build up a text string of the concatenated addresses into another
variable looping round until you've got them all. Then update the
column in the table with the value of the variables.- Hide quoted text -
>
- Show quoted text -

sqlsql

Combining records/Foreach Loop

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

cboom wrote:

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

Won't the following work:

UPDATE h

SET h.DentalConcern = c.MaxDentalConcern,

h.VisionConcern = c.MaxVisionConcern,

c.HearingConcern = c.MaxHearingConcern

FROM Health h

INNER JOIN (

SELECT ChildID,

CAST(MAX(CAST(DentalConcern as tinyint)) AS bit) as MaxDentalConcern,

CAST(MAX(CAST(VisionConcern as tinyint)) AS bit) as MaxVisionConcern,

CAST(MAX(CAST(HearingConcern as tinyint)) AS bit) as MaxHearingConcern,

FROM concerns

GROUP BY ChildID

) c

ON h.ChildID = c.ChildID

?

-Jamie

|||

Well, back to basic Transact-SQL for me. Did play with doing Max on the boolean fields which obviously didn't work, and didn't even think to Cast to integer. Many, many thanks.

Chera

Combining Multiple Values into One Field

I have two tables called tblName and tblType.

tblName
Name Type
Mike 1
Frank 2
Robert 2
Edward 3
David 4
Albert 4

I want the output to look like this in the tblType table:

tblType
Type Name
1 Mike
2 Frank, Robert
3 Edward
4 David, Albert
5

When I execute the following vb code all I get is the last value from the tblName table:

strSQL = "UPDATE tblType "
strSQL = strSQL & "SET tblType.Name = tblType.Name + " ', ' + "
strSQL = strSQL & "tblName.Name "
strSQL = strSQL & "FROM tblName, tblType "
strSQL = strSQL & "WHERE tblName.type = tblType.type;"

Any suggestions?

ThanksUsing COALESCE to Build Comma-Delimited String (http://sqlteam.com/item.asp?ItemID=2368)

rudy
http://r937.com/|||Thanks Rudy, I knew there was some way of accomplishing this. I went to the link you posted and gave the COALESCE Function a quick read but is there a way of substituting the comma for another value such as a vbCRLF?

Thanks,

Mark|||is there a way of substituting the comma for another value such as a vbCRLF
don't see why not -- didja try it?|||Rudy,
Thanks for your post. I was unable to make the query do what I initally wanted it to do. I was able to accomplish what I wanted by using two nested recordsets. It takes a little longer to complete but it does what I want it to do.

Thanks!

Mark

Combining Fields to string

I have a function that takes a field with values separated by commas within the field and splits them to multiple rows.

Example:
Field - Interior
Value - abc,def,efg,ghi

Output:
ID Item
1 abc
2 def
3 efg
etc

This is working great thanks to help that I received on here.

Now I am combining multiple fields to a string.
Example:
Field1: abc, def
Field2: ghi, jkl

using

SELECT (Field1 + ',' + Field2) From ....

This is working great unless there is a field that has a NULL value. Then I get a NULL result.

Is there an easy way to only put the fields with value into my string and leave out the NULL fields? Some have one NULL field, some have multiple. I just need to get the string to work and get only the fields that have values.

Any suggestions are always appreciated.It has been resolved on another post.

THANKS!!|||

Quote:

Originally Posted by rpeacock

It has been resolved on another post.

THANKS!!

I am having the same problem - can you tell me what other post solved the issue?

Thanks in advance

RIP

combine values in view?

Is there an easy way to do this?
I need to pull sales values for each store and make one record per month.
This used to be easy, as I was provided only one sales record per month per
store, but now I've been told that SOME of the stores have a "secondary" ID
from which to pull sales numbers, and these numbers need to be added to the
number from their PRIMARY number before displaying them. I'm not sure how
to do this.
Here is some sample data (cols don't align well here - sorry):
STORES:
ID1 ID2 STORE
100 AAA A-STORE
200 B-STORE
300 CCC C-STORE
400 D-STORE
(note that B-Store and D-Store have only ONE ID, not a secondary ID)
SALES:
ID SALES MONTH YEAR
100 5.00 01 2004
AAA 5.00 01 2004
200 5.00 01 2004
300 5.00 01 2004
CCC 5.00 01 2004
400 5.00 01 2004
(note that sales values from IDs "100" and "AAA" both belong to "A-Store",
and "300" and "CCC" belong to "C-Store"; also that there is a set of sales
records for each month & year so those fields need to be accounted for.)
I need a view that will show:
ID SALES MONTH YEAR
100 10.00 01 2004
200 5.00 01 2004
300 10.00 01 2004
400 5.00 01 2004
(note that IDs 100 and 300 show the combined sales of both their primary AND
secondary IDs)
Is this easily doable? If yes, how'!!
Thanks!
-RThere are some data integrity problems here, and in particular,
if the ID2 values are not unique, this is something of a mess, but
if the data is not messed up, something like this should work:
select ID, sum(SALES) as SALES, [MONTH], [YEAR]
from (
select ID, SALES, [MONTH], [YEAR]
from SALES
union all
select ST.ID1, SA.SALES, SA.[MONTH], SA.[YEAR]
from SALES AS SA join STORES AS ST
on SA.ID = ST.ID2
) S
group by ID, [MONTH], [YEAR]
If the data is messed up, this query could provide completely
wrong information. You would be better off keeping track
of the store IDs differently:
-- primary IDs only, with store attributes
CREATE TABLE STORES (
ID char(3) primary key,
StoreName varchar(30),
.. other attributes of a store
)
-- all IDs, primary and alternate, for stores, with
-- the primary storeID for each
CREATE TABLE STORE_IDS (
ID char(3) primary key,
storeID char(3) references STORES(ID)
-- put an index on storeID to support the FK
)
The foreign key on SALES would now link to this
second table instead of the first. The query would
be different, too - something like this:
select ST.ID, sum(SA.SALES) as SALES, SA.[MONTH], SA.[YEAR]
from STORES as ST
join STORE_IDS as I
on I.storeID = ST.ID
join SALES as SA
on SA.ID = I.ID
group by ST.ID, SA.[MONTH], SA.[YEAR]
Steve Kass
Drew University
r wrote:

>Is there an easy way to do this?
>I need to pull sales values for each store and make one record per month.
>This used to be easy, as I was provided only one sales record per month per
>store, but now I've been told that SOME of the stores have a "secondary" ID
>from which to pull sales numbers, and these numbers need to be added to the
>number from their PRIMARY number before displaying them. I'm not sure how
>to do this.
>Here is some sample data (cols don't align well here - sorry):
>STORES:
>ID1 ID2 STORE
>100 AAA A-STORE
>200 B-STORE
>300 CCC C-STORE
>400 D-STORE
>(note that B-Store and D-Store have only ONE ID, not a secondary ID)
>SALES:
>ID SALES MONTH YEAR
>100 5.00 01 2004
>AAA 5.00 01 2004
>200 5.00 01 2004
>300 5.00 01 2004
>CCC 5.00 01 2004
>400 5.00 01 2004
>(note that sales values from IDs "100" and "AAA" both belong to "A-Store",
>and "300" and "CCC" belong to "C-Store"; also that there is a set of sales
>records for each month & year so those fields need to be accounted for.)
>I need a view that will show:
>ID SALES MONTH YEAR
>100 10.00 01 2004
>200 5.00 01 2004
>300 10.00 01 2004
>400 5.00 01 2004
>(note that IDs 100 and 300 show the combined sales of both their primary AN
D
>secondary IDs)
>Is this easily doable? If yes, how'!!
>Thanks!
>-R
>
>
>sqlsql

Comapre two values in same resultsets

Hi

I want to compare values of two fields in same resultset. Like I have resultset whrre there are two fields Frequency and New_Frequency I want to compare this two like if

Frequency = New_Frequency then do some logic.

I tried with derived columun using Frequency == New_NewFrequency but it doesn't work.

Let me know is there any way to compare?

Dnyandeo

for derived column use in such a way

a = case when frequency = new_frequency then

begin

some logic

end

else

some logic

end

end

or can u send me the script code that u had used for derived column?

COM access to SERVERPROPERTY values

Is it possible to get the properties retrievable with T-SQL
SERVERPROPERTY via COM access instead? Do you know some sqlserver COM
object that can provide this kind of information (e.g. LicenseType or
NumLicenses)? Any Ideas?You could use SQL DMO object model. See SQL Server Books Online for more
info. See SQLServer2 object for a start.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Holger" <atlan@.tournedos.de> wrote in message
news:1124040877.686226.191970@.g43g2000cwa.googlegroups.com...
> Is it possible to get the properties retrievable with T-SQL
> SERVERPROPERTY via COM access instead? Do you know some sqlserver COM
> object that can provide this kind of information (e.g. LicenseType or
> NumLicenses)? Any Ideas?
>|||Thank you for your reply but in COM programming it is always good to
read the manual first. The object SQLServer2 does not provide access to
the properties LicenseType or NumLicenses exposed by SERVERPROPERTY.
These properties are of special interest to me. Any further object you
can recommend?|||Another approach is simply the registry to get these values
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\M
icrosoft SQL
Server\80\MSSQLLicenseInfo\MSSQL8.00]
"ConcurrentLimit"=dword:00000006
"Mode"=dword:00000002
Mode = 2 means PerProcessor
Mode != 2 means PerSeat
ConcurrentLimit = Number of Seats OR Processors
Just to complete this topic,
Holger

Columns with multiple values ?

Hi,
The values I need to store in the table are

Student ID
Student Name
Subjects

The "Student ID" is the primary key.

A student can take more than 1 subject.

For example:
Student ID: 100
Student Name: Kelly Preston
Subjects: Geography, History, Math

How can I store these values in a database table?
I know the normal "INSERT" statement, but how would I store the multiple subjects for a single student ID?

My "Student ID" is auto generated. If I create a new row for each subject, the Student ID will be different for each subject, which I dont want.

Or I can create a new field called "RowNumber" and keep that the primary key..
For example:

Row Number StudentID StudentName Subject
1 100 Kelly Geography
2 100 Kelly History
3 100 Kelly Math

If this is the only way to store the multiple sibjects, then for a given student ID (say 100), how can I retreieve the associated name and subjects? What is the query for that?...If this is the only way to store the multiple sibjects...It isn't. I'm afraid that your design is really flawed. You would want to be certain that your design is correct before you even start thinking about SQL.

Have a read of this and then come back and let us know what you think your design should now be
http://r937.com/relational.html

EDIT: Oops - first post - welcome to the forum :D

HTH|||Pay particular attention to the section on many-to-many relationships, which I think is applicable to your situation.|||Table # 1: Student Details
---------
Student ID (primary key)
Student Name

Table # 2: Subjects
-------
RowID (primary key)
Student ID
Subject

For example:
We have 2 students, Kelly and Mary.
Kelly's subjects are History and Math
Mary's subjects are Math and English

Table # 1
----
Student ID Student Name
100 Kelly
200 Mary

Table # 2:
----
RowID StudentID Subject
1 100 History
2 100 Math
3 200 Math
4 200 English

When I want to display Kelly's details:
select * from 'Student Details' where StudentID = '100' UNION select * from Subjects where StudentID = '100'

The SQL query might not be of the right syntax, but is this idea right?

I believe this is a one-to-many relationship..|||No. You do not need a UNION query to do this, and it won't even work unless the designs of the two tables are identical.

Again, your database design is flawed. Please read the section on how to create many-to-many relationships.|||The SQL query might not be of the right syntax, but is this idea right?You've got a few details left to resolve, but you've definitely got the right idea. You want to use a JOIN instead of a UNION, but you're headed the right way now.

-PatP

Columns inster into problem

Hi Every1,
This is the first time I'm trying to use columns by themselves instead
of using individual values.
And I'm getting an error.
Here is my sql
INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
& the Error is
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.
I've matched all the columns. Second table have extra columns & I
didn't included them because then the columns will not match.
Also I only want to insert the values which are not currently present
in table 1.
Thanks in advance for your help.
Try...
INSERT INTO PS_CUST_CONTACT
SELECT SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
Almost correct but you didn't need the brackets around the select columns,
and you don't need column names in the insert.
Peter
"You can always count on Americans to do the right thing - after they've
tried everything else."
Winston Churchill
"aspnetpal" wrote:

> Hi Every1,
> This is the first time I'm trying to use columns by themselves instead
> of using individual values.
> And I'm getting an error.
> Here is my sql
> INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
> ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
> SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
> LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
> NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
> & the Error is
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.
> I've matched all the columns. Second table have extra columns & I
> didn't included them because then the columns will not match.
> Also I only want to insert the values which are not currently present
> in table 1.
> Thanks in advance for your help.
>
|||Hello Peter, thanks for your suggestion.
After removing the brackets it worked just fine.
Thanks

Columns inster into problem

Hi Every1,
This is the first time I'm trying to use columns by themselves instead
of using individual values.
And I'm getting an error.
Here is my sql
INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
& the Error is
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.
I've matched all the columns. Second table have extra columns & I
didn't included them because then the columns will not match.
Also I only want to insert the values which are not currently present
in table 1.
Thanks in advance for your help.Try...
INSERT INTO PS_CUST_CONTACT
SELECT SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
Almost correct but you didn't need the brackets around the select columns,
and you don't need column names in the insert.
Peter
"You can always count on Americans to do the right thing - after they've
tried everything else."
Winston Churchill
"aspnetpal" wrote:

> Hi Every1,
> This is the first time I'm trying to use columns by themselves instead
> of using individual values.
> And I'm getting an error.
> Here is my sql
> INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
> ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
> SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
> LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
> NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
> & the Error is
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.
> I've matched all the columns. Second table have extra columns & I
> didn't included them because then the columns will not match.
> Also I only want to insert the values which are not currently present
> in table 1.
> Thanks in advance for your help.
>|||Hello Peter, thanks for your suggestion.
After removing the brackets it worked just fine.
Thanks

Columns inster into problem

Hi Every1,
This is the first time I'm trying to use columns by themselves instead
of using individual values.
And I'm getting an error.
Here is my sql
INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
& the Error is
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ','.
I've matched all the columns. Second table have extra columns & I
didn't included them because then the columns will not match.
Also I only want to insert the values which are not currently present
in table 1.
Thanks in advance for your help.Try...
INSERT INTO PS_CUST_CONTACT
SELECT SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
LAST_MAINT_OPRID, DATE_LAST_MAINT FROM PS_CONTACT_CUST WHERE CUST_ID
NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
Almost correct but you didn't need the brackets around the select columns,
and you don't need column names in the insert.
Peter
"You can always count on Americans to do the right thing - after they've
tried everything else."
Winston Churchill
"aspnetpal" wrote:
> Hi Every1,
> This is the first time I'm trying to use columns by themselves instead
> of using individual values.
> And I'm getting an error.
> Here is my sql
> INSERT INTO PS_CUST_CONTACT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS,
> ADDRESS_SEQ_NUM, LAST_MAINT_OPRID, DATE_LAST_MAINT)
> SELECT (SETID, CUST_ID, CNTCT_SEQ_NUM, EFF_STATUS, ADDRESS_SEQ_NUM,
> LAST_MAINT_OPRID, DATE_LAST_MAINT) FROM PS_CONTACT_CUST WHERE CUST_ID
> NOT IN(SELECT CUST_ID FROM PS_CUST_CONTACT)
> & the Error is
> Server: Msg 170, Level 15, State 1, Line 2
> Line 2: Incorrect syntax near ','.
> I've matched all the columns. Second table have extra columns & I
> didn't included them because then the columns will not match.
> Also I only want to insert the values which are not currently present
> in table 1.
> Thanks in advance for your help.
>|||Hello Peter, thanks for your suggestion.
After removing the brackets it worked just fine.
Thanks

Columns in Crystal

hey

is it possible to create columns under crystal ... so instead of the values going vertically, for them go to horizontally ...

eg. when you put the value 'icecream'

it is going to show you all the possible icecream types

eg:
vanilla
chocolate
strawberry
etc

now would you be able to show them like this :

vanilla chocalate strawberry etc .....Even I would like to know if it is possible, right now I m using temp tables to achieve this.|||hey

well i have found a way that it can be done. under the details section in crystal, select 'Format with Multiple Columns'. tt should display the values going horizontally rather than vertically. but there are still some issues outstanding like formatting of the actual values. you do not seem to be able to move the field after you have selected the 'Format with Multiple Column'.

hope it helps.

cheers

milos|||Hey

yes its me again ... Well there is a better way than the one described previously.

By Inserting a Cross-Tab, you have the option of selecting values to show as Columns and Rows. Its a VERY useful feature for those who need to add columns in their reports.

Any questions, feel free to ask ...

Cheers

Milos

columns containing percantage values

Hi,
I am a newbie in reporting services with a pretty easy question.
How can display percentage values of a column depending of the column
sum?
Example:
_______________________________________________
row1 3 30%
row2 5 50%
row3 2 20%
_______________
sum 10 100%
_______________________________________________
How do I create the last column?
ThanxLast week I did this for the first time.
Created a sum total in a report footer for your 2nd col and the10 would show
for your sample. Give it a label like Total_Count. Then for the 3rd col
which is to be the percentage, use the expression =Sum(Fields!Total_Count.
Value)/(ReportItems!TotalCount.Value)
HTH!
James Ski
mickmack wrote:
>Hi,
>I am a newbie in reporting services with a pretty easy question.
>How can display percentage values of a column depending of the column
>sum?
>Example:
>_______________________________________________
>row1 3 30%
>row2 5 50%
>row3 2 20%
>_______________
>sum 10 100%
>_______________________________________________
>How do I create the last column?
>Thanx
--
Message posted via http://www.sqlmonster.com|||Thank you James for your prompt reply,
that works with one column.
But how would I create the following matrix?
_____________________________________
row1 3 30% 8 40%
row2 5 50% 8 40%
row3 2 20% 4 20%
_________________________
sum 10 100% 20 100%
_____________________________________
I dont know how to call the 10 and the 20 seperatly. Which names do
they have?
Thanx|||You could use the sum aggregate , but shouldn't the group % always be 100%..
You could also refer to the column with 10 in it using
ReportItems!textboxname.Value
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"mickmack" wrote:
> Thank you James for your prompt reply,
> that works with one column.
> But how would I create the following matrix?
> _____________________________________
> row1 3 30% 8 40%
> row2 5 50% 8 40%
> row3 2 20% 4 20%
> _________________________
> sum 10 100% 20 100%
> _____________________________________
> I dont know how to call the 10 and the 20 seperatly. Which names do
> they have?
> Thanx
>|||here is how I did:
drag a list into your report, in the list, drag in two subreports, one is
on the left and another one is on the right. then you write stored procudures
(name it sptest for now) with one parameter. In your subreport one, you call
sptest 0, and it will display the left 3 columns, subreport two, you call
sptest 1, and it will display the right 2 columns. sptest 0, and sptest 1 is
the dataset that you create when you design your subreport. The hard part for
this is the store procedure. you need to decide how many rows/columns that
you want to display. For my example, sptest 0, I display:
01/2004 5
02/2004 6
03/2004 7
.
.
.
sptest 1, then display on the second subreport
01/2005 1
02/2005 2
03/2005 3
.
.
.
finally, it ends up like this
01/2004 5 01/2005 1
02/2004 6 02/2005 2
03/2004 7 03/2005 3
. .
. .
. .
I hope this will give you some idea.
Henry
"mickmack" wrote:
> Thank you James for your prompt reply,
> that works with one column.
> But how would I create the following matrix?
> _____________________________________
> row1 3 30% 8 40%
> row2 5 50% 8 40%
> row3 2 20% 4 20%
> _________________________
> sum 10 100% 20 100%
> _____________________________________
> I dont know how to call the 10 and the 20 seperatly. Which names do
> they have?
> Thanx
>|||today I found a page at microsoft, where the problem is solved:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/semiadd2.asp

Column type to select data from big tables

Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.
The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.
|||If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.
|||Uytkownik Roger Wolter[MSFT] napisa:

> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much more
> efficient than converting a character column to integers which you're making
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn't
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.

Column type to select data from big tables

Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||Uytkownik Roger Wolter[MSFT] napisa:

> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much mo
re
> efficient than converting a character column to integers which you're maki
ng
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn'
t
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.

Column type to select data from big tables

Hello,
I have a system containing tables with data (number of rows in K or even
M). I will be selecting data from these tables using values in one column.
Certainly I will make an indeks on this column.
I can choose type of this column (int, char)
Is it important to performance what will be this type ?
For example selecting data using index on small int column will be
faster than using index on char(5) column ?
M.The more selective your index, then the more efficient it behaves.
I don't think (Although am prepared to be corrected on this) that the actual
data type matters much especially when dealing with basic data types like
int & char.
I would have thought a varhcar(5) would be more space efficient than a
char(5) though.
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||If you can decide the type of the column, I assume the data is a number so
if you are going to select on numbers, making the column an int is much more
efficient than converting a character column to integers which you're making
the change. In general, SQL Server can compare integers faster than it can
compare strings because strings have to take collation into account in
ordering while integers are compared the same in all languages. This isn't
a huge difference but if you can choose without affecting functionality,
choose int.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"MerlinXP" <MerlinXP_NOSPAM@.NOSPAM_poczta.onet.pl> wrote in message
news:uaFQb0k3GHA.5032@.TK2MSFTNGP04.phx.gbl...
> Hello,
> I have a system containing tables with data (number of rows in K or even
> M). I will be selecting data from these tables using values in one column.
> Certainly I will make an indeks on this column.
> I can choose type of this column (int, char)
> Is it important to performance what will be this type ?
> For example selecting data using index on small int column will be faster
> than using index on char(5) column ?
> M.|||U¿ytkownik Roger Wolter[MSFT] napisa³:
> If you can decide the type of the column, I assume the data is a number so
> if you are going to select on numbers, making the column an int is much more
> efficient than converting a character column to integers which you're making
> the change.
I can choose both: type of column and type of value. So, if I choose
char type I will fill column with char data. No conversion is needed.
In general, SQL Server can compare integers faster than it can
> compare strings because strings have to take collation into account in
> ordering while integers are compared the same in all languages. This isn't
> a huge difference but if you can choose without affecting functionality,
> choose int.
My intuition said the same, but I was not sure.
Thanks.
M.

Column name or number of supplied values...

Greetings
Appologies in advance as this is a bit of a newbie question as I have just
started playing with 2005 replication.
I am getting the error
Column name or number of supplied values does not match table definition.
I began ok by setting up replication between 2 servers on the same network.
It replicated some data across ok but I noticed that some tables did not
replicate. When I looked deeper at the articles it said that they can't
replicate because they didn't have a primary key. Ok so I modified the table
in the GUI and added the primary key (it is an identity column) and then the
replication error appeared for the stored procedure that adds entries to
that table.
I then did some looking around and found others had had similar problems. It
looks like I went about it the wrong way (newbie.newbie), so I thought, ok
I'll make sure all the primary keys are there first and then replicate. I
did this by deleting the subscriptions and disabling Pub&Dist, added the PK
and setup replication again. Error appears again.
I have tried numerous ways to "clean up" the Publisher/Distributor data and
remove the replication. While it all looks like it has been removed, if I
setup the replication again it seems to "know" about the previous problem
and my error message keeps appearing. I have even completely removed the
database I am replicating from the publisher and subscriber machines, have
used the procedure in the BOL to disable Pub & Dist
(sp_removedbreplication,sp_dropdistpublisher,sp_dr opdistributiondb,sp_dropdistributor),
and done a full restore of the offending database, however next time I setup
the replication I get the same error.
I maybe off track and identity columns are a no-no?
Anyway sorry for the long winded story. Any help would be creatly
appreciated. I am thinking of doing a compete SQL Server reload as it is
only a test system, but if I don't have to....
Cheers
Peter Lock
PalaceSoftware, Canberra OZ
I think you need to either update your replication stored procedures for the
newly added pk, or update the tables at the subscriber. I think the best
thing to do would be to generate a new snapshot and redistribute it.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Peter" <palace@.internode.on.net> wrote in message
news:uFcN5QM7GHA.1252@.TK2MSFTNGP04.phx.gbl...
> Greetings
> Appologies in advance as this is a bit of a newbie question as I have just
> started playing with 2005 replication.
> I am getting the error
> Column name or number of supplied values does not match table definition.
> I began ok by setting up replication between 2 servers on the same
> network. It replicated some data across ok but I noticed that some tables
> did not replicate. When I looked deeper at the articles it said that they
> can't replicate because they didn't have a primary key. Ok so I modified
> the table in the GUI and added the primary key (it is an identity column)
> and then the replication error appeared for the stored procedure that adds
> entries to that table.
> I then did some looking around and found others had had similar problems.
> It looks like I went about it the wrong way (newbie.newbie), so I thought,
> ok I'll make sure all the primary keys are there first and then replicate.
> I did this by deleting the subscriptions and disabling Pub&Dist, added the
> PK and setup replication again. Error appears again.
> I have tried numerous ways to "clean up" the Publisher/Distributor data
> and remove the replication. While it all looks like it has been removed,
> if I setup the replication again it seems to "know" about the previous
> problem and my error message keeps appearing. I have even completely
> removed the database I am replicating from the publisher and subscriber
> machines, have used the procedure in the BOL to disable Pub & Dist
> (sp_removedbreplication,sp_dropdistpublisher,sp_dr opdistributiondb,sp_dropdistributor),
> and done a full restore of the offending database, however next time I
> setup the replication I get the same error.
> I maybe off track and identity columns are a no-no?
> Anyway sorry for the long winded story. Any help would be creatly
> appreciated. I am thinking of doing a compete SQL Server reload as it is
> only a test system, but if I don't have to....
> Cheers
> Peter Lock
> PalaceSoftware, Canberra OZ
>
|||Hi Hilary
Thank you for the prompt reply. I had already tried numerous new snapshots
and reloads, however I think I have worked out where the problem is (at
least it now replicates all articles without error). It looks like lazy mans
T-SQL is the problem.
I had a table with 2 columns ID (tinyint) as identity and ComputerName as
varchar(50). I set the PK in ID (which I didn't have before because I didn't
need it), and my stored procedure for adding a new entry was
INSERT Computers VALUES (@.ComputerName)
Once I changed my stored procedure to
INSERT Computers (ComputerName) VALUES (@.ComputerName)
the errors disappeared.
I'll give it a good test tonight and see how it goes.
Cheers
Peter Lock
PalaceSoftware, Canberra OZ
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uA%23LjfN7GHA.2288@.TK2MSFTNGP05.phx.gbl...
>I think you need to either update your replication stored procedures for
>the newly added pk, or update the tables at the subscriber. I think the
>best thing to do would be to generate a new snapshot and redistribute it.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Peter" <palace@.internode.on.net> wrote in message
> news:uFcN5QM7GHA.1252@.TK2MSFTNGP04.phx.gbl...
>
|||OK, I take it the errors were occurring on the publisher side, and in this
case were probably not replication related.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Peter" <palace@.internode.on.net> wrote in message
news:ugK3chP7GHA.3452@.TK2MSFTNGP05.phx.gbl...
> Hi Hilary
> Thank you for the prompt reply. I had already tried numerous new snapshots
> and reloads, however I think I have worked out where the problem is (at
> least it now replicates all articles without error). It looks like lazy
> mans T-SQL is the problem.
> I had a table with 2 columns ID (tinyint) as identity and ComputerName as
> varchar(50). I set the PK in ID (which I didn't have before because I
> didn't need it), and my stored procedure for adding a new entry was
> INSERT Computers VALUES (@.ComputerName)
> Once I changed my stored procedure to
> INSERT Computers (ComputerName) VALUES (@.ComputerName)
> the errors disappeared.
> I'll give it a good test tonight and see how it goes.
> Cheers
> Peter Lock
> PalaceSoftware, Canberra OZ
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uA%23LjfN7GHA.2288@.TK2MSFTNGP05.phx.gbl...
>

column name as group in a table

Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
MangeshGroup is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
Mangesh|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:
> Group is a reserved word. It's a best practice not to use column names that
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what'
> Mangesh
>|||"Mangesh Deshpande" schrieb:
> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller|||Thanks a lot.
"doller" wrote:
> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>

column name as group in a table

Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what?
Mangesh
Group is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what?
Mangesh
|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:

> Group is a reserved word. It's a best practice not to use column names that
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what?
> Mangesh
>
|||"Mangesh Deshpande" schrieb:

> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.
|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller
|||Thanks a lot.
"doller" wrote:

> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>

column name as group in a table

Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
MangeshGroup is a reserved word. It's a best practice not to use column names that
are reserved words. If you insist on using reserved words, use square
brackets:
[Group]
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
Hi
I have a table created by user as
use base
insert into table1 values ( 'x')
select * from table1
where group = 'x'
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Group'.
My question is :
I tried to create a table with
create table x ( group varchar(10))
and it gives error
But same table I Can create from EM with group column.
But I Can not write a where clause on the group column.
Is it a bug or what'
Mangesh|||Thanks Tom. So what is the difference when you put a square bracket and
when you don't
"Tom Moreau" wrote:

> Group is a reserved word. It's a best practice not to use column names th
at
> are reserved words. If you insist on using reserved words, use square
> brackets:
> [Group]
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Mangesh Deshpande" <MangeshDeshpande@.discussions.microsoft.com> wrote in
> message news:1020953B-89BF-46BE-9A6E-8A1B41E40D30@.microsoft.com...
> Hi
> I have a table created by user as
> use base
> insert into table1 values ( 'x')
> select * from table1
> where group = 'x'
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'Group'.
> My question is :
> I tried to create a table with
> create table x ( group varchar(10))
> and it gives error
> But same table I Can create from EM with group column.
> But I Can not write a where clause on the group column.
> Is it a bug or what'
> Mangesh
>|||"Mangesh Deshpande" schrieb:

> Thanks Tom. So what is the difference when you put a square bracket and
> when you don't
Object names between square brackets are called 'delimited identifiers', and
delimited identifiers allow you to break each and every restriction of name
creation (e.g. VERY long names, names that contain spaces or other forbidden
characters, names that are reserved words, etc.).
Delimited identifiers are not comfortable to handle though - why don't you
translate the name 'group' into Hindi or Punjab? It is rather unlikely that
it will still conflict with a reserved word ... ;-)
Another solution is using the hungarian notation: the character field
'group' would then be 'cGroup' (or 'vcGroup') which both wouldn't conflict
with the reserved words.|||Hi,
If u understand the process of query execution then u should know that
microsoft compiler detects the reserverd name and from the information
of BOL microsoft suggest to use the reserved names in brakets becasue
on compiltion time microsoft doesnt detects as reserved becasue of
deliminated identifier.
create table ff([group] varchar(20))
from
doller|||Thanks a lot.
"doller" wrote:

> Hi,
> If u understand the process of query execution then u should know that
> microsoft compiler detects the reserverd name and from the information
> of BOL microsoft suggest to use the reserved names in brakets becasue
> on compiltion time microsoft doesnt detects as reserved becasue of
> deliminated identifier.
> create table ff([group] varchar(20))
> from
> doller
>