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

Invalid Primary Key Error During Table Linking

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

Invalid Syntax Near Keyword Default (was Why Do I Get The Following Error?)

Mar 8, 2005

select * from CurrencyMaster where default=true

invalid syntax near keyword default.

View 1 Replies View Related

Parser: The Following Syntax Error Occurred During Parsing: Invalid Token, Line 1, Offset 67, ? .

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

Merge Replication Subscriber Error: Invalid Cursor State

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

Replication Fails With Primary Key Violation Error

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

Replication Error Message On Primary Key Constraint.

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

Invalid Column Name 'rowguid'. Error When Generating Snapshot For Merge Replication SQL Server 2005

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

Is Is Necessary For A Table Which Will Be Part Of The Replication Process To Have A And Primary Key Defined On It?

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

Replication :: Replicate Value Of A Computed Column Not The Formula

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

Invalid Syntax

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

SQL Statement Not Producing Any Error For Invalid Column In A Table

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

Alter Table Syntax Error - Anyone Sees The Error?

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

Invalid Syntax Near 'nvarchar'

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

Invalid Syntax In My Sproc?!?!

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

BUG? SQL 2k - IN () Allows Invalid Syntax And Fails Silently

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

Insert Into Table-Primary Key Error

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

Syntax Error ALTER TABLE

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

CREATE TABLE Syntax Error...

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

Syntax Error With &#34;alter Table&#34; Statement

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

Error Creating Table - Incorrect Syntax

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

Create Table If Not Exists Syntax Error

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

Syntax Error In Accessing An Object (table Value Function)

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

Composite Primary Key Syntax

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

Creating A Table Through Stored Procedure Shows An Syntax Error

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

SQL Server 2012 :: Syntax Error While Creating Table Dynamically

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

Syntax To Add Records If Primary Key = List

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

SqlBulkCopy Computed Column Error.

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

Computed Column In A Replicated Table

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

Table Size And Computed Columns

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

Table Partioning With Computed Column

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

CTE Error: Incorrect Syntax Near The Keyword 'with'. If This Statement Is A Common Table Expression Or An Xmlnamespaces Clause,

Aug 3, 2006

I am having this error when using execute query for CTE

Help will be appriciated

View 9 Replies View Related

OLE DB ARITHABORT Error With Indexed Computed Columns

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







Copyrights 2005-15 www.BigResource.com, All rights reserved