I'm writing a stored procedure and when I click on the Check Syntax button its giving me the error in the subject. I'm not really sure whats wrong with this. Here is my Stored Procedure code. Any help wud be appreciated.
Hi, I'm having an SSIS package which gives the following error when executed :
Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server
The package has a single Execute SQL task with the properties listed below :
General Properties Result Set : None
ConnectionType : OLEDB Connection : Connected to a Local Database (DB1) SQLSourceType : Direct Input SQL Statement : exec(?) IsQueryStorePro : False BypassPrepare : False
Parameter Mapping Properties
variableName Direction DataType ParameterName
User::AddLinkSql Input Varchar 0
'AddLinkSql' is a global variable of package scope of type string with the value Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'
When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"
I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough it gives the same parse error message.
I would appreciate if anybody can help me out of this issue by suggeting where the problem is.
Hi guys! I am using SQL 2005 and I wonder why I am encountering the error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" everytime I am trying to create stored procedure with temp table and table variable. See my code below with temp table.
Any thoughts will be appreciated!
CREATE PROCEDURE DBO.SAMPLESP (@DETAILS AS VARCHAR(8000), @ID AS VARCHAR(15))
AS
BEGIN TRANSACTION
CREATE TABLE DBO.#TEMPTABLE { ASSET VARCHAR(50) }
DECLARE @INSTINSERT AS NVARCHAR(4000) SET @INSTINSERT= 'INSERT INTO #TEMPTABLE(ASSET)' SET @INSTINSERT= @INSTINSERT+ @DETAILS
EXEC sp_ExecuteSQL @INSTINSERT
INSERT INTO InstDetail (TrackNum, ASSETID) SELECT @ID, A.ASSE FROM #TEMPTABLE A
DROP TABLE #TEMPTABLE
IF @@ERROR != 0 BEGIN ROLLBACK TRANSACTION RAISERROR('There was an error in here', 11, 1) RETURN END ELSE COMMIT TRANSACTION
I have tried several times to install SQL server onto an NT4.0 server which is more than capable of having more than one application to cope with, however, when going through the install procedure the programm stops with the following message
DR.Watson Access Violation Setup.exe In address :
Any hints as to where the problems lies or point me inthe right direction would be appreciated.
There is a JOIN syntax error in this SQL, but my slow brain cannot figure out where. I tried to join two queries, which had been successful. Problem occurred when I added second left join. Can anyone help?
PARAMETERS pstrFinYear Text ( 255 ), pintAdjMonth Long;
SELECT A.BudgetLineID, A.BudgetLine, B.NumIsDevelopBusinessInternationally, B.NumIsDeeperParticipation, B.NumIsNewExporter, B.NumProjects, C.NumCompanies, A.KMISReportOrder FROM ( tblkpBudgetLine AS A
LEFT JOIN
[SELECT BudgetLineID, FinancialYear, SUM(IsDevelopBusinessInternationally) AS NumIsDevelopBusinessInternationally, SUM(IsDeeperParticipation) AS NumIsDeeperParticipation, (-1*SUM(AdjustedNewExpMonth=pintAdjMonth)) AS NumIsNewExporter, COUNT(ProjectID) AS NumProjects FROM qryBoardReport_Actuals WHERE FinancialYear=pstrFinYear AND AdjustedProjectStartMonth=pintAdjMonth GROUP BY BudgetLineID, FinancialYear]. AS B ON (A.BudgetLineID=B.BudgetLineID) AND (A.FinancialYear=B.BudgetLine)
LEFT JOIN
[SELECT Z.BudgetLineID, Z.FinancialYear, Z.AdjustedProjectStartMonth, COUNT(Z.Company) AS NumCompanies FROM [SELECT DISTINCT qryBoardReport_Actuals.BudgetLineID AS BudgetLineID, qryBoardReport_Actuals.FinancialYear AS FinancialYear, qryBoardReport_Actuals.AdjustedProjectStartMonth, qryBoardReport_Actuals.CompanyID as Company FROM qryBoardReport_Actuals WHERE FinancialYear=pstrFinYear AND AdjustedProjectStartMonth=pintAdjMonth GROUP BY qryBoardReport_Actuals.FinancialYear, qryBoardReport_Actuals.BudgetLineID, qryBoardReport_Actuals.AdjustedProjectStartMonth, qryBoardReport_Actuals.CompanyID]. AS Z GROUP BY FinancialYear, BudgetLineID, AdjustedProjectStartMonth] as C
ON (A.BudgetLineID=C.BudgetLineID) and (A.FinancialYear=C.FinancialYear))
WHERE A.FinancialYear=pstrFinYear ORDER BY A.KMISReportOrder;
I have an File System Task that copies a file from one directory ot another. When I hard code the target directory (c:dirfile.txt) it works fine. When I change it to a virtual directory (\serverdirfile.txt) I get a security error:
[File System Task] Error: An error occurred with the following error message: "Access to the path '\gracehbtest oS2TMM_Live_Title_000002.xml' is denied.".
hi, I am trying to save the record which is displayed in the textbox by selecting a row in the datagrid. If i click the save button I am getting the following error. Violation of PRIMARY KEY constraint 'PK_clientinfo'. Cannot insert duplicate key in object 'clientinfo'. The statement has been terminated. button save----------- conn.Open(); SqlCommand cmd = new SqlCommand("insert into clientinfo (client_name, address, telephone, fax, email, contperson1, dept, contpos1, contperson2, contpos2, rscompname, rscontperson, rsposition, actdate, acttime, instsno, actkey) values ('"+txtclientname.Text+"', '"+txtaddress.Text+"', '"+txttelephone.Text+"', '"+txtfax.Text+"', '"+txtemail.Text+"', '"+txtcntperson1.Text+"', '"+txtdept.Text+"', '"+txtcnt1pos.Text+"', '"+txtcntperson2.Text+"', '"+txtcnt2pos.Text+"', '"+txtcompname.Text+"', '"+txtrscntperson.Text+"', '"+txtrspos.Text+"', '"+txtactdate.Text+"', '"+txtacttime.Text+"','"+txtinstno.Text+"', '"+txtactkey.Text+"')", conn); cmd.ExecuteNonQuery(); conn.Close(); BindData(); txtclear(); }
Hiim keep getting the following errorConcurrency violation: the UpdateCommand affected 0 recordsI dunno whats wrong, im the only person using the database and program at the moment.Anyone know what im doing wrong?thanks
On a regular basis, it seems that the primary key of my table gets corrupted and I get the error message below. I can still read the data but no longer update or insert. There is no reason why there should be such error. I fix it by creating a temporary table and copying the data accross, then renaming the table, but I would rather prevent it. Any thought on this??
<P>ODBC Error Code = 23000 (Integrity constraint violation) <P>[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK___1__23': Attempt to insert duplicate key in object 'SiteList'
Hello,, I need to capture the primary key violation error: If e.CommandName = "Insert" Then Dim EmployeeIDTextBox As TextBox = CType(dvContact.FindControl("EmployeeIDTextBox"), TextBox) Dim LastName As TextBox = CType(dvContact.FindControl("LastName"), TextBox) Dim FirstName As TextBox = CType(dvContact.FindControl("FirstName"), TextBox) Using cmdAdd As New System.Data.SqlClient.SqlCommand 'Establish connection to the database connection Dim sqlcon As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("eConnString").ToString) 'Open connection sqlcon.Open() 'Pass opened connection (see above) to the command object cmdAdd.Connection = sqlcon 'Using "With/End With" pass content to columns from text objects and datatime variables (see above) With cmdAdd .Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", EmployeeIDTextBox.Text)) .Parameters.Add(New SqlClient.SqlParameter("@LastName", LastName.Text)) .Parameters.Add(New SqlClient.SqlParameter("@FirstName", FirstName.Text)) 'Establish the type of commandy object .CommandType = CommandType.Text 'Pass the Update nonquery statement to the commandText object previously instantiated .CommandText = "INSERT INTO ATTEmployee(EmployeeID, LastName, FirstName & _ "VALUES (@EmployeeID, @LastName, @FirstName)" End With 'Execute the nonquerry via the command object cmdAdd.ExecuteNonQuery() '<==Need to capture primaryKey violation, give user message, cancel insert,return to detailView ReadOnly 'I haven't figured out the correct code to capture the primary key violation EDITMsg.Text="You can not insert an duplicate record. Try Again." 'Close the sql connection sqlcon.Close() End Using End If
I've recently migrated a SQL 2000 db to SQL 2005. There is a table with a defined primary key. In 2000 when I try importing a duplicate record my application would continue and just skip the duplicates. In 2005 I get an error message "Cannot insert duplicate key row in object ... with unique index..." Is there a setting that I can enable/disable to ignore and continue processing when these errors are encountered? I've read a little on "fail package on step failure" but not quite clear on it. Any tips? Thanks alot
i have migrated a DTS package wherein it consists of SQL task.
this has been migrated succesfully. but when i execute the package, i am getting the error with Excute SQL task which consists of Store Procedure excution.
But the SP can executed in the client server. can any body help in this regard.
Hi, all I'm getting this error at runtime when my page tries to populate a datagrid. Here's the relevant code. First, the user selects his choice from a dropdownlist, populated with a sqldatasource control on the aspx side:<asp:SqlDataSource ID="sqlDataSourceCompany" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [PayrollCompanyID], [DisplayName] FROM [rsrc_PayrollCompany] ORDER BY [DisplayName]"> </asp:SqlDataSource> And the dropdown list's code:<asp:DropDownList ID="ddlPayrollCompany" runat="server" AutoPostBack="True" DataSourceID="sqlDataSourcePayrollCompany" DataTextField="DisplayName" DataValueField="PayrollCompanyID"> </asp:DropDownList> Then, I use the selectedindexchanged event to bind the data to the datagrid. Here's that code: 1 Sub BindData() 2 3 Dim ds As New DataSet 4 Dim sda As SqlClient.SqlDataAdapter 5 Dim strSQL As String 6 Dim strCon As String 7 8 strSQL = "SELECT [SocialSecurityNumber], [Prefix], [FirstName], [LastName], [HireDate], [PayrollCostPercent], " & _ 9 "[Phone], [BadgeNumber], [IsSupervisor], [SupervisorID], [IsUser], [IsScout] FROM [rsrc_Personnel] " & _ 10 "WHERE ([PayrollCompanyID] = @PayrollCompanyID)" 11 12 strCon = "Data Source=DATASOURCE;Initial Catalog=DATABASE;User ID=USERID;Password=PASSWORD" 13 14 sda = New SqlClient.SqlDataAdapter(strSQL, strCon) 15 16 sda.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@PayrollCompanyID", Me.ddlPayrollCompany.SelectedItem.ToString())) 17 18 sda.Fill(ds, "rsrc_Personnel") 19 20 dgPersonnel.DataSource = ds.Tables("rsrc_Personnel") 21 dgPersonnel.DataBind() 22 23 End Sub 24
I'm assuming my problem lies in line 16 of the above code. I've tried SelectedItemIndex, SelectedItemValue too and get errors for those, as well. What am I missing? Thanks for anyone's help! Cappela07
I've read a few different articticles wrt how the handle this error gracefully. I am thinking of wrapping my sql insert statement in a try catch and have the catch be something likeIF ( e.ToString() LIKE '% System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_PS_HR_Hrs'. Cannot insert duplicate key in object %'){lable1.text = "Sorry, you already have reported hours for that day, please select anothe rdate" } Is there a better way?TIA Dan
I have a publication that has been working great for over eight months. I have on occassion had to stop publishing and stop subscribing in normal operation without problems.
Yesterday, I ran into a problem that I am not sure how to solve.
I keep getting a distribution task failure "Violation of Primary Key constraint 'aaaashipm_pk'. Attempt to insert duplicate key in object shipm failed while bulk copying into shipm
Anyone run across this or have any suggestions -- do I have a transaction log problem, distribution data base/log problem. I have checked the identity column to make sure its not out of sync, run a check on the table to make sure its not corrupted. I have a couple of other things I am trying but was curious if anyone else had come across this?
Will let you know if I find a solution but it doesn't look good.
I tried to do the merge replication between SQL 2000 database and the SQL mobile server on PDA with SQL server management studio from SQL 2005 and I have already successfully synchronized my PDA with one small SQL server database file. However when I tried to synchronized my PDA with another larger SQL server database file, I got the error on PDA as following: €œThe row operation cannot be reapplied due to an integrity violation. Check the publication filter. [Table = AuditCriterion, operation = Insert, RowGuid = {1ee9321d-f00d-410c-8d5b-08d4220d2627}]€?. I have keep checking the size of sdf file during synchronization, I found after the size of the sdf file stop increasing for about 20 mintues, then I got the error above. Morever, AuditCriterion table have a foreign key with another table AuditElement and I have not used publication filter at this stage.
Hi All, can someone help me, i've created a stored procedure to make a report by calling it from a website. I get the message error "241: Syntax error converting datetime from character string" all the time, i tryed some converting things but nothig works, probably it is me that isn't working but i hope someone can help me. The code i use is:
CREATE proc CP_Cashbox @mID varchar,@startdate datetime,@enddate datetime as set dateformat dmy go declare @startdate as varchar declare @enddate as varchar
--print "query aan het uitvoeren"
select sum(moneyout) / sum(moneyin)*100 as cashbox from dbo.total where machineID = '@mID' and njdate between '@startdate' and '@enddate' GO
The following SP causes the error "Violation of UNIQUE KEY constraint 'AlumniID'. Cannot insert duplicate key in object [table name]. The statement has been terminated." AlumniID is the table's PK and is set to autoincrement. I'd appreciate any help or suggestions.
1 ALTER PROCEDURE dbo.sp_CreateUser 2 3 @UserID uniqueidentifier, 4 @UserName nvarchar(128), 5 @Email nvarchar(50), 6 @FirstName nvarchar(25), 7 @LastName nvarchar(50), 8 @Teacher nvarchar(25), 9 @GradYr int 10 11 AS 12 SET NOCOUNT ON; 13 --DECLARE @UserID uniqueidentifier 14 --SELECT @UserID = NULL 15 --SELECT @UserID = UserID FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName-- AND @ApplicationId = ApplicationId 16 INSERT INTO [table] 17 (UserID,UserName,Email,FirstName,LastName,Teacher,GradYr) 18 VALUES (@UserID,@UserName,@Email,@FirstName,@LastName,@Teacher,@GradYr
Hi there. We're doing a save on a MS Great Plains with SQL Server screen and get the following error: "A save operation on table 'WS_Time_Sheet_TRX_WORK' failed because a database constraint was violated."
If I hit More Info it says: [Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_WS10702_UPR00100'. The conflict occurred in database 'NSP', table 'U00100PR', column 'EMPLOYID'.
I know these messages probably make perfect sense to you guys, but I'm a newbie. Can you point me in the right direction? My thinking is that maybe I should force the foreign key constraint using the "WITH NOCHECK" option or maybe the wrong data type is mapped between the two tables sharing the Foreign Key and the key should be deleted and recreated.
Any help you can provide would be most appreciated! Assume that I'm a newbie and that I know very little. You won't hurt my feelings if you "dumb it down" so I can understand where to begin! lol I'm not even entirely sure I know where to look to edit the connection in the first place.
I have a table with 0 records. When I try to insert records using a SP, it gives the following error. Violation of UNIQUE KEY Constraint 'constraint name'. Cannot insert duplicate key in object 'Objectname'. How do I resolve this. Thanks.
ALTER TABLE [dbo].[CalCalendar] ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED
is answered with:
Server: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'PRIMARY'.
which I consider to be interesting. Anyone has an idea why? I checked documentation but I do not see an error.
Note that:
ALTER TABLE [dbo].[CalCalendar] ALTER COLUMN [OID] uniqueidentifier NOT NULL
DOES get executed, and
ALTER TABLE [dbo].[CalCalendar] ALTER COLUMN [OID] uniqueidentifier NOT NULL PRIMARY KEY
produces the same error.
Now, in my understanding this has nothing to do with an index may already exist etc. - the eror indicates a SYNTAX error, before any checking. Makes no sense to me, though, reading the documentation.
My SSIS control flow includes a standard "textbook" WMI Event Watcher Task that monitors a folder for incoming files. Generally it works fine, but now I regularly see the error message:
"Watching for the Wql Query caused the following system exception: "Quota Violation." Check the query for errors or WMI connection for access rights/permissions"
I do not see any indication of trouble in the event logs. The SSIS log simply states that it failed.
Is there any magic about WMI Event Watcher?
When I restart, it runs fine for hours.
SQL05 is 9.0.3054 on W2003 with all microsoft updates applied. It is basically a bare machine with SQL Server, SSIS running and a service that kicks in occasionally.
This is a problem I encountered when I had to detach a database file (type .mdf):
1) I went to the MS SQL Management Server Studi and detached my database file successfully from a connection called Workhorse.
2) I needed to place the .mdf database file into a zip file in order to put it on a remote server. I did this using Shared Portal. This was also successful
3) However when I tried reattaching the database file, I got this error:
CREATE FILE encountered operating system error 5A(Access denied.) while attempting to open or create the physical file "CProgram FilesMSSQL ServerMSSQLData<databasename>.mdf'
Q) The database file and log file (ldf) exist in the correct directory so I don't know what happened. Can any one help?
I recently started having trouble with my local SQL Server 8.0 database server. When I try to start it with Enterprise Manager I get the following error:
An error 5 - (Access is denied) occured while performing this service operation on the MSSQLServer service.
Nothing showed up in the SQL Server error logs. If I try to connect to it using the SQL Server Management Studio that came with SQL Server 2005 I get the following (slightly more informative) message:
Cannot connect to (local) ... (provider: Named Pipes Provider, error 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
I'm not sure which log to look at in the MSSQL folders: MSSQL.1, MSSQL.2 or MSSQL.3.
Furthermore, if I manually start the MSSQLSERVER service, then I can connect just fine with the SQL Server 2005 Management Studio but still get the weird error message with SQL Server 8.0 Enterprise Manager.
I am trying to install Project Server, and i'm having issues with sharepoint, and connecting to SQL:
dataserver is running sbs2003 sql2003 and analsys services.
server2 is running server2003 is to be the application server for project.
ProjectDb is the database that i have setup in sql.
username is the account that can control everything as administrer.
in Sharepoint is asks for the database server: <<dataserver>>
SQL Server database name: <<ProjectDb>>
I'm using windoes authentication and then i click ok, and get the error message.
I've also see the error message can not find the SQL Server, and access denied. Under ODBC i have installed the sql server information under System DSN.
I'm getting the following error when I run a stored procedure which truncates a table then inserts data into it. There are no complex joins, order, group by or union clauses in the insert statement:
Error: 0, Severity: 19, State: 0 SqlDumpExceptionHandler: Process 17 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I've read that this is fixed in SQL 7 SP2 but we're on SP4?? I have re-installed SQL SP4.
