Problems Inserting Data- Am I doing something wrong?

I am having problem inserting data obtained from a adhoc sql query. Am I doing something wrong here (the select statement in below mentioned query works fine)

Insert

into customer(CustomerID, LastName, FirstName, BillingAddress, City,State, Country, Zipcode, PhoneNumber, EmailAddress)

select

CustomerID,(left(ContactName,charindex(' ', ContactName)-1))as LastName,

(right(

ContactName,charindex(' ',reverse(ContactName))-1))as FirstName,

Address

, City,Region,PostalCode,Country,Phonefrom Northwind.dbo.Customers

I am just doing it this way since Customer's name (both last name & first name ) are stored in a single column in Northwind database. I want to break it into two columns...The reason I am trying to get this to work is...it would be less of a work for me in inserting test data for my application

Any help would be of great

It looks like you list 10 rows to insert to, and then only select 9 rows.  Make sure the formatting is correct.

INSERT INTO table (Column1,Column2,Column3)VALUES (Value1,Value2,Value3)

This is what your query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber***nothing*** -> EmailAddress

Try something like this:

INSERT INTO customer
(CustomerID, LastName, FirstName, BillingAddress, City, State,
Country, Zipcode, PhoneNumber, EmailAddress)
SELECT
CustomerID,
(left(ContactName, charindex(' ', ContactName)-1))as LastName,
(right(ContactName, charindex(' ', reverse(ContactName))-1))as FirstName,
Address,
City,
Region, ***this goes into'State' ***
Country,
PostalCode,
Phone,
**EmailAddress** [insert correct column name]
FROM Northwind.dbo.Customers

|||

Hi Stew,

I corrected it in the actual query (when I executed) and it gives me this error message....any pointers??

Msg 8152, Level 16, State 13, Line 1

String or binary data would be truncated.

Thank you

|||Look at the size constraints of the columns you are inserting too and see if they make sense against what is being inserted. You may have a string field going into a char field, or something of the sort.|||

Stew312:

It looks like you list 10 rows to insert to, and then only select 9 rows.  Make sure the formatting is correct.

INSERT INTO table (Column1,Column2,Column3)VALUES (Value1,Value2,Value3)

This is what your query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber***nothing*** -> EmailAddress

Try something like this:

INSERT INTO customer
(CustomerID, LastName, FirstName, BillingAddress, City, State,
Country, Zipcode, PhoneNumber, EmailAddress)
SELECT
CustomerID,
(left(ContactName, charindex(' ', ContactName)-1))as LastName,
(right(ContactName, charindex(' ', reverse(ContactName))-1))as FirstName,
Address,
City,
Region, ***this goes into'State' ***
Country,
PostalCode,
Phone,
**EmailAddress** [insert correct column name]
FROM Northwind.dbo.Customers

Looks like the problem is this:

This is what my query is trying to do now:

FROM column ->INTO columnCustomerID -> CustomerIDLastName -> LastNameFirstName -> FirstNameAddress -> BillingAddressCity -> CityRegion -> StatePostalCode -> CountryCountry -> ZipcodePhone -> PhoneNumber

There is only one FROM COLUMN (ie., Contactname) but is repeated twice...I mean to say in

the Northwind customer database both the last name and first name are put in a single column

and I am trying to separate it into two colums (Lastname , Firstname).....Is it a wrong way of doing things.....I had been breaking my on this for couple of hours

your help is greatly appreciated

|||Too embrassing and a costly mistake (had to waste most of my time).........the order of columns I was trying to insert was wrong....though embrassing, I am sharing because some people (including me) tend to forget minor details while focusing on other major problems.....so moral of story....Pay attention to detail and don't forget basics.

Problems having moved from SQL 7 to SQL 2000

We ported our database 2 weeks ago. We have come across a problem with
one
stored procedure.
1) Running some queries / Procedures in Query Analyser return
"[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
The SQL Logs have the following errors whenever the procedure runs
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
I have tried to run the procedure from SQL Analyser on my client
machine and locally on the server. Both return the same error.
The stored procedure completes and the records appear in the relvent
tables. At best we get the errors described above and entries in teh
SQL log. The worst scenario we have had is each time the procedure
fired the AQL server services terminated unexpectedly disconencting
everyone from the SQL server and all databases (More of a problem I am
sure you will agree).
The stored procedure is outlined below. Running in SQL Analyser does
not display the printed debug messages although they are viewable in
the Stack dump.
SQL VERSION : Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec
17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
CREATE PROCEDURE spSaveNewCall
/*----*/
/* PROCEDURE : spSaveNewCall */
/* Description : saves a new call in database */
/*----*/
@.i_str_CustID varchar(50),
@.i_str_CustRef varchar(10),
@.i_str_ContactID int,
@.i_str_ProductCode varchar(6),
@.i_str_ModuleCode varchar(6),
@.i_str_Version varchar(10),
@.i_str_PatchLevel varchar(10),
@.i_str_EmergencyPatch varchar(10),
@.i_str_Environment varchar(4),
@.i_str_CallType varchar(100),
@.i_str_CallDescription text,
@.i_str_Priority varchar(1),
@.i_str_SLAclass varchar(10),
@.i_str_CustType varchar(15),
@.i_str_ModuleName varchar(40),
@.i_str_ProductName varchar(40),
@.i_str_ServerPlatform varchar(40)
AS
DECLARE @.l_str_CallID varchar(8)
DECLARE @.l_boo_calllog int
DECLARE @.l_boo_asgnmnt int
DECLARE @.l_boo_detail int
DECLARE @.l_boo_subset int
BEGIN TRANSACTION
print 'DEBUG : --update sequence number for anyone else trying to log
a call'
UPDATE HeatSeq
SET SeqValue = SeqValue + 1
WHERE SeqKey = 'CallID'
--get the next call id
SET @.l_str_CallID = (SELECT SeqValue AS NextCall FROM HEATSeq WHERE
SeqKey = 'CallID')
SET @.l_str_CallID = RIGHT('00000000' + @.l_str_CallID,8)
print 'DEBUG : --start logging call'
SET @.l_boo_calllog = 0
SET @.l_boo_asgnmnt = 0
SET @.l_boo_detail = 0
SET @.l_boo_subset = 0
INSERT INTO CallLog
( CallID,
CompanyRef,
CustID,
CallType,
CallStatus,
Tracker,
Priority,
CallDesc,
RecvdBy,
RecvdDate,
RecvdTime,
ModBy,
ModDate,
ModTime,
CallSource,
SLAClass,
Environment,
ProductCode,
ModuleCode,
OnHold,
ProductVersion,
PatchLevel,
EmergencyPatch,
CustType,
ProductModule,
Product,
DBPlatform,
SLA_ClockStatus,
SLA_CalcCBWarn,
SLA_CalcCloseWarn,
SLA_CalcClose,
SLA_CalcCB,
SLA_Status,
Weighting,
PriorityDesc,
ServerPlatform,
DTLastMod,
Escalated,
EscalationOrder
)
( SELECT @.l_str_CallID,
@.i_str_CustRef,
@.i_str_CustID,
@.i_str_CallType,
'Open',
'HSS',
@.i_str_Priority,
@.i_str_CallDescription,
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'Website',
@.i_str_SLAclass,
@.i_str_Environment,
@.i_str_ProductCode,
@.i_str_ModuleCode,
'F',
@.i_str_Version,
@.i_str_PatchLevel,
@.i_str_EmergencyPatch,
@.i_str_CustType,
@.i_str_ModuleName,
@.i_str_ProductName,
'Progress',
'SLA is Stopped',
SLA_WarnResponse,
SLA_WarnComplete,
SLA_TgtComplete,
SLA_TgtResponse,
'OK',
Weighting,
SLA_PriorityDesc,
@.i_str_ServerPlatform,
datediff(ss,'01-01-1970',getdate()),
'F',
0
FROM SLAMatrix,
Priority
WHERE SLA_Priority = Priority.Priority
AND SLA_Priority = @.i_str_Priority
AND SLA_Class = @.i_str_SLAclass
)
print 'DEBUG : --check calllog has been updated'
SET @.l_boo_calllog = @.@.ERROR
print 'DEBUG : --save the subset'
INSERT INTO Subset
( CustID,
CallID,
CustType,
EmailID,
Phone,
CompanyName,
Contact,
Ext,
CustRefReq,
OrchMgr,
Phone2,
Ext2,
PhoneDesc1,
PhoneDesc2,
ContactMethod,
Fax1,
Alert,
AccMgr,
ProjMgr,
Supported,
Mobile,
KeyCustomer,
ContactSeqNum
)
( SELECT @.i_str_CustID,
@.l_str_CallID,
CustType,
Email1,
Telephone1,
CustomerName,
ContactName,
Extension1,
CustRef,
FPOC,
Telephone2,
Extension2,
Tel1Description,
Tel2Description,
ContactMethod,
Fax1,
ALERT,
AccountManager,
ProjectManager,
Supported,
Mobile1,
KeyCustomer,
ContactSeqNum
FROM Contacts, Profile
WHERE ContactSeqNum = @.i_str_ContactID
AND Contacts.CustID = Profile.CustID
)
print 'DEBUG : --check subset has been updated'
SET @.l_boo_subset = @.@.ERROR
print 'DEBUG : --save the details'
INSERT INTO Detail
(
CallID,
Details
)
VALUES
(
@.l_str_CallID,
''
)
print 'DEBUG : --check detail has been updated'
SET @.l_boo_detail = @.@.ERROR
print 'DEBUG : --save the assignment'
INSERT INTO Asgnmnt
(
AssignedBy,
DateAssign,
TimeAssign,
GroupName,
CallID,
HEATSeq,
GroupEMail,
DTLastMod,
Assignee,
GroupDesc,
ResolveOrder,
WhoResolv
)
VALUES
(
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'WebUpdate',
@.l_str_CallID,
datediff(ss,'01-01-1970',getdate()),
'CustomerServices@.orchard-systems.co.uk',
datediff(ss,'01-01-1970',getdate()),
'HSS',
'New call logged on-line',
0,
''
)
print 'DEBUG : --check asgnmnt has been updated'
SET @.l_boo_asgnmnt = @.@.ERROR
IF @.l_boo_calllog = 0 AND @.l_boo_asgnmnt = 0 AND @.l_boo_detail = 0
AND @.l_boo_subset = 0
BEGIN
print 'DEBUG : --Transaction Commit'
COMMIT TRANSACTION
--return the call id
SELECT @.l_str_CallID AS NextCall
END
ELSE
BEGIN
print 'DEBUG : --Transaction Rollback'
ROLLBACK TRANSACTION
SELECT 'No Call Raised' AS NextCall
END
/*----*/
GOAt the very least, you need to correct your logic to accurately figure out
what is happening. You have a fundamental mistake in your error handling.
Try the following:
if object_id ('tempdb..#test') is not null
drop table #test
create table #test (test_id int not null)
insert #test (test_id) values (null)
print 'debug'
select @.@.error
insert #test (test_id) values (null)
select @.@.error
print 'debug'
go
Notice how the print statement changes the value of @.@.error. Erland has an
excellent discussion of error handling
(http://www.sommarskog.se/error-handling-II.html). Once you understand the
above, you should then correct the 2nd logic error. There would appear to
be no need to execute any insert statements that follow the first failed
insert statement. Yet your procedure just keeps blindly inserting until the
end. This only wastes server resources. You should also review any trigger
logic that will execute - this is a frequent problem area.
Inspite of the logic issues, the error log indicates that the fault is
within sql server. This requires assistance from MS to diagnose and
correct. The profiler might offer some insight - perhaps there is a problem
with parallelism. If so, you might be able to at least avoid the problem.
Perhaps the problem can be avoided entirely by correcting the logic flaws
alone.sql

Problems having moved from SQL 7 to SQL 2000

We ported our database 2 weeks ago. We have come across a problem with
one
stored procedure.
1) Running some queries / Procedures in Query Analyser return
"[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
The SQL Logs have the following errors whenever the procedure runs
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
I have tried to run the procedure from SQL Analyser on my client
machine and locally on the server. Both return the same error.
The stored procedure completes and the records appear in the relvent
tables. At best we get the errors described above and entries in teh
SQL log. The worst scenario we have had is each time the procedure
fired the AQL server services terminated unexpectedly disconencting
everyone from the SQL server and all databases (More of a problem I am
sure you will agree).
The stored procedure is outlined below. Running in SQL Analyser does
not display the printed debug messages although they are viewable in
the Stack dump.
SQL VERSION : Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec
17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
CREATE PROCEDURE spSaveNewCall
/*----*/
/* PROCEDURE : spSaveNewCall */
/* Description : saves a new call in database */
/*----*/
@.i_str_CustID varchar(50),
@.i_str_CustRef varchar(10),
@.i_str_ContactID int,
@.i_str_ProductCode varchar(6),
@.i_str_ModuleCode varchar(6),
@.i_str_Version varchar(10),
@.i_str_PatchLevel varchar(10),
@.i_str_EmergencyPatch varchar(10),
@.i_str_Environment varchar(4),
@.i_str_CallType varchar(100),
@.i_str_CallDescription text,
@.i_str_Priority varchar(1),
@.i_str_SLAclass varchar(10),
@.i_str_CustType varchar(15),
@.i_str_ModuleName varchar(40),
@.i_str_ProductName varchar(40),
@.i_str_ServerPlatform varchar(40)
AS
DECLARE @.l_str_CallID varchar(8)
DECLARE @.l_boo_calllog int
DECLARE @.l_boo_asgnmnt int
DECLARE @.l_boo_detail int
DECLARE @.l_boo_subset int
BEGIN TRANSACTION
print 'DEBUG : --update sequence number for anyone else trying to log
a call'
UPDATE HeatSeq
SET SeqValue = SeqValue + 1
WHERE SeqKey = 'CallID'
--get the next call id
SET @.l_str_CallID = (SELECT SeqValue AS NextCall FROM HEATSeq WHERE
SeqKey = 'CallID')
SET @.l_str_CallID = RIGHT('00000000' + @.l_str_CallID,8)
print 'DEBUG : --start logging call'
SET @.l_boo_calllog = 0
SET @.l_boo_asgnmnt = 0
SET @.l_boo_detail = 0
SET @.l_boo_subset = 0
INSERT INTO CallLog
( CallID,
CompanyRef,
CustID,
CallType,
CallStatus,
Tracker,
Priority,
CallDesc,
RecvdBy,
RecvdDate,
RecvdTime,
ModBy,
ModDate,
ModTime,
CallSource,
SLAClass,
Environment,
ProductCode,
ModuleCode,
OnHold,
ProductVersion,
PatchLevel,
EmergencyPatch,
CustType,
ProductModule,
Product,
DBPlatform,
SLA_ClockStatus,
SLA_CalcCBWarn,
SLA_CalcCloseWarn,
SLA_CalcClose,
SLA_CalcCB,
SLA_Status,
Weighting,
PriorityDesc,
ServerPlatform,
DTLastMod,
Escalated,
EscalationOrder
)
( SELECT @.l_str_CallID,
@.i_str_CustRef,
@.i_str_CustID,
@.i_str_CallType,
'Open',
'HSS',
@.i_str_Priority,
@.i_str_CallDescription,
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'Website',
@.i_str_SLAclass,
@.i_str_Environment,
@.i_str_ProductCode,
@.i_str_ModuleCode,
'F',
@.i_str_Version,
@.i_str_PatchLevel,
@.i_str_EmergencyPatch,
@.i_str_CustType,
@.i_str_ModuleName,
@.i_str_ProductName,
'Progress',
'SLA is Stopped',
SLA_WarnResponse,
SLA_WarnComplete,
SLA_TgtComplete,
SLA_TgtResponse,
'OK',
Weighting,
SLA_PriorityDesc,
@.i_str_ServerPlatform,
datediff(ss,'01-01-1970',getdate()),
'F',
0
FROM SLAMatrix,
Priority
WHERE SLA_Priority = Priority.Priority
AND SLA_Priority = @.i_str_Priority
AND SLA_Class = @.i_str_SLAclass
)
print 'DEBUG : --check calllog has been updated'
SET @.l_boo_calllog = @.@.ERROR
print 'DEBUG : --save the subset'
INSERT INTO Subset
( CustID,
CallID,
CustType,
EmailID,
Phone,
CompanyName,
Contact,
Ext,
CustRefReq,
OrchMgr,
Phone2,
Ext2,
PhoneDesc1,
PhoneDesc2,
ContactMethod,
Fax1,
Alert,
AccMgr,
ProjMgr,
Supported,
Mobile,
KeyCustomer,
ContactSeqNum
)
( SELECT @.i_str_CustID,
@.l_str_CallID,
CustType,
Email1,
Telephone1,
CustomerName,
ContactName,
Extension1,
CustRef,
FPOC,
Telephone2,
Extension2,
Tel1Description,
Tel2Description,
ContactMethod,
Fax1,
ALERT,
AccountManager,
ProjectManager,
Supported,
Mobile1,
KeyCustomer,
ContactSeqNum
FROM Contacts, Profile
WHERE ContactSeqNum = @.i_str_ContactID
AND Contacts.CustID = Profile.CustID
)
print 'DEBUG : --check subset has been updated'
SET @.l_boo_subset = @.@.ERROR
print 'DEBUG : --save the details'
INSERT INTO Detail
(
CallID,
Details
)
VALUES
(
@.l_str_CallID,
''
)
print 'DEBUG : --check detail has been updated'
SET @.l_boo_detail = @.@.ERROR
print 'DEBUG : --save the assignment'
INSERT INTO Asgnmnt
(
AssignedBy,
DateAssign,
TimeAssign,
GroupName,
CallID,
HEATSeq,
GroupEMail,
DTLastMod,
Assignee,
GroupDesc,
ResolveOrder,
WhoResolv
)
VALUES
(
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'WebUpdate',
@.l_str_CallID,
datediff(ss,'01-01-1970',getdate()),
'CustomerServices@.orchard-systems.co.uk',
datediff(ss,'01-01-1970',getdate()),
'HSS',
'New call logged on-line',
0,
''
)
print 'DEBUG : --check asgnmnt has been updated'
SET @.l_boo_asgnmnt = @.@.ERROR
IF @.l_boo_calllog = 0 AND @.l_boo_asgnmnt = 0 AND @.l_boo_detail = 0
AND @.l_boo_subset = 0
BEGIN
print 'DEBUG : --Transaction Commit'
COMMIT TRANSACTION
--return the call id
SELECT @.l_str_CallID AS NextCall
END
ELSE
BEGIN
print 'DEBUG : --Transaction Rollback'
ROLLBACK TRANSACTION
SELECT 'No Call Raised' AS NextCall
END
/*----*/
GOAt the very least, you need to correct your logic to accurately figure out
what is happening. You have a fundamental mistake in your error handling.
Try the following:
if object_id ('tempdb..#test') is not null
drop table #test
create table #test (test_id int not null)
insert #test (test_id) values (null)
print 'debug'
select @.@.error
insert #test (test_id) values (null)
select @.@.error
print 'debug'
go
Notice how the print statement changes the value of @.@.error. Erland has an
excellent discussion of error handling
(http://www.sommarskog.se/error-handling-II.html). Once you understand the
above, you should then correct the 2nd logic error. There would appear to
be no need to execute any insert statements that follow the first failed
insert statement. Yet your procedure just keeps blindly inserting until the
end. This only wastes server resources. You should also review any trigger
logic that will execute - this is a frequent problem area.
Inspite of the logic issues, the error log indicates that the fault is
within sql server. This requires assistance from MS to diagnose and
correct. The profiler might offer some insight - perhaps there is a problem
with parallelism. If so, you might be able to at least avoid the problem.
Perhaps the problem can be avoided entirely by correcting the logic flaws
alone.

Problems having moved from SQL 7 to SQL 2000

We ported our database 2 weeks ago. We have come across a problem with
one
stored procedure.
1) Running some queries / Procedures in Query Analyser return
"[Microsoft][ODBC SQL Server Driver][Shared
Memory]ConnectionCheckForData (CheckforData()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Connection Broken"
The SQL Logs have the following errors whenever the procedure runs
* Exception Address = 00000000
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 00000000
I have tried to run the procedure from SQL Analyser on my client
machine and locally on the server. Both return the same error.
The stored procedure completes and the records appear in the relvent
tables. At best we get the errors described above and entries in teh
SQL log. The worst scenario we have had is each time the procedure
fired the AQL server services terminated unexpectedly disconencting
everyone from the SQL server and all databases (More of a problem I am
sure you will agree).
The stored procedure is outlined below. Running in SQL Analyser does
not display the printed debug messages although they are viewable in
the Stack dump.
SQL VERSION : Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec
17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
CREATE PROCEDURE spSaveNewCall
/*----*/
/* PROCEDURE :spSaveNewCall */
/* Description : saves a new call in database */
/*----*/
@.i_str_CustID varchar(50),
@.i_str_CustRefvarchar(10),
@.i_str_ContactID int,
@.i_str_ProductCode varchar(6),
@.i_str_ModuleCode varchar(6),
@.i_str_Version varchar(10),
@.i_str_PatchLevel varchar(10),
@.i_str_EmergencyPatch varchar(10),
@.i_str_Environment varchar(4),
@.i_str_CallType varchar(100),
@.i_str_CallDescription text,
@.i_str_Priorityvarchar(1),
@.i_str_SLAclassvarchar(10),
@.i_str_CustTypevarchar(15),
@.i_str_ModuleNamevarchar(40),
@.i_str_ProductNamevarchar(40),
@.i_str_ServerPlatformvarchar(40)
AS
DECLARE @.l_str_CallID varchar(8)
DECLARE @.l_boo_calllog int
DECLARE @.l_boo_asgnmnt int
DECLARE @.l_boo_detail int
DECLARE @.l_boo_subset int
BEGIN TRANSACTION
print 'DEBUG : --update sequence number for anyone else trying to log
a call'
UPDATE HeatSeq
SET SeqValue = SeqValue + 1
WHERE SeqKey = 'CallID'
--get the next call id
SET @.l_str_CallID = (SELECT SeqValue AS NextCall FROM HEATSeq WHERE
SeqKey = 'CallID')
SET @.l_str_CallID = RIGHT('00000000' + @.l_str_CallID,8)
print 'DEBUG : --start logging call'
SET @.l_boo_calllog = 0
SET @.l_boo_asgnmnt = 0
SET @.l_boo_detail = 0
SET @.l_boo_subset = 0
INSERT INTO CallLog
(CallID,
CompanyRef,
CustID,
CallType,
CallStatus,
Tracker,
Priority,
CallDesc,
RecvdBy,
RecvdDate,
RecvdTime,
ModBy,
ModDate,
ModTime,
CallSource,
SLAClass,
Environment,
ProductCode,
ModuleCode,
OnHold,
ProductVersion,
PatchLevel,
EmergencyPatch,
CustType,
ProductModule,
Product,
DBPlatform,
SLA_ClockStatus,
SLA_CalcCBWarn,
SLA_CalcCloseWarn,
SLA_CalcClose,
SLA_CalcCB,
SLA_Status,
Weighting,
PriorityDesc,
ServerPlatform,
DTLastMod,
Escalated,
EscalationOrder
)
(SELECT@.l_str_CallID,
@.i_str_CustRef,
@.i_str_CustID,
@.i_str_CallType,
'Open',
'HSS',
@.i_str_Priority,
@.i_str_CallDescription,
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'Website',
@.i_str_SLAclass,
@.i_str_Environment,
@.i_str_ProductCode,
@.i_str_ModuleCode,
'F',
@.i_str_Version,
@.i_str_PatchLevel,
@.i_str_EmergencyPatch,
@.i_str_CustType,
@.i_str_ModuleName,
@.i_str_ProductName,
'Progress',
'SLA is Stopped',
SLA_WarnResponse,
SLA_WarnComplete,
SLA_TgtComplete,
SLA_TgtResponse,
'OK',
Weighting,
SLA_PriorityDesc,
@.i_str_ServerPlatform,
datediff(ss,'01-01-1970',getdate()),
'F',
0
FROMSLAMatrix,
Priority
WHERESLA_Priority=Priority.Priority
ANDSLA_Priority=@.i_str_Priority
ANDSLA_Class=@.i_str_SLAclass
)
print 'DEBUG : --check calllog has been updated'
SET @.l_boo_calllog = @.@.ERROR
print 'DEBUG : --save the subset'
INSERT INTO Subset
(CustID,
CallID,
CustType,
EmailID,
Phone,
CompanyName,
Contact,
Ext,
CustRefReq,
OrchMgr,
Phone2,
Ext2,
PhoneDesc1,
PhoneDesc2,
ContactMethod,
Fax1,
Alert,
AccMgr,
ProjMgr,
Supported,
Mobile,
KeyCustomer,
ContactSeqNum
)
(SELECT@.i_str_CustID,
@.l_str_CallID,
CustType,
Email1,
Telephone1,
CustomerName,
ContactName,
Extension1,
CustRef,
FPOC,
Telephone2,
Extension2,
Tel1Description,
Tel2Description,
ContactMethod,
Fax1,
ALERT,
AccountManager,
ProjectManager,
Supported,
Mobile1,
KeyCustomer,
ContactSeqNum
FROM Contacts, Profile
WHEREContactSeqNum=@.i_str_ContactID
ANDContacts.CustID = Profile.CustID
)
print 'DEBUG : --check subset has been updated'
SET @.l_boo_subset = @.@.ERROR
print 'DEBUG : --save the details'
INSERT INTO Detail
(
CallID,
Details
)
VALUES
(
@.l_str_CallID,
''
)
print 'DEBUG : --check detail has been updated'
SET @.l_boo_detail = @.@.ERROR
print 'DEBUG : --save the assignment'
INSERT INTO Asgnmnt
(
AssignedBy,
DateAssign,
TimeAssign,
GroupName,
CallID,
HEATSeq,
GroupEMail,
DTLastMod,
Assignee,
GroupDesc,
ResolveOrder,
WhoResolv
)
VALUES
(
'HSS',
CONVERT(varchar(10),GETDATE(),120),
CONVERT(varchar(8),GETDATE(),108),
'WebUpdate',
@.l_str_CallID,
datediff(ss,'01-01-1970',getdate()),
'CustomerServices@.orchard-systems.co.uk',
datediff(ss,'01-01-1970',getdate()),
'HSS',
'New call logged on-line',
0,
''
)
print 'DEBUG : --check asgnmnt has been updated'
SET @.l_boo_asgnmnt = @.@.ERROR
IF @.l_boo_calllog = 0 AND @.l_boo_asgnmnt = 0 AND @.l_boo_detail = 0
AND @.l_boo_subset = 0
BEGIN
print 'DEBUG : --Transaction Commit'
COMMIT TRANSACTION
--return the call id
SELECT @.l_str_CallID AS NextCall
END
ELSE
BEGIN
print 'DEBUG : --Transaction Rollback'
ROLLBACK TRANSACTION
SELECT 'No Call Raised' AS NextCall
END
/*----*/
GO
At the very least, you need to correct your logic to accurately figure out
what is happening. You have a fundamental mistake in your error handling.
Try the following:
if object_id ('tempdb..#test') is not null
drop table #test
create table #test (test_id int not null)
insert #test (test_id) values (null)
print 'debug'
select @.@.error
insert #test (test_id) values (null)
select @.@.error
print 'debug'
go
Notice how the print statement changes the value of @.@.error. Erland has an
excellent discussion of error handling
(http://www.sommarskog.se/error-handling-II.html). Once you understand the
above, you should then correct the 2nd logic error. There would appear to
be no need to execute any insert statements that follow the first failed
insert statement. Yet your procedure just keeps blindly inserting until the
end. This only wastes server resources. You should also review any trigger
logic that will execute - this is a frequent problem area.
Inspite of the logic issues, the error log indicates that the fault is
within sql server. This requires assistance from MS to diagnose and
correct. The profiler might offer some insight - perhaps there is a problem
with parallelism. If so, you might be able to at least avoid the problem.
Perhaps the problem can be avoided entirely by correcting the logic flaws
alone.

Problems getting all the data back with a XML query

I am having a nightmare trying to get to the bottom of this problem.
I have a stored procedure that has a user defined function to create a
select table from a csv string of IDs.
The function and the Stored procedure works fine and returns the XML,
Elements as required except that when i process this sp on the client most
of the data is missing.
If i run the query in the query analyser with the csv parameter and no FOR
XML output i get 72 records (10 unique resources, the rest are due to the
joins in the query)
If i run it with the FOR XML i get 6 lines of XML although it is oddly
truncated. If i cut and past it into xmlspy there are missing elements so i
can't verify exactly what i'm getting.
But when i run this in my web app and use am XMLTextReader to turn the
results into a string I get 2 records, when i was expecting 10. I can verify
this by pasting the xml into xmlspy.
I have tried SQLXML managed classes but can't get them to accept parameters,
i keep getting the message that the sp is expecting parameter despite using
createParameter against the command as per all the documentation, so i gave
up with this approach. I thought that this way i could populate a new
dataset and then bind a table to a datagrid control to see what was
returned. I can get it all to work bar the parameters.
Can anyone shed any light on why the results are significantly less than the
sp should return
and can someone give me an example of working with a parameterised sp and
SQLXML
Many thanks
John
VS2003, SQLXML sp2, XPpro sp2
I am confused about what you are trying to get.
FOR XML results a single XML stream if you use the supported APIs through
ADO.Net, ADO, or OLEDB's stream interfaces. Query Analyser is using ODBC and
thus shows the XML result junked into 2033 characters per row. You should
not use QA if you plan on further process the result.
So if you can provide us with some more information about what exactly you
do on the API level, we may be able to help...
Best regards
Michael
"John Mas" <mase@.btopenworld.org> wrote in message
news:G2x8d.316$Xy3.217@.newsfe6-gui.ntli.net...
>I am having a nightmare trying to get to the bottom of this problem.
> I have a stored procedure that has a user defined function to create a
> select table from a csv string of IDs.
> The function and the Stored procedure works fine and returns the XML,
> Elements as required except that when i process this sp on the client most
> of the data is missing.
> If i run the query in the query analyser with the csv parameter and no FOR
> XML output i get 72 records (10 unique resources, the rest are due to the
> joins in the query)
> If i run it with the FOR XML i get 6 lines of XML although it is oddly
> truncated. If i cut and past it into xmlspy there are missing elements so
> i can't verify exactly what i'm getting.
> But when i run this in my web app and use am XMLTextReader to turn the
> results into a string I get 2 records, when i was expecting 10. I can
> verify this by pasting the xml into xmlspy.
> I have tried SQLXML managed classes but can't get them to accept
> parameters, i keep getting the message that the sp is expecting parameter
> despite using createParameter against the command as per all the
> documentation, so i gave up with this approach. I thought that this way i
> could populate a new dataset and then bind a table to a datagrid control
> to see what was returned. I can get it all to work bar the parameters.
> Can anyone shed any light on why the results are significantly less than
> the sp should return
> and can someone give me an example of working with a parameterised sp and
> SQLXML
>
> Many thanks
> John
> VS2003, SQLXML sp2, XPpro sp2
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.xml:25072
Michael,
thanks I have sorted the problem eventually. Brain fade and not enough time
thinking it throgh. The problem was in the sql statement which took ages to
track down but there we go.
One question that is unanswered is how do i pass parameters to SQLXML with
stored procedures, not raw sql text?
i keep getting the message expecting parameter as per my post
thanks
john
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:Ok7uv9DrEHA.2796@.TK2MSFTNGP10.phx.gbl...
>I am confused about what you are trying to get.
> FOR XML results a single XML stream if you use the supported APIs through
> ADO.Net, ADO, or OLEDB's stream interfaces. Query Analyser is using ODBC
> and thus shows the XML result junked into 2033 characters per row. You
> should not use QA if you plan on further process the result.
> So if you can provide us with some more information about what exactly you
> do on the API level, we may be able to help...
> Best regards
> Michael
> "John Mas" <mase@.btopenworld.org> wrote in message
> news:G2x8d.316$Xy3.217@.newsfe6-gui.ntli.net...
>
|||To what exactly do you want to pass parameters? SQLXML is a general term and
the name of the mid-tier component.
Do you mean how to pass parameters into the SQL statement that uses FOR XML
via stored procs?
Thanks
Michael
"John Mas" <mase@.btopenworld.org> wrote in message
news:Fmz9d.270$Vd.96@.newsfe5-win.ntli.net...
> Michael,
> thanks I have sorted the problem eventually. Brain fade and not enough
> time thinking it throgh. The problem was in the sql statement which took
> ages to track down but there we go.
> One question that is unanswered is how do i pass parameters to SQLXML with
> stored procedures, not raw sql text?
> i keep getting the message expecting parameter as per my post
> thanks
> john
>
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:Ok7uv9DrEHA.2796@.TK2MSFTNGP10.phx.gbl...
>
|||Michael
here is the code that i am using
Dim strConn = "provider=SQLOLEDB;data source='....';initial
catalog=......;user id=sa;password=......"
Dim sxcCmd As New SqlXmlCommand(strConn)
Dim sdaDA As New SqlXmlAdapter(sxcCmd)
Dim sxpParam As SqlXmlParameter
Dim xr As Xml.XmlReader
Dim ds As New DataSet
With sxcCmd
..RootTag = "root"
..CommandType = SqlXmlCommandType.Sql
..CommandText = "Test2"
sxpParam = .CreateParameter
End With
With sxpParam
..Name = "@.IDs"
..Value = 5
End With
sdaDA.Fill(ds)
DataGrid1.DataSource = ds.Tables(0)
and here is the sp
ALTER PROCEDURE dbo.test2
(
@.IDs int
)
AS
/* SET NOCOUNT ON */
SELECT '<root>'
SELECT * FROM tblResource WHERE ResourceID=@.Ids
FOR XML AUTO, Elements
SELECT '</root>'
as you can see the sp has this parameter @.IDs but when i run the code the
error meesage says ' expecting parameter @.IDs'' yet i am passing it. If i
change the command text to a sql statement with a ? for the parameter then
it works.
Obviously i am missing something here, I presume the command type might be
wrongly set.
thanks
john
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:O45cbdZrEHA.1964@.TK2MSFTNGP12.phx.gbl...
> To what exactly do you want to pass parameters? SQLXML is a general term
> and the name of the mid-tier component.
> Do you mean how to pass parameters into the SQL statement that uses FOR
> XML via stored procs?
> Thanks
> Michael
> "John Mas" <mase@.btopenworld.org> wrote in message
> news:Fmz9d.270$Vd.96@.newsfe5-win.ntli.net...
>

problems executing a SELECT inside a TRAN against other computer

Hi
I have a problem executing a SELECT inside a TRAN against other computer

For example:
IN THE SQL Query Analizer of the COMPUTER2
1) this runs OK
BEGIN TRAN
SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE
COMMIT TRAN
2) this runs OK
SELECT * FROM COMPUTER2.DATABASE.DBO.TABLE
3) this runs OK
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
4) this runs bad
BEGIN TRAN
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
COMMIT TRAN

The problem is that TABLE locks and it does not finish.

I've been looking for similar ERRORS in Microsoft Support but I found nothing
I've uninstall and install de SQL server 2000 SP4 and the problems continues the same

Please, someone could help me, thanks

Assanta:

A couple of things: (1) make sure that DTC is on and (2) maybe try using BEGIN DISTRIBUTED TRAN instead of BEGIN TRAN when you know you will be explicitly involving an external machine.

Dave

|||What do you mean when you say "this runs bad"? Does the statement take a long time to execute? Are you getting any errors? What is the exact behavior? Can you just isolate the case where it doesn't work and specify what you are doing? Are you running #4 from Computer2 and so on?|||

Hi

Thanks for your answers

MUGAMBO

Yes, I have the DTC STARTED

Yes, I've also try with de DISTRIBUTED and the problem is the same

UMACHANDAR

you have all the answers in my first mail

What do you mean when you say "this runs bad"? The problem is that TABLE locks and it does not finish.

Does the statement take a long time to execute? The problem is that TABLE locks and it does not finish.

Are you getting any errors? The problem is that TABLE locks and it does not finish, so the aren't ERRORS

What is the exact behavior? The problem is that TABLE locks and it does not finish.

Can you just isolate the case where it doesn't work and specify what you are doing?

4) IN THE SQL Query Analizer of the COMPUTER2
BEGIN TRAN
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
COMMIT TRAN

Are you running #4 from Computer2?

IN THE SQL Query Analizer of the COMPUTER2

BEGIN TRAN
SELECT * FROM COMPUTER1.DATABASE.DBO.TABLE
COMMIT TRAN

If I try a TRAN on other computer all is OK, so the problem is only on this two computers, there must have something diferent on the configuration, but I can't know what it is.

Any help please?

|||You are not changing data. There is no reason to run a transaction around a SELECT statement.|||

Hi Tom, thanks for the response

Yes, of course. But is for doing the statement more simple, what you use inside the TRAN is the same, always i use a TRAN it blocks, with a select inside, or with a insert, or......

|||

It is still not clear what you mean when you say the table locks. This can mean several things. One, the query you are trying is taking a long time or the query is getting blocked by other transactions on the server. Also, I was looking for the exact error message you are receiving from the server? Are you simply cancelling the command since it is taking a long time? Are you getting error# 7391? Did you look at sp_lock output for example to see if this is due to locks on the table due to other transactions in the server? Next, you need to check the waitstats using DBCC SQLPERF (see MSKB) to determine if the wait is related to MSDTC for example. Here the configuration of the MSDTC on computer1 needs to be checked (what is the coordinator, does it have network setting enabled, did you check the MSDTC statistics from the MSDTC console - this will show how long each distributed transaction is taking and if anything got committed or aborted). Lastly, your first query in your original post does not start a distributed transaction because the server part is a local server (you are using 4-part name on the same server - loopback) so you can't compare the two. Below is a KB article that has some steps for troubleshooting the MSDTC connection part:

http://support.microsoft.com/default.aspx/kb/306212

|||"Explicit" transactions, using BEGIN TRAN/COMMIT, are very special and should not be used "generically" for every command. They should only be used in the case where you have multiple update statements which depend on each other.

Bascially, what you are doing in the select statement is causing a table lock (for no reason). This is probably causing a deadlock situation. Look at your activity log and see if something is blocking.|||

Hi Umachandar, thanks for the reply

Nobody use this server, only me and i use for test, so there are no more transactions on the server

I mean with block that the table is "IX" during 30 minutes, then i cancel the command. Without the TRAN the select lasts 1 second

thanks for the article, but I continue with the same problem. also I have tried to Add Value TurnOffRpcSecurity (http://support.microsoft.com/default.aspx/kb/827805) although are in the same domain

Executing DBCC SQLPERF (waitstats) there are 77 types of names but noone about MSDTC, there is one lock, is the type "LCK_M_S" and has the values "Requests=1", "Wait time=78" and "Signal Wait Time=16"

Executing DBCC SQLPERF (SpinLockStats) "LOCK_HASH" with 2 Collisions and 132 Spins

More info I hadn't said until now:

The instalation is made from an image, after the instalation i changed the server and SQL name, now the SQL and the Server name is the same, and uniques in the domain, but maybe the image installation could have any problem with the name of any low level thing?

I've been looking for any soft to test the MSDTC communication between 2 machines, I've readed in a forum something about the "DTCPing.exe", I've executed the file and I thing the problem is this "RPC server is ready WARNING:the CID values for both test machines are the same while this problem won't stop DTCping test"

The solution like I read in a forum (http://cogitativemind.homeip.net/archive/2005/08/01/487.aspx) is "For enabling your MSDTC service, you go to command prompt type: msdtc -uninstall , you'll not get any feedback that this has completed so just wait for a bit, then type: msdtc -install, again wait for a bit. I then rebooted my server and everything came up roses"

but this didn't works ok, so I tried (http://forums.asp.net/thread/1192335.aspx)

Run MSDTC -uninstall

Go into the registry and remove the MSDTC keys in HKLM/Software/Microsoft/MSDTC and

HKLM/System/CurrentControlSet/Services/MSDTC

Reboot

Run MSDTC -install

An this yes, this makes my distributed transctions works ok!!!

|||Glad you got it working. You need to be careful using imaging software since there is lot of metadata used by various applications that can be incorrectly handled. And this can differ from different releases of Windows / Service Pack / Software too. For SQL Server, you said you changed the server and SQL name - so did you use sp_dropserver & sp_addserver with 'local' option. Apart from this one, there are other places in MSDB that can reference server names - like master servers in SQLAgent configuration and so on. And you should actually run SQL Server SETUP again to ensure that the machine change is handled correctly. I believe that SETUP detects the name change and performs some actions. Note that you still need to do the sp_dropserver/sp_addserver part since the @.@.SERVERNAME value is stored & obtained from the system catalog. Search in MSKB for articles that may help the imaging process or name change of machine.

problems displaying images

Hi
I have a query that returns a companyname eg. "ACD". This info is then saved in a parameter.
On the logo (which depends on company), I use this parameter value to the pick up the right picture like this:

=Parameter!.company.Value &"_logo.jpg"

The image is there on the server an the path is correct, but the image is not displayed!
What is wrong?

Hi,

Under what account does your reporting services run? Have you checked that the account has the correct permissions to browse to the location. You can test it by setting a static image on your report and set its url to the location you want.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Yes, if i for example sets the path ACD_logo.jpg it works.
Dynamically it doesn't work. Neither in client or on the web.

|||I've had similar problems. Have you tried hosting the images on a website and using a URL to access the images instead of a local windows path?|||

No, i have not tried that.
Maybe I should.

Is there any way to get the base URL to the report?

Ex rs.base.url ?

|||

I think you're misunderstanding. Use a URL to access the LOGOs (not the report). In other words, host your images on a website and access them from there instead of from windows.

If you don't have a website, set up an account at http://photobucket.com/ and host your images there. It's quick and simple.

|||

Hi

It worked to put the images on a webserver.

I still though wonder why it doesn′t work when the image is an local external link e.g "ACD_logo.jpg" instead of ′the webserversolution eg. http://localhost:4000/reportimgs/ACD_logo.jpg

Thanks for the help!

/A

Problems creating full-text population schedule via Management Studio

I am trying to schedule an hourly incremental update of the FT catalog on an indexed query. I have tried creating the catalog and schedule on both a remote machine (my workstation) and directly on the server. I get different errors on the two machines. I have tried creating the scheduled updates during the catalog creation process as well as seperately. I am able to create the catalog and update it manually in both cases. I cannot get the schedules created. The text of the errors are as follows:

NOTE: We run on an alternate port

From my workstation during the catalog creation process:

===================================

Create full-text population schedule failed.

===================================

Apply to target server failed for Job 'Start Incremental View Population on SyllabiDBI.qry_FTSearch'. (Microsoft.SqlServer.Smo)

For help, click:
removed

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.Job.ApplyToTargetServer(String serverName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPopulationSchedule.ApplyIndexScheduleChanges(Server server, String databaseName, String tableName, String schemaName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexScheduleData.ApplyChanges(Server server, ServerConnection sqlConnInfo, FullTextIndexPopulationScheduleList scheduleList)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextWizardForm.PerformActions()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Agent.Job.ApplyToTargetServer(String serverName)

===================================

The specified @.server_name ('XXX.XXX.XXX.XXX,0000') does not exist. (.Net SqlClient Data Provider)

For help, click:
removed

Server Name: XXX.XXX.XXX.XXX,0000
Error Number: 14262
Severity: 16
State: 1
Procedure: sp_add_jobserver
Line Number: 88

Program Location:

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.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

Upon recieveing that error, the catalog is created but contains no schedule population events. Trying to add the population job via the FT properties dialog results in the following (which is almost identical to the previous):

===================================

Cannot execute changes.

===================================

Apply to target server failed for Job 'Start Incremental Table Population on SyllabiDBI.qry_FTSearch'. (Microsoft.SqlServer.Smo)

For help, click:
removed

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.Job.ApplyToTargetServer(String serverName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPopulationSchedule.ApplyIndexScheduleChanges(Server server, String databaseName, String tableName, String schemaName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexScheduleData.ApplyChanges(Server server, ServerConnection sqlConnInfo, FullTextIndexPopulationScheduleList scheduleList)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPropertiesSchedule.OnRunNow(Object sender)

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Agent.Job.ApplyToTargetServer(String serverName)

===================================

The specified @.server_name ('XXX.XXX.XXX.XXX,8081') does not exist. (.Net SqlClient Data Provider)

For help, click:
removed

Server Name: XXX.XXX.XXX.XXX,8081
Error Number: 14262
Severity: 16
State: 1
Procedure: sp_add_jobserver
Line Number: 88

Program Location:

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.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

If I immediately try to creat it again using the same method, it successfully creates the scheduled item and job but the job is malformed and will not run:

===================================

Start failed for Job 'Start Incremental Table Population on SyllabiDBI.qry_FTSearch'. (Microsoft.SqlServer.Smo)

For help, click:
removed

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()
at Microsoft.SqlServer.Management.SqlManagerUI.StartAgentJobs.StartJobAction.DoAction(ActionCollection actions, Int32 index)
at Microsoft.SqlServer.Management.SqlManagerUI.ActionCollection.DoWorkOnThread()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Agent.Job.StartImpl(String jobStepName)
at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()

===================================

Cannot start the job "Start Incremental Table Population on SyllabiDBI.qry_FTSearch" (ID 24C9505F-1388-46AD-AD4B-3024A8D3D154) because it does not have any job server or servers defined. Associate the job with a job server by calling sp_add_jobserver. (.Net SqlClient Data Provider)

For help, click:
removed

Server Name: XXX.XXX.XXX.XXX,0000
Error Number: 14256
Severity: 16
State: 1
Procedure: sp_start_job
Line Number: 51

Program Location:

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.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

Now, on the server itself, I recieve the follwoing when trying to create the schedule via the initial catalog setup:

===================================

Create full-text population schedule failed.

===================================

Object reference not set to an instance of an object. (SqlManagerUI)

Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPopulationSchedule.ApplyIndexScheduleChanges(Server server, String databaseName, String tableName, String schemaName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexScheduleData.ApplyChanges(Server server, ServerConnection sqlConnInfo, FullTextIndexPopulationScheduleList scheduleList)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextWizardForm.PerformActions()

And then when trying to add the scheduled jobs:

===================================

Object reference not set to an instance of an object. (SqlManagerUI)

Program Location:

at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPopulationSchedule.ApplyIndexScheduleChanges(Server server, String databaseName, String tableName, String schemaName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexScheduleData.ApplyChanges(Server server, ServerConnection sqlConnInfo, FullTextIndexPopulationScheduleList scheduleList)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPropertiesSchedule.OnRunNow(Object sender)
at Microsoft.SqlServer.Management.SqlMgmt.PanelExecutionHandler.Run(RunType runType, Object sender)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)

Again, when I try to run the resulting job, they are malformed and result in the a similar error as to the one I posted above:

===================================

Start failed for Job 'Start Incremental View Population on SyllabiDBI.qry_FTSearch'. (Microsoft.SqlServer.Smo)

For help, click:
removed

Program Location:

at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()
at Microsoft.SqlServer.Management.SqlManagerUI.StartAgentJobs.StartJobAction.DoAction(ActionCollection actions, Int32 index)
at Microsoft.SqlServer.Management.SqlManagerUI.ActionCollection.DoWorkOnThread()

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Agent.Job.StartImpl(String jobStepName)
at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()

===================================

SQLServerAgent Error: Request to run job Start Incremental View Population on SyllabiDBI.qry_FTSearch (from User XXX\Administrator) refused because the job has no job steps. (.Net SqlClient Data Provider)

For help, click:
removed

Server Name: XXX
Error Number: 22022
Severity: 16
State: 1

Program Location:

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.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)

I believe it is a problem with the Management Studio IDE, but I can't be sure. So far, I have been unable to find another way to create the schedule or jobs. If I can create the jobs and schedule them via T-SQL I will happily do so.

Any insight is greatly appreciated.

JC

I would like to know how you created this job, that is having this issue. It should work fine if you created under SQLAgent node.

Programatically you can use SMO objects (Look for JobServer in BOL)or agent SPs (look for sp_add_job and other related sps in BOL) to get this done

Thanks,

Gops Dwarak

sql

Problems creating a View

I have a query on one of my pages that is just too large to keep in the page, so I need to reference a stored view in sql. I'm kind of new to this, so I'm having trouble getting the syntax right. The query is just a simple select statement using the results of several textboxes as parameters. I know how to do the query inside an asp.net page, but when I move it to sql, I don't know how to reference the textbox value i.e. @.textbox. Here's what I have so far:
USE [Maindb]
GO
CREATE VIEW [tblMain_view] (@.textbox nvarchar(30)) ??
AS SELECT dbo.tblMain.Field1, ...
FROM dbo.tblMain
WHERE dbo.tblMain.Field1 = @.textbox and ...

First of all, I know that where I declare @.textbox is wrong, so where is the right place to declare it? Also, how do I reference the view from the webpage and do I still use:
cmd.SelectCommand.Parameters.Add . . .
in the page to establish the value. Anyone know a good tutorial on this. All the ones I've found were either in C# or didn't really apply. I need to know how to do this in VB. ThanksUSE [Maindb]
GO
CREATE VIEW [tblMain_view] (@.textbox nvarchar(30)) ??
AS SELECT dbo.tblMain.Field1, ...
FROM dbo.tblMain
WHERE dbo.tblMain.Field1 = @.textbox and ...

USE Your DB
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'Yourtable')
GO
CREATE VIEW Name
AS
SELECT col1,col2,
FROM your table
WHERE

Try the above statement with your info, your code is missing the WHERE clause before the create view. Run a search for create View in the BOL or use my email address in my profile and I will send you the VIEW tutorial I wrote a while back. Sorry cannot help you with VB I write C#. Hope this helps.

Kind regards,
Gift Peddiesql

Problems creating "such" query

Hi guys,

There is a problem now. I'm currently using Access2000, two tables A & B respectively. The both tables contain one columns of data. What I am needed to do now is a query that results the difference in the data.

Eg. Table A contains 1,2,3 | Table B contains 1,3,5

The similar numbers: 1, 3
The different numbers: 2, 5

My query results need to show 2 & 5 as they are not in both the tables.

Can anyone enlighten me on this? I've tried Unmatch Query Wizard. But my results only shows 2 but not 5 because the left join only applies on one table - A.

Thanks for help in advance! Hope to hear from you guys real soon. This is a urgent problem. = )Here are 2. One that uses outer joins and the other uses a combination of union, minus and intersect.

1)
select a.a
from A LEFT OUTER JOIN B on a.a = b.b
where b.b is null
union
select b.b
from A RIGHT OUTER JOIN B on a.a = b.b
where a.a is null;

2)
(union)
minus
(intersection*)

* - difference in other DBMS's|||hey, thanks r123456

i really got what i wan, but that was a trial for me, may i know is it possible for the query to work if there are ten tables?

same thing applies, i need to get all the different numbers from
table - a,b,c,d,e,f,g,h,i and j.

Is it possible? can help me on this? thanks a lot anyway!|||You're talking about an EXTREMELY intensive query there. Is it necessary to do this in SQL?

Essentially you need to ask access to compare A to B, A to C, A to D, A to E, A to F, B to C, B to D, B to E, C to E, C to D, E to D

etc etc

This will be obscene to do in SQL, I would highly recommend scripting a small function to do it for you, depending on how you intend to use the data.|||Your comment ->
"i need to get all the different numbers from
table - a,b,c,d,e,f,g,h,i and j."

I have assumed you wish to return a single set containing all numbers from accross all tables that do not appear in the intersection of all tables.

If this is the case then for more than say 2-3 tables I would personally write a recursive code block. Essentially for a given number of tables any value in table1 that does not appear in table2 can be immediately disregarded as it instantly fails the intersection.

Thus instead of constructing a 10-way join or 10-way union minus 10-way intersection accross all tables, by using a recursive procedure, one is able to compare a continously decreasing set of tuples with each remaining table, providing the majority of numbers do not form the intersection.

Example
----

Table 1 - Table 2 returns a set of 6 matching tuples. Now these 2 tables can be disregarded, and only the 6 resulting tuples need be continued. After comparing to the next table, only 4 rows are returned. As the procedure continues this set decreases in size.|||But can i know how do i go about doing a recursive code block? can i run it using a macro? i'm really new to Access2000, hope u guys will help me. i'm willing to try though.

r123456,
i understand what you are trying to say, basically, compare table 1 & 2 first, then get the different numbers from these two tables, then get the result to compare with other tables am i right?

regards,
wensheng|||As you are using Access not SQL Server I don't know to what extent Access supports these types of queries.

For example a typical example of recursion may be:

List all parts and subparts along with all their prices required to make a specific device. Ie. Device requires 5 main parts and each main part requires 10-20 sub parts etc. This query can not be written in pure SQL hence external code is required, on ORACLE this can be PL/SQL. SQL Server would probably contain a similar concept.

Access however, I could not tell you. If it does not, then the above queries can be modified. The outer join needs to be altered sligtly whereas the (union) / (intersect) can simply be duplicated. Consider the following,

TableA Table B
--- ----
1 1
3 3
5 4

The intersection can be constructed as an INNER JOIN instead (note, intersection compares all columns not just a candidate key, which in this example is suitable), giving the first 2 rows. An obvious next step

would be:
select (a, b)
from a, b
where (a, b) NOT IN (the intersection returned set), this however would return a set of unwanted combinations that remained from the CP.

To resolve this one of the following can be done,
1) Union the tables instead of a cartesian product to eliminate combinations, then where union column NOT IN any of the columns returned by the inner join.
2) Perform an inner join on all tables with the join condition being the collection of 10 NOT IN / NOT EXISTS operators to check their column with any column in the intersection.

Clearly the 2nd option is not an option. Regarding option 1, the (minus and intersection) clauses have essentially been re-written. The question can now be simplified.

Is it faster to use (minus & intersect) as opposed to where NOT IN (INNER JOIN). I would suggest INTERSECT.|||Hi r1234567 again, thanks for replying promptly.. thanks a lot. appreciated. but somehow i tried something strange.. i think this kind of stupid idea can only come from my brain, wanna take a look? just see below Query Statement:

select [A].[Object ID], [A].[Object Description]
from A LEFT OUTER JOIN B on [A].[Object ID]=[B].[Object ID]
where [B].[Object ID] is null;

UNION select [B].[Object ID], [B].[Object Description]
from A RIGHT OUTER JOIN B on [A].[Object ID] = [B].[Object ID]
where [A].[Object ID] is null;

UNION select [A].[Object ID], [A].[Object Description]
from A LEFT OUTER JOIN C on [A].[Object ID]=[C].[Object ID]
where [C].[Object ID] is null;

UNION select [C].[Object ID], [C].[Object Description]
from A RIGHT OUTER JOIN C on [A].[Object ID] = [C].[Object ID]
where [A].[Object ID] is null;

UNION select [B].[Object ID], [B].[Object Description]
from B LEFT OUTER JOIN C on [B].[Object ID]=[C].[Object ID]
where [C].[Object ID] is null;

UNION select [C].[Object ID], [C].[Object Description]
from B RIGHT OUTER JOIN C on [B].[Object ID] = [C].[Object ID]
where [B].[Object ID] is null;

Somehow, this gives the result of what i want man! thanks a lot! i really appreciate u guys for helping.. especially r1234567! really.|||That is a valid solution for only three tables, please be cautioned that for each table you add, the number of select statements you will need to include will grow exponentially.|||Hi Teddy,

i know the number of sql statements are going to grow exponentially.
However the few solutions other than sql queries i ain't sure. I'm not a programmer or what, that's why i dun understand.

I'm sorry. I did this Union Queries till the 7th table. when i reach 8th table, there was an error, " Query too complex! "

So i stopped at 7th table. i think that should be enough. = )|||I'm not up to speed on my vb lately as I've been working primarily in delphi lately, but I can outline a basic concept. I would do this using an array and a for loop. Essentially declare an array to store your final value list. Open your first data set with SELECT * FROM A and scroll through the record set to assign values to the array...

Essentially you would need to declare an array, assign the first table to the array, then compare it to the next table. Scroll through each record in the next table, if a match is found in the array, delete the matching field in the array, otherwise add the field to the array from the dataset. Then compare to the next table and do the same.

basically you'd have a for loop that would iterate and either append or remove depending on a match. I hope that makes any sort of sense heh..

Problems converting Access UNION query to SQL

I've got a Union query that works in Access but I'm unable to get it to work
querying a SQL Server. Access was using linked tables so the data was
really coming from SQL anyway. I want to make this query work within
Reporting Services, but I'm receiving the following error message:
ADO error: ORDER BY items must appear in the select list if the statement
contains a UNION operator.
I was able to take the two Select statements from Access and make good SQL
queries with them, but I'm unable to UNION them together eventhough it works
fine in Access. Here's my two queries I'm trying to Union:
SELECT TOP 100 PERCENT dbo.SOP30200.SLPRSNID, dbo.SOP30200.DOCAMNT
FROM dbo.SOP30200 LEFT OUTER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
dbo.RM00101.CUSTNMBR
WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
102)) AND (dbo.SOP30200.DOCDATE <= '2006-03-01') AND
(dbo.SOP30200.SOPTYPE = 3) AND (dbo.SOP30200.VOIDSTTS
= 0) AND (dbo.SOP30200.DOCID = 'inv')
ORDER BY dbo.SOP30200.SLPRSNID, dbo.SOP30200.CUSTNMBR
UNION
SELECT dbo.SOP30200.SLPRSNID, - (1 * dbo.SOP30200.DOCAMNT) AS expReturns
FROM dbo.SOP30200 LEFT OUTER JOIN
dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
dbo.viewSalesperson ON dbo.RM00101.SLPRSNID =
dbo.viewSalesperson.SLPRSNID
WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
102)) AND (dbo.SOP30200.DOCDATE <= CONVERT(DATETIME,
'2006-03-01 00:00:00', 102)) AND
(dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE = 4) AND
(dbo.SOP30200.DOCID = 'returns')order by of the first part of the union should be the problem.
Colin wrote:
>I've got a Union query that works in Access but I'm unable to get it to wor
k
>querying a SQL Server. Access was using linked tables so the data was
>really coming from SQL anyway. I want to make this query work within
>Reporting Services, but I'm receiving the following error message:
>ADO error: ORDER BY items must appear in the select list if the statement
>contains a UNION operator.
>I was able to take the two Select statements from Access and make good SQL
>queries with them, but I'm unable to UNION them together eventhough it work
s
>fine in Access. Here's my two queries I'm trying to Union:
>SELECT TOP 100 PERCENT dbo.SOP30200.SLPRSNID, dbo.SOP30200.DOCAMNT
>FROM dbo.SOP30200 LEFT OUTER JOIN
> dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
>dbo.RM00101.CUSTNMBR
>WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
>102)) AND (dbo.SOP30200.DOCDATE <= '2006-03-01') AND
> (dbo.SOP30200.SOPTYPE = 3) AND (dbo.SOP30200.VOIDSTTS
>= 0) AND (dbo.SOP30200.DOCID = 'inv')
>ORDER BY dbo.SOP30200.SLPRSNID, dbo.SOP30200.CUSTNMBR
>UNION
>SELECT dbo.SOP30200.SLPRSNID, - (1 * dbo.SOP30200.DOCAMNT) AS expReturn
s
>FROM dbo.SOP30200 LEFT OUTER JOIN
> dbo.RM00101 ON dbo.SOP30200.CUSTNMBR =
>dbo.RM00101.CUSTNMBR LEFT OUTER JOIN
> dbo.viewSalesperson ON dbo.RM00101.SLPRSNID =
>dbo.viewSalesperson.SLPRSNID
>WHERE (dbo.SOP30200.DOCDATE >= CONVERT(DATETIME, '2006-02-15 00:00:00',
>102)) AND (dbo.SOP30200.DOCDATE <= CONVERT(DATETIME,
> '2006-03-01 00:00:00', 102)) AND
>(dbo.SOP30200.VOIDSTTS = 0) AND (dbo.SOP30200.SOPTYPE = 4) AND
>(dbo.SOP30200.DOCID = 'returns')
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||Any suggestions on what I need to do to fix it?
"psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
news:5cf4869c57190@.uwe...
> order by of the first part of the union should be the problem.
> Colin wrote:
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200603/1|||Syntax is:
SELECT ... FROM ...
UNION (ALL)
SELECT ... FROM ...
ORDER BY...
(I also wonder why a TOP 100 PERCENT. That should return everything and
requires an ORDER BY to work. Get rid of the TOP 100 PERCENT> )
RLF
"Colin" <legendsfan@.nospam.nospam> wrote in message
news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Any suggestions on what I need to do to fix it?
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:5cf4869c57190@.uwe...
>|||Thanks for the help and feedback. I've got it working now. Is there a
reason why Visual Studio or any other design interface doesn't support
Design mode for UNION of two queries?
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uWwHb5uQGHA.2816@.TK2MSFTNGP15.phx.gbl...
> Syntax is:
> SELECT ... FROM ...
> UNION (ALL)
> SELECT ... FROM ...
> ORDER BY...
> (I also wonder why a TOP 100 PERCENT. That should return everything and
> requires an ORDER BY to work. Get rid of the TOP 100 PERCENT> )
> RLF
> "Colin" <legendsfan@.nospam.nospam> wrote in message
> news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
>|||A few notes that may help you (and others):
1. TOP 100 Percent ... ORDER BY was a workaround/hack/creative piece of SQL
to try to get the optimizer to sort values at a point in the query plan.
Unfortunately, it didn't really work in all cases, and it actually isn't
being honored at all in SQL 2005. So, please consider removing this from
your code in the future.
2. As you've seen from the other posts, ORDER BY should be applied to the
end of the complete statement to affect the presentation order of the
results returned to the client (and not each block of the UNION).
3. The column binding rules for Jet Red (Access's engine) never really
conformed to the ANSI standards. Given the installed base, it's not likely
to be changed. So, just be aware that the queries you may have in your
Access application are sometimes interpreted in slightly different ways.
Best of luck,
Conor Cunningham
SQL Server Query Optimization Development Lead
"Colin" <legendsfan@.nospam.nospam> wrote in message
news:etPkz6sQGHA.5924@.TK2MSFTNGP09.phx.gbl...
> Any suggestions on what I need to do to fix it?
> "psychodad71 via webservertalk.com" <u2248@.uwe> wrote in message
> news:5cf4869c57190@.uwe...
>

Problems connecting to server using Management Studio

Hi all,
I got this problem which makes me struggle in the last few days.
I am able to connect to my local server using a query analyzer or calling sql commands in a window application, but there's no way I can access it in Management Studio: by clicking "Connect" it just waits forever, and it doesn't respond to any other command (it must be shut by terminating the process)
Got any hints?

Thanks

Mauro

Try restarting the sql services may be your server is in hunged state......but i am not sure if that would solve the problem

|||Unfortunately, it doesn't do the trick... in fact, it's like Manag. Studio is able to connect to the server (I can see the list of databases in advanced options) but it doesn't show the result afterwards. Could it be a security/account problem?

Thanks

M|||Hi again,
just another hint trying to understand what's going on.
I just spent a couple of hours removing both the server and the client components and install it again, but the problems remains. Does configuration survive removal? Or should I look somewhere else?

Thanks

Mauro
|||From Management Studio are u trying to connect using Windows authentication or SQL authentication?
|||Both actually. And the result is the same connecting to a local or remote machine, that's why I am quite curious about this error (that in fact makes development harder... )

Mauro

Problems connecting to server using Management Studio

Hi all,
I got this problem which makes me struggle in the last few days.
I am able to connect to my local server using a query analyzer or calling sql commands in a window application, but there's no way I can access it in Management Studio: by clicking "Connect" it just waits forever, and it doesn't respond to any other command (it must be shut by terminating the process)
Got any hints?

Thanks

Mauro

Try restarting the sql services may be your server is in hunged state......but i am not sure if that would solve the problem

|||Unfortunately, it doesn't do the trick... in fact, it's like Manag. Studio is able to connect to the server (I can see the list of databases in advanced options) but it doesn't show the result afterwards. Could it be a security/account problem?

Thanks

M|||Hi again,
just another hint trying to understand what's going on.
I just spent a couple of hours removing both the server and the client components and install it again, but the problems remains. Does configuration survive removal? Or should I look somewhere else?

Thanks

Mauro
|||From Management Studio are u trying to connect using Windows authentication or SQL authentication?
|||Both actually. And the result is the same connecting to a local or remote machine, that's why I am quite curious about this error (that in fact makes development harder... )

Mauro

Problems calling an SP with SQL Server ( EXECUTE permission denied )

Hi,
I'm trying to test an SP with SQL Query Analyser from my client db an
it gives me this error :
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
denied on object 'sp_sdidebug', database 'master', owner 'dbo'.
I'm using Microsoft SQL SE SP4 and i'm new to it. I read on gg forums
that you have to execute sp_sdidebug with the 'legacy_on' parameter and
you need EXECUTE permission on sp_sdidebug. How do you do that !
Need help ! Thanks !
Olivier
To grant permission, you can use:
GRANT EXECUTE ON sp_sdidebug TO <YourUser>
To enable it for legacy clients, use:
EXEC sp_sdidebug 'legacy_on'
-Sue
On 14 Feb 2005 06:24:02 -0800, "OliE" <olie.rej@.gmail.com>
wrote:

>Hi,
>I'm trying to test an SP with SQL Query Analyser from my client db an
>it gives me this error :
>[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permission
>denied on object 'sp_sdidebug', database 'master', owner 'dbo'.
>I'm using Microsoft SQL SE SP4 and i'm new to it. I read on gg forums
>that you have to execute sp_sdidebug with the 'legacy_on' parameter and
>you need EXECUTE permission on sp_sdidebug. How do you do that !
>Need help ! Thanks !
>Olivier
sql

Problems calling an SP with SQL Server ( EXECUTE permission denied )

Hi,
I'm trying to test an SP with SQL Query Analyser from my client db an
it gives me this error :
[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permissio
n
denied on object 'sp_sdidebug', database 'master', owner 'dbo'.
I'm using Microsoft SQL SE SP4 and i'm new to it. I read on gg forums
that you have to execute sp_sdidebug with the 'legacy_on' parameter and
you need EXECUTE permission on sp_sdidebug. How do you do that !
Need help ! Thanks !
OlivierTo grant permission, you can use:
GRANT EXECUTE ON sp_sdidebug TO <YourUser>
To enable it for legacy clients, use:
EXEC sp_sdidebug 'legacy_on'
-Sue
On 14 Feb 2005 06:24:02 -0800, "OliE" <olie.rej@.gmail.com>
wrote:

>Hi,
>I'm trying to test an SP with SQL Query Analyser from my client db an
>it gives me this error :
>[Microsoft][ODBC SQL Server Driver][SQL Server]EXECUTE permissi
on
>denied on object 'sp_sdidebug', database 'master', owner 'dbo'.
>I'm using Microsoft SQL SE SP4 and i'm new to it. I read on gg forums
>that you have to execute sp_sdidebug with the 'legacy_on' parameter and
>you need EXECUTE permission on sp_sdidebug. How do you do that !
>Need help ! Thanks !
>Olivier

Problems after SP4 - 100%CPU time

We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of the
databases runs at 100% CPU when ever a query it made. Nothing else has
changed! Before SP4 the queries are quite small and took only 1-2 seconds to
run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
If I copy the database to another server with SP3 on it, it works fine. If I
then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds.
The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN with
RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
temp, msdb, etc And monitoring them there seems to be no disk problems,
queues are short, etc
Can anyone help? either hot fix, uninstall (don't have SP3 backup of master,
etc), sp changes.
Cheers
nigelDid you run update_statistics , reindex database.
Maybe recompile stored procedures!
Greetz
--
I drank alot of beer and ended up in the police department database.
Drank more beer and learned SQL in the dark hours.
DELETE FROM offenders WHERE Title=''MrAA'' AND Year=2006;
I love SQL
"Nigel" wrote:

> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of th
e
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
I
> then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds
.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN wit
h
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of maste
r,
> etc), sp changes.
> Cheers
> nigel|||Thanks.
I've run these manually and as part of the daily maintenance plan. The
database also has Auto update and create statistics.
At present the part where it all goes mad does not use any SP, but is called
via a JDBC Java client directly.
Task Manager view can be seen here.
http://www.callacomp.co.uk/sql/orion%20cpu.jpg
Performance monitor SP3 (live)
http://www.callacomp.co.uk/sql/sp3mon.jpg
Performance monitor SP4(test machine)
http://www.callacomp.co.uk/sql/sp4mon.jpg
Nigel
Underpaid and working for the NHS.
So help me and save lives!
"Hate_orphaned_users" wrote:

> Did you run update_statistics , reindex database.
> Maybe recompile stored procedures!
> Greetz|||Nigel wrote:
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of th
e
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
I
> then install SP4 on this machine it now runs at 100% CPU for 30-50 seconds
.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN wit
h
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of maste
r,
> etc), sp changes.
> Cheers
> nigel
Have you reviewed the execution plan for one of the problem queries?
Did you update statistics (manually, not automatically) when you moved
the database?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||It sounds like the query plan has changed so you may need to tune or change
that query slightly so that it gets a better plan.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:85602962-D3D7-4951-8E08-4C70D67C4855@.microsoft.com...
> We've recently moved our databases from SQL 2000 SP3 to SP4. Now one of
> the
> databases runs at 100% CPU when ever a query it made. Nothing else has
> changed! Before SP4 the queries are quite small and took only 1-2 seconds
> to
> run and maybe 10% CPU time, now it 100% CPU time and 30-50 seconds.
> If I copy the database to another server with SP3 on it, it works fine. If
> I
> then install SP4 on this machine it now runs at 100% CPU for 30-50
> seconds.
> The system is a clustered 2003R2 with SQL2000 connected to a fibre SAN
> with
> RAID10 disks for the databases, 1 for the logs, 5 for the SQL EXE, 5 for
> temp, msdb, etc And monitoring them there seems to be no disk problems,
> queues are short, etc
> Can anyone help? either hot fix, uninstall (don't have SP3 backup of
> master,
> etc), sp changes.
> Cheers
> nigel|||I've updated statistics -sp_updatestats
I've updated all the indexes DBCC DBREINDEX(@.TableName,' ',90)
The process uses no SP all queries are suppled from the client using JDBC,
doesn't this mean that the query plan is updated as and when. Certainly afte
r
a reboot? Or am I wrong?
I've run PSSDiag but I'm no expert at looking at the traces files. Either a
little help or lots of reading to do?
Regatds
NIgel|||It may very well generate a new query plan each and every time you execute
it. But that does not mean it will generate a good one or even a different
one. If it generated a bad plan the last time you executed it and the
conditions or parameters have not changed chances are it will generate a bad
one again and again and again. Some times the conditions are such or the
data is such that the optimizer thinks it has the right plan but ends up
with a bad one for your conditions. By changing the query slightly ( an IN
to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a different
and in this case hopefully better plan.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:5B61E38E-18CF-4735-B15E-AC81C2460D35@.microsoft.com...
> I've updated statistics -sp_updatestats
> I've updated all the indexes DBCC DBREINDEX(@.TableName,' ',90)
> The process uses no SP all queries are suppled from the client using JDBC,
> doesn't this mean that the query plan is updated as and when. Certainly
> after
> a reboot? Or am I wrong?
> I've run PSSDiag but I'm no expert at looking at the traces files. Either
> a
> little help or lots of reading to do?
> Regatds
> NIgel|||OK Cheers, I start looking into this. So does this mean that SP4 has changed
how query plans are calculated?
--
Nigel
Underpaid and working for the NHS.
So help me and save lives!
"Andrew J. Kelly" wrote:

> It may very well generate a new query plan each and every time you execute
> it. But that does not mean it will generate a good one or even a different
> one. If it generated a bad plan the last time you executed it and the
> conditions or parameters have not changed chances are it will generate a b
ad
> one again and again and again. Some times the conditions are such or the
> data is such that the optimizer thinks it has the right plan but ends up
> with a bad one for your conditions. By changing the query slightly ( an IN
> to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a different
> and in this case hopefully better plan.
>
> --
> Andrew J. Kelly SQL MVP
>|||Every service pack and or edition has the potential to change query plans to
some extent and Sp4 is no exception. Most of the time it is due to fixing
bugs and occasionally a query plan may be affected by it. This does not mean
all of them will change.
Andrew J. Kelly SQL MVP
"Nigel" <Nigel@.discussions.microsoft.com> wrote in message
news:0DD973D6-5D6C-4633-B533-350115E0477C@.microsoft.com...[vbcol=seagreen]
> OK Cheers, I start looking into this. So does this mean that SP4 has
> changed
> how query plans are calculated?
> --
> Nigel
> Underpaid and working for the NHS.
> So help me and save lives!
>
> "Andrew J. Kelly" wrote:
>|||Hi, you need at least apply postSp hotfix (2187) and check again.
Regards,
Oleg.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uOSI3EpPHHA.1252@.TK2MSFTNGP02.phx.gbl...
> It may very well generate a new query plan each and every time you execute
> it. But that does not mean it will generate a good one or even a different
> one. If it generated a bad plan the last time you executed it and the
> conditions or parameters have not changed chances are it will generate a
> bad one again and again and again. Some times the conditions are such or
> the data is such that the optimizer thinks it has the right plan but ends
> up with a bad one for your conditions. By changing the query slightly ( an
> IN to an EXISTS or an EXISTS to a JOIN etc) you can wind up with a
> different and in this case hopefully better plan.
>
> --
> Andrew J. Kelly SQL MVP
> "Nigel" <Nigel@.discussions.microsoft.com> wrote in message
> news:5B61E38E-18CF-4735-B15E-AC81C2460D35@.microsoft.com...
>

Problems accessing Oracle Linked Server data

Hi all,
Long time programmer/jack-of-all-trades. Using a linked server set up
in SQL Server 2000, connected to an Oracle 9i instance. I need to
query specific rows of data in several large Oracle tables from
several SQL Server stored procedures via an already defined linked
server using the MS Oracle data provider.
If you're reading this I'm sure you know, there are issues here.
1) OpenQuery : You cannot use variables when using the OpenQuery
method of retrieving data through the link. Basic select <column
name>
or select * queries work fine but you cannot restrict with a where
unless the condition is hard coded. So I will be forced to either
throw the data into a temp table or a cursor, neither of which I want
to do.
2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
name.schema_name.table_name
In this case, the queries execute, but Oracle number datatypes throw
an error as follows:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
column.
Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column
'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
'"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
130 at compile time and 5 at run time].
I did quite a bit of poking around, and tried many permutations of
syntax, including considering the "lazy schema validation" for the
linked server definition, which is not an option for me or in SQL 2k,
and making changes to the datatypes in Oracle, which is also not an
option for me, all to no avail.
Any viable options or potential solutions would be greatly
appreciated.
Thanks,
Kmazthemarkfords@.yahoo.com wrote:
> Hi all,
> Long time programmer/jack-of-all-trades. Using a linked server set up
> in SQL Server 2000, connected to an Oracle 9i instance. I need to
> query specific rows of data in several large Oracle tables from
> several SQL Server stored procedures via an already defined linked
> server using the MS Oracle data provider.
> If you're reading this I'm sure you know, there are issues here.
> 1) OpenQuery : You cannot use variables when using the OpenQuery
> method of retrieving data through the link. Basic select <column
> name>
> or select * queries work fine but you cannot restrict with a where
> unless the condition is hard coded. So I will be forced to either
> throw the data into a temp table or a cursor, neither of which I want
> to do.
> 2) Fully qualified Linked Server Syntax : linked_server_name.catalog_
> name.schema_name.table_name
> In this case, the queries execute, but Oracle number datatypes throw
> an error as follows:
> Server: Msg 7356, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' supplied inconsistent metadata for a
> column.
> Metadata information was changed at execution time.
> OLE DB error trace [Non-interface error: Column
> 'AZ_EMPLOYEE_ID' (compile-time ordinal 1) of object
> '"CAPUBLISH"."CHNL_C_EMPL_ROSTER"' was reported to have a DBTYPE of
> 130 at compile time and 5 at run time].
> I did quite a bit of poking around, and tried many permutations of
> syntax, including considering the "lazy schema validation" for the
> linked server definition, which is not an option for me or in SQL 2k,
> and making changes to the datatypes in Oracle, which is also not an
> option for me, all to no avail.
>
> Any viable options or potential solutions would be greatly
> appreciated.
>
> Thanks,
> Kmaz
>
Hi,
Have you looked at this link - http://support.microsoft.com/kb/314520
It describes how you can use variables in a OPENQUERY statement.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator