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


ADVERTISEMENT

ALTER Table Command

Jul 20, 2005

Hi,I'm trying to run the ALTER TABLE command using a dynamic string for thetable, like so:DECLARE @TableName CHARSET @TableName = 'Customers'ALTER TABLE @TableNameADD ...blahIs this possible? We know this works:ALTER TABLE Customers ADD ...blahIt looks like I need a way to convert the CHAR value to a literal or perhapseven a table ID?Thanks in advance,Paul

View 3 Replies View Related

Help Me Please I Have A Problem In ALTER Command

Sep 3, 2007



Dear all
I need to add new column in table TranPay , I use ALTER command on PDA on Button Click




Dim SqlStm = "ALTER TABLE TranPay ALTER COLUMN TranKey IDENTITY (1, 1)"

Dim Dcmd As SqlCeCommand = New SqlCeCommand

Dcmd.Connection = Conn

Dcmd.CommandText = SqlStm

Dcmd.ExecuteNonQuery()


But cannot create new Field , Help me please

Brg ,

Tingnong

View 3 Replies View Related

Adding The Column By Using The Alter Command.

Jun 1, 2008

ALTER TABLE customers
add column active_flg int(1)

but i got error.

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

what should i do?

ps. thanks for warning... i won't edit the post after i got the answer anymore.

View 6 Replies View Related

Add A Column Trough Command (ALTER)

Sep 12, 2013

I'm trying to add a column trough command "ALTER" and I've tried in many different ways.

Here is the result of statement..

View 1 Replies View Related

Extend Alter Procedure Command

Jun 27, 2007

Is there any way to extend the Alter Procedure command or fire somekind of event when it is executed in SQL Server 2005?

Thanks,

David

View 9 Replies View Related

Simple ALTER Command Setting DEFAULT Help

Apr 21, 2008

Hi all, I am trying to do a very basic ALTER Command and am trying to change its DEFAULT value. Code below is what I currently have:





Code Snippet

ALTER TABLE Table_1

ALTER COLUMN TEST VARCHAR(1000) NULL DEFAULT 2

Thanks, Onam.

*UPDATE* I found this code but are there alternative methods? Additionally, if I was to update its DEFAULT value again how would I go about doing that? Do I first have to remove the CONSTRAINT and then run the command?





Code Snippet

ALTER TABLE Table_1 ADD CONSTRAINT DF_Albumns_Comment DEFAULT 2 FOR TEST

View 8 Replies View Related

SQL 2012 :: Alter Database Set Partner Command Failed

Jun 23, 2014

Running this query in DR server to start SQL mirroring but encountered an error below.

Query:
use master
go
alter database test set partner= N'TCP://HOSTNAME.DOMAIN.GROUP.INTRANET:5023'
go

Error:
Msg 1452, Level 16, State 6, Line 2

The partner server instance name must be distinct from the server instance that manages the database. The ALTER DATABASE SET PARTNER command failed.

View 2 Replies View Related

SQL 2005 Won't Start After Moving Model Database With Alter DB Command

Sep 11, 2007

I used the code below to move the Model database and Model log file to new SAN drive locations.
After running the commands I stopped the SQL server and moved the physical files to the correct location.
Now I am unable to start SQL server, I get error 17204 Could not open file:Access is denied

I think the problem maybe that for FILENAME new_path I put "J:SQL ServerMSSQL.1MSSQLData"
but did not include model.mdf in the file path.

How do I correct the path now that SQL service won't start?
I have included the log file below.



ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path/os_file_name' )


2007-09-11 13:19:31.82 Server Microsoft SQL Server 2005 - 9.00.3042.00 (X64)
Feb 10 2007 00:59:02
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
2007-09-11 13:19:31.82 Server (c) 2005 Microsoft Corporation.
2007-09-11 13:19:31.82 Server All rights reserved.
2007-09-11 13:19:31.82 Server Server process ID is 3264.
2007-09-11 13:19:31.82 Server Authentication mode is MIXED.
2007-09-11 13:19:31.82 Server Logging SQL Server messages in file 'D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG'.
2007-09-11 13:19:31.82 Server This instance of SQL Server last reported using a process ID of 2648 at 9/11/2007 1:10:02 PM (local) 9/11/2007 8:10:02 PM (UTC). This is an informational message only; no user action is required.
2007-09-11 13:19:31.82 Server Registry startup parameters:
2007-09-11 13:19:31.82 Server -d D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf
2007-09-11 13:19:31.82 Server -e D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG
2007-09-11 13:19:31.82 Server -l D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
2007-09-11 13:19:31.84 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2007-09-11 13:19:31.84 Server Detected 4 CPUs. This is an informational message; no user action is required.
2007-09-11 13:19:31.96 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2007-09-11 13:19:31.98 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-09-11 13:19:32.00 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2007-09-11 13:19:32.00 Server Database mirroring has been enabled on this instance of SQL Server.
2007-09-11 13:19:32.00 spid5s Starting up database 'master'.
2007-09-11 13:19:32.09 spid5s SQL Trace ID 1 was started by login "sa".
2007-09-11 13:19:32.10 spid5s Starting up database 'mssqlsystemresource'.
2007-09-11 13:19:32.10 spid5s The resource database build version is 9.00.3042. This is an informational message only. No user action is required.
2007-09-11 13:19:32.26 spid5s Server name is 'SS02'. This is an informational message only. No user action is required.
2007-09-11 13:19:32.26 spid9s Starting up database 'model'.
2007-09-11 13:19:32.26 spid9s Error: 17207, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB:pen: Operating system error 5(Access is denied.) occurred while creating or opening file 'J:SQL ServerMSSQL.1MSSQLData'. Diagnose and correct the operating system error, and retry the operation.
2007-09-11 13:19:32.26 spid9s Error: 17204, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB:pen failed: Could not open file J:SQL ServerMSSQL.1MSSQLData for file number 1. OS error: 5(Access is denied.).
2007-09-11 13:19:32.26 spid9s Error: 5120, Severity: 16, State: 101.
2007-09-11 13:19:32.26 spid9s Unable to open the physical file "J:SQL ServerMSSQL.1MSSQLData". Operating system error 5: "5(Access is denied.)".
2007-09-11 13:19:32.26 spid9s Error: 17207, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB:pen: Operating system error 5(Access is denied.) occurred while creating or opening file 'I:SQL ServerMSSQL.1MSSQLData'. Diagnose and correct the operating system error, and retry the operation.
2007-09-11 13:19:32.26 spid9s Error: 17204, Severity: 16, State: 1.
2007-09-11 13:19:32.26 spid9s FCB:pen failed: Could not open file I:SQL ServerMSSQL.1MSSQLData for file number 2. OS error: 5(Access is denied.).
2007-09-11 13:19:32.26 spid9s Error: 5120, Severity: 16, State: 101.
2007-09-11 13:19:32.26 spid9s Unable to open the physical file "I:SQL ServerMSSQL.1MSSQLData". Operating system error 5: "5(Access is denied.)".
2007-09-11 13:19:32.26 spid9s File activation failure. The physical file name "I:SQL ServerMSSQL.1MSSQLData" may be incorrect.
2007-09-11 13:19:32.26 spid9s Error: 945, Severity: 14, State: 2.
2007-09-11 13:19:32.26 spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2007-09-11 13:19:32.26 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2007-09-11 13:19:32.26 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

View 4 Replies View Related

T-SQL (SS2K8) :: MASTER And MSDB - Defrag Using Standard Alter Index Command?

Oct 10, 2014

On a 2008r2 server, I ran the frag utility against master and msdb and noticed they were severely fragmented.

Is it ok to defrag them using the standard Alter Index command?

View 7 Replies View Related

T-SQL (SS2K8) :: Use Variable In ALTER / CREATE PARTITION SCHEME Command For Filegroup?

May 9, 2010

Is it possible to use a variable to specify the filegroup in the ALTER/CREATE PARTITION SCHEME command?

I want the partition scheme to use the default filegroup for ALTER and CREATE PARTITION SCHEME. At the time the script is created, I don't know the default filegroup in the database.

My code:

declare @fileGroupName VARCHAR(50) = (select top 1 name from
sys.filegroups where is_default = 1)
ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName

Is failing:

Incorrect syntax near '@fileGroupName'.

Q: Is it possible to use a variable for the filegroup in the ALTER/CREATE commands? Is so, what is the correct syntax?

Q: If using a variable is not possible, is there another way to specify the default filegroup?

View 2 Replies View Related

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

Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE

Feb 23, 2007

i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()        test.InsertCommandType = SqlDataSourceCommandType.Text        test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) "                  test.InsertParameters.Add("roll", TextBox1.Text)        test.InsertParameters.Add("name", TextBox2.Text)        test.InsertParameters.Add("age", TextBox3.Text)        test.InsertParameters.Add("email", TextBox4.Text)        test.Insert() i am using UPDATE command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()        test.UpdateCommandType = SqlDataSourceCommandType.Text        test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll                                                         123 "        test.Update()but i have to use the SELECT command like this which is completely different from INSERT and  UPDATE commands   Dim tblData As New Data.DataTable()         Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated                                                                                Security=True;User Instance=True")   Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn)   Dim da As New Data.SqlClient.SqlDataAdapter(Command)   da.Fill(tblData)   conn.Close()                   TextBox4.Text = tblData.Rows(1).Item("name").ToString()        TextBox5.Text = tblData.Rows(1).Item("age").ToString()        TextBox6.Text = tblData.Rows(1).Item("email").ToString()       for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me

View 2 Replies View Related

Using A Variable In SSIS - Error - Command Text Was Not Set For The Command Object..

Nov 4, 2006

Hi All,

i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,

enterName - String packageLevel (will store the name I enter)

myVar - String packageLevel. (to store the query)

I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"

Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.

Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

Can Someone guide me whr am going wrong?

myVar variable, i have set the ExecuteAsExpression Property to true too.

Please let me know where am going wrong?

Thanks in advance.








View 12 Replies View Related

Do Somebody Know How Long (in Chars) Script(command) Can Be Solved By SQL Command?

Aug 30, 2004

Do somebody know how long (in chars) script(command) can be solved by SQL Command?
Thanks

View 1 Replies View Related

What Command Is Used To Get Back The Privileges Offered By The GRANT Command?

Mar 10, 2007

reply.

View 1 Replies View Related

Command Text Was Not Set For The Command Object Error

Sep 19, 2006

Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:

Description: An OLE DB Error has occured. Error code: 0x80040E0C.

An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object".

.........

Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".

This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:

private bool BuildPackage()

{




// Create the package object

oPackage = new Package();

// Create connections for the Foxpro and SQL Server data

Connections oPkgConns = oPackage.Connections;

// Foxpro Connection

ConnectionManager oFoxConn = oPkgConns.Add("OLEDB");

oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere

oFoxConn.Name = "SourceConnectionOLEDB";

oFoxConn.Description = "OLEDB Connection For Foxpro Database";

// SQL Server Connection

ConnectionManager oSQLConn = oPkgConns.Add("OLEDB");

oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere

oSQLConn.Name = "DestinationConnectionOLEDB";

oSQLConn.Description = "OLEDB Connection For SQL Server Database";

// Add Prepare SQL Task

Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask");

TaskHost thSQLTask = exSQLTask as TaskHost;

thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn");

thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true);

thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None);

thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @"C:LPFMigrateLPF_Script.sql");

thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection);

thSQLTask.FailPackageOnFailure = true;



// Add Data Flow Tasks. Create a separate task for each table.

// Get a list of tables from the source folder

arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF");

for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++)

{


// Get the name of the file from the array

sDataFile = Path.GetFileName(arFiles[iCount].ToString());

sDataFile = sDataFile.Substring(0, sDataFile.Length - 4);

oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;

oDataFlow.AutoGenerateIDForNewObjects = true;



// Create the source component

IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New();

oSource.Name = (sDataFile + "Src");

oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";

// Get the design time instance of the component and initialize the component

CManagedComponentWrapper srcDesignTime = oSource.Instantiate();

srcDesignTime.ProvideComponentProperties();

// Add the connection manager

if (oSource.RuntimeConnectionCollection.Count > 0)

{


oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID;

oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn);

}

// Set Custom Properties

srcDesignTime.SetComponentProperty("AccessMode", 0);

srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true);

srcDesignTime.SetComponentProperty("OpenRowset", sDataFile);

// Re-initialize metadata

srcDesignTime.AcquireConnections(null);

srcDesignTime.ReinitializeMetaData();

srcDesignTime.ReleaseConnections();

// Create Destination component

IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New();

oDestination.Name = (sDataFile + "Dest");

oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

// Get the design time instance of the component and initialize the component

CManagedComponentWrapper destDesignTime = oDestination.Instantiate();

destDesignTime.ProvideComponentProperties();

// Add the connection manager

if (oDestination.RuntimeConnectionCollection.Count > 0)

{


oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID;

oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn);

}

// Set custom properties

destDesignTime.SetComponentProperty("AccessMode", 2);

destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);

destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]");



// Create the path to link the source and destination components of the dataflow

IDTSPath90 dfPath = oDataFlow.PathCollection.New();

dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]);

// Iterate through the inputs of the component.

foreach (IDTSInput90 input in oDestination.InputCollection)

{


// Get the virtual input column collection

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the column collection

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{


// Call the SetUsageType method of the design time instance of the component.

destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);

}

//Map external metadata to the inputcolumn

foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)

{


IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New();

externalColumn.Name = inputColumn.Name;

externalColumn.Precision = inputColumn.Precision;

externalColumn.Length = inputColumn.Length;

externalColumn.DataType = inputColumn.DataType;

externalColumn.Scale = inputColumn.Scale;

// Map the external column to the input column.

inputColumn.ExternalMetadataColumnID = externalColumn.ID;

}

}

}

// Add precedence constraints to the package executables

PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]);

pcTasks.Value = DTSExecResult.Success;

for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++)

{


pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]);

pcTasks.Value = DTSExecResult.Success;

}

// Validate the package

DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null);

// Check if the package was successfully executed

if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure))

{


string sErrorMessage = "";

foreach (DtsError pkgError in oPackage.Errors)

{


sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + "";

sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + "";

sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + "";

sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + "";

sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + "";

sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + "";

sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + "";

sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode;

}

MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);

return false;

}

// return a successful result

return true;
}

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

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







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