help in bulk load - first time user

Hi all,
I've a xml file like this:
<ROOT>
<customerlist>
<Customers sequence="1">
<details>
<PersonID>1</PersonID>
</details>
<name>
<LastName>Vinod</LastName>
<FirstName>Kumar</FirstName>
</name>
</Customers>
<Customers sequence="2">
<details>
<PersonID>2</PersonID>
</details>
<name>
<LastName>Saravana</LastName>
<FirstName>Kumar</FirstName>
</name>
</Customers>
</customerlist>
</ROOT>
how should I write an xsd file to store the data in the following sql server table (Person) with columns:
personId - primary key, LastName and FirstName.
Thanks.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:exDw0UcdEHA.320@.TK2MSFTNGP10.phx.gbl...
[snip]
> how should I write an xsd file to store the data in the following sql
> server table (Person) with columns:
I would start with the examples given in the SQLXML documentation. Look at
how the XSD is mapped to the XML. That is the best way to learn.
Bryant

Help improving this script

Hello and thank you for taking the time to read this.
Can anyone provide any recomendations for improving this script. It is
fairly straight forward, and it works. I am looking for tips the experts
would use.
Also there are two things I would like to do that this script currently does
not
1. Print the print statement after each section is finished
2. The last Drop Table action. I do not want to drop the table unless I know
the previous Insert Into was successfull.
thank you very much
USE ADV_Goldmine
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CONTACTDel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CONTACTDel]
GO
PRINT 'Create Table CONTACTDel to replace CONTACT1DEL'
CREATE TABLE [dbo].[CONTACTDel] (
[ACCOUNTNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RECTYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONTSUPREF] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMPANY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CONTACT] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTNAME] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEPARTMENT] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TITLE] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SECR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONE1] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONE2] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONE3] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAX] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT1] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT2] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT3] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EXT4] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS1] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS3] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COUNTRY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DEAR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY4] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[KEY5] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATUS] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MERGECODES] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATEBY] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATEON] [datetime] NULL ,
[CREATEAT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OWNER] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTUSER] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTDATE] [datetime] NULL ,
[LASTTIME] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RECID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE INDEX [CONTACCT] ON [dbo].[CONTACTDel]([ACCOUNTNO], [RECID]) WITH
FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [CREC] ON [dbo].[CONTACTDel]([RECID]) WITH FILLFACTOR = 90
ON [PRIMARY]
GO
PRINT 'Drop if EXISTS and create Trigger Supp_contact_delete on CONTSUPP'
IF EXISTS (SELECT name from sysobjects
where name ='Supp_contact_delete' AND type = 'TR')
DROP TRIGGER Supp_contact_delete
GO
CREATE TRIGGER Supp_contact_delete ON Contsupp
FOR DELETE
AS
SET NOCOUNT ON
INSERT INTO ContactDEL
(accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
address1, address2, address3, city, state, zip, country, mergecodes,
lastdate, lasttime, recid, company,key5)
SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
deleted.fax, deleted.address1,
deleted.address2, deleted.address3, deleted.city, deleted.state,
deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
deleted.lasttime, deleted.recid, contact1.company, contact1.key5
FROM deleted
INNER join contact1(NOLOCK)
ON contact1.accountno = deleted.accountno
WHERE deleted.rectype='C' AND deleted.accountno > ''
INSERT INTO ContactDEL
(accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
address1, address2, address3, city, state, zip, country, mergecodes,
lastdate, lasttime, recid, company,key5)
SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
deleted.fax, deleted.address1,
deleted.address2, deleted.address3, deleted.city, deleted.state,
deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
deleted.lasttime, deleted.recid, Contactdel.company, Contactdel.key5
FROM deleted
INNER join contactdel(NOLOCK) ON contactdel.accountno = deleted.accountno
left join contact1(NOLOCK) /*only the ones not in contact1*/
ON contact1.accountno = deleted.accountno
WHERE contactdel.rectype IS NULL and deleted.rectype='C'
AND contact1.accountno IS NULL /*not in Contact1*/
GO
PRINT 'Drop old Trigger on CONTACT1 DeleteTrigger'
IF EXISTS (SELECT name from sysobjects
where name ='DeleteTrigger' AND type = 'TR')
DROP TRIGGER DeleteTrigger
GO
PRINT 'Create Trigger C1Delete on CONTACT1'
IF EXISTS (SELECT name from sysobjects
where name ='C1Delete' AND type = 'TR')
DROP TRIGGER C1Delete
GO
CREATE TRIGGER C1Delete ON Contact1
FOR DELETE
AS
insert into ContactDEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
from deleted
GO
PRINT 'Move data from CONTACT1DEL to CONTACTDEL'
INSERT INTO ContactDel
(ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR, PHONE1,
PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
select [CONTACT1Del].[ACCOUNTNO],[CONTACT1Del].[COMPANY],
[CONTACT1Del].[CONTACT], [CONTACT1Del].[LASTNAME],
[CONTACT1Del].[DEPARTMENT], [CONTACT1Del].[TITLE], [CONTACT1Del].[SECR],
[CONTACT1Del].[PHONE1], [CONTACT1Del].[PHONE2], [CONTACT1Del].[PHONE3],
[CONTACT1Del].[FAX],
[CONTACT1Del].[EXT1], [CONTACT1Del].[EXT2], [CONTACT1Del].[EXT3],
[CONTACT1Del].[EXT4],
[CONTACT1Del].[ADDRESS1], [CONTACT1Del].[ADDRESS2], [CONTACT1Del].[ADDRESS3]
,
[CONTACT1Del].[CITY], [CONTACT1Del].[STATE], [CONTACT1Del].[ZIP],
[CONTACT1Del].[COUNTRY],
[CONTACT1Del].[DEAR], [CONTACT1Del].[SOURCE], [CONTACT1Del].[KEY1],
[CONTACT1Del].[KEY2],
[CONTACT1Del].[KEY3], [CONTACT1Del].[KEY4], [CONTACT1Del].[KEY5],
[CONTACT1Del].[STATUS],
[CONTACT1Del].[MERGECODES], [CONTACT1Del].[CREATEBY],
[CONTACT1Del].[CREATEON],
[CONTACT1Del].[CREATEAT], [CONTACT1Del].[OWNER], [CONTACT1Del].[LASTUSER],
[CONTACT1Del].[LASTDATE], [CONTACT1Del].[LASTTIME], [CONTACT1Del].[RECID]
from [CONTACT1Del]
where [CONTACT1Del].[ACCOUNTNO]>''
GO
PRINT 'Drop Contact1del table'
DROP TABLE Contact1delTry:

> 1. Print the print statement after each section is finished
use raiserror instead print.
Example:
raiserror('put the msg here.', 10, 1) with nowait
go

> 2. The last Drop Table action. I do not want to drop the table unless I kn
ow
> the previous Insert Into was successfull.
declare @.error int, @.rowcnt int
INSERT INTO ContactDel
...
select @.error = @.@.error, @.rowcnt = @.@.rowcount
if @.rowcnt > 1 and @.error = 0
begin
PRINT 'Drop Contact1del table'
DROP TABLE Contact1del
end
go
AMB
"jenks" wrote:

> Hello and thank you for taking the time to read this.
> Can anyone provide any recomendations for improving this script. It is
> fairly straight forward, and it works. I am looking for tips the experts
> would use.
> Also there are two things I would like to do that this script currently do
es
> not
> 1. Print the print statement after each section is finished
> 2. The last Drop Table action. I do not want to drop the table unless I kn
ow
> the previous Insert Into was successfull.
> thank you very much
> USE ADV_Goldmine
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[CONTACTDel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[CONTACTDel]
> GO
> PRINT 'Create Table CONTACTDel to replace CONTACT1DEL'
> CREATE TABLE [dbo].[CONTACTDel] (
> [ACCOUNTNO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [RECTYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CONTSUPREF] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COMPANY] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CONTACT] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LASTNAME] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DEPARTMENT] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TITLE] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SECR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PHONE1] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PHONE2] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [PHONE3] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FAX] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EXT1] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EXT2] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EXT3] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [EXT4] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS1] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ADDRESS3] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CITY] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [STATE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [COUNTRY] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DEAR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [SOURCE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [KEY1] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [KEY2] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [KEY3] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [KEY4] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [KEY5] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [STATUS] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MERGECODES] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CREATEBY] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [CREATEON] [datetime] NULL ,
> [CREATEAT] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [OWNER] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LASTUSER] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [LASTDATE] [datetime] NULL ,
> [LASTTIME] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RECID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE INDEX [CONTACCT] ON [dbo].[CONTACTDel]([ACCOUNTNO], [RECID]) WITH
> FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [CREC] ON [dbo].[CONTACTDel]([RECID]) WITH FILLFACTOR = 90
> ON [PRIMARY]
> GO
>
> PRINT 'Drop if EXISTS and create Trigger Supp_contact_delete on CONTSUPP'
> IF EXISTS (SELECT name from sysobjects
> where name ='Supp_contact_delete' AND type = 'TR')
> DROP TRIGGER Supp_contact_delete
> GO
> CREATE TRIGGER Supp_contact_delete ON Contsupp
> FOR DELETE
> AS
> SET NOCOUNT ON
> INSERT INTO ContactDEL
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
> address1, address2, address3, city, state, zip, country, mergecodes,
> lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
> deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, contact1.company, contact1.key5
> FROM deleted
> INNER join contact1(NOLOCK)
> ON contact1.accountno = deleted.accountno
> WHERE deleted.rectype='C' AND deleted.accountno > ''
> INSERT INTO ContactDEL
> (accountno, rectype, contact, title, contsupref, dear, phone1, ext1, fax,
> address1, address2, address3, city, state, zip, country, mergecodes,
> lastdate, lasttime, recid, company,key5)
> SELECT deleted.accountno, deleted.rectype, deleted.contact, deleted.title,
> deleted.contsupref, deleted.dear, deleted.phone , deleted.ext,
> deleted.fax, deleted.address1,
> deleted.address2, deleted.address3, deleted.city, deleted.state,
> deleted.zip, deleted.country, deleted.mergecodes, GETDATE(),
> deleted.lasttime, deleted.recid, Contactdel.company, Contactdel.key5
> FROM deleted
> INNER join contactdel(NOLOCK) ON contactdel.accountno = deleted.accountno
> left join contact1(NOLOCK) /*only the ones not in contact1*/
> ON contact1.accountno = deleted.accountno
> WHERE contactdel.rectype IS NULL and deleted.rectype='C'
> AND contact1.accountno IS NULL /*not in Contact1*/
>
> GO
> PRINT 'Drop old Trigger on CONTACT1 DeleteTrigger'
> IF EXISTS (SELECT name from sysobjects
> where name ='DeleteTrigger' AND type = 'TR')
> DROP TRIGGER DeleteTrigger
> GO
> PRINT 'Create Trigger C1Delete on CONTACT1'
> IF EXISTS (SELECT name from sysobjects
> where name ='C1Delete' AND type = 'TR')
> DROP TRIGGER C1Delete
> GO
> CREATE TRIGGER C1Delete ON Contact1
> FOR DELETE
> AS
>
> insert into ContactDEL (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT,
> TITLE, SECR, PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1
,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR,
> PHONE1, PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, GETDATE(), LASTTIME, RECID
> from deleted
> GO
> PRINT 'Move data from CONTACT1DEL to CONTACTDEL'
> INSERT INTO ContactDel
> (ACCOUNTNO, COMPANY, CONTACT, LASTNAME, DEPARTMENT, TITLE, SECR, PHONE1,
> PHONE2, PHONE3, FAX, EXT1, EXT2, EXT3, EXT4, ADDRESS1,
> ADDRESS2, ADDRESS3, CITY, STATE, ZIP, COUNTRY, DEAR, SOURCE, KEY1, KEY2,
> KEY3, KEY4, KEY5, STATUS, MERGECODES, CREATEBY,
> CREATEON, CREATEAT, OWNER, LASTUSER, LASTDATE, LASTTIME, RECID)
> select [CONTACT1Del].[ACCOUNTNO],[CONTACT1Del].[COMPANY],
> [CONTACT1Del].[CONTACT], [CONTACT1Del].[LASTNAME],
> [CONTACT1Del].[DEPARTMENT], [CONTACT1Del].[TITLE], [CONTACT1Del].[SECR],
> [CONTACT1Del].[PHONE1], [CONTACT1Del].[PHONE2], [CONTACT1Del].[PHONE3],
> [CONTACT1Del].[FAX],
> [CONTACT1Del].[EXT1], [CONTACT1Del].[EXT2], [CONTACT1Del].[EXT3],
> [CONTACT1Del].[EXT4],
> [CONTACT1Del].[ADDRESS1], [CONTACT1Del].[ADDRESS2], [CONTACT1Del].[ADDRES
S3],
> [CONTACT1Del].[CITY], [CONTACT1Del].[STATE], [CONTACT1Del].[ZIP],
> [CONTACT1Del].[COUNTRY],
> [CONTACT1Del].[DEAR], [CONTACT1Del].[SOURCE], [CONTACT1Del].[KEY1],
> [CONTACT1Del].[KEY2],
> [CONTACT1Del].[KEY3], [CONTACT1Del].[KEY4], [CONTACT1Del].[KEY5],
> [CONTACT1Del].[STATUS],
> [CONTACT1Del].[MERGECODES], [CONTACT1Del].[CREATEBY],
> [CONTACT1Del].[CREATEON],
> [CONTACT1Del].[CREATEAT], [CONTACT1Del].[OWNER], [CONTACT1Del].[LASTUSER],
> [CONTACT1Del].[LASTDATE], [CONTACT1Del].[LASTTIME], [CONTACT1Del].[RECID]
> from [CONTACT1Del]
> where [CONTACT1Del].[ACCOUNTNO]>''
> GO
> PRINT 'Drop Contact1del table'
> DROP TABLE Contact1del
>
>

Help for Performing Updates Across Multiple Servers

Hi,
We have two DataBases Residing on two different Servers. We would like to
set up real time uodates between these two servers.
We have an Inventory System on server A and a Tracking System on Server B.
Now an Transaction in Server B should Update a Certain Table or Tables on
Server A. and vice versa.
I would like people to help me choose the Best soulution for this to work
successfully.
I was leaning towards Linked Servers anfd The making use of triggers. Is
this a Good Option.
But waht happens if the Trigger Fails how do we get the data Across to the
Servers.
Thanks for your help in advance.
Thanks,
Jothi
Don't fire triggers over a network. The latency added by doing this tends to
make such a solution unworkable. Plus if one of the servers go down the
trigger and sometimes the entire server will hang for up to 20 seconds
before failing.
Hilary Cotter
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
"Jothi" <Jothi@.discussions.microsoft.com> wrote in message
news:7F1F3EA8-2719-4A0B-AC57-1F6AB44183CF@.microsoft.com...
> Hi,
> We have two DataBases Residing on two different Servers. We would like to
> set up real time uodates between these two servers.
> We have an Inventory System on server A and a Tracking System on Server B.
> Now an Transaction in Server B should Update a Certain Table or Tables on
> Server A. and vice versa.
> I would like people to help me choose the Best soulution for this to work
> successfully.
> I was leaning towards Linked Servers anfd The making use of triggers. Is
> this a Good Option.
> But waht happens if the Trigger Fails how do we get the data Across to the
> Servers.
> Thanks for your help in advance.
> Thanks,
> Jothi
>
>
|||BTW - your best bet is using distributed transactions.
Hilary Cotter
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
"Jothi" <Jothi@.discussions.microsoft.com> wrote in message
news:7F1F3EA8-2719-4A0B-AC57-1F6AB44183CF@.microsoft.com...
> Hi,
> We have two DataBases Residing on two different Servers. We would like to
> set up real time uodates between these two servers.
> We have an Inventory System on server A and a Tracking System on Server B.
> Now an Transaction in Server B should Update a Certain Table or Tables on
> Server A. and vice versa.
> I would like people to help me choose the Best soulution for this to work
> successfully.
> I was leaning towards Linked Servers anfd The making use of triggers. Is
> this a Good Option.
> But waht happens if the Trigger Fails how do we get the data Across to the
> Servers.
> Thanks for your help in advance.
> Thanks,
> Jothi
>
>
sql

Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?
Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>

Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and
I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>

Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>

Help backing up and restoring database

Hi,

I have been using the backup feature of SQL Express for some time and I thought it would be good to test a restore in case the worst happend. I moved my bak files to a usb memory stick and copied them to a machine and installed SQL Express so its empty no database's etc. So I right click the databae folder and choose restore from device and point to the db's but I get errors restoring them. What am I doing wrong? Is their any guides that give steps to backup and restore database's ? Any help would be great - I can of course provide more information need be.

Thanks,

Adam.

Hi,

Here is a good reference article for backup http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx

BTW you will have to post the error message to get a good answer, unless we cannot provide you the good suggesion.

Hemantgiri S. Goswami

|||

hi Adam,

2326ac wrote:

Hi,

I have been using the backup feature of SQL Express for some time and I thought it would be good to test a restore in case the worst happend. I moved my bak files to a usb memory stick and copied them to a machine and installed SQL Express so its empty no database's etc. So I right click the databae folder and choose restore from device and point to the db's but I get errors restoring them. What am I doing wrong? Is their any guides that give steps to backup and restore database's ? Any help would be great - I can of course provide more information need be.

Thanks,

Adam.

as Hemantgiri already pointed out, it's difficult to help torubleshooting without the actual exception message... but I can guess the Windows account running the SQLExpress intance has not been granted adeguated NTFS permissions on the backup source, the actual folder (path) containing the backup set..

regards

Help :Custom Date Time Format

Dear All,
How to return current time in format as below:
YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
Thanks
Robert LieBest thing would be to write a UDF and put in some standardformat to get the
special format you need.
HTH, Jens Suessmeyer.
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie|||Hi,
Use this format
select Convert(Varchar,getdate(),120)
Hope this will help
Herbert
"Robert Lie" wrote:

> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie
>|||To add to the other responses, consider performing data formatting in your
presentation layer rather than Transact-SQL. SQL Server is optimized for
efficient data access and application code/reporting tools generally provide
more robust and efficient formatting capabilities.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie

Help :Custom Date Time Format

Dear All,
How to return current time in format as below:
YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
Thanks
Robert Lie
Best thing would be to write a UDF and put in some standardformat to get the
special format you need.
HTH, Jens Suessmeyer.
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie
|||Hi,
Use this format
select Convert(Varchar,getdate(),120)
Hope this will help
Herbert
"Robert Lie" wrote:

> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie
>
|||To add to the other responses, consider performing data formatting in your
presentation layer rather than Transact-SQL. SQL Server is optimized for
efficient data access and application code/reporting tools generally provide
more robust and efficient formatting capabilities.
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie

Help :Custom Date Time Format

Dear All,
How to return current time in format as below:
YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
Thanks
Robert LieBest thing would be to write a UDF and put in some standardformat to get the
special format you need.
HTH, Jens Suessmeyer.
"Robert Lie" <robert.lie24@.gmail.com> schrieb im Newsbeitrag
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie|||Hi,
Use this format
select Convert(Varchar,getdate(),120)
Hope this will help
Herbert
"Robert Lie" wrote:
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie
>|||To add to the other responses, consider performing data formatting in your
presentation layer rather than Transact-SQL. SQL Server is optimized for
efficient data access and application code/reporting tools generally provide
more robust and efficient formatting capabilities.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Robert Lie" <robert.lie24@.gmail.com> wrote in message
news:O9BRSXJeFHA.3864@.TK2MSFTNGP10.phx.gbl...
> Dear All,
> How to return current time in format as below:
> YYYYMMDDHHMM -> Year-Month-Date-Hour-Minute
> Thanks
> Robert Lie

Help (error 17883, under sql 2000 sp4 US)

Hello,
I have this error:
http://support.microsoft.com/kb/909734/en-us
But microsoft france is close at this time and support say that they send me
a patch tomorrow, ...
If you have this magic patch you can send me (zipped) at:
ericg@.catalina-mkt.fr
Thanks for you help,
Best regards
EricOnly option is to have the server patched with the Hotfix by MS.
"Eric" wrote:

> Hello,
> I have this error:
> http://support.microsoft.com/kb/909734/en-us
> But microsoft france is close at this time and support say that they send
me
> a patch tomorrow, ...
> If you have this magic patch you can send me (zipped) at:
> ericg@.catalina-mkt.fr
> Thanks for you help,
> Best regards
> Eric

Help (error 17883, under sql 2000 sp4 US)

Hello,
I have this error:
http://support.microsoft.com/kb/909734/en-us
But microsoft france is close at this time and support say that they send me
a patch tomorrow, ... :(
If you have this magic patch you can send me (zipped) at:
ericg@.catalina-mkt.fr
Thanks for you help,
Best regards
EricOnly option is to have the server patched with the Hotfix by MS.
"Eric" wrote:
> Hello,
> I have this error:
> http://support.microsoft.com/kb/909734/en-us
> But microsoft france is close at this time and support say that they send me
> a patch tomorrow, ... :(
> If you have this magic patch you can send me (zipped) at:
> ericg@.catalina-mkt.fr
> Thanks for you help,
> Best regards
> Eric

Help ! Procedure to delete files from operating system

Hi all,

Can anyone help me with a script which would delete files or
move them to a different folder at some scheduled time..!
Please....!!!

Thanks in advance...Raj (rjdave@.indiatimes.com) writes:
> Can anyone help me with a script which would delete files or
> move them to a different folder at some scheduled time..!

Which files? MDF, LDF and NDF files? Or just any random selected files?

In any case, you could use SQL Server Agent to create a CmdExec job for
the task. You can access SQL Server Agent from Enterprise Manager.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

The check for the date in your cursor declaration is probably the cause of
the problem!

Declare mycursor cursor for
select name from #Filenames
where convert(datetime,left(name,10)) <= getdate()-@.duration

Although you can add a style to the convert function, this is not guaranteed
to give the correct results.

From BOL:
style
Is the style of date format used to convert datetime or smalldatetime data
to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data
types), or the string format when converting float, real, money, or
smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or
nvarchar data types).

i.e no mention of use of style when converting from character to datetime

A less risky option would be to convert both to strings and then re-arrange
them into an alphabetically comparable format such as YYYYMMDD. Overall I
think the DTS/Filesystemobject may well be the faster and safer solution.

John

"Raj" <rjdave@.indiatimes.com> wrote in message
news:c11c051e.0307120602.4fe8b9c7@.posting.google.c om...
> Erland Sommarskog <sommar@.algonet.se> wrote in message
news:<Xns93B6B78068CAYazorman@.127.0.0.1>...
> > Raj (rjdave@.indiatimes.com) writes:
> > > Can anyone help me with a script which would delete files or
> > > move them to a different folder at some scheduled time..!
> > Which files? MDF, LDF and NDF files? Or just any random selected files?
> > In any case, you could use SQL Server Agent to create a CmdExec job for
> > the task. You can access SQL Server Agent from Enterprise Manager.
> Thanks ...I didn't know about it ... It was great help. But still it
> didn't work . I wanted to run a job and the script is below . I am not
> able to run this script using sql server agent. Can anyone suggest
> whats wrong ...and whats the trick i am missing?
> Create procedure USP_DelOldFiles @.path varchar(25),@.duration int
> as
> --Objective: To delete files older than certain period from a folder
> --Usage example:
> --Exec USP_DelOldFiles 'c:\test',30 -- which deletes files older than
> todaydate-30
> --Created by :MAK
> --Created date: Jan 7,2003
> --OS: windows 2000
> declare @.myquery varchar(1000)
> declare @.query varchar(1000)
> declare @.name varchar(100)
> set @.myquery = "exec master.dbo.xp_cmdshell 'dir "+
> ltrim(rtrim(@.path)) + "\*.* /a/od'"
> print @.query
> create table #Filenames (id int identity(1,1) ,name varchar(100))
> insert #Filenames(name)
> exec (@.Myquery)
> delete from #Filenames where substring(name,3,1) <> '/' or name is
> null or
> substring(name,25,1) ='<'
> Declare mycursor cursor for
> select name from #Filenames where
> convert(datetime,left(name,10)) <= getdate()-@.duration
> open mycursor
> fetch next from mycursor into @.name
> while (@.@.fetch_status =0! )
> begin
> set @.query = 'exec master.dbo.xp_cmdshell "del '+@.path+'\'+
> ltrim(rtrim(substring(@.name,40,59)))+'"'
> --print @.query
> exec (@.query)
> fetch next from mycursor into @.name
> end
> close mycursor
> deallocate mycursor
> drop table #Filenames|||Raj (rjdave@.indiatimes.com) writes:
> Thanks ...I didn't know about it ... It was great help. But still it
> didn't work . I wanted to run a job and the script is below . I am not
> able to run this script using sql server agent. Can anyone suggest
> whats wrong ...and whats the trick i am missing?

Hey, what about *you* tell us what is wrong? I mean, you say that you
are not able to run it, but rather let us guess what you mean with that,
why not specify what you. Do you get an error message? Do the script
end without an files being deleted? Did the script work from Query
Analyzer?

What I can see at a glance is:

> while (@.@.fetch_status =0! )

Syntax error.

> set @.query = 'exec master.dbo.xp_cmdshell "del '+@.path+'\'+
> ltrim(rtrim(substring(@.name,40,59)))+'"'

Should have a /F to force deletion of read-only files.

Really why you make the entire call to xp_cmdshell in dynamic SQL, I
don't know, neither I have cared to check that you get the right statement.
It should be sufficient to have only the DOS command in a variable.

Then again, why SQL at all? Isn't this a symptom of that when all you
have is hammer, everything looks like nails to you. Personally I would
implement this in Perl, but if you VB script or C++, these languages
work equally well for the task. SQL is probably one of more complicated
options you can go for.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi

Check out the method in my previous posts, your procedure should work if you
corrected the date formats and typos.
Note the escaped quotes in the command string, rather than using double
quotes.

Create procedure USP_DelOldFiles @.path varchar(25),@.duration int
as
--Objective: To delete files older than certain period from a folder
--Usage example:
--Exec USP_DelOldFiles 'c:\test',30
-- which deletes files older than todaydate-30
--Created by :MAK
--Created date: Jan 7,2003
--OS: windows 2000
declare @.myquery varchar(1000)
declare @.query varchar(1000)
declare @.name varchar(100)
set @.myquery = 'exec master.dbo.xp_cmdshell ''dir '+ ltrim(rtrim(@.path)) +
'\*.* /a/od'''
print @.query

create table #Filenames (id int identity(1,1) ,name varchar(100))

insert #Filenames(name)
exec (@.Myquery)
delete from #Filenames
where substring(name,3,1) <> '/'
or name is null
or substring(name,25,1) ='<'

select name, SUBSTRING(name,7,4) + SUBSTRING(name,4,2) + left(name,2)
from #Filenames

/* Make sure dates are in comparable formats */
Declare mycursor cursor for
select name from #Filenames
where SUBSTRING(name,7,4) + SUBSTRING(name,4,2) + left(name,2) <=
CONVERT(char(8),DATEADD(d,@.duration,getdate()),112 )

open mycursor

fetch next from mycursor into @.name
while @.@.fetch_status = 0
begin
set @.query = 'exec master.dbo.xp_cmdshell ''del '+ @.path + '\'+
ltrim(rtrim(substring(@.name,40,59)))+''''
print @.query
exec (@.query)
fetch next from mycursor into @.name
end
close mycursor
deallocate mycursor

drop table #Filenames

This may also be useful for other things.
http://vyaskn.tripod.com/oracle_sql...equivalents.htm
http://msdn.microsoft.com/library/d...asp?frame=true

John

"Raj" <rjdave@.indiatimes.com> wrote in message
news:c11c051e.0307130220.6fae6dc3@.posting.google.c om...
> Hi ... Sorry but I have to clarify a few things about me first...I am
> a newbie in database administration . I am familiar with many things
> which are Oracle related but for a particular task our company is
> using SQLSERVER 2000. I am lost in this SQL SERVER world . I have only
> one task to be finished with SQLSERVER 2000 . I am slowly making "new
> discoveries" of this database software. Lots of thanks specially to
> YOU Mr.Erland Sommarskog.
> Now the specifics of my task. I want to schedule a job by the
> database software which would delete the files in some folder based on
> the date. The dates of the files would be associated with createddate
> and expirydate columns in a database table i.e., when a file is
> uploaded to a directory then the date info would be inserted in the
> database table column and when the file's reference is deleted in the
> corresponding column a job scheduled to run at some interval of days
> should delete the physical files (*.txt,*.dat..etc) . Hope this make
> something clear for you. Sorry IF I sound stupid but am just earning
> my bread .
> Thanks for the help so far and best wishes.
> Erland Sommarskog <sommar@.algonet.se> wrote in message
news:<Xns93B6F1164E422Yazorman@.127.0.0.1>...
> > Raj (rjdave@.indiatimes.com) writes:
> > > Thanks ...I didn't know about it ... It was great help. But still it
> > > didn't work . I wanted to run a job and the script is below . I am not
> > > able to run this script using sql server agent. Can anyone suggest
> > > whats wrong ...and whats the trick i am missing?
> > Hey, what about *you* tell us what is wrong? I mean, you say that you
> > are not able to run it, but rather let us guess what you mean with that,
> > why not specify what you. Do you get an error message? Do the script
> > end without an files being deleted? Did the script work from Query
> > Analyzer?
> > What I can see at a glance is:
> > > while (@.@.fetch_status =0! )
> > Syntax error.
> > > set @.query = 'exec master.dbo.xp_cmdshell "del '+@.path+'\'+
> > > ltrim(rtrim(substring(@.name,40,59)))+'"'
> > Should have a /F to force deletion of read-only files.
> > Really why you make the entire call to xp_cmdshell in dynamic SQL, I
> > don't know, neither I have cared to check that you get the right
statement.
> > It should be sufficient to have only the DOS command in a variable.
> > Then again, why SQL at all? Isn't this a symptom of that when all you
> > have is hammer, everything looks like nails to you. Personally I would
> > implement this in Perl, but if you VB script or C++, these languages
> > work equally well for the task. SQL is probably one of more complicated
> > options you can go for.|||Raj (rjdave@.indiatimes.com) writes:
> Now the specifics of my task. I want to schedule a job by the
> database software which would delete the files in some folder based on
> the date. The dates of the files would be associated with createddate
> and expirydate columns in a database table i.e., when a file is
> uploaded to a directory then the date info would be inserted in the
> database table column and when the file's reference is deleted in the
> corresponding column a job scheduled to run at some interval of days
> should delete the physical files (*.txt,*.dat..etc) . Hope this make
> something clear for you. Sorry IF I sound stupid but am just earning
> my bread .

Still makes more sense to write a program in VBscript, Perl or
whatever that reads the required information from the database, and
then deletes the files. Running that program could possible be scheduled
from SQL Server Agent.

But running the whole thing from T-SQL with xp_cmdshell is not very
robust, becuase you need handle dateformats that that kind of thing.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Help - Unable to Add Column to Report

I have inherited a report that appears to be too big for the VS designer.
The query is 664 lines long and every time I move to the Data tab, Reporting
Services locks up and stops responding. (I am using the generic query
designer)
The report runs fine, my problem is I need to add another data column to the
report. I did this by editing the RDL in a text editor but when I reload in
RS and got to the Layout tab to add the column to the report, it is not
listed in the Fields list. I tried to manually add it but I then get the
'Index out of bounds error'. So it seems the field list needs to be updated
by accessing the Data tab and then moving to the Layout or Preview tab but
every time I try to do this it locks up.
I thought I might try to temporarily remove part of the query so the field
list could be updated but the report has the columns scattered throughout so
I can't locate a large enough block of contiguous query data that does NOT
affect the fields list.
Is there any work around for this?
Many thanksHi Mike,
I'm not sure what the preview issue is, but if you are ok with editing the
RDL, you should be able to do it.
When you added the field to the RDL, did you add it to both the query and to
the fields collection?
The fields collection maps results returned from the query to fields you can
put on the report. If you added a column to the query in the RDL, makes
sure you add a field element (under datasets/fields) that matches the column
name. If you did both of those and still have the problem then I don't
know.
I hope that helps!
Chris
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OO$4Noz3FHA.4080@.tk2msftngp13.phx.gbl...
>I have inherited a report that appears to be too big for the VS designer.
>The query is 664 lines long and every time I move to the Data tab,
>Reporting Services locks up and stops responding. (I am using the generic
>query designer)
> The report runs fine, my problem is I need to add another data column to
> the report. I did this by editing the RDL in a text editor but when I
> reload in RS and got to the Layout tab to add the column to the report, it
> is not listed in the Fields list. I tried to manually add it but I then
> get the 'Index out of bounds error'. So it seems the field list needs to
> be updated by accessing the Data tab and then moving to the Layout or
> Preview tab but every time I try to do this it locks up.
> I thought I might try to temporarily remove part of the query so the field
> list could be updated but the report has the columns scattered throughout
> so I can't locate a large enough block of contiguous query data that does
> NOT affect the fields list.
> Is there any work around for this?
> Many thanks
>

Help - syncronizing forms in Access 2000

regular access db - usually use sub-forms or tabs - this time I want seperate forms - have linked fields - but can't final code line in "open form" button command. Any suggestions welcome. Denny :(but can't final code line in "open form" button command

Can you explain this a little better. You might want to bullet point exactly what you want. If you want to just populate two forms on the open, you need to just scroll down on properties of main form until you get to the open propety. You'll want to set the properties of the parent form first, then the second. I have no idea if this is what you are trying to do.

Help - Just using the time of DateTime or SmallDateTime

I need to store the time in a table ("10:00:00 AM") and then compare just
the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
this is simple as heck but because I am a newbie I am stumbling. Can
someone provide me with a sample of this? Thank you.What datatype is the column in the table of?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Code Boy" <CodeBoy@.microsoft.com> wrote in message news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.ph
x.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
> the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
> this is simple as heck but because I am a newbie I am stumbling. Can
> someone provide me with a sample of this? Thank you.
>|||That is up to me (so whatever you tell me). I just want to end up selecting
(with T-SQL) any row where a column has a time only greater then the time
portion of the current time stamp. Thank you.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMb8aRNVGHA.4740@.TK2MSFTNGP14.phx.gbl...
> What datatype is the column in the table of?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Code Boy" <CodeBoy@.microsoft.com> wrote in message
> news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...|||code
select convert(varchar(8),current_timestamp,108
) , it will give you
time.
You have to compare it with time in your table.
Look at convert function for more details in BOL.
Regards
Amish Shah.|||When I do this the compare to the time in my table does not select anyting.
It is as if now that they are varchars the >= operator does not work
properly. I will look in BOL for more info. Thank you.
"amish" <shahamishm@.gmail.com> wrote in message
news:1143820529.897848.99710@.g10g2000cwb.googlegroups.com...
> code
> select convert(varchar(8),current_timestamp,108
) , it will give you
> time.
> You have to compare it with time in your table.
> Look at convert function for more details in BOL.
> Regards
> Amish Shah.
>|||>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL.
Okay, so when you insert the data into SQL Server, make sure the date is not
included (or manually force it to be 1900-01-01 on insert).
Now, you can compare to CURRENT_TIMESTAMP - DATEDIFF(DAY, 0, GETDATE()). An
example:
CREATE TABLE #foo
(
FooID INT,
NextBar SMALLDATETIME
)
INSERT #foo SELECT 1, '1900-01-01 18:34';
INSERT #foo SELECT 2, '02:25'; -- 1900-01-01 is the default
INSERT #foo SELECT 3, '06:34';
INSERT #foo SELECT 4, '10:25';
INSERT #foo SELECT 5, '14:57';
INSERT #foo SELECT 6, '20:36';
INSERT #foo SELECT 6, '23:11';
-- now let's find those rows where NextBar is within the next 6 hours
DECLARE @.s SMALLDATETIME, @.e SMALLDATETIME;
SET @.s = CURRENT_TIMESTAMP - DATEDIFF(DAY, 0, GETDATE());
SET @.e = DATEADD(HOUR, 6, @.s);
SELECT FooID, NextBar = CONVERT(CHAR(5), NextBar, 108)
FROM #foo
WHERE NextBar >= @.s
AND NextBar <= @.e;
-- see all rows:
SELECT FooID, NextBar = CONVERT(CHAR(5), NextBar, 108)
FROM #foo;
DROP TABLE #foo;|||Figured it out:
select cast(convert(varchar,OurTime,114) as datetime) as OurTime
from FileName
where cast(convert(varchar,OurTime,114) as datetime) >
convert(varchar,getdate(),114)
Thank you all!
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
>this is simple as heck but because I am a newbie I am stumbling. Can
>someone provide me with a sample of this? Thank you.
>|||Figured it out:
select cast(convert(varchar,OurTime,114) as datetime) as OurTime
from FileName
where cast(convert(varchar,OurTime,114) as datetime) >
convert(varchar,getdate(),114)
Thank you all!
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
>this is simple as heck but because I am a newbie I am stumbling. Can
>someone provide me with a sample of this? Thank you.
>|||> select cast(convert(varchar,OurTime,114) as datetime) as OurTime
> from FileName
> where cast(convert(varchar,OurTime,114) as datetime) >
> convert(varchar,getdate(),114)
All these casts and converts will really hurt your performance.
If you're cunning enough to have an index on that column, you should compare
the plan for this solution compared to the one I provided...

help

Can anybody please help me with this problem
1. I have a table with the following column :
code Date Time vol exptime elapse
4 20010424 104232 945 40165
My problem is none of these column can be my primary key so my question is
How can I assign another column say 'transact_id' which will have an incremental and unique number? In other word my objective are:
a. I would like to have the data sorted according to code, date, time
b. I would like to assign a column which consist of unique and incremental number e.g. for code 1, first date and first time the 'transact_id' column will be = 1; for code 1, first date and second time the 'transact_id' column will be = 2;and so on

2. I would like to calculate the column elapse as = 'exptime(t) - exptime (t-1)'. In other words, the elapse is the difference between exptime of the current collumn with the previous one? Can you create a query to calculate this?Can the date and time together be your primary key? SQL Server will store them as a single column anyway, so this might make sense.

-PatP|||Can the date and time together be your primary key? SQL Server will store them as a single column anyway, so this might make sense.

-PatP|||Yes I think I can add another column which is the combination of date and time so each column will look like this yyyymmddhhmmss--> e.g. 20010101102010 which is 1 Jan 2001 10:20:10|||It would really help if you could post the DDL (probably the CREATE TABLE statement) for your table. Otherwise we need to guess at too much. Yes, it is quite possible to create a query like what you want.

-PatP|||I have attached the sample file
for you convenience I have selected random sample of less than 100 observation
thanks in advance for your help|||a. I would like to have the data sorted according to code, date, time you don't need a primary key to do that

b. I would like to assign a column which consist of unique and incremental number e.g. for code 1, first date and first time the 'transact_id' column will be = 1; for code 1, first date and second time the 'transact_id' column will be = 2;and so on you could add an IDENTITY column to the table, but you don't need to

2. I would like to calculate the column elapse as = 'exptime(t) - exptime (t-1)'. In other words, the elapse is the difference between exptime of the current collumn with the previous one? Can you create a query to calculate this?yes, i can

by "previous" you mean the row with the highest date and time that is less than the current row, for the same code, right? or do you regard all codes identically when it comes to sequencing by date and time?

clarifying exactly what you want is important to the eventual sql

by the way, if you can, you should replace the date and time columns with one datetime column|||by the way, if you can, you should replace the date and time columns with one datetime column
Yes I have done that see this attachment

by "previous" you mean the row with the highest date and time that is less than the current row, for the same code, right? or do you regard all codes identically when it comes to sequencing by date and time?

I'm not really sure whether i understand your question however, here's my explanation: The code is a stock code so I arrange the data to be sorted according to code, date and time and I would like to calculate the elapse
as the difference between exptime for the current row and the previous row using macro in excel the langguange will look like this
R1C1 = "=RC[-1]-R[-1]C[-1]"

please tell me if you think you need more explanation|||by the way, if you can, you should replace the date and time columns with one datetime column
Yes I have done that see this attachmentif you meant to attach a new description, you forgot ;)

i will use your first description:select t1.code
, t1.[Date]
, t1.[Time]
, t1.vol
, t1.exptime
, t1.exptime
-t2.exptime as diff
from currprev as t1
left outer
join currprev as t2
on t1.code = t2.code
and cast(t2.[Date] as char(8))
+cast(t2.[Time] as char(6))
= (
select max(
cast([Date] as char(8))
+cast([Time] as char(6))
)
from currprev
where (
[Date] < t1.[Date]
or [Date] = t1.[Date]
and [Time] < t1.[Time]
)
)
order by 1,2,3
the results of this are: code Date Time exptime elapse
5 20010102 100921 36561
5 20010102 104046 38446 1885
5 20010102 132221 48141 9695
5 20010102 132518 48318 177
5 20010103 102123 37283 -11035
5 20010103 120312 43392 6109
5 20010103 122434 44674 1282
5 20010103 150953 54593 9919
5 20010103 150953 54593 9919
5 20010103 151918 55158 565
5 20010103 151918 55158 565
5 20010104 101123 36683 -18475
5 20010104 121213 43933 7250
5 20010104 144338 53018 9085
5 20010104 145634 53794 776
5 20010104 153809 56289 2495
5 20010105 123717 45437 -10852
5 20010105 132814 48494 3057
5 20010125 112752 41272 -7222
5 20010125 113146 41506 234
5 20010125 113146 41506 234
5 20010125 113146 41506 234
5 20010125 113653 41813 307
5 20010125 113653 41813 307
5 20010125 113653 41813 307
5 20010125 114443 42283 470
5 20010125 114550 42350 67
5 20010125 114756 42476 126
5 20010125 114756 42476 126
5 20010125 114905 42545 69
5 20010125 114905 42545 69
5 20010125 115235 42755 210
5 20010125 121430 44070 1315
5 20010125 123000 45000 930
329 20010424 104232 38552
329 20010424 104806 38886 334
329 20010424 104806 38886 334
329 20010424 104806 38886 334
329 20010424 104940 38980 94
329 20010424 104940 38980 94
329 20010424 104940 38980 94
329 20010424 110925 40165 1185
329 20010424 110925 40165 1185
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112156 40916 751
329 20010424 112216 40936 20
329 20010424 112216 40936 20
329 20010424 112216 40936 20
329 20010424 120540 43540 2604
329 20010424 120540 43540 2604
329 20010424 120540 43540 2604
329 20010424 120540 43540 2604
329 20010424 120623 43583 43
329 20010424 120623 43583 43
329 20010424 120623 43583 43
329 20010424 120623 43583 43
329 20010424 120627 43587 4
329 20010424 121024 43824 237
329 20010521 104239 38559 -5265
et cetera
having an IDENTITY primary key would have helped immensely

your data has dupes in it, and consequently the output reflects this, because the dupes are cross-joined within groups of the same code/[Date]/[Time]

i strongly urge you to clean up the data before proceeding|||Thanks very much, (sorry about the attachment)
However I can not delete the duplicates since it is a vaild observations
Do you have any suggestion about creating an IDENTITY primary key ?

help

every time i log onto yahoo messenger i keep getting this
screen full of messages and it shuts down my computer,
please help. gunnerIt probably doesn't have anything to do with SQL Server
security...not that I can think of anyway. You may want to
check Yahoo messenger help site instead:
http://help.yahoo.com/help/mesg/index.html
-Sue
On Fri, 9 Apr 2004 15:37:43 -0700, "gunner1469@.yahoo.com"
<anonymous@.discussions.microsoft.com> wrote:

>every time i log onto yahoo messenger i keep getting this
>screen full of messages and it shuts down my computer,
>please help. gunner