Hello!
I was wondering if anyone could help me.
I am running an Access application that uses linked tables to connect to a SQL server.
Ever since I turned on replication on those tables, I cannot add records to the table using regular Access forms (and, for that matter, I cannot do it by just double-clicking on the table from Access and trying to enter records).
Any help will be greatly appreciated!!!!!Have you also refreshed those links i.e.(deleted the links and recreated them in Access) since you "turned on replication on those tables" (or not)?|||Yes, I have deleted the link and recreated it after turning on replication, but I still get the same error every time I add a record.|||You are able to add a row via query analyzer or EM, and the table is not a read only subscribed table you are attempting to add rows to? (just asking the obvious things)
If all that is in order you might check to see if the generated link schemas include columns that the system automatically populates with data. If there are some (replication related columns) or others such as identity, GUID, timestamp, etc. columns (that are automatically populated), try removing them from the Access link schemas (not the base tables) then recreate your link(s) and attempt a test row insert. If that does not work try creating a view (test insertion in QA / EM), then create a linked table to the view and test its behavior.sql
Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Problems creating second relationship to the same table
I have two tables: ads and categories. I have an existing relationship: categories.id (PK) and ads.categoryid (FK). Now I want to create additional relationship with categories.id (PK) on ads.SecondCategoryID (FK). When I try to save it in SQL Manager I get the following error:
- Unable to create relationship 'FK_classifieds_Ads_classifieds_Categories2'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_classifieds_Ads_classifieds_Categories2". The conflict occurred in database "mydb", table "dbo.classifieds_Categories", column 'Id'.
Hi. Maybe the SecondCategoryId field has data that not exists in the categories table|||
mariop77 is probably right.
Try doing a SELECT DISTINCT SecondCategoryID FROM ADS
Make sure all FKs really do exist.
|||It has Nulls someties. How can I allow nulls? The reason is sometimes there will be no second category...|||Hi,
Actually, null is allowed here. But you need to make sure that Allow Null has been checked in the design view of the table.
|||Yes, it is allowed on the column level of course, yet can not create relationship...|||Seems to me like this is a limitation not allowing more than one join to the same table... Do I need to create alias for the existing table?
1 Category (alias 1) many Ads on Ads.CategoryID
1 Category (Alias 2) many Ads on Ads.SecondCategoryID
I used to be able to do this in MS Access, but maybe SQL does not allow...
sql
Problems creating "such" query
Hi guys,
There is a problem now. I'm currently using Access2000, two tables A & B respectively. The both tables contain one columns of data. What I am needed to do now is a query that results the difference in the data.
Eg. Table A contains 1,2,3 | Table B contains 1,3,5
The similar numbers: 1, 3
The different numbers: 2, 5
My query results need to show 2 & 5 as they are not in both the tables.
Can anyone enlighten me on this? I've tried Unmatch Query Wizard. But my results only shows 2 but not 5 because the left join only applies on one table - A.
Thanks for help in advance! Hope to hear from you guys real soon. This is a urgent problem. = )Here are 2. One that uses outer joins and the other uses a combination of union, minus and intersect.
1)
select a.a
from A LEFT OUTER JOIN B on a.a = b.b
where b.b is null
union
select b.b
from A RIGHT OUTER JOIN B on a.a = b.b
where a.a is null;
2)
(union)
minus
(intersection*)
* - difference in other DBMS's|||hey, thanks r123456
i really got what i wan, but that was a trial for me, may i know is it possible for the query to work if there are ten tables?
same thing applies, i need to get all the different numbers from
table - a,b,c,d,e,f,g,h,i and j.
Is it possible? can help me on this? thanks a lot anyway!|||You're talking about an EXTREMELY intensive query there. Is it necessary to do this in SQL?
Essentially you need to ask access to compare A to B, A to C, A to D, A to E, A to F, B to C, B to D, B to E, C to E, C to D, E to D
etc etc
This will be obscene to do in SQL, I would highly recommend scripting a small function to do it for you, depending on how you intend to use the data.|||Your comment ->
"i need to get all the different numbers from
table - a,b,c,d,e,f,g,h,i and j."
I have assumed you wish to return a single set containing all numbers from accross all tables that do not appear in the intersection of all tables.
If this is the case then for more than say 2-3 tables I would personally write a recursive code block. Essentially for a given number of tables any value in table1 that does not appear in table2 can be immediately disregarded as it instantly fails the intersection.
Thus instead of constructing a 10-way join or 10-way union minus 10-way intersection accross all tables, by using a recursive procedure, one is able to compare a continously decreasing set of tuples with each remaining table, providing the majority of numbers do not form the intersection.
Example
----
Table 1 - Table 2 returns a set of 6 matching tuples. Now these 2 tables can be disregarded, and only the 6 resulting tuples need be continued. After comparing to the next table, only 4 rows are returned. As the procedure continues this set decreases in size.|||But can i know how do i go about doing a recursive code block? can i run it using a macro? i'm really new to Access2000, hope u guys will help me. i'm willing to try though.
r123456,
i understand what you are trying to say, basically, compare table 1 & 2 first, then get the different numbers from these two tables, then get the result to compare with other tables am i right?
regards,
wensheng|||As you are using Access not SQL Server I don't know to what extent Access supports these types of queries.
For example a typical example of recursion may be:
List all parts and subparts along with all their prices required to make a specific device. Ie. Device requires 5 main parts and each main part requires 10-20 sub parts etc. This query can not be written in pure SQL hence external code is required, on ORACLE this can be PL/SQL. SQL Server would probably contain a similar concept.
Access however, I could not tell you. If it does not, then the above queries can be modified. The outer join needs to be altered sligtly whereas the (union) / (intersect) can simply be duplicated. Consider the following,
TableA Table B
--- ----
1 1
3 3
5 4
The intersection can be constructed as an INNER JOIN instead (note, intersection compares all columns not just a candidate key, which in this example is suitable), giving the first 2 rows. An obvious next step
would be:
select (a, b)
from a, b
where (a, b) NOT IN (the intersection returned set), this however would return a set of unwanted combinations that remained from the CP.
To resolve this one of the following can be done,
1) Union the tables instead of a cartesian product to eliminate combinations, then where union column NOT IN any of the columns returned by the inner join.
2) Perform an inner join on all tables with the join condition being the collection of 10 NOT IN / NOT EXISTS operators to check their column with any column in the intersection.
Clearly the 2nd option is not an option. Regarding option 1, the (minus and intersection) clauses have essentially been re-written. The question can now be simplified.
Is it faster to use (minus & intersect) as opposed to where NOT IN (INNER JOIN). I would suggest INTERSECT.|||Hi r1234567 again, thanks for replying promptly.. thanks a lot. appreciated. but somehow i tried something strange.. i think this kind of stupid idea can only come from my brain, wanna take a look? just see below Query Statement:
select [A].[Object ID], [A].[Object Description]
from A LEFT OUTER JOIN B on [A].[Object ID]=[B].[Object ID]
where [B].[Object ID] is null;
UNION select [B].[Object ID], [B].[Object Description]
from A RIGHT OUTER JOIN B on [A].[Object ID] = [B].[Object ID]
where [A].[Object ID] is null;
UNION select [A].[Object ID], [A].[Object Description]
from A LEFT OUTER JOIN C on [A].[Object ID]=[C].[Object ID]
where [C].[Object ID] is null;
UNION select [C].[Object ID], [C].[Object Description]
from A RIGHT OUTER JOIN C on [A].[Object ID] = [C].[Object ID]
where [A].[Object ID] is null;
UNION select [B].[Object ID], [B].[Object Description]
from B LEFT OUTER JOIN C on [B].[Object ID]=[C].[Object ID]
where [C].[Object ID] is null;
UNION select [C].[Object ID], [C].[Object Description]
from B RIGHT OUTER JOIN C on [B].[Object ID] = [C].[Object ID]
where [B].[Object ID] is null;
Somehow, this gives the result of what i want man! thanks a lot! i really appreciate u guys for helping.. especially r1234567! really.|||That is a valid solution for only three tables, please be cautioned that for each table you add, the number of select statements you will need to include will grow exponentially.|||Hi Teddy,
i know the number of sql statements are going to grow exponentially.
However the few solutions other than sql queries i ain't sure. I'm not a programmer or what, that's why i dun understand.
I'm sorry. I did this Union Queries till the 7th table. when i reach 8th table, there was an error, " Query too complex! "
So i stopped at 7th table. i think that should be enough. = )|||I'm not up to speed on my vb lately as I've been working primarily in delphi lately, but I can outline a basic concept. I would do this using an array and a for loop. Essentially declare an array to store your final value list. Open your first data set with SELECT * FROM A and scroll through the record set to assign values to the array...
Essentially you would need to declare an array, assign the first table to the array, then compare it to the next table. Scroll through each record in the next table, if a match is found in the array, delete the matching field in the array, otherwise add the field to the array from the dataset. Then compare to the next table and do the same.
basically you'd have a for loop that would iterate and either append or remove depending on a match. I hope that makes any sort of sense heh..
There is a problem now. I'm currently using Access2000, two tables A & B respectively. The both tables contain one columns of data. What I am needed to do now is a query that results the difference in the data.
Eg. Table A contains 1,2,3 | Table B contains 1,3,5
The similar numbers: 1, 3
The different numbers: 2, 5
My query results need to show 2 & 5 as they are not in both the tables.
Can anyone enlighten me on this? I've tried Unmatch Query Wizard. But my results only shows 2 but not 5 because the left join only applies on one table - A.
Thanks for help in advance! Hope to hear from you guys real soon. This is a urgent problem. = )Here are 2. One that uses outer joins and the other uses a combination of union, minus and intersect.
1)
select a.a
from A LEFT OUTER JOIN B on a.a = b.b
where b.b is null
union
select b.b
from A RIGHT OUTER JOIN B on a.a = b.b
where a.a is null;
2)
(union)
minus
(intersection*)
* - difference in other DBMS's|||hey, thanks r123456
i really got what i wan, but that was a trial for me, may i know is it possible for the query to work if there are ten tables?
same thing applies, i need to get all the different numbers from
table - a,b,c,d,e,f,g,h,i and j.
Is it possible? can help me on this? thanks a lot anyway!|||You're talking about an EXTREMELY intensive query there. Is it necessary to do this in SQL?
Essentially you need to ask access to compare A to B, A to C, A to D, A to E, A to F, B to C, B to D, B to E, C to E, C to D, E to D
etc etc
This will be obscene to do in SQL, I would highly recommend scripting a small function to do it for you, depending on how you intend to use the data.|||Your comment ->
"i need to get all the different numbers from
table - a,b,c,d,e,f,g,h,i and j."
I have assumed you wish to return a single set containing all numbers from accross all tables that do not appear in the intersection of all tables.
If this is the case then for more than say 2-3 tables I would personally write a recursive code block. Essentially for a given number of tables any value in table1 that does not appear in table2 can be immediately disregarded as it instantly fails the intersection.
Thus instead of constructing a 10-way join or 10-way union minus 10-way intersection accross all tables, by using a recursive procedure, one is able to compare a continously decreasing set of tuples with each remaining table, providing the majority of numbers do not form the intersection.
Example
----
Table 1 - Table 2 returns a set of 6 matching tuples. Now these 2 tables can be disregarded, and only the 6 resulting tuples need be continued. After comparing to the next table, only 4 rows are returned. As the procedure continues this set decreases in size.|||But can i know how do i go about doing a recursive code block? can i run it using a macro? i'm really new to Access2000, hope u guys will help me. i'm willing to try though.
r123456,
i understand what you are trying to say, basically, compare table 1 & 2 first, then get the different numbers from these two tables, then get the result to compare with other tables am i right?
regards,
wensheng|||As you are using Access not SQL Server I don't know to what extent Access supports these types of queries.
For example a typical example of recursion may be:
List all parts and subparts along with all their prices required to make a specific device. Ie. Device requires 5 main parts and each main part requires 10-20 sub parts etc. This query can not be written in pure SQL hence external code is required, on ORACLE this can be PL/SQL. SQL Server would probably contain a similar concept.
Access however, I could not tell you. If it does not, then the above queries can be modified. The outer join needs to be altered sligtly whereas the (union) / (intersect) can simply be duplicated. Consider the following,
TableA Table B
--- ----
1 1
3 3
5 4
The intersection can be constructed as an INNER JOIN instead (note, intersection compares all columns not just a candidate key, which in this example is suitable), giving the first 2 rows. An obvious next step
would be:
select (a, b)
from a, b
where (a, b) NOT IN (the intersection returned set), this however would return a set of unwanted combinations that remained from the CP.
To resolve this one of the following can be done,
1) Union the tables instead of a cartesian product to eliminate combinations, then where union column NOT IN any of the columns returned by the inner join.
2) Perform an inner join on all tables with the join condition being the collection of 10 NOT IN / NOT EXISTS operators to check their column with any column in the intersection.
Clearly the 2nd option is not an option. Regarding option 1, the (minus and intersection) clauses have essentially been re-written. The question can now be simplified.
Is it faster to use (minus & intersect) as opposed to where NOT IN (INNER JOIN). I would suggest INTERSECT.|||Hi r1234567 again, thanks for replying promptly.. thanks a lot. appreciated. but somehow i tried something strange.. i think this kind of stupid idea can only come from my brain, wanna take a look? just see below Query Statement:
select [A].[Object ID], [A].[Object Description]
from A LEFT OUTER JOIN B on [A].[Object ID]=[B].[Object ID]
where [B].[Object ID] is null;
UNION select [B].[Object ID], [B].[Object Description]
from A RIGHT OUTER JOIN B on [A].[Object ID] = [B].[Object ID]
where [A].[Object ID] is null;
UNION select [A].[Object ID], [A].[Object Description]
from A LEFT OUTER JOIN C on [A].[Object ID]=[C].[Object ID]
where [C].[Object ID] is null;
UNION select [C].[Object ID], [C].[Object Description]
from A RIGHT OUTER JOIN C on [A].[Object ID] = [C].[Object ID]
where [A].[Object ID] is null;
UNION select [B].[Object ID], [B].[Object Description]
from B LEFT OUTER JOIN C on [B].[Object ID]=[C].[Object ID]
where [C].[Object ID] is null;
UNION select [C].[Object ID], [C].[Object Description]
from B RIGHT OUTER JOIN C on [B].[Object ID] = [C].[Object ID]
where [B].[Object ID] is null;
Somehow, this gives the result of what i want man! thanks a lot! i really appreciate u guys for helping.. especially r1234567! really.|||That is a valid solution for only three tables, please be cautioned that for each table you add, the number of select statements you will need to include will grow exponentially.|||Hi Teddy,
i know the number of sql statements are going to grow exponentially.
However the few solutions other than sql queries i ain't sure. I'm not a programmer or what, that's why i dun understand.
I'm sorry. I did this Union Queries till the 7th table. when i reach 8th table, there was an error, " Query too complex! "
So i stopped at 7th table. i think that should be enough. = )|||I'm not up to speed on my vb lately as I've been working primarily in delphi lately, but I can outline a basic concept. I would do this using an array and a for loop. Essentially declare an array to store your final value list. Open your first data set with SELECT * FROM A and scroll through the record set to assign values to the array...
Essentially you would need to declare an array, assign the first table to the array, then compare it to the next table. Scroll through each record in the next table, if a match is found in the array, delete the matching field in the array, otherwise add the field to the array from the dataset. Then compare to the next table and do the same.
basically you'd have a for loop that would iterate and either append or remove depending on a match. I hope that makes any sort of sense heh..
Problems converting Access UNION query to SQL
I've got a Union query that works in Access but I'm unable to get it to work
querying a SQL Server. Access was using linked tables so the data was
really coming from SQL anyway. I want to make this query work within
Reporting Services, but I'm receiving the following error message:
ADO error: ORDER BY items must appear in the select list if the statement
contains a UNION operator.
I was able to take the two Select statements from Access and make good SQL
queries with them, but I'm unable to UNION them together eventhough it works
fine in Access. Here's my two queries I'm trying to Union:
SELECT TOP 100 PERCENT dbo.SOP30200.SLPRSNID, dbo.SOP30200.DOCAMNT
FROM dbo.SOP30200 LEFT OUTER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
dbo.RM00101.CUSTNMBR
WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
102)) AND (dbo.SOP30200.DOCDATE <= '2006-03-01') AND
(dbo.SOP30200.SOPTYPE = 3) AND (dbo.SOP30200.VOIDSTTS
= 0) AND (dbo.SOP30200.DOCID = 'inv')
ORDER BY dbo.SOP30200.SLPRSNID, dbo.SOP30200.CUSTNMBR
UNION
SELECT dbo.SOP30200.SLPRSNID, - (1 * dbo.SOP30200.DOCAMNT) AS expReturns
FROM dbo.SOP30200 LEFT OUTER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
dbo.viewSalesperson ON dbo.RM00101.SLPRSNID =
dbo.viewSalesperson.SLPRSNID
WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
102)) AND (dbo.SOP30200.DOCDATE <= CONVERT(DATETIME,
'2006-03-01 00:00:00', 102)) AND
(dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE = 4) AND
(dbo.SOP30200.DOCID = 'returns')order by of the first part of the union should be the problem.
Colin wrote:
>I've got a Union query that works in Access but I'm unable to get it to wor
k
>querying a SQL Server. Access was using linked tables so the data was
>really coming from SQL anyway. I want to make this query work within
>Reporting Services, but I'm receiving the following error message:
>ADO error: ORDER BY items must appear in the select list if the statement
>contains a UNION operator.
>I was able to take the two Select statements from Access and make good SQL
>queries with them, but I'm unable to UNION them together eventhough it work
s
>fine in Access. Here's my two queries I'm trying to Union:
>SELECT TOP 100 PERCENT dbo.SOP30200.SLPRSNID, dbo.SOP30200.DOCAMNT
>FROM dbo.SOP30200 LEFT OUTER JOIN
> dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
>dbo.RM00101.CUSTNMBR
>WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
>102)) AND (dbo.SOP30200.DOCDATE <= '2006-03-01') AND
> (dbo.SOP30200.SOPTYPE = 3) AND (dbo.SOP30200.VOIDSTTS
>= 0) AND (dbo.SOP30200.DOCID = 'inv')
>ORDER BY dbo.SOP30200.SLPRSNID, dbo.SOP30200.CUSTNMBR
>UNION
>SELECT dbo.SOP30200.SLPRSNID, - (1 * dbo.SOP30200.DOCAMNT) AS expReturn
s
>FROM dbo.SOP30200 LEFT OUTER JOIN
> dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
>dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
> dbo.viewSalesperson ON dbo.RM00101.SLPRSNID =
>dbo.viewSalesperson.SLPRSNID
>WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
>102)) AND (dbo.SOP30200.DOCDATE <= CONVERT(DATETIME,
> '2006-03-01 00:00:00', 102)) AND
>(dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE = 4) AND
>(dbo.SOP30200.DOCID = 'returns')
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||Any suggestions on what I need to do to fix it?
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:5cf4869c57190@.uwe...
> order by of the first part of the union should be the problem.
> Colin wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||Syntax is:
SELECT ... FROM ...
UNION (ALL)
SELECT ... FROM ...
ORDER BY...
(I also wonder why a TOP 100 PERCENT. That should return everything and
requires an ORDER BY to work. Get rid of the TOP 100 PERCENT> )
RLF
"Colin" <legendsfan@.nospam.nospam> wrote in message
news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Any suggestions on what I need to do to fix it?
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:5cf4869c57190@.uwe...
>|||Thanks for the help and feedback. I've got it working now. Is there a
reason why Visual Studio or any other design interface doesn't support
Design mode for UNION of two queries?
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uWwHb5uQGHA.2816@.TK2MSFTNGP15.phx.gbl...
> Syntax is:
> SELECT ... FROM ...
> UNION (ALL)
> SELECT ... FROM ...
> ORDER BY...
> (I also wonder why a TOP 100 PERCENT. That should return everything and
> requires an ORDER BY to work. Get rid of the TOP 100 PERCENT> )
> RLF
> "Colin" <legendsfan@.nospam.nospam> wrote in message
> news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
>|||A few notes that may help you (and others):
1. TOP 100 Percent ... ORDER BY was a workaround/hack/creative piece of SQL
to try to get the optimizer to sort values at a point in the query plan.
Unfortunately, it didn't really work in all cases, and it actually isn't
being honored at all in SQL 2005. So, please consider removing this from
your code in the future.
2. As you've seen from the other posts, ORDER BY should be applied to the
end of the complete statement to affect the presentation order of the
results returned to the client (and not each block of the UNION).
3. The column binding rules for Jet Red (Access's engine) never really
conformed to the ANSI standards. Given the installed base, it's not likely
to be changed. So, just be aware that the queries you may have in your
Access application are sometimes interpreted in slightly different ways.
Best of luck,
Conor Cunningham
SQL Server Query Optimization Development Lead
"Colin" <legendsfan@.nospam.nospam> wrote in message
news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Any suggestions on what I need to do to fix it?
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:5cf4869c57190@.uwe...
>
querying a SQL Server. Access was using linked tables so the data was
really coming from SQL anyway. I want to make this query work within
Reporting Services, but I'm receiving the following error message:
ADO error: ORDER BY items must appear in the select list if the statement
contains a UNION operator.
I was able to take the two Select statements from Access and make good SQL
queries with them, but I'm unable to UNION them together eventhough it works
fine in Access. Here's my two queries I'm trying to Union:
SELECT TOP 100 PERCENT dbo.SOP30200.SLPRSNID, dbo.SOP30200.DOCAMNT
FROM dbo.SOP30200 LEFT OUTER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
dbo.RM00101.CUSTNMBR
WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
102)) AND (dbo.SOP30200.DOCDATE <= '2006-03-01') AND
(dbo.SOP30200.SOPTYPE = 3) AND (dbo.SOP30200.VOIDSTTS
= 0) AND (dbo.SOP30200.DOCID = 'inv')
ORDER BY dbo.SOP30200.SLPRSNID, dbo.SOP30200.CUSTNMBR
UNION
SELECT dbo.SOP30200.SLPRSNID, - (1 * dbo.SOP30200.DOCAMNT) AS expReturns
FROM dbo.SOP30200 LEFT OUTER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
dbo.viewSalesperson ON dbo.RM00101.SLPRSNID =
dbo.viewSalesperson.SLPRSNID
WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
102)) AND (dbo.SOP30200.DOCDATE <= CONVERT(DATETIME,
'2006-03-01 00:00:00', 102)) AND
(dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE = 4) AND
(dbo.SOP30200.DOCID = 'returns')order by of the first part of the union should be the problem.
Colin wrote:
>I've got a Union query that works in Access but I'm unable to get it to wor
k
>querying a SQL Server. Access was using linked tables so the data was
>really coming from SQL anyway. I want to make this query work within
>Reporting Services, but I'm receiving the following error message:
>ADO error: ORDER BY items must appear in the select list if the statement
>contains a UNION operator.
>I was able to take the two Select statements from Access and make good SQL
>queries with them, but I'm unable to UNION them together eventhough it work
s
>fine in Access. Here's my two queries I'm trying to Union:
>SELECT TOP 100 PERCENT dbo.SOP30200.SLPRSNID, dbo.SOP30200.DOCAMNT
>FROM dbo.SOP30200 LEFT OUTER JOIN
> dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
>dbo.RM00101.CUSTNMBR
>WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
>102)) AND (dbo.SOP30200.DOCDATE <= '2006-03-01') AND
> (dbo.SOP30200.SOPTYPE = 3) AND (dbo.SOP30200.VOIDSTTS
>= 0) AND (dbo.SOP30200.DOCID = 'inv')
>ORDER BY dbo.SOP30200.SLPRSNID, dbo.SOP30200.CUSTNMBR
>UNION
>SELECT dbo.SOP30200.SLPRSNID, - (1 * dbo.SOP30200.DOCAMNT) AS expReturn
s
>FROM dbo.SOP30200 LEFT OUTER JOIN
> dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
>dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
> dbo.viewSalesperson ON dbo.RM00101.SLPRSNID =
>dbo.viewSalesperson.SLPRSNID
>WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
>102)) AND (dbo.SOP30200.DOCDATE <= CONVERT(DATETIME,
> '2006-03-01 00:00:00', 102)) AND
>(dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE = 4) AND
>(dbo.SOP30200.DOCID = 'returns')
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||Any suggestions on what I need to do to fix it?
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:5cf4869c57190@.uwe...
> order by of the first part of the union should be the problem.
> Colin wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||Syntax is:
SELECT ... FROM ...
UNION (ALL)
SELECT ... FROM ...
ORDER BY...
(I also wonder why a TOP 100 PERCENT. That should return everything and
requires an ORDER BY to work. Get rid of the TOP 100 PERCENT> )
RLF
"Colin" <legendsfan@.nospam.nospam> wrote in message
news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Any suggestions on what I need to do to fix it?
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:5cf4869c57190@.uwe...
>|||Thanks for the help and feedback. I've got it working now. Is there a
reason why Visual Studio or any other design interface doesn't support
Design mode for UNION of two queries?
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uWwHb5uQGHA.2816@.TK2MSFTNGP15.phx.gbl...
> Syntax is:
> SELECT ... FROM ...
> UNION (ALL)
> SELECT ... FROM ...
> ORDER BY...
> (I also wonder why a TOP 100 PERCENT. That should return everything and
> requires an ORDER BY to work. Get rid of the TOP 100 PERCENT> )
> RLF
> "Colin" <legendsfan@.nospam.nospam> wrote in message
> news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
>|||A few notes that may help you (and others):
1. TOP 100 Percent ... ORDER BY was a workaround/hack/creative piece of SQL
to try to get the optimizer to sort values at a point in the query plan.
Unfortunately, it didn't really work in all cases, and it actually isn't
being honored at all in SQL 2005. So, please consider removing this from
your code in the future.
2. As you've seen from the other posts, ORDER BY should be applied to the
end of the complete statement to affect the presentation order of the
results returned to the client (and not each block of the UNION).
3. The column binding rules for Jet Red (Access's engine) never really
conformed to the ANSI standards. Given the installed base, it's not likely
to be changed. So, just be aware that the queries you may have in your
Access application are sometimes interpreted in slightly different ways.
Best of luck,
Conor Cunningham
SQL Server Query Optimization Development Lead
"Colin" <legendsfan@.nospam.nospam> wrote in message
news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Any suggestions on what I need to do to fix it?
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:5cf4869c57190@.uwe...
>
Tuesday, March 20, 2012
problems building sql statement
Hello,
I'm having problems building an sql stament that joins a few tables. I can seem to get my head around the structure!
I have to try and link up four different tables to try and get my result.
Here are the 4 table structures...
Web_Users
------
User_ID
Name
Tags_Table
------
Tags_ID
User_ID
Group_ID
Title
Created_Groups
--------
Group_ID
Group_Name
Tags_To_Groups
--------
Group_Link_ID
Group_ID
Tag_ID
Basically, this database, has four tables; One table (Web_Users) that contains a users name, and assigns a unique ID (User_ID), another table that stores a users tags they have created, and also links it to a group_ID. The created_groups table, contains group names and assigns a unique id also. And the last table, Tags_To_Groups, links tags to groups.
So this is what I'm trying to do...
I'm trying to get the Group_name field from Created_Groups table, of a tag , that belongs to a certain user. If sounds easy when I say it like that, but I've been inner joining tables all night and failing every time.
Does this make sense? Can anyone help?
Thank you
Hmm... maybe I didn't understand, but here's what I came up with:
select cg.Group_Name
from Created_Groups cg (nolock)
join Tags_Table tt (nolock) on tt.Group_ID = cg.Group_ID
and tt.User_ID = @.User_ID
select cg.Group_Name
from Created_Groups cg
inner join Tags_Table tt on cg.Group_ID = tt.Group_ID
and tt.User_ID = ?
or:
select cg.Group_Name
from Created_Groups cg
inner join Tags_Table tt on cg.Group_ID = tt.Group_ID
inner join Web_Users wu on tt.User_ID = wu.User_ID
and wu.User_Name = '?'
or
select cg.GroupName
from Web_Users wu
inner join Tags_Table tt on wu.User_ID = tt.User_ID
and wu.User_Name = '?'
inner join Created_Groups cg on tt.Group_ID = cg.Group_ID
Depending upon indexes, some ways will be faster than others.
|||
amazing. thank you both!
problems adding records to linked tables
Hi everyone,
I just used the upsize wizard in Access to create a Access front end and SQL server 2000 backend with linked tables, but I am receiving an error that I can't figure out.
When I am trying to add a record into my 'Cases' linked table in the SQL server, I get a error saying: "Invalid object name" with -2147217865 as the error message.
Here is my code:
Dim testrecord As ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=sqloledb.1;" & _
"DSN=intelitrak;" & _
"UID=geir;PWD=test"
conn.Mode = adModeReadWrite
conn.Open
Set testrecord = New ADODB.Recordset
testrecord.Open "Cases", conn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect -- the ERROR occurs here!!!
With testrecord
.AddNew
If Forms(stDocName)!NewReopen = "New" Then
!MasterID = NewID
IsReOpen = False
Else
....etc.
I looked all around for an answer, and I partially found a answer at Microsoft's website: http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q218/9/95.asp&NoWebContent=1
However, I do not know how to make this work. I tried to create a stored procedure, and test if that made any differance, but it still said it the it was a invalid object.
Can anyone help me?
GeirJust a shot in the dark...
What if you add dbo_ to the front of Cases|||That was not it.
I tried to put 'dbo_Cases' in my VBA code, but the same error happens.
Geir|||Sorry...mayb a post in the VB forum...
Do you have other code that connects?|||none of my code that involves adding new records to the database is working. The code that views the records, however, works perfectly.
Geir
I just used the upsize wizard in Access to create a Access front end and SQL server 2000 backend with linked tables, but I am receiving an error that I can't figure out.
When I am trying to add a record into my 'Cases' linked table in the SQL server, I get a error saying: "Invalid object name" with -2147217865 as the error message.
Here is my code:
Dim testrecord As ADODB.Recordset
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=sqloledb.1;" & _
"DSN=intelitrak;" & _
"UID=geir;PWD=test"
conn.Mode = adModeReadWrite
conn.Open
Set testrecord = New ADODB.Recordset
testrecord.Open "Cases", conn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect -- the ERROR occurs here!!!
With testrecord
.AddNew
If Forms(stDocName)!NewReopen = "New" Then
!MasterID = NewID
IsReOpen = False
Else
....etc.
I looked all around for an answer, and I partially found a answer at Microsoft's website: http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q218/9/95.asp&NoWebContent=1
However, I do not know how to make this work. I tried to create a stored procedure, and test if that made any differance, but it still said it the it was a invalid object.
Can anyone help me?
GeirJust a shot in the dark...
What if you add dbo_ to the front of Cases|||That was not it.
I tried to put 'dbo_Cases' in my VBA code, but the same error happens.
Geir|||Sorry...mayb a post in the VB forum...
Do you have other code that connects?|||none of my code that involves adding new records to the database is working. The code that views the records, however, works perfectly.
Geir
Friday, March 9, 2012
Problem: mssql locks several records in a specific table, while no one is editing them
Hi,
I have an Access application with linked tables via ODBC to MSSQL
server 2000.
I have an Access application with linked tables via ODBC to MSSQL
server 2000.
Having a weird problem, probably something i've done while not being
aware of (kinda newbie).
the last 20 records (and growing)of a specific table are locked - cant
change them - ("another user is editing these records ... ").
I know for a fact that no one is editing records and yet no user can
edit these last records in the MDB - including the administrator -
while able to add new records.
Administrator able to edit records in the ADP (mssql server) where the
tables are stored.
Please help, the application is renedred inert .
Thanks for reading,
Oren.Hi
I can't say that I am an expert on access, but this sounds like the
recordset may need refreshing i.e someone has changed the record and access
knows the local copy is out of date.
John
"Oren" <orentini@.hotmail.com> wrote in message
news:b1368c93.0409160909.128bae81@.posting.google.c om...
> Hi,
> I have an Access application with linked tables via ODBC to MSSQL
> server 2000.
> Having a weird problem, probably something i've done while not being
> aware of (kinda newbie).
> the last 20 records (and growing)of a specific table are locked - cant
> change them - ("another user is editing these records ... ").
> I know for a fact that no one is editing records and yet no user can
> edit these last records in the MDB - including the administrator -
> while able to add new records.
> Administrator able to edit records in the ADP (mssql server) where the
> tables are stored.
> Please help, the application is renedred inert .
> Thanks for reading,
> Oren.|||Hi,
thanks for replying.
There is no local and remote copy of the table, the table is only
stored in the sql server and linked to the access MDB.
Simple refresh wont do (tried it, via the access menubars).
have any more suggestions?
Thanks,
Oren,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<414b53f6$0$27296$afc38c87@.news.easynet.co.uk>...
> Hi
> I can't say that I am an expert on access, but this sounds like the
> recordset may need refreshing i.e someone has changed the record and access
> knows the local copy is out of date.
> John
> "Oren" <orentini@.hotmail.com> wrote in message
> news:b1368c93.0409160909.128bae81@.posting.google.c om...
> > Hi,
> > I have an Access application with linked tables via ODBC to MSSQL
> > server 2000.
> > Having a weird problem, probably something i've done while not being
> > aware of (kinda newbie).
> > the last 20 records (and growing)of a specific table are locked - cant
> > change them - ("another user is editing these records ... ").
> > I know for a fact that no one is editing records and yet no user can
> > edit these last records in the MDB - including the administrator -
> > while able to add new records.
> > Administrator able to edit records in the ADP (mssql server) where the
> > tables are stored.
> > Please help, the application is renedred inert .
> > Thanks for reading,
> > Oren.|||Hi
You may want to post this is an access news group!
John
"Oren" <orentini@.hotmail.com> wrote in message
news:b1368c93.0409180015.718465e6@.posting.google.c om...
> Hi,
> thanks for replying.
> There is no local and remote copy of the table, the table is only
> stored in the sql server and linked to the access MDB.
> Simple refresh wont do (tried it, via the access menubars).
> have any more suggestions?
> Thanks,
> Oren,
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<414b53f6$0$27296$afc38c87@.news.easynet.co.uk>...
> > Hi
> > I can't say that I am an expert on access, but this sounds like the
> > recordset may need refreshing i.e someone has changed the record and
access
> > knows the local copy is out of date.
> > John
> > "Oren" <orentini@.hotmail.com> wrote in message
> > news:b1368c93.0409160909.128bae81@.posting.google.c om...
> > > Hi,
> > > I have an Access application with linked tables via ODBC to MSSQL
> > > server 2000.
> > > > Having a weird problem, probably something i've done while not being
> > > aware of (kinda newbie).
> > > > the last 20 records (and growing)of a specific table are locked - cant
> > > change them - ("another user is editing these records ... ").
> > > > I know for a fact that no one is editing records and yet no user can
> > > edit these last records in the MDB - including the administrator -
> > > while able to add new records.
> > > > Administrator able to edit records in the ADP (mssql server) where the
> > > tables are stored.
> > > > Please help, the application is renedred inert .
> > > > Thanks for reading,
> > > Oren.
Problem: "joining" data from two tables
Hi,
I'm a quite newbie with sql. I have problem with joining data from two tables. I think that example will describe it in the best way.
Table 1:
Date Data1
12.12.2000 3
13.12.2000 4
15.12.2000 6
Table 2:
Date Data2
12.12.2000 4
14.12.2000 8
15.12.2000 9
16.12.2000 4
Result (this is a thing that i need)
Date Data1 Data2
12.12.2000 3 4
13.12.2000 4 (0)
14.12.2000 (0) 8
15.12.2000 6 9
16.12.2000 0 4
Numbers in () are values that does not really exist in tables 1 or 2, but they are understood as zero. If I use
select table1.date, data1, data2 from table1,table2 where table1.date=table2.date
result does not contain rows (dates) that where _not_ in both tables.
-Timowhat you want is a FULL OUTER JOIN:select coalesce(Table1.Date,Table2.Date) as Date
, coalesce(Table1.Data1,0) as Data1
, coalesce(Table2.Data2,0) as Data2
from Table1
full outer
join Table2
on Table1.Date = Table2.Date|||Thanks, it works now.
I'm a quite newbie with sql. I have problem with joining data from two tables. I think that example will describe it in the best way.
Table 1:
Date Data1
12.12.2000 3
13.12.2000 4
15.12.2000 6
Table 2:
Date Data2
12.12.2000 4
14.12.2000 8
15.12.2000 9
16.12.2000 4
Result (this is a thing that i need)
Date Data1 Data2
12.12.2000 3 4
13.12.2000 4 (0)
14.12.2000 (0) 8
15.12.2000 6 9
16.12.2000 0 4
Numbers in () are values that does not really exist in tables 1 or 2, but they are understood as zero. If I use
select table1.date, data1, data2 from table1,table2 where table1.date=table2.date
result does not contain rows (dates) that where _not_ in both tables.
-Timowhat you want is a FULL OUTER JOIN:select coalesce(Table1.Date,Table2.Date) as Date
, coalesce(Table1.Data1,0) as Data1
, coalesce(Table2.Data2,0) as Data2
from Table1
full outer
join Table2
on Table1.Date = Table2.Date|||Thanks, it works now.
Problem: Trigger and multiple updates to the table
Hey, I have couple of triggers to one of the tables. It is failing if I update multiple records at the same time from the stored procedure.
UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20
Error I am getting is :
Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
What is the best possible way to make it work? Thank you.Figure out which trigger is blowing up, and fix it? Knowing that there is more code than we can see, I can't for the life of me figure out a way to help you find out where the problem is, much less how to fix it.
-PatP|||I guess this is the problematic trigger. Should I use cursor to handle multiple inserts/updates?
CREATE trigger t_thickupdate on dbo.sched_lot_hdr
for update
as
-- declare local variables
declare
@.lotno int,
@.charthick varchar(10),
@.decthick decimal(6,5)
-- populate local variables
set @.lotno = (select lotno from inserted)
set @.charthick = (select matcharthick from inserted)
set @.decthick = (select matthick from inserted)
-- determine which was updated: character thickness or decimal thickness
if update(matthick)
-- decimal thickness was updated
begin
set @.charthick = (select thkfrac from prod_thk_stds where thkdec = @.decthick)
update sched_lot_hdr set matcharthick = @.charthick where lotno = @.lotno
end
else
-- character thickness was updated
begin
set @.decthick = (select thkdec from prod_thk_stds where thkfrac = @.charthick)
update sched_lot_hdr set matthick = @.decthick where lotno = @.lotno
end|||A trigger act on 1 or more rows...depending on the DML
inserted and/or deleted can have more than 1 row...
You need to al;ter your thinking and think more set based...|||Also looking at it...
Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense...|||I'd suggest using:CREATE TRIGGER t_thickupdate
ON dbo.sched_lot_hdr
FOR UPDATE
AS
-- determine which was updated: character thickness or decimal thickness
IF Update(matthick) -- decimal thickness was updated
UPDATE sched_lot_hdr
SET matcharthick = s.thkfrac
FROM inserted AS i
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)
JOIN prod_thk_stds AS s
ON (s.thkdec = i.matthick)
ELSE -- character thickness was updated
update sched_lot_hdr
set matthick = s.thkdec
FROM inserted AS i
JOIN prod_thk_stds AS s
ON (s.thkfrac = i.matcharthick)
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)
RETURNNote that you need to test this up one side and down the other before you put it into production, this was a quick paste up job with no opportunity for me to test it!
-PatP|||yeah :(
This already been developed. I came to this project recently and working on some enhancements. Thanks for the help. I think I understand the problem, I will resolve it.
Update: thats fast. Thanks Pat, I will test it.|||Originally posted by Brett Kaiser
A trigger act on 1 or more rows...depending on the DML
inserted and/or deleted can have more than 1 row...
You need to al;ter your thinking and think more set based... Yea vous! I'm with you on that!
Originally posted by Brett Kaiser
Also looking at it...
Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense... Nah, that happens all the time when folks convert from what I call "unit-record" code to a database. They find ways to get the database to "clean up" data coming from disparate sources that would have required application changes when the only thing that could reach the data was their application.
-PatP
UPDATE
table1
SET
col1 = 0
WHERE col2 between 10 and 20
Error I am getting is :
Server: Msg 512, Level 16, State 1, Procedure t_thickupdate, Line 12
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
What is the best possible way to make it work? Thank you.Figure out which trigger is blowing up, and fix it? Knowing that there is more code than we can see, I can't for the life of me figure out a way to help you find out where the problem is, much less how to fix it.
-PatP|||I guess this is the problematic trigger. Should I use cursor to handle multiple inserts/updates?
CREATE trigger t_thickupdate on dbo.sched_lot_hdr
for update
as
-- declare local variables
declare
@.lotno int,
@.charthick varchar(10),
@.decthick decimal(6,5)
-- populate local variables
set @.lotno = (select lotno from inserted)
set @.charthick = (select matcharthick from inserted)
set @.decthick = (select matthick from inserted)
-- determine which was updated: character thickness or decimal thickness
if update(matthick)
-- decimal thickness was updated
begin
set @.charthick = (select thkfrac from prod_thk_stds where thkdec = @.decthick)
update sched_lot_hdr set matcharthick = @.charthick where lotno = @.lotno
end
else
-- character thickness was updated
begin
set @.decthick = (select thkdec from prod_thk_stds where thkfrac = @.charthick)
update sched_lot_hdr set matthick = @.decthick where lotno = @.lotno
end|||A trigger act on 1 or more rows...depending on the DML
inserted and/or deleted can have more than 1 row...
You need to al;ter your thinking and think more set based...|||Also looking at it...
Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense...|||I'd suggest using:CREATE TRIGGER t_thickupdate
ON dbo.sched_lot_hdr
FOR UPDATE
AS
-- determine which was updated: character thickness or decimal thickness
IF Update(matthick) -- decimal thickness was updated
UPDATE sched_lot_hdr
SET matcharthick = s.thkfrac
FROM inserted AS i
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)
JOIN prod_thk_stds AS s
ON (s.thkdec = i.matthick)
ELSE -- character thickness was updated
update sched_lot_hdr
set matthick = s.thkdec
FROM inserted AS i
JOIN prod_thk_stds AS s
ON (s.thkfrac = i.matcharthick)
JOIN sched_lot_hdr
ON (sched_lot_hdr.lotno = i.lotno)
RETURNNote that you need to test this up one side and down the other before you put it into production, this was a quick paste up job with no opportunity for me to test it!
-PatP|||yeah :(
This already been developed. I came to this project recently and working on some enhancements. Thanks for the help. I think I understand the problem, I will resolve it.
Update: thats fast. Thanks Pat, I will test it.|||Originally posted by Brett Kaiser
A trigger act on 1 or more rows...depending on the DML
inserted and/or deleted can have more than 1 row...
You need to al;ter your thinking and think more set based... Yea vous! I'm with you on that!
Originally posted by Brett Kaiser
Also looking at it...
Your updating the same table in the trigger that is causing the trigger action because of an update....that doesn't make sense... Nah, that happens all the time when folks convert from what I call "unit-record" code to a database. They find ways to get the database to "clean up" data coming from disparate sources that would have required application changes when the only thing that could reach the data was their application.
-PatP
Saturday, February 25, 2012
Problem with Views
I have a situation with a view. This view uses 2 or 3 or 4 tables to
retrieve data, doesn't matter I have several. Someone came to me and said
"During design, I forgot a field..." in one of the tables that make up this
view. So the field was inserted into one of the tables. Now when I execute
the view, the data is off one field AFTER the added field. What I mean by
that is I have an "Code" field and then a "Date" field after the newly added
field. The data from the "Code" field now shows up in the "Date" field, and
so on.
To fix this problem, I simply open the view, and resave it, and the problem
goes away. My questions are:
1) Am I doing something wrong in creating my views?
2) Is there a way to fix all my views instead of opening and resaving all.
Thanks for any help with this
Brij
When you create a view,. SQL Server stores meta-data in syscolumns. This doesn't get updated if
change the underlying table. To refresh a view definition, you can use sp_refreshview. Most of us do
not use SELECT * in views, btw, instead we prefer to list all columns to avoid problems as table
structure changes...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brij Singh" <brijs@.telcorinc.com> wrote in message
news:4141d5d5$0$25239$9a6e19ea@.news.newshosting.co m...
> I have a situation with a view. This view uses 2 or 3 or 4 tables to
> retrieve data, doesn't matter I have several. Someone came to me and said
> "During design, I forgot a field..." in one of the tables that make up this
> view. So the field was inserted into one of the tables. Now when I execute
> the view, the data is off one field AFTER the added field. What I mean by
> that is I have an "Code" field and then a "Date" field after the newly added
> field. The data from the "Code" field now shows up in the "Date" field, and
> so on.
> To fix this problem, I simply open the view, and resave it, and the problem
> goes away. My questions are:
> 1) Am I doing something wrong in creating my views?
> 2) Is there a way to fix all my views instead of opening and resaving all.
> Thanks for any help with this
> Brij
>
retrieve data, doesn't matter I have several. Someone came to me and said
"During design, I forgot a field..." in one of the tables that make up this
view. So the field was inserted into one of the tables. Now when I execute
the view, the data is off one field AFTER the added field. What I mean by
that is I have an "Code" field and then a "Date" field after the newly added
field. The data from the "Code" field now shows up in the "Date" field, and
so on.
To fix this problem, I simply open the view, and resave it, and the problem
goes away. My questions are:
1) Am I doing something wrong in creating my views?
2) Is there a way to fix all my views instead of opening and resaving all.
Thanks for any help with this
Brij
When you create a view,. SQL Server stores meta-data in syscolumns. This doesn't get updated if
change the underlying table. To refresh a view definition, you can use sp_refreshview. Most of us do
not use SELECT * in views, btw, instead we prefer to list all columns to avoid problems as table
structure changes...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brij Singh" <brijs@.telcorinc.com> wrote in message
news:4141d5d5$0$25239$9a6e19ea@.news.newshosting.co m...
> I have a situation with a view. This view uses 2 or 3 or 4 tables to
> retrieve data, doesn't matter I have several. Someone came to me and said
> "During design, I forgot a field..." in one of the tables that make up this
> view. So the field was inserted into one of the tables. Now when I execute
> the view, the data is off one field AFTER the added field. What I mean by
> that is I have an "Code" field and then a "Date" field after the newly added
> field. The data from the "Code" field now shows up in the "Date" field, and
> so on.
> To fix this problem, I simply open the view, and resave it, and the problem
> goes away. My questions are:
> 1) Am I doing something wrong in creating my views?
> 2) Is there a way to fix all my views instead of opening and resaving all.
> Thanks for any help with this
> Brij
>
Problem with Views
I have a situation with a view. This view uses 2 or 3 or 4 tables to
retrieve data, doesn't matter I have several. Someone came to me and said
"During design, I forgot a field..." in one of the tables that make up this
view. So the field was inserted into one of the tables. Now when I execute
the view, the data is off one field AFTER the added field. What I mean by
that is I have an "Code" field and then a "Date" field after the newly added
field. The data from the "Code" field now shows up in the "Date" field, and
so on.
To fix this problem, I simply open the view, and resave it, and the problem
goes away. My questions are:
1) Am I doing something wrong in creating my views?
2) Is there a way to fix all my views instead of opening and resaving all.
Thanks for any help with this
BrijWhen you create a view,. SQL Server stores meta-data in syscolumns. This doesn't get updated if
change the underlying table. To refresh a view definition, you can use sp_refreshview. Most of us do
not use SELECT * in views, btw, instead we prefer to list all columns to avoid problems as table
structure changes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brij Singh" <brijs@.telcorinc.com> wrote in message
news:4141d5d5$0$25239$9a6e19ea@.news.newshosting.com...
> I have a situation with a view. This view uses 2 or 3 or 4 tables to
> retrieve data, doesn't matter I have several. Someone came to me and said
> "During design, I forgot a field..." in one of the tables that make up this
> view. So the field was inserted into one of the tables. Now when I execute
> the view, the data is off one field AFTER the added field. What I mean by
> that is I have an "Code" field and then a "Date" field after the newly added
> field. The data from the "Code" field now shows up in the "Date" field, and
> so on.
> To fix this problem, I simply open the view, and resave it, and the problem
> goes away. My questions are:
> 1) Am I doing something wrong in creating my views?
> 2) Is there a way to fix all my views instead of opening and resaving all.
> Thanks for any help with this
> Brij
>
retrieve data, doesn't matter I have several. Someone came to me and said
"During design, I forgot a field..." in one of the tables that make up this
view. So the field was inserted into one of the tables. Now when I execute
the view, the data is off one field AFTER the added field. What I mean by
that is I have an "Code" field and then a "Date" field after the newly added
field. The data from the "Code" field now shows up in the "Date" field, and
so on.
To fix this problem, I simply open the view, and resave it, and the problem
goes away. My questions are:
1) Am I doing something wrong in creating my views?
2) Is there a way to fix all my views instead of opening and resaving all.
Thanks for any help with this
BrijWhen you create a view,. SQL Server stores meta-data in syscolumns. This doesn't get updated if
change the underlying table. To refresh a view definition, you can use sp_refreshview. Most of us do
not use SELECT * in views, btw, instead we prefer to list all columns to avoid problems as table
structure changes...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brij Singh" <brijs@.telcorinc.com> wrote in message
news:4141d5d5$0$25239$9a6e19ea@.news.newshosting.com...
> I have a situation with a view. This view uses 2 or 3 or 4 tables to
> retrieve data, doesn't matter I have several. Someone came to me and said
> "During design, I forgot a field..." in one of the tables that make up this
> view. So the field was inserted into one of the tables. Now when I execute
> the view, the data is off one field AFTER the added field. What I mean by
> that is I have an "Code" field and then a "Date" field after the newly added
> field. The data from the "Code" field now shows up in the "Date" field, and
> so on.
> To fix this problem, I simply open the view, and resave it, and the problem
> goes away. My questions are:
> 1) Am I doing something wrong in creating my views?
> 2) Is there a way to fix all my views instead of opening and resaving all.
> Thanks for any help with this
> Brij
>
Problem with view
I am trying to create a view that creates a mailing list from two
different tables. The t-sql code executes successfully but when I try
to open the view I get this error message [Microsoft][ODBC SQL Server
Driver][SQL Server]Error converting data type vchar to float.
different tables. The t-sql code executes successfully but when I try
to open the view I get this error message [Microsoft][ODBC SQL Server
Driver][SQL Server]Error converting data type vchar to float.
Here is the t-sql code I used to create the view:
CREATE VIEW [vwAllSnailMail]
AS
SELECT [dbo].[_tblLeads].[FirstName] + ' ' +
[dbo].[_tblLeads].[LastName] as Fullname, [dbo].[_tblLeads].[Address 1]
as Address1, [dbo].[_tblLeads].[Address 2] as Address2,
[dbo].[_tblLeads].[City] as City, [dbo].[_tblLeads].[State] as ST,
[dbo].[_tblLeads].[Zip] as Zip
FROM [dbo].[_tblLeads]
WHERE [dbo].[_tblLeads].[Address 1] IS NOT NULL AND
[dbo].[_tblLeads].[City] IS NOT NULL AND [dbo].[_tblLeads].[State] IS
NOT NULL AND [dbo].[_tblLeads].[Zip] IS NOT NULL
UNION
SELECT [dbo].[tblClients].[FName] + ' ' + [dbo].[tblClients].[LName] as
Fullname, [dbo].[tblClients].[Street1] as Address1,
[dbo].[tblClients].[Street2] as Address2, [dbo].[tblClients].[City] as
City, [dbo].[tblClients].[State_cd] as ST, [dbo].[tblClients].[Zip] as
Zip
FROM [dbo].[tblClients]
WHERE [dbo].[tblClients].[Street1]<>'' AND [dbo].[tblClients].[City]
<>'' AND [dbo].[tblClients].[State_cd] <>'0' AND
[dbo].[tblClients].[Zip] <>''
Note - When I separate the select statements and create two separate
views, both execute correctly AND I can view the results for each
individual view. I just can't get the above to work. What am I
missing?When you have UNION the columns in the first SELECT statement define the
datatype of the columns. The columns in the following SELECT statements must
match the datatype or be implicitly convertible. In your case implicit
conversion from varchar to float is allowed. Then the problem is that a
column in your first SELECT statement is of type float and the same column
in the second SELECT is a varchar, but the varchar column has some
characters that cannot be converted to float.
Without seeing the table definitions and just guessing it seems that your
_tblLeads.Zip might be of type float and tblClients.Zip is of type varchar.
Most likely tblClients.Zip has some invalid characters that cannot be
converted to float. One solution is to replace the column
[dbo].[_tblLeads].[Zip] in your first SELECT (if that is the correct column)
with an explicit conversion to varchar, like CAST([dbo].[_tblLeads].[Zip] AS
varchar). To be more precise you can convert to varchar(n) where n is the
exact size of the column.
Regards,
Plamen Ratchev
http://www.SQLStudio.com|||Hi
What are the field format at each table?
Harry
On Jan 23, 1:34 am, "Chris" <cjscu...@.gmail.comwrote:
Quote:
Originally Posted by
I am trying to create a view that creates a mailing list from two
different tables. The t-sql code executes successfully but when I try
to open the view I get this error message [Microsoft][ODBC SQL Server
Driver][SQL Server]Error converting data type vchar to float.
>
Here is the t-sql code I used to create the view:
>
CREATE VIEW [vwAllSnailMail]
AS
SELECT [dbo].[_tblLeads].[FirstName] + ' ' +
[dbo].[_tblLeads].[LastName] as Fullname, [dbo].[_tblLeads].[Address 1]
as Address1, [dbo].[_tblLeads].[Address 2] as Address2,
[dbo].[_tblLeads].[City] as City, [dbo].[_tblLeads].[State] as ST,
[dbo].[_tblLeads].[Zip] as Zip
FROM [dbo].[_tblLeads]
WHERE [dbo].[_tblLeads].[Address 1] IS NOT NULL AND
[dbo].[_tblLeads].[City] IS NOT NULL AND [dbo].[_tblLeads].[State] IS
NOT NULL AND [dbo].[_tblLeads].[Zip] IS NOT NULL
UNION
SELECT [dbo].[tblClients].[FName] + ' ' + [dbo].[tblClients].[LName] as
Fullname, [dbo].[tblClients].[Street1] as Address1,
[dbo].[tblClients].[Street2] as Address2, [dbo].[tblClients].[City] as
City, [dbo].[tblClients].[State_cd] as ST, [dbo].[tblClients].[Zip] as
Zip
FROM [dbo].[tblClients]
WHERE [dbo].[tblClients].[Street1]<>'' AND [dbo].[tblClients].[City]
<>'' AND [dbo].[tblClients].[State_cd] <>'0' AND
[dbo].[tblClients].[Zip] <>''
>
Note - When I separate the select statements and create two separate
views, both execute correctly AND I can view the results for each
individual view. I just can't get the above to work. What am I
missing?
Monday, February 20, 2012
Problem with user defined funtions
Hi,
I defined a publisher and a pushsubscriber with some articles. The articles
were Tables, Views and user defined functions. The pushsubscriber was created
with the sync_type=automatic. When I synchronisize the publisher with the
subscriber, the merge-agent fails on the follwing view creation command:
CREATE VIEW [dbo].[TGS_CATALOG]
AS
SELECT TOP 100 PERCENT dbo.TGS_BAUMTHEMEN.SORT * 100 +
dbo.TGS_CATALOG_MASTER.SORT AS LFDNUM,
dbo.NullStringFilter(dbo.TGS_REVIERE.REVIER) +
dbo.NullStringFilter(dbo.TGS_BAUMTHEMEN.BAUMTHEMA)
+
dbo.NullStringFilter(dbo.TGS_CATALOG_MASTER.BAUMZW EIG1) COLLATE
Latin1_General_CS_AS AS PFAD,
dbo.TGS_CATALOG_MASTER.ENTITYNUM,
dbo.TGS_CATALOG_MASTER.TABLENAME, dbo.TGS_CATALOG_MASTER.SORT,
dbo.TGS_CATALOG_MASTER.THEMENELEMENT,
dbo.TGS_CATALOG_MASTER.THEMA, dbo.TGS_CATALOG_MASTER.DECKERALIAS,
dbo.TGS_CATALOG_MASTER.BAUMTHEMA,
dbo.TGS_CATALOG_MASTER.BAUMZWEIG1, dbo.TGS_CATALOG_MASTER.MARKER,
dbo.TGS_CATALOG_MASTER.KEYS,
dbo.TGS_CATALOG_MASTER.REVIERID
FROM dbo.TGS_BAUMTHEMEN INNER JOIN
dbo.TGS_CATALOG_MASTER ON dbo.TGS_BAUMTHEMEN.REVIERID
= dbo.TGS_CATALOG_MASTER.REVIERID AND
dbo.TGS_BAUMTHEMEN.BAUMTHEMA =
dbo.TGS_CATALOG_MASTER.BAUMTHEMA INNER JOIN
dbo.TGS_REVIERE ON dbo.TGS_BAUMTHEMEN.REVIERID =
dbo.TGS_REVIERE.REVIERID
I looked in the logfile and saw that there was a drop command of the user
defined function 'NullStringFilter', but no recreationof it before the
create command
was proccessed. Therfore the creation of the view fails.
What can I do to change the order of the schema creation process ?
I want to recreate the user defined functions before the recreation of the
views
starts !!!
Best regards
Axel Lanser
replication uses sysdepends to figure out the ordering of articles.
Sometimes this table can be out of sync (read erroneous).
I tend to deploy my problematic schema using a pre-snapshot command where
all constraints are disabled. Then I set all articles up so that they use
the delete data in existing table in the name conflicts section. I then use
a post-snapshot command to enable the constraints.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:77B1F47E-8B99-4635-868E-009B6BB63C50@.microsoft.com...
> Hi,
> I defined a publisher and a pushsubscriber with some articles. The
articles
> were Tables, Views and user defined functions. The pushsubscriber was
created
> with the sync_type=automatic. When I synchronisize the publisher with the
> subscriber, the merge-agent fails on the follwing view creation command:
> CREATE VIEW [dbo].[TGS_CATALOG]
> AS
> SELECT TOP 100 PERCENT dbo.TGS_BAUMTHEMEN.SORT * 100 +
> dbo.TGS_CATALOG_MASTER.SORT AS LFDNUM,
> dbo.NullStringFilter(dbo.TGS_REVIERE.REVIER) +
> dbo.NullStringFilter(dbo.TGS_BAUMTHEMEN.BAUMTHEMA)
> +
> dbo.NullStringFilter(dbo.TGS_CATALOG_MASTER.BAUMZW EIG1) COLLATE
> Latin1_General_CS_AS AS PFAD,
> dbo.TGS_CATALOG_MASTER.ENTITYNUM,
> dbo.TGS_CATALOG_MASTER.TABLENAME, dbo.TGS_CATALOG_MASTER.SORT,
> dbo.TGS_CATALOG_MASTER.THEMENELEMENT,
> dbo.TGS_CATALOG_MASTER.THEMA, dbo.TGS_CATALOG_MASTER.DECKERALIAS,
> dbo.TGS_CATALOG_MASTER.BAUMTHEMA,
> dbo.TGS_CATALOG_MASTER.BAUMZWEIG1, dbo.TGS_CATALOG_MASTER.MARKER,
> dbo.TGS_CATALOG_MASTER.KEYS,
> dbo.TGS_CATALOG_MASTER.REVIERID
> FROM dbo.TGS_BAUMTHEMEN INNER JOIN
> dbo.TGS_CATALOG_MASTER ON
dbo.TGS_BAUMTHEMEN.REVIERID
> = dbo.TGS_CATALOG_MASTER.REVIERID AND
> dbo.TGS_BAUMTHEMEN.BAUMTHEMA =
> dbo.TGS_CATALOG_MASTER.BAUMTHEMA INNER JOIN
> dbo.TGS_REVIERE ON dbo.TGS_BAUMTHEMEN.REVIERID =
> dbo.TGS_REVIERE.REVIERID
> I looked in the logfile and saw that there was a drop command of the user
> defined function 'NullStringFilter', but no recreationof it before the
> create command
> was proccessed. Therfore the creation of the view fails.
> What can I do to change the order of the schema creation process ?
> I want to recreate the user defined functions before the recreation of the
> views
> starts !!!
> Best regards
> Axel Lanser
|||Hi,
how can I create pre- and post-snapshots ?
Best regards
Axel Lanser
"Hilary Cotter" wrote:
> replication uses sysdepends to figure out the ordering of articles.
> Sometimes this table can be out of sync (read erroneous).
> I tend to deploy my problematic schema using a pre-snapshot command where
> all constraints are disabled. Then I set all articles up so that they use
> the delete data in existing table in the name conflicts section. I then use
> a post-snapshot command to enable the constraints.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ALN" <ALN@.discussions.microsoft.com> wrote in message
> news:77B1F47E-8B99-4635-868E-009B6BB63C50@.microsoft.com...
> articles
> created
> dbo.TGS_BAUMTHEMEN.REVIERID
>
>
|||ALN:
You need to write you own SQL File where you set your custom ALTER TABLE
commands for disabling constraints. While setting up publication you can
specify which SQL file needs to be run before snapshot is applied. Check BOL
for @.pre_snapshot_script parameter in sp_addpublication procedure.
"ALN" wrote:
[vbcol=seagreen]
> Hi,
> how can I create pre- and post-snapshots ?
> Best regards
> Axel Lanser
> "Hilary Cotter" wrote:
|||Hi,
thank you very much for your fast response. It helps me.
Best regards
Axel Lanser
"Mark" wrote:
[vbcol=seagreen]
> ALN:
> You need to write you own SQL File where you set your custom ALTER TABLE
> commands for disabling constraints. While setting up publication you can
> specify which SQL file needs to be run before snapshot is applied. Check BOL
> for @.pre_snapshot_script parameter in sp_addpublication procedure.
>
> "ALN" wrote:
I defined a publisher and a pushsubscriber with some articles. The articles
were Tables, Views and user defined functions. The pushsubscriber was created
with the sync_type=automatic. When I synchronisize the publisher with the
subscriber, the merge-agent fails on the follwing view creation command:
CREATE VIEW [dbo].[TGS_CATALOG]
AS
SELECT TOP 100 PERCENT dbo.TGS_BAUMTHEMEN.SORT * 100 +
dbo.TGS_CATALOG_MASTER.SORT AS LFDNUM,
dbo.NullStringFilter(dbo.TGS_REVIERE.REVIER) +
dbo.NullStringFilter(dbo.TGS_BAUMTHEMEN.BAUMTHEMA)
+
dbo.NullStringFilter(dbo.TGS_CATALOG_MASTER.BAUMZW EIG1) COLLATE
Latin1_General_CS_AS AS PFAD,
dbo.TGS_CATALOG_MASTER.ENTITYNUM,
dbo.TGS_CATALOG_MASTER.TABLENAME, dbo.TGS_CATALOG_MASTER.SORT,
dbo.TGS_CATALOG_MASTER.THEMENELEMENT,
dbo.TGS_CATALOG_MASTER.THEMA, dbo.TGS_CATALOG_MASTER.DECKERALIAS,
dbo.TGS_CATALOG_MASTER.BAUMTHEMA,
dbo.TGS_CATALOG_MASTER.BAUMZWEIG1, dbo.TGS_CATALOG_MASTER.MARKER,
dbo.TGS_CATALOG_MASTER.KEYS,
dbo.TGS_CATALOG_MASTER.REVIERID
FROM dbo.TGS_BAUMTHEMEN INNER JOIN
dbo.TGS_CATALOG_MASTER ON dbo.TGS_BAUMTHEMEN.REVIERID
= dbo.TGS_CATALOG_MASTER.REVIERID AND
dbo.TGS_BAUMTHEMEN.BAUMTHEMA =
dbo.TGS_CATALOG_MASTER.BAUMTHEMA INNER JOIN
dbo.TGS_REVIERE ON dbo.TGS_BAUMTHEMEN.REVIERID =
dbo.TGS_REVIERE.REVIERID
I looked in the logfile and saw that there was a drop command of the user
defined function 'NullStringFilter', but no recreationof it before the
create command
was proccessed. Therfore the creation of the view fails.
What can I do to change the order of the schema creation process ?
I want to recreate the user defined functions before the recreation of the
views
starts !!!
Best regards
Axel Lanser
replication uses sysdepends to figure out the ordering of articles.
Sometimes this table can be out of sync (read erroneous).
I tend to deploy my problematic schema using a pre-snapshot command where
all constraints are disabled. Then I set all articles up so that they use
the delete data in existing table in the name conflicts section. I then use
a post-snapshot command to enable the constraints.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"ALN" <ALN@.discussions.microsoft.com> wrote in message
news:77B1F47E-8B99-4635-868E-009B6BB63C50@.microsoft.com...
> Hi,
> I defined a publisher and a pushsubscriber with some articles. The
articles
> were Tables, Views and user defined functions. The pushsubscriber was
created
> with the sync_type=automatic. When I synchronisize the publisher with the
> subscriber, the merge-agent fails on the follwing view creation command:
> CREATE VIEW [dbo].[TGS_CATALOG]
> AS
> SELECT TOP 100 PERCENT dbo.TGS_BAUMTHEMEN.SORT * 100 +
> dbo.TGS_CATALOG_MASTER.SORT AS LFDNUM,
> dbo.NullStringFilter(dbo.TGS_REVIERE.REVIER) +
> dbo.NullStringFilter(dbo.TGS_BAUMTHEMEN.BAUMTHEMA)
> +
> dbo.NullStringFilter(dbo.TGS_CATALOG_MASTER.BAUMZW EIG1) COLLATE
> Latin1_General_CS_AS AS PFAD,
> dbo.TGS_CATALOG_MASTER.ENTITYNUM,
> dbo.TGS_CATALOG_MASTER.TABLENAME, dbo.TGS_CATALOG_MASTER.SORT,
> dbo.TGS_CATALOG_MASTER.THEMENELEMENT,
> dbo.TGS_CATALOG_MASTER.THEMA, dbo.TGS_CATALOG_MASTER.DECKERALIAS,
> dbo.TGS_CATALOG_MASTER.BAUMTHEMA,
> dbo.TGS_CATALOG_MASTER.BAUMZWEIG1, dbo.TGS_CATALOG_MASTER.MARKER,
> dbo.TGS_CATALOG_MASTER.KEYS,
> dbo.TGS_CATALOG_MASTER.REVIERID
> FROM dbo.TGS_BAUMTHEMEN INNER JOIN
> dbo.TGS_CATALOG_MASTER ON
dbo.TGS_BAUMTHEMEN.REVIERID
> = dbo.TGS_CATALOG_MASTER.REVIERID AND
> dbo.TGS_BAUMTHEMEN.BAUMTHEMA =
> dbo.TGS_CATALOG_MASTER.BAUMTHEMA INNER JOIN
> dbo.TGS_REVIERE ON dbo.TGS_BAUMTHEMEN.REVIERID =
> dbo.TGS_REVIERE.REVIERID
> I looked in the logfile and saw that there was a drop command of the user
> defined function 'NullStringFilter', but no recreationof it before the
> create command
> was proccessed. Therfore the creation of the view fails.
> What can I do to change the order of the schema creation process ?
> I want to recreate the user defined functions before the recreation of the
> views
> starts !!!
> Best regards
> Axel Lanser
|||Hi,
how can I create pre- and post-snapshots ?
Best regards
Axel Lanser
"Hilary Cotter" wrote:
> replication uses sysdepends to figure out the ordering of articles.
> Sometimes this table can be out of sync (read erroneous).
> I tend to deploy my problematic schema using a pre-snapshot command where
> all constraints are disabled. Then I set all articles up so that they use
> the delete data in existing table in the name conflicts section. I then use
> a post-snapshot command to enable the constraints.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "ALN" <ALN@.discussions.microsoft.com> wrote in message
> news:77B1F47E-8B99-4635-868E-009B6BB63C50@.microsoft.com...
> articles
> created
> dbo.TGS_BAUMTHEMEN.REVIERID
>
>
|||ALN:
You need to write you own SQL File where you set your custom ALTER TABLE
commands for disabling constraints. While setting up publication you can
specify which SQL file needs to be run before snapshot is applied. Check BOL
for @.pre_snapshot_script parameter in sp_addpublication procedure.
"ALN" wrote:
[vbcol=seagreen]
> Hi,
> how can I create pre- and post-snapshots ?
> Best regards
> Axel Lanser
> "Hilary Cotter" wrote:
|||Hi,
thank you very much for your fast response. It helps me.
Best regards
Axel Lanser
"Mark" wrote:
[vbcol=seagreen]
> ALN:
> You need to write you own SQL File where you set your custom ALTER TABLE
> commands for disabling constraints. While setting up publication you can
> specify which SQL file needs to be run before snapshot is applied. Check BOL
> for @.pre_snapshot_script parameter in sp_addpublication procedure.
>
> "ALN" wrote:
problem with updateing foreign key to null value
Hi,
I was wondering if anyone out there can help me with the following problem:
I've written an annotated XSD for retrieving some related tables from
sql-server.
It's used in an XPATH Query that populates a strongly typed dataset
(generated using xsd.exe).
So far everyting works like a dream!
There is however one nagging problem i've encounterd so far. I'm trying to
"deassign" a child from it's parent by updating it's foreign key field to
null (by using the setnull method generated by xsd.exe).
The dataset records this but when I use it to try to update the database, no
update statements are executed and no errors are raised.
Updated the child with a different parent key does however work!!!
So it would seem SQLXML doesn't detect a change when updating to null?!
Any help would be greatly appreciated.
Regards, Rob HuibersHi Rob,
It is not clear which SQLXML API you are using? Are you using
SqlXmlAdapter and its Update method? Is it possible to post your schema so
that I can try to repro the issue here.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> Hi,
> I was wondering if anyone out there can help me with the following
problem:
> I've written an annotated XSD for retrieving some related tables from
> sql-server.
> It's used in an XPATH Query that populates a strongly typed dataset
> (generated using xsd.exe).
> So far everyting works like a dream!
> There is however one nagging problem i've encounterd so far. I'm trying to
> "deassign" a child from it's parent by updating it's foreign key field to
> null (by using the setnull method generated by xsd.exe).
> The dataset records this but when I use it to try to update the database,
no
> update statements are executed and no errors are raised.
> Updated the child with a different parent key does however work!!!
> So it would seem SQLXML doesn't detect a change when updating to null?!
> Any help would be greatly appreciated.
> Regards, Rob Huibers|||Hi Amar,
Thanks for looking into the problem.
You're right in that I'm using SqlXmlAdapter.Update (SQLXML 3.0 sp3).
I've included the schema. The element I'm trying to nullify is
"sub_claim_id" belonging to parent "xyz_assigned_part_claim".
As I've mentioned before, the dataset record records the change but does not
serialize it to the database. When I generate an XmlDataDocument from the
dataset the sub_claim_id" element does not exist anymore. I would have
expected the element appearing with the "xsi:nil" attribute.
Hope you can figure it out, regards, Rob
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ClaimSubclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_sub_claim"
child-key="claim_id" />
<sql:relationship name="ClaimSubclaimPartclaims" parent="xyz_sub_claim"
parent-key="sub_claim_id"
child="xyz_part_claim" child-key="sub_claim_id" />
<sql:relationship name="ClaimPartclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_part_claim"
child-key="claim_id" />
<sql:relationship name="ClaimTasks" parent="xyz_claim"
parent-key="claim_id" child="xyz_task"
child-key="claim_id" />
<sql:relationship name="SubclaimTasks" parent="xyz_sub_claim"
parent-key="sub_claim_id" child="xyz_task"
child-key="sub_claim_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:element name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:element name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
nillable="true" minOccurs="1" />
<xsd:element name="description" sql:field="description" type="xsd:string"
/>
</xsd:sequence>
</xsd:complexType>
<xsd:attributeGroup name="xyz_part_claim_investigation_type">
<xsd:attribute name="part_claim_investigation_id"
sql:field="part_claim_investigation_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" />
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_investigation_type">
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_task_type">
<xsd:attribute name="task_id" sql:field="task_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="type" sql:field="type" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
<xsd:attribute name="creator" sql:field="creator" type="xsd:string" />
<xsd:attribute name="performer" sql:field="performer" type="xsd:string" />
<xsd:attribute name="status" sql:field="status" type="xsd:int" />
<xsd:attribute name="date_started" sql:field="date_started"
type="xsd:dateTime" />
<xsd:attribute name="date_closed" sql:field="date_closed"
type="xsd:dateTime" />
<xsd:attribute name="date_created" sql:field="date_created"
type="xsd:dateTime" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
/>
</xsd:attributeGroup>
<xsd:element name="xyz_claims" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim" sql:key-fields="claim_id"
sql:relation="xyz_claim">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task" sql:relationship="ClaimTasks"
sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_sub_claim" sql:key-fields="sub_claim_id"
sql:relation="xyz_sub_claim"
sql:relationship="ClaimSubclaims">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_sub_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task"
sql:relationship="SubclaimTasks">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_assigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimSubclaimPartclaims">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_unassigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimPartclaims" sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_unassigned_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
"Amar Nalla [MS]" wrote:
> Hi Rob,
> It is not clear which SQLXML API you are using? Are you using
> SqlXmlAdapter and its Update method? Is it possible to post your schema so
> that I can try to repro the issue here.
> Thank you,
> Amar Nalla
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
> news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> problem:
> no
>
>
I was wondering if anyone out there can help me with the following problem:
I've written an annotated XSD for retrieving some related tables from
sql-server.
It's used in an XPATH Query that populates a strongly typed dataset
(generated using xsd.exe).
So far everyting works like a dream!
There is however one nagging problem i've encounterd so far. I'm trying to
"deassign" a child from it's parent by updating it's foreign key field to
null (by using the setnull method generated by xsd.exe).
The dataset records this but when I use it to try to update the database, no
update statements are executed and no errors are raised.
Updated the child with a different parent key does however work!!!
So it would seem SQLXML doesn't detect a change when updating to null?!
Any help would be greatly appreciated.
Regards, Rob HuibersHi Rob,
It is not clear which SQLXML API you are using? Are you using
SqlXmlAdapter and its Update method? Is it possible to post your schema so
that I can try to repro the issue here.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> Hi,
> I was wondering if anyone out there can help me with the following
problem:
> I've written an annotated XSD for retrieving some related tables from
> sql-server.
> It's used in an XPATH Query that populates a strongly typed dataset
> (generated using xsd.exe).
> So far everyting works like a dream!
> There is however one nagging problem i've encounterd so far. I'm trying to
> "deassign" a child from it's parent by updating it's foreign key field to
> null (by using the setnull method generated by xsd.exe).
> The dataset records this but when I use it to try to update the database,
no
> update statements are executed and no errors are raised.
> Updated the child with a different parent key does however work!!!
> So it would seem SQLXML doesn't detect a change when updating to null?!
> Any help would be greatly appreciated.
> Regards, Rob Huibers|||Hi Amar,
Thanks for looking into the problem.
You're right in that I'm using SqlXmlAdapter.Update (SQLXML 3.0 sp3).
I've included the schema. The element I'm trying to nullify is
"sub_claim_id" belonging to parent "xyz_assigned_part_claim".
As I've mentioned before, the dataset record records the change but does not
serialize it to the database. When I generate an XmlDataDocument from the
dataset the sub_claim_id" element does not exist anymore. I would have
expected the element appearing with the "xsi:nil" attribute.
Hope you can figure it out, regards, Rob
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ClaimSubclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_sub_claim"
child-key="claim_id" />
<sql:relationship name="ClaimSubclaimPartclaims" parent="xyz_sub_claim"
parent-key="sub_claim_id"
child="xyz_part_claim" child-key="sub_claim_id" />
<sql:relationship name="ClaimPartclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_part_claim"
child-key="claim_id" />
<sql:relationship name="ClaimTasks" parent="xyz_claim"
parent-key="claim_id" child="xyz_task"
child-key="claim_id" />
<sql:relationship name="SubclaimTasks" parent="xyz_sub_claim"
parent-key="sub_claim_id" child="xyz_task"
child-key="sub_claim_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:element name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:element name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
nillable="true" minOccurs="1" />
<xsd:element name="description" sql:field="description" type="xsd:string"
/>
</xsd:sequence>
</xsd:complexType>
<xsd:attributeGroup name="xyz_part_claim_investigation_type">
<xsd:attribute name="part_claim_investigation_id"
sql:field="part_claim_investigation_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" />
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_investigation_type">
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_task_type">
<xsd:attribute name="task_id" sql:field="task_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="type" sql:field="type" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
<xsd:attribute name="creator" sql:field="creator" type="xsd:string" />
<xsd:attribute name="performer" sql:field="performer" type="xsd:string" />
<xsd:attribute name="status" sql:field="status" type="xsd:int" />
<xsd:attribute name="date_started" sql:field="date_started"
type="xsd:dateTime" />
<xsd:attribute name="date_closed" sql:field="date_closed"
type="xsd:dateTime" />
<xsd:attribute name="date_created" sql:field="date_created"
type="xsd:dateTime" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
/>
</xsd:attributeGroup>
<xsd:element name="xyz_claims" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim" sql:key-fields="claim_id"
sql:relation="xyz_claim">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task" sql:relationship="ClaimTasks"
sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_sub_claim" sql:key-fields="sub_claim_id"
sql:relation="xyz_sub_claim"
sql:relationship="ClaimSubclaims">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_sub_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task"
sql:relationship="SubclaimTasks">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_assigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimSubclaimPartclaims">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_unassigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimPartclaims" sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_unassigned_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
"Amar Nalla [MS]" wrote:
> Hi Rob,
> It is not clear which SQLXML API you are using? Are you using
> SqlXmlAdapter and its Update method? Is it possible to post your schema so
> that I can try to repro the issue here.
> Thank you,
> Amar Nalla
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
> news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> problem:
> no
>
>
problem with updateing foreign key to null value
Hi,
I was wondering if anyone out there can help me with the following problem:
I've written an annotated XSD for retrieving some related tables from
sql-server.
It's used in an XPATH Query that populates a strongly typed dataset
(generated using xsd.exe).
So far everyting works like a dream!
There is however one nagging problem i've encounterd so far. I'm trying to
"deassign" a child from it's parent by updating it's foreign key field to
null (by using the setnull method generated by xsd.exe).
The dataset records this but when I use it to try to update the database, no
update statements are executed and no errors are raised.
Updated the child with a different parent key does however work!!!
So it would seem SQLXML doesn't detect a change when updating to null?!
Any help would be greatly appreciated.
Regards, Rob Huibers
Hi Rob,
It is not clear which SQLXML API you are using? Are you using
SqlXmlAdapter and its Update method? Is it possible to post your schema so
that I can try to repro the issue here.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> Hi,
> I was wondering if anyone out there can help me with the following
problem:
> I've written an annotated XSD for retrieving some related tables from
> sql-server.
> It's used in an XPATH Query that populates a strongly typed dataset
> (generated using xsd.exe).
> So far everyting works like a dream!
> There is however one nagging problem i've encounterd so far. I'm trying to
> "deassign" a child from it's parent by updating it's foreign key field to
> null (by using the setnull method generated by xsd.exe).
> The dataset records this but when I use it to try to update the database,
no
> update statements are executed and no errors are raised.
> Updated the child with a different parent key does however work!!!
> So it would seem SQLXML doesn't detect a change when updating to null?!
> Any help would be greatly appreciated.
> Regards, Rob Huibers
|||Hi Amar,
Thanks for looking into the problem.
You're right in that I'm using SqlXmlAdapter.Update (SQLXML 3.0 sp3).
I've included the schema. The element I'm trying to nullify is
"sub_claim_id" belonging to parent "xyz_assigned_part_claim".
As I've mentioned before, the dataset record records the change but does not
serialize it to the database. When I generate an XmlDataDocument from the
dataset the sub_claim_id" element does not exist anymore. I would have
expected the element appearing with the "xsi:nil" attribute.
Hope you can figure it out, regards, Rob
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ClaimSubclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_sub_claim"
child-key="claim_id" />
<sql:relationship name="ClaimSubclaimPartclaims" parent="xyz_sub_claim"
parent-key="sub_claim_id"
child="xyz_part_claim" child-key="sub_claim_id" />
<sql:relationship name="ClaimPartclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_part_claim"
child-key="claim_id" />
<sql:relationship name="ClaimTasks" parent="xyz_claim"
parent-key="claim_id" child="xyz_task"
child-key="claim_id" />
<sql:relationship name="SubclaimTasks" parent="xyz_sub_claim"
parent-key="sub_claim_id" child="xyz_task"
child-key="sub_claim_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:element name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:element name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
nillable="true" minOccurs="1" />
<xsd:element name="description" sql:field="description" type="xsd:string"
/>
</xsd:sequence>
</xsd:complexType>
<xsd:attributeGroup name="xyz_part_claim_investigation_type">
<xsd:attribute name="part_claim_investigation_id"
sql:field="part_claim_investigation_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" />
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_investigation_type">
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_task_type">
<xsd:attribute name="task_id" sql:field="task_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="type" sql:field="type" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
<xsd:attribute name="creator" sql:field="creator" type="xsd:string" />
<xsd:attribute name="performer" sql:field="performer" type="xsd:string" />
<xsd:attribute name="status" sql:field="status" type="xsd:int" />
<xsd:attribute name="date_started" sql:field="date_started"
type="xsd:dateTime" />
<xsd:attribute name="date_closed" sql:field="date_closed"
type="xsd:dateTime" />
<xsd:attribute name="date_created" sql:field="date_created"
type="xsd:dateTime" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
/>
</xsd:attributeGroup>
<xsd:element name="xyz_claims" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim" sql:key-fields="claim_id"
sql:relation="xyz_claim">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task" sql:relationship="ClaimTasks"
sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_sub_claim" sql:key-fields="sub_claim_id"
sql:relation="xyz_sub_claim"
sql:relationship="ClaimSubclaims">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_sub_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task"
sql:relationship="SubclaimTasks">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_assigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimSubclaimPartclaims">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_unassigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimPartclaims" sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_unassigned_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
"Amar Nalla [MS]" wrote:
> Hi Rob,
> It is not clear which SQLXML API you are using? Are you using
> SqlXmlAdapter and its Update method? Is it possible to post your schema so
> that I can try to repro the issue here.
> Thank you,
> Amar Nalla
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
> news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> problem:
> no
>
>
I was wondering if anyone out there can help me with the following problem:
I've written an annotated XSD for retrieving some related tables from
sql-server.
It's used in an XPATH Query that populates a strongly typed dataset
(generated using xsd.exe).
So far everyting works like a dream!
There is however one nagging problem i've encounterd so far. I'm trying to
"deassign" a child from it's parent by updating it's foreign key field to
null (by using the setnull method generated by xsd.exe).
The dataset records this but when I use it to try to update the database, no
update statements are executed and no errors are raised.
Updated the child with a different parent key does however work!!!
So it would seem SQLXML doesn't detect a change when updating to null?!
Any help would be greatly appreciated.
Regards, Rob Huibers
Hi Rob,
It is not clear which SQLXML API you are using? Are you using
SqlXmlAdapter and its Update method? Is it possible to post your schema so
that I can try to repro the issue here.
Thank you,
Amar Nalla
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> Hi,
> I was wondering if anyone out there can help me with the following
problem:
> I've written an annotated XSD for retrieving some related tables from
> sql-server.
> It's used in an XPATH Query that populates a strongly typed dataset
> (generated using xsd.exe).
> So far everyting works like a dream!
> There is however one nagging problem i've encounterd so far. I'm trying to
> "deassign" a child from it's parent by updating it's foreign key field to
> null (by using the setnull method generated by xsd.exe).
> The dataset records this but when I use it to try to update the database,
no
> update statements are executed and no errors are raised.
> Updated the child with a different parent key does however work!!!
> So it would seem SQLXML doesn't detect a change when updating to null?!
> Any help would be greatly appreciated.
> Regards, Rob Huibers
|||Hi Amar,
Thanks for looking into the problem.
You're right in that I'm using SqlXmlAdapter.Update (SQLXML 3.0 sp3).
I've included the schema. The element I'm trying to nullify is
"sub_claim_id" belonging to parent "xyz_assigned_part_claim".
As I've mentioned before, the dataset record records the change but does not
serialize it to the database. When I generate an XmlDataDocument from the
dataset the sub_claim_id" element does not exist anymore. I would have
expected the element appearing with the "xsi:nil" attribute.
Hope you can figure it out, regards, Rob
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema"
xmlns:codegen="urn:schemas-microsoft-com:xml-msprop">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ClaimSubclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_sub_claim"
child-key="claim_id" />
<sql:relationship name="ClaimSubclaimPartclaims" parent="xyz_sub_claim"
parent-key="sub_claim_id"
child="xyz_part_claim" child-key="sub_claim_id" />
<sql:relationship name="ClaimPartclaims" parent="xyz_claim"
parent-key="claim_id" child="xyz_part_claim"
child-key="claim_id" />
<sql:relationship name="ClaimTasks" parent="xyz_claim"
parent-key="claim_id" child="xyz_task"
child-key="claim_id" />
<sql:relationship name="SubclaimTasks" parent="xyz_sub_claim"
parent-key="sub_claim_id" child="xyz_task"
child-key="sub_claim_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType name="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:element name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:element name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
nillable="true" minOccurs="1" />
<xsd:element name="description" sql:field="description" type="xsd:string"
/>
</xsd:sequence>
</xsd:complexType>
<xsd:attributeGroup name="xyz_part_claim_investigation_type">
<xsd:attribute name="part_claim_investigation_id"
sql:field="part_claim_investigation_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="part_claim_id" sql:field="part_claim_id"
type="xsd:int" />
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_investigation_type">
<xsd:attribute name="investigation_id" sql:field="investigation_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:attributeGroup>
<xsd:attributeGroup name="xyz_task_type">
<xsd:attribute name="task_id" sql:field="task_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="type" sql:field="type" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
<xsd:attribute name="creator" sql:field="creator" type="xsd:string" />
<xsd:attribute name="performer" sql:field="performer" type="xsd:string" />
<xsd:attribute name="status" sql:field="status" type="xsd:int" />
<xsd:attribute name="date_started" sql:field="date_started"
type="xsd:dateTime" />
<xsd:attribute name="date_closed" sql:field="date_closed"
type="xsd:dateTime" />
<xsd:attribute name="date_created" sql:field="date_created"
type="xsd:dateTime" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id" type="xsd:int"
/>
</xsd:attributeGroup>
<xsd:element name="xyz_claims" sql:is-constant="true">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim" sql:key-fields="claim_id"
sql:relation="xyz_claim">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task" sql:relationship="ClaimTasks"
sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_sub_claim" sql:key-fields="sub_claim_id"
sql:relation="xyz_sub_claim"
sql:relationship="ClaimSubclaims">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="xyz_sub_claim_task" sql:key-fields="task_id"
sql:relation="xyz_task"
sql:relationship="SubclaimTasks">
<xsd:complexType>
<xsd:attributeGroup ref="xyz_task_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_assigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimSubclaimPartclaims">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="sub_claim_id" sql:field="sub_claim_id"
type="xsd:int" sql:identity="ignore" />
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
<xsd:element name="xyz_unassigned_part_claim"
sql:key-fields="part_claim_id" sql:relation="xyz_part_claim"
sql:relationship="ClaimPartclaims" sql:limit-field="sub_claim_id">
<xsd:complexType>
<xsd:complexContent>
<xsd:extension base="xyz_part_claim_type">
<xsd:sequence>
<xsd:element name="xyz_unassigned_investigation"
sql:key-fields="investigation_id" sql:relation="xyz_investigation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="xyz_part_claim"
parent-key="part_claim_id" child="xyz_part_claim_investigation"
child-key="part_claim_id" />
<sql:relationship parent="xyz_part_claim_investigation"
parent-key="investigation_id" child="xyz_investigation"
child-key="investigation_id" />
</xsd:appinfo>
</xsd:annotation>
<xsd:complexType>
<xsd:attributeGroup
ref="xyz_investigation_type"></xsd:attributeGroup>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:extension>
</xsd:complexContent>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="claim_id" sql:field="claim_id" type="xsd:int"
sql:identity="ignore" />
<xsd:attribute name="description" sql:field="description"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
"Amar Nalla [MS]" wrote:
> Hi Rob,
> It is not clear which SQLXML API you are using? Are you using
> SqlXmlAdapter and its Update method? Is it possible to post your schema so
> that I can try to repro the issue here.
> Thank you,
> Amar Nalla
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Rob Huibers" <Rob Huibers@.discussions.microsoft.com> wrote in message
> news:E482DAFF-76A7-4A88-993C-A75DB20C5954@.microsoft.com...
> problem:
> no
>
>
Problem with update tables on linked Sybase server
While working on MsSQL 2000 I'm trying to update table on remote server
(Sybase)
Here is the description of remote table (Sybase)
IF OBJECT_ID('adm.TransClient') IS NOT NULL
drop table adm.TransClient
go
CREATE TABLE adm.TransClient
(
CUST_ID numeric(10,0) NOT NULL,
NAME varchar(254) NOT NULL,
ADR1 varchar(254) NOT NULL,
ADR2 varchar(254) NOT NULL,
CREDIT numeric(17,9) NOT NULL,
SALES varchar(30) NOT NULL,
INN varchar(30) NULL,
TYPEC smallint NOT NULL,
NO_NDS smallint NOT NULL,
NUM_DOG varchar(20) NOT NULL,
LOGIN varchar(16) NOT NULL,
BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
TransType varchar(20) DEFAULT "insert" NOT NULL,
TransDate datetime DEFAULT getdate() NOT NULL,
Transferred smallint DEFAULT 0 NOT NULL,
CONSTRAINT PK1
PRIMARY KEY NONCLUSTERED (CUST_ID)
)
LOCK datarows
go
1. Doing like this
update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1
MsSQL returns:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Query-based update failed because the row
to update could not be found.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
returned 0x80004005: ].
2. Second atempt
update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7
MsSQL returns:
1 row affected
3. Final shoot - I wrote simpliest cursor that goes throw rows and sets
Transferred=1
Althogh MsSQL has returned:
Remote tables are not updatable. Updatable keyset-driven cursors on remote
tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
isolation level spanning the cursor.
After that
select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=1 = 202 !!!!!!!
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=0 = 48 !!!!!!!!
No comments from my side - simply do not understand what is going on
Any answers are appreciated
Hi
0x80004005 = Access Denied. Check that you have permissions on the
destination server for what you are doing.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> While working on MsSQL 2000 I'm trying to update table on remote server
> (Sybase)
> Here is the description of remote table (Sybase)
> IF OBJECT_ID('adm.TransClient') IS NOT NULL
> drop table adm.TransClient
> go
> CREATE TABLE adm.TransClient
> (
> CUST_ID numeric(10,0) NOT NULL,
> NAME varchar(254) NOT NULL,
> ADR1 varchar(254) NOT NULL,
> ADR2 varchar(254) NOT NULL,
> CREDIT numeric(17,9) NOT NULL,
> SALES varchar(30) NOT NULL,
> INN varchar(30) NULL,
> TYPEC smallint NOT NULL,
> NO_NDS smallint NOT NULL,
> NUM_DOG varchar(20) NOT NULL,
> LOGIN varchar(16) NOT NULL,
> BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
> TransType varchar(20) DEFAULT "insert" NOT NULL,
> TransDate datetime DEFAULT getdate() NOT NULL,
> Transferred smallint DEFAULT 0 NOT NULL,
> CONSTRAINT PK1
> PRIMARY KEY NONCLUSTERED (CUST_ID)
> )
> LOCK datarows
> go
> 1. Doing like this
> update BILLING_ASE12.billing.adm.TransClient
> set [Transferred]=1
> MsSQL returns:
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: Query-based update failed because the
row
> to update could not be found.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
> returned 0x80004005: ].
> 2. Second atempt
> update BILLING_ASE12.billing.adm.TransClient
> set [Transferred]=1 where CUST_ID=7
> MsSQL returns:
> 1 row affected
>
> 3. Final shoot - I wrote simpliest cursor that goes throw rows and sets
> Transferred=1
> Althogh MsSQL has returned:
> --
> Remote tables are not updatable. Updatable keyset-driven cursors on remote
> tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
> isolation level spanning the cursor.
> --
> After that
> select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=1 = 202 !!!!!!!
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=0 = 48 !!!!!!!!
> No comments from my side - simply do not understand what is going on
> Any answers are appreciated
|||Surely I have access to this server
Otherwise I couldn't do
update no.2
(update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7)
that completed successfully
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 0x80004005 = Access Denied. Check that you have permissions on the
> destination server for what you are doing.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
> news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> row
>
>
(Sybase)
Here is the description of remote table (Sybase)
IF OBJECT_ID('adm.TransClient') IS NOT NULL
drop table adm.TransClient
go
CREATE TABLE adm.TransClient
(
CUST_ID numeric(10,0) NOT NULL,
NAME varchar(254) NOT NULL,
ADR1 varchar(254) NOT NULL,
ADR2 varchar(254) NOT NULL,
CREDIT numeric(17,9) NOT NULL,
SALES varchar(30) NOT NULL,
INN varchar(30) NULL,
TYPEC smallint NOT NULL,
NO_NDS smallint NOT NULL,
NUM_DOG varchar(20) NOT NULL,
LOGIN varchar(16) NOT NULL,
BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
TransType varchar(20) DEFAULT "insert" NOT NULL,
TransDate datetime DEFAULT getdate() NOT NULL,
Transferred smallint DEFAULT 0 NOT NULL,
CONSTRAINT PK1
PRIMARY KEY NONCLUSTERED (CUST_ID)
)
LOCK datarows
go
1. Doing like this
update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1
MsSQL returns:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Query-based update failed because the row
to update could not be found.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
returned 0x80004005: ].
2. Second atempt
update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7
MsSQL returns:
1 row affected
3. Final shoot - I wrote simpliest cursor that goes throw rows and sets
Transferred=1
Althogh MsSQL has returned:
Remote tables are not updatable. Updatable keyset-driven cursors on remote
tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
isolation level spanning the cursor.
After that
select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=1 = 202 !!!!!!!
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=0 = 48 !!!!!!!!
No comments from my side - simply do not understand what is going on
Any answers are appreciated
Hi
0x80004005 = Access Denied. Check that you have permissions on the
destination server for what you are doing.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> While working on MsSQL 2000 I'm trying to update table on remote server
> (Sybase)
> Here is the description of remote table (Sybase)
> IF OBJECT_ID('adm.TransClient') IS NOT NULL
> drop table adm.TransClient
> go
> CREATE TABLE adm.TransClient
> (
> CUST_ID numeric(10,0) NOT NULL,
> NAME varchar(254) NOT NULL,
> ADR1 varchar(254) NOT NULL,
> ADR2 varchar(254) NOT NULL,
> CREDIT numeric(17,9) NOT NULL,
> SALES varchar(30) NOT NULL,
> INN varchar(30) NULL,
> TYPEC smallint NOT NULL,
> NO_NDS smallint NOT NULL,
> NUM_DOG varchar(20) NOT NULL,
> LOGIN varchar(16) NOT NULL,
> BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
> TransType varchar(20) DEFAULT "insert" NOT NULL,
> TransDate datetime DEFAULT getdate() NOT NULL,
> Transferred smallint DEFAULT 0 NOT NULL,
> CONSTRAINT PK1
> PRIMARY KEY NONCLUSTERED (CUST_ID)
> )
> LOCK datarows
> go
> 1. Doing like this
> update BILLING_ASE12.billing.adm.TransClient
> set [Transferred]=1
> MsSQL returns:
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: Query-based update failed because the
row
> to update could not be found.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
> returned 0x80004005: ].
> 2. Second atempt
> update BILLING_ASE12.billing.adm.TransClient
> set [Transferred]=1 where CUST_ID=7
> MsSQL returns:
> 1 row affected
>
> 3. Final shoot - I wrote simpliest cursor that goes throw rows and sets
> Transferred=1
> Althogh MsSQL has returned:
> --
> Remote tables are not updatable. Updatable keyset-driven cursors on remote
> tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
> isolation level spanning the cursor.
> --
> After that
> select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=1 = 202 !!!!!!!
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=0 = 48 !!!!!!!!
> No comments from my side - simply do not understand what is going on
> Any answers are appreciated
|||Surely I have access to this server
Otherwise I couldn't do
update no.2
(update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7)
that completed successfully
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 0x80004005 = Access Denied. Check that you have permissions on the
> destination server for what you are doing.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
> news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> row
>
>
Problem with update tables on linked Sybase server
While working on MsSQL 2000 I'm trying to update table on remote server
(Sybase)
Here is the description of remote table (Sybase)
IF OBJECT_ID('adm.TransClient') IS NOT NULL
drop table adm.TransClient
go
CREATE TABLE adm.TransClient
(
CUST_ID numeric(10,0) NOT NULL,
NAME varchar(254) NOT NULL,
ADR1 varchar(254) NOT NULL,
ADR2 varchar(254) NOT NULL,
CREDIT numeric(17,9) NOT NULL,
SALES varchar(30) NOT NULL,
INN varchar(30) NULL,
TYPEC smallint NOT NULL,
NO_NDS smallint NOT NULL,
NUM_DOG varchar(20) NOT NULL,
LOGIN varchar(16) NOT NULL,
BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
TransType varchar(20) DEFAULT "insert" NOT NULL,
TransDate datetime DEFAULT getdate() NOT NULL,
Transferred smallint DEFAULT 0 NOT NULL,
CONSTRAINT PK1
PRIMARY KEY NONCLUSTERED (CUST_ID)
)
LOCK datarows
go
1. Doing like this
update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1
MsSQL returns:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Query-based update failed because the
row
to update could not be found.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
returned 0x80004005: ].
2. Second atempt
update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7
MsSQL returns:
1 row affected
3. Final shoot - I wrote simpliest cursor that goes throw rows and sets
Transferred=1
Althogh MsSQL has returned:
--
Remote tables are not updatable. Updatable keyset-driven cursors on remote
tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
isolation level spanning the cursor.
--
After that
select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=1 = 202 !!!!!!!
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=0 = 48 !!!!!!!!
No comments from my side - simply do not understand what is going on
Any answers are appreciatedHi
0x80004005 = Access Denied. Check that you have permissions on the
destination server for what you are doing.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> While working on MsSQL 2000 I'm trying to update table on remote server
> (Sybase)
> Here is the description of remote table (Sybase)
> IF OBJECT_ID('adm.TransClient') IS NOT NULL
> drop table adm.TransClient
> go
> CREATE TABLE adm.TransClient
> (
> CUST_ID numeric(10,0) NOT NULL,
> NAME varchar(254) NOT NULL,
> ADR1 varchar(254) NOT NULL,
> ADR2 varchar(254) NOT NULL,
> CREDIT numeric(17,9) NOT NULL,
> SALES varchar(30) NOT NULL,
> INN varchar(30) NULL,
> TYPEC smallint NOT NULL,
> NO_NDS smallint NOT NULL,
> NUM_DOG varchar(20) NOT NULL,
> LOGIN varchar(16) NOT NULL,
> BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
> TransType varchar(20) DEFAULT "insert" NOT NULL,
> TransDate datetime DEFAULT getdate() NOT NULL,
> Transferred smallint DEFAULT 0 NOT NULL,
> CONSTRAINT PK1
> PRIMARY KEY NONCLUSTERED (CUST_ID)
> )
> LOCK datarows
> go
> 1. Doing like this
> update BILLING_ASE12.billing.adm.TransClient
> set [Transferred]=1
> MsSQL returns:
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: Query-based update failed because the[/vbco
l]
row[vbcol=seagreen]
> to update could not be found.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
> returned 0x80004005: ].
> 2. Second atempt
> update BILLING_ASE12.billing.adm.TransClient
> set [Transferred]=1 where CUST_ID=7
> MsSQL returns:
> 1 row affected
>
> 3. Final shoot - I wrote simpliest cursor that goes throw rows and sets
> Transferred=1
> Althogh MsSQL has returned:
> --
> Remote tables are not updatable. Updatable keyset-driven cursors on remote
> tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
> isolation level spanning the cursor.
> --
> After that
> select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=1 = 202 !!!!!!!
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=0 = 48 !!!!!!!!
> No comments from my side - simply do not understand what is going on
> Any answers are appreciated|||Surely I have access to this server
Otherwise I couldn't do
update no.2
(update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7)
that completed successfully
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 0x80004005 = Access Denied. Check that you have permissions on the
> destination server for what you are doing.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
> news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> row
>
>
(Sybase)
Here is the description of remote table (Sybase)
IF OBJECT_ID('adm.TransClient') IS NOT NULL
drop table adm.TransClient
go
CREATE TABLE adm.TransClient
(
CUST_ID numeric(10,0) NOT NULL,
NAME varchar(254) NOT NULL,
ADR1 varchar(254) NOT NULL,
ADR2 varchar(254) NOT NULL,
CREDIT numeric(17,9) NOT NULL,
SALES varchar(30) NOT NULL,
INN varchar(30) NULL,
TYPEC smallint NOT NULL,
NO_NDS smallint NOT NULL,
NUM_DOG varchar(20) NOT NULL,
LOGIN varchar(16) NOT NULL,
BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
TransType varchar(20) DEFAULT "insert" NOT NULL,
TransDate datetime DEFAULT getdate() NOT NULL,
Transferred smallint DEFAULT 0 NOT NULL,
CONSTRAINT PK1
PRIMARY KEY NONCLUSTERED (CUST_ID)
)
LOCK datarows
go
1. Doing like this
update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1
MsSQL returns:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Query-based update failed because the
row
to update could not be found.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
returned 0x80004005: ].
2. Second atempt
update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7
MsSQL returns:
1 row affected
3. Final shoot - I wrote simpliest cursor that goes throw rows and sets
Transferred=1
Althogh MsSQL has returned:
--
Remote tables are not updatable. Updatable keyset-driven cursors on remote
tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
isolation level spanning the cursor.
--
After that
select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=1 = 202 !!!!!!!
select count(*) from BILLING_ASE12.billing.adm.TransClient where
Transferred=0 = 48 !!!!!!!!
No comments from my side - simply do not understand what is going on
Any answers are appreciatedHi
0x80004005 = Access Denied. Check that you have permissions on the
destination server for what you are doing.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> While working on MsSQL 2000 I'm trying to update table on remote server
> (Sybase)
> Here is the description of remote table (Sybase)
> IF OBJECT_ID('adm.TransClient') IS NOT NULL
> drop table adm.TransClient
> go
> CREATE TABLE adm.TransClient
> (
> CUST_ID numeric(10,0) NOT NULL,
> NAME varchar(254) NOT NULL,
> ADR1 varchar(254) NOT NULL,
> ADR2 varchar(254) NOT NULL,
> CREDIT numeric(17,9) NOT NULL,
> SALES varchar(30) NOT NULL,
> INN varchar(30) NULL,
> TYPEC smallint NOT NULL,
> NO_NDS smallint NOT NULL,
> NUM_DOG varchar(20) NOT NULL,
> LOGIN varchar(16) NOT NULL,
> BusinessGroup numeric(10,0) DEFAULT 0 NOT NULL,
> TransType varchar(20) DEFAULT "insert" NOT NULL,
> TransDate datetime DEFAULT getdate() NOT NULL,
> Transferred smallint DEFAULT 0 NOT NULL,
> CONSTRAINT PK1
> PRIMARY KEY NONCLUSTERED (CUST_ID)
> )
> LOCK datarows
> go
> 1. Doing like this
> update BILLING_ASE12.billing.adm.TransClient
> set [Transferred]=1
> MsSQL returns:
> OLE DB provider 'MSDASQL' reported an error.
> [OLE/DB provider returned message: Query-based update failed because the[/vbco
l]
row[vbcol=seagreen]
> to update could not be found.]
> OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData
> returned 0x80004005: ].
> 2. Second atempt
> update BILLING_ASE12.billing.adm.TransClient
> set [Transferred]=1 where CUST_ID=7
> MsSQL returns:
> 1 row affected
>
> 3. Final shoot - I wrote simpliest cursor that goes throw rows and sets
> Transferred=1
> Althogh MsSQL has returned:
> --
> Remote tables are not updatable. Updatable keyset-driven cursors on remote
> tables require a transaction with the REPEATABLE_READ or SERIALIZABLE
> isolation level spanning the cursor.
> --
> After that
> select count(*) from BILLING_ASE12.billing.adm.TransClient = 300
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=1 = 202 !!!!!!!
> select count(*) from BILLING_ASE12.billing.adm.TransClient where
> Transferred=0 = 48 !!!!!!!!
> No comments from my side - simply do not understand what is going on
> Any answers are appreciated|||Surely I have access to this server
Otherwise I couldn't do
update no.2
(update BILLING_ASE12.billing.adm.TransClient
set [Transferred]=1 where CUST_ID=7)
that completed successfully
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> 0x80004005 = Access Denied. Check that you have permissions on the
> destination server for what you are doing.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Viacheslav" <Viacheslav@.discussions.microsoft.com> wrote in message
> news:2F06080B-EEC4-4D91-A133-AE0C3E3923B6@.microsoft.com...
> row
>
>
Problem with Update query
Hi - I have 2 tables, which I would like to 'merge'.
tblAccess and tblCars
tblAccess:
a_id int (identity)
a_user (varchar50)
a_pass (varchar20)
a_cid (bigint)
tblCars:
c_id (identity)
c_user (varchar50)
c_carname (varchar50)
These were joined using the a_cid to c_id field, but now I want to copy
the tblAccess.a_user into the relative record in the tblCars.c_user
table.
Something like:
Update tblCars set tblCars.c_user = (select tblAccess.a_user from
tblAccess WHERE tblAccess.a_id = tblCars.c_id)
But I get an error advising the subquery returns more than 1 result.
Could anyone please help?
Thanks, Mark
*** Sent via Developersdex http://www.examnotes.net ***>> But I get an error advising the subquery returns more than 1 result.
The error message suggests that you have more than one value for a_id in the
tblAccess table for each value of c_id in the tblCars table.
If you want to get the right value, you might have to look carefully into
the logic you use in the WHERE clause in your subquery. If all you want to
do is to avoid the error, then you can use an extrema aggregate like:
UPDATE tblCars
SET c_user = ( SELECT MAX( tblAccess.a_user )
FROM tblAccess
WHERE tblAccess.a_id = tblCars.c_id )
WHERE EXISTS ( SELECT *
FROM tblAccess
WHERE tblAccess.a_id = tblCars.c_id )
The WHERE clause is to constrain the statement to update only the values
that have matching values for the identifier columns in both the tables.
Anith|||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.
I guess that you meant to post something like this, assume that you
know that IDENTITY is not ever a key , how to do proper data element
names:and followed industry standards. And not put "rbl-" suffixes on
table names or table anem suffixes on column names.
This is still bad, but it is not awful:
CREATE TABLE VehicleAccess
(vehicle_user VARCHAR (50) NOT NULL, -- size is careful research'
vehicle_pass VARCHAR (50) NOT NULL,
vin CHAR(17) NOT NULL --industry std
REFERENCES Cars(vin)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (vehicle_user, vehicle_pass) -- wild guess by me
);
CREATE TABLE Cars
(vin CHAR(17) NOT NULL PRIMARY KEY, --industry std
vehicle_user VARCHAR (50) NOT NULL, -- size is careful research'
car_name VARCHAR (50) NOT NULL -- you name your car?
);
First of all, fields are not anything like columns and rows are not
anything like records! No wonder you were using the word "merge" in
this posting -- you are locked into a file system model of data, not an
RDBMS.
Next, a data element has one and only one name in a schema. It does
not change from table to table. But file systems do not have a data
dictionary, so you missed this basic point.
Just like the redundancies found in a file system? Which an RDBMS was
supposed to remove? One fact, one way, one place, one time.
Of course; this design has no data integrity. You cannot trust
anything you get out of it. This attempt at kludging an unusable
design only showed you a FEW of the problems you have.
Start over. What are the attributes of a car? The VIN is the natural
key. But why does a car have a name? Does it come when you call it?
And even if it have a name, why is it soooooo long?
You need a table of vehicle users (they are entities, aren't they?
Model them!).
You need a table of vehicle assignments (or access rights) It will
reference the Cars and the Users, but also have a date range, the users
role (driver, passenger), etc.
You used over-sized columns -- Why did you pick BIGINT and VARCHAR(50)?
They will only accumulate garbage. You have no DRI actions. YOu have
no constraint.
Oh, and remember to do a full data audit to clean up what you have in
the DBMS now.
tblAccess and tblCars
tblAccess:
a_id int (identity)
a_user (varchar50)
a_pass (varchar20)
a_cid (bigint)
tblCars:
c_id (identity)
c_user (varchar50)
c_carname (varchar50)
These were joined using the a_cid to c_id field, but now I want to copy
the tblAccess.a_user into the relative record in the tblCars.c_user
table.
Something like:
Update tblCars set tblCars.c_user = (select tblAccess.a_user from
tblAccess WHERE tblAccess.a_id = tblCars.c_id)
But I get an error advising the subquery returns more than 1 result.
Could anyone please help?
Thanks, Mark
*** Sent via Developersdex http://www.examnotes.net ***>> But I get an error advising the subquery returns more than 1 result.
The error message suggests that you have more than one value for a_id in the
tblAccess table for each value of c_id in the tblCars table.
If you want to get the right value, you might have to look carefully into
the logic you use in the WHERE clause in your subquery. If all you want to
do is to avoid the error, then you can use an extrema aggregate like:
UPDATE tblCars
SET c_user = ( SELECT MAX( tblAccess.a_user )
FROM tblAccess
WHERE tblAccess.a_id = tblCars.c_id )
WHERE EXISTS ( SELECT *
FROM tblAccess
WHERE tblAccess.a_id = tblCars.c_id )
The WHERE clause is to constrain the statement to update only the values
that have matching values for the identifier columns in both the tables.
Anith|||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.
I guess that you meant to post something like this, assume that you
know that IDENTITY is not ever a key , how to do proper data element
names:and followed industry standards. And not put "rbl-" suffixes on
table names or table anem suffixes on column names.
This is still bad, but it is not awful:
CREATE TABLE VehicleAccess
(vehicle_user VARCHAR (50) NOT NULL, -- size is careful research'
vehicle_pass VARCHAR (50) NOT NULL,
vin CHAR(17) NOT NULL --industry std
REFERENCES Cars(vin)
ON DELETE CASCADE
ON UPDATE CASCADE,
PRIMARY KEY (vehicle_user, vehicle_pass) -- wild guess by me
);
CREATE TABLE Cars
(vin CHAR(17) NOT NULL PRIMARY KEY, --industry std
vehicle_user VARCHAR (50) NOT NULL, -- size is careful research'
car_name VARCHAR (50) NOT NULL -- you name your car?
);
First of all, fields are not anything like columns and rows are not
anything like records! No wonder you were using the word "merge" in
this posting -- you are locked into a file system model of data, not an
RDBMS.
Next, a data element has one and only one name in a schema. It does
not change from table to table. But file systems do not have a data
dictionary, so you missed this basic point.
Just like the redundancies found in a file system? Which an RDBMS was
supposed to remove? One fact, one way, one place, one time.
Of course; this design has no data integrity. You cannot trust
anything you get out of it. This attempt at kludging an unusable
design only showed you a FEW of the problems you have.
Start over. What are the attributes of a car? The VIN is the natural
key. But why does a car have a name? Does it come when you call it?
And even if it have a name, why is it soooooo long?
You need a table of vehicle users (they are entities, aren't they?
Model them!).
You need a table of vehicle assignments (or access rights) It will
reference the Cars and the Users, but also have a date range, the users
role (driver, passenger), etc.
You used over-sized columns -- Why did you pick BIGINT and VARCHAR(50)?
They will only accumulate garbage. You have no DRI actions. YOu have
no constraint.
Oh, and remember to do a full data audit to clean up what you have in
the DBMS now.
Subscribe to:
Posts (Atom)