Can anybody help me with this?
I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:
Dim rs1, strSQL
strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)
Do While NOT rs1.EOF
strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)
rs1.MoveNext()
Loop
Function Main()
Main = DTSTaskExecResult_Success
End FunctionLook at http://dbforums.com/t673820.html
Hugh Scott
Originally posted by Bigced_21
Can anybody help me with this?
I'm trying to update three fields in one table from an import table. Is the anything you see missing in my code:
Dim rs1, strSQL
strSQL = "Select safety_valve_cap, hydro_date, hydro_psi, state_No From HBC_ZImport"
set rs1 = objConn.Execute(strSQL)
Do While NOT rs1.EOF
strSQL = "Update HBC_Boiler_Inspection set Safety_Valve_Cap = '" & rs1 ("Safety_Valve_Cap") & "', "
strSQL = strSQL & " Hydro_PSI = '" & rs1 ("Hydro_PSI") & "', "
strSQL = strSQL & " Hydro_Date = '" & rs1 ("Hydro_Date") & "' "
strSQL = strSQL & " where Boiler_ID = (Select ID from HBC_Boiler where State_No = ' " & rs1 ( "State_No") & " ') & " ' "
objConn.Execute(strSQL)
rs1.MoveNext()
Loop
Function Main()
Main = DTSTaskExecResult_Success
End Function
Showing posts with label update. Show all posts
Showing posts with label update. Show all posts
Thursday, March 29, 2012
Monday, March 26, 2012
getting left _
how can I can update a table ::
I must get all the left part of a columns part1_part2
I dont know how many characters
something like :
UPDATE Users SET Users.Info = left(Users.name, _ ? or split('_'))
in that case Users.name = part1
i must update only the one with a '_' and do nothing for the others
thank youTry variations of these to get what you want:
declare @.Name varchar(50)
set @.Name = 'Part1Part2'
select left(@.Name, charindex('_', @.Name + '_')-1)
select substring(@.Name, charindex('_', @.Name + '_')+1, 50)
...or if the names are more complex (such as including middle names, initials, or titles), I have a name splitting function I can give you.|||thank you BlindMan
but the name of the column is Users.Name and part1_part2 was an exemple of value
in that exemple i must get part1
it could be werfewgf fff jjj_rgfregreg
and in that case i must get werfewgf fff jjj
allways the left part of '_'|||with select substring(Users.Name, charindex('_', Users.Name + '_')+1, 50) FROM Users
I get exactly the right part
the left will be great :-)
but I get all even if there is no '_'|||Can you supply some sample data and what the expect result is suppose to be?
My esp usb port is clogged|||sample data >> expect result
wefwefqrwf_hhhh >> wefwefqrwf
fff jj ff_rgdefrhbg >> fff jj ff
acacac145 i4_kk >> acacac145 i4
dddd >> no result, I dont take it there is no _ in the sample data
thanks Brett
I must get all the left part of a columns part1_part2
I dont know how many characters
something like :
UPDATE Users SET Users.Info = left(Users.name, _ ? or split('_'))
in that case Users.name = part1
i must update only the one with a '_' and do nothing for the others
thank youTry variations of these to get what you want:
declare @.Name varchar(50)
set @.Name = 'Part1Part2'
select left(@.Name, charindex('_', @.Name + '_')-1)
select substring(@.Name, charindex('_', @.Name + '_')+1, 50)
...or if the names are more complex (such as including middle names, initials, or titles), I have a name splitting function I can give you.|||thank you BlindMan
but the name of the column is Users.Name and part1_part2 was an exemple of value
in that exemple i must get part1
it could be werfewgf fff jjj_rgfregreg
and in that case i must get werfewgf fff jjj
allways the left part of '_'|||with select substring(Users.Name, charindex('_', Users.Name + '_')+1, 50) FROM Users
I get exactly the right part
the left will be great :-)
but I get all even if there is no '_'|||Can you supply some sample data and what the expect result is suppose to be?
My esp usb port is clogged|||sample data >> expect result
wefwefqrwf_hhhh >> wefwefqrwf
fff jj ff_rgdefrhbg >> fff jj ff
acacac145 i4_kk >> acacac145 i4
dddd >> no result, I dont take it there is no _ in the sample data
thanks Brett
Friday, March 23, 2012
GETDATE() Function Issue
Hi Everyone,
I'm running into an issue trying to update a column on a remote database.
The server is linked from the server I'm calling this update statement. The
syntax is as follows:
UPDATE Server1.Test.dbo.Testdata
SET dtUpdated = GETDATE()
WHERE intID = 111
The problem is that the update takes a very long time to complete. Now if I
declare a variable with a datetime and set that variable to GETDATE(), the
update takes seconds. Is there some known issue? Can anyone help with this
?
I have noticed that I can update small size tables, but it takes much longer
to update large size tables. The columns in the "Where" clause have indexes
on them and that can't be the issue since my test using the variable worked
quicker. I'm just wondering why I can't use the GETDATE() function directly
.
DarrenDarren
No, it works fine with GETDATE() function too. Please take a look at
execution plan of the query (with GETDATE() and without) and see what is
going on
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:8D7E1367-7B04-4F57-B671-F79AB6F6E60C@.microsoft.com...
> Hi Everyone,
> I'm running into an issue trying to update a column on a remote database.
> The server is linked from the server I'm calling this update statement.
> The
> syntax is as follows:
> UPDATE Server1.Test.dbo.Testdata
> SET dtUpdated = GETDATE()
> WHERE intID = 111
> The problem is that the update takes a very long time to complete. Now if
> I
> declare a variable with a datetime and set that variable to GETDATE(), the
> update takes seconds. Is there some known issue? Can anyone help with
> this?
> I have noticed that I can update small size tables, but it takes much
> longer
> to update large size tables. The columns in the "Where" clause have
> indexes
> on them and that can't be the issue since my test using the variable
> worked
> quicker. I'm just wondering why I can't use the GETDATE() function
> directly.
> Darren|||Run it again and see if there is any issues
Madhivanan|||I looked at the execution plan and it is doing what I thought it would. The
execution plan shows that it does a remote scan to look at the whole table
then it will filter by the where clause. This is the source of my problem.
For a large table that holds over 4 million records, it has to scan that
whole table before filtering on the primary key I'm already passing it. I
don't understand why on the remote server it must scan the whole table
instead of already filtering by the where clause. When I use a variable to
update the date column it shows a remote query for the update. Why is this
happening? Did some setting happen that I don't know about? How can I trac
k
down this issue?
"Uri Dimant" wrote:
> Darren
> No, it works fine with GETDATE() function too. Please take a look at
> execution plan of the query (with GETDATE() and without) and see what is
> going on
>
>
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:8D7E1367-7B04-4F57-B671-F79AB6F6E60C@.microsoft.com...
>
>
I'm running into an issue trying to update a column on a remote database.
The server is linked from the server I'm calling this update statement. The
syntax is as follows:
UPDATE Server1.Test.dbo.Testdata
SET dtUpdated = GETDATE()
WHERE intID = 111
The problem is that the update takes a very long time to complete. Now if I
declare a variable with a datetime and set that variable to GETDATE(), the
update takes seconds. Is there some known issue? Can anyone help with this
?
I have noticed that I can update small size tables, but it takes much longer
to update large size tables. The columns in the "Where" clause have indexes
on them and that can't be the issue since my test using the variable worked
quicker. I'm just wondering why I can't use the GETDATE() function directly
.
DarrenDarren
No, it works fine with GETDATE() function too. Please take a look at
execution plan of the query (with GETDATE() and without) and see what is
going on
"Darren" <Darren@.discussions.microsoft.com> wrote in message
news:8D7E1367-7B04-4F57-B671-F79AB6F6E60C@.microsoft.com...
> Hi Everyone,
> I'm running into an issue trying to update a column on a remote database.
> The server is linked from the server I'm calling this update statement.
> The
> syntax is as follows:
> UPDATE Server1.Test.dbo.Testdata
> SET dtUpdated = GETDATE()
> WHERE intID = 111
> The problem is that the update takes a very long time to complete. Now if
> I
> declare a variable with a datetime and set that variable to GETDATE(), the
> update takes seconds. Is there some known issue? Can anyone help with
> this?
> I have noticed that I can update small size tables, but it takes much
> longer
> to update large size tables. The columns in the "Where" clause have
> indexes
> on them and that can't be the issue since my test using the variable
> worked
> quicker. I'm just wondering why I can't use the GETDATE() function
> directly.
> Darren|||Run it again and see if there is any issues
Madhivanan|||I looked at the execution plan and it is doing what I thought it would. The
execution plan shows that it does a remote scan to look at the whole table
then it will filter by the where clause. This is the source of my problem.
For a large table that holds over 4 million records, it has to scan that
whole table before filtering on the primary key I'm already passing it. I
don't understand why on the remote server it must scan the whole table
instead of already filtering by the where clause. When I use a variable to
update the date column it shows a remote query for the update. Why is this
happening? Did some setting happen that I don't know about? How can I trac
k
down this issue?
"Uri Dimant" wrote:
> Darren
> No, it works fine with GETDATE() function too. Please take a look at
> execution plan of the query (with GETDATE() and without) and see what is
> going on
>
>
>
> "Darren" <Darren@.discussions.microsoft.com> wrote in message
> news:8D7E1367-7B04-4F57-B671-F79AB6F6E60C@.microsoft.com...
>
>
Wednesday, March 21, 2012
GetChanges Update problem
I have the below C# routine which is working but now I need to write its newly randomized L_Rank values back up to the Sql Server. How can i do that in Button1_Click? DataTable 'dt' contains only two fields - L_ID and L_Rank. I have a stored procedure but I do not know how to call it in Button1_Click and pass the parameters it is looking for.
SP:
PROCEDURE RandomizeLinks
@.L_ID int,
@.L_Rank int
AS
UPDATE tblLinkInfo_OLD2 SET L_Rank = @.L_Rank
WHERE (L_ID = @.L_ID)
protected void Button1_Click(object sender, EventArgs e)
{
GetRandLinks();
DataTable dt = GetRandLinks();
int RowIncrement;
RowIncrement = 0;
System.Random myRandom = new System.Random();
foreach (DataRow row in dt.Rows)
{
int LinkRank = myRandom.Next(25, 250);
row["L_Rank"] = LinkRank;
RowIncrement++;
}
Is what I want to do possible - it seems easy but nothing I do works.
|||Am I asking the question wrong - Can you use a DataTable to update a SQL Table and can it be done in a batch UPDATE as opposed to incrementing through every row?
|||anyone?
Monday, March 19, 2012
Get value of parameters passed in stored procedure in a trigger
In updating my tables (insert/update), i use stored procedures.
Some of the values passed as parameters to the stored procedures
are only necessary for audit trail only and not for updating the tables.
How can i get hold of these parameter values while inside a trigger?Put the parameters in a permanent table or a local temp table.
David Portas
SQL Server MVP
--
"manK" <manK@.discussions.microsoft.com> wrote in message
news:EA0833BF-A070-4720-9E50-9C80EAE45FF9@.microsoft.com...
> In updating my tables (insert/update), i use stored procedures.
> Some of the values passed as parameters to the stored procedures
> are only necessary for audit trail only and not for updating the tables.
> How can i get hold of these parameter values while inside a trigger?
>
Some of the values passed as parameters to the stored procedures
are only necessary for audit trail only and not for updating the tables.
How can i get hold of these parameter values while inside a trigger?Put the parameters in a permanent table or a local temp table.
David Portas
SQL Server MVP
--
"manK" <manK@.discussions.microsoft.com> wrote in message
news:EA0833BF-A070-4720-9E50-9C80EAE45FF9@.microsoft.com...
> In updating my tables (insert/update), i use stored procedures.
> Some of the values passed as parameters to the stored procedures
> are only necessary for audit trail only and not for updating the tables.
> How can i get hold of these parameter values while inside a trigger?
>
Get value from query to update another another table
I have a query that simply slelcts the min value of a specified field
from one table, I want to take that value to update a field in
annother table, just can not figure it out.(rzito@.si.rr.com) writes:
UPDATE tbl
SET col = (SELECT MIN(somecol) FROM othertbl)
WHERE ...
from one table, I want to take that value to update a field in
annother table, just can not figure it out.(rzito@.si.rr.com) writes:
Quote:
Originally Posted by
I have a query that simply slelcts the min value of a specified field
from one table, I want to take that value to update a field in
annother table, just can not figure it out.
UPDATE tbl
SET col = (SELECT MIN(somecol) FROM othertbl)
WHERE ...
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Get User-Input into a Stored Proc
Hi,
Is there any way to accomplish this (SQL Server 2000):
Client-App sends an Update to an SP.
The SP has to perform various actions, amongst one setting a FK.
Normally there will only be One FK possible per Update.
BUT
It can happens there are multiple FK's possible for One Update.
Under that condition the possible FK's should be sent back to the App where
the User can select One FK.
Once that FK is returned to the SP, the Procedure can continue it's actions.
I fear this is science fiction though I'd like to be sure :-)
TIA,
MichaelMichael,
A stored procedure has no way to request further input from the user. You
can, of course, write two stored procedures (1) figure out if all is well
and (2) do the work, then design your app to use the procedures.
RLF
"Michael Maes" <michael.maes@.community.nospam> wrote in message
news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
> Hi,
> Is there any way to accomplish this (SQL Server 2000):
> Client-App sends an Update to an SP.
> The SP has to perform various actions, amongst one setting a FK.
> Normally there will only be One FK possible per Update.
> BUT
> It can happens there are multiple FK's possible for One Update.
> Under that condition the possible FK's should be sent back to the App
> where
> the User can select One FK.
> Once that FK is returned to the SP, the Procedure can continue it's
> actions.
> I fear this is science fiction though I'd like to be sure :-)
> TIA,
>
> Michael
>|||You can of course do all of this - but not in a stored proc. Why would that
matter? Stored procs are not for UI code.
David Portas
SQL Server MVP
--
"Michael Maes" <michael.maes@.community.nospam> wrote in message
news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
> Hi,
> Is there any way to accomplish this (SQL Server 2000):
> Client-App sends an Update to an SP.
> The SP has to perform various actions, amongst one setting a FK.
> Normally there will only be One FK possible per Update.
> BUT
> It can happens there are multiple FK's possible for One Update.
> Under that condition the possible FK's should be sent back to the App
> where
> the User can select One FK.
> Once that FK is returned to the SP, the Procedure can continue it's
> actions.
> I fear this is science fiction though I'd like to be sure :-)
> TIA,
>
> Michael
>|||Hi David & Russel,
Thanks for your replies.
The reason I would like to implement this is that various Bit, DateTime & FK
fields have to be set accross various tables depending on certain Updates on
another table.
On itself this is pretty straight foreward, but in the App this procedure
can be started on many forms under various ways and conditions.
It is the Undoing of this operation that is tadious in the App. (so many
variations). It makes it easy to break the logic.
Having it all done by an Update-Trigger on that table, causing it to launch
various sp's, makes it all solid.
The only caveat is that there * can * be more then One FK and it's
impossoble for non human-logic to determine which to use.
Thus I was "hoping" there would be any means to have a user-interaction on
this level.
I think I will have to come up with an alternative.
Any way: thanks for the input guys!
Regads,
Michael
"David Portas" wrote:
> You can of course do all of this - but not in a stored proc. Why would tha
t
> matter? Stored procs are not for UI code.
> --
> David Portas
> SQL Server MVP
> --
> "Michael Maes" <michael.maes@.community.nospam> wrote in message
> news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
>
>|||> The only caveat is that there * can * be more then One FK and it's
> impossoble for non human-logic to determine which to use.
I've no idea what this means. The foreign keys on a table are fixed
unless you are executing DDL in your update. So how can there be any
doubt about which they are?
David Portas
SQL Server MVP
--|||I think I have expressed myself badly.
it's all about the Value of the FK to save.
To put it in a simplified example:
A workorder can consist of various tasks.
Each task is a certain day performed by a certain technician (the FK)
In another table (installation - statistics) you can see what date the last
visit was by which technician for what type of job, ...
Normally an orders' childrececords (tasks) is always performed by the same
technician. But sometimes more technicians are assigned to an order (each
having his own taskrow).
Since the Technician FK only holds one Value, the user has to decide which
technician to assign for "the last visit" because it's important for
follow-up & support to know who was the "most important" technician. It's
impossible for 'Code' to know which one to choose.
Hence the User-Input.
I hope this clarifies a bit my 'case'.
Regards,
Michael
"David Portas" wrote:
> I've no idea what this means. The foreign keys on a table are fixed
> unless you are executing DDL in your update. So how can there be any
> doubt about which they are?
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Michael,
Is it possible for you to provide a simplified table schema with sample
data to clarify it more? I am afriad one of the most possible resolution is
redesign the table structure or application structure.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
Thanks for your reply.
For the moment I have worked around the issue, so I guess the 'Case is
closed' :-)
Thanks,
Michael
"Michael Cheng [MSFT]" wrote:
> Hi Michael,
> Is it possible for you to provide a simplified table schema with sample
> data to clarify it more? I am afriad one of the most possible resolution i
s
> redesign the table structure or application structure.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Michael,
You are welcome and thanks for the update.
If you have any questions or concerns next time, don't hesitate to let me
know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Is there any way to accomplish this (SQL Server 2000):
Client-App sends an Update to an SP.
The SP has to perform various actions, amongst one setting a FK.
Normally there will only be One FK possible per Update.
BUT
It can happens there are multiple FK's possible for One Update.
Under that condition the possible FK's should be sent back to the App where
the User can select One FK.
Once that FK is returned to the SP, the Procedure can continue it's actions.
I fear this is science fiction though I'd like to be sure :-)
TIA,
MichaelMichael,
A stored procedure has no way to request further input from the user. You
can, of course, write two stored procedures (1) figure out if all is well
and (2) do the work, then design your app to use the procedures.
RLF
"Michael Maes" <michael.maes@.community.nospam> wrote in message
news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
> Hi,
> Is there any way to accomplish this (SQL Server 2000):
> Client-App sends an Update to an SP.
> The SP has to perform various actions, amongst one setting a FK.
> Normally there will only be One FK possible per Update.
> BUT
> It can happens there are multiple FK's possible for One Update.
> Under that condition the possible FK's should be sent back to the App
> where
> the User can select One FK.
> Once that FK is returned to the SP, the Procedure can continue it's
> actions.
> I fear this is science fiction though I'd like to be sure :-)
> TIA,
>
> Michael
>|||You can of course do all of this - but not in a stored proc. Why would that
matter? Stored procs are not for UI code.
David Portas
SQL Server MVP
--
"Michael Maes" <michael.maes@.community.nospam> wrote in message
news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
> Hi,
> Is there any way to accomplish this (SQL Server 2000):
> Client-App sends an Update to an SP.
> The SP has to perform various actions, amongst one setting a FK.
> Normally there will only be One FK possible per Update.
> BUT
> It can happens there are multiple FK's possible for One Update.
> Under that condition the possible FK's should be sent back to the App
> where
> the User can select One FK.
> Once that FK is returned to the SP, the Procedure can continue it's
> actions.
> I fear this is science fiction though I'd like to be sure :-)
> TIA,
>
> Michael
>|||Hi David & Russel,
Thanks for your replies.
The reason I would like to implement this is that various Bit, DateTime & FK
fields have to be set accross various tables depending on certain Updates on
another table.
On itself this is pretty straight foreward, but in the App this procedure
can be started on many forms under various ways and conditions.
It is the Undoing of this operation that is tadious in the App. (so many
variations). It makes it easy to break the logic.
Having it all done by an Update-Trigger on that table, causing it to launch
various sp's, makes it all solid.
The only caveat is that there * can * be more then One FK and it's
impossoble for non human-logic to determine which to use.
Thus I was "hoping" there would be any means to have a user-interaction on
this level.
I think I will have to come up with an alternative.
Any way: thanks for the input guys!
Regads,
Michael
"David Portas" wrote:
> You can of course do all of this - but not in a stored proc. Why would tha
t
> matter? Stored procs are not for UI code.
> --
> David Portas
> SQL Server MVP
> --
> "Michael Maes" <michael.maes@.community.nospam> wrote in message
> news:DD6A2970-CADA-4CF7-8188-9E95B73AE646@.microsoft.com...
>
>|||> The only caveat is that there * can * be more then One FK and it's
> impossoble for non human-logic to determine which to use.
I've no idea what this means. The foreign keys on a table are fixed
unless you are executing DDL in your update. So how can there be any
doubt about which they are?
David Portas
SQL Server MVP
--|||I think I have expressed myself badly.
it's all about the Value of the FK to save.
To put it in a simplified example:
A workorder can consist of various tasks.
Each task is a certain day performed by a certain technician (the FK)
In another table (installation - statistics) you can see what date the last
visit was by which technician for what type of job, ...
Normally an orders' childrececords (tasks) is always performed by the same
technician. But sometimes more technicians are assigned to an order (each
having his own taskrow).
Since the Technician FK only holds one Value, the user has to decide which
technician to assign for "the last visit" because it's important for
follow-up & support to know who was the "most important" technician. It's
impossible for 'Code' to know which one to choose.
Hence the User-Input.
I hope this clarifies a bit my 'case'.
Regards,
Michael
"David Portas" wrote:
> I've no idea what this means. The foreign keys on a table are fixed
> unless you are executing DDL in your update. So how can there be any
> doubt about which they are?
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Michael,
Is it possible for you to provide a simplified table schema with sample
data to clarify it more? I am afriad one of the most possible resolution is
redesign the table structure or application structure.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
Thanks for your reply.
For the moment I have worked around the issue, so I guess the 'Case is
closed' :-)
Thanks,
Michael
"Michael Cheng [MSFT]" wrote:
> Hi Michael,
> Is it possible for you to provide a simplified table schema with sample
> data to clarify it more? I am afriad one of the most possible resolution i
s
> redesign the table structure or application structure.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi Michael,
You are welcome and thanks for the update.
If you have any questions or concerns next time, don't hesitate to let me
know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
get triggers latest update date
Is there a way in MSSQL 2000 to get trigger's latest update date?
sysobject table only has creation date of a trigger and I've been using ALTER TRIGGER command to modify it.
Thanks,
IgorUnfortunately, no. You can't get the last modified date for any SQL Server objects. You might instead do a DROP and CREATE when modifying your triggers.
sysobject table only has creation date of a trigger and I've been using ALTER TRIGGER command to modify it.
Thanks,
IgorUnfortunately, no. You can't get the last modified date for any SQL Server objects. You might instead do a DROP and CREATE when modifying your triggers.
Terri
Friday, February 24, 2012
get recordcount from Oracle and update sql server table
I posted this question a little while ago but was not able to implement it. Now I am back to the same issue. Basically I want to get a recordcount from a table in Oracle and update an existing record in sql server with the value.
I am trying to accomplish this using a Execute SQL Task. In this task I am pointing to a Oracle DB that I am able to query from SSIS so connectivity is not an issue.
I have defined a variable EmpRC of type int32.
I have a following the the SQL Task:
query: select count(*) from emp;
result set=single row.
and on result set tab ResultName =0 and variable name is same defined above : User::EmpRC
I get an error when I run this:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "EmpCompRC": "Unsupported data type on result set binding 0.".
I have tried using different data types for EmpRC but having no luck. any ideas?
Not sure if this is a typo, but you keep refering to variable EmpRC, yet the error from ssis refers to a variable EmpCompRC.
Also, why don't you try using that sql statement in a data flow task to see what data type is assigned to it.
|||
Anthony Martin wrote:
Not sure if this is a typo, but you keep refering to variable EmpRC, yet the error from ssis refers to a variable EmpCompRC.
Also, why don't you try using that sql statement in a data flow task to see what data type is assigned to it.
Oracle doesn't have "integer" data types, that's why it doesn't work. Oracle only has NUMERIC data types, and when used without a precision, it is to be considered an "integer." It's a pain in the a$$ and I don't think Microsoft has any intentions of fixing the problem:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264932
[Microsoft follow-up]|||
so is there any workaround? I am able to see the link but when click on view workaround it takes me to my msdn profile.
|||
so is there any workaround? I am able to see the link but when click on view workaround it takes me to my msdn profile.
This is the referenced link in the workaround section of that Connect article:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=2&SiteID=1&PostID=107027
I don't think it will help in the end though.
|||
that is a disappointment.
|||well I figured out how to accomplish this. So hopefully everyone else wont have to bang their head on the 'wall of data types'.
1. well basically you will create a variable in SSIS of data type String.
2. you will have to convert the returning data type of select count(*) to a character in oracle. e.g.
select to_char(count(*)) from emp
3. create another SQL Task after the SQL Task for "count(*)". and in the parameter tab set the datatype to varchar with variable same as ResultSetName from previous SQL Task.
hope this is clear enough.
get recordcount from Oracle and update sql server table
I posted this question a little while ago but was not able to implement it. Now I am back to the same issue. Basically I want to get a recordcount from a table in Oracle and update an existing record in sql server with the value.
I am trying to accomplish this using a Execute SQL Task. In this task I am pointing to a Oracle DB that I am able to query from SSIS so connectivity is not an issue.
I have defined a variable EmpRC of type int32.
I have a following the the SQL Task:
query: select count(*) from emp;
result set=single row.
and on result set tab ResultName =0 and variable name is same defined above : User::EmpRC
I get an error when I run this:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "EmpCompRC": "Unsupported data type on result set binding 0.".
I have tried using different data types for EmpRC but having no luck. any ideas?
Not sure if this is a typo, but you keep refering to variable EmpRC, yet the error from ssis refers to a variable EmpCompRC.
Also, why don't you try using that sql statement in a data flow task to see what data type is assigned to it.
|||
Not sure if this is a typo, but you keep refering to variable EmpRC, yet the error from ssis refers to a variable EmpCompRC.
Also, why don't you try using that sql statement in a data flow task to see what data type is assigned to it.
Oracle doesn't have "integer" data types, that's why it doesn't work. Oracle only has NUMERIC data types, and when used without a precision, it is to be considered an "integer." It's a pain in the a$$ and I don't think Microsoft has any intentions of fixing the problem:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=264932
[Microsoft follow-up]|||
so is there any workaround? I am able to see the link but when click on view workaround it takes me to my msdn profile.
|||
so is there any workaround? I am able to see the link but when click on view workaround it takes me to my msdn profile.
This is the referenced link in the workaround section of that Connect article:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=2&SiteID=1&PostID=107027
I don't think it will help in the end though.
|||
that is a disappointment.
|||well I figured out how to accomplish this. So hopefully everyone else wont have to bang their head on the 'wall of data types'.
1. well basically you will create a variable in SSIS of data type String.
2. you will have to convert the returning data type of select count(*) to a character in oracle. e.g.
select to_char(count(*)) from emp
3. create another SQL Task after the SQL Task for "count(*)". and in the parameter tab set the datatype to varchar with variable same as ResultSetName from previous SQL Task.
hope this is clear enough.
Subscribe to:
Posts (Atom)