Easy way to update multiple databases

SQL 2K, Win 2K
My company is in the process of developing a new app that will have a lot of
client specific databases (30 to 50) with identical schemas and stored
procedures. My job as the DBA will be to make the schema and SP changes to
all of these when we have updates.
I have a similar app now that I have a script with several Use Database
commands and I just paste the changes in between the Use Database commands,
but I only have to do this for a few databases.
Does anybody know of a way to automate this with scripts or a tool to apply
the schema and SO change to all user databases on a server.
Thanks.
MikePerhaps DB Ghost would fill your needs.
www.dbghost.com
or
http://www.innovartis.co.uk/Home.aspx
-- Keith
"Mike" <Mike@.Comcast.net> wrote in message =news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> > My company is in the process of developing a new app that will have a =lot of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP =changes to
> all of these when we have updates.
> > I have a similar app now that I have a script with several Use =Database
> commands and I just paste the changes in between the Use Database =commands,
> but I only have to do this for a few databases.
> > Does anybody know of a way to automate this with scripts or a tool to =apply
> the schema and SO change to all user databases on a server.
> > Thanks.
> > Mike
> >|||Hi
If you are using a version control system to maintain your database code.
The most obvious way to upgrade would be use scripts called from osql/isql
or possibly DMO.
John
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||Check out if www.red-gate.com or www.dbghost.com does what you want.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||I wrote a little vb app that gets a list of db's to apply
changes to. you just paste the SQL syntax (ie "Alter
Table x add constraint y...") and click go, and it runs
the command on all dbs. I populate a simple table w/ the
names of db's to run the command against.
Also, I put together an app that uses a template database
(with the tables, sp's, triggers, that you want), and
pushes the schema out to a list of databases (in my case,
that list is in a table).
If you want the VB code for either, just let me know.
>--Original Message--
>SQL 2K, Win 2K
>My company is in the process of developing a new app that
will have a lot of
>client specific databases (30 to 50) with identical
schemas and stored
>procedures. My job as the DBA will be to make the schema
and SP changes to
>all of these when we have updates.
> I have a similar app now that I have a script with
several Use Database
>commands and I just paste the changes in between the Use
Database commands,
>but I only have to do this for a few databases.
>Does anybody know of a way to automate this with scripts
or a tool to apply
>the schema and SO change to all user databases on a
server.
>Thanks.
>Mike
>
>.
>|||I would appreciate it if you could share the app with me. It doesn't look
like DBGhost or SQL Compare will do it.
Thanks.
Mike
"Gene Daigle" <anonymous@.discussions.microsoft.com> wrote in message
news:024101c3bb73$7b7e3e10$a401280a@.phx.gbl...
> I wrote a little vb app that gets a list of db's to apply
> changes to. you just paste the SQL syntax (ie "Alter
> Table x add constraint y...") and click go, and it runs
> the command on all dbs. I populate a simple table w/ the
> names of db's to run the command against.
> Also, I put together an app that uses a template database
> (with the tables, sp's, triggers, that you want), and
> pushes the schema out to a list of databases (in my case,
> that list is in a table).
> If you want the VB code for either, just let me know.
>
> >--Original Message--
> >SQL 2K, Win 2K
> >
> >My company is in the process of developing a new app that
> will have a lot of
> >client specific databases (30 to 50) with identical
> schemas and stored
> >procedures. My job as the DBA will be to make the schema
> and SP changes to
> >all of these when we have updates.
> >
> > I have a similar app now that I have a script with
> several Use Database
> >commands and I just paste the changes in between the Use
> Database commands,
> >but I only have to do this for a few databases.
> >
> >Does anybody know of a way to automate this with scripts
> or a tool to apply
> >the schema and SO change to all user databases on a
> server.
> >
> >Thanks.
> >
> >Mike
> >
> >
> >.
> >

Easy Update

can someone please enlighten me. why doesn't this work? It is supposed
to remove any periods in the TSUSA_Code field.
UPDATE tblLookupParts
SET TSUSA_Code = REPLACE(TSUSA_Code, '.', '')
thanks
-doodle
I am getting: Undefined function 'Replace' in expression.
|||Is it because you have a case sensitive collation?
You can find this using Select SERVERPROPERTY('Collation')...
doodle wrote:
> can someone please enlighten me. why doesn't this work? It is supposed
> to remove any periods in the TSUSA_Code field.
> UPDATE tblLookupParts
> SET TSUSA_Code = REPLACE(TSUSA_Code, '.', '')
>
> thanks
> -doodle
|||that returns an error also: unable to parse query text
|||You are running Microsoft SQL Server, are you not? What version and
service pack level?
Roy
On 20 Oct 2006 15:07:49 -0700, "doodle" <ADraughn@.mazakcorp.com>
wrote:

>can someone please enlighten me. why doesn't this work? It is supposed
>to remove any periods in the TSUSA_Code field.
>UPDATE tblLookupParts
>SET TSUSA_Code = REPLACE(TSUSA_Code, '.', '')
>
>thanks
>-doodle
|||I have SQL Server 2005, using Visual Studio 2005 to run my SQL queries
against Access DB tables.
-doodle
|||On 21 Oct 2006 05:44:05 -0700, "doodle" <ADraughn@.mazakcorp.com>
wrote:

>I have SQL Server 2005, using Visual Studio 2005 to run my SQL queries
>against Access DB tables.
>-doodle
That explains it. You need to use Access's dialect of SQL. Look for
help in an Access newsgroup.
http://groups.google.com/groups/dir?...06877&expand=1
Roy Harvey
Beacon Falls, CT

Easy Update

can someone please enlighten me. why doesn't this work? It is supposed
to remove any periods in the TSUSA_Code field.
UPDATE tblLookupParts
SET TSUSA_Code = REPLACE(TSUSA_Code, '.', '')
thanks
-doodleI am getting: Undefined function 'Replace' in expression.|||Is it because you have a case sensitive collation?
You can find this using Select SERVERPROPERTY('Collation')...
doodle wrote:
> can someone please enlighten me. why doesn't this work? It is supposed
> to remove any periods in the TSUSA_Code field.
> UPDATE tblLookupParts
> SET TSUSA_Code = REPLACE(TSUSA_Code, '.', '')
>
> thanks
> -doodle|||that returns an error also: unable to parse query text|||You are running Microsoft SQL Server, are you not? What version and
service pack level?
Roy
On 20 Oct 2006 15:07:49 -0700, "doodle" <ADraughn@.mazakcorp.com>
wrote:

>can someone please enlighten me. why doesn't this work? It is supposed
>to remove any periods in the TSUSA_Code field.
>UPDATE tblLookupParts
>SET TSUSA_Code = REPLACE(TSUSA_Code, '.', '')
>
>thanks
>-doodle|||I have SQL Server 2005, using Visual Studio 2005 to run my SQL queries
against Access DB tables.
-doodle|||On 21 Oct 2006 05:44:05 -0700, "doodle" <ADraughn@.mazakcorp.com>
wrote:

>I have SQL Server 2005, using Visual Studio 2005 to run my SQL queries
>against Access DB tables.
>-doodle
That explains it. You need to use Access's dialect of SQL. Look for
help in an Access newsgroup.
http://groups.google.com/groups/dir...606877&expand=1
Roy Harvey
Beacon Falls, CT

Easy Update

can someone please enlighten me. why doesn't this work? It is supposed
to remove any periods in the TSUSA_Code field.
UPDATE tblLookupParts
SET TSUSA_Code = REPLACE(TSUSA_Code, '.', '')
thanks
-doodleI am getting: Undefined function 'Replace' in expression.|||Is it because you have a case sensitive collation?
You can find this using Select SERVERPROPERTY('Collation')...
doodle wrote:
> can someone please enlighten me. why doesn't this work? It is supposed
> to remove any periods in the TSUSA_Code field.
> UPDATE tblLookupParts
> SET TSUSA_Code = REPLACE(TSUSA_Code, '.', '')
>
> thanks
> -doodle|||that returns an error also: unable to parse query text|||You are running Microsoft SQL Server, are you not? What version and
service pack level?
Roy
On 20 Oct 2006 15:07:49 -0700, "doodle" <ADraughn@.mazakcorp.com>
wrote:
>can someone please enlighten me. why doesn't this work? It is supposed
>to remove any periods in the TSUSA_Code field.
>UPDATE tblLookupParts
>SET TSUSA_Code = REPLACE(TSUSA_Code, '.', '')
>
>thanks
>-doodle|||I have SQL Server 2005, using Visual Studio 2005 to run my SQL queries
against Access DB tables.
-doodle|||On 21 Oct 2006 05:44:05 -0700, "doodle" <ADraughn@.mazakcorp.com>
wrote:
>I have SQL Server 2005, using Visual Studio 2005 to run my SQL queries
>against Access DB tables.
>-doodle
That explains it. You need to use Access's dialect of SQL. Look for
help in an Access newsgroup.
http://groups.google.com/groups/dir?&sel=33606877&expand=1
Roy Harvey
Beacon Falls, CTsql

Easy Transact SQL Question

I'm a novice with SQL Querying, and can't figure out an update command.
My SELECT statement is as follows:
SELECT * FROM table1, table2
WHERE table1.keyfield = table2.keyfield and table2.field is not null
How do I convert this to an update statement on a field in table1 while
maintaining the restriction based on the field in table2?
Thanks for the help!Cindy Mikeworth wrote:
> I'm a novice with SQL Querying, and can't figure out an update command.
> My SELECT statement is as follows:
> SELECT * FROM table1, table2
> WHERE table1.keyfield = table2.keyfield and table2.field is not null
> How do I convert this to an update statement on a field in table1 while
> maintaining the restriction based on the field in table2?
> Thanks for the help!
For example:
UPDATE table1
SET col1 = 1234
WHERE EXISTS
(SELECT *
FROM table2
WHERE table2.keycol = table1.keycol
AND table2.col IS NOT NULL) ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||First learn that rows are NOT anythign like records, nor are columns
anything like a field. it is VITAL to have the right mindset in SQL
SELECT *
FROM Table1, Table2
WHERE table1.keyfield = table2.keyfield
AND table2.field IS NOT NULL;
You don't do it at all!! One of the MANY differences between a field
and column is that a column can have constraints on it. An SQL
programmer woudl have done this in the DDL (do you know what DDL is? If
not, you are sooooo screwed).
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. Could you program from what you posted? HOW?!
If you have a key, in ANY table is BY DEFINITION NOT NULL, so your|||Cindy Mikeworth (CindyMikeworth@.newsgroups.nospam) writes:
> I'm a novice with SQL Querying, and can't figure out an update command.
> My SELECT statement is as follows:
> SELECT * FROM table1, table2
> WHERE table1.keyfield = table2.keyfield and table2.field is not null
> How do I convert this to an update statement on a field in table1 while
> maintaining the restriction based on the field in table2?
UPDATE table1
SET field = ...
FROM table1, table2
WHERE table1.keyfield = table2.keyfield
and table2.field is not null
This uses a non-standard extension of the UPDATE statement that is
proprietary to SQL Server and Sybase. As long as one is careful that
the join produces a unique value for the row to update, this is a very
practical method, not the least because it's so easy to transform a
SELECT into an UPDATE.
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|||UPDATE t1
SET t1.column1 = t2.value
FROM table1 t1
INNER JOIN table2 t2
ON t1.keyfield = t2.keyfield
WHERE t2.field IS NOT NULL

easy table based update statement??

Hello,

I have 2 ways of updating data I'm using often

1) via a cursor on TABLE1 update fields in TABLE2
2) via an some of variables ...

SELECT @.var1=FLD1, @.var2=FLD2 FROM TABLE1 WHERE FLD-ID = @.inputVAR
UPDATE TABLE2
SET FLDx = @.var1, FLDy = @.var2
WHERE ...

Now I have a system with 2 databases and I need to update table DB2.TAB
based on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,
I thought it would be much easier to get the required data immediately from
DB1.TAB in the update-statement of DB2.TAB ... but the way to do that
confuses me. I've checked books online and a lot of newsgrouppostings
giving good information but still I keep getting errors like this ...

The column prefix 'x.ADS' does not match with a table name or alias name
used in the query.

while executing the following statement ...

UPDATE DB2.dbo.TAB
SET
FLD1 = x.FLD1,
FLD2 = x.FLD2,
...
FROM DB1.dbo.TAB x, DB2.dbo.ADS
WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
@.InputParameter

So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
table DB1.TAB
AND I only want to update the row in DB2.TAB with the unique keyfield
IDOFTAB2 equal to variable @.InputParameter

Do you see what I'm doing wrong?

--
Thank you,
Kind regards,
Perre Van Wilrijk,
Remove capitals to get my real email address,Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> The column prefix 'x.ADS' does not match with a table name or alias name
> used in the query.
> while executing the following statement ...
> UPDATE DB2.dbo.TAB
> SET
> FLD1 = x.FLD1,
> FLD2 = x.FLD2,
> ...
> FROM DB1.dbo.TAB x, DB2.dbo.ADS
> WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
> @.InputParameter
> So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
> table DB1.TAB
> AND I only want to update the row in DB2.TAB with the unique keyfield
> IDOFTAB2 equal to variable @.InputParameter

The string x.ADS is not in the part of the query you posted. Maybe you
should post the complete query?

But what is really suspect is thaht DB.dbo.ADS is in the FROM lcause,
but not in the WHERE clause. That could cause some unexpectedly bad
performance, as you get a cartesian join.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply Mr Erland Sommarskog.

I changed the names of the fields and tables in order to eliminate dutch
databasenames and in order to avoid confusion about the tablename ADS which
appears in both databases, but which isn't an exact copy. DBIngeven is made
only to insert new rows. DBLezen is made only to read data earlier entered
in DBIngeven and other databases. So I'm sorry I made the statement unclear
by replacing tablename ADS by tablename TAB, forgetting to do it
everywhere.in the statement.

This is actually the statement I wrote to synchronize the data, the
statement causing the error "The column prefix 'x.ADS' does not match with a
table name or alias name used in the query.". Might the problem being
caused by the fact that both tables, being in different databases, have the
same name?

CREATE STORED PROCEDURE USP_SYNC
@.ADIDLezen int
AS
DECLARE @.ER int

UPDATE DBLezen.dbo.ADS
SET
ADS_HR = x.ADS_HR,
ADS_OR = x.ADS_OR,
ADS_VA = x.ADS_VA,
ADS_PH = x.ADS_PH,
ADS_GB = x.ADS_GB,
ADS_TELEPHONE = x.ADS_TELEPHONE,
ADS_GSM = x.ADS_GSM,
ADS_PHOTO = x.ADS_PHOTO,
ADS_USRID= x.ADS_USRID,
ADS_PRICE = x.ADS_PRICE,
ADS_PRICETYPE = x.ADS_PRICETYPE,
ADS_PRICEINDICATION = x.ADS_PRICEINDICATION,
ADS_REGION = x.ADS_REGION,
ADS_KGITEMID = x.ADS_KGITEMID,
ADS_PRODUCTTYPE = x.ADS_PRODUCTTYPE,
ADS_PRODUCTTYPE_WEB = x.ADS_PRODUCTTYPE_WEB,
ADS_FIL = x.ADS_FIL,
ADS_EOONLINE = x.ADS_ONLINE_END,
ADS_CHUS = 'ITOLUPD',
ADS_CHDT = getdate()
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
@.ADIDLezen

SET @.ER = @.@.ERROR

Thanks,
Perre.

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95B3F1B592928Yazorman@.127.0.0.1...
> Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> > The column prefix 'x.ADS' does not match with a table name or alias name
> > used in the query.
> > while executing the following statement ...
> > UPDATE DB2.dbo.TAB
> > SET
> > FLD1 = x.FLD1,
> > FLD2 = x.FLD2,
> > ...
> > FROM DB1.dbo.TAB x, DB2.dbo.ADS
> > WHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =
> > @.InputParameter
> > So in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 of
> > table DB1.TAB
> > AND I only want to update the row in DB2.TAB with the unique keyfield
> > IDOFTAB2 equal to variable @.InputParameter
> The string x.ADS is not in the part of the query you posted. Maybe you
> should post the complete query?
> But what is really suspect is thaht DB.dbo.ADS is in the FROM lcause,
> but not in the WHERE clause. That could cause some unexpectedly bad
> performance, as you get a cartesian join.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 3 Dec 2004 14:30:43 +0100, Perre Van Wilrijk wrote:

> WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
>@.ADIDLezen

Hi Perre,

Change x.ADS.ADID to x.ADID (or x.ADS_ADID - I can only guess here).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> I changed the names of the fields and tables in order to eliminate dutch
> databasenames and in order to avoid confusion about the tablename ADS
> which appears in both databases, but which isn't an exact copy.

Instead you caused confusion. (And Dutch is not a problem to understand,
at least as it's single. Double-dutch may be more difficult...)

> This is actually the statement I wrote to synchronize the data, the
> statement causing the error "The column prefix 'x.ADS' does not match
> with a table name or alias name used in the query.". Might the problem
> being caused by the fact that both tables, being in different databases,
> have the same name?

No, but because you the alias in the wrong place:

> FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
> WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID =
> @.ADIDLezen

x.ADS.ADID would refer to a table ADS owned by the user x.

A tip is to always use aliases. They usually make queries less verbose,
not the least when you use three-part names.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
Thanks Hugo,

Indead I wrote x.ADS.ADID instead of x.ADS_ADID. Unfortenatly the error
messages given back by SQL Server wasn't clear enough for me to locate the
synthax error I made. Great you saw it without knowing my table and field
names. Sorry to have bothered you with such a stupid mistake.

I also made an alias for DBLezen making the WHERE-clause better readable and
maintainable.
A little bit strange now I can't use the y alias in the SET clause (eg SET
y.ADS_HR = x.ADS_HR, ...), I guess that's because the first field name
refers to the table name after the UPDATE-word, which doesn't seem to be
aliasable. So this on works fine ...

UPDATE DBLezen.dbo.ADS
SET
ADS_HR = x.ADS_HR,
ADS_OR = x.ADS_OR,
ADS_VA = x.ADS_VA,
ADS_PH = x.ADS_PH,
ADS_GB = x.ADS_GB,
ADS_TELEPHONE = x.ADS_TELEPHONE,
ADS_GSM = x.ADS_GSM,
ADS_PHOTO = x.ADS_PHOTO,
ADS_USRID= x.ADS_USRID,
ADS_PRICE = x.ADS_PRICE,
ADS_PRICETYPE = x.ADS_PRICETYPE,
ADS_PRICEINDICATION = x.ADS_PRICEINDICATION,
ADS_REGION = x.ADS_REGION,
ADS_KGITEMID = x.ADS_KGITEMID,
ADS_PRODUCTTYPE = x.ADS_PRODUCTTYPE,
ADS_PRODUCTTYPE_WEB = x.ADS_PRODUCTTYPE_WEB,
ADS_FIL = x.ADS_FIL,
ADS_EOONLINE = x.ADS_ONLINE_END,
ADS_CHUS = 'ITOLUPD',
ADS_CHDT = getdate()
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS y
WHERE y.ADS_OVID = x.ADS_ADID AND y.ADS_ADID = @.ADIDLezen

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95B4AD8233CE9Yazorman@.127.0.0.1...
> Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> > I changed the names of the fields and tables in order to eliminate dutch
> > databasenames and in order to avoid confusion about the tablename ADS
> > which appears in both databases, but which isn't an exact copy.
> Instead you caused confusion. (And Dutch is not a problem to understand,
> at least as it's single. Double-dutch may be more difficult...)
> > This is actually the statement I wrote to synchronize the data, the
> > statement causing the error "The column prefix 'x.ADS' does not match
> > with a table name or alias name used in the query.". Might the problem
> > being caused by the fact that both tables, being in different databases,
> > have the same name?
> No, but because you the alias in the wrong place:
> > FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS
> > WHERE DBLezen.dbo.ADS.ADS_OVID = x.ADS.ADID AND DBLezen.dbo.ADS.ADID
=
> > @.ADIDLezen
> x.ADS.ADID would refer to a table ADS owned by the user x.
> A tip is to always use aliases. They usually make queries less verbose,
> not the least when you use three-part names.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 7 Dec 2004 13:55:55 +0100, Perre Van Wilrijk wrote:

(snip)
>I also made an alias for DBLezen making the WHERE-clause better readable and
>maintainable.
>A little bit strange now I can't use the y alias in the SET clause (eg SET
>y.ADS_HR = x.ADS_HR, ...), I guess that's because the first field name
>refers to the table name after the UPDATE-word, which doesn't seem to be
>aliasable. So this on works fine ...
> UPDATE DBLezen.dbo.ADS
> SET
> ADS_HR = x.ADS_HR,
(snip)

This should work as well:

UPDATE y
SET
ADS_HR = x.ADS_HR,
(...)
FROM DBIngeven.dbo.ADS x, DBLezen.dbo.ADS y
WHERE y.ADS_OVID = x.ADS_ADID AND y.ADS_ADID = @.ADIDLezen

Personally, I prefer to always use the UPDATE .. FROM syntax this way, so
I won't forget to use it if a self-join is included (than it becomes
mandatory to use the alias in the UPDATE clause).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perre Van Wilrijk (prSPAM@.AkoopjeskrantWAY.be) writes:
> Indead I wrote x.ADS.ADID instead of x.ADS_ADID. Unfortenatly the error
> messages given back by SQL Server wasn't clear enough for me to locate the
> synthax error I made.

It's kind of difficult for SQL Server to second-guess what you really meant.

But it is true that the error messages from SQL Server are not always
crystal clear. When it comes to true parsing errors, the obscureness of
the messages partly comes from the too rich syntax of T-SQL. A typo can
lead to some legal syntax SQL that you are not aware of, but then lead
a syntax error further ahead.

For the error message you got, I guess the main problem is that the
message points to the first line in the UPDATE statement, instead of
the line where the error is. That makes it more difficult to spot the
error.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> It's kind of difficult for SQL Server to second-guess what you really
meant.
> But it is true that the error messages from SQL Server are not always
> crystal clear. When it comes to true parsing errors, the obscureness
of
> the messages partly comes from the too rich syntax of T-SQL. A typo
can
> lead to some legal syntax SQL that you are not aware of, but then
lead
> a syntax error further ahead.

It might not be easy to parse especially my code :-)
I can imagine that.
I once believed I could give people using my programs always clear
messages, for each kind of problem. We all have to learn.

Easy question on UPDATE

I have one field organization_operating_name that is on two tables vendor and
vendor_loc

I want to update the vendor name to the vendor_loc name

I tried this but get errors...

update vendor_loc
set organization_operating_name = vendor.organization_operating_name
where organization_operating_name like 'DO NOT%'

Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'vendor' does not match with a table name or alias name
used in the query.

vendor is a valid table name...so I must be missing something.

jeff

--
Message posted via http://www.sqlmonster.comSee Example C under UPDATE in Books Online, and also "Changing Data
Using the FROM Clause".

Simon|||thanks Simon,

the lightbulb went off...

Simon Hayes wrote:
>See Example C under UPDATE in Books Online, and also "Changing Data
>Using the FROM Clause".
>Simon

--
Message posted via http://www.sqlmonster.com|||UPDATE Vendor_Loc
SET organization_operating_name
= (SELECT organization_operating_name
FROM Vendors
WHERE organization_operating_name LIKE 'DO NOT%');

You would never use the proprietary UPDATE .. FROM syntax because the
results are unpredictable. It will fail to discover cardinality
violations, does not port, and depends on the physical arrangment of
the data. .

Easiest way to update lots of records

I have a database where several thousand records have NULL in a binary field. I want to change all the NULLs to false. I have Visual Studio 5, and the database is a SQL Server 5 database on a remote server. What is the easiest way to do this? Is there a query I can run that will set all ReNew to false where ReNew is Null? This is a live database so I want to get it right. I can't afford to mess it up.

Diane

You can do something like the following:

UPDATE [Table] SET ReNew=0 WHERE ReNew IS NULL

Before I run an update or delete statement, I always run it as a select first, just to make sure I'm only updating the rows I want. Something like this:

SELECT * FROM [Table] WHERE ReNew IS NULL

|||

Benners_J:

Before I run an update or delete statement, I always run it as a select first, just to make sure I'm only updating the rows I want. Something like this:

SELECT * FROM [Table] WHERE ReNew IS NULL

Rather, SELECT COUNT(*) FROM [Table] WHERE ReNew IS NULL would be good, since the poster is saying there are too many rows. Anything which makes you feel comfortable.

Dynamically select column

Hey all. I'm trying to create a stored proc that will update a variable column, depending on the parameter I pass it. Here's the stored proc:

CREATE PROCEDURE VoteStoredProc
(
 @.PlayerID int,
 @.VoteID int,
 @.BootNumber nvarchar(50)
)
AS
DECLARE @.SQLStatement varchar(255)
SET @.SQLStatement = 'UPDATE myTable SET '+ @.BootNumber+'='+ @.VoteID + ' WHERE (PlayerID = '+ @.PlayerID +')'
EXEC(@.SQLStatement)
GO

I get the following error:

Syntax error converting the nvarchar value 'UPDATE myTable SET Boot3=' to a column of data type int

The update statement is good, because if I use the stored proc below (hard-coded the column), it works fine.

CREATE PROCEDURE VoteStoredProc
(
 @.PlayerID int,
 @.VoteID int,
 @.BootNumber nvarchar(50)
)
AS
UPDATE
 myTable
SET
 Boot3 = @.VoteID
WHERE
 PlayerID = @.PlayerID
GO

Is there a way to dynamically choose a column/field to select from? Or is my syntax incorrect..?
Thanks!Try this:

CREATE PROCEDURE VoteStoredProc
(
 @.PlayerID int,
 @.VoteID int,
 @.BootNumber varchar(50)
)
AS
DECLARE @.SQLStatement varchar(255)
SET @.SQLStatement = 'UPDATE myTable SET '+ @.BootNumber+ ' = ' + CAST(@.VoteID as VARCHAR(10)) + ' WHERE (PlayerID = '+ CAST(@.PlayerID as VARCHAR(10)) +')'
EXEC(@.SQLStatement)
GO

Casting the integers to varchars so they can be concatenated into the larger string.

Hope this helps,
John|||John:

Brilliant! Thanks; works beautifully.

JP

Dynamically generate SqlDataSource

Hi, how I do create SqlDataSource in thecode-behind,setting Select SQL along with its parameters and, finallyautomically generating Del, Update, Insert SQL.

Is there a way to achieve this goal as it would in VS designer?

Thanks,

Ricky.

It is possible to create the TSQL code and wrapper code automatically by a variety of means. I use TSQL scripts to do the generation from within Query Analyser.

Which version of SQL Server are you targetting?

|||

Hi, I use SQL SERVER 2005

Thanks,

Ricky.

Dynamically add update parameter to formview

I have a formview with name, email, and password. I bind all fields to sql except the password which is blank.

In my sqldatasource, I define parameters for name, email and id:

UpdateCommand

="UPDATE UserProfile SET Name = @.Name,Email = @.Email WHERE (ID = @.ID)">
<UpdateParameters>
<asp:ParameterName="Name"/>
<asp:ParameterName="Email"/>
<asp:ParameterName="ID"/>
</UpdateParameters>

In code I want to add a password parameter if there is value in the password field otherwise I don't want the password field updated. If I add define a password parameter like above then if a user left the password field blank then their new is blank. That's way I think adding it dynamically is the way. But I am having problems with the code to add the parameter in sqldatasource_updating event.

Protected

Sub SqlProfile_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.Updating
Dim passwordAs TextBox = FormView1.FindControl

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not password.Text.ToString &"" =""ThenSqlProfile.UpdateParameters.Add(New Parameter("@.Password", TypeCode.String, password.Text.ToString))End IfEnd Sub

ThanksYou're close:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Thene.Command.Parameters.Add(password.Text)End IfEnd Sub

|||

I think you should add the parameter manually, and check for a null / blank parameter in the sql statement. That way you just pass what ever you have in your form (blank password or populated password) and let the SQL statement figure it out for you. If not, then you have do add a new parameter to the updateparameters AND modify your UpdateCommand to have the additional line.

need help with the SQL?

|||

ecbruck:

You're close:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Thene.Command.Parameters.Add(password.Text)End IfEnd Sub

if he does it that way, he will need to modify his command as well... adding "Password = @.Something"

|||

pixelsyndicate:

I think you should add the parameter manually, and check for a null / blank parameter in the sql statement.

I agree. I would personally let me Stored Procedure handle the case when the Password parameter was passed in as null.

|||

Thanks for the help.

This is what I have so far but still doesn't work.

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then SqlProfile.UpdateParameters.Add("password", password.Text) SqlProfile.UpdateCommand ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

|||

Thanks for the help.

This is what I have so far but still doesn't work.

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then SqlProfile.UpdateParameters.Add("password", password.Text) SqlProfile.UpdateCommand ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

 It updates the name field with no errors but the password doesn't get updated.

|||You need to be modifying the members of the SqlDataSourceCommandEventArgs class rather than the SqlDataSource class as I did in my previous example.|||

When I did your example:

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then e.Command.Parameters.Add(password.Text) e.Command.CommandText ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

I get this error:

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidCastException: The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Source Error:

Line 31: Dim password As TextBox = FormView1.FindControl("tb_password1")Line 32: If Not String.IsNullOrEmpty(password.Text) ThenLine 33: e.Command.Parameters.Add(password.Text)Line 34: e.Command.CommandText = "UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)"Line 35: End If

|||

Thanks for all the help. This finally work with this code:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Then Dim pAs SqlParameter =New SqlParameter("@.Password", SqlDbType.NVarChar) p.Value = password.Text e.Command.Parameters.Add(p) e.Command.CommandText ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)"End If End Sub

Dynamic Update with a sub select

Hi I need some help writing a dynamic Update with a sub select.

I am trying to execute this query and retrieve a variable.The update and select work separately but when I put them together I get the following error,

incorrect syntax near’= ‘

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.SQL = 'UPDATEdbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set@.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID ='

Set@.SQL = @.SQL +' (Select @.XWorkingSheduleID = (Max (WorkingSheduleID)'

Set@.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set@.SQL = @.SQL +'@.XDeliveryDate'

Set@.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT,@.DeliveryDate

Any Help appreciated

Mr Tumnus wrote:

Hi I need some help writing a dynamic Update with a sub select.

I am trying to execute this query and retrieve a variable. The update and select work separately but when I put them together I get the following error,

incorrect syntax near’= ‘

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.SQL = 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID ='

Set @.SQL = @.SQL +' (Select @.XWorkingSheduleID = (Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT, @.DeliveryDate

Any Help appreciated

I think that the indicated (red) bracket is wrong as this is bracketing the SELECT away from (at a different level to) the other parts of the query (FROM, WHERE). I am less certain about which corresponding bracket to remove but I think it is the indicated one (blue).

|||

No change, I still get the same error

I have tried to use @.@.Identity to retrieve the variable, but I keep getting the identity of a query I run earlier in the SP (don’t sure I am using @.@.Identity properly).

I am fairly new to SQL and would appreciate any advice.

|||

Have you tried capturing the value of @.SQL and running that interactively with correct surrouding code (the DECLAREs, SETs, and a SELECT to inspect the final value). If you can find a version that works like that then you should only need to build it.

Another option is to split the operation into a batch of 2 steps like:

Code Snippet

SET @.SQL = 'SELECT @.XWorkingSheduleID = Max (WorkingSheduleID)'
SET @.SQL = @.SQL +' From dbo.['+ @.TableName +']'
SET @.SQL = @.SQL + ' Where (DeliveryDate = CAST(@.XDeliveryDate'
SET @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0); '
SET @.SQL = @.SQL + 'UPDATE dbo.['+ @.TableName +'] SET UID = '
SET @.SQL = @.SQL + '@.XCustomerID, SlotClosed = 1'
SET @.SQL = @.SQL +' WHERE (WorkingSheduleID = @.XWorkingSheduleID)'

For your @.SQL setting code.

|||Hi,

try this:

Code Snippet

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.TABLENAME VARCHAR(100)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

SET @.TableName = 'SomeTable'

SET @.SQL = 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID = '

Set @.SQL = @.SQL +' (Select Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0)) )'

PRINT @.SQL

UPDATE dbo.[SomeTable]

SET

UID =@.XCustomerID ,

SlotClosed=1

where WorkingSheduleID =

(

Select Max (WorkingSheduleID) From dbo.[SomeTable]

Where DeliveryDate =(CAST(@.XDeliveryDate AS datetime)) And (SlotClosed=0))

)

Don′t know why you did the thing with the @.XcustomerId in the brackets, but you wither leave that out or put it somewhere in there where-clause instead.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Try to change the query as follows :

DECLARE @.SQL NVARCHAR(4000)

DECLARE @.ParameterList NVARCHAR(4000)

Declare @.WorkingSheduleID Bigint

SET @.ParameterList = ' @.XCustomerID bigint, @.XWorkingSheduleID bigint OUTPUT, @.XDeliveryDate smallDatetime'

Set @.SQL = 'Select @.XWorkingSheduleID = Max (WorkingSheduleID)'

Set @.SQL = @.SQL +' From dbo.['+ @.TableName +'] Where DeliveryDate =(CAST('

Set @.SQL = @.SQL +'@.XDeliveryDate'

Set @.SQL = @.SQL +' AS datetime)) And (SlotClosed=0);'

set @.SQL = @.SQL + 'UPDATE dbo.['+ @.TableName +'] SET UID ='

Set @.SQL = @.SQL + '@.XCustomerID'

Set @.SQL = @.SQL +' ,SlotClosed=1 where WorkingSheduleID = @.XWorkingSheduleID '

EXEC sp_executesql @.SQL, @.ParameterList,@.CustomerID ,@.WorkingSheduleID OUTPUT, @.DeliveryDate

SELECT @.XWorkingSheduleID

Dynamic Update System

Hello folks!

I'm have some trouble with a dynamic update system! What I want to do:

1. I want to send in the code behind from any .aspx file values and parameters to a .vb class

2. Code (code behind in .aspx file):

1 gridupdate.updategrid("tblFeedbackA", e.CommandArgument,"locked=@.locked","@.locked","int","0") 'Call de .vb class
2 getgridfeedback()

3. Code (code in .vb class)

1Public Shared Function updategrid(ByVal tblnaamAs String,ByVal xAs Integer,ByVal sqlAs String,ByVal parametersAs String,ByVal sqltypeAs String,ByVal waardenAs String 'translation: values)2Dim dynstrAs String()3Dim dynstr2As String()4Dim reAs New Regex(",")5 dynstr = re.Split(parameters)6Dim iAs Integer7 dynstr2 = re.Split(sqltype)8Dim dynstr3As String()9 dynstr3 = re.Split(waarden)1011Dim sqlconnAs New SqlConnection(ConfigurationManager.ConnectionStrings("DataBase").ConnectionString)12Dim sql2As String ="UPDATE " & HttpContext.Current.Session("prefix").ToString & tblnaam &" set " & sql &" where id=" & x13Dim sqlcmdAs New SqlCommand(sql2, sqlconn)14 sqlconn.Open()1516For i = 0To dynstr.Length - 117 sqlcmd.Parameters.Add(dynstr(i),CType("sqldbtype." & dynstr2(i), System.Data.SqlDbType))18 sqlcmd.Parameters(dynstr(i)).Value = dynstr3(i)19Next20 sqlcmd.ExecuteNonQuery()21 sqlconn.Close()22Return Nothing23 End Function

All I want to do is to add the sqlparameters dynamically, but I don't find a way to do this :).

Can you help me out?
Thanks!

Hi Bert,

You are real close. You are trying to parse in a command string dynamically which usually wont work. A better way to do it, is to add paramaters to the sqlcommand method.

Rather than trying to parse a string together, do something like

sql2 = "UPDATE @.Parm1 SET @.Parm2 WHERE ID=@.Parm3"

sqlCmd.Paramaters.Addwithvalue("@.Parm1", HttpContext.Current.Session("prefix").ToString & tblnaam)

sqlCmd.Parmaters.addwithvalue("@.Parm2", sql)

sqlCmd.Parmaters.addwithvalue("@.parm3", x)

Sqlcmd.ExecuteNonQuery()

etc...

Give that a shot, see if it works for what you are trying to do.

Dynamic update of a column

Hi,
In the code below, I am trying to update a column to '0'. But, I dont know
which column to update until my variable @. Month fetches the column name.
DECLARE @.Month Varchar(20)
SELECT @.Month = MD.sFieldName FROM MonthDefinition MD, DTSScheduler DTS
WHERE MD.lMonthId = DTS.JobMonth
and DTS.JobId = 2
PRINT @.Month
update FTEActualForecast set @.Month = 0
where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
and FTEActualForecast.lChartId in (select FAF.lChartId from
FTEActualForecast FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
Now, the problem is
1. The column I am trying to update to '0'is determined dynamically, so I
have used a variable name called @.Month
2. But in the UPDATE statement when I use the keyword SET it sets the
variable and not the column value to ‘zero’
Is there some workaround or better way of doing this ?
Please advise.
Cheers,
Hemil.Try
Exec ('update FTEActualForecast set ' + @.Month + '= 0
where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
and FTEActualForecast.lChartId in (select FAF.lChartId from
FTEActualForecast FAF, FTEPayrollSource FPS
where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)')
"Hemil" wrote:

> Hi,
> In the code below, I am trying to update a column to '0'. But, I dont kno
w
> which column to update until my variable @. Month fetches the column name.
> DECLARE @.Month Varchar(20)
> SELECT @.Month = MD.sFieldName FROM MonthDefinition MD, DTSScheduler DTS
> WHERE MD.lMonthId = DTS.JobMonth
> and DTS.JobId = 2
> PRINT @.Month
> update FTEActualForecast set @.Month = 0
> where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
> FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> and FTEActualForecast.lChartId in (select FAF.lChartId from
> FTEActualForecast FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> Now, the problem is
> 1. The column I am trying to update to '0'is determined dynamically, so I
> have used a variable name called @.Month
> 2. But in the UPDATE statement when I use the keyword SET it sets the
> variable and not the column value to ‘zero’
>
> Is there some workaround or better way of doing this ?
> Please advise.
> Cheers,
> Hemil.
>|||Thanks a lot, Tom.
It does the job.
Thanks again,
Hemil.
"Tom" wrote:
> Try
> Exec ('update FTEActualForecast set ' + @.Month + '= 0
> where FTEActualForecast.lRCId in (select FAF.lRCId from FTEActualForecast
> FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)
> and FTEActualForecast.lChartId in (select FAF.lChartId from
> FTEActualForecast FAF, FTEPayrollSource FPS
> where FAF.lRCId = FPS.RCCode and FAF.lChartId = FPS.ChartCode)')
>
> "Hemil" wrote:
>|||Please include DDL with questions like this so that we don't have to guess
what your table looks like.
Why separate columns for each month? Instead, add a DATETIME column to
record the month then you can specify which row to update with a WHERE
clause.
David Portas
SQL Server MVP
--

dynamic update

i have a table with the following values

iden nam status
-- -- --
1 pp NULL
1 kk NULL
2 rr NULL
2 nn NULL
2 jj NULL
3 hh NULL

now i want to update the status cloumn in this table in such a way that the status colum = 'Status is' + iden + nam for all distinct values of iden from the table

how can we do this without using a cursor?

Here it is,

Code Snippet

Create Table #data (

[iden] int ,

[nam] Varchar(100) ,

[status] Varchar(100)

);

Insert Into #data Values('1','pp',NULL);

Insert Into #data Values('1','kk',NULL);

Insert Into #data Values('2','rr',NULL);

Insert Into #data Values('2','nn',NULL);

Insert Into #data Values('2','jj',NULL);

Insert Into #data Values('3','hh',NULL);

Update #data

Set

[status] = 'Status is ' + Cast(iden as varchar) + ' ' +nam

Select * from #data

|||

The most important question is why would you want to do that? It is both unnecessary, and not a good design consideration to store data that is easily 'computed' from existing row data.

Use a VIEW instead.

CREATE VIEW dbo.vMyTableView

AS

SELECT

Iden,

Nam,

[Status] = 'Status is ' + Cast( Iden as varchar(10) ) + Nam

FROM dbo.MyTable

GO

Dynamic Sql to update a table row

Hi All MSSQL Experts-

I am facing a typical problem in one of my store procedure.

I am trying to update a table thru a dynamically generated SQL statement.

In my stored procedure I do all kinds of looping and manipultion to get this statement and execute like this:

mSQL = 'UPDATE TblSomeTable SET FldSomeFile=200 WHERE FldPK=100'
EXEC (mSQL)

This stuff has always worked for me, for Temporary tables, but some how does not seem to work on physical table. Am I doing something wrong? Missing something?

Any help will be greatly appreciated.

Best Wishes,Hi All-

Please never mind, the problem was with my field being int and the value I was trying to set it to was somethnig like 0.233, which eventually kept putting in there...

Regards

Dynamic SQL in Stored Proc

Environment:
Window2K workstation, SQL Server 2000 Vesion 8.00.760 (SP3)
Setup:
I have a database setup so that NO users (except dbo) have READ,
UPDATE, or DELETE access to my database. But I have a single role
called MySPUser that is granted EXECUTE access to all of my stored
procs that do all data access for the system. The MySPUser role has a
single user in that group called MyUser, which is a windows domain
level account. My Webserver then impersonates that user when it calls
the stored procs. This setup worked on both my development machine and
my development test machine.
Problem:
So everything was going great for about a year when my dev machine
crashed. When I rebuilt the box with the same software (os and sql
included) everything seemed to be working just fine. The impersonated
user can still call all the stored proc and either retrieve or update
data. The only problem is that I have 2 stored proc that require
Dynamic SQL and they have stopped working. I now receive the following
error message when executing one of the stored procs.
SELECT permission denied on object 'tblMyTable', database
'MyApplication-Dev', owner 'dbo'.
I have tried deleting the users from the database and server and fully
rebuilding the users and roles with no luck. If I change my connection
string to point to my Test machine, which was built a year ago and also
uses Win2K and SQL2K SP3 everything seems to work fine.
Question:
What could cause Dynamic SQL Stored Procs to execute under a different
security context than Non-Dynamic SQL Stored Procs?
Any help would be greatly appreciated.
Will
P.S. I need to user dynamic sql because the sql statement is a query
for data by the user that can be searched on 12 different fields
simultaniously. Therefore the number of combinations of statements I
would need to build would be huge.It sounds like the user was granted access to the underlying tables.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Will" <WillCWirtz@.Yahoo.com> wrote in message
news:1129649346.104637.10300@.g43g2000cwa.googlegroups.com...
Environment:
Window2K workstation, SQL Server 2000 Vesion 8.00.760 (SP3)
Setup:
I have a database setup so that NO users (except dbo) have READ,
UPDATE, or DELETE access to my database. But I have a single role
called MySPUser that is granted EXECUTE access to all of my stored
procs that do all data access for the system. The MySPUser role has a
single user in that group called MyUser, which is a windows domain
level account. My Webserver then impersonates that user when it calls
the stored procs. This setup worked on both my development machine and
my development test machine.
Problem:
So everything was going great for about a year when my dev machine
crashed. When I rebuilt the box with the same software (os and sql
included) everything seemed to be working just fine. The impersonated
user can still call all the stored proc and either retrieve or update
data. The only problem is that I have 2 stored proc that require
Dynamic SQL and they have stopped working. I now receive the following
error message when executing one of the stored procs.
SELECT permission denied on object 'tblMyTable', database
'MyApplication-Dev', owner 'dbo'.
I have tried deleting the users from the database and server and fully
rebuilding the users and roles with no luck. If I change my connection
string to point to my Test machine, which was built a year ago and also
uses Win2K and SQL2K SP3 everything seems to work fine.
Question:
What could cause Dynamic SQL Stored Procs to execute under a different
security context than Non-Dynamic SQL Stored Procs?
Any help would be greatly appreciated.
Will
P.S. I need to user dynamic sql because the sql statement is a query
for data by the user that can be searched on 12 different fields
simultaniously. Therefore the number of combinations of statements I
would need to build would be huge.