Replication Invalid Syntax Error: Table Has Computed Primary Key
Sep 13, 2006
Hi,
I have set up a publisher using transactional replication. ( all seems ok). The initial snapshot has been generated.
The replication share on the distributor has all the generated DDL in it.
I add a subscriber. The tables are generated up to tblCountry then I get an incorrect syntax near ')' error
The Replication Monitor shows the following code as the cause. ( bold indicates incorrect sql)
Is this a bug in Replication (as this is an autogenerated sp)or have I configured something incorrectly?
The ddl for the table index is as follows ( from the replication folder)
/----
CREATE TABLE [APP].[tblCountry](
[CountryId] AS ([ISO 3166-1 NUMERIC-3]) PERSISTED NOT NULL,
[CountryCode] AS ([ISO 3166-1 ALPHA-2]) PERSISTED NOT NULL,
[CountryName] [varchar](80) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-2] [char](2) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 ALPHA-3] [char](3) COLLATE Latin1_General_CI_AS NOT NULL,
[ISO 3166-1 NUMERIC-3] [int] NOT NULL
)
GO
---/
/------ Keys ddl (.dx)
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [PK_TBLCOUNTRY] PRIMARY KEY CLUSTERED ([CountryId])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA2] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-2])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_ALPHA3] UNIQUE NONCLUSTERED ([ISO 3166-1 ALPHA-3])
go
ALTER TABLE [APP].[tblCountry] ADD CONSTRAINT [UQ_TBLCOUNTRY_COUNTRYNAME] UNIQUE NONCLUSTERED ([CountryName])
go
--------/
/------------
Command attempted:
create procedure "sp_MSins_APPtblCountry_msrepl_ccs"
@c1 int,@c2 varchar(80),@c3 char(2),@c4 char(3),@c5 int
as
begin
if exists ( select * from "APP"."tblCountry"
where
)
begin
update "APP"."tblCountry" set
"CountryName" = @c2
,"ISO 3166-1 ALPHA-2" = @c3
,"ISO 3166-1 ALPHA-3" = @c4
,"ISO 3166-1 NUMERIC-3" = @c5
where
end
else
begin
insert into "APP"."tblCountry"(
"CountryName"
,"ISO 3166-1 ALPHA-2"
,"ISO 3166-1 ALPHA-3"
,"ISO 3166-1 NUMERIC-3"
)
values (
@c2
,@c3
,@c4
(Transaction sequence number: 0x00000016000004F2014500000000, Command ID: 213)
------------/
View 5 Replies
ADVERTISEMENT
Dec 30, 2005
Hi,
Something strange has happened to my table. I used Enterprise Manager today to delete 3 columns. When I went to re-link the table using Access Linked Table Manager, it gave me an error. I then deleted the link to the table, and tried to Link it again using 'Get External Data---Link Tables'. I am getting an error (no surprise!):
" 'dbo.tblSpaceUse.PK_RoomID' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long".
When I go into Enterprise Manager to 'manage Indexes' on the table, it shows me that the existing index is in fact dbo.tblSpaceUse.PK_RoomID.
About a month ago, I had to rename the index, because it had been pointing to the wrong table. The SQL I used to rename it (in Query Analyzer) is:
EXEC sp_rename 'dbo.tblSpaceUse.PK_RoomID', 'tblSpaceUse.PK_RoomID', 'INDEX'
I have been using the table successfully since then, until today. I have not done anything with the index; the only change I attempted was to delete 3 columns (not related to the index). I do not think I have made any changes to the table since I renamed the index.
I tried to run the rename SQL again (a desperate attempt!) and get the error message:
Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 192
Either the parameter @objname is ambiguous or the claimed @objtype (INDEX) is wrong.
Any ideas on what went wrong and what I can do to fix it???
Thanks,
Lori
View 1 Replies
View Related
Mar 8, 2005
select * from CurrencyMaster where default=true
invalid syntax near keyword default.
View 1 Replies
View Related
May 29, 2008
I'm sure I am not undestanding some basic concept here but the following formula always produces an invalid token error at the '-' sign. In this example, I'm trying to subtract out a specific month from the total (this is a simplified example, my actual formula needs to compute a % change over time using lag...)
This produces the invalid token error (it always errors at the '-' in the equation)
with member [Measures].[MyCalcMeasure] as [Measures].[MyBaseMeasure]-([Date Submitted].[Date Submitted YQMD].[month].&[2008]&[1],[Measures].[MyBaseMeasure])
select [Measures].[MyCalcMeasure] on columns,
[MyDim].[MyHierarchy].[Level1].members on rows
from MyCube
But this works
with member [Measures].[MyCalcMeasure] as [Measures].[MyBaseMeasure]
select [Measures].[MyCalcMeasure] on columns,
[MyDim].[MyHierarchy].[Level1].members on rows
from MyCube
As does this
with member [Measures].[MyCalcMeasure] as ([Date Submitted].[Date Submitted YQMD].[month].&[2008]&[1],[Measures].[MyBaseMeasure])
select [Measures].[MyCalcMeasure] on columns,
[MyDim].[MyHierarchy].[Level1].members on rows
from MyCube
What am I missing?
View 3 Replies
View Related
Aug 13, 2004
These errors occur at the subscriber. First, "The merge process could
not query the last sent and received generations" is generated, then
immediately afterwards, "invalid cursor state" is generated. We are
trying to pull a new subscription snapshot from the republisher. The
subscriber has been added and removed from the replusher's pull
subscriptions a number of times for testing. Now, we cannot get the
subscriber to re-sync with the republisher.
Any ideas?
View 1 Replies
View Related
Jun 7, 1999
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.
View 1 Replies
View Related
Oct 22, 2007
Hi, everyone,
I have problems to setup peer-to-peer replication. After I setup, I have an error message as below.
Violation of PRIMARY KEY constraint 'XPKSYS_SRTY_ADT'. Cannot insert duplicate key in object 'dbo.SYS_SRTY_ADT'. (Source: MSSQLServer, Error number: 2627)
Event ID 14151 in vent viewer.
Does anyone have a solution to resolve the problem?
Thanks a lot.
View 3 Replies
View Related
May 15, 2007
Hi there,
I have setup merge replication which successfully synchronizes with a group of desktop users using SQL Compact Edition.
However now I have setup Article Filters and when I attempt to regenerate the snapshot I get the following error:
Invalid column name 'rowguid'.
Failed to generate merge replication stored procedures for article 'AssignedCriteria'.
When I look at publication properties at the Articles page.. All my tables have the rowguid uniqueidentifier successfully added to tables and selected as a compulsory published column, apart from the table above "AssignedCriteria".. Even when I attempt to select this column in the article properties page and press ok, when I come back it is deselected again. ( The Rowguid column is however physically added to the table)
I have scripted the publication SQL and then totally reinstalled from scratch, including the database but for some reason it doesn't like this table. I remove the article filters, but still this "rowguid" is never "selected" in article properties.
We are using Uniqueidentifiers in other columns as well for historical reasons, but this doesn't appear to be a problem in other tables..
DDL For this problematic table is as follows
CREATE TABLE [dbo].[AssignedCriteria](
[AssignedCriteria] [uniqueidentifier] NOT NULL,
[CriteriaName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TargetScore] [numeric](5, 0) NULL,
[HRPlan] [uniqueidentifier] NULL,
[ActualScore] [numeric](18, 0) NULL,
[Criteria] [uniqueidentifier] NULL,
[Employee] [uniqueidentifier] NULL,
[IsActive] [bit] NULL,
[addDate] [datetime] NULL,
[totalscore] [numeric](5, 0) NULL,
[isCalc] [bit] NULL,
[Weight] [decimal](18, 2) NULL,
[ProfileDetail] [uniqueidentifier] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_7FF25DF903B6415FBFF24AC954BC88E4] DEFAULT (newsequentialid()),
CONSTRAINT [PK_AssignedCriteria] PRIMARY KEY CLUSTERED
(
[AssignedCriteria] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Thanks.
View 5 Replies
View Related
May 17, 2007
Hi there,
We're going to use replication on our database. There are tables without any primary key.
Is a primary key necessary for replication?
I have also antoher question: We want to replicate the whole OLTP database (size 30 GB).
Is snapshot replication a suitable mechanisme for this? I thougt snapshot replication overwrites all of the data, not only changed data. Or should we use another replication method?
Thanks in advance,
Patrick de Jong
View 8 Replies
View Related
Jun 16, 2015
I'm trying to replicate client data from multiple databases into a single table. So database A, B and C replicate client data to a table in database Z. There is a settings table on A, B and C that holds the businessID and I use a function in a computed column to add this to the client table. At the moment I have just database A and Z as a test and what happens is the computed column gets replicated as a computed column with the formula.
Can I change this so that I can replicate the computed value (at db A,B,C) of this column?
I don't really want to add a businessID to the table if possible as I'm going to need to add a number of other tables to this replication which will each need to have the businessID, a computed column would be much easier and save updating a load of scripts.
If the above is not possible is there some other solution available so I can identify which database the records came from in the table on database Z?
View 4 Replies
View Related
Aug 10, 2006
Im 3 months into building a web page for my company. I want to insert and update data into the data base, but i keep getting this error message (Incorrect syntax near 'nvarchar') I have no programing experience and I dont understand the stack trace. Im using visual web developer which comes with sql express. Here is all the code. Thank you all for any help offered.
jdslim
Incorrect syntax near 'nvarchar'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'nvarchar'.
Stack trace
[SqlException (0x80131904): Incorrect syntax near 'nvarchar'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +551
System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +173
System.Web.UI.WebControls.DetailsView.HandleInsert(String commandArg, Boolean causesValidation) +628
System.Web.UI.WebControls.DetailsView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +745
System.Web.UI.WebControls.DetailsView.OnBubbleEvent(Object source, EventArgs e) +162
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56
System.Web.UI.WebControls.DetailsViewRow.OnBubbleEvent(Object source, EventArgs e) +117
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56
System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +107
System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +175
System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +244
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3837
Source code For web page
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="Supplier" DataSourceID="SqlDataSource1" Height="50px" Style="z-index: 100; left: 333px;
position: absolute; top: 264px" Width="125px">
<Fields>
<asp:BoundField DataField="Supplier" HeaderText="Supplier" ReadOnly="True" SortExpression="Supplier" />
<asp:BoundField DataField="Variety" HeaderText="Variety" SortExpression="Variety" />
<asp:BoundField DataField="Arrival Date" HeaderText="Arrival Date" SortExpression="Arrival Date" />
<asp:CommandField ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>" DeleteCommand="DELETE FROM [Table1] WHERE [Supplier] = @original_Supplier AND [Variety] = @original_Variety AND [Arrival Date] = @original_Arrival_Date"
InsertCommand="INSERT INTO [Table1] ([Supplier], [Variety], [Arrival Date]) VALUES (@Supplier, @Variety, @Arrival_Date)"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Table1]"
UpdateCommand="UPDATE [Table1] SET [Variety] = @Variety, [Arrival Date] = @Arrival_Date WHERE [Supplier] = @original_Supplier AND [Variety] = @original_Variety AND [Arrival Date] = @original_Arrival_Date">
<DeleteParameters>
<asp:Parameter Name="original_Supplier" Type="String" />
<asp:Parameter Name="original_Variety" Type="String" />
<asp:Parameter Name="original_Arrival_Date" Type="DateTime" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Variety" Type="String" />
<asp:Parameter Name="Arrival_Date" Type="DateTime" />
<asp:Parameter Name="original_Supplier" Type="String" />
<asp:Parameter Name="original_Variety" Type="String" />
<asp:Parameter Name="original_Arrival_Date" Type="DateTime" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="Supplier" Type="String" />
<asp:Parameter Name="Variety" Type="String" />
<asp:Parameter Name="Arrival_Date" Type="DateTime" />
</InsertParameters>
</asp:SqlDataSource>
</div>
</form>
</body>
</html>
View 3 Replies
View Related
Sep 2, 2006
Hi,
I am using SQL Server 2005 with SP1 patch update.I have tow tables
X table fields:
ClientID,ClientName,ClientRegisteredNumber,HoldingName,HoldingRegisteredNumber,NumberOfHoldings
Y table fields:
ClientID,ClientName,RegisteredNumber,HoldingName,HoldingRegisteredNumber,NumberOfHoldings
If i run a query for X table:
SELECT RegisteredNumber FROM X it produces the error like this
Msg 207, Level 16, State 1, Line 1
Invalid column name 'RegisteredNumber'.
But if i run the query for X,Y table:
SELECT * FROM Y WHERE RegisteredNumber NOT IN
(SELECT RegisteredNumber FROM X)
It's not producing any errors.
Why this? Is this the SQL Bug or my query problem?
Can anyone explain how to solve this?
Balaji
View 3 Replies
View Related
Nov 24, 2004
Hello,
the following alter table statement:
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.
So - anyone an idea?
View 4 Replies
View Related
Jul 26, 2006
Im trying to update and modify data in a grid view in my sql data base. I keep getting this error..What is the solution to this. Sorry, I have no programing experience. All help will be greatly appreciated. thank youjdslimIncorrect syntax near 'nvarchar'. Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'nvarchar'.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Incorrect syntax near 'nvarchar'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +915 System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179 System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +835 System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +162 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56 System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +118 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +56 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +107 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +175 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +31 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +32 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +244 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3840
View 5 Replies
View Related
Feb 18, 2007
Hi I have a gridview that is being populated from a method that gets it's data from a table view.
SELECT dbo.cis_AlumniContact.Street, dbo.cis_AlumniContact.City, dbo.cis_AlumniContact.State, dbo.cis_AlumniContact.Telephone, dbo.cis_AlumniContact.Occupation, dbo.cis_AlumniContact.Description, dbo.cis_AlumniContact.Zip, dbo.cis_AlumniContact.FirstName, dbo.cis_AlumniContact.LastName, dbo.cis_AlumniContact.YearGraduate, dbo.cis_AlumniContact.Email, dbo.cis_AlumniContact.Contact, dbo.aspnet_Users.UserName, dbo.cis_StudentId.UaaStudentIdFROM dbo.aspnet_Users INNER JOIN dbo.cis_AlumniContact ON dbo.aspnet_Users.UserId = dbo.cis_AlumniContact.UserId INNER JOIN dbo.cis_StudentId ON dbo.aspnet_Users.UserId = dbo.cis_StudentId.UserId
No big deal, works great. Now when I click update I call this method
<DataObjectMethod(DataObjectMethodType.Update)> Public Sub UpdateAlumni(ByVal Street As String, ByVal City As String, ByVal State As String, ByVal Telephone As String, ByVal Occupation As String, ByVal Description As String, ByVal Zip As String, ByVal FirstName As String, ByVal LastName As String, ByVal YearGraduate As String, ByVal Email As String, ByVal Contact As Boolean, ByVal original_UserName As String, ByVal UaaStudentId As String)
Try
Dim connx As New SqlConnection(getConnectionString)
connx.Open()
Dim sqlCmd As New SqlCommand("cis_UpdateAlumniContact", connx)
sqlCmd.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NVarChar))
sqlCmd.Parameters("@UserName").Value = original_UserName
sqlCmd.Parameters.Add(New SqlParameter("@FirstName", SqlDbType.NVarChar))
sqlCmd.Parameters("@FirstName").Value = FirstName
sqlCmd.Parameters.Add(New SqlParameter("@LastName", SqlDbType.NVarChar))
sqlCmd.Parameters("@LastName").Value = LastName
sqlCmd.Parameters.Add(New SqlParameter("@Email", SqlDbType.NVarChar))
sqlCmd.Parameters("@Email").Value = Email
sqlCmd.Parameters.Add(New SqlParameter("@Street", SqlDbType.NVarChar))
sqlCmd.Parameters("@Street").Value = Street
sqlCmd.Parameters.Add(New SqlParameter("@City", SqlDbType.NVarChar))
sqlCmd.Parameters("@City").Value = City
sqlCmd.Parameters.Add(New SqlParameter("@State", SqlDbType.NVarChar))
sqlCmd.Parameters("@State").Value = State
sqlCmd.Parameters.Add(New SqlParameter("@Occupation", SqlDbType.NVarChar))
sqlCmd.Parameters("@Occupation").Value = Occupation
sqlCmd.Parameters.Add(New SqlParameter("@Description", SqlDbType.NVarChar))
sqlCmd.Parameters("@Description").Value = Description
sqlCmd.Parameters.Add(New SqlParameter("@Telephone", SqlDbType.NChar))
sqlCmd.Parameters("@Telephone").Value = Telephone
sqlCmd.Parameters.Add(New SqlParameter("@Zip", SqlDbType.NChar))
sqlCmd.Parameters("@Zip").Value = Zip
sqlCmd.Parameters.Add(New SqlParameter("@Contact", SqlDbType.Bit))
sqlCmd.Parameters("@Contact").Value = Contact
sqlCmd.Parameters.Add(New SqlParameter("@YearGraduate", SqlDbType.NVarChar))
sqlCmd.Parameters("@YearGraduate").Value = YearGraduate
Dim cmd As SqlDataReader = sqlCmd.ExecuteReader
Catch ex As Exception
Dim er As New cis_ODS_Error
er.InsertError("cis_ODS_Alumni - UpdateAlumni: " + ex.Message.ToString)
End Try
End Sub
The sproc it calls is:
ALTER PROCEDURE dbo.cis_UpdateAlumniContact
@UserName as nvarchar(50),
@Street as nvarchar(50),
@City as nvarchar(50),
@State as nvarchar(2),
@Telephone as nvarchar(50),
@Occupation as nvarchar(50),
@Description as nvarchar(50),
@Zip as nvarchar(50),
@FirstName as nvarchar(50),
@LastName as nvarchar(50),
@YearGraduate as nvarchar(4),
@Email as nvarchar(50),
@Contact as bit
AS
UPDATE cis_AlumniContact
SET Street = @Street, City = @City, State = @State, Telephone = @Telephone, Occupation = @Occupation, Description = @Description, Zip = @Zip, FirstName = @FirstName, LastName = @LastName, YearGraduate = @YearGraduate, Email = @Email, Contact = @Contact
FROM aspnet_Users INNER JOIN cis_AlumniContact
ON cis_AlumniContact.UserId = aspnet_Users.UserId
WHERE @UserName = aspnet_Users.UserName
RETURN
I get this vague error
cis_ODS_Alumni - UpdateAlumni: Incorrect syntax near 'cis_UpdateAlumniContact'
If I execute the SQL from the editor it works fine. The only thing different about this sproc and my other update sprocs is the inner join. Any idea? Thanks
View 2 Replies
View Related
Jul 23, 2005
Behavior I found:Invalid Column name in select embedded in a IN() clause appears toreturn a NULL, and fails silently - completing the query givingincorrect results.Behavior I expected:RunTime error, as column didn't exist.-------------------------------------------------------createtable #THEONE( RowNo int not null )createtable #THEOTHER( RowNumb int not null )select *from #THEONEwhere RowNo not in (-- SHOULD THROW RUNTIME ERROR --select RowNo from #THEOTHER)--------------------------------------------------------- select @@VERSION-- Microsoft SQL Server 2000 - 8.00.871 (Intel X86)-- Oct 21 2003 16:16:50-- Copyright (c) 1988-2003 Microsoft Corporation-- Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
View 4 Replies
View Related
Jul 20, 2005
I'm trying to do multiple insert statements. The table looks likethis:CREATE TABLE $table (CNTY_CNTRY_CD char(3),ST char(2),CNTY_CNTRY_DESCR varchar(50),CNTY_CNTRY_IND char(1),HOME_CNTRY_IND char(1),CONSTRAINT cnty_key PRIMARY KEY (CNTY_CNTRY_CD, ST))I'm using 2 fields for the primary key constraintMy insert statement looks like this:INSERT INTO $table(CNTY_CNTRY_CD,ST,CNTY_CNTRY_DESCR)VALUES(?,?,?)I've been through the list of values and none have both the sameCNTY_CNTRY_CD and ST and yet, this is the error message I'm getting:DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQLServer]Violation of PRIMARY KEY constraint 'cnty_key'. Cannot insert duplicatekey in object 'event_CNTY_CNTRY_CD'. (SQL-23000)[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has beenterminated.. (SQL-01000)(DBD: st_execute/SQLExecute err=-1)Why is it looking for unique in just the one column instead ofreferencing both? What do I need to do to get this to work? Help!
View 1 Replies
View Related
Jul 25, 2006
HiHaving a problem with a ms sql 2000 server. The script below wascreated i SQL manager 2005 lite and gives a syntax error near '('ALTER TABLE [dbo].[Community_ActivityLog]ADD CONSTRAINT [PK_Community_Errors]PRIMARY KEY CLUSTERED ([activity_ID])WITH (PAD_INDEX = OFF,IGNORE_DUP_KEY = OFF,STATISTICS_NORECOMPUTE = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON)ON [PRIMARY]GOAny ideas of what might be wrong?/Michael
View 1 Replies
View Related
Sep 10, 2007
CREATE TABLE Agents(ID COUNTER NOT NULL CONSTRAINT constraintName_pk PRIMARY KEY,Name VARCHAR(255),Supervisor INTEGER,MasterCalendarVisible BOOLEAN default false)I'm using this against an Access DB using JET driver... I keep gettinga syntax error which goes away when I remove the last line... Anyideas whats wrong with it?Thanks!
View 1 Replies
View Related
Mar 27, 2001
Folks!
What is wrong with my syntax with the following command?:
alter table EmployeeInfo alter column OriginDate smalldatetime not null default getdate()
I'm getting:
Incorrect syntax near the keyword 'default'
Currently, in my table OriginDate is nullable with no default.
Thanks in advance for your help!
APF
View 2 Replies
View Related
May 27, 2014
Have been given this code to create a table
CREATE TABLE 'emaillist' (
'id' INT(11) NOT NULL AUTO_INCREMENT,
'clientname' VARCHAR(200) NOT NULL DEFAULT '0',
'email' VARCHAR(200) NOT NULL DEFAULT '0',
PRIMARY KEY ('id')
);
when I execute it says
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'emaillist'.
What the correct code should be?
View 3 Replies
View Related
Mar 10, 2007
CREATE TABLE IF NOT EXISTS TempA (id int);
CREATE TABLE IF NOT EXISTS TempB (id int);
For some reason the above statements are giving me syntax errors?
I want to create table only if it does not already exist.
Also, can the same "if not exists" clause be applied to "DROP TABLE" and "TRUNCATE" ?
thx in advance .
View 3 Replies
View Related
Aug 29, 2007
I am trying to access data from a database to print a report. The code I am using says I have a syntax error regarding the object person_info. Person_Info is a table value function on the SQL Server (2005). Does anybody see a problem with this line of code? Thanks for your help! Regards, Steve
"INNER JOIN (SELECT * Person_Info FROM (" & Session("current_project") & ")) ON pc.personID_fk=pe.personID_pk " & _
View 4 Replies
View Related
Nov 27, 2007
I'm trying this
Code:
CREATE TABLE Rating
(ContentID int NOT NULL PRIMARY KEY REFERENCES Content_(ContentID),
UserID int NOT NULL PRIMARY KEY REFERENCES Usr(UserID),
rating tinyint DEFAULT 2,
LastRead smalldatetime NOT NULL DEFAULT CURRENT_TIMESTAMP)
And it is telling me this:
Msg 8110, Level 16, State 0, Line 14
Cannot add multiple PRIMARY KEY constraints to table 'Rating'.
So how do I combine two non-unique foreign keys, the combination of which is unique, into one primary key?
View 2 Replies
View Related
May 26, 2000
hai guys,
i have written a stored procedure which creates a table ex:
USE PUBS
GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'RC_STRPROC')
DROP PROCEDURE RC_STRPROC
GO
USE PUBS
GO
CREATE PROCEDURE RC_STRPROC
(@TBLNAME VARCHAR(35), @COLVAL1 VARCHAR(35), @COLVAL2 VARCHAR(35))
AS
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = '@TBLNAME')
DROP TABLE @TBLNAME
CREATE TABLE @TBLNAME
(@COLVAL1, @COLVAL2)
GO
it gives an syntax error at '@tblname'
can u guys tell me the problem
thanks
hiss
View 2 Replies
View Related
Apr 19, 2015
I am getting error when I am trying to create table on runtime
Declare @FileName varchar(100)
Declare @File varchar(100)
set @FileName='brkrte_121227102828'
SET @File = SUBSTRING(@FileName,1,CHARINDEX('_',@FileName)-1)
--=select @File
[Code] ....
Error massage:-
Msg 203, Level 16, State 2, Line 16
The name 'CREATE TABLE DataStaging.dbo.Staging_brkrte ( [COL001] VARCHAR (4000) NOT NULL, [Id] Int Identity(1,1), [LoadDate] datetime default getdate() )' is not a valid identifier.
How to resolve above error....
View 4 Replies
View Related
Sep 21, 2005
I am new to SQL administration.[color=blue]>From a list of IDs that are the primary key in one table (i.e. Customer[/color]Table), I want to make changes in tables that use those IDs as aforeign key.Basically I want to say:If fk_ID is in list [1,2,3,4,5] thendo these statements to that recordEnd ifWhere do I begin?Thanks for help with this low-level view of SQL programming.-tom
View 8 Replies
View Related
Feb 27, 2008
Hi,
Iam using SqlBulkCopy to copy data of all the tables from one database to another database. SqlBulkCopy runs just fine but it throws exception for one of the tables which is having computed column.
snnipet of code is
For Cnt = 0 To oDS.Tables(0).Rows.Count - 1
oSqlCmd2 = New SqlCommand
oSqlCmd2.Connection = oConn
oSqlCmd2.CommandText = "select * from " & "" & oDS.Tables(0).Rows(Cnt).Item(0).ToString & " "
'Dim reader As SqlDataReader
oDS2 = New DataSet
oDA2 = New SqlDataAdapter
oDA2.SelectCommand = oSqlCmd2
'reader = oSqlCmd2.ExecuteReader
oDA2.Fill(oDS2, "test")
Dim bulkData As SqlBulkCopy = New SqlBulkCopy(oConn2)
'Get the data from the second database and fill the dataset
oDA2 = New SqlDataAdapter
bulkData.DestinationTableName = "" & oDS.Tables(0).Rows(Cnt).Item(0).ToString & " "
bulkData.BatchSize = 1000
bulkData.BulkCopyTimeout = 2000
bulkData.WriteToServer(oDS2.Tables(0))
oDS2.Dispose()
oSqlCmd2.Dispose()
Next
For one of the tables iam getting the following error
The column "Col4" cannot be modified because it is either a computed column or is the result of a UNION operator
Since iam fetching table names at runtime its not possible to use columnMappings.
So, how to come out of this situation.
TIA
View 6 Replies
View Related
Apr 10, 2015
I have a strange problem. I have a computed column in a replicated table, the Formula is as follows:
(isnull(hashbytes('SHA2_256',CONVERT([varchar](256),[AccrualReference],(0))),(0))) the column is also Persisted.
This gets around a case sensitivity issue and is used as the Primary Key column, which works well.The problem is that this table is then replicated to another server. On the subscriber the value of this computed field is being returned as 0x00000000 for every row, so this must be the ISNULL function doing its job. But why? The AccrualReference is the true PrimaryKey and is never NULL.
If I remove the computed specification and set the field up as varbinary(64) the value then gets replicated. This then means maintaining a different table schema for in excess of 500 tables.
View 2 Replies
View Related
Jul 23, 2005
In SQL Sever, do the size of computed columns gets added to the totalsize of the tables? Does SQL server stores the actual values incomputed columns?Thanks_GJK
View 1 Replies
View Related
Oct 25, 2006
This full version of this reporting table will have about 12 million row for each of three years. Prior years will be in separate partitions and frozen but the current year will be reloaded each night by source_key, probably in parallel.
I am trying to do this with a computed column but I can't slide the partition back into the main table due to an apparent problem with the Check constraint. I have tried everything I can think of and still can't get it to work.
I hope I am missing something simple. Anyone know why this does not work or how to fix it?
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'db_template.dbo.foo_year_source_partition_test_stage' allows values that are not allowed by check constraints or partition function on target table 'db_template.dbo.foo_year_source_partition_test'.
------------------------------------------------------------
CREATE PARTITION SCHEME zzYearSourcePScheme
AS
PARTITION zzYearSourceRangePFN
TO
(
[fg_template_0],
[fg_template_0],
[fg_template_0],
[fg_template_0],
[fg_template_0]
)
go
CREATE TABLE [dbo].[foo_year_source_partition_test](
[detail_date] [datetime] NULL,
[source_key] [int] NULL,
[year_source] AS ((CONVERT([char](2),right(datepart(year,[detail_date]),2),0)+'-')
+right('0000'+CONVERT([varchar](3),[source_key],0),(3))) PERSISTED,
[ys_id] int identity (1,1)
) ON zzYearSourcePScheme(year_source)
go
create unique clustered index ix_year_source_ys_id on [foo_year_source_partition_test] ([year_source], [ys_id]) ON zzYearSourcePScheme(year_source)
go
insert into [foo_year_source_partition_test] values('20060131',2)
insert into [foo_year_source_partition_test] values('20060131',3)
insert into [foo_year_source_partition_test] values('20060131',4)
SELECT *, $PARTITION.zzYearSourceRangePFN(year_source) AS Partition from [foo_year_source_partition_test] order by detail_date
go
CREATE TABLE [dbo].[foo_year_source_partition_test_stage](
[detail_date] [datetime] NULL,
[source_key] [int] NULL,
[year_source] AS ((CONVERT([char](2),right(datepart(year,[detail_date]),2),0)+'-')
+right('0000'+CONVERT([varchar](3),[source_key],0),(3))) PERSISTED,
[ys_id] int identity (1,1)
) --on same one ON YearSourcePScheme(year_source)
create unique clustered index ix_year_source_ys_id
on [foo_year_source_partition_test_stage] ([year_source], [ys_id]) --ON YearSourcePScheme(year_source)
ALTER TABLE db_template.dbo.foo_year_source_partition_test
SWITCH PARTITION 3 to db_template.dbo.[foo_year_source_partition_test_stage]
TRUNCATE TABLE db_template.dbo.[foo_year_source_partition_test_stage]
ALTER TABLE db_template.dbo.foo_year_source_partition_test_stage
WITH CHECK
ADD CONSTRAINT CK_foo_year_source_partition_test_stage_YearSource
CHECK
(
[year_source] = '06-003'
)
insert into foo_year_source_partition_test_stage values('20060202',3)
insert into foo_year_source_partition_test_stage values('20060303',3)
insert into foo_year_source_partition_test_stage values('20060404',3)
insert into foo_year_source_partition_test_stage values('20060505',3)
ALTER TABLE db_template.dbo.foo_year_source_partition_test_stage
SWITCH TO db_template.dbo.foo_year_source_partition_test PARTITION 3
View 6 Replies
View Related
Aug 3, 2006
I am having this error when using execute query for CTE
Help will be appriciated
View 9 Replies
View Related
Oct 31, 2002
Instead of using Full-Text indices, which I don't like to manage, we've tried to use seperate tables that contain recordID, the word, a count of the word in the parent field and computed column which is the CHECKSUM() of the word column. I indexed the checksum column with a clustered index.
Works great in Query Analyser. But when the ASP page calls it, I get this message:
Microsoft OLE DB Provider for SQL Server (0x80040E14)
INSERT failed because the following SET options have incorrect settings: 'ARITHABORT'.
Same for updates and deletes. The question is how should these SET settings be done? Any ideas would be greatly welcomed.
Thanks
Jason
View 3 Replies
View Related