I have a package which contains a variable with the value of a UNC sharename. The hostname in the share contains dashes.
I am trying to create a SQL Agent entry to execute said package. Because the package is now in production I want to pass in a new value for the sharename variable. I use the SQL @@SERVERNAME variable to build the package command. (Notice the BlotterUploadFolder variable at the end.)
If I print the @command variable right after setting it, I get what I expect.
/FILE "\PRD-NY-DB-01PackagesMiddleOfficeReconcilliationImportManualBlotter_AccountTransfers.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /CONNECTION MiddleOfficeDb;"Data Source=PRD-NY-DB-01;Initial Catalog=MiddleOffice;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /SET package.variables[BlotterUploadFolder].Value;\PRD-NY-DB-01ManualBlottersUploads
If I cut-paste this into cmd window and execute it with DTExec.exe the package works fine. But when I run the schedule task it causes an error. When I go into the Job Properties dialog to look at the step, I see the value (in the "Set values" table) set to \PRD instead of \PRD-NY-DB-01.
If I modify the SELECT command above to put double-quotes around the value, the value is correct in the Job Properties dialog but causes an error when I exec it with DTExec.
/FILE "\PRD-NY-DB-01PackagesMiddleOfficeReconcilliationImportManualBlotter_AccountTransfers.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /CONNECTION MiddleOfficeDb;"Data Source=PRD-NY-DB-01;Initial Catalog=MiddleOffice;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /SET package.variables[BlotterUploadFolder].Value;"\PRD-NY-DB-01ManualBlottersUploads"
causes the error
Option "-NY" is not valid.
Hopefully somebody has an idea of what is going on.
insert into #t(branchnumber) values (005) insert into #t(branchnumber) values (090) insert into #t(branchnumber) values (115) insert into #t(branchnumber) values (210) insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
I run a query that returns strings that look like this: 08-07, 06-07,04-05 etc. But when I copy the query outuput from Management Studion in SS2005 into Excel 2003, strings get converted into custom dates such as 8-Jun, 9-Jul, 4-May etc in Excel. Is there anthing I can do in T-SQL to prevent this conversion? The strings are set as char(5) datatype in a T-SQL view.
Can anyone help with this. IF i have a ssn like 123459636 and i want to insert dashes into it how would i do that. Results would be 123-45-9636 The second problem is that some of the ssn dont have zero in them so some look like 1234567 i would have to pad with zero then trim from the right to 9 digits then insert the dashes not sure how to do that
Hi,I have a large table with a 'datetime' column that has date and timevalues in it. The data is in this format:2007-10-02 09:54:00.000The table is called 'profile' and the column 'msgdate'I want to return only rows that match a specific date. So far I havethe following query working:select * from profile where msgdate like '%2007%'This returns all rows that start with '2007'. However I cannot seem toge the syntax that will allow me to return a specific date, e.g.2007-10-02I have researched this, trying all sorts of queries with escapecharacters/sequences because of the dash character, but I cannot getit to return anything. Most of my queries have ran without error, itsjust that no data is returned.James
when i do a bulk insert with dashes (i.e. one - two), i get the character u with two dots above it ( Ü ) in place of all the dashes? can't seem to figure out why. the field terminator is a comma ( , ).
i'm inserting just text with dashes into char(75) field.
can someone explain and provide a solution? i would greatly appreciate it!
my script is quite simple, maybe i'm missing something.
Code Snippet
bulk insert testtable from '\localc$adjust.txt' with (fieldterminator = ',')
Hi,I'm having trouble running the following query:select * from message where text_body like ' ----------%'ie, five spaces followed by at least ten hyphens. The query doesn'tcomplete, so eventually I cancel it. If I remove the hyphens from thequery ("... like ' %'") then it runs fine (though it doesn't findthe correct data).Am I confusing SQL Server by using a wildcard or regular expression?(I'm using SQL Server 2000 - 8.99.760).Thanks in advance for any helpRichard
Hello,I have had a request, one of our tables is used as a report and I have beenasked that all fieldnames for months have dashes in them,like Jan-05 instead of Jan05 and so on...Now what we have is a CURSOR which loops through all values in another tablegenerating these fieldnames, like 'Jan-05', Feb-05' etc..Then the table definition is modified so these months display as fieldnames.That is fine,Except later it calls another stored procedure which inserts values fromanother table into this one.INSERT INTO TableNameSELECT *FROM TablenameObviously this is failing because the newly amended table now has dashes inthe fieldnames now.Now as the fieldnames are created dynamically I cannot simply do INSERTTablename ([Jan-05], [feb-5]) etc, as one would normally do,How could I do this bearing in mind its not always going to be 12 months weuse,Basically I am asking how can I modify this INSERT statement so it takesinto account the fieldnames with dashes in themBearing in mind I don't know what the fieldnames are called as they arecreated dynamically by a CURSOR in a different routine.And its not always 12 months.have you any ideas?and the dashes are requiredJayne
Is it possible to save variable values to the log file? I have a for each loop that loops through customer orders and I would like to know the order number when the package fails.
Hi all,I have a table in the DB which holds ISBN book nubers .. which, as you knowhave dashes (examlple: 0-98765-8-9).When I run a query from an ASP page to find the number, it doesn't returnanything:sISBN = "0-98765-8-9"OpenSQL(select URL from ISBN_Redirect where ISBN=" & sISBN )Can you give me a hind of what I am doing wrong?Thanks so much in advance.S
Using the "set values" tab, I am trying to set the values of package variables to strings that contain spaces and/or dashes. If I run the package using the integrations services server, it runs perfectly. If I schedule the package on SQL Server agent, I get the following error.
"Executed as user: GBPSQLServer. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Option "-restofstring" is not valid. The command line parameters are invalid. The step failed."
Looking at the command line generated by the SQL Agent interface, the value is quoted using double quotes like this:
/SQL "PackageName" /SERVER SQL1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "Package.Variables[LogFolder].Value";"\remoteservernameinetpub he-restofstring estofstring" /REPORTING E
It seems that the command line parser is not recognising the quotes, or maybe the slashes are throwing it off, or sql server agent is doing something funny to the command line before it executes it.
I have created a job which executes a procedure which executes and creates logs of StartTime and EndTime and records affected. The last step of the job sends mails to recipients from the logs table. Though I get the info correctly I get all annoying dashes for 4-5 lines between each line of message. How to remove the dashes.
Below is the code i use to send mails.
EXEC msdb.dbo.sp_send_dbmail  @profile_name='SQL Mail Service',  @recipients='biscribbler@gmail.com',  @copy_recipients='biscribbler@gmail.com',  @subject='SOFT SCRUB Completed for LiveDB',
How can I populate values for package variables inside a data flow task? I could do this inside a script task in control tab, but how inside a data flow task?
As there is no support of check points at data flow component level, we are trying to implement this on our own. In the process of implementing we have to save values of certain user defined variables in to an xml file. To do this, we added a data flow task in the event handler OnTaskFailure. Now we want to save values of the variables in to an xml file in the data flow. Can you please suggest me how can we save values of variables in an event handler?
I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.
I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.
Can somebody please tell me whether the following syntax is supportedor whether it's a "feature" that will someday stop working. It works inboth SQL Server 2000 and 2005 at the moment.declare @var varchar(1000)set @var = ''select @var = @var + colx from some_table where col1 = some_valuecolx is a varchar or at least is cast to one as part of the selectstatement. If the where clause would normally return more than one row,all returned values for colx are concatenated into @var.I've not seen this syntax before but that doesn't make it wrong ;-)Malc.
I have just developed my first full package and it has been, ahem, an adventure- but I can see the power of SSIS. I am splitting 1M rows in to up to 11 parts (therefore up to 11M rows) for several files and it takes a matter of seconds!
I have used some variables in the package and would like to see the results of these at the end of execution. They are purely for interest at the moment.
I think I can output them to a flat file, but is it possible to output them to the Immediate window at the end of execution?
I can not figure out how to have a watch on them either- is this possible? Ideally I would like a counter on screen next to my loop containers.
All of the web pages I have seen regarding debugging seem to assume that VB is being used to create the package.
is there a possibility to print the value of a variable in the debug console? it seems that Console.WriteLine in a script task doesn't work. or is there a better way in order to debug the value of a variable at a certain point?
I've got this query inside a Sql Task against a Excel connection and I'd like to insert that value into a user variable called "Proyecto". How do I such thing?
I have to generate a seq num and increment it by 1,i used execute sql task and wrote a proc using a variable(out) now this out variable has that value.Now i need to add a extra column to all the records coming in this current batch with the value in the variable.
I know that i can use derived column transformation ,but its not working,giving the following error.
the errors thrown are:
[OLE DB Destination [16]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Unspecified error". An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01779: cannot modify a column which maps to a non key-preserved table ". [OLE DB Destination [16]] Error: The "input "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component. [DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (16) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. [DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0209029. [OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. [DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. [DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.
i have created a variable with scope as package,used that in execute sql task.then using precedence constrains added a data flow task to it.
In the data flow task between the source and destination i placed a derived column transformation.
the execute sql task is running fine,dataflow task is failing.In dataflow task also source and destination is failing but derived column transformation is working.
HiI am new to the world of aspx, .net and C#.In aspx .net 2.0. I am trying to work out how to get a datagrid to perform an update. Using Visual Developer I have successfully added the control and specifed a select statement to return data via my SQLData Source. This works fine. However having specifed the control as editable I would like to perform an update through the datagrid and SQLDatasource. I see in the properties for the SQLDatasource object I can specify my update statement.However I do not understand how to get that update statement to have variable values and how newly entered values from the grid can be placed into these variables when the update takes place. Can someone please point me in the right direction? I have not found the MS doc very illuminating thus far and have not found any examples.Many ThanksT
Can someone show how to do this?I have a SqlDataSource1, and i have a SELECT * FROM Table1How would i get@ProdName@ProdNumber Into the following local variablesString ProductNameInt ProductNumber I’m using C# and ASP 2.0 VWDThanks for Help1
I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter. The problem is I don't know how many. I have a textbox that the user can enter search words in. The select string is built from the string of words that are entered, like this:For iCount = 0 To UBound(sArray) strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "Next Can I do this is a dataset method? How? If I can't, what are my options?Diane
