Changing SQL-command with a script

Hello

I want to change the sql-command that is behind a ole db datasource with a script
So that first the script is run and that the sql-command is changed

Somebody who has any idea?

You cannot access object or change their properties from Script in SSIS, a somewhat dramatic change from DTS.

First choice would be to use an Property Expression on the SqlCommand property, but unfortunately this property has not been exposed as an expression.

Next choice would be to use the "SQL Command from variable". You can use a script to set the variable, and the source can be set to use that variable, so job done. I woudl actually be tempted to set the SQL through an expression on the variable. Highlight teh variable in the Variables pane and select the properties grid. You can then set EvaluateAsExpression, followed by the Expression itself.|||Thats indeed a dramatic change

Can you do it then in Microsoft Visual Studio for Application
Thx|||

g4rc wrote:

Thats indeed a dramatic change

Can you do it then in Microsoft Visual Studio for Application
Thx

In the context of SSIS, a script task and VSA are the same thing. So the answer is "No".

You should use the approach outlined by Darren in the third paragraph of his earlier post. Its very easy - far easier than ActiveX scripting.

-Jamie|||can you give me a link to the post
I cannot find it|||

g4rc wrote:

can you give me a link to the post
I cannot find it

Its at the top of this page

i.e. The first reply on this discussion thread.

-Jamie

changing servers and ip affect sql dbs?

Hello:
I make an environmet server and all sql server applications ,
it works know fine, but I need to change the server of place to
production department and it will have another ip does this change affect all the information I backup in sql ?
Thanks.It shouldn't affect anything unless you are actually connecting to the server with the ip address. As long as it is assigned the same servername it should work fine.|||Originally posted by Memnoch1207
It shouldn't affect anything unless you are actually connecting to the server with the ip address. As long as it is assigned the same servername it should work fine.

tomorrow will be the big day..thanks and regards.Mirtha.

Changing of @@IDENTITY item in a trigger

Hello!

I have a instead of insert trigger that after doing some validation, inserts a record into a specific table. Another table requires the key generated by this insert in order to add a record, which is done at the end of the trigger. When called from a client (via a recordset.update call) the newly created key from the first insert is overridden with the key from the second insert. Any ideas on how I can save the first key, then pass it back to the client? The client currently receives the second key created.

Thanks in advance,

EverettI have exactly the same problem and although I don't have a solution (yet), I have found out that this appears to be by design. ADO does a "Select @.@.IDENTITY" after an insert to retrieve the key of the record inserted (See Chapter 11 of "Programming ADO" by David Sceppa at http://www.microsoft.com/mspress/books/sampchap/3445a.asp). If there was some way to modify ADO's behaviour to use instead "Select IDENT_CURRENT('<tablename>')" then problem solved. The article describes a "Update Resync" dynamic record set property but it doesn't appear to be flexible enough to tell ADO to use IDENT_CURRENT.

The hunt continues...

Changing Field/Column Name

Hello:
I'm working on a Crystal report for a client and, in order for it to work
without errors, I have to change the name of a field (column) in a table.
You see, the field name is two words called "[Print Count]". The space
between these two words, believe it or not, causes errors in my report.
So, I took out the space in this field so that the field name reads
"PrintCount". That fixed my report.
Well, unfortunately, doing this caused problems elsewhere as this name
change did not update (filter to) a SQL View that a user needs in order to
conduct his daily business activities. So, I had to go back and reverse wha
t
I did and add that space back in.
I really did not think that changing the name of a field would cause issues.
But, like I said, changing the name in this table did not update the View
that is based on this table and other tables.
How do I change this field name to be without this space and have this
change update everywhere else effectively in the database including in this
View? I tried to change it subsequently in the View but could not figure ou
t
how to do so.
Any insights would be appreciated!
Thanks!
childofthe1980s> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
Correct. This is by design.

> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in thi
s
> View?
There might exist some tools that keep control of all your objects and depen
dencies that can handle
this for you. There's nothing built into SQL Server or any of the tools that
comes with SQL Server.

> I tried to change it subsequently in the View but could not figure out
> how to do so.
ALTER VIEW. Or, of you did SELECT * (horror), you could have used sp_refresh
view.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in messa
ge
news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The spac
e
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse w
hat
> I did and add that space back in.
> I really did not think that changing the name of a field would cause issue
s.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in thi
s
> View? I tried to change it subsequently in the View but could not figure
out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s|||Is the view referencing the column name directly? If so, you'd need to
change it there too. If you're
doing a SELECT * on the table within the view, then run sp_refreshview to
update the view metadata.
For columns that have spaces, put brackets around them. ie select [pr
int
count] from mytable
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:D2FF25C0-D581-4027-BA45-E2D5CC40D597@.microsoft.com...
> Hello:
> I'm working on a Crystal report for a client and, in order for it to work
> without errors, I have to change the name of a field (column) in a table.
> You see, the field name is two words called "[Print Count]". The spac
e
> between these two words, believe it or not, causes errors in my report.
> So, I took out the space in this field so that the field name reads
> "PrintCount". That fixed my report.
> Well, unfortunately, doing this caused problems elsewhere as this name
> change did not update (filter to) a SQL View that a user needs in order to
> conduct his daily business activities. So, I had to go back and reverse
what
> I did and add that space back in.
> I really did not think that changing the name of a field would cause
issues.
> But, like I said, changing the name in this table did not update the View
> that is based on this table and other tables.
> How do I change this field name to be without this space and have this
> change update everywhere else effectively in the database including in
this
> View? I tried to change it subsequently in the View but could not figure
out
> how to do so.
> Any insights would be appreciated!
> Thanks!
> childofthe1980s