SQLSRV Driver For PHP Delete Queries Issue?

Jan 1, 2008

I have been experimenting with the preview release of the SQL Server 2005 driver for PHP. I have used the PHP MSSQL extension a lot as well as connecting thru COM. I am glad we finally will see a Microsoft supported extension.

With the current release I am able to do most things my current MSSQL driver does. It does require some workarounds (there is no sqlsrv_num_rows nor sqlsrv_data_seek) here and there. But nothing major.

Now to get to the issue at hand. When I run a DELETE query, there is no statement resource returned and my application incorrectly detects an error.

Sample code:
$the_query = "DELETE FROM sometable WHERE id=5";
if ( ! $qid = sqlsrv_conn_execute ( $connection_id, $the_query ) )
{
echo $the_query."<br />";
foreach( sqlsrv_errors() as $error )
{
echo "SQLSTATE: ".$error['SQLSTATE']."<br/>";
echo "Code: ".$error['code']."<br/>";
echo "Message: ".$error['message']."<br/>";
}
exit();
}

Looks like a bug to me. The funny thing is that exactly the same bug was in the PHP MSSQL extension a while ago.

View 2 Replies


ADVERTISEMENT

Transactions And Sqlsrv Php Driver

Nov 20, 2007

Hi,

I am testing the sqlsrv driver for php5. I wonder if there is any way how to work with transactions.

What I basically need is some equivalent of:
mssql_query("BEGIN TRAN", $c);


The command sqlsrv_conn_execute($c, "BEGIN TRAN") returns null and error code 266, message [Microsoft][SQL Native Client][SQL Server]Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

Is it possible to use the driver the way I need? I mean, is there a way how to issue multiple statements (sqlsrv_stmt_execute()) in one transaction?

View 3 Replies View Related

SQLSrv JDBC Driver 1.2.2828 Bug?

Jan 19, 2008

Hi All,
To me the results of getSchemas appears wrong and the catalog/schema values the wrong way round!

Using latest version of Sql Server 2005 Express with 1.2.2828 JDBC driver.

Created a database called: db_connection_tests

Created some tables inside this database called:


main
child
lookupThese show in Sql Server Manager as:


dbo.main
dbo.child
dbo.lookupIf I now log on through JDBC as user "dddddddddddd" and run 2 metadata calls (getCatalogs() and getSchemas()) the results to me do not match, that is the schema and catalog seem to be wrong in getSchemas.

Why is the catalog DBO returned for schema d_connection_tests in getSchemas(), when not in results of getCatalogs()?

TABLE_SCHEM db_connection_tests
TABLE_CATALOG dbo


getDatabaseProductName: Microsoft SQL Server

getDatabaseProductVersion: 9.00.3042

getDatabaseMajorVersion: 9

getDatabaseMinorVersion: 0

meta.getDriverMajorVersion(): 1

meta.getDriverMinorVersion(): 2

meta.getDriverName(): Microsoft SQL Server 2005 JDBC Driver

meta.getDriverVersion(): 1.2.2828.100

List of schemas (snippet):

TABLE_SCHEM db_connection_tests
TABLE_CATALOG dbo

List of catalogs:

TABLE_CAT db_connection_tests
TABLE_CAT master
TABLE_CAT model
TABLE_CAT msdb
TABLE_CAT tempdb

Tried JTds driver and results for getSchemas() are different!

getDatabaseProductName: Microsoft SQL Server

getDatabaseProductVersion: 09.00.3042

getDatabaseMajorVersion: 9

getDatabaseMinorVersion: 0

meta.getDriverMajorVersion(): 1

meta.getDriverMinorVersion(): 2

meta.getDriverName(): jTDS Type 4 JDBC Driver for MS SQL Server and Sybase

meta.getDriverVersion(): 1.2.2

List of schemas:

TABLE_SCHEM db_connection_tests
TABLE_CATALOG null


Code

package test.com.database;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SqlServeTest_DriverMs {

private Connection con;
private DatabaseMetaData meta;

public static void main(String[] args) throws ClassNotFoundException, SQLException {

SqlServeTest_DriverMs sqlServeTest = new SqlServeTest_DriverMs();
sqlServeTest.runTests();
}

protected void runTests() throws ClassNotFoundException, SQLException {

// Class.forName("net.sourceforge.jtds.jdbc.Driver"); // JTds Driver
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); // MS Driver
try {
// Jtds Drvier
// con = DriverManager.getConnection("jdbc:jtdsqlserver://localhost:1433/db_connection_tests;user=dddddddddddd;password=root");
// MS Driver
con = DriverManager.getConnection("jdbcqlserver://localhost:1433;user=dddddddddddd;password=root;database=db_connection_tests");

meta = con.getMetaData();
System.out.println("getDatabaseProductName: " + meta.getDatabaseProductName());
System.out.println("getDatabaseProductVersion: " + meta.getDatabaseProductVersion());
System.out.println("getDatabaseMajorVersion: " + meta.getDatabaseMajorVersion());
System.out.println("getDatabaseMinorVersion: " + meta.getDatabaseMinorVersion());
System.out.println("meta.getDriverMajorVersion(): " + meta.getDriverMajorVersion());
System.out.println("meta.getDriverMinorVersion(): " + meta.getDriverMinorVersion());
System.out.println("meta.getDriverName(): " + meta.getDriverName());
System.out.println("meta.getDriverVersion(): " + meta.getDriverVersion());

printOutSchemas();
printOutCatalogs();

} finally {
if (con != null) {
try {
con.close();

} catch (Exception e) { // Ignore error
}
}
}
}

protected void printOutSchemas() throws SQLException {
ResultSet rs = null;
try {
rs = meta.getSchemas();
System.out.println("List of schemas: ");
printResults(rs);

} finally {
if (rs != null) rs.close();
}
}

protected void printOutCatalogs() throws SQLException {
ResultSet rs = null;
try {
rs = meta.getCatalogs();
System.out.println("List of catalogs: ");
printResults(rs);

} finally {
if (rs != null) rs.close();
}
}

protected void printResults(ResultSet rs) throws SQLException {
final int cols = rs.getMetaData().getColumnCount();
while (rs.next()) {
System.out.println();
for (int i = 1; i <= cols; i++) {
System.out.println(' ' + rs.getMetaData().getColumnName(i) + ' ' + rs.getString(i));
}
}
}
}

View 1 Replies View Related

SQL Server 2005 Driver For PHP - Parameterized Queries

Mar 14, 2008

I've setup a parameterised query in PHP correctly and achieved the results I wanted from a basic view.

I then developed the application to use a Table-valued Function to be able to simplify the PHP code.


SELECT * FROM [CDBF].[dbo].[webOrganisation] ('w%',5,11)


This query works fine within the code.

/* Define the query. */
$tsql = "SELECT * FROM webOrganisation ('w%',5,11)";

/* Execute the query. */
$stmt = sqlsrv_query( $connection, $tsql);
if ( ! $stmt )
{
echo "Error in statement 2 execution in display_search().";
die( print_r( sqlsrv_errors(), true));
}

/* Iterate through the resultset printing a row of data upon each iteration.*/
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC))
{

// Display results (this bit works fine!)
}

/* Free statement and connection resources. */
sqlsrv_free_stmt( $stmt);


However when I try to parameterise it, I get errors.

Error in statement 2 execution in display_search(). Array ( [0] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Invalid Descriptor Index [message] => [Microsoft][SQL Native Client]Invalid Descriptor Index ) [1] => Array ( [0] => 07009 [SQLSTATE] => 07009 [1] => 0 [code] => 0 [2] => [Microsoft][SQL Native Client]Invalid parameter number [message] => [Microsoft][SQL Native Client]Invalid parameter number ) )


/* Define the query. */
$tsql = "SELECT * FROM webOrganisation ( ? , ? , ? )";

/* Execute the query. */
$start = 5;
$finish = 11;
$params = array($search.'%',$start,$finish);
$stmt = sqlsrv_query( $connection, $tsql, $params);
if ( ! $stmt )


All other parts of the code remain the same.

Where am I going wrong

View 3 Replies View Related

Combine Delete Queries Running Separately

May 21, 2014

I have for delete queries which I run separately. Could I have a one statement to run instead.

DELETE FROM dbo.PatientHistory
FROM dbo.PatientHistory INNERJOIN TEST_PATS ON dbo.PatientHistory.PatientGuidDigest = TEST_PATS.PatientGuidDigest

DELETE FROM dbo.PostcodeScores
FROM dbo.PostcodeScores INNERJOIN TEST_PATS
ON dbo.PostcodeScores.PatientGuidDigest = TEST_PATS.PatientGuidDigest

[Code] ....

View 2 Replies View Related

Select, Insert And Delete Queries Timing Out

Jul 20, 2005

I am using a sql server 2000 database to log the results from a monitorthat I have running - essentially every minuite, the table describedbelow has a insert and delete statements similar to the ones below runagaint it.Everything is fine for a few weeks, and then without fail, all accessesto the table start slowing down, to the point where even trying toselect all rows starts timing out.At that point, the only way to make things right that I have found, isto delete the table and recreate it.Am I doing something specific that sql server really doesn't like? Isthere a better solution then deleting and recreating the table?CREATE TABLE [www2] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),[success] [bit] NULL ,[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[level] [int] NULL ,[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOINSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])VALUES ('Error on: <ahref="http://www2.klickit.com/include/asp/system_test.asp">http://www2.klickit.com/include/asp/system_test.asp</a><br><br>The operation timedout<br><br>(Test Activated From: Lynx/2.8.2rel.1libwww-FM/2.14)',0,1,'',getDate())DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (ReportNot Like 'ResetThanks in advance,Simon Withers*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Can't Load The Php Sqlsrv.dll For Some Reason

Feb 5, 2008

Running MS2k3 webserver with IIS and PHP 5.2.5

my php.ini is in the Windows folder containing among other things these 2 entries:

extension_dir ="C:PHPext"
extension=php_sqlsrv.dll

and the file php.sqlsrv.dll is located in the PHPext directory

I've restarted the IIS server with php running and working just fine. Is there anything else I could still check ?





Code Snippet

if (!extension_loaded('sqlsrv')) {
echo("NOT LOADED");
}

Produces: not loaded.


EDIT:
Dug up some logs:
The description for Event ID ( 2 ) in Source ( PHP-5.2.5 ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: php[2144]; PHP Warning: PHP Startup: Unable to load dynamic library 'C:PHPextphp_sqlsrv.dll' - The specified module could not be found.
in Unknown on line 0.

View 1 Replies View Related

Sqlsrv.exe Consuming High CPU Cycles

Dec 19, 2005

Hi,
The sqlsrv.exe process has been consuming a high number of cpu cycles and time and seems not be decreasing at all. Can somebody point me in the right direction as to how to identify the bottleneck and resolve the same at the earliest

Thanks in advance

View 3 Replies View Related

Prob With 120 Days Evaluation Copy Of Sqlsrv 2000

Mar 22, 2004

Hi,
I m using SQL Server 2000's 120 days evaluation copy for testing my application. The prob i am facing is that whenever the rows in one of the table increses beyond 10K it gets cleared up.

I could not trace the reason for this. Plz some one suggest a possible solution for the same.

Thanks in advance.

View 1 Replies View Related

[Microsoft][ODBC Driver Manager] Data Source Name Not Found And No Default Driver Specified

Oct 19, 2006

trying to install sql server2005 on a windows 2003 server box.

getting msg below at the sql server . i looked at other posts on trying to uninstall SQL Native Access Client and norton antivirus. i could never find the snac on the add - remove programs and this server does not have a virus protection program yet.

here's the history of the installs on the server:

wanted to test a 2005 upgrade so:

1) installed sql server 2000 then sp4 then restored some databases to it - all OK

2) tried to upgrade to sql 2005 but ran into problems and left it at that.

had a disk drive crash on the d drive so lost the installs but not the operating system

when the drive was replaced, left alone for a while

then wanted to test a straight 2005 install

1) removed the broken 2005 attempt

2) removed the 2000

3) installed 2005 and got the error on the subject line:

TITLE: Microsoft SQL Server 2005 Setup
------------------------------

SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.

i've gone through as many of the forums that i can and have tried several things - like uninstalling 2005 and installing pieces and parts but but nothing seems to work.

Thanks!

Dan <><

View 17 Replies View Related

Sql2005 ODBC Driver Manager Data Source Name Not Found And No Default Driver Specified

Feb 4, 2008

i am attempting to run phpbb using ms sql 2005 on the same box but get the following error during the setup


Could not connect to the database, see error message below.
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified


i am not sure yet if this is an issue with sql, php or phpbb

php is installed in iis and has all the modules installed which the php msi installer supported. i can run php code but in this case when i'm running the install.php file for the phphbb setup, i fill in the values for the database and got that error

phpbb detects all the required server settings and sees that i have ms sql installed

any suggestions on what that error means or how to go about configuring odbc driver?

probably a simple issue. but i'm still new with sql stuff

View 1 Replies View Related

Sybase Native Driver Vs Ole Db Driver

Oct 13, 2004

Hello,

I want to have a comparison about operation of a power builder application with sql server database, using sybase native driver and ole db driver.

View 2 Replies View Related

Compatible Versions Of Ms SQL Server, ODBC Driver, Driver Manager And ODBC API Spec

Oct 28, 2004

Hi,

Please help share with me if you know the version compatibility matrix of Ms SQL Server, ODBC driver (sqlsrv32.dll), Driver Manager (odbc32.dll) and ODBC API spec. For instance, how can I know Ms SQL Server 2000 can work with which version of sqlsrv32.dll, a particular version of sqlsrv32.dll can work with which version of odbc32.dll and a certain version of sqlsrv32.dll/odbc32.dll conforms to which version of ODBC API spec (e.g. 3.5).

Any help will be appreciated.

Thanks,

vtluu.

View 1 Replies View Related

MS SQL Server JDBC Driver Type 2 And Driver Type 4 Differences

Jul 20, 2005

Hi,Could some please tell me whether Microsoft provides Type 2 and Type 4jdbc driver ? For Type 4 MS providescom.microsoft.jdbc.sqlserver.SQLServerDriver driver. What is thecofiguration required for type 2 driver and what driver class filesrequired ?Its very urgent to me please reply.Ajay

View 1 Replies View Related

SQL Server 2008 :: Maintenance Plan Delete History Trying To Delete Wrong Files

Sep 11, 2015

I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.

It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog

how I can get this corrected so I can get the Maintenance Plans to run correctly.

I have tried deleting and recreating the Plan but to no avail

View 0 Replies View Related

Master Data Services :: Hard Delete All Soft Delete Records (members) In Database

May 19, 2012

I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.

View 18 Replies View Related

Copy And Delete Table With Foreign Key References(...,...) On Delete Cascade?

Oct 23, 2004

Hello:
Need some serious help with this one...

Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...

Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.

The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.

This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.

So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?

The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.

Thanks!
Sky

View 1 Replies View Related

SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!

Nov 13, 2006

I am having great difficulty with cascading deletes, delete triggers and referential integrity.

The database is in First Normal Form.

I have some tables that are child tables with two foreign keyes to two different parent tables, for example:

Table A
/
Table B Table C
/
Table D

So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.

SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.

Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.

When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????

This is an example of my delete trigger:

CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;

And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.

So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.

So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).

Hope this makes sense...
Thanks,
Josh


View 6 Replies View Related

Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another

Nov 17, 2006

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

View 6 Replies View Related

I Use SQL 2000, Can You Use One Delete Query To Delete 2 Tables?

Nov 26, 2007

this is my Delete Query NO 1
alter table ZT_Master disable trigger All
Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
alter table ZT_Master enable trigger All
 
I have troble in Delete Query No 2
here is a select statemnt , I need to delete them
select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey)  And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
I tried modified it as below
delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey)  And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
but this doesn't works..
 
can you please help?
and can I combine these 2 SQL Query into one Sql Query? thank you

View 1 Replies View Related

How To Run Delete Query / Delete Several Rows Just By One Click ?

Feb 16, 2008

I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question) 

View 2 Replies View Related

Allow Single Row Delete From A Table But Not Bulk Delete

Sep 16, 2013

The requirement is: I should allow single row delete from a table but not bulk delete. An audit table should get updated if there is any single delete or single update. So I wrote the triggers as follows: for single and bulk delete

ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS

[code]...

When I try to run the website, the database error I am getting is:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

View 3 Replies View Related

Delete Doesn't Delete Rows, But @@ROWCOUNT Says It Did

Aug 20, 2007

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?

View 3 Replies View Related

Cannot Amend Or Delete Subscription And Cannot Delete Report.

Nov 20, 2007



Hi,

I have a problem with one report on my server. A user has requested that I exclude him from receiving a timed email subscription to several reports. I was able to amend all the subscriptions except one. When I try to remove his email address from the subscription I receive this error:

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help







For more information about this error navigate to the report server on the local server machine, or enable remote errors


Online no help couldn't offer any advice at all, so I thought I'd just delete the subscription and recreate it again, but I receive the same message. "Okay, no problem, I'll just delete the report and redeploy it and set up the subscription so all the other users aren't affected", says I. "Oh, no!", says the report server, and then it give me this message:





System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---



What's even weirder is that I'm the owner and creator of the report and I'm a system admin and content manager on the report server and I set up the subscription when the report was initially deployed. Surely I should have sufficient rights to fart around with this subscription/report as I see fit?

I have rebooted the server, redeployed the report, checked credentials on the data source and tried amending and deleting from both the report manager and management studio but still I am prevented from doing so.

Any help would be much appreciated.

Thanks in advance,

Paul

View 3 Replies View Related

Parameterized Queries Running Slower Than Non-parameterized Queries

Jul 20, 2005

HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)

View 1 Replies View Related

Delete From Vs Delete With Subquery In Transaction

Feb 23, 2006

First, this is not my code.

This one is weird and I am missing something fundamental on this one. A developer was getting a timeout with this...


CREATE PROCEDURE p_CM_DeleteBatch
(
@SubmitterTranID VARCHAR(50)
)
AS
DECLARE
@COUNT INT,
@COMMIT INT

SET @COUNT = 0
SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1

select @COUNT = COUNT(*) from claimsreceived
where (claimstatus NOT IN ('Keyed', 'Imported')) AND
SubmitterTranID = @SubmitterTranID

IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete
BEGIN
BEGIN TRANSACTION
DELETE FROM INVOICECLAIMMAPPING WHERE CLMRECDID IN (SELECT DISTINCT CLMRECDID FROM CLAIMSRECEIVED WHERE SUBMITTERTRANID = @SUBMITTERTRANID)
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPayment WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsreceivedPayorServices where ClmRecdPyID in (SELECT ClmRecdPyID
FROM ClaimsReceivedPayors
WHERE SubmitterTranID = @SubmitterTranID)

IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceivedPayors WHERE ClmRecdid in (SELECT ClmRecdID
FROM ClaimsReceived
WHERE SubmitterTranID = @SubmitterTranID)
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0
DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

IF (@COMMIT = 1)
BEGIN
--ROLLBACK TRANSACTION --For Testing Purpose ONLY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN (-1)
END
END
ELSE
BEGIN
RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1)
END
GO


I applied a couple of indices and got ride of the uncorrelated subqueries


CREATE PROCEDURE p_CM_DeleteBatch
(
@SubmitterTranID VARCHAR(50)
)
AS
DECLARE
@COUNT INT,
@COMMIT INT

SET @COUNT = 0
SET @COMMIT = 1 --DO NOT CHANGE THIS. The Operation will be commited only when this value is 1

select @COUNT = COUNT(*) from claimsreceived
where (claimstatus NOT IN ('Keyed', 'Imported')) AND
SubmitterTranID = @SubmitterTranID

IF (@COUNT = 0) --This means that that Claims under this Batch have not been adjudicated & it is safe to delete
BEGIN
BEGIN TRANSACTION

DELETE INVOICECLAIMMAPPING
FROM INVOICECLAIMMAPPING
JOIN CLAIMSRECEIVED
ON INVOICECLAIMMAPPING.CLMRECDID = CLAIMSRECEIVED.CLMRECDID
WHERE CLAIMSRECEIVED.SUBMITTERTRANID = @SUBMITTERTRANID

IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPayment
WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsPaymentServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE ClaimsreceivedPayorServices
FROM ClaimsreceivedPayorServices
JOIN ClaimsReceivedPayors
ON ClaimsreceivedPayorServices.ClmRecdPyID = ClaimsReceivedPayors.ClmRecPyID
WHERE ClaimsReceivedPayors.SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE ClaimsReceivedPayors
FROM ClaimsReceivedPayors
JOIN ClaimsReceived
ON ClaimsReceivedPayors.ClmRecdid = ClaimsReceived.ClmRecdid
WHERE ClaimsReceived.SubmitterTranID = @SubmitterTranID

IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceivedServices WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM ClaimsReceived WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

DELETE FROM BATCHLOGCLAIMS WHERE SubmitterTranID = @SubmitterTranID
IF (@@ERROR <> 0)
SET @COMMIT = 0

IF (@COMMIT = 1)
BEGIN
--ROLLBACK TRANSACTION --For Testing Purpose ONLY
COMMIT TRANSACTION
RETURN (0)
END
ELSE
BEGIN
ROLLBACK TRANSACTION
RETURN (-1)
END
END
ELSE
BEGIN
RaisError ('This Batch cannot be deleted. It has claim(s) which has been Adjudicated', 16, 1)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON

GO


Suddenly this constraint was being violated with the change


ALTER TABLE [dbo].[ClaimsReceivedPayorServices] ADD CONSTRAINT [FK_ClaimsReceivedPayorServices_CLAIMSRECEIVEDPAYOR S] FOREIGN KEY
(
[ClmRecdPyID]
) REFERENCES [CLAIMSRECEIVEDPAYORS] (
[CLMRECPYID]
)


Is the delete on ClaimsReceivedPayors starting before the delete on ClaimsreceivedPayorServices finishes? If so why would it matter between the join and subquery? This one is making me depressed because I can not explain it.

View 2 Replies View Related

To Delete Or Not To Delete - DESIGN ISSUE!

Apr 3, 2008

Hi all

Need some advice solving a little problem I have with my database!

Current setup:

I have a person table that is made up of 39 columns. I also allow for person records to be deleted but I do this by having another table I call LogicallyDeletedrecords. This table is made up of the PersonId, Reason for deletion/suppression and a date time stamp. To access Live records I created a view based on my Person table which contains a WHERE clause to exclude records that exist in the LogicallyDeletedrecords. Similarly, I have another view DeadPersonData which contains Person records that have been removed. Hope it all makes sense so far! Now on to my worries!

The problem:

My Person table contains 9+ million records. The LogicallyDeletedrecords table has 500k+ but I anticipate further growth over the coming weeks/months. My worry is that my LivePersonData view will be too slow to access as my LogicallyDeletedrecords table grows. What’s more, as part of my Load routine, I have to make sure that Person data loaded on to the system is excluded if that same person exists as a deleted member. Both of these actions could slow down my system as the deleted table grows.

My thoughts:

I’ve been thinking of physically deleting dead Person records from my person table (possibly creating an archive table to hold them). But then if I delete them how do I cross check the details when new Person details get loaded?! As I said, my current LogicallyDeletedrecords table holds the PersonId, ReasonDeleted and CreationStamp. The only way is to add further columns which I use to match Person Details?

Any design suggestions would be welcome!

View 3 Replies View Related

One DELETE Sql Statement To Delete From Two Tables

Aug 12, 2007

I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?

View 5 Replies View Related

Soft Delete In Table, Why Merge Agent Report Hards Delete On Table ?

Feb 1, 2007

Hi seniors

there are two tables involve in replication let say table1 and replicated table is also rep.table1.

we are not deleting records physically in table1 so only a bit in table1 has true when u want to delete a record but the strange thing is that replication agaent report that this is hard delete operation on table1 so download and report hard delete operation and delete the record in replicated table which is very crucial.

plz let me know where am i wrong and how i put it into right way.

there is no triggers on published tables and noother trigger is created on published table.

regards

Ahmad Drshen

View 6 Replies View Related

DRIVER SQL

Aug 4, 1998

I like to know where I can have the DRIVER for SQL ODBC for 16 bits.

Thank you.

View 1 Replies View Related

Db2 Driver For 64 Bit

Nov 29, 2006

Hi everyone,

Running both Sql25k and cluster to 64.
Is there a DB2 provider for 64 bit? If not, are there problems if you take 32 bit provider there?

Thanks a lot for your time,

View 4 Replies View Related

Btrieve Driver

May 10, 2001

Does anyone know where I can get a btrieve driver (possibly for free) that works on Win 2K? I want to run some quick tests.

TIA

AJ

View 1 Replies View Related

ODBCP Driver

Jun 25, 2001

When running a bcp command I recevie the error message
"ODBCBCP/DRIVER Version mismatch" normally that's
when the sqlsrv32.dll version is different from the bcp.exe.

To fix this problem do I install the latest MDAC ?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved