Kind of cross-tab query

Hi
I'd like to get the results below from this sort of data. I might also have
additional tables that need another 'prods' type column.
Thanks
Andrew
declare @.docs table (docID int primary key, docname varchar(25))
declare @.prods table (prodID int primary key, docID int, prodname
varchar(25))
insert @.docs values (1, 'doc1')
insert @.docs values (2, 'doc2')
insert @.docs values (3, 'doc3')
insert @.prods values (1, 1, 'prod1')
insert @.prods values (2, 2, 'prod2')
insert @.prods values (3, 1, 'prod3')
insert @.prods values (4, 2, 'prod4')
insert @.prods values (5, 3, 'prod5')
insert @.prods values (6, 2, 'prod6')
/*
docID docname prods
-- -- --
1 doc1 prod1, prod3
2 doc2 prod2, prod4, prod6
3 doc3 prod5
*/In general, a recommended approach is to extract the resultset outside the
server and massage the data to appropriate display format using some client.
Regarding the workarounds for forcing this at the server, you can check out
the following links:
( For SQL 2005 only )
http://groups.google.com/group/micr...br />
9b9b968a
( For SQL 2000 & 2005 )
http://groups.google.com/group/micr...br />
6dd9e73e
Anith|||For all types of static and dynamic crosstabs server side it's recommended
you check out RAC.Powerful and easy.
www.rac4sql.net

Kill cmd process started from SQL Server Agent

Hi!
I have a small problem , but it's still a problem.
I have a SQL Server Agent job that runs a .cmd file. This CMD is logged to a textfile.
This process is locked, waiting for me to type a password, but I have nowhere to type that pass.

What I want to do is kill the process that i locking the logfile, because since the logfile is locked, the job cannot be started again (and it's a scheduled job).
The jobs status is 'Not Running'.
I have solved the problem by making the cmd write to another logfile, so the schedule will work, but the file is still locked, and I don't want to restart the server since it's a productionserver.

How to I find the process that is initialized from SQL Agent, and kill it?

Thanks!

BixCould be you'll have a big problem...

what does the cmd file execute?

If it's ANY type of GUI you could hang the box...

What's in the cmd file?|||Nope, no GUI is executed.
It's juat a matter of reading textfiles, formatting the data and inserting it into the db.

The part that hangs is a "Net Use"-command for accessing a networkshare.
I have added the user and pass so that this does not happen again...|||I'm not sure about this, but I believe you will find cmdexec in your system processes. You can kill the PID and it should take care of you.|||killing the parent process without terminating the child may lead to system instability. cmdexec does not take care of anything that had been invoked from it.

Keep table as it is..

Hi
I have a publication with about 30 tables. For one of the tables
'tabA', I only want to publish 4 of the 6 colums, so i uncheck them in
"publish columns". None of them are keys of any kind.
Each subscriber does have a complete 'tabA' at startup and i only want
to transfer changes made to the first 4 coulmns after it is added as a
subscriber.
Now, the prolem is
1. When the snapshot is created i get a end of file reached,
terminator missing of field data incomplete for the .bcp.
2. I get an error since it tried to copy over records that are already
there.
So, is there a way to NOT copy any data for this table during the
snapshot, and only send changes for the first 4 columns in the future
after the snapshot?
You may want to post this in the replication newsgroup. Hilary should be
able to answer that one real quick.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<jostein.solstad@.gmail.com> wrote in message
news:1185459329.169314.161210@.r34g2000hsd.googlegr oups.com...
Hi
I have a publication with about 30 tables. For one of the tables
'tabA', I only want to publish 4 of the 6 colums, so i uncheck them in
"publish columns". None of them are keys of any kind.
Each subscriber does have a complete 'tabA' at startup and i only want
to transfer changes made to the first 4 coulmns after it is added as a
subscriber.
Now, the prolem is
1. When the snapshot is created i get a end of file reached,
terminator missing of field data incomplete for the .bcp.
2. I get an error since it tried to copy over records that are already
there.
So, is there a way to NOT copy any data for this table during the
snapshot, and only send changes for the first 4 columns in the future
after the snapshot?

Keep table as it is..

Hi
I have a publication with about 30 tables. For one of the tables
'tabA', I only want to publish 4 of the 6 colums, so i uncheck them in
"publish columns". None of them are keys of any kind.
Each subscriber does have a complete 'tabA' at startup and i only want
to transfer changes made to the first 4 coulmns after it is added as a
subscriber.
Now, the prolem is
1. When the snapshot is created i get a end of file reached,
terminator missing of field data incomplete for the .bcp.
2. I get an error since it tried to copy over records that are already
there.
So, is there a way to NOT copy any data for this table during the
snapshot, and only send changes for the first 4 columns in the future
after the snapshot?You may want to post this in the replication newsgroup. Hilary should be
able to answer that one real quick.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<jostein.solstad@.gmail.com> wrote in message
news:1185459329.169314.161210@.r34g2000hsd.googlegroups.com...
Hi
I have a publication with about 30 tables. For one of the tables
'tabA', I only want to publish 4 of the 6 colums, so i uncheck them in
"publish columns". None of them are keys of any kind.
Each subscriber does have a complete 'tabA' at startup and i only want
to transfer changes made to the first 4 coulmns after it is added as a
subscriber.
Now, the prolem is
1. When the snapshot is created i get a end of file reached,
terminator missing of field data incomplete for the .bcp.
2. I get an error since it tried to copy over records that are already
there.
So, is there a way to NOT copy any data for this table during the
snapshot, and only send changes for the first 4 columns in the future
after the snapshot?