You cannot DELETE from multiple tables with a single expression in SQL 2005 – or any other standard SQL for that matter. Access is the exception here. The best method to get this effect is to specify FOREIGN KEYS between the table with an ON DELETE trigger.
The limits are hard-coded: Parameters per stored procedure 2,100 Parameters per user-defined function 2,100 I wrote some code before that split the Contains query into batches and combined the results… see here for more.
insert the result of the SP into a temp table, then join: CREATE TABLE #Temp ( TenantID int, TenantBalance int ) INSERT INTO #Temp EXEC TheStoredProc SELECT t.TenantName, t.CarPlateNumber, t.CarColor, t.Sex, t.SSNO, t.Phone, t.Memo, u.UnitNumber, p.PropertyName FROM tblTenant t INNER JOIN #Temp ON t.TenantID = #Temp.TenantID …
Typically, to troubleshoot this, you go to SQL Server Configuration Manager (SSCM) and: ensure Shared Memory protocol is enabled ensure Named Pipes protocol is enabled ensure TCP/IP is enabled, and is ahead of the Named Pipes in the settings Maybe it can help: Could not open a connection to SQL Server Note : If this … Read more
You need to set identity_insert YourTable ON Then delete your row and reinsert it with different identity. Once you have done the insert don’t forget to turn identity_insert off set identity_insert YourTable OFF