Using The SQL Query At Runtime..
Mar 28, 2007
Hi,
Currently im using SQL queries in combination with ASP.NET..
I have currently only one table('Requests'), which has all the user info of name,id,studyrequested etc.. A person can request for any number of studies.
Now the prob is that from now on whenever a user requests for a new study not only should his details be stored in the Request table but also in a new Users table. In the Users table that particular users details must only be loaded once.. i.e. if the same user enrolls for another study his details should only be updated in the requests table and not in the Users table, since his details have already been loaded once in the Users Table..
While this works fine for new users, but what bout the old users already stored in the Requests table before the Users table existed.
Is there any way i could run a sql script only once at runtime, so that all the old legacy users are loaded once in to the Users Table.
Thanks,
Nitin
View 1 Replies
ADVERTISEMENT
Jul 23, 2005
I get Invalid object name 'bstr'. when I try to run this querySelect distinct c0.oid, c1.Value, c2.Value, c3.ValueFrom(SELECT oid FROM dbo.COREAttributeWHERE CLSID IN ('{1449DB2B-DB97-11D6-A551-00B0D021E10A}','{1449DB2D-DB97-11D6-A551-00B0D021E10A}','{1449DB2F-DB97-11D6-A551-00B0D021E10A}','{1449DB31-DB97-11D6-A551-00B0D021E10A}','{1449DB33-DB97-11D6-A551-00B0D021E10A}','{1449DB35-DB97-11D6-A551-00B0D021E10A}','{1449DB37-DB97-11D6-A551-00B0D021E10A}','{1449DB39-DB97-11D6-A551-00B0D021E10A}','{1449DB3B-DB97-11D6-A551-00B0D021E10A}','{1449DB3D-DB97-11D6-A551-00B0D021E10A}','{1449DB3F-DB97-11D6-A551-00B0D021E10A}','{1449DB43-DB97-11D6-A551-00B0D021E10A}','{1449DB45-DB97-11D6-A551-00B0D021E10A}','{1449DB47-DB97-11D6-A551-00B0D021E10A}','{1449DB49-DB97-11D6-A551-00B0D021E10A}','{1449DB4B-DB97-11D6-A551-00B0D021E10A}','{1449DB4D-DB97-11D6-A551-00B0D021E10A}','{1449DB51-DB97-11D6-A551-00B0D021E10A}','{DAA598D9-E7B5-4155-ABB7-0C2C24466740}','{6921DAC3-5F91-4188-95B9-0FCE04D3A04D}','{128F17D4-2014-480A-96C6-370599F32F67}','{9F3A64C9-28F3-440B-B694-3E341471ED8E}','{2E3AB438-7652-4656-9A18-4F9C1DC27E8C}','{B69E74A7-0E48-4BA2-B4B7-5D9FFEDC2D97}','{2BB836D3-2DC1-4899-9406-6A495ED395C3}','{9CFFDC3A-5DF5-4AD8-B067-6EF5A9736681}','{E18E470B-B297-43D2-B9CD-71AF65654970}','{9BDCDA97-1171-409D-B3AB-71DA08B1E6D3}','{0E91AC62-7929-4B42-B771-7A6399A9E3B0}','{C8BAE335-CCB7-4F1D-8E9D-85C301188BE2}','{97E6E186-8F32-42E6-B81C-8E2E0D7C5ABA}','{BE5B6233-D4E7-4EF6-B5FC-91EA52128723}','{4ECDAAE1-828A-4C43-8A66-A7AB6966F368}','{19082B90-EF02-45CC-B037-AFD0CF91D69E}','{6F76CEF7-EBC0-48C6-8B78-C5330324C019}','{18492042-B22A-4370-BFA3-D0481800BBC7}','{A71343AD-CC09-4033-A224-D2D8C300904A}','{EC10BD0A-FDE3-4484-BEA6-D5A2E456256C}','{F7F8A4E1-651A-4A48-B55A-E8DA59D401B2}','{A923226F-B920-4CFA-9B0D-F422D1C36902}','{A95ACA6A-16AC-47E4-A9A6-F530D50A475A}','{C31DB61A-5221-42CF-9A73-FE76D5158647}')) AS c0 ,(select oid, dispid, valueFROM dbo.COREBSTRAttributeWHERE iid = '{1449DB20-DB97-11D6-A551-00B0D021E10A}') As bstrLEFT JOIN bstr AS c1ON (c0.oid = c1.oid)AND c1.dispid = 28LEFT JOIN bstr AS c2ON (c0.oid = c2.oid)AND c2.dispid = 112LEFT JOIN bstr AS c3ON (c0.oid = c3.oid)AND c3.dispid = 192thanksSunit
View 1 Replies
View Related
Jul 20, 2005
What are some sound solutions to capture the query runtime via ascript/proc? The info returned from Statistics time is hard to process.The goal is to run a statement(s) and capture and track the executiontime to check on the availability of SQL Server. The data can beanalyzed to determine when perf is high and low. But the main reason isto check db availability.Thanks,Mike--Posted via http://dbforums.com
View 3 Replies
View Related
Jun 21, 2007
Hi all,
Im new to SSRS, basically I have created and deployed a report to my reporting server.
Now I have a query parameter on my report. From my application, I can access the report manager, and here it asks me for my variable. I'm not sure if my question is a vb.net one, but I was wondering if anyone knew how I could pass my query paramater to SSRS at runtime. Basically it would be easier for my users if they could just see the report they are interested in, rather than having to type the variable in to be able to see the report.
Thanks for your time.
Pace
"Impossible is Nothing"
View 1 Replies
View Related
Sep 13, 2007
Please help figure out what is wrong with my code. The script is supposed to load a package (from file). The loaded package already has everything set up to run a query against a local server and output the results to an Excel file. The reason for the outer script is because I need to change the query based on a global variable. When the query changes, though, I think the existing dataflow Path is no longer valid, so I should remove it and re-create another one with the new input mappings. Here is my code, which runs and throws an exception at the AcquireConnections call.
The error is
Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B
I pieced together this code from the examples in the online books, but I am not sure what to do.
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Public Class ScriptMain
Public Sub Main()
'
Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application()
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _
app.LoadPackage("c:systimeExcelOutExcelOutExcelOutDo.dtsx", Nothing)
Dim pkgVars As Variables = package.Variables
Dim gsVar As Variable = pkgVars("User::gsExcelFile")
Dim currVars As Variables = Dts.Variables
Console.WriteLine(Dts.Variables("User::gsExcelFile").Value)
gsVar.Value = Dts.Variables("User::gsExcelFile").Value
pkgVars("User::gsQuery").Value = Dts.Variables("User::gsQuery").Value
pkgVars("User::gsCreateTable").Value = Dts.Variables("User::gsCreateTable").Value
Dim e As Executable = package.Executables("ExcelOutTask")
Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _
CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
' Get the source component.
Dim SourceComponent As IDTSComponentMetaData90 = _
dataFlowTask.ComponentMetaDataCollection("Local Source")
Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate()
srcDesignTime.ProvideComponentProperties()
' Reinitialize the metadata.
srcDesignTime.AcquireConnections(vbNull)
srcDesignTime.ReinitializeMetaData()
srcDesignTime.ReleaseConnections()
' Get the destination component.
Dim destination As IDTSComponentMetaData90 = _
dataFlowTask.ComponentMetaDataCollection("Excel Destination")
Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()
destDesignTime.ProvideComponentProperties()
' Create the path.
dataFlowTask.PathCollection.RemoveAll()
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()
path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _
destination.InputCollection(0))
'Console.WriteLine(dataFlowTask.PathCollection.Count)
Dim ret As DTSExecResult
ret = package.Execute()
Console.WriteLine(ret.ToString)
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
View 3 Replies
View Related
Jun 21, 2007
Hi all,
Im new to SSRS, basically I have created and deployed a report to my reporting server.
Now I have a query parameter on my report. From my application, I can access the report manager, and here it asks me for my variable. I'm not sure if my question is a vb.net one, but I was wondering if anyone knew how I could pass my query paramater to SSRS at runtime. Basically it would be easier for my users if they could just see the report they are interested in, rather than having to type the variable in to be able to see the report.
Thanks for your time.
Barry Andrew
View 3 Replies
View Related
Aug 2, 2005
Hi all,
I have the following query...
SELECT Count(*)
FROM Incidents I
WHERE (Priority = 1)
AND (Time_First_Unit_On_Scene IS NOT NULL)
AND (DateDiff(s, Time_ClockStart, Time_First_Unit_On_Scene) <= 480)
AND (Response_Date BETWEEN '1-Apr-2004')
AND ('31-Mar-2005 23:59:59')
AND (I.Disposition_ID <> 9 )
...and I get the following error message...
System.Data.OleDb.OleDbException: Difference of two datetime columns caused overflow at runtime.
... any one know what it could be?
Thanks
Tryst
View 1 Replies
View Related
Mar 21, 2008
Hi Friends,
I want to have solution for one of the problem.
The requirement is like this :
I want to write stored procedure or function which will take parameter as SQL Server name, DB name, UserName and passwod.
This Stored proc will connect to Remote server using these parameters and will get the count of the rows in one of the table.
I created the connection using the linked server
EXEC sp_addlinkedserver @SerevrName,N'SQL Server'
EXEC sp_addlinkedsrvlogin @SerevrName, False, Null, @ServerUserName,@SerevrPws
Now I am trying to get count using following query :
set @SQLQuery = 'SELECT count(*) FROM [' + @SerevrName + '].' + @SrcDataBaseName +'.dbo.<<TableName>>'
But the question is that the execution goes this way :
exec(@SQLQuery)
Now how to assign this count value to some variable so that I can use it later ...?
Going forword I want to use cursor and get the rows in these table using cursor ...?
How can I assign values returned from any runtime query to temporary variable or table ...?
I tried another approach also:
I put remote connection and query execution in inner stored proc called usp_GetTableRowCount
set @SQLQuery = 'SELECT count(*) FROM [' + @SerevrName + '].' + @SrcDataBaseName +'.dbo.<<TableName>>'
exec(@SQLQuery)
and in outer stored proc : referenced the inner stored proc like this
exec @AFSDataRowCount = dbo.usp_GetTableRowCount <<Server Name>>,<<User Name>>, <<Password>>, <<DBName>>
The execution of dbo.usp_GetTableRowCount <<Server Name>>,<<User Name>>, <<Password>>, <<DBName>> gives me exact no of rows
but when I see value of AFSDataRowCount, I get 0.
Kindly help me out whereever I am making mistake or else pls tell me any other approach to follow.
Thanks in advance.
View 6 Replies
View Related
Apr 18, 2008
Hi,
I am having a Data flow task in For each loop which will gets 100 sourcetable names and 100 target table names...
am having a simpleData flow task which trasferes from OLEDBSource to OLEDBDestination.
I am repeating the Dataflow task which transfers from sourcetablename extracted from for loop to a destination table var.
The problem am gettting is for the first table it is able to transfer correcly because I did mapping for those tables at design time...but for the next coming sourcetable-desttable (which r having different no of cols,datatypes) its giving Validation failed...and...needs to refresh metadata....
is there any way to refresh the metadata of Data flow task (I set the property of OLEDBSource validate external meta to false then also same error is coming)
Thanks
Radhika
View 4 Replies
View Related
Nov 30, 2007
Is there an SQL db that can function as a runtime stand alone app, inconjunction with a VB interface?
View 8 Replies
View Related
Sep 18, 2007
Hello. I am trying to write a VB.net application to call our SSIS packages. I have seen several tutorials, including this one:
http://msdn2.microsoft.com/en-us/library/ms190901.aspx
Which calls for me to import:
Microsoft.SqlServer.Dts.Runtime
However, I don't have this DST Runtime DLL installed on my machine. I have installed "everything" in SQL Server Developer Edition and Visual Studio. Any idea how I can get this?
Thanks,
Chris
View 5 Replies
View Related
Jul 10, 2006
I just ran a testbed of 4 types of SQL queries:1. inline SQL with a StringBuilder2. managed sql3. SQL text processing (@SQL as varchar(5000); SET @SQL = 'SELECT ' + @var...)4. a regular sproc that has the columns and table name hard coded1,2, and 4 always end up at about the same time given the averages.3 is always at last 1.5 times slower, and usually closed to 2 times.1 and 2 both use StringBuilders, the code is a direct copy, and 3 is a copy as well.My managed SQL is: [Microsoft.SqlServer.Server.SqlProcedure] public static void usp_Items_Select_Managed(SqlString table, SqlString name, SqlString value) { // sql StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendFormat( "SELECT {0}.* FROM {0} WHERE {0}.{1} = {2}", table, col, value ); SqlConnection sqlConnection = new SqlConnection("context connection=true"); SqlCommand sqlCommand = new SqlCommand(stringBuilder.ToString(), sqlConnection); sqlConnection.Open(); SqlContext.Pipe.Send(sqlCommand.ExecuteReader()); sqlConnection.Close(); }Is there anything wrong with my Managed SQL, or is this just the way that it is?Thanks
View 1 Replies
View Related
May 2, 2001
I'm running a package on SQL Server 7, SP2 created in the same environment.
When running a package from DTS Designer, I get an execution status of "Error Occurred" in the Package Execution Status dialog on one of the data pump tasks I'm running along with the number of rows inserted. When I double-click on the task, I get the following message: " The Connection is currently being used by a task. The connection cannot be closed or reused".
Both the exception log for the task and the package, specified for error output, indicate the task ran successfully and the clients confirm that the appropriate number of rows have been inserted in the table. Has anyone encountered this error that can explain the cause/cure?
View 1 Replies
View Related
Jun 4, 2001
Hi,
When I'm using installshield to install sql runtime, I get the message "The Workstation service has not been started" during Configure SQL Server Agent. Does someone know what it means and how to solve it?
Thank you
View 1 Replies
View Related
May 12, 2000
Hi Everyone,
I've an DTS package, which has an Execute SQL Task. I'm doing the operations based on a parameter. I need to pass that in runtime. Is there any way to do that in Execute SQL Task? BOL says that the runtime parameter can be set by giving a question mark(?). But when I tried this, it says, value not passed.
Thanks in advance.
View 1 Replies
View Related
Sep 4, 2007
I am Using Sql Server 2005 enterprise edition to develop an accounting system.
now i want to distribute this software to other cleints.
I have created a setup which includes all the components required for the run time of the software but i dont know how to include the sql server database in it.
as i know that i have created access based software i used to include the database file in the setup and it would work but in the case of sql server i dont know how to do it .
is there any runtime of sql server which needs to be downloaded or any other way out.
View 4 Replies
View Related
May 30, 2008
Hi.
I posted similar stuff in one of my previous topic. what i need to do is parse database name runtime
means sample example is
USE <%1>
GO
SELECT * FROM Table_Name
here instead of <%1> i need to pass Test DB name
USE Test
GO
SELECT * FROM Table_Name
my .sql file contains <%1> so i need to replace it
i tried like
declare @t varchar(50)
declare @chg22 varchar(500)
set @t = 'Test'
set @chg22 = 'USE <%1> GO'
set @chg22 = REPLACE(@chg22,'<%1>',@t)
exec(@chg22)
SELECT * FROM Table_Name
but i am not getting it..in above example i am just took one line select * from table_name but actually in .sql file more that 300 lines are there so i m not able to concatenate it..
T.I.A
View 4 Replies
View Related
Nov 30, 2006
We run a regular query through Excel that is linked to a Microsoft SQL Server data source.
We have run this query routinely for the past few years. Suddenly this query has started returning an error message - “[Microsoft][ODBC SQL Server Driver] [SQL Server] Difference of two datetime columns caused overflow at runtime�. It seems to be a common error (as per Google search) however I don’t want to go messing around with something I don’t fully understand. Can anyone point me in the direction of what to look out for?
View 2 Replies
View Related
Oct 2, 2006
Hi!Can anybody tell me what I'm missing here?I'm trying to insert a new record into a sql-database from code atruntime (not stored procedure),but get the following error message:"The 'strUn' is not permitted in this context.Only constants, expressions or variables allowed here.Column names are not permitted."//Create sql connectionSqlConnection con = new SqlConnection("server=LocalHost;database=Users;uid=geir;pwd=geir");//Open database connectioncon.Open();//Create variables to hold values from textboxesstring strUn = txtUsername.Text;string strPw = txtPassword.Text;//Create a sqlCommand to insert textbox values into sql-databaseSqlCommand sqlcmd = new SqlCommand();sqlcmd.CommandText = "INSERT INTO tblUsers(Username,Password)VALUES(strUn, strPw)";sqlcmd.Connection = con;try{sqlcmd.ExecuteNonQuery();}catch(SqlException ex){lblInfo.Text = "ExecuteNonQuery failed because: " +"" +ex.Message;}finally{con.Close();}
View 4 Replies
View Related
Feb 19, 2007
My boss claims that there is a free SQL server runtime. Is thistrue? I was only aware of MSDE. But I believe the connections arecapped at 5 in MSDE. If there is a free SQL server runtime, are thereany limitations to using it (performance, connection count)? Thanks.
View 2 Replies
View Related
Aug 4, 2006
I want to combine a bunch of reports (which are stand alone reports on there own) as subreports in a parent or master report. We have done this in Crystal Reprots in order to get a group of reports out in one file.
The issue with Reporting Services is the header and footers of the subreports are not rendered, so I have to repeat the logo information for each subreport in the body of the parent report. I can do this in a table or list or rectangle, but I have to do this for each subreport. Is there any way that I can specify the name of the subreport at runtime? That way I can put everything into a List and drive it from the database. I noticed that the dropdown to select the report name does not allow you to create an expression for this field.
View 3 Replies
View Related
Jan 25, 2008
is that have a function to get it?
View 3 Replies
View Related
Jan 19, 2006
Does anyone have an idea on how to handle runtime parameters for a package? Ideally I'd like to have the package prompt the user for a date range and then store the 2 dates in package variables for use later in the package.
View 1 Replies
View Related
Mar 23, 2007
Hey Guyes,
I'm developing SSRS 2005 Reports in multi languages (i.e. regional language like hindi, marathi)
here i'm sending font name from database field with the help of expression in FontFamily Property of textbox as follows
FontFamily =IIF(Fields!DistrictNameRegional.Value<>Nothing,CStr(Fields!FontName.Value),"Tahoma")
It don't work.
But when i'm Writting like this
FontFamily =IIF(Fields!DistrictNameRegional.Value<>Nothing,"Shivaji01","Tahoma")
It works.
Plz tell me where I'm wrong with this.
View 4 Replies
View Related
May 8, 2007
Hi
I am trying to create a report with unknown number of report items. (as I dont know how many columns the stored proc is going to return).Is there a way to create these type of reports using SSRS other than creating the rdl programatically using vb.net or c#?
Let me know if I am not clear.
Thanks in advance.
View 1 Replies
View Related
Mar 31, 2008
Hi,
Is it possible to create Flat file as runtime destination ?
For Ex:
Data retriew from SQL Server, in the fly destination flat file need to create along with timestamp (abcyyyymmdd.txt).
Regards,
View 1 Replies
View Related
May 20, 2007
I'm trying to create a custom data flow transformation, I need to set the value of a user variable which will be used by a downstream component(for example: a data flow destination) at runtime, is this possible? If possible, in which method should I do that? Thanks.
View 4 Replies
View Related
Apr 17, 2008
I uploaded my repot to the report manager.
How can I get the URL of my report in runtime (through vbscript?)?
I want to use it later as a parameter.
View 1 Replies
View Related
May 15, 2006
Hi,
Our company is currently looking at a package which uses sql server 2000 as backend and power builder as front end. The vendor said that we need to have SQL client runtime install in the user pc. Can anyone tells me what is SQL client rutime and the purpose of it?
Thanks!
View 3 Replies
View Related
Dec 8, 2006
Hi!
My question is exactly the subject.
My Web Form has only a GridView and a DetailsView, there is no SqlDataSource at project time, i create the SqlDataSource at runtime using code like this in the Page_Load event: (I NEED IT TO BE CREATED DYNAMICALLY)1 Dim SQLDS As SqlDataSource = New SqlDataSource()
2
3 SQLDS.ID = "CustomerDataSource"
4 SQLDS.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString
5 SQLDS.SelectCommand = "select customerid,companyname,contactname,country from customers"
6 SQLDS.InsertCommand = "insert into customers(customerid,companyname,contactname,country) values(@customerid,@companyname,@contactname,@country)"
7 SQLDS.UpdateCommand = "update customers set companyname=@companyname,contactname=@contactname,country=@country where customerid=@customerid"
8 SQLDS.DeleteCommand = "delete from customers where customerid=@customerid"
9
10 SQLDS.UpdateParameters.Add(New Parameter("companyname"))
11 SQLDS.UpdateParameters.Add(New Parameter("contactname"))
12 SQLDS.UpdateParameters.Add(New Parameter("country"))
13 SQLDS.UpdateParameters.Add(New Parameter("customerid"))
14
15 Page.Controls.Add(SQLDS)
16
17 If Not Page.IsPostBack Then
18 GridView1.DataKeyNames = New String() {"customerid"}
19 GridView1.DataSourceID = SQLDS.ID
20
21 ' ... and so on
The DetailsView1 uses the same SqlDataSource to show data, but i could not find a way to synchronize the DetailsView1 with the GridView1 when a record is selected in the GridView1.How can I synchronize the DetailsView?I played with the ControlParameter but i can't find either how to add a ControlParameter in code, is there a way? Every place talking about ControlParameter shows something like this: 1 <asp:SqlDataSource ID="SqlDataSource1" runat="server"
2 ConnectionString="<%$ ConnectionStrings:Pubs %>"
3 SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE [state] = @state">
4 <SelectParameters>
5 <asp:ControlParameter Name="state" ControlID="DropDownList1" PropertyName="SelectedValue" />
6 </SelectParameters>
7 </asp:SqlDataSource>
8
Ok. OK. But my SqlDataSource is created dynamically. Any ideas on how to solve this problem?Thanks!
View 3 Replies
View Related
Mar 7, 2007
Hi,
I am trying to create a DTS package dynamically. I have taken all these steps i.e.,
1. SN.EXE -K c:DTS.KEY2.tlbimp.exe "C:program filesmicrosoft SQL Sever80ToolsBindtspkg.dll" /out:c:Microsoft.SQLServer.DTSPkg80.dll /Keyfile:c:DTS.KEY3.gacutil.exe -i C:Microsoft.SQLServer.DTSPkg80.dll.
Now when i compile the code, i didnt get any compilation error. But when execute the code it gives me a runtime error which goes as given below :
An unhandled exception of type 'System.InvalidCastException' occurred in DTSFactory.exe
Additional information: QueryInterface for interface Microsoft.SQLServer.DTSPkg80.CustomTask failed.
I am getting this error near the line : oCustTask = (DTS.DataPumpTask2)oTask.CustomTask;
The following is the code.
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using DTS = Microsoft.SQLServer.DTSPkg80;
namespace DTSFactory
{
/// <summary>
/// Summary description for Form1.
///This is assuming that all steps have been taken in the following document:
///http://SQLDEV.NET/DTS/DotNetCookBook.htm
///SN.EXE -K c:DTS.KEY
///tlbimp.exe "C:program filesmicrosoft SQL Sever80ToolsBindtspkg.dll" /out:c:Microsoft.SQLServer.DTSPkg80.dll
///Keyfile:c:DTS.KEY
///gacutil.exe -i C:Microsoft.SQLServer.DTSPkg80.dll
///These steps are needed for interop with dtspkg.dll
/// </summary>
public class Form3 : System.Windows.Forms.Form
{
/// <summary>
/// Required designer variable.
/// </summary>
private System.ComponentModel.Container components = null;
private System.Windows.Forms.Button button1;
public DTS.Package2Class pkg = new DTS.Package2Class();
public Form3()
{
//
// Required for Windows Form Designer support
//
InitializeComponent();
//
// TODO: Add any constructor code after InitializeComponent call
//
}
private void Form3_Load(object sender, System.EventArgs e)
{
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if(components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.button1 = new System.Windows.Forms.Button();
this.SuspendLayout();
//
// button1
//
this.button1.Location = new System.Drawing.Point(0, 0);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(40, 56);
this.button1.TabIndex = 0;
this.button1.Text = "button1";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// Form3
//
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(648, 429);
this.Controls.Add(this.button1);
this.Name = "Form3";
this.Text = "Form3";
this.Load += new System.EventHandler(this.Form3_Load);
this.ResumeLayout(false);
}
#endregion
private void button1_Click(object sender, System.EventArgs e)
{
initpackage();
}
public void initpackage()
{
CreateConnections();
CreatePackageSteps();
DefinTasks(pkg);
pkg.Name="MyCSharpDTSTest";
pkg.Description = "CShart DTS Test";
object MIA=System.Reflection.Missing.Value;
pkg.SaveToSQLServer("MyServerName", "MyUserID", "MyPassword",
DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "","","",ref
MIA,false);
pkg.Execute();
pkg.UnInitialize();
pkg = null;
}
public void CreateConnections()
{
DTS.Connection txtConn,sqlConn;
txtConn = pkg.Connections.New("DTSFlatFile");
//label1.Text = label2.Text = "";
//for (int i=1;i<txtConn.ConnectionProperties.Count;i++)
// label1.Text = label1.Text + "
" + txtConn.ConnectionProperties.Item(i).Name;
//for text connection
//start
txtConn.ConnectionProperties.Item(1).Value = "C:\FlatFiles\1.txt";
txtConn.ConnectionProperties.Item(3).Value = 1;
txtConn.ConnectionProperties.Item(4).Value = "
";
txtConn.ConnectionProperties.Item(5).Value = 1;
txtConn.ConnectionProperties.Item(7).Value = "|";
txtConn.ConnectionProperties.Item(9).Value = 1;
txtConn.ConnectionProperties.Item(10).Value = 0;
txtConn.ConnectionProperties.Item(11).Value = """;
txtConn.ConnectionProperties.Item(12).Value = false;
txtConn.ConnectionProperties.Item(14).Value = 0;
txtConn.ConnectionProperties.Item(18).Value = 255;
txtConn.Name = "Text File (Source)";
txtConn.ID = 1;
txtConn.Reusable = true;
txtConn.ConnectImmediate = false;
txtConn.DataSource = "C:\FlatFiles\1.txt";
txtConn.ConnectionTimeout = 60;
txtConn.UseTrustedConnection = false;
txtConn.UseDSL = false;
//end
pkg.Connections.Add(txtConn);
sqlConn = pkg.Connections.New("SQLOLEDB");
//for (int i=1;i<sqlConn.ConnectionProperties.Count;i++)
// label2.Text = label2.Text + "
" + sqlConn.ConnectionProperties.Item(i).Name;
sqlConn.ConnectionProperties.Item(3).Value = true;
sqlConn.ConnectionProperties.Item(4).Value = "sa";
sqlConn.ConnectionProperties.Item(5).Value = "FCSUAT";
sqlConn.ConnectionProperties.Item(6).Value = "(local)";
sqlConn.ConnectionProperties.Item(19).Value = "DTS Designer";
sqlConn.Name = "Microsoft OLE DB Provider for SQL Server";
sqlConn.ID = 2;
sqlConn.Reusable = true;
sqlConn.ConnectImmediate = false;
sqlConn.DataSource = "(local)";
sqlConn.UserID = "sa";
sqlConn.ConnectionTimeout = 60;
sqlConn.Catalog = "FCSUAT";
sqlConn.UseTrustedConnection = false;
sqlConn.UseDSL = false;
pkg.Connections.Add(sqlConn);
}
public void CreatePackageSteps()
{
DTS.Step2 oStep = (DTS.Step2)pkg.Steps.New();
oStep.Name = "Copying Data from myTableName";
oStep.Description = "Copying Data from myTableName";
oStep.TaskName = "Copying Data from myTableName";
oStep.CommitSuccess = false;
oStep.RollbackFailure = false;
oStep.ScriptLanguage = "VBScript";
oStep.AddGlobalVariables = true;
oStep.CloseConnection = false;
oStep.ExecuteInMainThread = true;
oStep.IsPackageDSORowset = false;
oStep.JoinTransactionIfPresent = false;
oStep.DisableStep = false;
oStep.FailPackageOnError = true;
pkg.Steps.Add(oStep);
oStep = null;
}
public void DefinTasks(DTS.Package2Class package)
{
DTS.DataPumpTask2 oCustTask;
DTS.Task oTask;
oTask = package.Tasks.New("DTSDataPumpTask");
oCustTask = (DTS.DataPumpTask2)oTask.CustomTask;
oCustTask.Name = "Copying Data from myTableName";
oCustTask.Description = "Copying Data from myTableName to MyDestDB.myTableName";
DTS.DataPumpTask2 oDataPump = (DTS.DataPumpTask2)oTask.CustomTask;
oDataPump.SourceConnectionID = 1;
oDataPump.SourceSQLStatement = "SELECT `MyField` FROM myTableName";
oDataPump.DestinationConnectionID =2;
oDataPump.DestinationObjectName = "myTableName";
oDataPump.ProgressRowCount = 1000;
oDataPump.MaximumErrorCount = 0;
oDataPump.FetchBufferSize = 1;
oDataPump.UseFastLoad=true;
oDataPump.InsertCommitSize = 0;
oDataPump.InsertCommitSize = 500000;
oDataPump.ExceptionFileColumnDelimiter = "|";
oDataPump.ExceptionFileRowDelimiter = "
";
oDataPump.AllowIdentityInserts = false;
oDataPump.FirstRow = 0;
oDataPump.LastRow = 0;
CreateTaskTrans(oDataPump, oCustTask);
pkg.Tasks.Add(oTask);
oCustTask = null;
oTask = null;
}
public void CreateTaskTrans(DTS.DataPumpTask DatPump,
DTS.DataPumpTask2 CustTask)
{
DTS.Transformation2 oTransformation;
oTransformation =
(DTS.Transformation2)CustTask.Transformations.New("DTS.DataPumpTransformCopy");
oTransformation.Name = "DirectCopyXform";
oTransformation.TransformFlags = 63;
oTransformation.ForceSourceBlobsBuffered = 0;
oTransformation.ForceBlobsInMemory = false;
oTransformation.InMemoryBlobSize = 1048576;
oTransformation.SourceColumns.AddColumn("MyField",1);
oTransformation.DestinationColumns.AddColumn("MyField",1);
DatPump.Transformations.Add(oTransformation);
}
}
}
Can anyone help me in solving this?
Thanks in advance.
View 1 Replies
View Related
Jun 27, 2007
Please explain how to create database in runtime ???
View 1 Replies
View Related
Oct 31, 2007
In a web site the user has to choose one out of several sql tables to deal with.
He will then be allowed to view the selected table data through a GridView, to insert a new row into and to update a row in the selected table by means of an array of TextBoxes created dynamically on the base of the selected table.
I think it is possible to solve the problem fully by properly configuring at run time an SqlDataSource.
I have solved the issue of data display by declaring in the code behind within the OnPageLoad sub the following:
SqlDataSource1.SelectCommand = "Select * FROM " & selectedTable
While for the GridView1 I have added the selected table columns to the columns collection as follows:
for i=0 to ColumnCount-1 Dim cac As BoundField = New BoundField
cac.HeaderText = HeaderNamesArray(i)
cac.DataField = ProductNamesArray(i)Me.GridView1.Columns.Add(cac)
next
I have difficulty on how to do similar declarations for the insertcommand and update command.
View 2 Replies
View Related
