adding a stored proc to a publication

Hi

I created a publication (merge anonymous via Web) - the subscriber already had the data, so there was no need to to initialize data and schema.

The problem is when it comes to adding a new stored proc to the publication. I create the proc, add it as an article to the publication and recreate the snapshot ok, but then the synchronisation fails. I presume this is because the stored proc does not exist at the subscriber. I bet it would work if I had initialized the schema and data, but due to the size of the data this is not practical.

If I manually create the proc at the subscriber then it works fine - but this defeats the purpose somewhat.....

Any ideas ?

thanks
BruceSince the subscription was created as no-sync, you will need to manually create the proc on the subscriber. You could create different publications for tables and stored procs and workaround that way. Now you can initialize the subscriber for the publication with the stored proc (and other smaller articles if you wish) and large data publication can stay intact (as no-sync)|||Thanks for the suggestion - a good workaround for now....

adding a new subscriber

Hi
I have merge replication. One client is already subscribed. I need to add
another client to the publication
The database is pretty big, so I don't want to send the whole db over the
internet.
I want to back it up and restore it, then establish the subscription.
Are the steps
a) backup database
b) recreate snapshot
c) restored db at subcriber B
d) establish subscription at subcriber B
I would assume I don't want any changes to the database made from the other
subscriber between steps a) and d)
Is this right. And I hope recreating the snapshot will not 'damage'
subscriber A
Thanks
Bruce
Thanks
Bruce
Bruce,
if you want a pull subscription, the easiest way is to create an Attachable
Subscription Database (details in BOL). If not, then you can backup and
restore and subscribe without initialization - @.sync_type = none.
HTH,
Paul Ibison
|||Bear in mind that if the database is too large the attachable subscription
will not work. The compressed databse is limited to 2 gig in size so a
large database will have problems.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Adding a new datafile

Hi:
I have SQL Server 2000; a database with three file groups located in three
different operating system files; the database size is 200GB; for
performance reasons we added an additional storage to the server and we have
an additional disk volume, what we want to do is to add a new datafile
(datafile4) and then use any command or procedure to equally split database
information (data) among the four datafiles.
How this split can be safely performed ?
Any idea or procedure ?
Thanks and best regards,
FedericoThere is no command that will automatically split the underlying table data
across all 4 data devices.
The process of moving tables to different filegroups is manual, but if
planned out correctly, can greatly improve the DBs read/write performance,
especially if the underlying data devices/files are on seperate drives.
You will need to decide how to place the various tables in your database
across the 4 files/filegroups. You may choose to place often accessed table
s
on different filegroups to improve performance when these table are accessed
simultaneously. Alternatively you may also want to place any 'archive' or
'historical' tables on a seperate data device as well. You may also choose
to place certain table's indexes on seperate filegroups then the actual
table. Which tables are moved to which filegroups and which drives those
filegroup's files reside on largely depends on the database's schema and th
e
way in which the database's tables are accessed.
Once you hae created the new data device and file group, search BOL for
"Placing Tables on Filegroups' to find out how to move existing tables
between filegroups.
Regards,
Dave
"Federico G. Babelis" wrote:

> Hi:
>
> I have SQL Server 2000; a database with three file groups located in three
> different operating system files; the database size is 200GB; for
> performance reasons we added an additional storage to the server and we ha
ve
> an additional disk volume, what we want to do is to add a new datafile
> (datafile4) and then use any command or procedure to equally split databas
e
> information (data) among the four datafiles.
>
> How this split can be safely performed ?
> Any idea or procedure ?
>
> Thanks and best regards,
> Federico
>
>
>

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.sql

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.

Adding a Log Provider

Hi

I've added a log provider to several packages, and for some reason, everytime I execute them, I end up with 2 log files. The first one is the right log file I was waiting for, and the second one is an almost empty file in my Desktop, that only contains this line

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message

I would really appreciatte if anyone could tell me what am I possibly doing wrong.

Thanks.

Does the text log provider use a connection manager with dynamic connection string, e.g. a connection string which is an expression, or uses a value which changes during package lifetime (like the milliseconds of GETDATE()), or uses a variable which itself is an expression?

If the log name is dynamic to the point where it may change within the package lifetime itself (between the validation and execution times), its quite possible to end up with two files names.

Two files may be created because the log provider (at least the stock text log provider) writes to the connection manager's path once during validation, and a second time during execution. There is a sample log provider called the HtmlLogProvider in the SQL Server samples which writes the log file name during the log provider's CloseLog method, and so will produce a single file name. Other alternates to the text log provider include the stock SQL Server log provider, or simply capturing the output of dtexec, if the package is so executed.

|||

Hi

I'm using an expression to build the connection string for the Text Log Provider. Something like:

@.[User::LogPath] + "logfilename.log"

The idea was to be able to dynamically change the path log files are going to be saved to, something I will probably never do. You know how these things are. I will check the sample log provider you mention.

Thanks a lot

|||Do you set the LogPath variable dynamically?|||

Hi

If by dynamically you mean if I change its value during the package excution, the answer is no. The variable 'LogPath' holds the path where the log file will be created, and the LogProvider connection string is set using the expression in the previous post.

Cheers.

|||

Hi jgomez,

1) Double click the SSIS Log Provider Connection Manager and check if it uses the Existing file or Creates a new File.

2) Assign the following expression for Connections String Property of SSIS Log Provider Connection Manager:

@.[User::LogPath] + Logfile.log

Thanks

Subhash Subramanyam

|||

Hi

I think I know why I had this problem. I'm invoking several packages from my SSIS, using package configurations to set the value of the LogPath variable . Everytime a new package is invoked, using the parent package variable to create the log provider, I end up with 2 differents log files as previously stated.

So I guess jaegd and jwelch were right, and this behaviour is caused by the evaluation of the log path during validation and execution times.

Thank you

Cheers

|||Logging starts before parent package variable configurations are applied. So it would start the log with the original value (saved in the package at design time), then start a new log file when the parent package variable was applied.

Added text field to database. New entries not equal to ""

Hi!
I'm accessing an MS Access database from an ASP server.
I just added a new text column (field) to one of my tables. I have not added
any data to this new field in any of the rows.
I read the field in and compare it to "" and it's coming up as false.
How do identify the empty fields?
'ASP code - Never produces "Blank"
if rsSet("region") = "" then response.write "Blank" else response.write
rsSet("region")Try
If IsNull (rsSet("region")) Then Response.Write "Null" Else Response.Write
rsSet("region")
Alan
"Noozer" <dont.spam@.me.here> wrote in message
news:IVmAc.755870$Pk3.730376@.pd7tw1no...
> Hi!
> I'm accessing an MS Access database from an ASP server.
> I just added a new text column (field) to one of my tables. I have not
added
> any data to this new field in any of the rows.
> I read the field in and compare it to "" and it's coming up as false.
> How do identify the empty fields?
> 'ASP code - Never produces "Blank"
> if rsSet("region") = "" then response.write "Blank" else response.write
> rsSet("region")
>|||Thanks!
"Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
news:ONCUTnKVEHA.2928@.tk2msftngp13.phx.gbl...
> Try
> If IsNull (rsSet("region")) Then Response.Write "Null" Else Response.Write
> rsSet("region")
> Alan
> "Noozer" <dont.spam@.me.here> wrote in message
> news:IVmAc.755870$Pk3.730376@.pd7tw1no...
> added
>|||And there are sometimes occasions when it could be a Null, or it could be a
blank. If you want to check for both at once, use:
If Len(rsSet("region") & "") = 0 Then Response.Write "Nothing" Else
Response.Write rsSet("region")
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Noozer" <dont.spam@.me.here> wrote in message
news:4MnAc.791748$oR5.380643@.pd7tw3no...
> Thanks!
> "Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
> news:ONCUTnKVEHA.2928@.tk2msftngp13.phx.gbl...
Response.Write[vbcol=seagreen]
response.write[vbcol=seagreen]
>sql

add time to datetime value and split into date and time

Hi

i have the following situation. in my database i have a datetime field (dd/mm/yy hh:mms) and i also have a field timezone.

the timezone field has values in minutes that i should add to my datetime field so i have the actual time.

afterwards i split the datetime into date and time.

the last part i can accomplish (CONVERT (varchar, datetime, 103) as DATEVALUE and CONVERT (varchar, DATETIME, 108) as TIMEVALUE).

could anybody tell me how i can add the timezone value (in minutes) to my datetime value ?

i do all the calculations in my datasource (sql).

Thanks

V.

at the end i found it myself

this is how i solved it.

to add the timezone value (in minutes) to my datetime i used following :

DATEADD(minute, TimeZone, DATETIMEVALUE) AS ACTUALDATETIME

this expression will add the timezone value (example 60) to my current date time value (12/06/2007 08:00:00) and store it in

actualdatetime value (12/06/2007 09:00:00).

from then on it's easy to extract date and time from the result.

CONVERT(varchar, (DATEADD(m,TimeZone,DATETIME)), 103) AS DATEVAL for the DATE

CONVERT(varchar, (DATEADD(m,TimeZone,DATETIME)), 108) AS TIMEVAL for the time.

i thank myself for my research .. lol

Greetings to all

Add Skuupgrade=1 ?

Hi!
I downloaded Sql server 2005 Standard directly, when I got this message. I am not used to the commandprompt, and does not know how to add Skoopgrade=1 .

Coud you please help me, may be I could add it in another way?
Thank you so much in advance!!

The SKUUPGRADE flag is used when upgrading one edition of SQL 2005 to another. Here is the documentation:

http://msdn2.microsoft.com/en-us/library/ms144259.aspx

[SKUUPGRADE]

Use the SKUUPGRADE parameter when upgrading from one edition of SQL Server 2005 to another edition of SQL Server 2005.

Important:

If you use the SKUUPGRADE parameter, Setup will remove all hotfix and service pack updates from the SQL Server instance being upgraded. Once the edition upgrade is complete, you must reapply all hotfix and service pack updates.

To perform an edition upgrade of a default instance of SQL Server 2005 (SQL Engine only) via unattended Setup, run the following command using either /qb or /qn:

start /wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine SKUUPGRADE=1 /qb

Thanks,
Sam Lester (MSFT)

|||Thank you so much!
So I run the following (I didn't use e CD)
C:\SQLEVAL\Servers\setup.exe ADDLOCAL=SQL_Engine,RS_Server INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine,RS_Server SKUUPGRADE=1 /qb

.... and it tells me my operating system does not meet service packs levels for this release.

I already downloaded .net Framework 2, ISS, SQL Server 2005 Service Pack 1...

I have windows 2000 32-bit, Windows XP Service Pack 2

I would be so grateful for some help...
|||

I'm a little confused on your OS. Can you do start -> Run and type in "winver". This will tell you your OS and SP level. Can you let me know what that shows? That way we'll be able to figure out what you need to install to get going.

Thanks,
Sam

|||

G:\servers> setup SKUUPGRADE=1

Worked fine.

Also note the important section of the quoted post.

Add Skoopgrade=1 ?

Hi!
I downloaded Sql server 2005 Standard directly, when I got this message. I am not used to the commandprompt, and does not know how to add Skoopgrade=1 .

Coud you please help me, may be I could add it in another way?
Thank you so much in advance!!

The SKUUPGRADE flag is used when upgrading one edition of SQL 2005 to another. Here is the documentation:

http://msdn2.microsoft.com/en-us/library/ms144259.aspx

[SKUUPGRADE]

Use the SKUUPGRADE parameter when upgrading from one edition of SQL Server 2005 to another edition of SQL Server 2005.

Important: If you use the SKUUPGRADE parameter, Setup will remove all hotfix and service pack updates from the SQL Server instance being upgraded. Once the edition upgrade is complete, you must reapply all hotfix and service pack updates.

To perform an edition upgrade of a default instance of SQL Server 2005 (SQL Engine only) via unattended Setup, run the following command using either /qb or /qn:

start /wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine SKUUPGRADE=1 /qb

Thanks,
Sam Lester (MSFT)

|||Thank you so much!
So I run the following (I didn't use e CD)
C:\SQLEVAL\Servers\setup.exe ADDLOCAL=SQL_Engine,RS_Server INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine,RS_Server SKUUPGRADE=1 /qb

.... and it tells me my operating system does not meet service packs levels for this release.

I already downloaded .net Framework 2, ISS, SQL Server 2005 Service Pack 1...

I have windows 2000 32-bit, Windows XP Service Pack 2

I would be so grateful for some help...
|||

I'm a little confused on your OS. Can you do start -> Run and type in "winver". This will tell you your OS and SP level. Can you let me know what that shows? That way we'll be able to figure out what you need to install to get going.

Thanks,
Sam

|||

G:\servers> setup SKUUPGRADE=1

Worked fine.

Also note the important section of the quoted post.

Add Skoopgrade=1 ?

Hi!
I downloaded Sql server 2005 Standard directly, when I got this message. I am not used to the commandprompt, and does not know how to add Skoopgrade=1 .

Coud you please help me, may be I could add it in another way?
Thank you so much in advance!!

The SKUUPGRADE flag is used when upgrading one edition of SQL 2005 to another. Here is the documentation:

http://msdn2.microsoft.com/en-us/library/ms144259.aspx

[SKUUPGRADE]

Use the SKUUPGRADE parameter when upgrading from one edition of SQL Server 2005 to another edition of SQL Server 2005.

Important:

If you use the SKUUPGRADE parameter, Setup will remove all hotfix and service pack updates from the SQL Server instance being upgraded. Once the edition upgrade is complete, you must reapply all hotfix and service pack updates.

To perform an edition upgrade of a default instance of SQL Server 2005 (SQL Engine only) via unattended Setup, run the following command using either /qb or /qn:

start /wait setup.exe ADDLOCAL=SQL_Engine INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine SKUUPGRADE=1 /qb

Thanks,
Sam Lester (MSFT)

|||Thank you so much!
So I run the following (I didn't use e CD)
C:\SQLEVAL\Servers\setup.exe ADDLOCAL=SQL_Engine,RS_Server INSTANCENAME=MSSQLSERVER UPGRADE=SQL_Engine,RS_Server SKUUPGRADE=1 /qb

.... and it tells me my operating system does not meet service packs levels for this release.

I already downloaded .net Framework 2, ISS, SQL Server 2005 Service Pack 1...

I have windows 2000 32-bit, Windows XP Service Pack 2

I would be so grateful for some help...
|||

I'm a little confused on your OS. Can you do start -> Run and type in "winver". This will tell you your OS and SP level. Can you let me know what that shows? That way we'll be able to figure out what you need to install to get going.

Thanks,
Sam

|||

G:\servers> setup SKUUPGRADE=1

Worked fine.

Also note the important section of the quoted post.

Add records in a table

Hi:
I need to add some records in a table called location(primary key:
loc_id). What I want to do is for each location in the table, I add
the same record but with a different loc_id, which can be a random
string. All the other column should contain the same value. Can anyone
give me a hint on how to do this in SQL server 2000 enterprise
manager?
thx."Hamilton sucks" <caof@.mcmaster.cawrote in message
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...

Quote:

Originally Posted by

Hi:
I need to add some records in a table called location(primary key:
loc_id). What I want to do is for each location in the table, I add
the same record but with a different loc_id, which can be a random
string. All the other column should contain the same value. Can anyone
give me a hint on how to do this in SQL server 2000 enterprise
manager?
thx.
>

A strange design. If the only key is random then how do you hope to retrieve
the information? If the rest of the data is to be identical then why bother
copying it?

DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));

INSERT INTO location (@.loc_id, col1, col2, ...)
SELECT col1, col2, ...
FROM location ;

--
David Portas|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:

Quote:

Originally Posted by

"Hamilton sucks" <caof@.mcmaster.cawrote in message
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...

Quote:

Originally Posted by

>I need to add some records in a table called location(primary key:
>loc_id). What I want to do is for each location in the table, I add
>the same record but with a different loc_id, which can be a random
>string. All the other column should contain the same value. Can anyone
>give me a hint on how to do this in SQL server 2000 enterprise
>manager?
>thx.
>>

>
A strange design. If the only key is random then how do you hope to
retrieve the information? If the rest of the data is to be identical
then why bother copying it?

Maybe he is generating test data?

Quote:

Originally Posted by

DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));
>
INSERT INTO location (@.loc_id, col1, col2, ...)
SELECT col1, col2, ...
FROM location ;

That does not look like it would work out. :-)

As I understand Hamilton, he wants each copied row to have each own
new id. Using newid() this would be:

INSERT location (loc_id, col1, col2, ...)
SELECT convert(char(36), newid()), col1, col2, ...
FROM location

Obviously, this will not work if loc_id is shorter than 36 characters.
Hamilton could use substring, but obviously the short loc_id is the
bigger the possibility for duplicates.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99A56D13F4F0DYazorman@.127.0.0.1...

Quote:

Originally Posted by

David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:

Quote:

Originally Posted by

>"Hamilton sucks" <caof@.mcmaster.cawrote in message
>news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...

Quote:

Originally Posted by

>>I need to add some records in a table called location(primary key:
>>loc_id). What I want to do is for each location in the table, I add
>>the same record but with a different loc_id, which can be a random
>>string. All the other column should contain the same value. Can anyone
>>give me a hint on how to do this in SQL server 2000 enterprise
>>manager?
>>thx.
>>>

>>
>A strange design. If the only key is random then how do you hope to
>retrieve the information? If the rest of the data is to be identical
>then why bother copying it?

>
Maybe he is generating test data?
>

Quote:

Originally Posted by

>DECLARE @.loc_id VARCHAR(36);
>SET @.loc_id = CAST(NEWID() AS VARCHAR(36));
>>
>INSERT INTO location (@.loc_id, col1, col2, ...)
> SELECT col1, col2, ...
> FROM location ;

>
That does not look like it would work out. :-)
>
As I understand Hamilton, he wants each copied row to have each own
new id. Using newid() this would be:
>
INSERT location (loc_id, col1, col2, ...)
SELECT convert(char(36), newid()), col1, col2, ...
FROM location
>
>
Obviously, this will not work if loc_id is shorter than 36 characters.
Hamilton could use substring, but obviously the short loc_id is the
bigger the possibility for duplicates.
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thanks Erland. My mistake.

--
David Portas|||On Sep 7, 6:08 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:

Quote:

Originally Posted by

"Hamilton sucks" <c...@.mcmaster.cawrote in message
>
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...
>

Quote:

Originally Posted by

Hi:
I need to add some records in a table called location(primary key:
loc_id). What I want to do is for each location in the table, I add
the same record but with a different loc_id, which can be a random
string. All the other column should contain the same value. Can anyone
give me a hint on how to do this in SQL server 2000 enterprise
manager?
thx.

>
A strange design. If the only key is random then how do you hope to retrieve
the information? If the rest of the data is to be identical then why bother
copying it?
>
DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));
>
INSERT INTO location (@.loc_id, col1, col2, ...)
SELECT col1, col2, ...
FROM location ;
>
--
David Portas

Thanks, david for your help. The reason for copying records is that I
need to change them to new records, which are exactly the same as the
old records except one column value. The loc_id is the primary key but
it's not really used for searching information. So basically I don't
care about the loc_id as long as they are unique, that 's why i want
it to be random.|||On Sep 8, 4:38 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

David Portas (REMOVE_BEFORE_REPLYING_dpor...@.acm.org) writes:

Quote:

Originally Posted by

"Hamilton sucks" <c...@.mcmaster.cawrote in message
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...

Quote:

Originally Posted by

I need to add some records in a table called location(primary key:
loc_id). What I want to do is for each location in the table, I add
the same record but with a different loc_id, which can be a random
string. All the other column should contain the same value. Can anyone
give me a hint on how to do this in SQL server 2000 enterprise
manager?
thx.

>

Quote:

Originally Posted by

A strange design. If the only key is random then how do you hope to
retrieve the information? If the rest of the data is to be identical
then why bother copying it?

>
Maybe he is generating test data?
>

Quote:

Originally Posted by

DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));

>

Quote:

Originally Posted by

INSERT INTO location (@.loc_id, col1, col2, ...)
SELECT col1, col2, ...
FROM location ;

>
That does not look like it would work out. :-)
>
As I understand Hamilton, he wants each copied row to have each own
new id. Using newid() this would be:
>
INSERT location (loc_id, col1, col2, ...)
SELECT convert(char(36), newid()), col1, col2, ...
FROM location
>
Obviously, this will not work if loc_id is shorter than 36 characters.
Hamilton could use substring, but obviously the short loc_id is the
bigger the possibility for duplicates.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Maybe I should consult about the design. The table location holds the
location info about the local schools, each of them is linked to a
test group and a set of students. If I want to add a new test group,
which contains the same set of locations except that they are linked
to the new group. Should I create a new table or add new records into
tbl_location? The same problem holds for tbl_students as well since in
the new group, all students' status must be reset.|||"Hamilton sucks" <caof@.mcmaster.cawrote in message
news:1189273920.752746.25660@.o80g2000hse.googlegro ups.com...

Quote:

Originally Posted by

>
The reason for copying records is that I
need to change them to new records, which are exactly the same as the
old records except one column value.

Then they are not exactly the same. Insert the new rows together including
the NEW column value(s). That way you can add any relevent candidate key
constraints to your table - something that wouldn't be possible if the table
had to support transitional "copies" of the old data. Example:

INSERT INTO tbl (col1, col2, col3)
SELECT col1, @.new_col2, col3
FROM tbl
WHERE ... ? ;

Quote:

Originally Posted by

Maybe I should consult about the design. The table location holds the
location info about the local schools, each of them is linked to a
test group and a set of students. If I want to add a new test group,
which contains the same set of locations except that they are linked
to the new group. Should I create a new table or add new records into
tbl_location? The same problem holds for tbl_students as well since in
the new group, all students' status must be reset.
>

This sounds very like a multi-valued or join-dependency situation. Are you
familiar with the Fourth and Fifth Normal Forms? If not then look up some
examples. You should satisfy yourself about the design based on your own
understanding of the business rules. It's notoriously difficult to give
detailed design advice in an online discussion. (Easy to spot potential
problems but hard to suggest the right solutions).

--
David Portas

add in query

hi

i have existing query ..now i want to add one more join in this with some condition in where clause.

my need is if emloyee's not present for last 4 week .so his working hour is zero..so i want to list those employeee who not worked for last 4 weeks.

see the bold one.

SELECT
HR.ClientID,
CD.Email,
CD.Surname,
CD.FirstNames,
CD.HomeTel,
CD.WorkTel,
CD.MobileTel,
CD.PreferredName,
HR.EmployeeNumber,
HR.EmploymentType,
HR.Status,
HR.Jobtitle,
HR.LocationID,
HR.DivisionID,
HR.IsPayrollEmployee,
HR.StartDate,

CASE
WHEN PIP.ClientID IS NULL THEN 0
ELSE 1
END AS 'IsPrevented',
HR.PayrollCompanyID,
HR.ProbationEndDate,
IsNull(HR.ProbationStatus, 0) AS 'ProbationStatus',
CD.UserTypeID,
CLT.Location,
CLT.Division,
IsNull((SELECT COUNT(*)
FROM t_TerminationDetails AS TD INNER JOIN fn_PayPeriodsByTaxYear(@.TaxYear) AS PD_01 ON (PD_01.Frequency = 'M' AND TD.TerminationDate >= PD_01.StartDate AND PD_01.PayPeriod = 1)
INNER JOIN fn_PayPeriodsByTaxYear(@.TaxYear) AS PD_02 ON (PD_02.Frequency = 'M' AND TD.TerminationDate <= PD_02.StartDate AND PD_02.PayPeriod = 12)
WHERE TD.ClientID = HR.ClientID), 0) AS 'HasBeenTerminated',
IsNull(TD.Reemploy, 0) AS 'Reemploy',

NOP.NumberOfPayruns,
CASE
WHEN CAR.ClientID IS NULL THEN 0
ELSE 1
END AS 'IsDriver',
HR.HasBeenRehired,
CD.DateCreated,
ISNULL(BD.AccountNumber, 0) AS 'AccountNumber',

(SELECT TOP 1 GD.CompanyID FROM t_BenefitCompanyEmployee BCE INNER JOIN t_GroupDetails GD ON BCE.CompanyID = GD.CompanyID WHERE (BCE.ClientID = CD.ClientID)) as 'PAYECompany',
JT.Name As 'JobTitleName',
JT.IsContractor As 'IsContractor',
CASE WHEN (IsNull(BD.PaymentMethod,0) = 1 AND Len(IsNull(BD.SortCode,0)) = 6) OR IsNull(BD.PaymentMethod,0) <> 1 THEN
1
ELSE
0
END AS 'ValidSortCode'

FROM
@.Client_Table AS CLT INNER JOIN (t_HRDetails AS HR INNER JOIN t_ClientDetails AS CD ON (HR.ClientID = CD.ClientID)) ON (CLT.ClientID = HR.ClientID)
LEFT JOIN #EmployeesByCompany AS EBC ON (CLT.ClientID = EBC.ClientID)
LEFT JOIN t_PayrollIsPrevented AS PIP ON (CLT.ClientID = PIP.ClientID)

LEFT JOIN #NumberOFPayruns_Table AS NOP ON (CLT.ClientID = NOP.ClientID)
LEFT JOIN t_CAR_Driver AS CAR ON (HR.ClientID = CAR.ClientID)
LEFT JOIN (SELECT ClientID, AccountNumber, SortCode, PaymentMethod FROM t_BankDetails WHERE Status = 1 AND AccountNumber <> '') AS BD ON BD.ClientID = HR.ClientID
LEFT JOIN t_JobTitle JT ON HR.Jobtitle = JT.JobTitleID
LEFT JOIN t_TerminationDetails TD ON HR.ClientID = TD.CLientID
Left join t_shiftmembers2 SM on HR.ClientID = SM.CLientID
WHERE

(@.Surname = '' OR CD.Surname LIKE @.Surname + '%' )
AND
(@.Firstname = '' OR CD.Firstnames LIKE @.Firstname + '%')
AND
(@.Preferredname = '' OR CD.PreferredName LIKE @.Preferredname + '%' )
AND
(@.AccountNumber = '' OR AccountNumber LIKE @.AccountNumber + '%' )
AND
(@.EmployeeNumber = '' OR HR.EmployeeNumber LIKE @.EmployeeNumber + '%' )
AND
(@.JobTitleID = 0 OR HR.JobTitle = @.JobTitleID )
AND (@.NOTWORKED = 0 OR (SM.shiftdate < dateadd(week,-@.NOTWORKED,getdate())AND sum(SM.totalacthrs)= 0) )
AND
(@.CompanyID=0 OR EBC.ClientID IS NOT NULL )
ORDER BY
CD.Surname, CD.FirstNames

i tried ..but not sure this is correct way.

Change your query as follow as ,

Left join t_shiftmembers2 SM on

HR.ClientID = SM.CLientID

AND (@.NOTWORKED = 0

OR (SM.shiftdate < dateadd(week,-@.NOTWORKED,getdate())

AND sum(SM.totalacthrs)= 0

)

)

WHERE

(@.Surname = '' OR CD.Surname LIKE @.Surname + '%' )

AND

(@.Firstname = '' OR CD.Firstnames LIKE @.Firstname + '%')

AND

(@.Preferredname = '' OR CD.PreferredName LIKE @.Preferredname + '%' )

AND

(@.AccountNumber = '' OR AccountNumber LIKE @.AccountNumber + '%' )

AND

(@.EmployeeNumber = '' OR HR.EmployeeNumber LIKE @.EmployeeNumber + '%' )

AND

(@.JobTitleID = 0 OR HR.JobTitle = @.JobTitleID )

AND

(@.CompanyID=0 OR EBC.ClientID IS NOT NULL )

ORDER BY

CD.Surname, CD.FirstNames

|||

First, I see in your query "... IsNull((SELECT COUNT(*) FROM t_TerminationDetails AS TD INNER JOIN ..." but any time select count(*) from any_table return at least 0 if the table has no rows, and you user isnull function ...

Beeing as so complicate selct i think you break it using views to simplify things.

I see @.Client_Table and i understand it is a temporary table variable but in

" (@.Surname = '' OR CD.Surname LIKE @.Surname + '%' )
AND
(@.Firstname = '' OR CD.Firstnames LIKE @.Firstname + '%')
AND
(@.Preferredname = '' OR CD.PreferredName LIKE @.Preferredname + '%' )
AND
(@.AccountNumber = '' OR AccountNumber LIKE @.AccountNumber + '%' )
AND
(@.EmployeeNumber = '' OR HR.EmployeeNumber LIKE @.EmployeeNumber + '%' )
AND
(@.JobTitleID = 0 OR HR.JobTitle = @.JobTitleID )
AND (@.NOTWORKED = 0 OR (SM.shiftdate < dateadd(week,-@.NOTWORKED,getdate())AND sum(SM.totalacthrs)= 0) )
AND
(@.CompanyID=0 OR EBC.ClientID IS NOT NULL )
ORDER BY
CD.Surname, CD.FirstNames"

@.NOTWORKED and etc are parameters of a stored procedure, isn't it ?

|||

main thanx for reply..

by seeing your query ..i can add all my condition in left join itself..no need to add in where clause...my sum(actualhours) will not give any error.

|||

The point is when you use the outer join, if there is a filter (where) condition then move them from the where clause to corresponding on clause.

|||

mani i run your query i m getting followin g error

An aggregate cannot appear in an ON clause unless it is in a subquery contained in a HAVING clause or select list, and the column being aggregated is an outer reference.

|||

Ohh yes…How I missed that.. may be the following query might work..

Code Snippet

Left join (Select ClientID,isnull(sum(SM.totalacthrs),0) totalhrs from t_shiftmembers2

Where SM.shiftdate < dateadd(week,-@.NOTWORKED,getdate())

Group By ClientID) as SM on

HR.ClientID = SM.CLientID

Where

(@.Surname = '' OR CD.Surname LIKE @.Surname + '%' )

AND

(@.Firstname = '' OR CD.Firstnames LIKE @.Firstname + '%')

AND

(@.Preferredname = '' OR CD.PreferredName LIKE @.Preferredname + '%' )

AND

(@.AccountNumber = '' OR AccountNumber LIKE @.AccountNumber + '%' )

AND

(@.EmployeeNumber = '' OR HR.EmployeeNumber LIKE @.EmployeeNumber + '%' )

AND

(@.JobTitleID = 0 OR HR.JobTitle = @.JobTitleID )

AND

(@.CompanyID=0 OR EBC.ClientID IS NOT NULL )

AND

(@.NOTWORKED = 0 OR (totalhrs = 0))

|||

thanx for reply

mani can u tell me dateadd(week,-4,getdate())

-4 week means 28 days

or some thing else?

|||in this query i think u r not checking this condition sum(totalacthrs) = 0|||

sorry my mistake i didn't see your whole code..

i want to convert getdate like this 2007-09-26 00:00:00:00

mean i want min ,sec all in zero

Add Identity Increment per user

Hi
I have a Jobs Table: Job_ID,User_ID,Job_Info,...
Job_ID is the Primary Key.
I want to add [Job_ID_PerUser] so I wiil get:
Job_ID Job_ID_PerUser User_ID
1 1 A
2 1 B
3 1 C
4 2 A
5 3 A
6 2 C
Thanks

1) Set the key as primary key...

2) In the coloum properties there in Identity Specification

3) Set it to Yes

4) And set Increment Seed As 1

|||

I have a Primary Key with the Increment Seed=1 named [Job_ID]

I want another ID for a job [Job_ID_PerUser] that will start the count from 1 for every new user

and will increment by 1 every time this new user will add a job

|||

Assuming that you are using SQL Server, you can create the a SQL Server function the will calculate the next value of for the customer and add that as the column for the server.

CREATE FUNCTION dbo.GetCustomerMax (@.userIdint)RETURNSintASBEGINRETURN SELECT MAX(Job_ID_PerUser) + 1WHERE User_Id= @.userIdEND

In SQL Server Management Studio, you modify the table, add the column of type int.

Then create a stored procedure for the insertion of the record and call the GetCustomerMax function.

|||

Thanks it works greate.

do i need to put all of that in one transaction?

Add a view in a diagram

Hi
I would like to import views in SQL Server's Diagram. I could only import tables. Thank you for your help
SQL Servers database diagram tool only supports diagrams
using tables. Some of the other data modeling tools support
views in diagrams. If you have Visio, you can reverse
engineer your database and include views in the diagrams.
-Sue
On Wed, 14 Apr 2004 07:36:02 -0700, "SLEMARIE"
<anonymous@.discussions.microsoft.com> wrote:

>Hi
>I would like to import views in SQL Server's Diagram. I could only import tables. Thank you for your help

Add a view in a diagram

Hi
I would like to import views in SQL Server's Diagram. I could only import ta
bles. Thank you for your helpSQL Servers database diagram tool only supports diagrams
using tables. Some of the other data modeling tools support
views in diagrams. If you have Visio, you can reverse
engineer your database and include views in the diagrams.
-Sue
On Wed, 14 Apr 2004 07:36:02 -0700, "SLEMARIE"
<anonymous@.discussions.microsoft.com> wrote:

>Hi
>I would like to import views in SQL Server's Diagram. I could only import tables. T
hank you for your help