FMTONLY problems
- From: "Bob" <not provided>
- Date: Tue, 29 May 2007 12:43:47 +1200
Hi
I'm connecting to SQL Server 2005 using ADO 2.8
I'm capturing the data using SQL Server Profiler
Some of my queries are calling the database with FMTONLY every time they are
executed.
I expect this is because there is an error each time, as only part of the
query is being passed to the db. They still execute ok.
I assume that ADO is trying to get the metadata for the result set being
returned. Can anyone tell me how to stop it doing this, or make it work
correctly?
eg
SET FMTONLY ON IF NOT EXISTS (
SELECT SystemScanfileKey
FROM SystemScanfileScan
WHERE SystemScanfileKey = SET FMTONLY OFF
This gives an Exception (Error: 156, Severity: 15, State: 1)
The full query is then executed (and works correctly)
exec sp_executesql N'IF NOT EXISTS (
SELECT ScanfileKey
FROM ScanfileScan
WHERE ScanfileKey = @P1
AND ScanKey = @P2
)
BEGIN
INSERT INTO ScanfileScan
(ScanfileKey, ScanKey)
VALUES
(@P3, @P4)
SELECT 1
END
--ELSE
--SELECT 0
',N'@P1 int,@P2 int,@P3 int,@P4 int',48,8224,48,8224
Example 2:
SET FMTONLY ON INSERT INTO Scan (
Barcode, Command, PalletKey, LocationKey, OwnerKey,
LoadInKey, LoadOutKey, OrderKey, ProductID,
ProductKey, ScanProductId, ScanProductKey, TraceBatch, BatchID, Weight,
ProductionDate, SlaughterDate, ProductionPlant, SlaughterPlant, UnitID,
SerialNumber, TimeStamp, Pieces, ChangedTimeStamp, Category, Eligibility,
InStock
) VALUES (
SET FMTONLY OFF
Then:
exec sp_executesql N'INSERT INTO Scan (
Barcode, Command, PalletKey, LocationKey, OwnerKey,
LoadInKey, LoadOutKey, OrderKey, ProductID,
ProductKey, ScanProductId, ScanProductKey, TraceBatch, BatchID, Weight,
ProductionDate, SlaughterDate, ProductionPlant, SlaughterPlant, UnitID,
SerialNumber, TimeStamp, Pieces, ChangedTimeStamp, Category, Eligibility,
InStock
) VALUES (
@P1, @P2, @P3, @P4, @P5,
@P6, @P7, @P8, @P9,
@P10, @P11, @P12, @P13, @P14, @P15,
@P16, @P17, @P18, @P19, @P20,
@P21, @P22, @P23, GetDate(), @P24, @P25, @P26);
SELECT SCOPE_IDENTITY() AS NewKey;
',N'@P1 varchar(60),@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8
int,@P9 varchar(8),@P10 int,@P11 varchar(8),@P12 int,@P13 varchar(30),@P14
varchar(6),@P15 money,@P16 datetime,@P17 datetime,@P18 int,@P19 int,@P20
int,@P21 bigint,@P22 datetime,@P23 int,@P24 varchar(15),@P25 varchar(8),@P26
bit','C00008032S7028WWWWppppU1S7029P010XSSSSSSS',0,NULL,1,1,1,NULL,NULL,'33-800',1,'33-800',1,'','',2.0000,'May
28 2007 12:00:00:000AM','May 29 2007 12:00:00:000AM',610,0,1,8032,'May 29
2007 12:40:57:000PM',10,'','',1
.
- Follow-Ups:
- Re: FMTONLY problems
- From: Bob Barrows [MVP]
- Re: FMTONLY problems
- Prev by Date: Re: ADOX Keys.Append not working on Vista
- Next by Date: Re: FMTONLY problems
- Previous by thread: ADOX Keys.Append not working on Vista
- Next by thread: Re: FMTONLY problems
- Index(es):
Relevant Pages
|
