Absurd project: script

Showing posts with label script. Show all posts

Friday, March 30, 2012

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
>
>

Monday, March 26, 2012

Help Fixing Script.

Hello everybody... I have a SQL 2000 Script that is not working on SQL 2005.. Or 2000 for that matter.. I wanted to know if somebody can help me fix this script.

/****** Object: Database NetManage_SQL ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'NetManage_SQL')
DROP DATABASE [NetManage_SQL]
GO

CREATE DATABASE [NetManage_SQL] ON (NAME = N'NetManage_SQL_Data', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NetManage_SQL_Data.MDF' , SIZE = 10, FILEGROWTH = 10%) LOG ON (NAME = N'NetManage_SQL_Log', FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\NetManage_SQL_Log.LDF' , SIZE = 10, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'NetManage_SQL', N'autoclose', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'bulkcopy', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'trunc. log', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'torn page detection', N'true'
GO

exec sp_dboption N'NetManage_SQL', N'read only', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'dbo use', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'single', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'autoshrink', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'ANSI null default', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'recursive triggers', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'ANSI nulls', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'concat null yields null', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'cursor close on commit', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'default to local cursor', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'quoted identifier', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'ANSI warnings', N'false'
GO

exec sp_dboption N'NetManage_SQL', N'auto create statistics', N'true'
GO

exec sp_dboption N'NetManage_SQL', N'auto update statistics', N'true'
GO

if( ( (@.@.microsoftversion / power(2, 24) = 8) and (@.@.microsoftversion & 0xffff >= 724) ) or ( (@.@.microsoftversion / power(2, 24) = 7) and (@.@.microsoftversion & 0xffff >= 1082) ) )
exec sp_dboption N'NetManage_SQL', N'db chaining', N'false'
GO

use NetManage_SQL
GO
exec sp_addlogin 'NetManageAdmin', 'DigitalNetrixdbadmin', 'NetManage_SQL', 'us_english'
GO

/****** Object: Table [dbo].[Device_SwitchPorts] ******/
CREATE TABLE [dbo].[Device_SwitchPorts] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Device_IPAddress_ID] [int] Default 0 ,
[SwitchPortDevice_Type_ID] [int] Default 0 ,
[SwitchPortSlotNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SwitchPortNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SwitchPortDeviceID] [int] Default 0 ,
[SwitchPortDeviceName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Device_Type] ******/
CREATE TABLE [dbo].[Device_Type] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Device_Type_ID] [int] Default 0 ,
[Device_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[IPAddress] ******/
CREATE TABLE [dbo].[IPAddress] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Subnet_ID] [int] Default 0 ,
[IPAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPDecimal] [float] Default 0 ,
[IPAddress_TypeID] [int] Default 0 ,
[Device_Type_ID] [int] Default 0 ,
[IPAddress_Host_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddressLocation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPComments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress_Subnet] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastUpdate] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[IPAddress_Mask] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[IPAddress_Type] ******/
CREATE TABLE [dbo].[IPAddress_Type] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[IPAddress_Type_ID] [int] Default 0 ,
[Can_Edit] [int] NULL ,
[IPAddress_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[User_Activity] ******/
CREATE TABLE [dbo].[User_Activity] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[UserName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Login_Date_Time] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[User_IP] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[Users] ******/
CREATE TABLE [dbo].[Users] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tree_Label] [nvarchar] (50) Default Contact_Name ,
[DisplayStyle] [int] Default 0 ,
[Password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Full_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmailAddress] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CanDelete] [int] Default 1 ,
[Node_Access] [ntext] Default 0 ,
[Priv] [int] Default 3
) ON [PRIMARY]
GO

/****** Object: Table [dbo].[settings] ******/
CREATE TABLE [dbo].[settings] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Company] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LicenseKey] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

/****** Object: Table [dbo].[subnet] ******/
CREATE TABLE [dbo].[subnet] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[Subnet_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Mask] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Parent_Subnet] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Type] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subnet_Comment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Company_Division] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Node_Location] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Contact_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Contact_Phone] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VLAN_Info] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Created_By] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Insert into [settings](Company, LicenseKey, comments) values('DEMO COMPANY','|95|49|47|26|253|195|170|232|71|19|151|77|188|231|23|64|87|62|215|53|169|186|27|65|218|111|185|218|238|127|2|115|187|245','Product License Key')
Insert into [subnet](Subnet_Name, Subnet_Mask, Parent_Subnet) values('Network Enterprise','000000000000','0')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(0,'Not Assigned')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(1,'PC')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(2,'Printer')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(3,'Router')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(4,'Switch')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(5,'Hub')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(6,'Web Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(7,'FTP Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(8,'Mail Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(9,'DNS Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(10,'DHCP Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(11,'Other')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(12,'Virtual Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(13,'Other Server')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(14,'VoIP Phone')
Insert into [Device_Type](Device_Type_ID, Device_Type) values(15,'SQL Server')

Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(0,0, 'Not Assigned')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(1,0, 'Static')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(2,0, 'DHCP')
Insert into [IPAddress_Type](IPAddress_Type_ID, Can_Edit, IPAddress_Type) values(3,0, 'Reserved')

Insert into [Users](UserName, DisplayStyle, [Password],Full_Name, EmailAddress, CanDelete, Priv) values('Administrator',0,'admin','Administrator', 'admin@.company.com',0,1)

I keep getting error:

Msg 128, Level 15, State 1, Line 6

The name "Contact_Name" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Any help pleae..

Thanks..

This line in the Create statement for dbo.Users is your problem: [Tree_Label] [nvarchar] (50) Default Contact_Name ,

If it is your attention to have the default value be the text string Contact_Name, then the string must be enclosed in single quotes. Like so:

[Tree_Label] [nvarchar] (50) Default ('Contact_Name') ,

If you want it to default to a value in another column, then you need to use a trigger, not a default.

|||

Robert,

Thanks for the quick reply.. Just because I am new to this sql scripting thing.. how do I use a trigger and not default?

Thanks..

|||

You would probably have to do something like this:

CREATE TRIGGER TRG_SOmeTrigger ON SomeTable
FOR INSERT
AS
UPDATE SomeTable
SET SomeColumnInsteadOfDefault = AnotherValueorColumn
FROM SomeTable T
INNER JOIN INSERTED I
ON T.YOuPrimaryKeyhere = I.YourPrimaryKeyHere
WHERE SomeColumnInsteadOfDefault IS NULL

Although this is not the same logic as the Default value this could be an example for a trigger.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Friday, March 23, 2012

Help desperately needed with a quick question about DDQ queries!

Hi,
I posted a question last w

, trying to find out if anyone knew
whether, in a Data Driven Query script, it's possible to do the
following:
IF (condition 1) THEN
.
.
Main = DTSTransformstat_UpdateQuery
Main = DTSTransformstat_InsertQuery
END IF
i.e. to call two queries consecutively if a specific condition is
satisfied.
I decided to press ahead and just hope that this would work...however,
it appears that unforrtunately it doesn't work!!
I would be so grateful if anyone knows any way of either getting the
script to execute both queries as above...or, alternatively, if anyone
knows of any way I can have two query statements in the one 'query' (or
another possible way of maybe doing this)?
DTS has such a rich programmable interface in every other way, I cannot
believe that there's no way of achieving this one simple requirement!
Thank you so, so much in advance for any help or pointers anyone can
provide.
CSSUCCESS! You can do it the second way - you can have two queries in the
same 'Query' window/area/function/call-it-what-you-like.

help creating a Stored Procedure

I have some script for creating the table i want but i need some help creating a Stored Procedure. That will Insert the UserId from the aspnet_Users Table (UserId) into the UserId Column in my table. So when a user creates an account it will put that users id into my table. The data will be retrieved by a FormView and the user can Update their info threw the FormView control..

I just need to associate the UserId from the aspnet_User Table with my table, so the data is stored per UserId in a new row for each new user..

create table dbo.custom_Profile (UserIduniqueidentifiernot nullPrimary Key,IamWeArenvarchar(50)null,InterestedInnvarchar(256)null,IntroTitlenvarchar(100)null,TellOthersnvarchar(MAX)null,MaritalStatusnvarchar(20)null,BodyTypenvarchar(50)null,Racenvarchar(20)null,Smokingnvarchar(20)null,Drinkingnvarchar(20)null,Drugsnvarchar(20)null,Educationnvarchar(256)null)go

Here is what i have so far for the stored procedure

create procedure getcustomProfile @.UserIduniqueidentifierasSELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdgoifnot exists (select *from dbo.aspnet_Userswhere UserId = @.UserId)BEGINDeclare @.UserIduniqueidentifier update (dbo.content_Profile)set UserId = @.UserIdwhere UserId = @.UserIdEndELSEBEGIN INSERT INTO dbo.custom_Profile (UserId) VALUES (@.UserId)END

|||

Hi,

I'm sure you can achieve what you want from a stored procedure, but there are easier ways. The article at:

http://davidhayden.com/blog/dave/archive/2006/01/27/2775.aspx

This shows how to create a table using SMO.

Hope this helps.

Paul

|||Thats cool something to look into later, but for now i'm just working on studying the creation of Stored Procedures. Won't learn how to create them if a program does most of the work for me, why i prefer to do it this way for now Smile

|||

Hi,

No problem with that. Mind you, think i mis-read it anyway - thought you needed to create a table from a stored procedure. You don't actually say what is wrong with what you've done so far. I might be wrong, but I don't think you need the line 'Declare @.UserIduniqueidentifier'as you have already passed this in as a parameter value.

Let me know what appears to be going wrong and I'll try and help further.

Paul

|||

I was just trying out the table and sp in my site, and when i create a new user account a new row isn't created in the column for the table..Trying to get the stored procedure to take the UserId from the aspnet_Users table example (59afcb85-c20c-4937-8ab9-a44a57e22ce0). Than Insert that UserId into my table custom_Profile (Column UserId) and do this for each new user account..

|||forgot to put in the T-SQL ...lol here you go

create table dbo.custom_Profile (UserIduniqueidentifiernot nullPrimary Key,IamWeArenvarchar(50)null,InterestedInnvarchar(256)null,IntroTitlenvarchar(100)null,TellOthersnvarchar(MAX)null,MaritalStatusnvarchar(20)null,SexualOrientationnvarchar(20)null,Heightnchar(10)null,BodyTypenvarchar(50)null,Racenvarchar(20)null,Smokingnvarchar(20)null,Drinkingnvarchar(20)null,Drugsnvarchar(20)null,Educationnvarchar(256)null,Circumcisednvarchar(20)null)gocreate procedure getcustomProfile @.UserIduniqueidentifier asSELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdgoBEGININSERT dbo.custom_Profile (UserId)VALUES (@.UserId)SELECT *FROM dbo.aspnet_UsersWHERE UserId = @.UserIdEND

|||

Oh and when i remove the Declare @.UserId uniqueidentifier i get a error::

Msg 137, Level 15, State 2, Line 5

Must declare the scalar variable "@.UserId".

|||

Hi,

Apologies, I misunderstood. The problem is passing of the uniqueidentifier - this will cause the SP to fail. You should change this to an nvarchar field. I messed around a little and the following SP will loop through all of the users:

CREATE procedure [dbo].[getcustomProfile] @.UserIdnvarchar(150)ASDECLARE UserInsertCURSORKEYSETFOR SELECT UserIDFROM dbo.aspnet_UsersDECLARE @.Usernvarchar(150)OPEN UserInsertFETCH NEXT FROM UserInsertINTO @.UserWHILE (@.@.fetch_status = 0)BEGININSERT dbo.custom_Profile (UserId)VALUES (@.User)FETCH NEXT FROM UserInsertINTO @.UserENDCLOSE UserInsertDEALLOCATE UserInsert

This does work, and you can use it as your starting point for the actual SP you need to call each time from your form.

Hope it helps,

Paul

|||So i need to call the SP from the code behind correct? for it to insert the UserId into the table upon creation of a new user account|||

Your logic in your original stored proc is incorrect. You have

IF NOT EXISTS(...)

UPDATE

ELSE

INSERT

If you think about it, how can you update something that doesnt exist? It should be the other way.

IF NOT EXISTS(...)

INSERT

ELSE

UPDATE

Fix your proc according to the logic above and post back if you have any issues.

|||

Hi,

Yes, you should call the SP from BLL once you've gathered together the results of your form. I'm assuming from the way you set out the SP that you're intending to do the User insert first and then then pass in the new UserID (with other parameters) - you should be setting up an output parameter from your Add_User SP. You actually don't need your If Exists part of the SP because you can check that the UserID has been created before allowing the call to this SP - the fact that it is a uniqueidentifier means it won't exist already. Personally, I always keep Insert, Update and Delete SPs seperate - but that is just personal preference.

Let e know if you need anything else on this.

Cheers,

Paul

|||

The UserId is the only data set getting populated automatically upon Registration of the site, the other columns aren't populated until they have registered. Than they get Re Directed to another page where they fill in the information via FormView in the User Control Panel. Than that information is displayed by another SqlDataSource and FormView on their profile page but without the Delete,Insert,Update etc. Basically using a Content Management system for the user profile to display textual information, on their page.

I'm going to use the UserId so i can pass it into the URL for unique pages like most communities (Myspace etc etc)..

|||Why is that the aspnet_Membership,Users,UsersInRoles etc don't use CodeBehind to pass the UserId into each table? cause thats what i want to do..Pass the UserId from the aspnet_Users Table into my table|||

Hi,

You can do all of that by customising the sqlMembershipProvider. It's not as difficult as you might think - thankfully! There's a pretty good tutorial to get you started at:

http://aspnet.4guysfromrolla.com/articles/120705-1.aspx

I hope this helps.

Paul

sql

Wednesday, March 21, 2012

Help batch script task stuck in a loop

I have a .bat file that calls a script file to log in to a server and upload a file to my pub account. When I run the .bat file it just executes the command over and over and never executes the script file. Does anyone know why? Am I missing something in my script or .bat file commands below?

my .bat file contains this>ftp -s:script.txt

my script file contains this:

open myserver.com
username
password
put W:\UPLOAD\doc.txt
bye
exit

Thanks,

Mike

Looping doesn't seem reminiscent of the batch file or the script you have listed here. I sounds like it may be due to some logic in your package. Any chance this is happening in your package?|||

It works now.

|||

Mike,

I have the same problem, can you post the solution?

Thanks.

Sebastian.

|||

Instead of using the batch file, I used a ftp script and called it using the Execute Process Task Editor:

Executable: C:\WINDOWS\system32\ftp.exe

Arguments: -s:"C:\Documents and Settings\me\Desktop\SQL 2005 DMS project-Prod\Download E-Apps\ftpscript.txt"

the ftpscript.txt file contains the following:

open hostname
me
password
Ascii
put filepath\name
bye
exit

sql

Help batch script task stuck in a loop

my .bat file contains this>ftp -s:script.txt

my script file contains this:

open myserver.com
username
password
put W:\UPLOAD\doc.txt
bye
exit

Thanks,

Mike

Looping doesn't seem reminiscent of the batch file or the script you have listed here. I sounds like it may be due to some logic in your package. Any chance this is happening in your package?|||

It works now.

|||

Mike,

I have the same problem, can you post the solution?

Thanks.

Sebastian.

|||

Instead of using the batch file, I used a ftp script and called it using the Execute Process Task Editor:

Executable: C:\WINDOWS\system32\ftp.exe

Arguments: -s:"C:\Documents and Settings\me\Desktop\SQL 2005 DMS project-Prod\Download E-Apps\ftpscript.txt"

the ftpscript.txt file contains the following:

open hostname
me
password
Ascii
put filepath\name
bye
exit

Monday, March 12, 2012

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 ! - How can a run a sql script to install a DB in a MS SQL Server?

Hello everyone,
I am new in the area of developing and I want to run a couple of sql scripts, what is the procedure to run them if I am using a MS SQL Server?
Thanks,
ArmandoUse Query Analyzer.

Check out the Books On Line.

Cheers,

hmscott

Friday, March 9, 2012

Help - With Script

I am a sql novice and would appreciate any help with the following problem.

In a table I have property addresses stored in 6 fields. Field6 always hold
the Post Code. However, fields 4 and 5 are sometime NULL. Using the
desktop integration package we have which interfaces with MS Word when
printing an address in a letter the end results often end up looking like
this.

1 Any Street
AnyTown
AnyCounty
"Null"
"Null"
PostCode

It is not a normal Mail merge so it is not possible to use the functionality
available within MS Word to not print empty fields. Therefore I need to do
a check within SQL on the null field so that when I pass the values which
are printed as fields within MS Word the variables created by the SELECT
statement are passed over like this

1 Any Street
AnyTown
Anycounty
PostCode
"Null"
"Null"

So in brief I guess what I am after is a script which as it passes the
values in fields 1-6 to variable 1-6 it always ensures that the field
containing values end up in the first variables and the remaining variable
are left as Null.

I hope this explanation is not too confusing.

Thanks

David
--

David M Loraine

life is a holiday from eternity - eternity is a long time - so enjoy your
life !!

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004Do you have access to the SQL that generates your return results?

If so you could use the ISNULL() function (not sure if this is DB specific,
I know it works with MS SQL).

So you could do something like this:

SELECT
ISNULL ( Street, '' ),
ISNULL ( Town, '' ),
ISNULL ( County, ''),
ISNULL ( PostalCode, '' ),
ISNULL ( FieldX, '' )
ISNULL ( FieldY, '' )
FROM User_Addresses

Basically the server checks each value as it comes out of the database to
see if its Null, if it is it replaces the null value with whatever is in the
quotes. In my example the null value is simply replaced with an empty
string.

Hope this help.
</Muhd
"David M Loraine" <davidloraine@.hotmail.com> wrote in message
news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> I am a sql novice and would appreciate any help with the following
problem.
> In a table I have property addresses stored in 6 fields. Field6 always
hold
> the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> desktop integration package we have which interfaces with MS Word when
> printing an address in a letter the end results often end up looking like
> this.
> 1 Any Street
> AnyTown
> AnyCounty
> "Null"
> "Null"
> PostCode
> It is not a normal Mail merge so it is not possible to use the
functionality
> available within MS Word to not print empty fields. Therefore I need to
do
> a check within SQL on the null field so that when I pass the values which
> are printed as fields within MS Word the variables created by the SELECT
> statement are passed over like this
>
> 1 Any Street
> AnyTown
> Anycounty
> PostCode
> "Null"
> "Null"
> So in brief I guess what I am after is a script which as it passes the
> values in fields 1-6 to variable 1-6 it always ensures that the field
> containing values end up in the first variables and the remaining variable
> are left as Null.
> I hope this explanation is not too confusing.
> Thanks
> David
> --
> David M Loraine
> life is a holiday from eternity - eternity is a long time - so enjoy your
> life !!
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004|||Hi David

You can use something like the following. I had created a table called
'Address' with fields 'Address1', 'Address2', 'City', 'Postcode'. You can
remove the PRINTs. I let them stay in, in case you want to run it in Query
Analyzer for debugging.

DECLARE
@.Address1 varchar(50),
@.Address2 varchar(50),
@.City varchar(50),
@.Postcode varchar(50)

DECLARE Address_Cursor CURSOR
FOR SELECT Address1, Address2, City, Postcode FROM Address

OPEN Address_Cursor
FETCH NEXT FROM Address_Cursor
INTO @.Address1, @.Address2, @.City, @.Postcode

WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (@.Address1 IS NULL) OR (@.Address1 = '')
BEGIN
SET @.Address1 = @.Address2
SET @.Address2 = @.City
SET @.City = @.Postcode
SET @.Postcode = ''
END

IF (@.Address2 IS NULL) OR (@.Address2 = '')
BEGIN
SET @.Address2 = @.City
SET @.City = @.Postcode
SET @.Postcode = ''
END

IF (@.City IS NULL) OR (@.City = '')
BEGIN
SET @.City = @.Postcode
SET @.Postcode = ''
END

PRINT @.Address1
PRINT @.Address2
PRINT @.City
PRINT @.Postcode
PRINT '----------'

FETCH NEXT FROM Address_Cursor
INTO @.Address1, @.Address2, @.City, @.Postcode
END

CLOSE Address_Cursor
DEALLOCATE Address_Cursor

"David M Loraine" <davidloraine@.hotmail.com> wrote in message
news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> I am a sql novice and would appreciate any help with the following
problem.
> In a table I have property addresses stored in 6 fields. Field6 always
hold
> the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> desktop integration package we have which interfaces with MS Word when
> printing an address in a letter the end results often end up looking like
> this.
> 1 Any Street
> AnyTown
> AnyCounty
> "Null"
> "Null"
> PostCode
> It is not a normal Mail merge so it is not possible to use the
functionality
> available within MS Word to not print empty fields. Therefore I need to
do
> a check within SQL on the null field so that when I pass the values which
> are printed as fields within MS Word the variables created by the SELECT
> statement are passed over like this
>
> 1 Any Street
> AnyTown
> Anycounty
> PostCode
> "Null"
> "Null"
> So in brief I guess what I am after is a script which as it passes the
> values in fields 1-6 to variable 1-6 it always ensures that the field
> containing values end up in the first variables and the remaining variable
> are left as Null.
> I hope this explanation is not too confusing.
> Thanks
> David
> --
> David M Loraine
> life is a holiday from eternity - eternity is a long time - so enjoy your
> life !!
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004|||Here is script in question, although it is really just the select part that
needs the work on it I believe.

The variables par_adr_line1 etc are passed to MS Word to form the address
which is printed in the letters, field 6 always holds the the post code and
as you can see it is always formatted to be in uppercase.

Frequently though fields 4 and 5 are null and consequently when the address
is printed it looks a little untidy as there is a large gap between the last
address line and the post code. What I need to happen is that when a blank
field is found in the dbase the next value down is moved up so that for
example if ad.adr_line_4 and 5 are empty the value in ad,adr_line_6 ends up
being in field par_adr_line4 or if only ad.adr_line_5 is empty then the
value in ad.adr_line_6 ends up in par_adr_line5.

I hope this clarifies my enquiry

Select initcap(ad.adr_line_1) par_adr_line1,

initcap(ad.adr_line_2) par_adr_line2,

initcap(ad.adr_line_3) par_adr_line3,

initcap(ad.adr_line_4) par_adr_line4,

initcap(ad.adr_line_5) par_adr_line5,

upper(ad.adr_line_6) par_adr_line6

from tenancy_instances ti,

household_persons ho,

address_usages au,

addresses ad

where ti.tin_tcy_refno = '$tenancy_ref'

and ad.adr_refno = au.aus_adr_refno

and au.aus_aut_fao_code = 'PAR'

and au.aus_aut_far_code = (select max(au2.aus_aut_far_code)

from

address_usages au2

where

au2.aus_par_refno = au.aus_par_refno

and

au2.aus_aut_fao_code = 'PAR'

and sysdate

between au2.aus_start_date and nvl(au2.aus_end_date, sysdate)

and

au2.aus_aut_far_code in ('CONTACT', 'CORRESPOND'))

and ti.tin_main_tenant_ind = 'Y'

and ti.tin_hop_refno = ho.hop_refno

and ho.hop_par_refno = au.aus_par_refno

and sysdate between au.aus_start_date and nvl(au.aus_end_date, sysdate+1)

"Muhd" <muhd@.binarydemon.com> wrote in message
news:Ef0Nb.82242$JQ1.19989@.pd7tw1no...
> Do you have access to the SQL that generates your return results?
> If so you could use the ISNULL() function (not sure if this is DB
specific,
> I know it works with MS SQL).
> So you could do something like this:
> SELECT
> ISNULL ( Street, '' ),
> ISNULL ( Town, '' ),
> ISNULL ( County, ''),
> ISNULL ( PostalCode, '' ),
> ISNULL ( FieldX, '' )
> ISNULL ( FieldY, '' )
> FROM User_Addresses
> Basically the server checks each value as it comes out of the database to
> see if its Null, if it is it replaces the null value with whatever is in
the
> quotes. In my example the null value is simply replaced with an empty
> string.
> Hope this help.
> </Muhd>
> "David M Loraine" <davidloraine@.hotmail.com> wrote in message
> news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> > I am a sql novice and would appreciate any help with the following
> problem.
> > In a table I have property addresses stored in 6 fields. Field6 always
> hold
> > the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> > desktop integration package we have which interfaces with MS Word when
> > printing an address in a letter the end results often end up looking
like
> > this.
> > 1 Any Street
> > AnyTown
> > AnyCounty
> > "Null"
> > "Null"
> > PostCode
> > It is not a normal Mail merge so it is not possible to use the
> functionality
> > available within MS Word to not print empty fields. Therefore I need to
> do
> > a check within SQL on the null field so that when I pass the values
which
> > are printed as fields within MS Word the variables created by the SELECT
> > statement are passed over like this
> > 1 Any Street
> > AnyTown
> > Anycounty
> > PostCode
> > "Null"
> > "Null"
> > So in brief I guess what I am after is a script which as it passes the
> > values in fields 1-6 to variable 1-6 it always ensures that the field
> > containing values end up in the first variables and the remaining
variable
> > are left as Null.
> > I hope this explanation is not too confusing.
> > Thanks
> > David
> > --
> > David M Loraine
> > life is a holiday from eternity - eternity is a long time - so enjoy
your
> > life !!
> > --
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004|||Here is script in question, although it is really just the select part that
needs the work on it I believe.

I hope this clarifies my enquiry

Select initcap(ad.adr_line_1) par_adr_line1,

initcap(ad.adr_line_2) par_adr_line2,

initcap(ad.adr_line_3) par_adr_line3,

initcap(ad.adr_line_4) par_adr_line4,

initcap(ad.adr_line_5) par_adr_line5,

upper(ad.adr_line_6) par_adr_line6

from tenancy_instances ti,

household_persons ho,

address_usages au,

addresses ad

where ti.tin_tcy_refno = '$tenancy_ref'

and ad.adr_refno = au.aus_adr_refno

and au.aus_aut_fao_code = 'PAR'

and au.aus_aut_far_code = (select max(au2.aus_aut_far_code)

from

address_usages au2

where

au2.aus_par_refno = au.aus_par_refno

and

au2.aus_aut_fao_code = 'PAR'

and sysdate

between au2.aus_start_date and nvl(au2.aus_end_date, sysdate)

and

au2.aus_aut_far_code in ('CONTACT', 'CORRESPOND'))

and ti.tin_main_tenant_ind = 'Y'

and ti.tin_hop_refno = ho.hop_refno

and ho.hop_par_refno = au.aus_par_refno

and sysdate between au.aus_start_date and nvl(au.aus_end_date, sysdate+1)

--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004|||By no means am i an expert and god i hope im not stearing you wrong but im
pretty sure you can simply do what i suggested above, to help you out i
changed the first six lines of your script to reflect what i was talking
about in my earlier post. You shouldn't need to change any other parts of
your script. Note that if the value is null in the database intead of
returning "null" your script should now just return blank data (i.e. an
empty string).

Select isnull(initcap(ad.adr_line_1),'')
par_adr_line1,
isnull(initcap(ad.adr_line_2),'') par_adr_line2,
isnull(initcap(ad.adr_line_3),'') par_adr_line3,
isnull(initcap(ad.adr_line_4),'') par_adr_line4,
isnull(initcap(ad.adr_line_5),'') par_adr_line5,
isnull(upper(ad.adr_line_6),'') par_adr_line6

It might not be the "best" way but its "a" way and it should work.
Best,
Muhd.

"David M Loraine" <davidloraine@.hotmail.com> wrote in message
news:7HjNb.23$S01.22@.news-binary.blueyonder.co.uk...
> Here is script in question, although it is really just the select part
that
> needs the work on it I believe.
> The variables par_adr_line1 etc are passed to MS Word to form the address
> which is printed in the letters, field 6 always holds the the post code
and
> as you can see it is always formatted to be in uppercase.
> Frequently though fields 4 and 5 are null and consequently when the
address
> is printed it looks a little untidy as there is a large gap between the
last
> address line and the post code. What I need to happen is that when a
blank
> field is found in the dbase the next value down is moved up so that for
> example if ad.adr_line_4 and 5 are empty the value in ad,adr_line_6 ends
up
> being in field par_adr_line4 or if only ad.adr_line_5 is empty then the
> value in ad.adr_line_6 ends up in par_adr_line5.
> I hope this clarifies my enquiry
> Select initcap(ad.adr_line_1) par_adr_line1,
> initcap(ad.adr_line_2) par_adr_line2,
> initcap(ad.adr_line_3) par_adr_line3,
> initcap(ad.adr_line_4) par_adr_line4,
> initcap(ad.adr_line_5) par_adr_line5,
> upper(ad.adr_line_6) par_adr_line6
>
> from tenancy_instances ti,
> household_persons ho,
> address_usages au,
> addresses ad
>
> where ti.tin_tcy_refno = '$tenancy_ref'
> and ad.adr_refno = au.aus_adr_refno
> and au.aus_aut_fao_code = 'PAR'
> and au.aus_aut_far_code = (select max(au2.aus_aut_far_code)
> from
> address_usages au2
> where
> au2.aus_par_refno = au.aus_par_refno
> and
> au2.aus_aut_fao_code = 'PAR'
> and sysdate
> between au2.aus_start_date and nvl(au2.aus_end_date, sysdate)
> and
> au2.aus_aut_far_code in ('CONTACT', 'CORRESPOND'))
> and ti.tin_main_tenant_ind = 'Y'
> and ti.tin_hop_refno = ho.hop_refno
> and ho.hop_par_refno = au.aus_par_refno
> and sysdate between au.aus_start_date and nvl(au.aus_end_date,
sysdate+1)
>
>
>
> "Muhd" <muhd@.binarydemon.com> wrote in message
> news:Ef0Nb.82242$JQ1.19989@.pd7tw1no...
> > Do you have access to the SQL that generates your return results?
> > If so you could use the ISNULL() function (not sure if this is DB
> specific,
> > I know it works with MS SQL).
> > So you could do something like this:
> > SELECT
> > ISNULL ( Street, '' ),
> > ISNULL ( Town, '' ),
> > ISNULL ( County, ''),
> > ISNULL ( PostalCode, '' ),
> > ISNULL ( FieldX, '' )
> > ISNULL ( FieldY, '' )
> > FROM User_Addresses
> > Basically the server checks each value as it comes out of the database
to
> > see if its Null, if it is it replaces the null value with whatever is in
> the
> > quotes. In my example the null value is simply replaced with an empty
> > string.
> > Hope this help.
> > </Muhd>
> > "David M Loraine" <davidloraine@.hotmail.com> wrote in message
> > news:hW_Mb.50$M26.30@.news-binary.blueyonder.co.uk...
> > > I am a sql novice and would appreciate any help with the following
> > problem.
> > > > In a table I have property addresses stored in 6 fields. Field6
always
> > hold
> > > the Post Code. However, fields 4 and 5 are sometime NULL. Using the
> > > desktop integration package we have which interfaces with MS Word when
> > > printing an address in a letter the end results often end up looking
> like
> > > this.
> > > > 1 Any Street
> > > AnyTown
> > > AnyCounty
> > > "Null"
> > > "Null"
> > > PostCode
> > > > It is not a normal Mail merge so it is not possible to use the
> > functionality
> > > available within MS Word to not print empty fields. Therefore I need
to
> > do
> > > a check within SQL on the null field so that when I pass the values
> which
> > > are printed as fields within MS Word the variables created by the
SELECT
> > > statement are passed over like this
> > > > > 1 Any Street
> > > AnyTown
> > > Anycounty
> > > PostCode
> > > "Null"
> > > "Null"
> > > > So in brief I guess what I am after is a script which as it passes the
> > > values in fields 1-6 to variable 1-6 it always ensures that the field
> > > containing values end up in the first variables and the remaining
> variable
> > > are left as Null.
> > > > I hope this explanation is not too confusing.
> > > > Thanks
> > > > David
> > > --
> > > > David M Loraine
> > > > life is a holiday from eternity - eternity is a long time - so enjoy
> your
> > > life !!
> > > > > --
> > > Outgoing mail is certified Virus Free.
> > > Checked by AVG anti-virus system (http://www.grisoft.com).
> > > Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004
> > >
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.560 / Virus Database: 352 - Release Date: 08/01/2004

HELP - urgent situation

Hey all

I've got a DTS package with a script that does this:

Set xlApp = CreateObject("Excel.Application")

This fails though. Do I have to install MS Office on the machine the SQL Server is on? Or can I simply copy a few DLLs over and register them??

Thanks! I gotta get this solved within 5 hrs :(I am by no means an expert on this particular situation, but I would think that your 'best' (meaning most likely to work the first time) solution would be to install MS Office (just the Excel portion of it) on the server.

Regards,

hmscott|||Our in-house IT guy won't install Excel/Office on the server that our SQL Server is on.. something about opening up for potential problems and/or security issues.

It seems to me that if you CreateObject("Excel.Application"), that there's some kind of ActiveX .dll that could be registered on the server to allow the creation of these Excel COM objects. Anyone know which files they are??|||Where is the Package called from?

If client side app I would suggest installing Excel would work,

If a VB app adding the Excel distributables in the compile should work.

Create a reference to the Excel Object in your VB app.

mine is at
C:\Program Files\Microsoft Office 2000\Office\EXCEL9.OLB

When installed the required registry entries should follow.

Good Luck|||It's an ActiveX script within a DTS package on the server. I'd LIKE to have Office installed, but IT won't allow it.

Is there some way I could place the EXCEL9.OLB file on the server, and reference it somehow in my VBScript??|||All DTS Packages are run on the client unless scheduled by SQL Server regardless of where the code resides.

Your users will need Excel on their PC to use the output anyway, as well as DTS dlls (read SQL Server Client).

Is XML an option? or a structured text file which they can import into Excel. If so you could use the fileScripting object in Active script.|||Well.. actually this is a web page executing the DTS package.. so all the proper DTS dlls are installed on the web server now. And the DTS package has an ActiveX script that tries to create an instance of Excel.Application..but can't do so because Office is not installed on the web server (and will never be).

Nope.. XML is not an option. They gave us an Excel workbook they want populated.. there's no room to budge on this, unfortunately.|||If all you are doing is a data migration into a template try to map to the Excel spreadheet and se a dtsDataPump|||What if you pumped the data to a Comma Separated Value file (.csv), and let Excel convert it from wherever the client happens to open it from?|||OK here's the solution we went with.. MS Office Web Components.

Problem is, I can't find any thorough documentation on it. I want to know if it's possible to open an Excel file with it.

Set objOWC = CreateObject("OWC10.Spreadsheet")

That's how it's created. I have a few examples that tell you how to create a new Excel file from scratch and save it. But not how to open an existing one, modify it, then save to a new file.

Does anyone know, or can point me to some documentation? Thanks!

Help - Updating A Field In Query Analyzer

Hi All,

I'm trying to create a script that updates a field in a table, based on data in another table. It should be simple, but I'm doing something wrong. Here's the code:

USE DBMyDatabase

UPDATE TblToBeUpdated
SET IDField=TblOther.IDNew
WHERE IDField=TblOther.IDOld

SELECT Pk, IDField
FROM TblToBeUpdated

What am I doing wrong? The error code I get is:

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'TblOther' does not match with a table name or alias name used in the query.

Thanks.

HenryUPDATE TblToBeUpdated
SET IDField=TblOther.IDNew
FROM TblToBeUpdated
inner join TblOther on TblToBeUpdated.IDField=TblOther.IDOld|||I very rarely use this syntax, but as I remember it:UPDATE tblToBeUpdated
SET IDField = b.IDNew
FROM tblToBeUpdated
JOIN tblOther AS b
ON (b.IDOld = tblToBeUpdated.IDField) should do the trick!

-PatP|||Thanks. Works beautifully.

Henry

Wednesday, March 7, 2012

Help - Script that checks for OFFLINE Database

Hello ,

I have got a script which checks for the databases that are offline. and mails the dba when any of the database is offline.

When I schedule the job , for every hour , it gives me an blank mail only with the subject ' status of database on testsql' even though no databases are offline.

So how can I change the script , so that it mails the dba only when a database is offline even though its scheduled every hour or half an hour.

The script is:

Set NoCount on
DECLARE @.dbname VARCHAR(100)
deCLARE @.Status varchar(100)
Declare @.Message VARCHAR(8000)
DECLARE @.date varchar(100)
set @.date = convert(varchar(100), getdate(),109)
set @.Message = ''DECLARE dbname_cursor
CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
order by name
OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
WHILE
@.@.FETCH_STATUS = 0
BEGIN select @.message = @.message + @.@.Servername + '-' + @.dbname + ' - ' + @.Status + Char(13)+ - + @.date
FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
END
CLOSE dbname_cursor
DEALLOCATE dbname_cursor

print @.message
EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'testsql2000@.is.depaul.edu',
@.TO = N'dvaddi@.depaul.edu',
@.server = N'smtp.depaul.edu',
@.subject = N'Status of the Database on Testsqlserver!',
@.type = N'text/html',
@.message = @.message

ThanksThe problem is there is no conditional around the exec master.dbo.xp_smtp_sendmail. Try this. At the beginning, check to see if there are any databases offline with something like:

if exists (select * from sysdatabases where databasepropertyex (name, 'Status') = 'OFFLINE'
begin
>>your code<<
end|||sorry to ask, but can you be more clear on what you have mentioned. Like after giving the statement that you have given, what I should be giving next as part of my code.

Thanks|||Cut-and-paste the code from your post, and place it into the code from MCrowley's post in place of the ">>your code<<" marker.

-PatP|||It doesnot seem to be working. It still gives out the blank email , when no database is offline.

Thanks|||Looks like I missed a close parenthesis, but since you got it to parse, looks like you found that problem with ease. Post what you have, now.|||The code that now I am executing is :

if exists (select * from sysdatabases where databasepropertyex (name, 'Status') = 'OFFLINE')
--Set NoCount on
DECLARE @.dbname VARCHAR(100)
deCLARE @.Status varchar(100)
Declare @.Message VARCHAR(8000)
DECLARE @.date varchar(100)
set @.date = convert(varchar(100), getdate(),109)
set @.Message = ''
--DECLARE dbname_cursor
--CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
--as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
--order by name
--OPEN dbname_cursor
--FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
--WHILE
--@.@.FETCH_STATUS = 0
BEGIN select @.message = @.message + @.@.Servername + '-' + @.dbname + ' - ' + @.Status + Char(13)+ '- ' + @.date
--FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
--END
--CLOSE dbname_cursor
--DEALLOCATE dbname_cursor
if (@.message <> '')
print @.message
EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'testsql2000@.is.depaul.edu',
@.TO = N'dvaddi@.depaul.edu',
@.server = N'smtp.depaul.edu',
@.subject = N'Status of the Database on Testsqlserver!',
@.type = N'text/html',
@.message = @.message
end

So ,if the database is offline I am getting the mail correctly.

But even if no database is offline, it is saying....command completed successfully and I am getting a blank mail.

Thanks|||How about this

if exists (select * from sysdatabases where databasepropertyex (name, 'Status') = 'OFFLINE')
begin
Set NoCount on
DECLARE @.dbname VARCHAR(100)
deCLARE @.Status varchar(100)
Declare @.Message VARCHAR(8000)
DECLARE @.date varchar(100)
set @.date = convert(varchar(100), getdate(),109)
set @.Message = ''DECLARE dbname_cursor
CURSOR FOR SELECT [name], CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s'))
as [Status] FROM master..sysdatabases WHERE CONVERT(varchar(30),DATABASEPROPERTYEX(name,'Statu s')) = 'OFFLINE'
order by name
OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
WHILE
@.@.FETCH_STATUS = 0
BEGIN select @.message = @.message + @.@.Servername + '-' + @.dbname + ' - ' + @.Status + Char(13)+ ‘- ‘ + @.date
FETCH NEXT FROM dbname_cursor INTO @.dbname, @.Status
END
CLOSE dbname_cursor
DEALLOCATE dbname_cursor

print @.message
EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'testsql2000@.is.depaul.edu',
@.TO = N'dvaddi@.depaul.edu',
@.server = N'smtp.depaul.edu',
@.subject = N'Status of the Database on Testsqlserver!',
@.type = N'text/html',
@.message = @.message
END|||Hello Mcrowley,

I have got the script working. But I am using a different one.The script I have got was,

SET NOCOUNT ON

DECLARE @.Msg VARCHAR(8000)

SELECT @.Msg = ISNULL(@.Msg + CHAR(13), '') + 'Database ' + z.Name + ' on machine ' + z.ServerName + ' is ' + z.Status + ' at ' + z.Now + '.'
FROM (
SELECT TOP 100 PERCENT @.@.SERVERNAME ServerName,
Name,
CONVERT(VARCHAR, DATABASEPROPERTYEX(Name, 'Status')) Status,
CONVERT(VARCHAR, GETDATE(), 109) Now
FROM master..sysdatabases
WHERE status & 512 = 512
ORDER BY Name
) z

PRINT @.Msg

IF @.Msg IS NOT NULL
EXEC master.dbo.xp_smtp_sendmail
@.FROM = N'from address',
@.TO = N'to address',
@.server = N'smtpaddress',
@.subject = N'Status of the Database on Testsqlserver!',
@.type = N'text/html',
@.message = @.Msg

Friday, February 24, 2012

HELP - ActiveX stuck in loop, cannot kill!

Someone please help--

I've been working on an ActiveX script in a DTS package. I executed the script and it's stuck in a while..wend loop. If I kill it I will lose all the code I wrote (pretty much a lot) since last saving the entire package.

Is it possible either:

a) Kill the activex step without killing the entire DTS designer session.
b) Locate the ActiveX code which must be in a temp table somewhere.

Please help I don't want to recode the entire object.

Thanks!If you kill the spid when eunning a query in qa it doesn't shut qa...

Now don't take my word for it, but maybe if you kill the spid, the designer session will still be open...

BUT WHY would you run something before saving and WHY would you have a loop of any kind?

Help

Can anyone help me,
I am writting a script where i get to find out all the
store procedure, triggers,... that has the command "..from
table_name order by 2,1.." for example...
Here is what i got as a base idea... hope you understand
what i am trying to get at:
declare @.i int
declare @.tbnames varchar (150)
set @.i = 1
declare test cursor for
select [name] from sysobjects where xtype = 'U'
open test
fetch next from test into @.tbnames
if @.@.fetch_status = 1
Begin
if @.i < 9
begin
select o.name from syscomments c join sysobjects o
on o.id = c.id
where o.type IN ('TR','TF','P','X','V','FN')
and
c.text like '%from ' + @.tbnames + 'order by' + @.i
+ '%'
set @.i = @.i + 1
end
fetch next from test into @.tbnames
End
close test
deallocate test
Can anyone give me a hand with this pleaseI don't think you need to join from sysobjects just try
select object_name(id),* from syscomments where ctext like '%order by
[0-9]%'
since the comments can be split across multiple rows, IF a row happens to be
split in the middle of the order by OR the sp, etc is encryped your will NOT
get a hit...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"

" <anonymous@.discussions.microsoft.com> wrote in message
news:106ea01c41581$4e921ee0$a001280a@.phx
.gbl...
> Can anyone help me,
> I am writting a script where i get to find out all the
> store procedure, triggers,... that has the command "..from
> table_name order by 2,1.." for example...
> Here is what i got as a base idea... hope you understand
> what i am trying to get at:
> declare @.i int
> declare @.tbnames varchar (150)
> set @.i = 1
> declare test cursor for
> select [name] from sysobjects where xtype = 'U'
> open test
> fetch next from test into @.tbnames
> if @.@.fetch_status = 1
> Begin
> if @.i < 9
> begin
> select o.name from syscomments c join sysobjects o
> on o.id = c.id
> where o.type IN ('TR','TF','P','X','V','FN')
> and
> c.text like '%from ' + @.tbnames + 'order by' + @.i
> + '%'
> set @.i = @.i + 1
> end
>
> fetch next from test into @.tbnames
> End
> close test
> deallocate test
> Can anyone give me a hand with this please

Friday, March 30, 2012

Help improving this script

Monday, March 26, 2012

Help Fixing Script.

Friday, March 23, 2012

Help desperately needed with a quick question about DDQ queries!

help creating a Stored Procedure

Wednesday, March 21, 2012

Help batch script task stuck in a loop

Help batch script task stuck in a loop

Monday, March 12, 2012

Help ! Procedure to delete files from operating system

HELP ! - How can a run a sql script to install a DB in a MS SQL Server?

Friday, March 9, 2012

Help - With Script

HELP - urgent situation

Help - Updating A Field In Query Analyzer

Wednesday, March 7, 2012

Help - Script that checks for OFFLINE Database

Friday, February 24, 2012

HELP - ActiveX stuck in loop, cannot kill!

Help

Absurd project

Blog Archive

About Me