I wanted to insert values in columns as explained in below ex.
I am having a table that contains Column1,Column2,Column3,......,Column10.
Inside my for loop, i am getting Column1 value then Column2 then Column3 values and so on till Column10.
My requirement is that on each iteration,I wanted to insert value of Column1 in field Column1, value of Column2 in field Column2 and so on.
If I create a memory table inside a while loop, I expect that every loop the memory table is (newly) created and thus empty.
But when I test this, I see that on the second, third, ... run the inserted data in the memory table is still present and not cleared.
This can be reproduces with the following code:
DECLARE @tbl TABLE
( [id] BIGINT )
DECLARE @tbl2 TABLE
( [value] BIGINT )
INSERT INTO @tbl
VALUES (1), (2)
[Code] ....
/*
Expectation: twice 11 and 12 in @tbl2
Actual result: three times 11 and 12
*/
SELECT *
FROM @tbl2
Is this a wrong assumption or is this a bug in SQL Server? (Tested on: SQL Server 2014 and 2008).
I passed .net datatable from a .net app to a store procedure. From this store procedure, how to code to bulk insert (or another way) to SQL table?
View 7 Replies View RelatedI have a problem where I want to create a loop in my script to insert 0's into my table.
I have a temp table with a list of company codes.
SELECT CODE FROM ##SENData GROUP BY CODE
This produces the following;
CODE
00C
00D
00K
00M
00Q
00T
00V
00X (there are 110 of these codes)
I want to insert data into a different table in the following format.
+------+------------+-------+---------+
| CODE | Month | Value | Measure |
+------+------------+-------+---------+
| 00C | 01/09/2015 | 0 | HAR-01 |
| 00D | 01/09/2015 | 0 | HAR-01 |
| 00K | 01/09/2015 | 0 | HAR-01 |
| 00M | 01/09/2015 | 0 | HAR-01 |
| 00Q | 01/09/2015 | 0 | HAR-01 |
| 00T | 01/09/2015 | 0 | HAR-01 |
| 00V | 01/09/2015 | 0 | HAR-01 |
| 00X | 01/09/2015 | 0 | HAR-01 |
+------+------------+-------+---------+
The month will be set from a declared variable and the others will just be hard coded.
Hi,
Here is the steps I should take...
1- Check for the log table and find run status ( there is a date field which tells the day run)
2- Lets say last day was 2008-05-15, So I have to check A1.DDDDMMYYY file exists in the folder for each
day like A1.20080516,A1.20080517 and A1.20060518 ( until today)
3- if A1.20080516 text file exist then I have to move it to the table and same thing for other dates
like if A1.20080517 exists I have to load it to table and so on
it looks like for each loop, first I have to get the last date and then I have to check the file exists for each date and
if the date file exists then I have to load it into table...
Please tell me How can I do it. it looks complex looping...
thanks,
J
hello,
anyone for help?
what's the syntax of for.next, do while loop in Stored Proc?
ur help is much appreciated!
thanks,
I have a loop(while) statement I need to run inside a cursor statement. The loop creates records based on a frequency. The cursor and the loop work but the problem is that the cursor only reads the first record, runs the loop, but then ends. I am pasting the code below. Any help appreciated
declare dbcursor cursor for select uniq_id,account_id,created_by,encounter_id, start_date,date_stopped,sig_codes, ndc_id,modified_by from patient_medication where convert(datetime,start_date) = '10/20/2000' and date_stopped is not null
open dbcursor fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid, @sdate, @edate ,@sig_code, @ndcid, @modid
while (@@FETCH_STATUS <> -1)
begin
select @freq = SIG.sig_frequency FROM SIG where SIG.SIG_KEY = @sig_code
set @hfreq = @freq if @freq = 9 set @freq = 1 set @nodays = datediff(day, @sdate - 1, @edate)
while @cnter < @nodays
begin
while @fcnter < @freq + 1 begin insert into PATIENT_MEDICATION_DISPERSAL_ (uniq_id,account_id, occurance_id, encounter_id, ndc_id, ddate, frequency, sig_code,disp_create_id, disp_mod_id) values (@uniqid,@account_id,@fcnter, @entcid, @ndcid, @sdate, @freq, @sig_code,@createid, @modid )
set @fcnter = @fcnter + 1
set @erdate = @sdate
END
if @hfreq = 9
begin set @fcnter = 1
set @sdate = @sdate + 2
Set @cnter = @cnter + 2
end
else
begin
set @fcnter = 1
set @sdate = @sdate + 1
Set @cnter = @cnter + 1
end
end
end
close dbcursor
deallocate dbcursor
Hello,
is it possible to build a loop for the following statement?
CREATE VIEW vwObjects as (
Select 2001 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate >= '20010101' and o.sdate < '20010401'
union
Select 2001 as year, 2 as quarter, id as id
from dbo.objects o
where o.edate >= '20010301' and o.sdate < '20010701'
...
union
Select 2002 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate > '20020101' and o.sdate < '20020401'
...
)
I want a kind of calender for my olap cube, so I can get every active object in a special quarter resp year.
Thank you!
Hi,
A very strange thing happened to me. I have a package that includes two For each loop containers. Each container has script tasks, sequence containers, etc. Both are Foreach ADO Enumerator basis. It works without any problems until I changed the position of one of them in the Control flow and added some code in the script task. After these changes I executed the package and both of For each loop containers did not execute the tasks inside of them, any task. However the execution color on the containers was green (success). How can it be?
Your help is much welcome. Thanks in Advance.
João Cruz
I have three tables:
"PaymentsLog"
"DatePeriod"
"PaidOrders"
As per below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](
[Code] ....
Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?
Hi,
When i execute the following set of statements only 8 is getting inserted into table instead 6 and 8.
Create Table BPTest(id int)
Declare @Id Int
Set @Id = 0
While (@Id < 10)
Begin
begin tran
Insert into BPTest values (@id)
if(@Id > 5)
begin
if(@Id % 2 = 0)
begin
print 'true' print @Id
commit tran
end
else
begin
print 'false' print @Id
rollback tran
end
end
Set @Id = @Id + 1
End
Select * from BPTest
drop table BPTest
Please let me know the reason for this.
Thanks in advance
Regards,
K. Manivannan
I am trying to write a utility/query to get a report from a table. Belowis the some values in the table:table name: dba_daily_resource_usage_v1conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_b atch------------------------------------------------------------80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:3080 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:3045 |dcp_webu|DCP |5967 |75 |669 |11/16/2004 11:3095 |dcp_webu|XYZ |5967 |75 |669 |11/17/2004 11:30I need to write a query which for a given date (say 11/15/2004),generate a resource usage report for a given duration (say 3 days).Here is my query:************************************set quoted_identifier offdeclare @var1 intset @var1=0--BEGIN OUTER LOOPwhile @var1<=3 --INPUT runs the report for 3 daysbegindeclare @vstartdate char (10) --INPUT starting dateset @vstartdate='11/15/2004'--builds a range of datedeclare @var2 datetimeset @var2=(select distinct (dateadd(day,@var1,convert(varchar(10),last_batch,101)))--set @var2=(select distinct (dateadd(day,@var1,last_batch))from dba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@vstartdate)set @var1=@var1+1 --increments a daydeclare @var5 varchar (12)--set dateformat mdy--converts the date into 11/15/2004 format from @var2set @var5="'"+(convert(varchar(10),@var2,101))+"'"--print @var5 produces '11/15/2004' as resultdeclare @vloginame varchar (50)declare @vdbname varchar (50)--BEGIN INNER LOOPdeclare cur1 cursor read_only forselect distinct loginame,dbname fromdba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@var5--??????PROBLEM AREA ABOVE STATEMENT??????--print @var5 produces '11/15/2004' as result--however cursor is not being built and hence it exits the--inner loop (cursor)open cur1fetch next from cur1 into @vloginame, @vdbnamewhile @@fetch_status=0begin--print @var5 produces '11/15/2004' as resultdeclare @vl varchar (50)set @vl="'"+rtrim(@vloginame)+"'"declare @vd varchar (50)set @vd="'"+@vdbname+"'"--processes the cursorsdeclare @scr varchar (200)set @scr=("select max(cum_cpu) from dba_daily_resource_usage_v1 whereloginame="+@vl+" and dbname="+@vd+" and "+"convert(varchar(10),last_batch,101)="+@var5)--set @var3 =(select max(cum_cpu) from dba_daily_resource_usage_v1where--loginame=@vloginame and dbname=@vdbname--and convert(varchar (10),last_batch,101)=@var5)print @scr--exec @scrfetch next from cur1 into @vloginame, @vdbnameend--END INNER LOOPselect @var2 as "For date"deallocate cur1end--END OUTER LOOP************************************PROBLEM:Even though variable @var5 is being passed as '11/15/2004' inside thecursor fetch (see print @var5 inside the fetch), the value is not beingused to build the cursor. Hence, the cursor has no row set.Basically, the variable @var5 is not being processed/passed correctlyfrom outside the cursor to inside the cursor.Any help please.Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies View Related
im trying to do this
declare @count int
set @count=0
while @count<4
begin
set @count=@count+1
select *
from dbo.Categories
where CategoryPID=-1
union()
end
and i get a error
this is not the original code but i want to union all select statements
please help !!!
Hi everyone,
I've got a Foreach loop container which uses a Foreach ADO Enumerator and works fine.
But problem comes when I launch inside the general loop another Sql Task (select) which sometimes has rows and sometimes hasn't. When it have rows everything is fine the workflow follows fine but when it has not.
I obtain this error (obvioulsy)
[Execute SQL Task] Error: An error occurred while assigning a value to variable "GZon": "Single Row result set is specified, but no rows were returned.".
How to deal with that?
Thanks indeed
Hi, I'm trying to loop thru a table and insert records into another table in ssis. So far I have been able to get the data using a execute sql task set up to store the full result set into a variable called data. I then drug a foreach loop container out and selected the foreach ADO enumerator and used my variable data as the ADO object source variable. I then set up a new variable under variable mappings with index 0 to get the collection values. How do I take that variable and update another table using another sql task inside the foreach loop container? Is this possible?
thanks,
Hi,
I am using a foreach loop to go through the .txt files inside a folder.
Using a variable I can pickup the filenames the loop is going through.
At present there is a sql task inside the foreach loop which takes the filename as a parameter and passes this filename to a stored procedure.
Now I would like to add one extra step before this sql task. Would like to have a dataflow with flatfile source which connects to oledb destination.
The question is:
While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?
Please note, this is a different question to my other post.
Many Thanks
Hopefully this is an easy question:
Inside of a for each loop (looping through an ADO record set of objects to import) I have a data flow task (along with many other processes).... if the dataflow task suceeds I log success in a table. If it errors I want it to fail the dataflow task (which will fire off my Event Handler for that data flow and log the failure, email etc) BUT I want it to continue the loop - I can't seem to figure out how to get the data flow object not to fail the whole loop. If any other objects inside the foreach, other than the data flow, fail I would like the whole loop to fail. Also if possible (but not a requirement) I would like it to have a threshold where if the data flow fails X variable times it will fail the package.
I am having difficulty how to not fail the loop when the import data fails..... just looking for a simple "on error next" type logic for that specific object in the foreach but not the rest. Thanks in advance for the help/advice.
Hi,
I am using an XML task for validating the XML data against the Schema XSD. I have more XML Files with same the schema. I have to used a for loop container which has an XML task for validate XML. The loop container gets the XML File into variables name "XMLFileName" which the loop current file, in turn, used by XML Task for validation.
XML task is configured in the option "Validate". I have provided the XML Data in variable name "XMLFileName" also get the name from the loop container and XSD file content File Connection. XML Task stored the result in the another variable name "OKFormat". FailOnValidationFail property set to false.
It had the error when I run the package, the error msg as below:
Error: 0xC002F304 at XML Task, XML Task: An error occurred with the following error message: "Data at the root level is invalid. Line 1, position 1.".
Error: 0xC002928F at XML Task, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.
Task failed: XML Task
After that, I had to change the source type from Variable to File Connection, and had to test fixed to some xml file it had ok for validate the XML file againt XSD.
I don't know this is the wrong setting or bug of SSIS, anyone can guide me through.
I am new to SSIS world, so my question is very basic.
Setup:
In a company I work for we have 12 SQL servers each running between 1 and 3 databases with anywhere between 10 to 20 tables. I need to query some of these tables and merge results to the destination database.
The list of all these tables is stored in the separate table <SOURCES> of the following format [ServerName,DatabaseName,TableName]. Tables of my interest have identical structure (same columns) accross servers and databases.
Question:
How can I loop over servers and databases specified in <SOURCES> to run otherwise identical query against these tables?
I can easily retrieve [ServerName,DatabaseName,TableName] from <SOURCES> as string variables using FOREACH loop. The problem is now - how do I use string variables to set up Server, Database and Table name at run-time?
Thank you
I have to write a Stired Procedure with the following functionality.
Write a simple select query say (Select * from tableA) result is
ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4
Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?
I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
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>
View 1 Replies View RelatedHas anyone ever seen this issue?
If i place a loop container inside a sequence container the connectors within the loop container disappear.... is this a bug - will the loop tasks still run in the correct order?? or will they fire off willy nilly??
Hello,
I have 10 tables (T1Orj,T2Orj,€¦T10Orj) and I need to find modified rows from each table and insert them to T1Bak, T2Bak, €¦T10Bak. Although original and bak tables have the common fields , the original tables have more fields than bak tables, and T1Orj, T2Orj, €¦ T10Orj tables have different table structures.
I can go head and write a insert into query for each table, I am just wondering Is there any way I can do this in a loop for all tables?
In C# .NET I have the possible to create some validations of my data, with regulary expressions. Do SQL have the same feature? I will like to do an data validation of all my insert statement inside the sql-server. Is that possible?
View 5 Replies View Related
Help with Looping in a SSIS Package
Scenario:
We have a web app that lets our existing clients insert new locations into a table (Clients) in a SQL Server DB. This table has an Identity column as the PK. This table also has another ID field HBID that is the PK for another table (HLocation) in another Database system (Sybase). The HBID field is given a Default value of €˜1€™ during an insert operation in our (SQL Server) table, Clients. The Sybase database uses a Sequence table for inserting a PK into the table. Whenever our clients insert a new record in Clients (SQL Server), I need to generate a new HBID from the Sequence table in (Sybase), update the HBID in the Clients table (SQL Server) and then finally insert the record with the new HBID into table HLocation (Sybase).
I have devised a SSIS package for this as following: Note all variables are scoped at the Package Level.
Execute SQL Task #1 that drives a For Each Loop with the following properties:
General:
Connection Type: OLE DB
Connection: SQL Server DB
SQL SourceType: Direct Input
SQLStatement:
SELECT COID, HBID, Name, DateCreated
FROM Client
WHERE HBID = '1'
ResultSet: = Full Resultset
Result Set:
Result Name = 0
VariableName = USER::rsClient Variable Type = Object
ForEachLoop with the follwing properties:
Collection:
Foreach ADO Enumerator
ADO Object Source Variable = USER::rsClient
Enumeration Mode = Rows in first table
Variable Mappings:
Variable: Index:
COID 0
HBID 1
Name 2
DateCreated 3
Inside of the ForEachLoop I have another Execute SQL Task to generate a new HBID from Sybase set up as following.
Execute SQL Task #2
Connection Type: OLE DB
Connection: Sybase
SQL SourceType: Direct Input
SQLStatement:
UPDATE Autoinc SET INC_LAST = (INC_LAST+1) WHERE INC_KEY ='HBID';
SELECT INC_LAST AS NewHBID FROM Autoinc WHERE INC_KEY ='HBID'
ResultSet = SingleRow
Result Set:
Result Name = NewHBID
VariableName = USER::NewHBID, VariableType = Int32
Also Inside the ForEachLoop is a Script Task that has all of my variables as ReadWrite = COID, HBID, Name, DateCreated and NewHBID. I concatenate the values in a string a pass the string into a Message Box to make sure they are looping correctly and they are.For example the results might look like the following:
12698, 1, John Doe Trucking, 10/1/2007, 14550
13974, 1, Joe Smith Trucking, 10/1/2007, 14551
10542, 1, Dave Jones Trucking, 10/1/2007, 14552
Etc.
The values 14550 -14552 are the new HBID being generated in the loop.
The problem is that when I try to Update the HBID in the Client table (SQL Server) with another Execute SQL Task I keep getting the same NewHBID number.
In this case 14550 would be updated for every record instead of the next number in the loop.
I have set up Execute SQL Task #3 as follows:
General:
Connection Type: OLE DB
Connection: SQL Server DB
SQL SourceType: Direct Input
SQLStatement:
UPDATE Client
SET HBID = ?
WHERE HBID = '1'(SELECT COID, HBID, Name, DateCreated FROM Client)
ResultSet: = Full ResultSet
Result Set:
Result Name = 0
VariableName = USER::rsNewClient, VariableType = Object
Parameter Mapping:
VariableName USER::NewHBID, Direction = INPUT, DataType = Long
ParameterName = 0
I€™ve tried putting Execute SQL Task #3 inside of the ForEachLoop, connecting it to the output of the ForEachLoop. I€™ve tried setting up a dataflow with a Derived Column using the USER::NewHBID as the Expression.
I still get the same results, 14550 added to every row.
Can any one help or shed some light?
Any and all suggestion will be deeply appreciated!
Thanks !!!
I have a report where in I want to show each record on a separate page.
So, to achieve that I took a single cell from table control, expanded it and used all the controls in that single cell. This looks nice so far.
Now, I also have to show a sub grid on each record. So I took a table control and added on the same single cell and tried to add a parent group to the table row.
When I preview, it throws this error.
"The tablix has a detail member with inner members. Detail members can only contain static inner members."
What am I doing wrong? How can I achieve table grouping inside a table cell?
I have a table with employee references and a startdate.
I want to insert into a new table an entry for each employee for each date since their startdate to today.
Eg
EMPTABLE
empref,startdate
0001,01.01.2012
0002,02.02.2012
What I require is
NEWTABLE
empref, Date
0001,01.01.2012
0001,01.02.2012
0001,01.03.2012
......
0001,08.27.2012
0002,02.02.2012
0002,02.03.2012
......
0002,08.27.2012
Hi everyone
I want to know if it's possible to do a for/while-loop so i can use INSERT
Look:
I've this int [] test = new test[140];
But i need to insert for every value (140) a number
so normally it would be :
INSERT ... (case1, case2, case3 ...) value (test[1],test[2],test[3] ...)
But isn't there a way to it with a loop?
SOmething Like this ?
for( int i = 0 , i< 140, i++) {
INSERT case[i] value test[i]
}
Thanks in advance
Jonas
Hi,
My procedure to implement a task is like this
I will be using execute SQL task to fetch the records from source,after this wanna use For each loop to access each record one at a time,perform some trnsformations and insert that record into destination.
Help me in accessing the data stored in the Variable(SQL task) in Dataflow task of foreach loop.
I have ForEach Loop using Foreach File Enumerator. Within this loop I have SQL Task containing an Insert statement. When I run the Insert statement in query builder the transaction inserts data into a table as expected.
However, when actually running the process I am getting the error message:
Executing the query "INSERT INTO dbo.TEST_TABLE
..." failed with the following error: "Value does not fall within the expected range.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I currently have the ResultSet to "None" and have defined the parameter I am using. Where the process seems to joke is on my file_Name variable will I am trying to insert only part of the file name.
My insert statement looks as follows:
INSERT INTO dbo.TEST_TABLE
(IID, AN8, File_Type, IB_OB, File_Name, Processed_Flag, ReceiptDateTime)
VALUES
(
'1111',
'123',
'888',
'IB',
RIGHT(LEFT(?, LEN(?) - 4), LEN(?) - 24),
'I',
GETDATE())
Hello All
I was trying to insert some row from one table to another of different database.
I was using Execute SQL task along with Foreach loop container.
In my execute SQL task I am using this query
SET IDENTITY_INSERT dbo.Table1 ON
INSERT INTO dbo.Table1
SELECT * FROM DB2.dbo.Table2
WHERE TableKey = ?
When executed I get this error:
failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
While the same query when executed in Management Studio Its successful.
The properties I set
For Each Loop Editor Settings:
1) Collection: a) Enumerator Set to ForEach ADO Enumerator
b) ADO Object Source Variable: User:bjectVariablename
c) Checked Rows in the first table
2) Variable Mapping: New Int Variable2 and Index = 0 to set it to first colunm.
3) Expression: Left blank
Execute SQL Task Editor:
1) General: a) Timeout : 0
b) CodePage: 1252
c) Result Set: None
d) SQLSourceType: Directinput
e) SQL Statement: SET IDENTITY_INSERT dbo.Table1 ON INSERT INTO dbo.Table1 SELECT * FROM DB2.dbo.Table2 WHERE TableKey = ?
f) BypassPrepare: False
2)Parameter Mapping: Variable Name : New Integer variable2 selected
Direction: Input
DataType: Long
ParameterName: 0
Can somebody help me in this regards.
Reference:
a) http://www.whiteknighttechnology.com/cs/blogs/brian_knight/archive/2006/03/03/126.aspx
Hi Guys
The problem is...
When i try to bulk insert a single file its working fine. When i want to loop a set of files in a folder and use Foreach Loop and BulkInsert Task...its failing..
In the flat file connection When i specify usage type as existing file...its loading the same file "n" number of times where n is the number of files in the folder.
When i select usage type as existing folder i get error " Cannot bulk load because the file "....folder" could not be opened. Can someone help me out with this?
I have sql 2005 as as separate instance...beside my 2000 which is default
Thanks!