Hello
I'm desperate for help with the following dynamic SQL. It used to work for
ages but suddenly stopped working today! I can't recall changing anything of
importance.. So I say. Anyway, I'm getting this error: "Cannot use empty
object or column names. Use a single space if necessary."
I've identified the location within the script that causes this message it's
this line:
(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,
3rd line of Set @cmdSQL =.
I've been trying to insert a single space between "" which eliminates half
of the error but I can't figure out what quotes to use around 'MG' and 'MA'.
I'd be grateful if you can have a look at this and let me know how to
correct this problem.
Like I said it used to work and I'm perplexed about this sudden error. Is
there any change that can cause this behaviour?
Many thanks for your efforts!!
Have a nice day!!
Martin
Paging Script:
CREATE PROCEDURE dbo.sp_ListKeyOut(
@page_number INT,
@number_of_records INT,
@cmdWHERE VARCHAR(200),
@cmdORDERBY VARCHAR(200)
) AS
SET NOCOUNT ON
SET @SizeString = CONVERT(VARCHAR, @number_of_records)
SET @PrevString = CONVERT(VARCHAR, @number_of_records * (@page_number - 1))
SET QUOTED_IDENTIFIER OFF
SET @cmdSQL = 'SELECT COALESCE((i2b_vw_contact.Firstname + CHAR(32) +
i2b_vw_contact.Lastname),i2b_vw_company.CompanyNam e) AS CName,
i2b_vw_keytransactionlog.KeyTransactionLogID,
i2b_vw_keytransactionlog.KeyID,
(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,
i2b_vw_address.Address1 AS PropertyAddress, i2b_vw_contact.MobileNo,
A.ProgUserName AS ProgUserName,
CONVERT (varchar(10), i2b_vw_keytransactionlog.TransactionDate, 104 ) AS
TransactionDate,
CONVERT(varchar(10),i2b_vw_keytransactionlog.Retur nByDate,104) AS
ReturnByDate
FROM i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID IN'
IF @cmdWHERE IS NULL OR @cmdWHERE = ''
BEGIN
EXEC(
@cmdSQL +
'(SELECT TOP ' + @SizeString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID NOT IN
(SELECT TOP ' + @PrevString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
ORDER BY ' + @cmdORDERBY + ')
ORDER BY ' + @cmdORDERBY + ') ORDER BY ' + @cmdORDERBY
)
-- EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM
i2b_vw_keytransactionlog')
END
ELSE
BEGIN
EXEC(
@cmdSQL +
'(SELECT TOP ' + @SizeString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE ' + @cmdWHERE + ' AND KeyTransactionLogID NOT IN
(SELECT TOP ' + @PrevString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE ' + @cmdWHERE + ' ORDER BY ' + @cmdORDERBY + ')
ORDER BY ' + @cmdORDERBY + ') ORDER BY ' + @cmdORDERBY
)
-- EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM
i2b_vw_keytransactionlog WHERE ' + @cmdWHERE)
HiI'm having trouble with the script below that it just won't do acorrect ORDER BY for a date field.When executing the two SELECT TOP statements on their own the recordsare sorted correctly but when wrapped in the main statement, the ORDERBY is just ignored. Tried to play around with the script but with noluck. Script is for paging, in this case, select all records meetingcriteria, order and select Top 25 to display on first page. Anyquestions, please don't hesitate contacting me.Thanks very much to anyone who's having a look at this!MartinSELECT COALESCE((i2b_contact.Firstname + CHAR(32) +i2b_contact.Lastname),i2b_company.CompanyName) AS CName,i2b_keytransactionlog.KeyTransactionLogID, i2b_key.KeyCode,(i2b_address.Address1) AS PropertyAddress,A.ProgUserName AS ProgUserName,CONVERT (varchar(10), i2b_keytransactionlog.TransactionDate, 104 ) ASTransactionDate,CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) ASReturnByDateFROM i2b_keytransactionlogLEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =A.ProgUserID)WHERE KeyTransactionLogID IN(SELECT TOP 25 KeyTransactionLogID FROMi2b_keytransactionlogLEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =A.ProgUserID)WHERE i2b_keytransactionlog.ProgClientID = 1 ANDi2b_keytransactionlog.ProgUserID = 3 AND KeyTransactionLogID NOT IN(SELECT TOP 0 KeyTransactionLogID FROM i2b_keytransactionlogLEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =A.ProgUserID)WHERE i2b_keytransactionlog.ProgClientID = 1 ANDi2b_keytransactionlog.ProgUserID = 3 ORDER BY ReturnByDate DESC)ORDER BY ReturnByDate DESC)ORDER BY ReturnByDate DESC
I have a SP that I am trying to finalize however; my inexperience is showing itself on this one.
History: 3 Tables: Tooldb - Employee - ToolUserdb
Scenario: I have a webform in c# that gathers data concerning internal tools(applications) that are written in-house. One of the fields is a listbox of names pulled from the Employee table called Creator. When the form is submitted I need to have the list of selected employees published to the ToolUserdb.
Hi, In a stored procedure (SP1) I call another stored procedure (SP2), passing along parameters. In SP2 I dynamically build cursor c1. I can execute SP2 without any problems but when I start SP1 I get the following message:
Msg 16916, Level 16, State 1, Procedure SP2, Line 114 A cursor with the name 'C1' does not exist.
Yes, the cursor is of type GLOBAL. I am sure I miss something here ... Any help is highly appreciated !
I got your email address from your web cast. I really enjoyed the web cast and found it to be very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question regarding the product. I have looked for the information on the web, but was not able to find relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets Are generated using reporting services. On examining the excel sheet, I found out that the name Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc. And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers. http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1 http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Hi, I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses. 2. Report is generated and displayed on screen. 3. Report is emailed to addresses specified by user.
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error: Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18 Incorrect syntax near the keyword 'external'. CREATE FUNCTION clrDynamicPivot ( -- Add the parameters for the function here @query nvarchar(4000), @pivotColumn nvarchar(4000),
CREATE PROCEDURE dbo.spinb_CheckYN @FnNameYN varchar(50), @InvLineID int, @FnBit bit output AS
declare @SQL varchar(8000)
set @SQL = ' if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1 set @FnBit = 1 else set @FnBit = 0'
exec (@SQL) GO
Obviously; @FnBit is not defined in @SQL so that execution will not work. Server: Msg 137, Level 15, State 1, Line 4 Must declare the variable '@FnBit'. Server: Msg 137, Level 15, State 1, Line 5 Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me. Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows: Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes ----------- if @FnNameYN = 'fnb_YN_B1_14' BEGIN if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1 set @FnBit = 1 else set @FnBit = 0 END
------ Vertical Build1 - Scissor Vanes ----------- if @FnNameYN = 'fnb_YN_B1_15' BEGIN if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1 set @FnBit = 1 else set @FnBit = 0 END . . . etc.
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.
Using the examples given in Books Online returns compilation errors. See below.
Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?
James
-- SQL ---------------
EXEC SQL BEGIN DECLARE SECTION; char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?"; char szLastName[] = "White"; char szFirstName[30]; EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE author_cursor CURSOR FOR select_statement;
EXEC SQL PREPARE select_statement FROM :szCommand;
EXEC SQL OPEN author_cursor USING :szLastName; EXEC SQL FETCH author_cursor INTO :szFirstName;
--Error-------------------- Server: Msg 170, Level 15, State 1, Line 23 Line 23: Incorrect syntax near ';'. Server: Msg 1038, Level 15, State 1, Line 24 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 1038, Level 15, State 1, Line 25 Cannot use empty object or column names. Use a single space if necessary. Server: Msg 170, Level 15, State 1, Line 27 Line 27: Incorrect syntax near ';'. Server: Msg 170, Level 15, State 1, Line 30 Line 30: Incorrect syntax near 'select_statement'. Server: Msg 170, Level 15, State 1, Line 33 Line 33: Incorrect syntax near 'select_statement'. Server: Msg 102, Level 15, State 1, Line 35 Incorrect syntax near 'author_cursor'. Server: Msg 170, Level 15, State 1, Line 36 Line 36: Incorrect syntax near ':'.
I have a requirment which i have partly accomplished , but could not get through completely
i have a file which comes in a standard format ending with date and seq number ,
suppose , the file name is abc_yyyymmdd_01 , for first copy , if it is copied more then once the sequence number changes to 02 and 03 and keep going on .
then i need to transform those in to new file comma delimited destination file with a name abc_yyyymmdd,txt and others counting file counting record abc_count_yyyymmdd.txt. and move it to a designated folder. and the source file is then moved to archived folder
what i have taken apprach is
script task select source file --------------------> data flow task------------------------------------------> script task to destination file
dataflow task -------------------------> does count and copy in delimited format
what is happening here is i can accomlish a regular source file convert it to delimited destination file --------> and move it to destination folder with script task .
but cannot work the dynamic pick of a source file.
please advise with your comments or solution you have
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
I have created a dynamic SQL program that returns a range of columns (1 -12) based on the date range the user may select. Each dynamic column is month based, however, the date range may overlap from one year to another. Thus, the beginning month for one selection may be October 2005, while another may have the beginning month of January 2007.
Basically, the dynamic SQL is a derived Pivot table. The problem that I need to resolve is how do I now use this dynamic result set in a Report. Please keep in mind that the name of the columns change based on the date range select.
I have come to understand that a dynamic anything is a moving target!
Hi all, i have a DTS package that i call from a C# app, i had it working great until i decided to use an activeX script to do the data transformations instead of the row copy. I need to use ActiveX to add a standard name to the last column in the destination table. the problem is the task is executing without errors (from c#) but nothing is happening, its failing silently. If i modify the Data Transformation back to a standard column mapping (with separate DTSTransformations for each column) it works fine, but as soon as i use activeX to handle the transformations it doesn't work. Can anyone tell me what i may be doing wrong. heres the calling code from C# if(f.Name.Substring(13,7).ToLower() == "product") { try { activity.Log("Starting Product DTS Package..."); DTS.Package2Class package = new DTS.Package2Class(); object pVarPersistStgOfHost = null; package.LoadFromSQLServer( "192.168.8.8", "username", "thepassword", Microsoft.SqlServer.DTSPkg80.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, null, null, null, (string)ConfigurationSettings.AppSettings["productDTSPackage"], ref pVarPersistStgOfHost); package.GlobalVariables.Item(1).Value = f.FullName.ToString(); package.Execute(); package.UnInitialize(); //force release of COM object System.Runtime.InteropServices.Marshal.ReleaseComObject(package); package = null; } catch(Exception e) { activity.Log(string.Format("Failureprocessing {0}",WorkingPath + f.Name) +" - "+ e.Message); } and the activex i tried to use for the transformations is: '**********************************************************************' Visual Basic Transformation Script'************************************************************************' Copy each source column to the destination columnFunction Main() DTSDestination("Yesmail Id") = DTSSource("product series") DTSDestination("Customer CKM CustId") = DTSSource("product family") DTSDestination("Product SKU") = DTSSource("transaction date") DTSDestination("product model name") = DTSSource("serial number") DTSDestination("serial number") = DTSSource("product model name") DTSDestination("transaction date") = DTSSource("Product SKU") DTSDestination("product family") = DTSSource("Customer CKM CustId") DTSDestination("product series") = DTSSource("Yesmail Id") DTSDestination("fileName") = DTSGlobalVariables("sourceFile").Value <-- this is why im using the activex for this field to get a Global Variable. Main = DTSTransformStat_OKEnd Functionthe weird thing is the PACKAGE runs fine from ENTERPRISE MANAGER with this activex, it just doesn't do it from my Calling app, perhaps i have missed something i need to change in the package constructor? BTW: i do have my assembly signed in C# for the COM wrapper.thanks in advance, mcm
Hello, I have done extensive work with Classic ASP for 9 years now. Working with Stored Proceedures etc However now am working in c# am adviced by a friend that its best to use the 1. The "Object data source" to call SP 2. Are there any documentations pointers on best practices, how its done ? I want to use the "on click event" in my class file. thanks Ehi
Hello, are there any sample codes that show you how to execute a SP in .net 1. Using a class 2. Calling the class in the onlick_button function ? thanks Ehi
I'm developing a new Stored Proc that will be taking information in to enter customer and order info. Looking at the current Stored Proc its using alot of If statements and I'm thinking of breaking the sp up into different Stored Procs and calling them all from One main stored proc. I know how to do this but I was wondering how it would effect performance. So should I simply keep all the stuff in one proc or module it out into multiple ones to make it easier to follow and read?
I'm working in SQL Server 2005. I have an existing SP that does exactly what I need (it's the aspnet_UsersInRoles_IsUserInRole SP). I want to reuse this SP and use it's return value as a field in the SP I'm writing. How do I go about doing this? I could take the logic out of the called SP and wrap it in a function; but, I would really like to reuse the SP aspnet_UsersInRoles_IsUserInRole. Thanks in advance for any assistance.
hi, i'm trying to call a dts package from vb.net. i got 2 examples which both don't work. first one gives me a [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied error. source code:Dim serverName As String = "SERVERNAME"Dim oPackage As New DTS.Package()Dim oStep As DTS.StepDim pVarPersistStgOfHost As Object = Nothing oPackage.LoadFromSQLServer(serverName, "USERID", "PASSWORD", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _ "DTSPASSWORD", Nothing, Nothing, "DTSPACKAGENAME", pVarPersistStgOfHost) For Each oStep In oPackage.Steps oStep.ExecuteInMainThread = TrueNext oPackage.Execute() Dim err As LongDim source, description, message As StringFor Each oStep In oPackage.StepsIf oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then oStep.GetExecutionErrorInfo(err, source, description) message = String.Format("ErrorCode: {0}, Source: {1}, Description: {2}", err.ToString(), source, description)Else message = "Success"End IfNext oPackage.UnInitialize()oPackage = Nothing second example tries to create a dts package dynamically. this time i get the error that the CustomTask can not be casted, somehting about the QueryInterface. source code:Dim oPackage As New Package2()Dim oConnection As Connection2Dim oStep As Step2Dim oTask As TaskDim oCustomTask As BulkInsertTask oConnection = oPackage.Connections.[New]("SQLOLEDB")oStep = oPackage.Steps.[New]()oTask = oPackage.Tasks.[New]("DTSBulkInsertTask")oCustomTask = CType(oTask.CustomTask, BulkInsertTask) <-- error With oConnection .Catalog = "CATALOG" .DataSource = "SERVERNAME" .ID = 1 .UseTrustedConnection = True .UserID = "USERID" .Password = "PASSWORD"End With oPackage.Connections.Add(oConnection)oConnection = Nothing With oStep .Name = "InsertGemal" .ExecuteInMainThread = TrueEnd With With oCustomTask .Name = "InsertGemal" .DataFile = "D:ImportGemal.dat" .ConnectionID = 1 .DestinationTableName = "Gemal" .FieldTerminator = ";" .RowTerminator = " "End With oStep.TaskName = oCustomTask.Name With oPackage .Steps.Add(oStep) .Tasks.Add(oTask) .FailOnError = TrueEnd With oPackage.Execute() oPackage.UnInitialize()oPackage = Nothing any help highly appreciated! t.i.a.,ratjetoes.
Hello, I have the class below. And trying to execute it on a button click event. What am i doing wrong ? Thanks Here is the button click event1 protected void Button1_Click(object sender, EventArgs e) 2 { 3 signup_data_entry signup = new signup_data_entry(); 4 signup.signup_data_entry(); 5 6 }
Here is my class file. please advice 1 public class signup_data_entry 2 { 3 public signup_data_entry() 4 { 5 //SqlConnection con = new SqlConnection("cellulant_ConnectionString"); 6 SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["cellulant_ConnectionString"]); 7 8 SqlCommand command = new SqlCommand("Cellulant_Users_registration", con); 9 command.CommandType = CommandType.StoredProcedure; 10 11 con.Open(); 12 13 //string IP = new string(); 14 15 16 command.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID")); 17 command.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription")); 18 19 command.Parameters[0].Value = 4; 20 command.Parameters[1].Value = "SouthEast"; 21 22 int i = command.ExecuteNonQuery(); 23 24 25 } 26 }
I have problem in calling the SP from ASP.NET application @DefApp nvarchar(255)= '' ,@DefBusFunction nvarchar(255)= '' ,@DefImpact nvarchar(255) = '',AS Begin declare @sql nvarchar(4000)declare @whereClause nvarchar(4000)DECLARE @return_value intdeclare @sqlWhere nvarchar(4000) select @sql = 'SELECT DefApp, DefBusFunction, DefImpact FROM Def LEFT JOIN ZFunction ON (def.DefApp = ZFunction.App) AND (Def.DefBusFunction = ZFunction.BusFunction)' if @DefImpact <> '' Set @whereClause = ' where DefImpact ' SET @whereClause = @whereClause + ' = ''' + @DefImpact + '''' set @sqlWhere=@sql + @WhereClauseEXEC @sqlWhereEnd and i am calling this SP from my ASP.net application to fill the SSRS report. I have written code in ASP.NET like: sqlCmd = new SqlCommand("subbusample", conn); sqlCmd.CommandType = CommandType.StoredProcedure;sqlCmd.Parameters.Add(new SqlParameter("@DefImpact", SqlDbType.NVarChar, 255, txtValue3.Text.ToString())); sqlCmd.Parameters.Add(new SqlParameter("@DefBusFunction", SqlDbType.NVarChar, 255, txtValue1.Text.ToString())); sqlCmd.Parameters.Add(new SqlParameter("@DefApp", SqlDbType.NVarChar, 255, txtValue2.Text.ToString())); RptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote; RptViewer.ServerReport.ReportServerUrl = new System.Uri("http://servername/ReportServer");RptViewer.ShowParameterPrompts = false; RptViewer.ServerReport.ReportPath = "/folder name/Subbu_Sample";
when i execute it, i am not able to fill the report with data.
In my shared hosting environment where I am making large volumes of database calls, I am very anxious to preserve my heavily throttled threads-I presume when I make a database call, that it is going to a separate server and any threads that server may be using are in addition to my " hosting plan ". My question (s) is, is it therefore a no-brainer to always make calls to:Dim oResult As IAsyncResult = iDBCmd.BeginExecuteReader()arWaitHandles(0) = oResult.AsyncWaitHandleiIndex = WaitHandle.WaitAny(arWaitHandles, 60000, False) i.e. run all calls to the database asynchronously? Is it really releasing a thread back to my asp.net App while it is waiting? (I.e. Is it worth doing)
I'm new to using DTS packages and I'm running into a problem. Hopefully someone can help me out. I have an ASP.NET page that needs to call a DTS package. Everything seems to be working right up until I call opkg.Execute. The package seems to run but nothing happens. After further investigation I have found what looks like a permissions issue. this is the message I'm getting" Error Number: -2147467259 {Integer}
Error Description: "The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." {String}
This is obviously a permissions issue. I'm just not sure who the DTS package is running as and where to set the permissions.
If you have a begin tran in a sp that calls another sp and that called sp fails. can you execute a roll back from the called sp, or should it return and error which will roll back the begin tran in the original sp
Hi all, I want to call dts package from visual basic. The dts package will do a backup of the database. I am not sure how this can be achieved. Any help will be great Thanks
