Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Server: Msg 229, Level 14, State 5???


Server: Msg 229, Level 14, State 5, Procedure sp_monitor, Line 167
EXECUTE permission denied on object 'sp_monitor', database 'master', owner 'dbo'.


How do I fix this? which roles, or permission is that? Is there a
easier way to find out solutions for these problems, as books online
does not seem to provide much help.

Thanks,

Vijay


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Server: Msg 11, Level 16, State 1
IN THE SQL SERVER 2000 AND IN ALSO IN 2005  WHEN GOING FOR THE LOGGING IN TO THE SQL SERVER USING A PUBLIC IP, AND GIVING THE SQL SERVER AUTENTICATION IT IS GIVING THE FOLLOWING ERROR

Server: Msg 11, Level 16, State 1[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]General network error. Check your network documentation.

THE SAME ISSUE HAPPENING WITH THE ODBC CONNECTION. CAN ANYONE EXPLAIN ME WITH THE FOLLOWING ERROR

DO NOT GIVE A SUGGESTION OF REINSTALLATION, BECAUSE TRIED THAT ONE TOO

REGARDS

CHAITANYA BABU YANAMADALA

View Replies !   View Related
Server: Msg 209, Level 16, State 1, Line 6 HELP
Simple SQL Error Can anyone help for a new starter. Error MessageServer: Msg 209, Level 16, State 1, Line 6 Ambiguous column name 'CustomerID'.Codeselect CustomerID, ContactName, OrderId from CustomersInner Join Orders OnCustomers.CustomerID = Orders.CustomerIDDBMS = SQL Server 2000 SP 3aDatabase = NorthwindI have no idea why i receive this error

View Replies !   View Related
Server: Msg 107, Level 16, State 2, Line 1
Can anybody help me with this syntax?
select
PHN.N_PHN_ID 'ID',
PHN.N_PHN_AREACD 'AREAD1',
PHN.N_PHN_PREFIX 'PREFIXD1',
PHN.N_PHN_NUMBER 'NBRD1',
PHN.N_PHN_EXTENTN 'EXTD1',
PHN2.N_PHN_AREACD 'AREAD2',
PHN2.N_PHN_PREFIX 'PREFIXD2',
PHN2.N_PHN_NUMBER 'NBRD2',
PHN2.N_PHN_EXTENTN 'EXTD2'
from Donor_Visit2 DV2 RIGHT OUTER JOIN NAT_PHN_DB_REC PHN
ON DV2.COUNT_INSTID = PHN.N_PHN_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHN2
ON DV2.COUNT_ID = PHN2.PHN.N_PHN_ID
order by PHN.N_PHN_ID

here is the error message:
Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'PHN2.PHN' does not match with a table name or alias name used in the query.

View Replies !   View Related
Server: Msg 208, Level 16, State 1, Line 1
Hello All,

This table was created by coldfusion and has been given all priviliges to public and I am in the public group but I still cant select from it. It gives me this error message: Server: Msg 208, Level 16, State 1, Line 1. Help!

Thanks in Advance.

View Replies !   View Related
Server: Msg 536, Level 16, State 3, Line 1
Hi, I would appreciate your help. I donot know why I am getting this error when I run this query :
if (select SUBSTRING(de_ftpdownload,1,CHARINDEX('.',de_ftpdow nload,0)-1)as de_ftpdownload from vendor_invoice)
<> (select stamp_number from vendor_invoice)
select 'no'
else
select 'yes'

I get this Error
Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.
----
yes
(1 row(s) affected)
I would appreciate your hlep

Al

View Replies !   View Related
Server: Msg 3910, Level 16, State 1
I am receiving the error, Server: Msg 3910, Level 16, State 1, Line 20 'Transaction context in use by another session', when running a stored procedure. The stored procedure inserts into a table from a database on another server. Anybody ever seen this.

View Replies !   View Related
Server: Msg 18452, Level 14, State 1
Hi All,

I am facing a very strange problem in one of my SQL2000 instance.

Actually, In one of my 2000 instance 'A', I have linked another 2000 instance 'B' (server 'B' is running under windows authentication) and I am a member of a group (SQL_Group) and that group is listed in sysadmin of both 'A' and 'B' Instances. When I am trying to run a simple select query from server 'A' to linked server 'B'

i.e. select top 2 * from [<Server_Name> <Instance_ Name>].master. dbo.sysobjects

It is giving me an error.

Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection.

But rest of the members of same group (SQL_Group) are able to execute this query. Can anyone please let me know why this issue is only for me even I am having the same permissions as others??

Any help will be highly appreciated. thanks a lot to all of you.

 

Rizwan...........

View Replies !   View Related
SQL Server Msg 1105, Level 17, State 2, Line 1
Dear (and mighty) all:I backed up a database (SQL server 7.0) and tried to restore it onanother system (SQL Server 2000). This is not the first time I'm doingthis and never had a problem before:-- in the source dbBACKUP db_icoaraci TO DISK = 'C:TEMPBACKUP-ICOARACI.DAT'--in the destination dbRESTORE db_icoaraci FROM DISK='E:TEMPBACKUP-ICOARACI.DAT'WITH MOVE='ICOARACI_DAT' TO 'E:DBICOARACI.MDF',MOVE='ICOARACI_LOG' TO 'E:DBICOARACI.LDF'Today, to my surprise, the destination SQL returned the followingerror:Processed 25592 pages for database 'db_icoaraci', file 'ICOARACI_DAT'on file 1.Processed 1 pages for database 'db_icoaraci', file 'ICOARACI_LOG' onfile 1.Server: Msg 1105, Level 17, State 2, Line 1Could not allocate space for object '(SYSTEM table id: 6)' in database'db_icoaraci' because the 'PRIMARY' filegroup is full.Server: Msg 3013, Level 16, State 1, Line 1RESTORE DATABASE is terminating abnormally.I searched the list and a found a few posts refering to the same error,but none in the exactly same scenario.I tried to restore the database with NORECOVERY, and, before restoringthe log, increase its size (ALTER DATABASE ... etc), but SQL Serversays that the DB couldn't be open because it's in the middle of arestore...I'll appreciate any help.Regards,Branco.

View Replies !   View Related
Server: Msg 2511, Level 16, State 1, Line 1
Dear All participants.

My distribution log file size is reached upto 33GB, What I should do?

Any clue or helps are too appreciable.

Thanks
R.Mall

View Replies !   View Related
Server: Msg 1101, Level 17, State 10, Line 8
If we run the query below with the values of 'rml' and 'ra' it runs but if we use 'cca' and 'cc' we get this error:

Server: Msg 1101, Level 17, State 10, Line 8
Could not allocate new page for database 'TEMPDB'. There are no more pages available in filegroup DEFAULT. Space can be created by dropping objects, adding additional files, or allowing file growth.

Here is the query

Declare @Level_1 varchar(3),
@Level_2 varchar(2)

Select @Level_1 = 'cca'
Select @Level_2 = 'cc'

select distinct tblAsset.State, tblAsset.County, tblAsset.Plant, tblAsset.Account,
tblAsset.Acq_Yr, tblAsset.Acq_Pd, tblAsset_Book.Cost_Original,
tblCounty_Names.County_Name, CER..tblLocation.Location,
tblGL_Account.Account_Desc

from tblAsset, tblAsset_Book, tblCounty_Names, CER..tblLocation, tblGL_Account
where tblAsset_Book.Level_1 = @Level_1
and tblAsset.Level_1 = tblAsset_Book.Level_1
and tblAsset_Book.Level_2 = @Level_2
and tblAsset.Level_2 = tblAsset_Book.Level_2
and tblGL_Account.Company = tblAsset.Level_2
and CER..tblLocation.SBU = tblGL_Account.Company
and tblCounty_Names.County = tblAsset.County
and CER..tblLocation.County = tblCounty_Names.County
and CER..tblLocation.Code = tblAsset.Plant
and tblGL_Account.Account = tblAsset.Account
and tblAsset_Book.Book_Number = '1'
order by tblAsset.State

View Replies !   View Related
Server:Msg 1105,Level 17,State 2,Line9
Server:Msg 1105,Level 17,State 2,Line9
Coule not allocate space to the object 'Tutorial' on 'Mytest1db'
in database because the 'PRIMARY' file group is full.

When I run a query to insert records into a table....i'm getting this error.Please Help....

View Replies !   View Related
Help : Server: Msg 8152, Level 16, State 9, Line 1
When I try to update one table from another I get the following error
message, Does anyone know what may be the cause of the error :

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

Thanks

KUL

View Replies !   View Related
Server: Msg 8645, Level 17, State 1, Line 1
We have VC++ / ODBC / Desktop SQL Server 7.0. Our program runs 9 select queries from the front-end thro' ODBC. If we set the min memory to 0 MB or 16 MB (use dynamic configuration of SQL Enterprise Manager in a 64 MB Ram machine) the program fails with the above error. "A time out occurred while waiting for memory resources to execute the query. Re-run the query".

If we set the min memory to 20MB and above, the program works fine. The result set is approx 90000 rows. In another 128 MB RAM machine a bigger query runs only at 128 MB min memory setting. Any other setting fails. Our worry is the more the result set should we keep changing the min memory setting. Does dynamic config of memory ever work ? We have played enough with the parameters of query wait time (s), mim memory per query, SQL timeout etc...

The same query works fine thro' query analyser and also in a client / server mode. It repeatedly fails in a SQL server desktop environment. Is this problem with C++ (or) ODBC (or) SQL Server.

PLEASE HELP !!!

Thanks
Gopal

View Replies !   View Related
Server: Msg 7619, Level 16, State 1, Line 2
Can somebody help me with following problem:
I have implemented fulltext-search on a table an when i´m running a query
like the following I get an error message which is not documented in
books-online.
Error-handling is impossible too.
So what can I do:

/*-----------------------------
use pubs
SELECT au_lname, state FROM authors WHERE CONTAINS(state, "*or*")
IF @@ERROR <> 0
print "We love undocumented bugs!"
-----------------------------*/
Server: Msg 7619, Level 16, State 1, Line 2
The Query contained only ignored words.

The error via ADO looks like that:
An Error has occurred

The Error Number is:
-2147217900
[Microsoft][ODBC SQL Server Driver][SQL Server]The Query contained only
ignored words.

View Replies !   View Related
Server: Msg 2537, Level 16, State 43, Line 1
i have sp4 install. Anyone know how to fix it?

Server: Msg 2537, Level 16, State 43, Line 1
Table error: Object ID 1376776012, index ID 0, page (1:56868), row 2. Record check (Valid SqlVariant) failed. Values are 7 and 0.
Server: Msg 2537, Level 16, State 1, Line 1 ... so on

=============================
http://www.sqlserverstudy.com

View Replies !   View Related
Server: Msg 8152, Level 16, State 6, Line 196
I am experiencing some frustration in regards to parsing an email that I need to work with.
(I don't have control over the format of the email I am receiving)
 
Any help would be appreciated. 
Thank you
Jake
 
Here is the error I get, when I use the SQL Query Analyzer to process my Stored Procedure.  (Not every email gets this error, just some, others process just fine.)

String or binary data would be truncated.
The statement has been terminated.
Stored Procedure: eResponse.dbo.spParser
 Return Code = -6
 
My Code is as follows:
 
CREATE PROCEDURE spParser
@_id nvarchar(50)
AS
--assumes the email is no longer than 4000 characters.  if it might be longer, bump this number up.
-- Common Varriables
DECLARE @body nvarchar(4000)
DECLARE @i int
DECLARE @phone nvarchar(50)
DECLARE @phonestart int
DECLARE @phoneend int
DECLARE @email nvarchar(50)
DECLARE @emailstart int
DECLARE @emailend int
DECLARE @comments nvarchar(3000)
DECLARE @commentsstart int
DECLARE @commentsend int
DECLARE @OrigID varchar(50)
-- Jose Ole Variables
DECLARE @firstname nvarchar(50)
DECLARE @firstnamestart int
DECLARE @firstnameend int
DECLARE @lastname nvarchar(50)
DECLARE @lastnamestart int
DECLARE @lastnameend int
DECLARE @address1 nvarchar(50)
DECLARE @address1start int
DECLARE @address1end int
DECLARE @address2 nvarchar(50)
DECLARE @address2start int
DECLARE @address2end int
DECLARE @city nvarchar(50)
DECLARE @citystart int
DECLARE @cityend int
DECLARE @state nvarchar(5)
DECLARE @statestart int
DECLARE @stateend int
DECLARE @zip nvarchar(15)
DECLARE @zipstart int
DECLARE @zipend int
DECLARE @country nvarchar(50)
DECLARE @countrystart int
DECLARE @countryend int
DECLARE @phone2 nvarchar(50)
DECLARE @phone2start int
DECLARE @phone2end int
DECLARE @productname nvarchar(50)
DECLARE @productnamestart int
DECLARE @productnameend int
DECLARE @upccode nvarchar(50)
DECLARE @upccodestart int
DECLARE @upccodeend int
DECLARE @datecode nvarchar(50)
DECLARE @datecodestart int
DECLARE @datecodeend int
DECLARE @purchaseat nvarchar(50)
DECLARE @purchaseatstart int
DECLARE @purchaseatend int
DECLARE @purchasecity nvarchar(50)
DECLARE @purchasecitystart int
DECLARE @purchasecityend int
DECLARE @datepurchased nvarchar(50)
DECLARE @datepurchasedstart int
DECLARE @datepurchasedend int
DECLARE @dateopened nvarchar(50)
DECLARE @dateopenedstart int
DECLARE @dateopenedend int
DECLARE @subject nvarchar(50)
DECLARE @subjectstart int
DECLARE @subjectend int
-- Windosr Variables
DECLARE @name nvarchar(50)
DECLARE @namestart int
DECLARE @nameend int
DECLARE @company nvarchar(50)
DECLARE @companystart int
DECLARE @companyend int

--First replace all ASCII carriage returns and line feeds and assign the whole chunk of data to a variable.
SET @body = (select replace( REPLACE(convert(varchar(4000), message),CHAR(10),' '),CHAR(13),'') from message where mailid = @_id)
--below is the sample syntax for the individual replacements of line feeds and carriage returns, but it's combined into one statement above.
--SELECT REPLACE(@jb,CHAR(10),'')  as firstpass
--SELECT REPLACE(@jb,CHAR(13),'')  as secondpass
IF left(@body,47) = 'Fromubject:contact info sent from samplesite.com'
--If this is true.. then it is a sample company.
BEGIN
--The 12 in the start is compensating for the 12 Characters of "First Name: "
--Below is collecting First Name
SET @firstnamestart = (CHARINDEX('First name:',@body) + 11)
SET @firstnameend = (CHARINDEX('Last name:',@body) - 1)
SET @firstname = SUBSTRING(@body,@firstnamestart,@firstnameend-@firstnamestart)
----Below is collecting Last Name
SET @lastnamestart = (CHARINDEX('Last name:',@body) + 10)
SET @lastnameend = (CHARINDEX('Address 1:',@body) - 1)
SET @lastname = SUBSTRING(@body,@lastnamestart,@lastnameend-@lastnamestart)
--Below is collecting Address1
SET @address1start = (CHARINDEX('Address 1:',@body) + 10)
SET @address1end = (CHARINDEX('Address 2:',@body) - 1)
SET @address1 = SUBSTRING(@body,@address1start,@address1end-@address1start)
--Below is collecting Address2
SET @address2start = (CHARINDEX('Address 2:',@body) + 10)
SET @address2end = (CHARINDEX('City:',@body) - 1)
SET @address2 = SUBSTRING(@body,@address2start,@address2end-@address2start)
--Below is collecting city information
SET @citystart = (CHARINDEX('City:',@body) + 5)
SET @cityend = (CHARINDEX('State/Prov',@body) -1)
SET @city = SUBSTRING(@body,@citystart,@cityend-@citystart)
--Below is collecting state information
SET @statestart = (CHARINDEX('State/Province:',@body) + 15)
SET @stateend = (CHARINDEX('Zip/Postal',@body) -1)
SET @state = SUBSTRING(@body,@statestart,@stateend-@statestart)
--Below is collecting zip information
SET @zipstart = (CHARINDEX('Zip/Postal Code:',@body) + 16)
SET @zipend = (CHARINDEX('Country',@body) -1)
SET @zip = SUBSTRING(@body,@zipstart,@zipend-@zipstart)
--Below is collecting country information
SET @countrystart = (CHARINDEX('Country:',@body) + 8)
SET @countryend = (CHARINDEX('E-mail address',@body) -1)
SET @country = SUBSTRING(@body,@countrystart,@countryend-@countrystart)
--Below is collecting email information
SET @emailstart = (CHARINDEX('E-mail address:',@body) + 15)
SET @emailend = (CHARINDEX('Daytime',@body) -1)
SET @email = SUBSTRING(@body,@emailstart,@emailend-@emailstart)
--Below is collecting phone information
SET @phonestart = (CHARINDEX('Daytime Phone:',@body) + 14)
SET @phoneend = (CHARINDEX('Evening Phone:',@body) -1)
SET @phone = SUBSTRING(@body,@phonestart,@phoneend-@phonestart)
--Below is collecting phone2 information
SET @phone2start = (CHARINDEX('Evening Phone:',@body) + 14)
SET @phone2end = (CHARINDEX('Product Name',@body) -1)
SET @phone2 = SUBSTRING(@body,@phone2start,@phone2end-@phone2start)
--Below is collecting Product Name information
SET @productnamestart = (CHARINDEX('Product Name:',@body) + 12)
SET @productnameend = (CHARINDEX('UPC Code:',@body) -1)
SET @productname = SUBSTRING(@body,@productnamestart,@productnameend-@productnamestart)
--Below is collecting UPC Code information
SET @upccodestart = (CHARINDEX('UPC Code:',@body) + 9)
SET @upccodeend = (CHARINDEX('Date Code:',@body) -1)
SET @upccode = SUBSTRING(@body,@upccodestart,@upccodeend-@upccodestart)
--Below is collecting Date Code information
SET @datecodestart = (CHARINDEX('Date Code:',@body) + 10)
SET @datecodeend = (CHARINDEX('Purchased At',@body) -1)
SET @datecode = SUBSTRING(@body,@datecodestart,@datecodeend-@datecodestart)
--Below is collecting Purchase At information
SET @purchaseatstart = (CHARINDEX('Purchased At',@body) + 26)
SET @purchaseatend = (CHARINDEX('Purchase City:',@body) -1)
SET @purchaseat = SUBSTRING(@body,@purchaseatstart,@purchaseatend-@purchaseatstart)
--Below is collecting Purchase City information
SET @purchasecitystart = (CHARINDEX('Purchase City:',@body) + 14)
SET @purchasecityend = (CHARINDEX('Date Purchased',@body) -1)
SET @purchasecity = SUBSTRING(@body,@purchasecitystart,@purchasecityend-@purchasecitystart)
--Below is collecting Date Purchased information
SET @datepurchasedstart = (CHARINDEX('Date Purchased:',@body) + 15)
SET @datepurchasedend = (CHARINDEX('Date Opened',@body) -1)
SET @datepurchased = SUBSTRING(@body,@datepurchasedstart,@datepurchasedend-@datepurchasedstart)
--Below is collecting Date Opened information
SET @dateopenedstart = (CHARINDEX('Date Opened:',@body) + 12)
SET @dateopenedend = (CHARINDEX('E-mail Subject:',@body) -1)
SET @dateopened = SUBSTRING(@body,@dateopenedstart,@dateopenedend-@dateopenedstart)
--Below is collecting Email Subject information
SET @subjectstart = (CHARINDEX('E-mail Subject:',@body) + 15)
SET @subjectend = (CHARINDEX('Comments:',@body) -1)
SET @subject = SUBSTRING(@body,@subjectstart,@subjectend-@subjectstart)
--Below is collecting message information
SET @commentsstart = (CHARINDEX('Comments:',@body) + 10)
SET @commentsend = len(@body)
SET @comments = SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart)
--below is the part that actually writes the data out to the Output table
INSERT INTO OutputEmails
 (FirstName, LastName, Address1, Address2, City, State, Zip, Country, eMail, Phone, Phone2, ProductName, UPCCode, DateCode, PurchaseAt, PurchaseCity, DatePurchased, DateOpened, Subject, Comments, OrigID)
SELECT   
  ltrim(SUBSTRING(@body,@firstnamestart,@firstnameend-@firstnamestart)) as FirstName,
  ltrim(SUBSTRING(@body,@lastnamestart,@lastnameend-@lastnamestart)) as LastName,
  ltrim(SUBSTRING(@body,@address1start,@address1end-@address1start)) as Address1,
  ltrim(SUBSTRING(@body,@address2start,@address2end-@address2start)) as Address2,
  ltrim(SUBSTRING(@body,@citystart,@cityend-@citystart)) as City,
  ltrim(SUBSTRING(@body,@statestart,@stateend-@statestart)) as State,
  ltrim(SUBSTRING(@body,@zipstart,@zipend-@zipstart)) as Zip,
  ltrim(SUBSTRING(@body,@countrystart,@countryend-@countrystart)) as Country,
  ltrim(SUBSTRING(@body,@emailstart,@emailend-@emailstart)) as eMail,
  ltrim(SUBSTRING(@body,@phonestart,@phoneend-@phonestart)) as Phone,
  ltrim(SUBSTRING(@body,@phone2start,@phone2end-@phone2start)) as Phone2,
  ltrim(SUBSTRING(@body,@productnamestart,@productnameend-@productnamestart)) as ProductName,
  ltrim(SUBSTRING(@body,@upccodestart,@upccodeend-@upccodestart)) as UPCCode,
  ltrim(SUBSTRING(@body,@datecodestart,@datecodeend-@datecodestart)) as DateCode,
  ltrim(SUBSTRING(@body,@purchaseatstart,@purchaseatend-@purchaseatstart)) as PurchaseAt,
  ltrim(SUBSTRING(@body,@purchasecitystart,@purchasecityend-@purchasecitystart)) as PurchaseCity,
  ltrim(SUBSTRING(@body,@datepurchasedstart,@datepurchasedend-@datepurchasedstart)) as DatePurchased,
  ltrim(SUBSTRING(@body,@dateopenedstart,@dateopenedend-@dateopenedstart)) as DateOpened,
  ltrim(SUBSTRING(@body,@subjectstart,@subjectend-@subjectstart)) as Subject,
  ltrim(SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart)) as Comments,
  @_id as OrigID
FROM         dbo.message
WHERE     (mailId = @_id)
END
ELSE BEGIN
--below prints the whole value in the debugger (SQL Query Analyzer)
--print @body
--below prints the length of the whole value
--print len(@body)
--below prints the location of 'Callers Name:'
--Print  CHARINDEX('Name: ',@body)
--Below is collecting the Name information
--The 6 in the start is compensating for the 6 Characters of "Name: "
SET @namestart = (CHARINDEX('Name:',@body) + 5)
SET @nameend = (CHARINDEX('Email:',@body) - 1)
SET @name = SUBSTRING(@body,@namestart,@nameend-@namestart)
--Below is collecting email information
SET @emailstart = (CHARINDEX('Email:',@body) + 6)
SET @emailend = (CHARINDEX('Title:',@body) -1)
SET @email = SUBSTRING(@body,@emailstart,@emailend-@emailstart)
--Below is collecting Company information
SET @companystart = (CHARINDEX('Company:',@body) + 8)
SET @companyend = (CHARINDEX('Phone Number:',@body) -1)
SET @company = SUBSTRING(@body,@companystart,@companyend-@companystart)
--Below is collecting phone information
SET @phonestart = (CHARINDEX('Phone Number:',@body) + 13)
SET @phoneend = (CHARINDEX('Comments:',@body) -1)
SET @phone = SUBSTRING(@body,@phonestart,@phoneend-@phonestart)
--Below is collecting message information
SET @commentsstart = (CHARINDEX('Comments:',@body) + 9)
SET @commentsend = len(@body)
SET @comments = SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart)
--below is the part that actually writes the data out to the Output table
INSERT INTO OutputEmails
 (Name, eMail, Company, Phone, Comments, OrigID)
SELECT  
  ltrim(SUBSTRING(@body,@namestart,@nameend-@namestart)) as Name,
  ltrim(SUBSTRING(@body,@emailstart,@emailend-@emailstart)) as eMail,
  ltrim(SUBSTRING(@body,@companystart,@companyend-@companystart)) as Company,
  ltrim(SUBSTRING(@body,@phonestart,@phoneend-@phonestart)) as Phone,
  ltrim(SUBSTRING(@body,@commentsstart,@commentsend-@commentsstart)) as Comments,
  @_id as OrigID
FROM         dbo.message
WHERE     (mailId = @_id)
END
--At the very end when you're satisfied that the data has been processed properly, delete the appropriate message record from the message table
DELETE FROM MESSAGE Where message.mailID = @_ID
GO

Sample Data that has problems
 
Fromubject:contact info sent from samplesite.comBody:First name: Sample
Last name: Name
Address 1: 123 Testing Road
Address 2: Don't filled
City: Park Forest
State/Province: FL
Zip/Postal Code: 12345-1234
Country: USA
E-mail address: validemail@email.com
Daytime Phone: 123-123-1234
Evening Phone: Don't filled
Product Name: Sample Product with Cheese
UPC Code: 12345-89521
Date Code: 1251237 D PST . 5290
Purchased At (Store Name): StoreName
Purchase City: Store City
Date Purchased: 12/8/2007
Date Opened: 12/14/2007
E-mail Subject: nail in product
Comments: when I purchased your product, there was something in it.  Obviously I am not happy about this.  Please contact me.  Thank you John Q Public
 

View Replies !   View Related
Server: MSg 17, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server Does Not Exist Or Access Denied
Hi,

When I am trying to access SQL Server 2000 database from another machine i got this error

Server: MSg 17, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied

but I could access the database on same server and in that server i could access other databases in different server.

 

 

View Replies !   View Related
Query Analyzer Error Unable To Connect Server Local Msg17, Level 16,state 1/ODBC SQL Server Driver [DBNETLIB]SQL Server Does Not
I am getteing
need help
Query analyzer error Unable to connect server local Msg17, level 16,state 1
ODBC SQL server driver [DBNETLIB]SQL server does not exist

View Replies !   View Related
SQL Server 2000 Server: Msg 18456, Level 14, State 1, Line 1 Login Failed For User 'sa'.
As an 'SA' I thought that you have permission to do anything on the server. Running an update command from Query Analyzer is throwing this error:

SQL Server 2000 SP3

Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.


The table is in the dbo schema.

Has anyone seen this error before?

 

View Replies !   View Related
SQL Server 2000 - Server: Msg 9002, Level 17, State 6, Line 1
I have a stored procedure that takes 18 hours to complete. SometimesI get the below error message when I run the stored procedure.'Server : Msg 9002, Level 17, State 6, Line 1 The log file fordatabase 'Customer' is full. Back up the transaction log for thedatabase to free up some log space.'.I checked my log file and it is not full. And I don't know why I amgetting this message. Any help is appreciated.Thanks,Anjula

View Replies !   View Related
Transaction Log Full - Msg 9002, Level 17, State 2 - SQL Server 2005 Standard
Hi,

I have database with unlimited growth in Data with 2MB growth and  Log File with 1MB growth. The continuous  data is inserted with min 20KB per minute. We struct with following error and Log file size is too large (85GB)  than Data file(20GB).  Now server is not allowing to take backup as diskspace is not available.

 

Msg 9002, Level 17, State 2, Procedure DumpData, Line 10
The transaction log for database '%DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

 

status at SYS.DATABASES:

log_reuse_wait log_reuse_wait_desc
-------------- ------------------------------------------------------------
2              LOG_BACKUP


Please help me to resolved the issue and let me know hints to shrink LOG file. Data inserted are using ADO applications that uses some insert/select stored procedures that does not have begin transaction-Commit Transaction block as appliction issues the Commit over connection object. 

 

Regards,

Pavan

 

View Replies !   View Related
Msg 107, Level 16, State 2, Line 1
Hi,I am getting the following error from the query below against SQLServer 8.00.2039 (SP4)Error:====Server: Msg 107, Level 16, State 2, Line 1The column prefix 'd' does not match with a table name or alias nameused in the query.Select Statement:=============select ....from trades a,gems_product_groups b,portfolio_nav_mapping c,products d,limit_types eleft outer join gems_prod_trade_mod f on d.product_id =f.product_IDI know this was a known bug in MS-SQL7, but I thought it had been fixedin 2000.Can anyone help?Thanks

View Replies !   View Related
Msg 18481, Level 16, State 2
I am getting this error trying to call a stored proc from MyServer. Server2 is the local server

Msg 18481, Level 16, State 2
Unable to connect to site 'myserver' because it is unavailable or does not exist.

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

BUG #: 16506 (Windows: 6.5)

SYMPTOMS
========

When you make a remote procedure call (RPC) to a SQL Server that does not exist
or is not started, you receive the following error message:

Msg 18481, Level 9, State 2
Unable to connect to site 'MyServer' because it is unavailable or does not exist.

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

Three problems.

1) The remote server is started (MyServer).

2) A different server (Server1) can access the remote server (MyServer).

3) The Server1 can access Server2. (Server2 can not access MyServer)

View Replies !   View Related
Msg 1509, Level 20, State 1....
Hi,

Running NT 4 Svc Pack 4, SQL Server 6.5 Svc Pack 5a. I have an sp to build indexes and it is no longer running...after a couple moments I get the error(s) "Msg 1509, Lvl20, State 2, Row Compare Error". The "State" is 2 on most of the messages and 1 on 1...I get 5 messages at once. I looked through the support information at Microsoft's site and found 1 article on this but...it was for 6.0, it stated that it was fixed in Svc Pack 3, and provided a workaround of increasing the "sort pages" value in the SQL configuration. I was unable to find the "sort pages" option so I am assuming it is no longer available. I then installed the SQL Svc Pack 5a but still no resolution. At this point I have no indexes on my tables and can't find a way to rebuild them. Any help would be appreciated...The only thing I can think of is that I installed the NT svc pack last week and this might have caused it...the indexes sp runs over the weekend so I did not notice this until this morning.


Thanks in Advance,

Kevin

View Replies !   View Related
Error Msg. 605 Level 21 State 1
Attempt to fetch logical page ' ' in database ' '. Object belongs to ' ' not object ' '.

Please someone help me fix this. My table is corrupt and I cannot drop it and restore it because I keep getting an error message that says:

Cannot drop table ' ' there is not enough space in syslogs. blah, blah, blah.

Can anyone help me? It woould be much appreciated.

Christine

View Replies !   View Related
How To Fix: &#34;Msg 8906, Level 16, State 1&#34;
Hello,

I´ve reinstalled from scratch SQL 7 and was executing a loop that consists in calculating balances for each day, from august 1st to november 30th.

when executing august 20th, appeared the following message on the result:

<result of stored procedure >

(3861 row(s) affected)

Location: alloc.cpp:7859
Expression: IAM_bp != NULL
SPID: 7
Process ID: 122
Server: Msg 3624, Level 20, State 1, Procedure p_pmp_carga_unificado_csg, Line 671


DB-Library Process Dead - Connection Broken


Tried to fix executing :

dbcc checkdb
go
dbcc newalloc
go
dbcc checkcatalog
go
dbcc updateusage(0)
go

but it didn´t fixed DB. It has shown the messages below:

<result of dbcc newalloc(database,repair_allow_data_loss) >

Server: Msg 8906, Level 16, State 1
Page (1:87) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 100_PCT_FULL'.
Server: Msg 8906, Level 16, State 1
Page (1:98) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 100_PCT_FULL'.
Server: Msg 8906, Level 16, State 1
Page (1:99) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 100_PCT_FULL'.
Server: Msg 8906, Level 16, State 1
Page (1:101) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 100_PCT_FULL'.
Server: Msg 8906, Level 16, State 1
Page (1:110) in database ID 9 is allocated in the SGAM (1:3) and PFS (1:1), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 100_PCT_FULL'.

Does anyone know how to fix it?

TKS

Masa

View Replies !   View Related
ODBC: Msg 0, Level 19, State 1 (!!!!!)
When executing a query on a table with circa 10 million rows I get the following error;

ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 8 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

However, when running exactly the same process on a sample data set I have no problems at all!!!!!!

Cheers

A

View Replies !   View Related
Msg 102, Level 15, State 1 Error
 

I am writing a user defined function but I am receiving an error message that I can figure out.  Any answers out there?
 
Msg 102, Level 15, State 1, Procedure ufn_GetStreetNumber, Line 25
Incorrect syntax near '@StreetNum'.
 
-- ---------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Description:  Parses out the Street number for Address field
-- =============================================
CREATE FUNCTION dbo.ufn_GetStreetNumber
(
          -- Add the parameters for the function here
          @StreetAddr varchar(50)
)
RETURNS int
AS
BEGIN
          -- Declare the return variable here
          DECLARE @StreetNum varchar(50);
          DECLARE @iIter smallint, @iNoOfChars smallint, @iStreetNo int;
 
          SET @StreetNum=NULL;
          SET @iStreetNo=0;
          SET @iNoOfChars=1;
          SET @iIter=1;
 
          -- Add the T-SQL statements to compute the return value here
                   WHILE Isnumeric(Substring(@StreetAddr,@iNoOfChars,@iIter))=1
@StreetNum=@StreetNum + Substring(@StreetAddr,@iNoOfChars,@iIter)
                             @iIter=@iIter+1
                   BREAK
                             @iStreetNo=@StreetNum    
 
          -- Return the result of the function
          RETURN @iStreetNo
 
END
GO
 
 

View Replies !   View Related
Msg 8101, Level 16, State 1, Line 68
I'm getting this error in SQL server.
Msg 8101, Level 16, State 1, Line 68
An explicit value for the identity column in table 'temp_notes' can only be specified when a column list is used and IDENTITY_INSERT is ON.

My original script looked like this.

SET NOCOUNT ON

DECLARE @patient_id int
,@phn varchar(9)
,@full_name varchar(100)
,@birth_date datetime
,@entry_note varchar(8000)
,@entry_date datetime
,@rec_counter int
,@Clinic_identifier varchar(24)
,@chart_count int

set @Clinic_identifier = 'MEDOWL.'
set @rec_counter = 0


DECLARE patients_cursor CURSOR FOR
SELECT a.full_name, a.birth_date, a.phn, a.patient_id
FROM patient as a
,clinicdoctors as b
WHERE a.patient_id IN (51450,49741,57290) --= 51450
and datalength(a.phn) = 9
and b.clinicdoctor_id = a.clinicdoctor_id
ORDER BY a.last_name,a.first_name,a.patient_id

OPEN patients_cursor
FETCH NEXT FROM patients_cursor
INTO @full_name, @birth_date, @phn, @patient_id

--select @full_name, @birth_date, @phn, @patient_id


WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE chartcount_cursor CURSOR FOR
select count(*)
from ChartEntries
where patient_id = @patient_id


OPEN chartcount_cursor
FETCH NEXT FROM chartcount_cursor INTO @chart_count

--select @chart_count as chtcount

DECLARE chartentries_cursor CURSOR FOR
select entry_datetime,entry_note
from ChartEntries
where patient_id = @patient_id
order by Patient_id, ChartEntry_Id desc

-- Variable value from the outer cursor

OPEN chartentries_cursor
FETCH NEXT FROM chartentries_cursor INTO @entry_date,@entry_note
--select @entry_date,@entry_note

WHILE @@FETCH_STATUS = 0

BEGIN
set @rec_counter = @rec_counter + 1
--select @@FETCH_STATUS as status
IF @@FETCH_STATUS = 0

insert into temp_notes
values (@Clinic_identifier+@phn+'.'
+convert(varchar,@rec_counter)+'.'
+convert(varchar,@chart_count),
@phn,
@full_name,
@birth_date,
@entry_date,
@entry_note
)

-- Get the next entry note
FETCH NEXT FROM chartentries_cursor INTO @entry_date,@entry_note
--select @entry_date,@entry_note

END
CLOSE chartentries_cursor
DEALLOCATE chartentries_cursor
CLOSE chartcount_cursor
DEALLOCATE chartcount_cursor


set @rec_counter = 0
set @chart_count = 0

-- Get the next patient record
FETCH NEXT FROM patients_cursor
INTO @full_name, @birth_date, @phn, @patient_id

--select @full_name, @birth_date, @phn, @patient_id

-- Get the next chart record count
--FETCH NEXT FROM chartcount_cursor INTO @chart_count
--select @chart_count as chtcount

END

CLOSE patients_cursor
DEALLOCATE patients_cursor

GO

I then changed it to left pad the variables @rec_counter and @chart_count.
SET NOCOUNT ON

DECLARE @patient_id int
,@phn varchar(9)
,@full_name varchar(100)
,@birth_date datetime
,@entry_note varchar(8000)
,@entry_date datetime
,@rec_counter int
,@Clinic_identifier varchar(24)
,@chart_count int

set @Clinic_identifier = 'MEDOWL.'
set @rec_counter = 0


DECLARE patients_cursor CURSOR FOR
SELECT a.full_name, a.birth_date, a.phn, a.patient_id
FROM patient as a
,clinicdoctors as b
WHERE a.patient_id IN (51450,49741,57290) --= 51450
and datalength(a.phn) = 9
and b.clinicdoctor_id = a.clinicdoctor_id
ORDER BY a.last_name,a.first_name,a.patient_id

OPEN patients_cursor
FETCH NEXT FROM patients_cursor
INTO @full_name, @birth_date, @phn, @patient_id

--select @full_name, @birth_date, @phn, @patient_id


WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE chartcount_cursor CURSOR FOR
select count(*)
from ChartEntries
where patient_id = @patient_id


OPEN chartcount_cursor
FETCH NEXT FROM chartcount_cursor INTO @chart_count

--select @chart_count as chtcount

DECLARE chartentries_cursor CURSOR FOR
select entry_datetime,entry_note
from ChartEntries
where patient_id = @patient_id
order by Patient_id, ChartEntry_Id desc

-- Variable value from the outer cursor

OPEN chartentries_cursor
FETCH NEXT FROM chartentries_cursor INTO @entry_date,@entry_note
--select @entry_date,@entry_note

WHILE @@FETCH_STATUS = 0

BEGIN
set @rec_counter = @rec_counter + 1
--select @@FETCH_STATUS as status
IF @@FETCH_STATUS = 0

insert into temp_notes
values (@Clinic_identifier+@phn+'.'
+REPLICATE('0',3),convert(varchar,@rec_counter)+'. '
+REPLICATE('0',3),convert(varchar,@chart_count),
@phn,
@full_name,
@birth_date,
@entry_date,
@entry_note
)

-- Get the next entry note
FETCH NEXT FROM chartentries_cursor INTO @entry_date,@entry_note
--select @entry_date,@entry_note

END
CLOSE chartentries_cursor
DEALLOCATE chartentries_cursor
CLOSE chartcount_cursor
DEALLOCATE chartcount_cursor


set @rec_counter = 0
set @chart_count = 0

-- Get the next patient record
FETCH NEXT FROM patients_cursor
INTO @full_name, @birth_date, @phn, @patient_id

--select @full_name, @birth_date, @phn, @patient_id

-- Get the next chart record count
--FETCH NEXT FROM chartcount_cursor INTO @chart_count
--select @chart_count as chtcount

END

CLOSE patients_cursor
DEALLOCATE patients_cursor

GO

That is when I received the error, so I inserted this command before the insert line but it made no difference.

SET IDENTITY_INSERT temp_notes ON
insert into temp_notes
values (@Clinic_identifier+@phn+'.'
+REPLICATE('0',3),convert(varchar,@rec_counter)+'. '
+REPLICATE('0',3),convert(varchar,@chart_count),
@phn,
@full_name,
@birth_date,
@entry_date,
@entry_note
)

I'm concatenating four different variables together to insert into the column note_id.

What is the problem? I just don't see it. My table definition looks like this.
CREATE TABLE [dbo].[temp_notes](
[temp_Id] [int] IDENTITY(1,1) NOT NULL,
[note_Id] [varchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[phn] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[full_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[birth_date] [datetime] NULL,
[entry_datetime] [datetime] NULL,
[Entry_Note] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_temp_notes] PRIMARY KEY CLUSTERED
(
[temp_Id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

View Replies !   View Related
Help On Msg 207, Level 16, State 1,- On PROCEDURE Expression IN
need help
on expression IN
whan i do this i get an error

Msg 207, Level 16, State 1, Line 17

Invalid column name 't.empID'.

 



Code Snippet
DECLARE @vempID varchar(500)
set @vempID = '68737477,51622017'
update [nili].[dbo].[tb_pivot_big]
set fld1 = case v.fld1 when '101' then 'a' when '102' then 'd' when '103' then 'u' when '104' then 'j' end
, f1 = case v.fld1 when '101' then 'b' when '102' then 'r' when '103' then 'i' when '104' then 'l' end
, f11 = case v.fld1 when '101' then 'c' when '102' then 't' when '103' then 'd' when '104' then 'm' end

from [nili].[dbo].[tb_pivot_big] t
inner join (select *
from [nili].[dbo].[tb_pivot_big]
where val_orginal = 1) v
on t.empID = v.empID
WHERE (t.val_orginal = 2) AND (charindex(','+CONVERT(varchar,[t.empID])+',',','+@vempID+',')) > 0
 
 




whan i run it like this  it OK



Code Snippet
SELECT     v.Fname, v.empID
FROM         dbo.tb_pivot_big AS t INNER JOIN
                          (SELECT     napipot, Fname, empID, new_unit, mhlka_id, mhlka, val_orginal, fld1, f1, f11, fld2, f2, f22, fld3, f3, f33, fld4, f4, f44, fld5, f5, f55, fld6, f6, f66, fld7, f7, f77,
                                                   fld8, f8, f88, fld9, f9, f99, fld10, f10, f110, fld11, f_11, f111, fld12, f12, f112, fld13, f13, f113, fld14, f14, f114, fld15, f15, f115, fld16, f16, f116, fld17, f_7,
                                                   f117, fld18, f18, f118, fld19, f19, f119, fld20, f20, f220, fld21, f21, f221, fld22, f_22, f222, fld23, f23, f223, fld24, f24, f224, fld25, f25, f225, fld26, f26, f226,
                                                   fld27, f27, f227, fld28, f28, f228, fld29, f29, f229, fld30, f30, f330, fld31, f31, f331
                            FROM          dbo.tb_pivot_big
                            WHERE      (val_orginal = 1)) AS v ON t.empID = v.empID
WHERE     (t.val_orginal = 2) AND (t.empID IN (68737477, 51622017))
 
 
TNX

View Replies !   View Related
Msg 3710, Level 16, State 1, Line 1
Hello Folks,

 

I am moving the model & Msdb databases to a different location and I have this error.

Thanks

Msg 3710, Level 16, State 1, Line 1

Cannot detach an opened database when the server is in minimally configured mode.

View Replies !   View Related
Error: Msg 102, Level 15, State 1, Line 1
Hi,

I have built a stored procedure and the code is like below:





Code Snippet

DECLARE @Table_Name        VARCHAR(100)
DECLARE @Table_Desc        SQL_VARIANT
DECLARE @Query            VARCHAR(8000)

USE HRD

SELECT @Table_Name = 'Employee'

SELECT @Query = 'IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[DEL_' + UPPER(@Table_Name) + ']''))
DROP TRIGGER [dbo].[DEL_' + UPPER(@Table_Name) + ']'
EXEC(@Query)

SELECT @Table_Desc = (SELECT a.value
FROM
sys.extended_properties a, sys.tables b
WHERE a.major_id = b.object_id
AND a.minor_id = 0
AND a.name = 'MS_DESCRIPTION'
AND b.name = @Table_Name)

SELECT @Query = '''CREATE TRIGGER [DEL_' + UPPER(@Table_Name) + '] ON dbo.' + @Table_Name + '
FOR DELETE
AS

DECLARE @Old_Value        VARCHAR(8000)
DECLARE @New_Value        VARCHAR(8000)
DECLARE @P_Key            VARCHAR(8000)
DECLARE @P_Key_Value    VARCHAR(8000)
DECLARE @P_Key_Insert    VARCHAR(8000)
DECLARE @Comment        VARCHAR(8000)

SELECT @P_Key_Insert = ''''''''

EXEC(''''IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_PrimKey]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_PrimKey]'''')

SELECT K.COLUMN_NAME INTO Temp_PrimKey
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K
    ON T.CONSTRAINT_NAME = K.CONSTRAINT_NAME 
WHERE
    T.CONSTRAINT_TYPE = ''''PRIMARY KEY'''' AND T.TABLE_NAME = ''''' + @Table_Name + '''''

EXEC(''''IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Value]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_Value]'''')

EXEC(''''CREATE TABLE [dbo].[Temp_Value](
    [PValue] [VARCHAR](max) NOT NULL
 ) ON [PRIMARY]'''')

EXEC(''''IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Deleted]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_Deleted]'''')

SELECT * INTO Temp_Deleted FROM deleted

DECLARE Curs_PrimKey CURSOR FOR
SELECT * FROM Temp_PrimKey

OPEN Curs_PrimKey
FETCH NEXT FROM Curs_PrimKey INTO @P_Key
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(''''INSERT INTO Temp_Value SELECT ''''+ @P_Key + '''' FROM Temp_Deleted'''')
    SELECT @P_Key_Value = (SELECT PValue FROM Temp_Value)
    EXEC(''''DELETE FROM Temp_Value'''')
    SELECT @P_Key_Insert = @P_Key_Insert + @P_Key + '''' = '''' + @P_Key_Value + '''', ''''
    FETCH NEXT FROM Curs_PrimKey INTO @P_Key
END
CLOSE Curs_PrimKey
DEALLOCATE Curs_PrimKey

EXEC(''''IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Deleted]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_Deleted]'''')

EXEC(''''IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''''''''[dbo].[Temp_Value]'''''''') AND type in (N''''''''U''''''''))
DROP TABLE [dbo].[Temp_Value]'''')

SELECT @P_Key_Insert = LEFT(@P_Key_Insert, LEN(@P_Key_Insert)-2)
SELECT @Comment = ''''' + CAST(@Table_Desc AS VARCHAR) + ' deleted, '''' + @P_Key_Insert

INSERT INTO TLOG(Log_Date, Log_Reference, Log_Comment)
    VALUES (GETDATE(), @P_Key_Insert, @Comment)
'''
--PRINT @Query
EXEC(@Query)
Problem is when I run it gives error:

Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'CREATE TRIGGER [DEL_EMPLOYEE] ON dbo.Employee FOR DELETEAS DECLARE @Old_Value        VARCHAR(8000)DECLARE @New_Value        VARCHA'.

If I print the @Query and run it in a query analyzer using the statement EXEC it worked. It's giving me headache, this supposed to be a simple exec statement. Please advise. Thanks.

View Replies !   View Related
Msg 4305, Level 16, State 1, Line 1
Error 4305
 
What would be the cause of this error?
 
Msg 4305, Level 16, State 1, Line 1
The log in this backup set begins at LSN 8229000000047200001, which is too recent to apply to the database. An earlier log backup that includes LSN 8219000000021400001 can be restored
 
And what would the fix be?
 
I am trying to restore from a Full Backup and then the log files on a development machine.
 
Cheers,
 
Casey
 
 

View Replies !   View Related
Error Message 18542 Level 16 State 1
hi we have a link server....andthe user credentials are not being trasfered to link server.....anythoughts??we get the following error messageServer message 18542 level 16 state 1--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/error-messa...pict266240.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=915273

View Replies !   View Related
CLR TVF Error: Msg 6260, Level 16, State 1, Line 1
Dear All, I always got this error in CLR TVF:
 
Msg 6260, Level 16, State 1, Line 1
An error occurred while getting new row from user defined Table Valued Function :
System.InvalidOperationException: Invalid attempt to FieldCount when reader is closed.
System.InvalidOperationException:
   at System.Data.SqlClient.SqlDataReaderSmi.get_FieldCount()
   at System.Data.Common.DbEnumerator.BuildSchemaInfo()
   at System.Data.Common.DbEnumerator.MoveNext()
 
Here is my code:
 
using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Collections;

public static class UserDefinedFunctions
{
       [Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "rowfiller",DataAccess=DataAccessKind.Read,TableDefinition = "ActID int, ActName nvarchar(50), ActCreatorID int,ActDesp nvarchar(200),ActCreateDate datetime,ActModifyDate datetime, ActStartDate datetime, ActEndDate datetime, Status int, Cost int")]
    public static IEnumerable Func_GetSchCatActivityIDTable(int CatActivityID)
    {
        using (SqlConnection connection = new SqlConnection("context connection=true"))
        {
            string sqlstring = "select * from Activity where CatActivityID=@CatActivityID;";            
         
            connection.Open();
            SqlCommand command = new SqlCommand(sqlstring, connection);           
            command.Parameters.AddWithValue("@CatActivityID", CatActivityID);     
            
                       
            return command.ExecuteReader(CommandBehavior.CloseConnection);           
          
        }
    }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft Performance","CA1811:AvoidUncalledPrivateCode")]
    public static void rowfiller(Object obj,
                                out SqlInt32 ActID,
                                out SqlString ActName,
                                out SqlInt32 ActCreatorID,
                                out SqlString ActDesp,
                                out SqlDateTime ActCreateDate,
                                out SqlDateTime ActModifyDate,
                                out SqlDateTime ActStartDate,
                                out SqlDateTime ActEndDate,
                                out SqlInt32 Status,
                                out SqlInt32 Cost,
                                )
    {
        
        SqlDataRecord row = (SqlDataRecord)obj;
        int column = 0;

        ActID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
        ActName = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
        ActCreatorID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
        ActDesp = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
        ActCreateDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
        ActModifyDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
        ActStartDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
        ActEndDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
        Status = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
        Cost = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
        }           
    
   
};
 
Can anyone tell me what I am doing wrong? Many thanks

.

View Replies !   View Related
Login Failed: Msg 18456, Level 14, State 11
Hi,
 
Last night I setup my MOSS 2007 server and SQL 2005, I got everything working fine in short order.
 
I had forgotten to look for updates for SQL 2005 so i decided I would run Windows Update.
 
 



SQL Server 2005
Critical Update for SQL Server 2005 Service Pack 2 (KB 934458)

Tuesday, March 04, 2008
Microsoft Update

SQL Server 2005
Microsoft SQL Server 2005 Service Pack 2 (KB 921896)

 
Those two updates were applied, then after a reboot I began getting the login failed error.
 
I can login using windows authentication as DOMAINAdministrator, I can also login as SA, but i am trying to figure out why it is denying me logins as  DOMAINSharepoint
 
Has anybody had prior experience with this or know what the deal is?
 
TIA,
 
-Drew
 
 
 
 
 

View Replies !   View Related
Msg 102, Level 15, State 1, Procedure Dni_invoices_get, Line 37
 

Hello,

 

I am using SQL express as a backend for my websites and my accounting software.

When installing my new app I run the install.sql file and all is good untill the end.

the following error occurs. Can someone direct me where to look to fix this please?

Msg 102, Level 15, State 1, Procedure dni_invoices_get, Line 37

Incorrect syntax near '.'.

 

Thanks eveningjazz

View Replies !   View Related
CLR Stored Procedure Craps Out - Msg 6532, Level 16, State 49
Hi All,

I have read the various posts regarding the thread abort issue.
I have a stored proc (CLR-C#) which makes and ldap call to a novell NDS tree and attempts to pull about 40k+ records which I would then insert into a sql server database.

I have set up all the requrements in terms of clr enabled etc...

I have written my class and I can run the sp when I limit the records returned. However when I go to grab all the records, (which takes some time), the sp craps out with the error message in the title of this post.

The assembly is running under unsafe permissions.

If I run the sp once, it works, then again and I may or may not get an 6522 (memory error). I added the search parameter to break down the records returned (so I return  a's then b's etc...) . BTW: This works fine ouside SQL Server, I can run this as an ASP.Net app (I prototyped it with a test harness aspx page).

Basically what the class does is
connect to novel nds directorypull records based on search criteria (blank for all)dump records into datatableserialize datatable to xmlcall another sp to take xml and insert into tabledisconnect from treeAny comments/suggestions apreciated.

Thanks,

Stephen

Here is the class (editied to protect privacy):

using System;
using System.IO;
using System.Text;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using Novell.Directory.Ldap;

namespace EDSPullInSQLStoreProc
{
    public class EDSPROC
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void EDSPull(bool IsProd, bool IsSecure, string searchParam)
        {
            //variable declarations
            string __LADPHost_DEV = "xx.xx.xx.xx";
            string __LADPHost_PROD = "xx.xx.xx.xx";
            int __LADPPort_NonSecure = xxx;
            int __LADPPort_Secure = xxx;
            int __LDAPPort;
            string __LDAPUserDN_DEV = "some string";
            string __LDAPUserDN_PROD = "somestring";
            string __LDAPUserDNPwd_DEV = "apassword";
            string __LDAPUserDNPwd_PROD = "apassword";
            LdapConnection oLDAPConn;
            DataTable oDTEDSData = new DataTable("EDSUSER");
            string strErrResponseMsg = "";
            SqlPipe oSQLPipe = SqlContext.Pipe;

            //connect to eds
            try
            {
                oLDAPConn = new LdapConnection();
                if (IsSecure)
                {
                    oLDAPConn.SecureSocketLayer = true;
                    __LDAPPort = __LADPPort_Secure;
                }
                else
                {
                    oLDAPConn.SecureSocketLayer = false;
                    __LDAPPort = __LADPPort_NonSecure;
                }
                if (IsProd)
                {
                    oLDAPConn.Connect(__LADPHost_PROD, __LDAPPort);
                    oLDAPConn.Bind(__LDAPUserDN_PROD, __LDAPUserDNPwd_PROD);
                }
                else
                {
                    oLDAPConn.Connect(__LADPHost_DEV, __LDAPPort);
                    oLDAPConn.Bind(__LDAPUserDN_DEV, __LDAPUserDNPwd_DEV);
                }
               
            }
            catch (LdapException ldapEx)
            {
                oSQLPipe.Send("Error connecting to EDS: " + ldapEx.LdapErrorMessage.ToString());
                return;
            }

            if (oLDAPConn != null)
            {
              
                oDTEDSData.Columns.Add("LastName");
                ...a bunch more columns

                DataRow oRow;

                //make the ldap query filling the datatable
                LdapSearchConstraints oEDSSearchConstriants = new LdapSearchConstraints();
                oEDSSearchConstriants.MaxResults = 0;

                LdapSearchResults oEDSSearchResults = oLDAPConn.Search("somestring", LdapConnection.SCOPE_ONE, "cn=" + searchParam + "*", null, false, oEDSSearchConstriants);
                LdapEntry oEDSEntry;
                LdapAttribute oEDSAttrib;
               
                while (oEDSSearchResults.hasMore())
                {
                    try
                    {
                        oEDSEntry = oEDSSearchResults.next();
                    }
                    catch (LdapException e)
                    {
                        oSQLPipe.Send("EDS Pull error: Obtaining next record - " + e.LdapErrorMessage.ToString());
                        try
                        {
                            oLDAPConn.Disconnect();
                        }
                        catch
                        {
                            oLDAPConn = null;
                        }
                        return;
                    }
                    oRow = oDTEDSData.NewRow();

                    oEDSAttrib = oEDSEntry.getAttribute("sn");
                    if (oEDSAttrib != null)
                    {
                        oRow["LastName"] = oEDSAttrib.StringValue;
                    }
                    else
                    {
                        oRow["LastName"] = "";
                    }
                   
...get more attributes etc
                   
                }//end while
                if (oDTEDSData.Rows.Count > 0)
                {
                    try
                    {
                        TextWriter oWriter = new StringWriter();
                        oDTEDSData.WriteXml(oWriter);
                        string strXML = oWriter.ToString();
                        strXML = strXML.Replace("'", "''");
                        using (SqlConnection oConn = new SqlConnection("context connection=true"))
                        {
                            oConn.Open();
                            SqlCommand oCmd = new SqlCommand("exec proc_BulkInsertEDSUsers '" + strXML + "','" + searchParam + "'", oConn);
                            oCmd.ExecuteNonQuery();
                            oSQLPipe.Send("EDS Pull complete.");
                        }
                    }
                    catch (Exception ex)
                    {
                        oSQLPipe.Send("An error has occurred. " + ex.Message.ToString() + "" + ex.InnerException.Message.ToString());
                        try
                        {
                            oLDAPConn.Disconnect();
                        }
                        catch
                        {
                            oLDAPConn = null;
                        }
                        return;
                    }                   
                }

                //close the ldap connection
                try
                {
                    oLDAPConn.Disconnect();
                }
                catch
                {
                    oLDAPConn = null;
                }
               
             }
        }
    }
}

View Replies !   View Related
Msg 15600, Level 15, State 1, Procedure Sp_change_users_login, Line 207
I restored a database to my test server from a production server.  I ran "sp_change_users_login 'report' " and found 1 orphan id.  Then I ran "sp_change_users_login 'auto_fix', 'orphan_id' ".  It gets the error.  I have run it before with success.  Any idea why this time is different?

 

View Replies !   View Related
Msg 4104, Level 16, State 1, Line 1 In Update Query
Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.JOB40115_Cost_Actindx.ACTINDX" could not be bound.
 
 
 
UPDATE dbo.JOB40115 set dbo.JOB40115.JOB_Cost_Index = dbo.JOB40115_Cost_Actindx.ACTINDX

SELECT JOB40115.ProjctID, JOB40115.SubLevel1, JOB40115.SubLevel2, JOB40115.CostCategorie, JOB40115.DepartementID, JOB40115.JOB_Cost_Index,

JOB40115_Cost_Actindx.ACTINDX, JOB40115_Cost_Actindx.ProjctID AS Expr1, JOB40115_Cost_Actindx.SubLevel1 AS Expr2,

JOB40115_Cost_Actindx.SubLevel2 AS Expr3, JOB40115_Cost_Actindx.DepartementID AS Expr4,

JOB40115_Cost_Actindx.CostCategorie AS Expr5

FROM JOB40115 INNER JOIN

JOB40115_Cost_Actindx ON JOB40115.ProjctID = JOB40115_Cost_Actindx.ProjctID AND

JOB40115.SubLevel1 = JOB40115_Cost_Actindx.SubLevel1 AND JOB40115.SubLevel2 = JOB40115_Cost_Actindx.SubLevel2 AND

JOB40115.CostCategorie = JOB40115_Cost_Actindx.CostCategorie AND JOB40115.DepartementID = JOB40115_Cost_Actindx.DepartementID

View Replies !   View Related
Msg 15151, Level 16, State 1, Line 5 (Cannot Find The User)
I'm getting a specific error when i try to run a .sql script for a Custom TableProfile from the Asp.net site,when i Parse the .sql file Commands execute perfectly. But when i actually "Execute" the   .sql script i get the following errors::

 

Msg 15151, Level 16, State 1, Line 5

Cannot find the user 'WYATT-PCWyattASPNET', because it does not exist or you do not have permission.

 

 

 

How can i fix this??

 

 

 

Thxs in Advance Rattlerr

View Replies !   View Related
Database Mirror Error Msg 1447, Level 16, State 21, Line 1
we config our SAP system to use SQL Server 2005 database mirror. but the mirror server hang by accident, after restart mirror server,the server return to normal,but the mirror can't be resume.

ALTER DATABASE R3P
 SET PARTNER resume

the error is:
Msg 1447, Level 16, State 21, Line 1
ALTER DATABASE "R3P" command cannot be executed until both partner server instances are up, running, and connected. Start the partner and reissue the command.

View Replies !   View Related
Msg 156, Level 15, State 1, Line 19,Incorrect Syntax Near The Keyword 'IDENTITY'.
Here is a Database script I wrote: I can't figure out what the problem is when I declare my primary Keys in the second and third table.
Thanks in Advance

Errors:

Msg 156, Level 15, State 1, Line 19

Incorrect syntax near the keyword 'IDENTITY'.

Msg 156, Level 15, State 1, Line 29

Incorrect syntax near the keyword 'IDENTITY'.
 
 

CREATE DATABASE GoGreen

Go
 

USE GoGreen

Create TABLE Student

(StudentID INT NOT NULL IDENTITY PRIMARY KEY,   <---no Error here

DrivePassCode char(3) NOT NULL,

LName varchar(50) NOT NULL,

FName varchar(50) NOT NULL,

Address1 varchar(50) NOT NULL,

Address2 varchar(50) NULL,

City varchar(50) NOT NULL,

PostalCode varchar(20) NOT NULL,

PhoneNumber varchar(50) NOT NULL,

Email varchar(50) NOT NULL,

Smoker char(2) NOT NULL,

NearestCityOrTown varchar(50) NOT NULL,

PaymentType varchar(20) NOT NULL,)
 

CREATE TABLE CoordinatedRideShare

(RideID INT NOT NULL, IDENTITY PRIMARY KEY,    <---Error #1

StudentID INT NOT NULL REFERENCES Student(StudentID)

[ON DELETE {CASCADE}]

[ON UPDATE {CASCADE}],

M-W ARRIVAL TIME smalldatetime NOT NULL,

M-W DEPART TIME smalldatetime NOT NULL,

T-TH ARRIVAL TIME smalldatetime NOT NULL,

T-TH DEPART TIME smalldatetime NOT NULL,)
 

CREATE TABLE MeetingLocation

(LocationID INT NOT NULL, IDENTITY PRIMARY KEY AUTONUMBER,   <---Error#2

RideID INT NOT NULL REFERENCES Coordinated RideShare(RideID)

[ON DELETE {CASCADE}]

[ON UPDATE {CASCADE}],

House Pickup char(1) NULL,

General meeting place varchar(50) NULL,)
 

CREATE INDEX DrivePass

ON Student (DrivePass)

CREATE INDEX NearestCityOrTown

ON Sudent (NearestCityOrTown)

 

View Replies !   View Related
EXEC Sp_insertRecord Has Too Many Arguments Specified: Msg 8144, Level 16, State 2-Why I Got This Error?
Hi all,
In Object Explorer of my ComputerNameSQLEXPRESS, I have a Database "shcDB" with Table "dbo.MyFriends" which has fields "PersonID" (Int), "FirstName", "LastName", "StreetAddress", "City", "State", "ZipCode", "E-MailAddress" set up.  When I executed the following query:
/////----shcSP-1.sql---////  
USE shcDB
GO
EXEC sp_insertRecord 3, "Mary", "Smith", "789 New St.", "Chicago", "Illinos", "12345", "M_Smith@Yahoo.com"
GO
/////////////////////////////////////////////////////////////
I got the following error message:
Msg 8144, Level 16, State 2, Procure sp_insertRecord, Line 0
Procedure or Function sp_insertRecord has too many arguments specified.
(1) Why did I get this error?
(2) Where can I get the documents for explaining the procedures like 'sp_insertRecord', 'sp_delecteRocord', 'sp_getRecord', 'sp_updateRecord', etc. and error messages?
Please help and give me the answers for the above-mentioned questions.
 
Thanks in advance,
Scott Chang
 
P. S.
I used my PC at home to post the above message.  I have not been able to post it by using my office PC in our LAN system.  In my office LAN system, I have been struggling to figure out and solve the following strange thing:  I log in http://forums.microsoft.com and "Sign Out" is on all the time. If I click on "Sign Out", I get "Logout:  You have been successfully logged out of the the forums." page all the time.  What is the cause to log me out?  How can I get it resolved? Our Computer Administrator? Or Microsoft? What Department in Microsoft can help me to solve this problem? Please advise on this strange thing.  Thanks.
 
 

View Replies !   View Related
Management Studio Disconnects With Error Msg 0, Level 11, State 0, Line 0
I am trying to run the following:

select * from sysindexes

The operation begins and results are produced but after about 200 results, I get the error - Msg 0, Level 11, State 0, Line 0 - and it disconnects.

This is a DB moved (restored) from SQL 2000 to SQL 2005.

Any ideas would be appreciated. I am the "make do" DBA and not very good at it yet, I am afraid.

Regards,

Mike

 

 

 

 

View Replies !   View Related
SQL HELP! Msg 4104, Level 16, State 1, Line 1 - The Multi-part Identifier Error
Hi chaps,

I have the following SQL query (SQL 2005).
Its basically retrieving some values using simple joins.
However there appears to be a problem with the LEFT OUTER JOIN:
"LEFT OUTER JOIN DDDispatchedOrder ON (OrderLineItemTransaction.OrderLineItemTransaction ID = DDDispatchedOrder.OrderItemTransactionID)
"
When I try to compile the code i Get :
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "OrderLineItemTransaction.OrderLineItemTransactionI D" could not be bound.

Any help would be appreciated.

Cheers
Bal

SELECT
ord.orderDate,
cc.forename + ' ' + cc.surname person,
prod.description,
oli.noofitems,
deladdr.housenameno + ' ' + deladdr.addressLine1 + ' ' + deladdr.addressLine2 + ' ' + deladdr.city + ' ' + deladdr.postcode + ' ' + deladdr.county + ' ' + deladdr.country deladdress
FROM
product prod,
OrderLineItem oli,
[Order] ord,
OrderTransaction ordT,
OrderLineItemTransaction oliT,
CustomerContact cc,
Customer cust,
DDDispatchedOrder dd,
address deladdr,
address invaddr
LEFT OUTER JOIN DDDispatchedOrder ON (OrderLineItemTransaction.OrderLineItemTransaction ID = DDDispatchedOrder.OrderItemTransactionID)
WHERE
prod.productID = oli.productID:eek:
AND ord.orderID = oli.orderID
AND ord.orderID = ordT.orderID
AND oliT.orderlineitemID = oli.orderlineitemID
AND cc.customercontactID = ord.customercontactID
AND cc.customerID = cust.customerID
AND ord.invoiceaddressID = invaddr.addressID
AND ord.deliveryaddressID = deladdr.addressID
AND ordT.dispatchTypeID = 2

View Replies !   View Related
Miss Match Between Column Type In A Cursor. Msg 8114, Level 16, State 5
hi

I have a float column . only in cursor I get a type missmatch.

does any one knows it ?

the error

Msg 8114, Level 16, State 5, Line 14

Error converting data type varchar to float.

 the code

DECLARE @RON_FLOAT FLOAT

DECLARE RON_CURSOR CURSOR FOR

SELECT RON_FLOAT

FROM RON_TABLE1

OPEN RON_CURSOR

FETCH NEXT FROM RON_CURSOR

INTO @RON_FLOAT

WHILE @@FETCH_STATUS = 0

BEGIN



PRINT 'VALUE IS ' + @RON_FLOAT

FETCH NEXT FROM RON_CURSOR

INTO @RON_FLOAT

END

CLOSE RON_CURSOR

DEALLOCATE RON_CURSOR

 

the code for the table

CREATE TABLE [dbo].[RON_TABLE1](

[RON_FLOAT] [float] NULL,

[RON_CHAR] [nchar](10) COLLATE Hebrew_BIN NULL

)

View Replies !   View Related
Generates Msg 107, Level 16, State 2, Line 2 - Column Prefix Does Not Exist (Oh Yes It Does.. Anyone Got A Solution ?)
 

hi All,
 
Following code generates error 107 (SQL Server 2000, SP3a):

SELECT

*
FROM

dbo.AssessmentItemHierarchy Parent

INNER JOIN dbo.RaterCategory

INNER JOIN dbo.RaterType

ON RaterCategory.ID = RaterType.RaterCategoryID

INNER JOIN dbo.AssessmentRater

ON AssessmentRater.RaterTypeID = RaterType.ID

INNER JOIN dbo.AssessmentItem

ON AssessmentItem.ID = Parent.ChildItemID

INNER JOIN dbo.Assessment

ON AssessmentItem.AssessmentID = Assessment.ID

INNER JOIN dbo.AssessmentResponse

ON AssessmentResponse.AssessmentItemID = AssessmentItem.ID

ON AssessmentResponse.AssessmentRaterID = AssessmentRater.ID

INNER JOIN dbo.ImageInstance

ON ImageInstance.ID = RaterType.ImageInstanceID

INNER JOIN dbo.AssessmentItem ParentGUID

ON Parent.ParentItemID = ParentGUID.ID
 
Error on Run:

Msg 107, Level 16, State 2, Line 2

The column prefix 'Parent' does not match with a table name or alias name used in the query.
 

 
The only work-around I have found is a rewrite of order:

SELECT

*
FROM

AssessmentItemHierarchy Parent

INNER JOIN RaterCategory

INNER JOIN RaterType

INNER JOIN AssessmentRater

INNER JOIN AssessmentItem

INNER JOIN Assessment ON AssessmentItem.AssessmentID = Assessment.ID

INNER JOIN AssessmentResponse ON AssessmentItem.ID = AssessmentResponse.AssessmentItemID

ON AssessmentRater.ID = AssessmentResponse.AssessmentRaterID

ON RaterType.ID = AssessmentRater.RaterTypeID

ON RaterCategory.ID = RaterType.RaterCategoryID

INNER JOIN ImageInstance

ON RaterType.ImageInstanceID = ImageInstance.ID

ON Parent.ChildItemID = AssessmentItem.ID

INNER JOIN AssessmentItem ParentGUID ON Parent.ParentItemID = ParentGUID.ID



 
Not very pretty... anyone know why I'm getting this error message with the first version of my query?

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved