Edit data with Query Analyzer

Is there any way I can use Query Analyzer to edit data in a table via a
graphic grid, i.e. actually changing individual values in cells? Using
SQL statements is too cumbersome for my purpose; I need to have more
hands-on control.Rick,
Use the Object Browser (F8) --> right-click table --> Open.
HTH
Jerry
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1dc1e9977390b6f9989901@.msnews.microsoft.com...
> Is there any way I can use Query Analyzer to edit data in a table via a
> graphic grid, i.e. actually changing individual values in cells? Using
> SQL statements is too cumbersome for my purpose; I need to have more
> hands-on control.|||Rick Charnes wrote:
> Is there any way I can use Query Analyzer to edit data in a table via
> a graphic grid, i.e. actually changing individual values in cells?
> Using SQL statements is too cumbersome for my purpose; I need to have
> more hands-on control.
Some tables yes and some tables no. I've never figures out why. I would
use SQL Enterprise Manager instead if you absolutely have to edit the
data in a table in this fashion.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I will give another solution, you can actually do everything you want to
manually. even queries can be developed using that... wondering what is it..
it is around every one but rarely people use that is access adp. More so, yo
u
can develop forms/ and reports from sql server data with ease.
follow these simple steps,
1.open access(better xp or above)
2.select new
3.select new project from exisiting data in the side pane( by this select
adp project not mdb)
4.data link dialogue appears, fill credentials and connection details
5.Rest is the world that you wont imagine
--
Regards
R.D
--Knowledge gets doubled when shared
"David Gugick" wrote:

> Rick Charnes wrote:
> Some tables yes and some tables no. I've never figures out why. I would
> use SQL Enterprise Manager instead if you absolutely have to edit the
> data in a table in this fashion.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||> Some tables yes and some tables no. I've never figures out why.
I always thought it is depending on whether the table has a PK, UQ, unique i
ndex or not.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eIQsIpc1FHA.3520@.TK2MSFTNGP10.phx.gbl...
> Rick Charnes wrote:
> Some tables yes and some tables no. I've never figures out why. I would us
e SQL Enterprise Manager
> instead if you absolutely have to edit the data in a table in this fashion
.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com|||David Gugick wrote:
> Rick Charnes wrote:
> Some tables yes and some tables no. I've never figures out why.
I've noticed that tables with no unique constraint of any
kind appear as read-only, but once you add a primary key for example,
you are able to modify data.
If QA front end is written in MFC, then it is probably related
to the behavior of MFC recordset, which is also read-only after
pulling data from such a table.|||Tibor Karaszi wrote:
> I always thought it is depending on whether the table has a PK, UQ,
> unique index or not.
That's probably the case.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Enterprise manager can do that, although I would not do it except when
creating a database for a non-live project.
All data manipulation needs to be done through applications such as
databrowsers and so on.
"Rick Charnes" <rickxyz--nospam.zyxcharnes@.thehartford.com> wrote in message
news:MPG.1dc1e9977390b6f9989901@.msnews.microsoft.com...
> Is there any way I can use Query Analyzer to edit data in a table via a
> graphic grid, i.e. actually changing individual values in cells? Using
> SQL statements is too cumbersome for my purpose; I need to have more
> hands-on control.

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.

Dynamically Drop and re-create all indexes for all tables

Hi There

I have a database where i want to move all data and indexes to separate drives via filegroups.

Apparantly there is no better way to do this in 2005 than in 2000, which means i need to drop and re-created clustered and non clustered index accordingly on the new filegroups.

What i did in 2000 was loop through tables and indexes dropping the indexes and dynamically re-creating the index defintions from sysindexes etc.

Is there an easier way to do this is 2005, in a nutshell how would i dynamically script the create index statements for all indexes for all tables in 2005.

Thanx

If you need just rebuild indexes, you could use ALTER with REBUILD instead of DELETE and CREATE|||Implementing partitions would allow you to shift things around as desired.|||Hi Konstantin i do not see the ON FILEGROUP syntax in BOL with ALTER INDEX ?

Dynamically configure CacheType?

I'm looking for a way to dynamically set the CacheType property of a Lookup transformation via a configuration variable. Is this possible? The CacheType does not appear to be a selectable property when choosing which properties to export to a configuration file. Can this property be manipulated through a script task inside the package?

Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.

So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.

|||

Ravi G wrote:

Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.

So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.|||

Phil Brammer wrote:

Ravi G wrote:

Interesting question. Looking into it I found out that the data flow components don't support expressions, which would have been nice and the solution to your problem. And you can't modify a package within itself. So, to answer your question, its not possible.

So, in view of that, if your data flow is not very complex, you can try two copies of it, on with the cached lookup another without. You would use expressions on the precedence constraint to decide which one to execute during runtime.

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Took the words right out of my mouth Phil In case anyone is interested, expressions on data-flow components was virtually the very last feature that was added to the product prior to RTM. All components CAN support expressions on their custom properties, the component developer decides whether or not it WILL by setting IDTSCustomProperty90.ExpressionType which is set to one of the values of the DTSCustomPropertyExpressionType enumeration.

In answer to the OP, CacheType of the Lookup component does not allow its value to be set by an expression, so you can't do what you want I'm afraid. If you want this behaviour changing then go to http://connect.microsoft.com/sqlserver/feedback

-Jamie

|||

Phil Brammer wrote:

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Phil,

I tried it and I can only see package level properties. I'll have to look closely.

But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?

|||

Ravi G wrote:

Phil Brammer wrote:

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Phil,

I tried it and I can only see package level properties. I'll have to look closely.

That's because not all components allow you to set their properties using expressions. if they do, then those properties will show up (off the top of my head I know that the SQLCommand property of the Datareader Source component can be set this way so take a look at that).

Ravi G wrote:

But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?

The path syntax allows for it - as you shall see.

Sounds like good blog material

-Jamie

|||

Ravi G wrote:

Phil Brammer wrote:

Ravi,
That isn't necessarily true. Right-clicking on the background of the control flow and selecting properties will allow you to access the expressions box. In that expressions box, if a data flow component supports expressions, it will appear in that list.

Phil,

I tried it and I can only see package level properties. I'll have to look closely.

But control flow doesn't seem like the right place to me. What if you have multiple instances of a data flow component that supports expressions, how would you know which property belongs to which instance?

Sorry. Right click on the data flow on the control flow background.

They are "fully qualified." [DFComponentName].[xxxx].[Property]|||

Phil Brammer wrote:

Sorry. Right click on the data flow on the control flow background.

They are "fully qualified." [DFComponentName].[xxxx].[Property]

Cool. I see it now.

In my package, the derived column was the only component that supported this.

This is how it shows up:

[DF component name].[Derived Column Output].[Column Name].[FriendlyExpression]

What does FriendlyExpression mean?

|||

Ravi G wrote:

Phil Brammer wrote:

Sorry. Right click on the data flow on the control flow background.

They are "fully qualified." [DFComponentName].[xxxx].[Property]

Cool. I see it now.

In my package, the derived column was the only component that supported this.

This is how it shows up:

[DF component name].[Derived Column Output].[Column Name].[FriendlyExpression]

What does FriendlyExpression mean?

That's the name of the property. Don't worry too much about why its called that, just know that it is a custom property that can be affected with an expression. More here: http://msdn2.microsoft.com/en-us/library/ms141069.aspx

Admittedly this takes a bit of getting your head around. Basically you can set an expression using an expression. Or to put it another way, the result of your expression is, in itself, another expression.

-Jamie

|||

I've found this useful reference for all the custom properties of the stock components.

http://msdn2.microsoft.com/en-us/ms136014.aspx

-Jamie