Different Behaviour Of A SP Called From SQL Query And From MS Access
Feb 2, 1999
Hi,
Please help me to elucidate a mistery !
I have a little SP in a database:
CREATE PROCEDURE XXXTest AS
DECLARE @a varchar(50)
SELECT @a = NULL
IF @a NOT IN ('x', 'y')
BEGIN
RAISERROR ('Error',16,1)
RETURN 0
END
RETURN 1
If I execute it from SQL Server, it returns 0. (after the error, of course).
If I execute it from MS ACCess through a Pass Through Query, it returns 0.
On the same server !!!
How could it to make me that ? I have been thought the SP is executed entirely on the server !!!
We have a view looking at all the columns of a table on a SQL server 7.0 db. When we link this view to MSAccess, we are seeing the old data which was there previously in the original table! not the latest data. We recompiled the table and views and also re-linked the view to MSAccess with no help,we still see the old data only.This is happening only for one of the views not for all.Any help??? sa.
Hi there, I wonder if one of you worthy folks can help me out with some strange behaviour exhibited by a piece of SQL. Its my first post here , so please be gentle. :)
Here is my simple example :-
<my test table>
create table test (ind int, message varchar(255))
insert into test (ind, message) values (1,'date=01/06/2006')
insert into test (ind, message) values (1,'date=20/12/2005') insert into test (ind, message) values (2,'test')
The first query is
select * from test t1 where t1.ind in (select max(ind) from test t2 where t2.ind = t1.ind and t2.message like 'date=%' )
fine.... 2 rows
second query
select * from test t1 where t1.ind =1 and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()
fine same 2 rows...
but If I try to combine the 2 clauses in
select * from test t1 where t1.ind in (select max(ind) from test t2 where t2.ind = t1.ind and t2.message like 'date=%' ) and convert(datetime, (SUBSTRING(Message, CHARINDEX('=',Message,0)+1, 10)),103) > getdate()
I get a Server: Msg 241, Level 16, State 1, Line 1 Syntax error converting datetime from character string.
I am seeming strange results with a query. I have two tables, lets call them Table1 and Table2. Table1 has an ID field, Table2 does not have an ID field. To be sure I wasn't blind, the query
'SELECT ID FROM Table2'
returns: Invalid column name 'ID'. OK. Now when I run the query
'SELECT * FROM Table1 WHERE ID IN (SELECT ID FROM Table2)'
it returns all the records from Table1.
What gives? Is this a bug, or am I missing something?
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.OleDb; using System.Collections;
namespace TimeTracking.DB { public class sql { OleDbConnection conn;
// //the constructor for this class, set the connectionstring // public sql() { DBConnectionstring ConnectToDB = new DBConnectionstring(); conn = ConnectToDB.MyConnection(); }
// // // public void UpdateEntry(int ID, string Week, string Year, string Date, string Project, string Action, string Time, string Comment) { int m_ID = ID; int m_Week = (Convert.ToInt32(Week)); int m_Year = (Convert.ToInt32(Year)); string m_Date = Date; string m_Project = Project; int m_ProjectID = new int(); string m_Action = Action; int m_ActionID = new int(); Single m_Time = (Convert.ToSingle(Time)); string m_Comment = Comment;
// //get the project ID from the database and store it in m_ProjectID // OleDbCommand SelectProjectID = new OleDbCommand("SELECT tblProject.ProjectID FROM tblProject" + " WHERE (((tblProject.Project) LIKE @Project))", conn);
// //get the action ID from the database and store it in m_ActionID // OleDbCommand SelectActionID = new OleDbCommand("SELECT tblAction.ActionID FROM tblAction" + " WHERE (((tblAction.Action) LIKE @Action))", conn);
finally { //close the connection if (conn != null) { conn.Close(); } } } } }
Code Snippet
The update statement is not working in my application, no error in C# and no error in ms-access. When I paste the update query into the ms-access query tool and replace the parameter values (@....) with real values, is will update the record.
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
This is the actual statement displayed from Response.Write in classic ASP. INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '06-012497',TaskNum,TaskDesc,TaskMemo,Account,'2006-Oct-3',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum = '000002' There are 4 records returned from the SELECT part of the statement. In some situations, 4 records are inserted to WOTasks table, in others, only 1 record is inserted. I can't find out why 1 record, instead of 4, record is inserted. A form page submits the form to the save page using post method. The above statement is contained in the save page. When one of the form textbox is filled, 1 record is inserted. When the textbox is not filled, 4 records are inserted. You may think the textbox has something to do with the behaviour. I also think so but the content of the textbox does not affect the sql statement. In both cases, the insert statement is the same. In the actual codes, only strings in quotes are variables and the rest are hardcoded. When I run the statement in SQL Server, 4 records are affected. No such problem when connected with Access.The actual code belowSub AddTask(ByVal proc, ByVal wonum) Dim sSQL sSQL = "INSERT INTO WOTasks (WoNum,TaskNum,TaskDesc,TaskMemo,Account,ModifyDate,Estimate,TaskHours,Unit,UnitCost,TotalCost) SELECT '" & wonum & _ "',TaskNum,TaskDesc,TaskMemo,Account,'" & curDate & "',1,TaskHours,Unit,UnitCost,TotalCost FROM Tasks WHERE procnum='" & proc & "'" 'Response.Write sSQL:Response.End conn.Execute sSQL, , 128 End Sub
I am using MSDE and Analysis Services (lastest packs) and the same installation on the same machine has been working great for the last 18 months or so untill yesterday. Whenever I try to open a DTS (in order to edit it) the machine just goes into a coma.... I have tried to re-start many times but of no use.
Can someone kindly guide me what should I look for in order to solve this.
When I run the command: exec master..xp_cmdshell 'NET USE' from the analyzer the box responds there are no entries in the list.
After that, I run the command: exec master..xp_cmdshell 'NET USE Z: /DELETE' after which the box responds with a "network connection could not be found." and that's all okay.
The weird thing is: exec master..xp_cmdshell 'NET USE Z: \MACHINESHARENAME' results in a "The local device name is already in use.".
The machine in this particular case is the box itself. I have no problem accessing other disks on other systems. I can see the share using the view command. There's no maximum on the share itself and I can connect to the share using another sql box with the same user.
I don't know why it won't budge, worked before like a charm. After six months or so it just stopped. Anyone seen/solved this behaviour?
Hello All,The following script is reproducing the problem assuming you haveNorthwind database on the server.Please note it gives you the error message on line 12.USE tempdbGOsp_addlinkedserver 'Test17'GOsp_setnetname 'Test17', @@SERVERNAMEGOIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_works]GOCREATE PROCEDURE This_works@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.OrdersSELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITgoIF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,N'IsProcedure') = 1)DROP PROCEDURE [dbo].[This_does_not]GOCREATE PROCEDURE This_does_not@UseLinkedServer bit = 0-- WITH RECOMPILE -- Does not helpASSET NOCOUNT ONIF @UseLinkedServer = 1 -- Linked ServerBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.OrdersENDELSE -- LocalBEGINIF EXISTS (SELECT 1 FROM dbo.sysobjects where id =object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)DROP TABLE dbo.Orders_TMPSELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?ENDBEGIN TRANSACTIONSelect 'Line 25'SELECT COUNT(*) FROM dbo.Orders_TMPCOMMITGOPRINT 'This_works'EXECUTE This_works 0PRINT ' 'PRINT 'This_does_not'EXECUTE This_does_not 0Thanks for any help or hint,Igor Raytsin
We have an application that executes a few queries against an SQL Server 2005 (64-bit) database. Since there can be several instances of the application running at any given time, and parts of the logic must be serialized, we've been using sp_getapplock and sp_releaseapplock. This has all been working fine since RC1 on which the system was released. However, after installing SP2 about a week ago, we have been having problems. The serialized portion of the code almost always stall now.
To see what is happening we've been using both Management Studio and Profiler. We have two applications running, let's call them A and B. Both create a prepared statement which begin with a call to sp_getapplock and ends with sp_releaseapplock. In between some tables are queried and inserts may be made in others. The accessed tables are never used anywhere else but in the serialized code. This is what is happening:
Application A: Calls sp_getapplock. Application A: Queries a table. Application B: Calls sp_getapplock. Application A: Inserts a row in a table. Application A: Calls sp_releaseapplock. Application B: Waits indefinitely (or at least more than 4 hours, after which we killed the spid).
Profiler cannot detect any deadlocks when this is happening. There are no blocking operations according to Management Studio. I can see the application lock having been set when I look at the spid for Application B in Management Studio.
Since this started to occur frequently after installing SP2 and had not been seen before, we are wondering if any changes has been made that could cause this behaviour? Has anyone else had problems using application locks, where a query would stall indefinitely waiting for the lock to be released? How then did you resolve it?
Any suggestions or ideas are welcome, Thanks, Lars
I were trying to achive paging through using a CTE etc, but ran into the following weither thing happening. The CTE allows me to use avariable as the ORder By field, although the CTE do not care at all what is in there? Have any one seen this or maybe can explain this?
USE AdventureWorks;
GO
DECLARE @SortExpression Varchar(50)
Set @SortExpression = 'SalesPersonID ASC';
WITH Sales_CTE (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT
ROW_NUMBER() OVER(Order by @SortExpression) RowNumber,
SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
Select * From Sales_CTE;
WITH Sales_CTE1 (RowNumber, SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT
ROW_NUMBER() OVER(Order by SalesPersonID ASC) RowNumber,
When I run the package from business solution environment, DBdate cast converts my date column (correctly) into a European date format dd/mm/yyyy and as such is inserted into sql server table.
When I run a package as a job, the same date in inserted into the database as mm/dd/yyyy.
So, if I have 3rd January 2007 in the source, in the first case i'll find 03/01/2007 in the database. When I run the package as a job, I find 01/03/2007 in the db.
The problem comes when I run different select statements - the 01/03/2007 behaves as if 1st March 2007
How can I avoid inserting of American data format into the db?
Hi to all! i had tried to install ads on a windows 5. i am able to connect to the pocket database. however when i trie to connect to the database that's on desktop i see a little window (probably a message) without anything and when i tap ok i see a message telling that the connection to the desktop database was not done! i had made the configuration before. can you tell me what is going wrong?
using System; using System.Data.SqlServerCe; using System.Data;
namespace ConsoleApplication1 { class Program { static void Main(string[] args) { using (SqlCeConnection conn = new SqlCeConnection(@"data source=d:mydb.sdf")) { conn.Open();
int counter = 0; using (SqlCeCommand cmd = new SqlCeCommand("products", conn)) {
using (SqlCeDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { counter++; } } }
conn.Close(); Console.WriteLine("Row count {0}", counter); //should show row count 2 but shows row count 3 instead }
Console.ReadKey(); } } } I'm setting the starting range to a date and a product name (PC 2) but it seems that the datareader only see the first array member and completely ignore the product name and show all 3 data instead of 2.
How can I execute or access a web page from a DTS package?
Both SQL server AND website are hosted on the same server (a Dual 2.4Gz Xeon with 2Gb RAM, RAID 5 etc)
I have 2 tables in SQL server 2000 that hold orders. These need to be posted into another table at a predefined time (ie: 4:30pm) and at the same time, access a remote address (a web service) and post certain elements of the order back.
Basically, can anyone help me out on how to execute a web page from a DTS.
I do NOT want to access a DTS from a webpage, which is all I'm finding at the moment.
Hello, Question is, when I try to create relationships in SQL 7 without using the wizard..upon adding the tables I get an errror message that says "Coinitialization has not been called". What does this mean?
I'm stumped on what to cal this, there might even be a method or pattern named for what i am trying to accomplish...
In the database, a number field is included on each table
When the DAL reads the record from the database, it is passed to the client - work is possibly done to the record and is sent back to the DAL for update.
A query is done against the table to retrieve the record again, the numbers are compared - if they don't match, it is assumed the record been modified by another user/thread/activity. An error is returned to the client stating the data has been changed.
if the numbers match, the record is updated with the number field being incremented by one.
what is this methodology called (beside crap :-) )
Hello, perhaps you guys have heard this before in the past, but here iswhat I'm looking for.I have a SQL 2000 table with Social security numbers. We need tocreate a Member ID using the Member's real SSN but since we are notallowed to use the exact SSN, we need to add 1 to each number in theSSN. That way, the new SSN would be the new Member ID.For example:if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.Sounds simply enough, but I can't seem to get it straight.I need this number to be created using a query, as this query is areport's record source.Again, any help would be appreciated it.
This seems like a basic question but I have not been able to find the answer in the help files or by searching this forum.
Is a trigger called for each row updated or is it called once for all rows updated?
for example if I have:
Code Snippet CREATE TRIGGER mytrigger
ON mytable AFTER UPDATE AS BEGIN
EXEC e-mail-me inserted, N'mytrigger', getdate() END
and I do this
Code Snippet UPDATE mytable SET mycolumn = N'whatever' WHERE ID > 5 AND ID <= 10
Assuming there is a record for each nteger value of ID, than will mytrigger run 5 times (once for each row updated) or one time (with inserted containing all 5 rows)?
When a column is evaluated against an UDF in a SELECT ... or WHERE ... It makes sense that the UDF is called for every row of the SELECT. But is it still true if the UDF is called in a subquery as below?
Code Snippet
SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) I've made a test and the SQL Profiler Trace shows that the UDF is called only once.
Can anyone confirm if my test is valid and most importantly that the UDF is called only once? FYI, I never use sub queries. This is to clarify a technical detail for our performance investigation.
Thank in advance for any help.
Here is the code to setup the test: USE NorthWind GO
CREATE TABLE dbo.UdfTest ( LineID int Identity(1,1) NOT NULL, AnyText varchar(200) COLLATE database_default NOT NULL ) GO
CREATE FUNCTION dbo.F_Bogus ( ) RETURNS @tab TABLE ( LineID int NOT NULL, AnyText varchar(100) COLLATE database_default NOT NULL) AS BEGIN INSERT @tab (LineID, AnyText) VALUES (1, 'UDF1') INSERT @tab (LineID, AnyText) VALUES (2, 'UDF2') INSERT @tab (LineID, AnyText) VALUES (3, 'UDF3') INSERT @tab (LineID, AnyText) VALUES (4, 'UDF4') INSERT @tab (LineID, AnyText) VALUES (5, 'UDF4')
RETURN END GO
Here is the capture of SQL Profiler when executing the statement: SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus())
SQL:BatchStarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543 SQLtmtStarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543 SPtarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.577 SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (1, 'UDF1') 51 2008-05-06 17:58:31.577 SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (2, 'UDF2') 51 2008-05-06 17:58:31.577 SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (3, 'UDF3') 51 2008-05-06 17:58:31.577 SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (4, 'UDF4') 51 2008-05-06 17:58:31.577 SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (5, 'UDF4') 51 2008-05-06 17:58:31.577 SPtmtCompleted -- F_Bogus RETURN 51 2008-05-06 17:58:31.577 SQLtmtCompleted SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543 SQL:BatchCompleted SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
We have this webiste which uses SQL express as database engine. Sometimes certain features of the website stop working. Like membership provider and other database related things. I have described the problem in more details here: http://forums.asp.net/t/1172253.aspx In consice the problem is: One query with fixed inputs does not always return the same results, though the data has not changed, you restart the SQL express and the problem resolives! I think thats a problem with SQL express, because when you restart SQL express everything starts working. Our database is kinda big. Like above 500 MB with up to 50 concurrent users. And our machine got a 3.2 CPU with 512 MB of ram. And our application is the only application runing there. What do you think please?
I have a scheduled job that inserts some records into a table. It fails with the following message, Violation of PRIMARY KEY constraint 'PK_FuturesOut'. Cannot insert duplicate key in object 'FuturesOut'. [SQLSTATE 23000] (Error 2627) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.
The strange thing about this is, if I copy the SQL statement from the job and paste it into Query Analyzer, it works without any modifications.
If anyone can explain this I would be most grateful.
My question: If I add an index to tblTable for the column B (not used in the view's WHERE clause, but used in the s-proc), will it have a performance improvement, because of the WHERE B > 6 on the view, assuming that this condition would benefit from the index if it were in the view itself.
I guess I could also put it this way: can an index on a column in a table improve the performance of a condition on a view using that table.
I use bcp fairly often in SQLServer2000 but have never run across this before. In a 512 SQLCHAR column containing notes, when two spaces are encountered (i.e. ' '), bcp is replacing ' ' with ' '.
I figured it was a problem with my format file, but I have not found enough specific info on the MSDN site to resolve this.
This is my bcp command:
bcp "SELECT DivCode,CommCode,ContactID,substring(Notes, 0, 512) from frep.dbo.BeBackExtract" queryout "D:BeBackDetail.dat" -f "D:BeBackDetail.fmt" -e "D:BeBackDetailErrors.dat" -U user -P pass -S server
Incidentally I also tried using the REPLACE function which seems to work great until trying to replace 2 spaces with 1 space, in which case it doesn't do anything.
