I used Tasks, Import Data to import a CSV file. During the process it asks if you want to save the SSIS. I did.
The import was successful. T^hen I opened up the SSIS package and was going to run it. But I see no way to ececute it. How do I exeute SSIS created by wizerd?
hi,
I am using CLR stored procedure having my dmx query.I need to exeute that Query and get the resultset as dataset?how to do this?
Here is my code sample
namespace PredictPerformance
{
class perf
{
public static dataset getPredict(int StudID)
{
----
return ds;
}
}
}
Execute the proc:
SELECT PredictPerformance.getPredict(1) FROM [Stud_Model]
how to get the result?
Thanks,
Karthik.
Run the SSIS import/export wizard.
(xls -> sql table)
Select the tasks created.
Copy.
Here is the error that occurs.
p.s. Does anyone have any needles I can borrow? I think sticking them in my eyes would be nicer than working with SSIS.
===================================
An error occurred while objects were being copied. SSIS Designer could not serialize the SSIS runtime objects. (Microsoft Visual Studio)
===================================
Could not copy object 'Preparation SQL Task' to the clipboard.
(Microsoft.DataTransformationServices.Design)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.DataTransformationServices.Design.SR&EvtID=SerializeComponentsFailed&LinkId=20476
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
at Microsoft.DataTransformationServices.Design.ControlFlowClipboardCommandHelper.InternalMenuCopy(MenuCommand sender, CommandHandlingArgs args)
===================================
Invalid access to memory location. (Exception from HRESULT: 0x800703E6) (Microsoft.SqlServer.ManagedDTS)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.PersistImpl.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events)
at Microsoft.SqlServer.Dts.Runtime.DtsContainer.SaveToXML(XmlDocument& doc, XmlNode node, IDTSEvents events)
at Microsoft.DataTransformationServices.Design.DtsClipboardCommandHelper.SerializeRuntimeObjects(ICollection logicalObjects)
Hi,
I just created my first SSIS package.
For now, I can only run it inside Visual Studio.
I want this packet to be run every day as a scheduled task. How do I do that?
When I double click my .dtsx I get an Execute Package Utility. Where can I set my package to run every day at certain time?
Thank you.
I am using SQL Server 2005 Version 9.00.1399.06 (Intel X86).
Whether I try to start a new Integration Services project in Visual Studio or try to import data into a database I receive the following error:
SSIS Runtime Object could not be created. Verify the DTS.dll is available and registered. The wizard can not continue and will terminate.
Unable to cast COM of type 'Microsoft.SqlServer.DTS.Runtime.Wrapper.PackageNeutralClass' to interface type
'Microsoft .SqlServer.DTS.Runtime.Wrapper.IDTSContainer90'
This operation failed because the QueryInterface call on the COM component for the interface failed due to the following error: Library not registered. (Exception from HRESULT: 0X80028010) (TYPE_E_LIBNOTREGISTERED)).
I have no idea why I am getting this error and would appreciate any help. I'm sure some of you SQL Server Gurus will know right away.
I am using SQL Server 2005 Version 9.00.1399.06 (Intel X86).
Whether I try to start a new Integration Services project in Visual Studio or try to import data into a database I receive the following error:
SSIS Runtime Object could not be created. Verify the DTS.dll is available and registered. The wizard can not continue and will terminate.
Unable to cast COM of type 'Microsoft.SqlServer.DTS.Runtime.Wrapper.PackageNeutralClass' to interface type
'Microsoft .SqlServer.DTS.Runtime.Wrapper.IDTSContainer90'
This operation failed because the QueryInterface call on the COM component for the interface failed due to the following error: Library not registered. (Exception from HRESULT: 0X80028010) (TYPE_E_LIBNOTREGISTERED)).
I have no idea why I am getting this error and would appreciate any help. I'm sure some of you SQL Server Gurus will know right away.
Hi,
I created a SSIS Package programatically based on the few threads here in this forum. This package just has a data flow task, during data transfer for every 1000 rows or so I want to update the status in a table in the database.
How do I achieve this?
As of now I just have a source and a destination, no transformations in between the flow. I'm not sure if rowcount will help, when I tried it using a onprogress even handler it always showed up as zero.
Thanks
I created a few in SqlServer2005 DEV version and ran them manually with success. But where are they? I chose the SqlServer storage option and I see them when I create Job and steps but how do I view/edit these packages from SqlServer Mgmt. Studio? The SSIS service is running.
View 6 Replies View RelatedI have installed the SQL Standard Client install on my laptop which has windows xpp using the following batch file.
Start /wait setup.exe /qn ADDLOCAL=SQL_DTS,Client_Components,Connectivity,SQL_Tools90,SDK,SQL_WarehouseDevWorkbench,SQLXML,Tools_Legacy,SQL_Documentation,SQL_BooksOnline
I am yet to connect to any datasource on my SSIS package. But when I right click on the connection Managers box in my SSIS project and select €œNew OLE DB Connection€? (or any other connection type), I get an Error as below. Please Help to resolve this error.
The new connection manager could not be created.
Additional information:
The service System.Windows.Forms.Design.IUIService could not be located.
(Microsoft.DataTransforamtionServices.Design).
if someone can throw light regarding the metadata repository creation,its usuage , it will help me out.
View 5 Replies View RelatedWe have a scenario to process last created/modified files from a location using SSIS package , eventhough the folder contains multiple files with same name and extension.
Kindly give respond to this if any one has worked on this.
Regards,
Sajesh
I built a packaage in SSIS with the import/export utility. It created a Package.dtsx and Package1.dtsx. Both of these files seem to be XML files. I want to understand how these files work. For example, in the package I built I had about 80 tables exporting and importing data. Some of them I want to allow the identiy insert and delete the rows first. Others I want to append the data. How can I find the code or settings that does this? Or where can I find the options on the gui interface to change these settings. When I search the code I can't even find a some of the tables that are being transferred.
View 20 Replies View RelatedHi everyone,
I wanted to thank everyone for posting a ton of valuable information in these forums. I also want to thank all the moderators that have been replying with really insightful help!
I am trying to programmatically create an SSIS package to take .CSV data and put it into a SQL Server 2005. I am assuming that this is pretty common scenario.
I have used many of the examples in this forum as well as heavily borrowing from this example http://www.codeproject.com/csharp/Digging_SSIS_object_model.asp written by Moim Hossain.
I can get my package to create and execute properly but no data is being written to the SQL Server table. This has puzzled me for the last 2 days!
I know the issue isnt with the server itself because I tested it by graphically creating a test SSIS package and it transfers the .CSV data to the table perfectly.
Would anyone know why this would happen? The Execution results are returning success but no data is written to the table!
Could anyone please provide insight as to what my issue may be?
Thanks in advance!
Code Snippet
using System;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
namespace SumCodeApp
{
class SumCodeApp
{
// Variables.
private Package package;
private ConnectionManager flatFileConnectionManager;
private ConnectionManager destinationDatabaseConnectionManager;
private Executable dataFlowTask;
private List<String> srcColumns;
int file_count;
SqlConnection connection;
String folder_path;
String username;
String password;
String DB_server;
String catalog;
// Default Constructor.
public SumCodeApp()
{
}
// Constructor taking in user info.
public SumCodeApp(String folder_path, String username, String password,
String DB_server, String catalog)
{
this.folder_path = folder_path;
this.username = username;
this.password = password;
this.DB_server = DB_server;
this.catalog = catalog;
}
private void CreatePackage()
{
package = new Package();
package.CreationDate = DateTime.Now;
package.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive;
package.Name = "SumCode Package";
package.Description = "Upload the SumCode files to the database";
package.DelayValidation = true;
package.PackageType = DTSPackageType.DTSDesigner90;
}
private void CreateFlatFileConnection()
{
String flatFileName = ". 1105.csv";
String flatFileMoniker = "FLATFILE";
flatFileConnectionManager = package.Connections.Add(flatFileMoniker);
flatFileConnectionManager.Name = "SSIS Connection Manager for Files";
flatFileConnectionManager.Description = String.Concat("SSIS Connection Manager");
flatFileConnectionManager.ConnectionString = flatFileName;
// Set some common properties of the connection manager object.
//flatFileConnectionManager.Properties["ColumnNamesInFirstRow"].SetValue(flatFileConnectionManager, false);
flatFileConnectionManager.Properties["Format"].SetValue(flatFileConnectionManager, "Delimited");
flatFileConnectionManager.Properties["TextQualifier"].SetValue(flatFileConnectionManager, """);
flatFileConnectionManager.Properties["RowDelimiter"].SetValue(flatFileConnectionManager, "
");
flatFileConnectionManager.Properties["DataRowsToSkip"].SetValue(flatFileConnectionManager, 0);
// Create the source columns into the connection manager.
CreateSourceColumns();
}
private void CreateSourceColumns()
{
// Get the actual connection manager instance
RuntimeWrapper.IDTSConnectionManagerFlatFile90 flatFileConnection = flatFileConnectionManager.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile90;
RuntimeWrapper.IDTSConnectionManagerFlatFileColumn90 column;
RuntimeWrapper.IDTSName90 name;
// Fill the source column collection.
srcColumns = new List<String>();
srcColumns.Add("CreateDate");
srcColumns.Add("CorpID");
srcColumns.Add("SumCodeID");
srcColumns.Add("Priority");
srcColumns.Add("SumCodeAbv");
srcColumns.Add("SumCodeDesc");
srcColumns.Add("SumCodeGroupID");
foreach (String colName in srcColumns)
{
column = flatFileConnection.Columns.Add();
if (srcColumns.IndexOf(colName) == (srcColumns.Count - 1))
//column.ColumnDelimiter = "
";
column.ColumnDelimiter = "{CR}{LF}";
else
//column.ColumnDelimiter = ",";
column.ColumnDelimiter = "Comma {,}";
name = (RuntimeWrapper.IDTSName90)column;
name.Name = colName;
column.TextQualified = true;
column.ColumnType = "Delimited";
column.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
column.ColumnWidth = 0;
column.MaximumWidth = 255;
column.DataPrecision = 0;
column.DataScale = 0;
}
}
private void CreateDestinationDatabaseConnection()
{
destinationDatabaseConnectionManager = package.Connections.Add("OLEDB");
destinationDatabaseConnectionManager.Name = "Destination Connection - SumCodeCorpGroup";
destinationDatabaseConnectionManager.Description = "Connection to the temporary table SumCodCorpGroup";
destinationDatabaseConnectionManager.ConnectionString = "Data Source=DIVWL-356KCB1;Initial Catalog=SumCode;Provider=SQLOLEDB;Persist Security Info=True;User ID=sum;Password=code";
}
public class Column
{
private String name;
private Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType dataType;
private int length;
private int precision;
private int scale;
private int codePage = 0;
public String Name
{
get { return name; }
set { name = value; }
}
public Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType DataType
{
get { return dataType; }
set { dataType = value; }
}
public int Length
{
get { return length; }
set { length = value; }
}
public int Precision
{
get { return precision; }
set { precision = value; }
}
public int Scale
{
get { return scale; }
set { scale = value; }
}
public int CodePage
{
get { return codePage; }
set { codePage = value; }
}
}
private Column GetTargetColumnInfo(string sourceColumnName)
{
Column cl = new Column();
if (sourceColumnName.Contains("CreateDate"))
{
cl.Name = "CreateDate";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (
sourceColumnName.Contains("CorpID"))
{
cl.Name = "CorpID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeID"))
{
cl.Name = "SumCodeID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("Priority"))
{
cl.Name = "Priority";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeAbv"))
{
cl.Name = "SumCodeAbv";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeDesc"))
{
cl.Name = "SumCodeDesc";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
else if (sourceColumnName.Contains("SumCodeGroupID"))
{
cl.Name = "SumCodeGroupID";
cl.DataType = Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR;
cl.Precision = 0;
cl.Scale = 0;
cl.Length = 255;
cl.CodePage = 1252;
}
return cl;
}
private void CreateDataFlowTask()
{
String dataFlowTaskMoniker = "DTS.Pipeline";
dataFlowTask = package.Executables.Add(dataFlowTaskMoniker);
}
public void ImportFile(String directory_path)
{
// Create the package.
CreatePackage();
// Create Flat File Source Connection.
CreateFlatFileConnection();
// Create Database Destination Connection.
CreateDestinationDatabaseConnection();
// Create DataFlowTask.
CreateDataFlowTask();
// Create the DataFlowTask
PipeLineWrapper.IDTSComponentMetaData90 sourceComponent = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
sourceComponent.Name = "Source File Component";
sourceComponent.ComponentClassID = "DTSAdapter.FlatFileSource";
PipeLineWrapper.CManagedComponentWrapper managedFlatFileInstance = sourceComponent.Instantiate();
managedFlatFileInstance.ProvideComponentProperties();
sourceComponent.RuntimeConnectionCollection[0].ConnectionManagerID = flatFileConnectionManager.ID;
sourceComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(flatFileConnectionManager);
managedFlatFileInstance.AcquireConnections(null);
managedFlatFileInstance.ReinitializeMetaData();
Dictionary<String, int> outputColumnLineageIDs = new Dictionary<String, int>();
PipeLineWrapper.IDTSExternalMetadataColumn90 exOutColumn = null;
foreach (PipeLineWrapper.IDTSOutputColumn90 outColumn in sourceComponent.OutputCollection[0].OutputColumnCollection)
{
exOutColumn = sourceComponent.OutputCollection[0].ExternalMetadataColumnCollection[outColumn.Name];
managedFlatFileInstance.MapOutputColumn(sourceComponent.OutputCollection[0].ID, outColumn.ID, exOutColumn.ID, true);
outputColumnLineageIDs.Add(outColumn.Name, outColumn.ID);
}
managedFlatFileInstance.ReleaseConnections();
String a = sourceComponent.RuntimeConnectionCollection[0].Name.ToString();
String b = sourceComponent.OutputCollection[0].Name;
String c = sourceComponent.OutputCollection[0].Description;
String d = sourceComponent.OutputCollection[0].OutputColumnCollection.Count.ToString();
// Create DataFlowTask Destination Component.
PipeLineWrapper.IDTSComponentMetaData90 destinationComponent = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).ComponentMetaDataCollection.New();
destinationComponent.Name = "OLEDB SQL Connection";
destinationComponent.ComponentClassID = "DTSAdapter.OLEDBDestination";
PipeLineWrapper.CManagedComponentWrapper managedOleInstance = destinationComponent.Instantiate();
managedOleInstance.ProvideComponentProperties();
// Create a path and attach the output of the source to the input of the destination.
PipeLineWrapper.IDTSPath90 path = ((dataFlowTask as TaskHost).InnerObject as PipeLineWrapper.MainPipe).PathCollection.New();
path.AttachPathAndPropagateNotifications(sourceComponent.OutputCollection[0], destinationComponent.InputCollection[0]);
destinationComponent.RuntimeConnectionCollection[0].ConnectionManagerID = destinationDatabaseConnectionManager.ID;
destinationComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(destinationDatabaseConnectionManager);
managedOleInstance.SetComponentProperty("AccessMode", 0);
managedOleInstance.SetComponentProperty("OpenRowset", "[SumCode].[dbo].[SumCodeCorpGroup]");
managedOleInstance.SetComponentProperty("AlwaysUseDefaultCodePage", false);
managedOleInstance.SetComponentProperty("DefaultCodePage", 1252);
managedOleInstance.SetComponentProperty("FastLoadKeepIdentity", false); // Fast load
managedOleInstance.SetComponentProperty("FastLoadKeepNulls", false);
managedOleInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", 0);
managedOleInstance.SetComponentProperty("FastLoadOptions","TABLOCK,CHECK_CONSTRAINTS");
managedOleInstance.AcquireConnections(null);
managedOleInstance.ReinitializeMetaData();
PipeLineWrapper.IDTSInput90 input = destinationComponent.InputCollection[0];
PipeLineWrapper.IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach (PipeLineWrapper.IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
//if (outputColumnLineageIDs.ContainsKey(vColumn.LineageID.ToString()))
//{
managedOleInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, Microsoft.SqlServer.Dts.Pipeline.Wrapper.DTSUsageType.UT_READONLY);
//}
}
List<String> tmp = new List<String>();
foreach(PipeLineWrapper.IDTSInputColumn90 inc in destinationComponent.InputCollection[0].InputColumnCollection)
{
tmp.Add(inc.Name);
}
PipeLineWrapper.IDTSExternalMetadataColumn90 exColumn;
foreach (PipeLineWrapper.IDTSInputColumn90 inColumn in destinationComponent.InputCollection[0].InputColumnCollection)
{
exColumn = destinationComponent.InputCollection[0].ExternalMetadataColumnCollection[inColumn.Name];
Column mappedColumn = GetTargetColumnInfo(exColumn.Name);
String destName = mappedColumn.Name;
exColumn.Name = destName;
managedOleInstance.MapInputColumn(destinationComponent.InputCollection[0].ID, inColumn.ID, exColumn.ID);
}
managedOleInstance.ReleaseConnections();
DTSExecResult result = package.Execute();
a = "0";
}
}
}
Hi all,
We are running SQL Server 2005 Standard edition on a Vista Business machine. Whenever we try to use DTS to import or export data we get this message:
The SSIS Data Flow Task could not be created. Verify that DTSPipeline.dll is available and registered.
Of course we have explicitly registered this dll and others.
Also we reinstalled SQL Server (and did it again. And did it again).
And we did the same for Visual Studio Standard (and again).
Any ideas in this elaborate group?
Thanks,
Tjerk
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.Â
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName]   Go  CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
I've been working on an SSIS package trying to load some data and the archive sequence is faulty. I've been trying to load a few tables created in a previous sequence into a local archive file and I've been getting the error "Could not find a part of the path."
The results aren't telling me what it's finding last and so I don't know where to start.
And the source DOES have data in it. It's something between the source and the destination.
Hi folks.
Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2
then execute bellow statements
select * from t1
this query gives you table with out data
select * from t2
you will recieve an error that there is no object
but if you rollback
T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Email Address:
myself@ramkistuff.8m.com
I've created a job using SQL DMO (in VB6) but it fails to run. When try to manual start the job from the Enterprise Manager, it gives error "cannot start job because it does not have any job server(s) defined."
Here is the code :
Dim oSQLServer As New SQLDMO.SQLServer
Dim oJobServer As New SQLDMO.JobServer
Dim oJob As New SQLDMO.Job
Dim oJobSch As New SQLDMO.JobSchedule
Dim oJobStep As SQLDMO.JobStep
oSQLServer.Connect "mc", "sa", ""
oJob.Name = "JOB_Backup"
oSQLServer.JobServer.Jobs.Add oJob
Set oJobStep = New SQLDMO.JobStep
With oJobStep
.Name = "Step 1"
.StepID = 1
.DatabaseName = "master"
.SubSystem = "TSQL"
.Command = "BACKUP DATABASE [medco] TO DISK = 'E:Projectmedco.BAK' With INIT"
.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess
End With
oJob.JobSteps.Add oJobStep
oJob.StartStepID = 1
'Set the schedule.
With oJobSch
.Name = "Schedule 1"
.Schedule.FrequencyType = SQLDMOFreq_Daily
.Schedule.FrequencyInterval = 1
.Schedule.ActiveStartDate = "20010315"
.Schedule.ActiveStartTimeOfDay = "150800"
.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME
End With
oJob.JobSchedules.Add oJobSch
oSQLServer.DisConnect
Set oJobStep = Nothing
Set oJobSch = Nothing
Set oJob = Nothing
Set oSQLServer = Nothing
Something is missing but ?
Thanks for all your helps in advance,
Ben
I'm buildng an application using the ADO.NET OleDB providor in order to maintain back end compatabiity with both SQL Server and Access Jet4.0. I need to get the Identity key of the new row created as a result of the following insert statement.
OLEDB COMMANT TEXT:
"insert into tblPerson (LastName,FirstName,SSN,BirthDate,Family) values( ?, ?,?,?,?)";
I assume I need to add a select statement immediately following the insert. Can anyone help?
PS - not using stored procedures due to Access jet4.0 limitation
I want to remove 2 big ldf files (400 mgb and 300 mgb) and start db with only one ldf.
I tried sp_attach_db specifing only mdf file but geting errors
-------
microsoft help PRB: sp_attach_single_file_db Does Not Work for Databases with Multiple Log Files (Q271223)
..... You cannot attach a database that has been created with multiple log files without also attaching all the log files.
----
how can I remove those big ldf files and create new ldf (defult(1mgb)
This procedure runs and I see my GM names come up one at a time but I get no tables created.
Sorry about the sloppy code - I'm not a real pro.
declare @@GM Char(100)
declare @SQL VarChar (2000)
Declare spot cursor scroll for
select GM from BIM_Historical_Performance.dbo.Performance_Master
open spot
fetch first from spot
into @@GM
While @@Fetch_Status =0
BEGIN
set @SQL = 'select Comp, Billto, Cust_name as Customer, Branch, BAC, [MgMt_Type], BondValue as Bondbucks , BondValueAlloc as Allocbucks, c1 as Curcode, u1 as CurUnderP$, s1 as Cur3mthBIMsales, p1 as performance, I1 as Inside, [IS_since] as Insidesince, O1 as Outside, [OS_since] as Outsidesince, c2 as CodeM-1, c3 as CodeM-2, c4 as CodeM-3, c5 as CodeM-4 ,c6 as CodeM-5, GM into ' +@@GM + ' from BIM_Historical_Performance.dbo.Performance_Master where BIM_Historical_Performance.dbo.Performance_Master.gm = ' +@@GM
EXEC (@Sql)
Fetch NExt from spot
End
I want to know who had created the new tables,How can I achieve it?
Can I know it by using the sysobjects table?
How Can I know who had created the table in my DB?
May I log it?
Hi,
How can i get the names of user created databases?
select Distinct (name) from sysobjects where xtype = 'U', i know this statement for tables but is there a statement for Databases?
thanks
Hi everyone.. I have a big problem.. :p
I have created a installer for my application and database.
When I run the installer it only creates the database but no tables created. But in some other workstation it works fine. I dont know what causes this problem....
Please guide me.
RON
________________________________________________________________________________________________
"I won't last a day without SQL"
ElementTypeDep_Cursor seems is not being created in this stored procedure:
Code Snippet
ALTER procedure spCopyTemplateElementTypesToIssues
@TemplateRecno integer,
@ProjRecNo integer,
@IssueRecNo integer
as
declare @ElementTypeRecno integer
declare @ElementTypeDepRecno integer
declare @ProjTypeRecno integer
declare @PreElementRecNo integer
declare @PostElementRecno integer
declare @Count integer
DECLARE element_Cursor CURSOR FOR
SELECT ElementTypeRecNo
FROM dbo.tblTemplateElementType
where TemplateRecno = @TemplateRecNo
OPEN element_cursor
FETCH NEXT FROM Element_Cursor into @ElementTypeRecno
--delete from tblElementCPO
WHILE @@FETCH_STATUS = 0
BEGIN
select @count = count (*)
from tblElementCPO
where ProjRecno = @ProjRecNo
and IssueRecno = @IssueRecno
and TemplateRecno = @TemplateRecno
and ElementTypeRecno = @ElementTypeRecNo
if @Count = 0
begin
insert into tblElementCPO(Ignore, ElementTypeRecno, IssueRecno,
ProjRecno, MaxAttemptNum, IsMileStone, ComponentOnly, Phase,
TaskHoursEst, TemplateRecNo, ChangeDate, ChangePerson)
values (0, @ElementTypeRecno, @IssueRecno,
@ProjRecno, 5,0,6,99,
99,@TemplateRecno, getdate(), current_user)
end
FETCH NEXT FROM element_Cursor into @ElementTypeRecno
END
CLOSE element_Cursor
DEALLOCATE element_Cursor
select @Count = count (*)
FROM dbo.tblElementTypeDep
where TemplateRecno = @TemplateRecNo
if @Count > 0 then
begin
DECLARE ElementTypeDep_Cursor CURSOR FOR
SELECT ElementTypeDepRecNo, PreElementTypeRecNo, PostElementTypeRecno
FROM dbo.tblElementTypeDep
where TemplateRecno = @TemplateRecNo
OPEN ElementTypeDep_cursor
FETCH NEXT FROM ElementTypeDep_Cursor
into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno
WHILE @@FETCH_STATUS = 0
BEGIN
select @Count = count (*)
from tblElementDepCPO
where ElementTypeDepRecno = @ElementTypeDepRecno
and PreElementRecNo = @PreElementRecNo
and PostElementRecno = @PostElementRecno
if @Count = 0
begin
insert tblElementDepCPO (ElementTypeDepRecno, PreElementRecNo,
PostElementRecno, ChangeDate, ChangePerson)
values (@ElementTypeDepRecno, @PreElementRecNo,
@PostElementRecno, getdate(), current_user)
end
FETCH NEXT FROM ElementTypeDep_Cursor
into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno
END
CLOSE elementTypeDep_Cursor
DEALLOCATE elementTypeDep_Cursor
end
called by
Code Snippet
Dim cnQI02414 As New SqlConnection(My.Settings.csQI02414Dev)
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
cmd.CommandText = "spCopyTemplateElementTypesToIssues"
cmd.CommandType = CommandType.StoredProcedure
Dim spmTemplateRecNo As SqlParameter = _
cmd.Parameters.Add("@TemplateRecNo", SqlDbType.Int)
spmTemplateRecNo.Value = _
Me.cbTemplate.SelectedValue
Dim spmProjRecNo As SqlParameter = _
cmd.Parameters.Add("@ProjRecNo", SqlDbType.Int)
spmProjRecNo.Value = _
Me.cbProject.SelectedValue
Dim spmIssueRecNo As SqlParameter = _
cmd.Parameters.Add("@IssueRecNo", SqlDbType.Int)
spmIssueRecNo.Value = _
Me.cbIssue.SelectedValue
cmd.Connection = cnQI02414
cnQI02414.Open()
reader = cmd.ExecuteReader()
' Data is accessible through the DataReader object here.
cnQI02414.Close()
What should I be looking for?
Hi all,
I have been trying to extract table data into file but its not getting extracted. I am using following command to run.
exec spGetIPAddress @ip = @servername output
select @SQL = 'bcp '
+ char(34)
+ ' select Field from ['
+ @DatabaseName
+ '].[dbo].Temp_ExtractSR39'
+ ' order by Field'
+ char(34)
+ ' queryout '
+ @RenamePathAndFile
+ ' -T -c -CACP -S'
+ @servername
exec master..xp_cmdshell @SQL, no_output
Is there any setting need to be done at database level? or any setting for Sql server?
Thanks in adavance,
Anshu
Hi,
I have been in the process of creating some new pkgs in BIS. My question is, once I am done writing the pkgs, what next?
That is, how do I get the pkgs into SQL Server, and how do I schedule them to run?
Also, what if I have the following scenario:
I have created a new SSIS pkg that has several EXECUTE DTS 2000 tasks in them. The DTS packages that are called from the EXECUTE DTS 2000 task are stored in the database under "Legacy". These should still run OK once I "load" the packages to SQL Server 2005, yes?
Where do the SSIS packages "live" in SQL Server 2005? That is, where can I view them.
Thank you!!
Hi,
View 4 Replies View RelatedHi there.
I have generated an SQL Script to script me a database, stored procs, tables, foriegn keys and users, as well as Create command and drop objects.
however it seems that it is not creating the database, if an existing database does not exist, or if it does after it has been dropped. I get this error:
Could not locate entry in sysdatabases for database 'db_name_something'. No entry found with that name. Make sure that the name is entered correctly.
This is the SQL:
/****** Object: Database SafeHands Script Date: 10/04/2006 02:05:30 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'db_name_something')
DROP DATABASE [db_name_something]
CREATE DATABASE [db_name_something] ON (NAME = N'db_name_something_Data', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdatadb_name_something_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'db_name_something_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdatadb_name_something_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
..
..
any ideas where I am going wrong?
I know that I do not have the "GO" command, however I am trying to run the entire script from C# 2.0, using SQL Server 2000 SP4, so I have read the entire script into a string array, and split each line from the word "GO" as otherwise, I would get an error.
Many thanks
--The Dependency row is not created!
-- I have a procedure "Example 1".
-- When I look at the sys.sql_dependencies I didn't find a row with Table1 dependency.
--If I write the procedure like "Example 2" the dependency Table1 exists in sys.sql_dependencies.
--
--DEFENENTLY A BUG. CAN YOU FIX IT?
--I REALLY NEED IT!!!!!!
Create table Table1 (id int);
GO
--Example 1
CREATE procedure a
as
create table #Test(id int)
Insert into #test
Select id from Table1
DROP TABLE #test;
GO
Select * from sys.sql_dependencies where object_id=object_id('a');
GO
--Example 2
CREATE procedure c
as
Select id into #test from Table1
DROP TABLE #test;
GO
Select * from sys.sql_dependencies where object_id=object_id('c');
DROP PROCEDURE c
DROP PROCEDURE a
DROP TABLE Table1
I loaded the import/export wizard into my Tools menu. I ran it and exported a view to an excel worksheet with mixed results (I got the headings, but no data). I want to take a look at the package that should have been created (I selected that option, but the export didn't complete and I couldn't decipher the log to determine whether the package was created).
If it was successfully created, where would I find it in SQL 2005 Express?
Thanks,
Bill
in my database i have a "datecreated" feild, its datatype is "datetime", is it possible to set the defualt value as the time now? if so what do I put in the defualt value property? thanks a million si!
View 2 Replies View Related