Problem With 'CREATE DEFAULT ...' Command
Sep 23, 2004
Hi,
Please help
The following command works ok:
create default [Zero] as 0
but the command:
if not exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[Zero]')
and OBJECTPROPERTY(id, N'IsDefault') = 1)
create default [Zero] as 0
returns:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'default'.
Env: Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Thanks
Helena
View 3 Replies
ADVERTISEMENT
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
Feb 13, 2004
Hi All,
Can someone tell me how to create a default that put the current date into a record on insert and current date + 1 year into another record!?
Cheers Wimmo
View 7 Replies
View Related
Dec 13, 2007
I want to create a default object if it does not exist in database.
if not exists (select name from sys.objects where name = 'dflt_zero')
create default dbo.dflt_zero as 0
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'default'.
But I am getting error.. the create default by itself works fine. How do I create this only when it does not exist?
Thanks!!
View 1 Replies
View Related
May 22, 2008
I Would like to create a schema sample and make it as default schema instead of dbo.
If a user logs in and creates a table like create table t1 (no int ) .
it would be assigned to sample schema and displayed as sample.t1 not dbo.t1.
How to set the user created schema as primary schema.
View 9 Replies
View Related
Jul 23, 2005
Hi I want to create a table with one column, which is a identitycolumn.Let's say like this:CREATE TABLE DefaultTable(N int identity(0,1))Then I want to fill this table with 15,000,000 records, so that I havea table with the numbers 0 to 14,999,999.How can I do this as fast as possible. A standard INSERT would take along time.(It can be a temp table or a table variable. I just need a list withnumbered 0 to 15,000,000)Thank you.Gidon
View 2 Replies
View Related
Apr 23, 2007
I went ahead and installed RS even though it was clear Microsoft install was confused (re: preivous post). Now I get to experience the thrill of configuration hell.
Report Server Virtual Directory had a green check. Unfortunately the Name block says <Not Set>. When I try to create a New... default directory I get the error message: "The virtual directory could not be created. The previously set virtual directory will still be used."
Does anyone know where the <Not Set> virtual directory is stored on the computer?
addl info:
ReportServicesConfigUI.WMIProvider.WMIProviderException: The virtual directory specified already exists. Specify a different name.
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)
(Vista Ultimate)
View 6 Replies
View Related
Aug 3, 2006
Hi,
I can't performe this:
use databaseX
CREATE LOGIN [abc]
WITH PASSWORD ='xxxxxxxxxxxxx',
DEFAULT_DATABASE = db_name()
or
DEFAULT_DATABASE = [ db_name() ] as well
I know that
DEFAULT_DATABASE = databaseX
works, but how can I create login with default database with db_name() ?
thanks,
View 6 Replies
View Related
Jul 25, 2006
I have a little application that I have designed where I need to be able to execute create table and create function comands against the database.
It seems that it does not like my sql file. Does anyone know of a different method of doing this?
Error message
Line 2: Incorrect syntax near 'GO'.
Line 4: Incorrect syntax near 'GO'.
Line 8: Incorrect syntax near 'GO'.
'CREATE FUNCTION' must be the first statement in a query batch.
Must declare the variable '@usb'.
Must declare the variable '@usb'.
Must declare the variable '@i'.
A RETURN statement with a return value cannot be used in this context.
Line 89: Incorrect syntax near 'GO'.
Line 91: Incorrect syntax near 'GO'.
Line 94: Incorrect syntax near 'GO'.
Protected Sub Install()
Dim err As String = ""
While err.Length < 1
' Dim your StreamReader
Dim TextFileStream As System.IO.TextReader
'Load the textfile into the stream
TextFileStream = System.IO.File.OpenText(Request.PhysicalApplicationPath & "Scripts .sql")
'Read to the end of the file into a String variable.
executesql(TextFileStream.ReadToEnd, err)
err = "Susscessful"
End While
If err = "Susscessful" Then
Response.Redirect("Default.aspx")
Else
Me.lblError.Text = err
End If
End Sub
Private Function executesql(ByVal s As String, ByRef err As String) As Boolean
Try
Dim conn As New Data.SqlClient.SqlConnection(GenConString())
Dim cmd As New Data.SqlClient.SqlCommand(s, conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Return True
Catch ex As Exception
err = ex.Message.ToString
Return False
End Try
End Function
Example sql file
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyFunc]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MyFunc]
GO
CREATE FUNCTION [dbo].[MyFunc]
(
-- Add the parameters for the function here
)
RETURNS varchar(1000)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(1000)
-- Add the T-SQL statements to compute the return value here
-- Do something here
-- Return the result of the function
RETURN @Result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 1 Replies
View Related
Aug 22, 2007
sqlplus for Oracle has a CREATE DATABASE LINK command. What is the equivalent functionality for MSSQL using sqlcmd or other script mechanism.
View 1 Replies
View Related
Jun 22, 2007
hi
can someone please explain the following please
why does this fail with specified owner testdb does not exist or you do not have permissions
create testdb.testtable (
testval int
)
yet if i create testdb and then run the following against this new db
create testtable (
testval int
)
command command completes successfully......what am i not understanding or doing wrong?
thank you
View 3 Replies
View Related
Mar 21, 2008
Hi All,
I have a requirement to create a dynamic SQL Command in an OLE DB Source due to the fact that I need to read data from another database based on a date range. For example, the SQL command would look like
SELECT * FROM Table1 WHERE DateField BETWEEN '17/03/2008' and '21/03/2008'
and I need to change the dates - '17/03/2008' and '21/03/2008' to different dates when the package is deployed in production, how do I do that ?
Regards
Ash
View 12 Replies
View Related
Jul 23, 2005
I'm trying to create a login in the default domain. I know I can pullthis information from xp_loginconfig, but don't see how I can use it inthe context of sp_grantlogin.For example, pull the domain the user is currently logged in on andinsert it into the sp_grantlogin script. Has anyone ever done this inthe past?
View 2 Replies
View Related
Nov 7, 2007
This is for SQL2k5. The database may be small or big, I don't know (it's going out to multiple customers). I'm wondering if in general it's considered "better" to create a single non-primary default filegroup and put all the objects there, or just leave everything in primary? In one training years back I got the impression that recovering the primary filegroup was important for certain restore operations, so it was always wise to separate them like this.
Thanks for your input,
Mike
MCDBA
View 4 Replies
View Related
Jan 15, 2005
Hi
Is there any why to Create SQL Server Objects from Command Prompts like (Databases , Tables, Stored Procedures, …) ??
If you will Install some Applications Like this forums you will see the SQL Server object Created from Command Prompts
How Can I do that .. ??
And thanks with my regarding
Fraas
View 1 Replies
View Related
Sep 7, 2006
This script will read the contents of a DB backup file, and generate a restore command.
Set the value of parameter @backup_path to point to the backup file, run in Query Analyzer, cut/paste the output into another Query Analyzer window, modify as necessary, and run.
This is just a barebones script to demo how this can be done. Modify as necessary to meet your own needs.
Works in SQL 2000 and 7.0. May work in SQL 2005, but it is not tested.
-- Create Restore Database Command from DB Backup File
set nocount on
go
declare @backup_path nvarchar(500)
select @backup_path =
-- Path to Backup file
'\SERVERNAMESHARE_NAMEMY_DB_BACKUP_FILENAME.BAK'
create table #header (
BackupNamenvarchar(128)null,
BackupDescriptionnvarchar(128)null,
BackupTypeintnot null,
ExpirationDatedatetimenull,
Compressedintnot null,
Positionintnot null,
DeviceTypeintnot null,
UserNamenvarchar(128)not null,
ServerNamenvarchar(128)not null,
DatabaseNamenvarchar(128)not null,
DatabaseVersionintnot null,
DatabaseCreationDatedatetimenot null,
BackupSizedecimal(28,0)not null,
FirstLsndecimal(28,0)not null,
LastLsndecimal(28,0)not null,
CheckpointLsndecimal(28,0)not null,
DatabaseBackupLsndecimal(28,0)not null,
BackupStartDatedatetimenot null,
BackupFinishDatedatetimenot null,
SortOrderintnot null,
CodePageintnot null,
UnicodeLocaleIdintnot null,
UnicodeComparisonStyleintnot null,
CompatibilityLevelintnot null,
SoftwareVendorIdintnull,
SoftwareVersionMajorintnull,
SoftwareVersionMinorintnull,
SoftwareVersionBuildintnull,
MachineNamenvarchar(128)not null,
Flagsintnull,
BindingIDuniqueidentifier null,
RecoveryForkIDuniqueidentifier null,
Collationnvarchar(128)null,
Seqintnot null
identity(1,1),
)
create table #filelist (
LogicalNamenvarchar(128)not null,
PhysicalNamenvarchar(128)not null,
Typenvarchar(10)not null,
FileGroupNamenvarchar(128)null,
Sizedecimal(28,0)not null,
MaxSizedecimal(28,0)not null,
Seqintnot null
identity(1,1),
)
insert into #header
exec ('restore HeaderOnly from disk = '''+@backup_path+''' ')
insert into #filelist
exec ('restore FilelistOnly from disk = '''+@backup_path+'''')
declare @tab varchar(1), @cr varchar(2)
select @tab = char(9), @cr = char(13)+Char(10)
select
[--Restore--]=
case
when a.Seq = 1
then
@cr+
@cr+'restore database '+c.DatabaseName+
@cr+'from disk ='+@cr+@tab+''''+
@backup_path+''''+@cr+'with'+@cr
else ''
end+
@tab+'move '''+a.LogicalName+
'''to '''+a.PhysicalName+''' ,'+
case
when a.Seq = b.Seq
then
@cr+@tab+'replace, stats = 5 , recovery'
else ''
end
from
#filelist a
cross join
( select Seq = max(b1.Seq) from #filelist b1 ) b
cross join
( select DatabaseName = max(c1.DatabaseName)
from #header c1 ) c
order by
a.Seq
go
drop table #header
drop table #filelist
Results, modify as needed:
--Restore--
--------------------------------------------------------------------
restore database MY_DB
from disk =
'\SERVERNAMESHARE_NAMEMY_DB_BACKUP_FILENAME.BAK'
with
move 'PRIMARY'to 'D:MSSQLDATAMY_DB_PRIMARY.MDF' ,
move 'DATA1'to 'D:MSSQLDATAMY_DB_DATA1.NDF' ,
move 'DATA2'to 'D:MSSQLDATAMY_DB_DATA2.NDF' ,
move 'LOG'to 'D:MSSQLDATAMY_DB_LOG.LDF' ,
replace, stats = 5 , recovery
CODO ERGO SUM
View 4 Replies
View Related
Aug 3, 2012
Any command which will make create script for my entire database including all sp's , tables, functions..i dont want to backup the db ..i want to create scripts for creating an entire database schema (only the structure and not data)
View 1 Replies
View Related
Apr 24, 2007
Scenario:
I have installed SQL Express 2005 and I want to create a new DB using sqlcmd.
I am running a command like: sqlcmd -i file.ini (I am logged like Administrator of PC)
the content of the file.ini is:
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = 'c:Program FilesMyTestdatabase'
-- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE DataCollection ON
( NAME = DC_dat,
FILENAME = '''+ @data_path + 'DC.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = DC_log,
FILENAME = '''+ @data_path + 'DClog.ldf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )'
) ;
GO
I obtain this error:
Msg 1802, Level 16, State 4, Server TESTQ5SQLEXPRESS, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5123, Level 16, State 1, Server TESTQ5SQLEXPRESS, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'c:Program FilesMyTestdatabaseDC.mdf'.
If I change the security of directory database (I add "modify" permission to Users windows group) the error disappear.
I am logged like Administrator... why is it necessary to change directory permission?
View 9 Replies
View Related
Sep 12, 2007
Hi all,
i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.
Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful
2. Creating the publication: executed as User2(db_owner)
2.1 publication.Create(); ... successful
2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with
agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by
agent.GenerateSnapshot();
And at this point,i got an error message, because the snapshot agent cant be executed ...
2007-09-12 12:05:46.58 User-specified agent parameter values:2007-09-12 12:05:46.58 --------------------------------------2007-09-12 12:05:46.60 -Publisher EDOM04SQLstandard2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest2007-09-12 12:05:46.60 -ReplicationType 22007-09-12 12:05:46.60 -Distributor EDOM04SQLstandard2007-09-12 12:05:46.60 -DistributorSecurityMode 12007-09-12 12:05:46.60 -PublisherSecurityMode 12007-09-12 12:05:46.60 --------------------------------------2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04SQLstandard'2007-09-12 12:05:46.96 The replication agent had encountered an exception.2007-09-12 12:05:46.96 Source: Replication2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command. Contact your system administrator.2007-09-12 12:05:46.96 Message Code: 142602007-09-12 12:05:46.96
Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role
This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!
Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo
View 6 Replies
View Related
Jan 5, 2007
I have a website I'm ready to test on the server it will call home. I just got connected to the remote SQL server that it will be using. As I've been creating the site, I've been using the default SQL Express set-up in Visual Studio. Is there a way to have Visual Studio create all those default tables, procedures, etc. OR is there a way to copy all of that stuff from the SQL Express running on my machine to the remote SQL Server 2005?
-Mathminded
View 4 Replies
View Related
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
Mar 11, 2015
when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?
View 5 Replies
View Related
Apr 13, 2007
Hi
I've never had to do this, but when I downloaded the Web Workflow Approvals Starter Kit, it requested that I install the database into a User Instance of .SQLEXPRESS.
Now the problem is, I've installed it onto a default instance, so I was wondering whether you can create a named instance on top of a default instance... and if so, how would you do that?
Cheers
Chris
View 3 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Aug 11, 2015
@pvColumnName VARCHAR(100) = Default,
However, I am unable to determine what is the value for Default. Is it '' ?
Default is not permitted as a constant - below fails to parse:
WHERE t2.TABLE_TYPE = 'BASE TABLE'
AND (@pvColumnName = Default OR t1.[COLUMN_NAME] Like @vColumnName)
View 4 Replies
View Related
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
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
Aug 30, 2004
Do somebody know how long (in chars) script(command) can be solved by SQL Command?
Thanks
View 1 Replies
View Related
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
Apr 3, 2008
Hi,
Does anyone have a workaround or know of a fix to this problem:
Default value set to 'date pick' from date currently within field by setting value equal to that field . ie if date is 01/01/2010 date picker opens in Jan 2010 - works ok.
However, once published to Sharepoint and run through browser the Date Picker ignores the default value and the date picker opens for today. ie April 2008.
Any words of wisdom gratefully recieved,
Howard Stiles
View 1 Replies
View Related
Aug 4, 2004
Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
View 14 Replies
View Related
May 20, 2008
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
View 3 Replies
View Related
