Need Someone Who Can Alter A Sql 7 Script For Me

Apr 2, 2002

Hello,

I tried to do this myself, but couldn't figure it out since I am very unfamiliar with sql. I have a script that exports data from our store database to a file that is used to update our webstore database. I want to add a chunk to the script that will copy an image file from one directory to another as it loops through the database. I want to copy a picture from directory1 to directory2 that has a name SKU.jpg.

I was told in this forum by Ray Miao that the format of the statement would be:

xp_cmdshell 'Copy c:directory1SKU.jpg c:directory2SKU.jpg'

My problem is that I don't know how or where in my script to place this statement so that it will work. If this is simplke and someone can tell me the setup that would be great. If not, I would be willing to hire someone to do what needs to be done to this script and send it to me. I can be reached at darren@jbjgifts.com or 217-369-2686.

Here is the script:

-------------------------------------------------------------------
use TAMDATA
go

set NOCOUNT on

/* GET SKUS THAT ARE NOT ON SALE HERE */
select s.sku SKU,
s.Description Description,
CONVERT(DECIMAL(9,2),s.SURetail) Price,
null Weight,
Replace(Replace(w.commentary,CHAR(13)+ CHAR(10),'<BR>'),Char(39),'') MarketingDescription,
'Yes' Taxable,
v.Company+' '+c.description SoftCartCategory,
null SoftCartTemplate,
/*lower('template_'+REPLACE(v.company,' ','_')) SoftCartTemplate,*/
null VendorNo,
null ListPrice,
null Graphic,
null Thumbnail,
null SoftCartAttributes,
ISNULL(s.vendstock,'none listed') vin,
/*CONVERT(DECIMAL(6,0),(sl.OHUnits-sl.PRUnits-LYUnits-2)) InventoryQuantity,*/
CONVERT(DECIMAL(6,0),(sl.OHUnits-sl.PRUnits-LYUnits)) InventoryQuantity,
v.company vn,
cl.description cl,
d.description dt,
c.description ct,
ISNULL(sz.description,'none listed') Size1,
ISNULL(clr.description,'none listed') Color,
' ' OnSale,
'N' OnSaleNow

from SKU S,
VENDORS V,
CATEGORIES C,
DEPARTMENTS D,
CLASS CL,
SKU_LOCATION SL,
SKU_WEB_INFO W,
SIZES SZ,
COLORS CLR

wheres.skuid=sl.skuid and
s.skuid=w.skuid and
s.vendorid=v.vendorid and
s.categoryid=c.categoryid and
s.deptid=d.deptid and
s.skuid=sl.skuid and
s.classid*=cl.classid and /* May not be a Class Outer Join */
s.size1id*=sz.sizeid and /* May not be a Size Outer Join - presume only first size is used */
s.colorid*=clr.colorid and /* May not be a Color Outer Join */
/*(sl.OHUnits-sl.PRUnits-sl.LYUnits-2)>0 and */ /* available must be > 2 */
(sl.OHUnits-sl.PRUnits-sl.LYUnits)>0 and
w.PublishToWeb=1 and
sl.location=1 and /* JBJ has only 1 Location */
(s.SalePrice=0 or s.saleprice is null
or getdate() < s.SaleStartDt or getdate() > s.SaleEndDt )

Union /* Pulls the two result sets together */

/* GET SKUS THAT ARE ON SALE HERE */
select s.sku SKU,
s.Description Description,
CONVERT(DECIMAL(9,2),s.SalePrice) Price,
null Weight,
Replace(Replace(w.commentary,CHAR(13)+ CHAR(10),'<BR>'),Char(39),'') MarketingDescription,
'Yes' Taxable,
v.Company+' '+c.description SoftCartCategory,
null SoftCartTemplate,
null VendorNo,
null ListPrice,
null Graphic,
null Thumbnail,
null SoftCartAttributes,
ISNULL(s.vendstock,'none listed') vin,
/*CONVERT(DECIMAL(6,0),(sl.OHUnits-sl.PRUnits-LYUnits-2)) InventoryQuantity,*/
CONVERT(DECIMAL(6,0),(sl.OHUnits-sl.PRUnits-LYUnits)) InventoryQuantity,
v.company vn,
cl.description cl,
d.description dt,
c.description ct,
ISNULL(sz.description,'Not Applicable') Size1,
ISNULL(clr.description,'Not Applicable') Color,
'On Sale! Normally $'+CONVERT(VARCHAR(13),s.suretail) OnSale,
'Y' OnSaleNow

from SKU S,
VENDORS V,
CATEGORIES C,
DEPARTMENTS D,
CLASS CL,
SKU_LOCATION SL,
SKU_WEB_INFO W,
SIZES SZ,
COLORS CLR

wheres.skuid=sl.skuid and
s.skuid=w.skuid and
s.vendorid=v.vendorid and
s.categoryid=c.categoryid and
s.deptid=d.deptid and
s.skuid=sl.skuid and
s.classid*=cl.classid and /* May not be a Class Outer Join */
s.size1id*=sz.sizeid and /* May not be a Size Outer Join - presume only first size is used */
s.colorid*=clr.colorid and /* May not be a Color Outer Join */
/*(sl.OHUnits-sl.PRUnits-sl.LYUnits-2)>0 and /* available must be > 2 */*/
(sl.OHUnits-sl.PRUnits-sl.LYUnits)>0 and
w.PublishToWeb=1 and
sl.location=1 and /* JBJ has only 1 Location */
(s.SalePrice>0 or s.saleprice is not null) and
getdate() between s.SaleStartDt and SaleEndDt /* Presumes this export will be run daily be Darren */
go
--------------------------------------------------------------------

Thanks,

Darren

View 2 Replies


ADVERTISEMENT

Alter Table Alter Column In MSACCESS. How Can I Do It For A Decimal Field?

Jul 23, 2005

Hi people,I?m trying to alter a integer field to a decimal(12,4) field in MSACCESS 2K.Example:table : item_nota_fiscal_forn_setor_publicofield : qtd_mercadoria integer NOT NULLALTER TABLE item_nota_fiscal_forn_setor_publicoALTER COLUMN qtd_mercadoria decimal(12,4) NOT NULLBut, It doesn't work. A sintax error rises.I need to change that field in a Visual Basic aplication, dinamically.How can I do it? How can I create a decimal(12,4) field via script in MSACCESS?Thanks,Euler Almeida--Message posted via http://www.sqlmonster.com

View 1 Replies View Related

Alter Table Alter Column

Jul 20, 2005

I would like to add an Identity to an existing column in a table using astored procedure then add records to the table and then remove the identityafter the records have been added or something similar.here is a rough idea of what the stored procedure should do. (I do not knowthe syntax to accomplish this can anyone help or explain this?Thanks much,CBLCREATE proc dbo.pts_ImportJobsas/* add identity to [BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL/* add records from text file here *//* remove identity from BarCode Part#] */alter table dbo.ItemTestalter column [BarCode Part#] [int] NOT NULLreturnGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOhere is the original tableCREATE TABLE [ItemTest] ([BarCode Part#] [int] NOT NULL ,[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Description] DEFAULT (''),[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]DEFAULT (0),[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]DEFAULT (0),[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT(0),[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULLCONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT(getdate()),CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED([BarCode Part#]) ON [PRIMARY]) ON [PRIMARY]GO

View 2 Replies View Related

Alter Table Alter Column...

Oct 8, 2007

I am using sql server ce.I am changing my tables sometimes.how to use 'alter table alter column...'.for example:I have table 'customers', I delete column 'name' and add column 'age'.Now I drop Table 'customers' and create again.but I read something about 'alter table alter column...'.I use thi command but not work.I thing syntax not true,that I use..plaese help me?



my code:
Alter table customers alter column age

View 7 Replies View Related

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...



ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;


I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;







View 18 Replies View Related

ALTER

Jun 23, 2000

I have SQL Server 7.0 running on both development and production boxes. The syntax below runs fine on my development box, but I am getting an error on my production box. Thanks for your help

ALTER TABLE SUPPORTINFO
ALTER COLUMN STORENUMBER VARCHAR(20)

Error Message:

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'COLUMN'.

View 2 Replies View Related

Alter SP From SP

Apr 5, 2007

Hello everyone.

I want to make a stored procedure that alters another stored procedure.

Is there a way to do it?

I need to to be able to execute a string as sql:
Something like
execute statement 'ALTER PROCEDURE ....'

View 2 Replies View Related

ALTER ?

Nov 13, 2007

What is the ALTER statement for ?

for example in some stored procedures
you have

ALTER PROCEDURE (procedure name..)

View 4 Replies View Related

Need To Alter A DML Trigger

May 17, 2007

Hi
I'm trying to write a trigger to insert data into an archive file.  I added a new trigger using database explorer, wrote the trigger and then saved it.  The trigger has an error in it and I need to alter it. Can you tell me how to access the trigger ?
Many thanks
Chris
 

View 5 Replies View Related

Alter 'sa' Login

May 22, 2008

In my SQL SERVER 2005, sa login is enabled, despite Windows Authentication mode being set.
When I'm trying to change it to disabled, I get the error "cannot alter 'sa' login. it doesn't exist or you don't have the permission"
 How can I change the status?

View 8 Replies View Related

SQL Alter Collation Name

Dec 3, 2004

Hello:

I have a database in SQL with the following collate name: SQL_Latin1_General_CP1_CI_AS... I am trying to change the accent sensetive to accent insensitive... how would I do this? I tried re-installing the SQL and setting the default to CI_AI, but since the database that is backed up uses CI_AS, the DB settings overrides the default settings...

Any suggestions?

View 1 Replies View Related

Alter A Table All In C#

Feb 15, 2006

Is there a way to add a column to an existing table and do it all in C#If my query string is as follows how do I execute the query?ALTER TABLE interests ADD COLUMN Swim VARCHAR(1) NOT NULL DEFAULT('n')ThanksMoonWa

View 2 Replies View Related

Alter Column

Jul 15, 2002

Hi smart people!

I would like to know how to alter a column to have a default value. For instance I have a column AreaCode Char(3) in a table. I have data in the table and now I want to add a default value of '123' to the AreaCode column.

I tried the following but did not work.
Alter Table Phone
Alter Column AreaCode Char(3) Default '123'

Can we even do it using SQL?

Thanks

View 1 Replies View Related

ALTER TABLE

Aug 13, 2002

I think I have blown a gasket..... a elementary simple sp that will not work but I am unable to find an explanation as to why..... please help.


CREATE PROCEDUREbrsp_table_alteration
@var_1 varchar(45)
,@var_2varchar(45)
,@var_3varchar(30)
AS

ALTER TABLE @var_1
ADD@var_2@var_3NULL


Thanks.

View 3 Replies View Related

Alter Column Name

Jul 12, 2001

Is there any way to alter a column's name using Transact SQL and not the GUI interface?

View 1 Replies View Related

Alter Statement

May 15, 2000

Do anyone knows the syntax for changing the name of a column in a table with the
alter statement or any other statements????

Thanks in advance,
Vic

View 1 Replies View Related

Alter Statement

Jan 5, 2001

Hi

Is it possible to remove Identity property of a column using ALTER statement in SQL Server 7.0.

Thanks in advance

Rahul

View 2 Replies View Related

Alter Database

Jan 23, 2001

I am trying to move one of my database log to different location but I am geeting an error when I try to do this.

I am moving Registration DB log file from 'c:mssql7
egistrstion_log.ldf'
to 'e:sql
egistration_log.ldf'

Here the syntax I am using to do this:

alter database registration modify file (name='registration_log',filename= 'e:sql
egistration_log.ldf')

But I get the following error:
Server: Msg 5037, Level 16, State 1, Line 2
MODIFY FILE failed. Do not specify physical name.


Thank You,
John

View 1 Replies View Related

Alter Table ......

Sep 30, 2003

I have a table tblABC which exists in 800 databases. I want to run a cursor to turn off the pk field ID indentity permannetly and then to "insert tblABC select * from tblXYZ".

I have tried the "set indentity_insert tblABC on" statement and failed on insert tblABC select * from tblXYZ.

Is there any alter table related t-sql could permannetly turn off the identity?

thanks
David

View 7 Replies View Related

Alter Table

Jun 7, 2001

Is there anyway to Insert a new column between two existing columns without creating a new table?

Thanks

View 3 Replies View Related

Alter Table

Oct 17, 2001

Hi,
is it possible to override the system when adjusting
columns. i.e. a system override setting.

For instance if I've got a column that is indexed and I
want to adjust it from varchar(50) to varchar(51) it lets
me. But if I then try and adjust the column to varchar
(49) I get the error below:

Server: Msg 5074, Level 16, State 8, Line 1
The index 'LastName' is dependent on column 'lastname'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN lastname failed because one or
more objects access this column.

I asked this question on another Forum and was told I had to drop the index first. Just thought I'd ask for a second opinion.

Thanks for the help.

Steve

View 1 Replies View Related

Alter Column

Mar 15, 2006

what is the syntax for multiple column modifications ?

alter table abc
alter column x1 nvarchar(10) null

-- works


alter table abc
alter column x1 nvarchar(10) null,
alter column x2 nvarchar(10) null

-- doesn't work or do i have to use the alter table each time ?

View 1 Replies View Related

How To Alter A Column??

Nov 15, 2007

Hi,
I am new to SQL Server 2005. Please help
what is the problem with this code..?

ALTER TABLE AM_Master
ALTER COLUMN Last_Updated datetime NOT NULL DEFAULT getdate();
Go

The error is like

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.

View 8 Replies View Related

Alter Table

Jan 30, 2004

Hi to all!
I have a silly problem, but I'm not able to solve it!
I know how to add a field in a table via Enterprise manager and I know also via ALTER TABLE command.
...But the problem is: I want to add a field NOT in the last position, but on the middle (for istance)
So, this is possible via Enterprise Manager but with the ALTER TABLE command? How can I add a field without put it in the last position?
...Thanks a lot!
Sergio

p.s.: Sorry for my poor english..I hope you understand my question!

View 2 Replies View Related

Alter Columns?

Apr 1, 2004

Hello,

I am trying to edit several tables that were imported in tab-delimited format from text files. I am trying to generate a script that will alter the data type for several different columns.

I have succesfully edited a single column with the following code:
USE THCIC
ALTER TABLE PudfTest
ALTER COLUMN
DISCHARGE VARCHAR(6) NULL

However, I have need to create a script that will change the data type for over 100 columns. So far, everything I've read tells me that multiple 'alter column' statements cannot be run in a single query. I'm hoping someone can shed some light on this, or at least point me in another direction so that I won't have to manually change the data type for every column in each of the tables.

Any help would be greatly appreciated.
Thanks!

View 1 Replies View Related

Can I Alter A Table Specified By @name?

Jun 5, 2008

hello,
i am trying to add a column to every table in a database, if it (doesn't already exist), with the following code:

declare @name varchar(50)
declare c cursor for select name from sys.tables
open c
fetch next from c into @name
while @@FETCH_STATUS = 0
begin
if( (select count(*) from sys.columns
WHERE object_id IN (SELECT object_id FROM sys.tables WHERE name = @name )
and name='ins_id') = 0 )
begin
alter table @name add ins_id int
end

fetch next from c into @name
end
close c
deallocate c

and when i run the script i get the error message
Incorrect syntax near '@name'.
reffering to the alter table statement
So, my question is, how can i actually perform the alter table in this case?
thank you

View 3 Replies View Related

Need Help With The ALTER Command

Dec 16, 2005

Hello everyone,
I just started learning sql and I came across a slight problem.
Assume I have a customer table composed of the following fields:
Customer_Number,Sname,Street,City,Postcode,credit_lim,balance.

What I want to do is to drop the Street,city,and postcode fields and add the following: fax,email,and phone.

Is it possible to use the following command:

Alter table customer
Drop column street,city,postcode

Or should I do as following:

Alter table customer
Drop column street

Alter table customer
Drop column city

Alter table customer
Drop column postcode

And the same goes to adding the phone,fax,and email field.
Thank You.

NB: I need to use the Alter command and nothing else

View 10 Replies View Related

Alter Tables

Apr 20, 2006

Dear friends

I would like to alter columns so that I have have empty string records instead of null. How do I query all tables and all columns so I can alter the column properties programmatically?

Thanks

Cm

View 19 Replies View Related

Alter Database

Jul 31, 2006

Hi all this is my first of most likely many posts.
I am writting a stored proc that will be used on many diffrent named databases and i am setting the database name with a variable. I would like to use this variable in the following situation any clues on this would be greatly appreciated.

DECLARE @DBName varchar(50)
SET @DBName='Database'
alter database @DBName set recovery full

regards
Phil

View 4 Replies View Related

ALTER INDEX

Jan 2, 2007

hey guys for some reason when i run this:

USE WebPageLog;
GO
ALTER INDEX PK_TNS_report ON TNS_report
REBUILD;
GO

then i check index fragmentation its still the same 50% why is that?

View 3 Replies View Related

Alter Table

Mar 2, 2007

Pramod writes "I want to change the primary key field of my database to Identity seed. the table is with data. data should be secured
this is the structure
'=================================
CREATE TABLE [tblStock] (
[StockId] [numeric](18, 0) NOT NULL ,
[ItemID] [numeric](18, 0) NULL ,
[Batch] [varchar] (50) NULL ,
[Qty] [float] NULL ,
[Prate] [float] NULL ,
[SRate] [float] NULL ,
[Mrp] [float] NULL ,
[PTR] [float] NULL ,
[batchid] [numeric](18, 0) NULL ,
[BatchCode] [varchar] (50) NULL ,
CONSTRAINT [PK_tblStock] PRIMARY KEY NONCLUSTERED
(
[StockId]
) ON [PRIMARY] ,
CONSTRAINT [IX_tblStock] UNIQUE NONCLUSTERED
(
[ItemID],
[Batch],
[Prate]
) ON [PRIMARY]
) ON [PRIMARY]
===================================
How do i use alter table with identity seed
ALTER TABLE tblStock alter column StockID Numeric IDENTITY (1, 1)

how the p[rimary key affects

Please help me"

View 1 Replies View Related

How To Alter Sp_addtype

Mar 21, 2007

hi fys,
I have created a user defined datatype using the following commands
sp_addtype 'balance_qty','int','null'

it was successfully created

just now i want to modity the datatype to numeric(28,3).if make a drop
sp_droptype 'balance_qty'
it throws a error:
Cannot drop type 'balance_qty' because it is currently in use.

can u pls suggest how to ovecome the above solution:
i need balance_qty numeric(28,2)

View 1 Replies View Related

Alter Table Name

Sep 13, 2007

How to alter a table name by using Query Analyser

i tried like this..

ALTER TABLE of_info_grp
RENAME TO stp_info_grp_map

It is giving an error like this
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'TO'.

Some one help on this

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved