Deleting records.

Anyone have ideas on how to delete records (see code below). I keep on getting the error message:

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

All I want to do is to remove the records in the EMPRATES table where the EMPLOYEEID and RATE are the same in the EMPLOYEE table. What am I missing?

delete emprates
where
emprates.employeeid = employee.employeeid
and emprates.rate=employee.ratedelete emprates from emprates
inner join employee on
emprates.employeeid = employee.employeeid
and emprates.rate=employee.rate|||delete from emprates
where exists
( select 1 from employee
where employeeid = emprates.employeeid
and rate = emprates.rate )|||I tried both syntax and they both perfomed what I needed. The first one had a lower execution cost though.

Thanks again.sql

Deleting Parameter Error

I have a report with 2 parameters, that are part of a hierarchy, that I want
to delete. I delete the query parameter via the designer and the query
mapping dialog and then the report parameters. When I build I get an error
saying the the query parameter I have deleted cannot find the the deleted
reporting parameter. In other situations this deletion seems to work - could
the hierarchy be a factor. This is SO frustrating. Can anyone point me in
the right direction or is it a bug. Regards, Chris.open up the file in XML and do a search on the deleted parameter name. that
will help you find where to look in the designer.
Basically there are three general places for parameters to live, in the
Report Parameters applet; Data Source -->parameters tab or Data Source Text
window depending on whether you are using a sproc or sql code; and in text
boxes in the layout, usually in the header section.
the easiest place to over look is the layout where sometimes parameters are
sometimes displayed or used as filters.
"Fresno Bob" wrote:
> I have a report with 2 parameters, that are part of a hierarchy, that I want
> to delete. I delete the query parameter via the designer and the query
> mapping dialog and then the report parameters. When I build I get an error
> saying the the query parameter I have deleted cannot find the the deleted
> reporting parameter. In other situations this deletion seems to work - could
> the hierarchy be a factor. This is SO frustrating. Can anyone point me in
> the right direction or is it a bug. Regards, Chris.
>
>|||I wouldn't know what to do with the report in XML format. I have looked in
there an I can see the parameter cropping as a query parameter in an
unrelated report parameter.
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:4EFB8F4F-0670-4898-B557-F1AB625CE083@.microsoft.com...
> open up the file in XML and do a search on the deleted parameter name.
> that
> will help you find where to look in the designer.
> Basically there are three general places for parameters to live, in the
> Report Parameters applet; Data Source -->parameters tab or Data Source
> Text
> window depending on whether you are using a sproc or sql code; and in text
> boxes in the layout, usually in the header section.
> the easiest place to over look is the layout where sometimes parameters
> are
> sometimes displayed or used as filters.
> "Fresno Bob" wrote:
>> I have a report with 2 parameters, that are part of a hierarchy, that I
>> want
>> to delete. I delete the query parameter via the designer and the query
>> mapping dialog and then the report parameters. When I build I get an
>> error
>> saying the the query parameter I have deleted cannot find the the deleted
>> reporting parameter. In other situations this deletion seems to work -
>> could
>> the hierarchy be a factor. This is SO frustrating. Can anyone point me in
>> the right direction or is it a bug. Regards, Chris.
>>

Deleting Indexes from a table

I am using SQL-DMO to loop through the Index collection and executing the
Remove method to delete indexses in a table. I am getting an error message
that says I can't delete an Index because it was created using 'PRIMARY KEY'
.
I need to be able to remove ALL Indexes from a table. Can I do this with
SQL-DMO?
Or how can I use SQL-DMO tor remove the PRIMARY KEY setting?
adv-thanks-anceJD,
You can't remove an index associated with a PRIMARY KEY constraint without
removing the PK constraint itself. So I think you'll either have to drop
the constraint and recreated it or if you just want to rebuild the index use
DBCC DBREINDEX instead.
HTH
Jerry
"JD" <JD@.discussions.microsoft.com> wrote in message
news:FDDB7221-2A65-4F8C-BB6A-1B9B931EC9B5@.microsoft.com...
>I am using SQL-DMO to loop through the Index collection and executing the
> Remove method to delete indexses in a table. I am getting an error
> message
> that says I can't delete an Index because it was created using 'PRIMARY
> KEY'.
> I need to be able to remove ALL Indexes from a table. Can I do this with
> SQL-DMO?
> Or how can I use SQL-DMO tor remove the PRIMARY KEY setting?
> adv-thanks-ance|||You will have to remove first all foreign key constraints, then all primary
key and unique constraints and then you will be able to drop the rest of the
indexes.
AMB
"JD" wrote:

> I am using SQL-DMO to loop through the Index collection and executing the
> Remove method to delete indexses in a table. I am getting an error messag
e
> that says I can't delete an Index because it was created using 'PRIMARY KE
Y'.
> I need to be able to remove ALL Indexes from a table. Can I do this with
> SQL-DMO?
> Or how can I use SQL-DMO tor remove the PRIMARY KEY setting?
> adv-thanks-ance|||Thanks,
Can you remove the PK constraint using SQL-DMO? Or will I have to write
a procedure that loops through all of the tables and remove the PK Constrain
t?
"Jerry Spivey" wrote:

> JD,
> You can't remove an index associated with a PRIMARY KEY constraint without
> removing the PK constraint itself. So I think you'll either have to drop
> the constraint and recreated it or if you just want to rebuild the index u
se
> DBCC DBREINDEX instead.
> HTH
> Jerry
> "JD" <JD@.discussions.microsoft.com> wrote in message
> news:FDDB7221-2A65-4F8C-BB6A-1B9B931EC9B5@.microsoft.com...
>
>|||JD,
I haven't actually used it before but there is a KEY object in SQL-DMO that
I believe will do what you're asking. See 'Key Object' in the SQL Server
Books Online. Also, be sure to read Alejandro's feedback as well if you
have RI established with FOREIGN KEYs to your PRIMARY KEYS.
HTH
Jerry
"JD" <JD@.discussions.microsoft.com> wrote in message
news:55256CBF-6AE1-437F-B3FB-087C67670AB7@.microsoft.com...
> Thanks,
> Can you remove the PK constraint using SQL-DMO? Or will I have to write
> a procedure that loops through all of the tables and remove the PK
> Constraint?
> "Jerry Spivey" wrote:
>|||Thanks for the help!
"Jerry Spivey" wrote:

> JD,
> I haven't actually used it before but there is a KEY object in SQL-DMO tha
t
> I believe will do what you're asking. See 'Key Object' in the SQL Server
> Books Online. Also, be sure to read Alejandro's feedback as well if you
> have RI established with FOREIGN KEYs to your PRIMARY KEYS.
> HTH
> Jerry
> "JD" <JD@.discussions.microsoft.com> wrote in message
> news:55256CBF-6AE1-437F-B3FB-087C67670AB7@.microsoft.com...
>
>|||Thanks for your help!
"Alejandro Mesa" wrote:
> You will have to remove first all foreign key constraints, then all primar
y
> key and unique constraints and then you will be able to drop the rest of t
he
> indexes.
>
> AMB
> "JD" wrote:
>sql

Deleting from Stored Procedures

I am trying to write a stored procedure to delete something from a table but I keep getting sytax error.

This is the code I am using:

DELETE [tablename].[tablerow], [tablename].[tablerow]
FROM [tablename]
WHERE (((tablename.tablerow)="void"));

This works as an Access query but not as a stored procedure.DELETE
FROM [tablename]
WHERE tablename.tablerow)='void'
GO

Deleting existing databases

What's the proper way to delete an existing database through native c++ code?

When I try DeleteFile(filename), I get an error about another process using that file. I hadn't created a connection to that file in my own program, so I assume that lock is from the sql engine.

Thanks folks!
does anyone have any insight in this matter?
|||There must be an open connection to the database - pls review your code.

|||it was due to me opening the database in the ssce wince interface...

Deleting Duplicate Data

Hi to All!
I have a table with 60 columns and more than one million rows. i have to
implement composite primary key but it gives me error of duplicate data.
i have used following query to detect the duplicate rows
select NID,output_No from tbl_Data
group by NID,output_No
having count(*) > 1
it gives me 2526 duplicate dows. Now i wants to delete the duplicate
rows what will be the query for deleting the duplicate records.
Thanx
*** Sent via Developersdex http://www.examnotes.net ***This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:umVO5durFHA.3444@.TK2MSFTNGP12.phx.gbl...
>
> Hi to All!
> I have a table with 60 columns and more than one million rows. i have to
> implement composite primary key but it gives me error of duplicate data.
> i have used following query to detect the duplicate rows
> select NID,output_No from tbl_Data
> group by NID,output_No
> having count(*) > 1
> it gives me 2526 duplicate dows. Now i wants to delete the duplicate
> rows what will be the query for deleting the duplicate records.
>
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Try this:
(1) SELECT columnList INTO workTable FROM tableName GROUP BY keyColumns
HAVING COUNT(*) > 1
(2) DELETE tableName FROM workTable WHERE tableName.keyColumns =
workTable.keyColumns
(3) INSERT tableName (columnList) SELECT columnList FROM workTable
(4) DROP workTable
You might want to wrap this in a transaction, but if you don't then a temp
table for the work table is contraindicated because if power goes out
between steps 2 and 3, you will lose the duplicate rows altogether.
If the table were tiny, you could use something like:
SET ROWCOUNT 1
AGAIN:
DELETE tableName FROM (SELECT keyColumns FROM tableName GROUP BY keyColumns
HAVING COUNT(*) > 1) a WHERE tableName.keyColumns = a.keyColumns
IF @.@.ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:umVO5durFHA.3444@.TK2MSFTNGP12.phx.gbl...
>
> Hi to All!
> I have a table with 60 columns and more than one million rows. i have to
> implement composite primary key but it gives me error of duplicate data.
> i have used following query to detect the duplicate rows
> select NID,output_No from tbl_Data
> group by NID,output_No
> having count(*) > 1
> it gives me 2526 duplicate dows. Now i wants to delete the duplicate
> rows what will be the query for deleting the duplicate records.
>
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***

Deleting a login account

I'm getting the below error when trying to change a few properties for a
login account. If I delete the login from Ent. Manager and then create a new
login with the same name, I get the same error.
I can create a new user as long as i use any other name. How can this
account already exist after I manually delete it?
Is there some code I can run to find this account and delete it? It's almost
like this particular account is hidden somewhere else within SQL.
This is SQL 2000.
ERROR ***********
Microsoft SQL-DMO
Error 21002:[SQL-DMO] User 'myUser' already exists.Did you do a restore operation? Did you refresh?
"scott" wrote:

> I'm getting the below error when trying to change a few properties for a
> login account. If I delete the login from Ent. Manager and then create a n
ew
> login with the same name, I get the same error.
> I can create a new user as long as i use any other name. How can this
> account already exist after I manually delete it?
> Is there some code I can run to find this account and delete it? It's almo
st
> like this particular account is hidden somewhere else within SQL.
> This is SQL 2000.
> ERROR ***********
> Microsoft SQL-DMO
> Error 21002:[SQL-DMO] User 'myUser' already exists.
>
>|||I just re-installed sql on the server, then attached my databases. After
that, this problem surfaced.
"Ata John" <AtaJohn@.discussions.microsoft.com> wrote in message
news:C9C4A787-1E6C-4C46-96AE-A3BE8FCBC454@.microsoft.com...
> Did you do a restore operation? Did you refresh?
> "scott" wrote:
>

Deletetion of an old Remote Server

I am trying to set up a publisher and I get an Error 18482: Could not
connect to server 'servername' because 'WEB1' is not defined as a remote
server. I went to the Remote Servers where WEB1' is listed, although this
server is no longer on our network. When I try to delete the listing, I get
the Error 15010: The server 'WEB1' does not exist. Use sp_helpserver to show
available servers. I do this and it is not listed of course. How do I
remove this server with a query?
Thank you.Do you mean linked server?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:CFBDDA35-61A5-4CF0-9899-D15B8AC63D32@.microsoft.com...
> I am trying to set up a publisher and I get an Error 18482: Could not
> connect to server 'servername' because 'WEB1' is not defined as a remote
> server. I went to the Remote Servers where WEB1' is listed, although this
> server is no longer on our network. When I try to delete the listing, I
get
> the Error 15010: The server 'WEB1' does not exist. Use sp_helpserver to
show
> available servers. I do this and it is not listed of course. How do I
> remove this server with a query?
> Thank you.|||No, it is listed under Remote Servers, not under Linked Servers.
"Jack Vamvas" wrote:
> Do you mean linked server?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:CFBDDA35-61A5-4CF0-9899-D15B8AC63D32@.microsoft.com...
> > I am trying to set up a publisher and I get an Error 18482: Could not
> > connect to server 'servername' because 'WEB1' is not defined as a remote
> > server. I went to the Remote Servers where WEB1' is listed, although this
> > server is no longer on our network. When I try to delete the listing, I
> get
> > the Error 15010: The server 'WEB1' does not exist. Use sp_helpserver to
> show
> > available servers. I do this and it is not listed of course. How do I
> > remove this server with a query?
> >
> > Thank you.
>
>

Deletetion of an old Remote Server

I am trying to set up a publisher and I get an Error 18482: Could not
connect to server 'servername' because 'WEB1' is not defined as a remote
server. I went to the Remote Servers where WEB1' is listed, although this
server is no longer on our network. When I try to delete the listing, I get
the Error 15010: The server 'WEB1' does not exist. Use sp_helpserver to sho
w
available servers. I do this and it is not listed of course. How do I
remove this server with a query?
Thank you.Do you mean linked server?
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:CFBDDA35-61A5-4CF0-9899-D15B8AC63D32@.microsoft.com...
> I am trying to set up a publisher and I get an Error 18482: Could not
> connect to server 'servername' because 'WEB1' is not defined as a remote
> server. I went to the Remote Servers where WEB1' is listed, although this
> server is no longer on our network. When I try to delete the listing, I
get
> the Error 15010: The server 'WEB1' does not exist. Use sp_helpserver to
show
> available servers. I do this and it is not listed of course. How do I
> remove this server with a query?
> Thank you.|||No, it is listed under Remote Servers, not under Linked Servers.
"Jack Vamvas" wrote:

> Do you mean linked server?
> --
> Jack Vamvas
> ___________________________________
> Receive free SQL tips - www.ciquery.com/sqlserver.htm
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:CFBDDA35-61A5-4CF0-9899-D15B8AC63D32@.microsoft.com...
> get
> show
>
>

deleteing small amount of records from a view causes IX lock on all the base tables.

Sorry that I had to post it as new message instead of replying since I got server application error.
Kalen, this is a different issue. I wonder why other 4 base tables got IX TAB lock as well since the partitioned view is supposed to look up the relevant tables only by querying on the constraint column.Tom
Can you please include relevant portions of the original message, so I can
know what I am replying to without having to search the archives?
If this is a question about partitioned views, did you supply the view
definition, and the version you are using?
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tom" <anonymous@.discussions.microsoft.com> wrote in message
news:29346002-6963-4D4E-B63C-C6A5C5E292CD@.microsoft.com...
> Sorry that I had to post it as new message instead of replying since I got
server application error.
> Kalen, this is a different issue. I wonder why other 4 base tables got IX
TAB lock as well since the partitioned view is supposed to look up the
relevant tables only by querying on the constraint column.

deleted object for trigger

Is 'deleted' object available for a table with Identity field? I try the
scripts as following. It gets me the error "Invalid object name 'deleted'".
How can I bypass it? Thanks.
CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
FOR UPDATE
AS
SET IDENTITY_INSERT dbo.myTable_History ON
GO
INSERT dbo.myTable_History SELECT * FROM deleted
GO
SET IDENTITY_INSERT dbo.myTable_History OFF
GOGO terminates batches in Query Analyzer. So your trigger does nothing
more than SET IDENTITY_INSERT ON for the table. Remove the GOs and it
should work...
That said, why does your table have an IDENTITY column if you're just
bypassing it from the trigger anyway?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:83E83E75-16E6-4473-B3AB-BEA946A60B08@.microsoft.com...
> Is 'deleted' object available for a table with Identity field? I try the
> scripts as following. It gets me the error "Invalid object name
'deleted'".
> How can I bypass it? Thanks.
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> GO
> INSERT dbo.myTable_History SELECT * FROM deleted
> GO
> SET IDENTITY_INSERT dbo.myTable_History OFF
> GO
>|||The GO keyword should be only at the end of the trigger, not after each
statement.
To use SET IDENTITY_INSERT you must specify the columns (it doesn't
work with *)
Razvan|||1.
I removed all GO statement. Now the trigger is:
CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
FOR UPDATE
AS
SET IDENTITY_INSERT dbo.myTable_History ON
INSERT dbo.myTable_History SELECT * FROM deleted
SET IDENTITY_INSERT dbo.myTable_History OFF
It still doesn't work though. The error shows:
Error 8101: An explicit value for the identity column in ... can only be
specified when a columne list is used and IDENTITY_INSERT is ON.
Do I miss anything?
2.
You raised a good quesiton. The reason I have IDENTITY field on the history
tables is just because they are created in the SQL script by
SELECT * INTO MyTable1_History FROM MyTable1
SELECT * INTO MyTable2_History FROM MyTable2
............
Since there is no short cut to change table's IDENTITY field to be plain int
field, we keep the IDENTITY field in the history table.
"Adam Machanic" wrote:

> GO terminates batches in Query Analyzer. So your trigger does nothing
> more than SET IDENTITY_INSERT ON for the table. Remove the GOs and it
> should work...
> That said, why does your table have an IDENTITY column if you're just
> bypassing it from the trigger anyway?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Sean" <Sean@.discussions.microsoft.com> wrote in message
> news:83E83E75-16E6-4473-B3AB-BEA946A60B08@.microsoft.com...
> 'deleted'".
>
>|||Thanks for your reply. What do you mean 'To use SET IDENTITY_INSERT you must
specify the columns'. Shouldn't I SET IDENTITY_INSERT ON to the table?
"Razvan Socol" wrote:

> The GO keyword should be only at the end of the trigger, not after each
> statement.
> To use SET IDENTITY_INSERT you must specify the columns (it doesn't
> work with *)
> Razvan
>|||A) Use a column list
B) Stop being lazy and create your tables using Data Definition Language.
Why would you take a shortcut that doesn't save much time and is going to
make your database worse?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:BBCE9EF9-1D63-4947-9B38-3A2F96195A3C@.microsoft.com...
> 1.
> I removed all GO statement. Now the trigger is:
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> INSERT dbo.myTable_History SELECT * FROM deleted
> SET IDENTITY_INSERT dbo.myTable_History OFF
> It still doesn't work though. The error shows:
> Error 8101: An explicit value for the identity column in ... can only be
> specified when a columne list is used and IDENTITY_INSERT is ON.
> Do I miss anything?
> 2.
> You raised a good quesiton. The reason I have IDENTITY field on the
history
> tables is just because they are created in the SQL script by
> SELECT * INTO MyTable1_History FROM MyTable1
> SELECT * INTO MyTable2_History FROM MyTable2
> ............
> Since there is no short cut to change table's IDENTITY field to be plain
int
> field, we keep the IDENTITY field in the history table.
>
> "Adam Machanic" wrote:
>
nothing
just
the|||You have to write the column list.
CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
FOR UPDATE
AS
SET IDENTITY_INSERT dbo.myTable_History ON
INSERT dbo.myTable_History (col1, ..., coln)
SELECT col1,..., coln FROM deleted
SET IDENTITY_INSERT dbo.myTable_History OFF
go

> You raised a good quesiton. The reason I have IDENTITY field on the histor
y
> tables is just because they are created in the SQL script by
> SELECT * INTO MyTable1_History FROM MyTable1
> SELECT * INTO MyTable2_History FROM MyTable2
select col2, ..., coln
into t
from table1
alter table t
add col1 int not nul
go
AMB
"Sean" wrote:
> 1.
> I removed all GO statement. Now the trigger is:
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> INSERT dbo.myTable_History SELECT * FROM deleted
> SET IDENTITY_INSERT dbo.myTable_History OFF
> It still doesn't work though. The error shows:
> Error 8101: An explicit value for the identity column in ... can only be
> specified when a columne list is used and IDENTITY_INSERT is ON.
> Do I miss anything?
> 2.
> You raised a good quesiton. The reason I have IDENTITY field on the histor
y
> tables is just because they are created in the SQL script by
> SELECT * INTO MyTable1_History FROM MyTable1
> SELECT * INTO MyTable2_History FROM MyTable2
> ............
> Since there is no short cut to change table's IDENTITY field to be plain i
nt
> field, we keep the IDENTITY field in the history table.
>
> "Adam Machanic" wrote:
>|||Sean
To use INSERT to put rows in a table with INDENTITY_INSERT ON, you must
explicitly list all the columns in the table. Please read about the
variations of the INSERT command in the Books Online.
It would be something like this:
INSERT dbo.myTable_History (name_of_column1, name_of_column_2, ...)
SELECT * FROM deleted
One of the columns names needs to be the name of the identity column, in the
right position.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:3A42CE27-B412-424B-9A12-35882AB63F4F@.microsoft.com...
> Thanks for your reply. What do you mean 'To use SET IDENTITY_INSERT you
> must
> specify the columns'. Shouldn't I SET IDENTITY_INSERT ON to the table?
>
> "Razvan Socol" wrote:
>|||Thanks for the reply. I got it.
"Alejandro Mesa" wrote:
> You have to write the column list.
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> INSERT dbo.myTable_History (col1, ..., coln)
> SELECT col1,..., coln FROM deleted
> SET IDENTITY_INSERT dbo.myTable_History OFF
> go
>
> select col2, ..., coln
> into t
> from table1
> alter table t
> add col1 int not nul
> go
>
> AMB
> "Sean" wrote:
>|||Thanks, Kalen.
"Kalen Delaney" wrote:

> Sean
> To use INSERT to put rows in a table with INDENTITY_INSERT ON, you must
> explicitly list all the columns in the table. Please read about the
> variations of the INSERT command in the Books Online.
> It would be something like this:
> INSERT dbo.myTable_History (name_of_column1, name_of_column_2, ...)
> SELECT * FROM deleted
> One of the columns names needs to be the name of the identity column, in t
he
> right position.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Sean" <Sean@.discussions.microsoft.com> wrote in message
> news:3A42CE27-B412-424B-9A12-35882AB63F4F@.microsoft.com...
>
>

Delete using a linked server

Hi,

I am trying to delete records in a file on the AS400 using a linked server
and I am getting the following error message:

Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"catalog"."schema"."table"'. There was a recoverable, provider-specific error, such as an RPC failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

The linked server is configured as follows:
"server type": other data source
"provider name": Microsoft OLE DB provider for ODBC drivers
"data source" references a system DSN name that uses the driver
"Client Access ODBC driver(32 bit)"
linked server options: "Data access" and "Use remote collation" boxes checked
provider options: "Dynamic parameters", "Nested queries", "Allow in process",
"Non transacted updates" boxes checked

The file on the AS400 is journaled and has a unique key.

The Select and Insert statements work like a charm but I can not for the life of me get the Delete or Update statements to work.

I have been struggling with this problem for over a month, so any help whatsoever would be tremendously appreciated.

Thanks!On the properties page for the linked server, are the RPC and RPC OUT check boxes checked?

That said, I never tried manipulating data on a linked server; only selects. Also, there is a different driver for AS/400s, I' racking my brain for the name, but it's not provided by IBM.

Regards,

hmscott|||Thanks for the reply.

I tried checking the RPC and RPC out boxes and I get the same error message.

As for the other driver, you're probably thinking of OLE DB provider for DB2.
I've have also tried using this driver without success.

My hunch is that there is an ODBC provider property(such as DBPROPSET_PROVIDERROWSET) that's not configured to handle updates or deletes, but I don't know how to display this information.

Are you familiar with OLE DB providers for ODBC or can you direct me to a
resource?|||Can you post the delete and update statements.|||Here's the statement I'm using in query analyzer.

DELETE FROM AS400LINKEDSERVER.SYSTEMNAME.LIBRARYNAME.FILENAME

The four part names have been changed here for privacy.

I have also tried OPENQUERY. Again, same error message.

Thanks.

Delete Replication per TSQL

Hello,
i use MSDE and try to setup a merge replication. I want to drop the
replicated db, but get some error. Sorry for the german error msg:
1> sp_helpdistributor
2> go
distributor
distribution database
directory
account
min distrib retention
max distrib retention history retention history cleanup agent
distribution cleanup agent
rpc server name
rpc login name
---
---
---
---
---
--- --
-- --
--
---
---
distrib
D:\Program Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData
LocalSystem
0
72 48 Agentverlaufscleanup:
distrib
Verteilungscleanup: distrib
repl_distributor
distributor_admin
1> sp_helpserver
2> go
name
network_name status
id
collation_name
connect_timeout query_timeout
---
-- --
---
-- --
rpc,rpc out,use remote collation
0
NULL
0 0
repl_distributor
rpc,dist,rpc out,system,use remote collation
1
NULL
0 0
1> sp_dropdistributor
2> go
Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
sp_dropdistributor, Zeile 150
Der Verteiler 'XXX' konnte nicht gelscht werden. Dieser Verteiler
besitzt zugeordnete Verteilungsdatenbanken.
1> sp_dropdistributiondb distrib
2> go
Meldung 14120, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistributiondb, Zeile 49
Die distrib-Verteilungsdatenbank konnte nicht gelscht werden. Diese
Verteilerdatenbank ist einem Verleger zugeordnet.
1> sp_dropdistpublisher
2> go
Meldung 201, Ebene 16, Status 3, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistpublisher, Zeile 0
Die sp_dropdistpublisher-Prozedur erwartet den @.publisher-Parameter;
dieser wurde nicht bergeben.
1> sp_dropdistpublisher 'BW1H049C\SUDSQLDB'
2> go
Meldung 21033, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_dropdistpublisher, Zeile 99
Der Server 'BW1H049C\SUDSQLDB' kann nicht als Verteilungsverleger
gelscht werden, da auf diesem Server Datenbanken fr die Replikation
aktiviert
sind.
1> sp_dropmergepublication @.publication=N'TTPMerge'
2> go
Meldung 21147, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
sp_MSCheckmergereplication, Zeile 12
Die master-Datenbank wurde nicht fr die Mergereplikation publiziert.
1> sp_helpmergepublication
2> go
How can i delete safely all replication entries so i can drop the db ?
Are there any docs beside BOL to Replication per TSQL ? I see the book
of Hilary and quick check the testchapter , are there more chapter
about replication with TSQL ?
Thx Baumgart
right click on your publication and select delete. Then go to Tools,
Replication, configure distributor, publishers, and subscribers and select
the publication databases tab. Uncheck the sahred databse you wish to drop.
Accept the defaults. Then you should be able to drop the database.
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
"Alexander Baumgart" <al.baumgart@.gmx.de> wrote in message
news:d586cm$g87$1@.mail1.sbs.de...
> Hello,
> i use MSDE and try to setup a merge replication. I want to drop the
> replicated db, but get some error. Sorry for the german error msg:
> 1> sp_helpdistributor
> 2> go
> distributor
> distribution database
> directory
>
> account
>
> min distrib retention
> max distrib retention history retention history cleanup agent
> distribution cleanup agent
> rpc server name
> rpc login name
>
>
>
>
> --- --
> -- --
> --
> --
>
> distrib
> D:\Program Files\MSSQL\Data\MSSQL$SUDSQLDB\RepData
>
> LocalSystem
>
> 0
> 72 48 Agentverlaufscleanup:
> distrib
> Verteilungscleanup: distrib
> repl_distributor
> distributor_admin
> 1> sp_helpserver
> 2> go
> name
> network_name status
> id
> collation_name
> connect_timeout query_timeout
> --
> -- --
> -- --
> rpc,rpc out,use remote collation
> 0
> NULL
> 0 0
> repl_distributor
> rpc,dist,rpc out,system,use remote collation
> 1
> NULL
> 0 0
> 1> sp_dropdistributor
> 2> go
> Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
> sp_dropdistributor, Zeile 150
> Der Verteiler 'XXX' konnte nicht gelscht werden. Dieser Verteiler
> besitzt zugeordnete Verteilungsdatenbanken.
> 1> sp_dropdistributiondb distrib
> 2> go
> Meldung 14120, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistributiondb, Zeile 49
> Die distrib-Verteilungsdatenbank konnte nicht gelscht werden. Diese
> Verteilerdatenbank ist einem Verleger zugeordnet.
> 1> sp_dropdistpublisher
> 2> go
> Meldung 201, Ebene 16, Status 3, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistpublisher, Zeile 0
> Die sp_dropdistpublisher-Prozedur erwartet den @.publisher-Parameter;
> dieser wurde nicht bergeben.
> 1> sp_dropdistpublisher 'BW1H049C\SUDSQLDB'
> 2> go
> Meldung 21033, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_dropdistpublisher, Zeile 99
> Der Server 'BW1H049C\SUDSQLDB' kann nicht als Verteilungsverleger
> gelscht werden, da auf diesem Server Datenbanken fr die Replikation
> aktiviert
> sind.
> 1> sp_dropmergepublication @.publication=N'TTPMerge'
> 2> go
> Meldung 21147, Ebene 16, Status 1, Server BW1H049C\SUDSQLDB, Prozedur
> sp_MSCheckmergereplication, Zeile 12
> Die master-Datenbank wurde nicht fr die Mergereplikation publiziert.
> 1> sp_helpmergepublication
> 2> go
> How can i delete safely all replication entries so i can drop the db ?
> Are there any docs beside BOL to Replication per TSQL ? I see the book
> of Hilary and quick check the testchapter , are there more chapter
> about replication with TSQL ?
> Thx Baumgart
|||Hilary Cotter wrote:
+AD4- right click on your publication and select delete. Then go to
Tools,
+AD4- Replication, configure distributor, publishers, and subscribers
and
+AD4- select the publication databases tab. Uncheck the sahred databse
you
+AD4- wish to drop.
+AD4-
+AD4- Accept the defaults. Then you should be able to drop the database.
I didnt have any tools , like i told +ADs-(( Only MSDE. I set all per
SP ,
and tried the reverse way to drop the replication but that didnt work.
What i tried, error msg got translate by me from german to english:
a) 1+AD4- sp+AF8-dropmergepublication +AEA-publication+AD0-N'TTPMerge'
Meldung 21147, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-MSCheckmergereplication, Zeile 12
Die master-Datenbank wurde nicht f+APw-r die Mergereplikation
publiziert.
(the master database didnt get shared per mergereplication)
b) 1+AD4- sp+AF8-dropdistpublisher 'BW1H049C+AFw-SUDSQLDB'
2+AD4- go
Meldung 21033, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-dropdistpublisher, Zeile 99
Der Server 'xxx+AFw-SUDSQLDB' kann nicht als Verteilungsverleger
gel+APY-scht werden, da auf diesem Server Datenbanken f+APw-r die
Replikation
aktiviert sind.
(the server xxx+AFw-instancename couldnt be delete as distributor,
there are database active for replication)
c) 1+AD4- sp+AF8-dropdistributiondb distrib
2+AD4- go
Meldung 14120, Ebene 16, Status 1, Server BW1H049C+AFw-SUDSQLDB,
Prozedur
sp+AF8-dropdistributiondb, Zeile 49
Die distrib-Verteilungsdatenbank konnte nicht gel+APY-scht werden. Diese
Verteilerdatenbank ist einem Verleger zugeordnet.
(the distrib - distributiondb couldnt be deleted, its assigned to a
distributor)
d) 1+AD4- sp+AF8-dropdistributor
2+AD4- go
Meldung 14121, Ebene 16, Status 1, Server XXX, Prozedur
sp+AF8-dropdistributor, Zeile 150
Der Verteiler 'XXX' konnte nicht gel+APY-scht werden. Dieser Verteiler
besitzt zugeordnete Verteilungsdatenbanken.
(the distributor +ACI-xxx+AFw-instancename+ACI- couldnt be deleted ,
its assigned with distribution-databases)
so how can i delete a merge-replication per osql.exe (TSQL) ?
Thx for your help Hilary , i will see if i can use the GUI way to find
the right SP's to delete the merge replication.
|||Hilary Cotter wrote:
+AD4- right click on your publication and select delete. Then go to
Tools,
+AD4- Replication, configure distributor, publishers, and subscribers
and
+AD4- select the publication databases tab. Uncheck the sahred databse
you
+AD4- wish to drop.
+AD4-
+AD4- Accept the defaults. Then you should be able to drop the database.
Hello,
after 2 day of work i was able to drop the merge replication. The
important cmd was sp+AF8-removedbreplication, as mentioned in
http://support.microsoft.com/default...en-us+ADs-3244
01
I check your book specially the example chapter where u described a way
to drop a replication, that also didnt work for me, because there was a
replication active. How deep u discusss manual setup of replication in
your book (in the example chapter u say more in chapter 6) ?
thx Baumgart

delete query with inner join

Hallo,
Can anybody let me know what I need to change to make this query work:
delete
from a
inner b
on a.item = b.item
I get the error message "Incorrect syntax near the keyword 'inner'."
Thanks,
Stephen.Hallo,

Can anybody let me know what I need to change to make this query work:

delete
from a
inner b
on a.item = b.item

I get the error message "Incorrect syntax near the keyword 'inner'."

Thanks,
Stephen.

Have you tried?

INNER JOIN b on...|||Hi Steve o

Key word is join rather than inner. Also, you'll need to use an EXISTS or IN statement rather than a join if you are deleting

HTH|||Check BOL for delete statment,
Here is the corect syntax,

delete a
from a
inner join b
on a.item = b.item

Delete Query

Can anyone help me with this error? I am trying to delete several
thousand records at a time, but for some reason I continue to get the
following error. I just applied SP3a for SQL2K hoping that would fix the
problem, but it has not. Thanks in advance.
delete [GA_SingleFamily] from (select top 500 * from [GA_SingleFamily]) as
stuff Where [GA_SingleFamily].ml = stuff.ml
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17055
Date: 10/6/2003
Time: 12:08:48 PM
User: N/A
Computer: DATASERVERA
Description:
17066 :
SQL Server Assertion: File: <recbase.cpp>, line=1378
Failed Assertion = 'm_offBeginVar < m_SizeRec'.
--
Doug Threewittdelete [GA_SingleFamily]
from
[GA_SingleFamily]
inner join
(select top 500 ml from [GA_SingleFamily]) as stuff --(don't use * if you
only want 1 col)
on [GA_SingleFamily].ml = stuff.ml
jobi
"Doug Threewitt" <doug@.seisystems.com> wrote in message
news:euXQhONjDHA.548@.TK2MSFTNGP11.phx.gbl...
> Can anyone help me with this error? I am trying to delete several
> thousand records at a time, but for some reason I continue to get the
> following error. I just applied SP3a for SQL2K hoping that would fix the
> problem, but it has not. Thanks in advance.
> delete [GA_SingleFamily] from (select top 500 * from [GA_SingleFamily]) as
> stuff Where [GA_SingleFamily].ml = stuff.ml
>
>
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17055
> Date: 10/6/2003
> Time: 12:08:48 PM
> User: N/A
> Computer: DATASERVERA
> Description:
> 17066 :
> SQL Server Assertion: File: <recbase.cpp>, line=1378
> Failed Assertion = 'm_offBeginVar < m_SizeRec'.
>
> --
> Doug Threewitt
>

Delete problem-too many parameters

I have a dataview control with the delete method pointing to a logical delete stored procedure in SQL SERVER Express. I am getting an error message saying too many parameters provided. I've check and there is one parameter expected and one passed in. This is my SP, the html, and the debug infor I'm looking at. Any ideas?

PROCEDUREdbo.usp_Drivers_Delete

@.mintDriver_IDint

AS

UPDATEtblDrivers

SETActive= 0

WHEREDriver_ID=@.mintDriver_ID

html:

<DeleteParameters>

<asp:ControlParameterControlID="GridView1"Name="mintDriver_ID"PropertyName="SelectedValue"

Type="Int32"/>

</DeleteParameters>

Debug:

?SqlDataSourceDrivers.DeleteParameters(0)

{System.Web.UI.WebControls.ControlParameter}

System.Web.UI.WebControls.ControlParameter: {System.Web.UI.WebControls.ControlParameter}

ConvertEmptyStringToNull: True

DefaultValue: Nothing

Direction: Input {1}

Name: "mintDriver_ID"

Size: 0

Type: Int32 {9}

?SqlDataSourceDrivers.DeleteParameters.Count

1

Error:

Exception Details:System.Data.SqlClient.SqlException: Procedure or function usp_Drivers_Delete has too many arguments specified.

Perhaps I'm missing something, but you don't really have a delete. You have an update that sets a status column (Active) to 0. I think if you try changing the <DeleteParameters> to <UpdateParameters> you should be fine.

Deb

DELETE permission denied problem when using a stored proc to delet

I'm using a stored proc to delete a record in two tables (in two different
databases) and I keep receiving “Error Number: 229 -- Error State: 5 -- Er
ror
Message: DELETE permission denied on object 'ewBehaviour', database
'eWorkSpaceV5', owner 'dbo' ”. The stored proc works for me (as sysadmin
for
the server), but won’t work for any other user. I’ve tried giving a use
r
db_owner access for both the databases but I still receive the error.
Below is the stored proc:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.spWEB_Delete_Detention
@.DetentionID as int
AS
SET XACT_ABORT ON
DECLARE @.BehaviourId as int
IF NOT EXISTS
(
SELECT DetentionID
FROM DC_Detentions
WHERE DetentionID=@.DetentionID
)
BEGIN
RAISERROR ('Detention does not exist in DC_Detention ',16,1)
RETURN -1
END
IF NOT EXISTS
(
SELECT Id
FROM eWorkSpaceV5.dbo.ewBehaviour
WHERE ID =
(SELECT Link
FROM DC_Detentions
WHERE DetentionID=@.DetentionID)
)
BEGIN
RAISERROR ('Behaviour entry does not exist in ewBehaviour',16,1)
RETURN -1
END
SELECT @.BehaviourId=Link
FROM DC_Detentions
WHERE DetentionID=@.DetentionID
BEGIN TRANSACTION
print 'Begin Transaction'
print 'Try Delete DC_Detentions'
DELETE FROM DC_Detentions
WHERE (DetentionID = @.DetentionID)
IF @.@.ERROR<>0 or @.@.ROWCOUNT<>1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not delete Detention from DC_Detention',16,1)
print 'Delete from DC_Detention failed'
RETURN -1
END
print 'Try Delete eWorkSpaceV5 ewBehaviour'
DELETE FROM eWorkSpaceV5.dbo.ewBehaviour
WHERE (Id = @.BehaviourId)
IF @.@.ERROR<>0 or @.@.ROWCOUNT<>1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Could not delete Detention into ewBehaviour',16,1)
print 'Delete from ewBehaviour failed'
RETURN -1
END
COMMIT TRANSACTION
RETURN 0
SET XACT_ABORT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOAs long as you dont activate ownership chain (as I assume that you are
deleting data in a different database) this won=B4t work. Ownerchip
chains is disabled by default since SP3.
Look for cross database ownership chain in BOL or for the thread:
http://groups.google.de/group/micro...ramming/browse=
_frm/thread/4b86a2ccefd974af
HTH, JEns Suessmeyer.