I'm trying to run a query with no luck, I'm getting
"The multi-part identifier "table2.qty" could not be bound."
Here is my query:
select cust_nam , dat ,item_no , desc_lin_1,table2.qty
prc, cost, ret_reas_cod,(table2.qty*cost)
from table1,table2 where hdr_ticket_no = ticket_no and dat >= '20080101' and cat = 'test' and hdr_ticket_no in
( select ticket_no from table1 where dat >= '20080101')
order by dat
I have 2 databases on my sql server. If i choose the other one then this query runs but, if i choose the one that contains the tables I just get that error.Strange part is that it runs under the database that doesn't even contains the table.
If i remove qty from the list then it runs ok. both tables have a qty column. What I'm doing wrong? Any help would be appreciated.
I am running a script against a couple of databases on my SQL Server 2000 Standard Edition Instance and I am getting the following in the results pane:
"Default bound to column" I have searched the MS Knowledge base and found a couple of vague references to this. Does anyone know why I might be getting this??? the script 'seems' to run fine.. except for the funky error in the results pane. Script is attached. Thank you!!
SELECT [Hourly] ,count(case when C.Documenttype=1 THEN '1' END) as EDI ,count(case when C.Documenttype=2 THEN '2' END) as SI ,count(case when C.Documenttype=1 THEN '1' END) + count(case when C.Documenttype=2 THEN '2' END) as GrandTotal
I'm running 2014 enterprise and getting an error on this form of a query...it says the multi part identifier "mns.col3" could not be bound. I'm aware that a cross apply would be more appropriate but i'm just prototyping and probably going to move to a set based approach anyway.The udf returns a table.
select mns.col1, mns.col2 from table1 mns left join dbo.udf_udf1(@firstofmonth,@lastofmonth, mns.col3) x on 1=1
In SQL Server 2000 can we bind objects, like tables, etc. to memory/cache to enhance the performance? This feature is available in Sybase and I am very new to SQL Server.
Hello ! i am new to databases so i would be grateful for your help. I am using SQL Server 2000 and vb to access and update the records of a database.
I want your opinion about the way i am displaying data. I have created a form with textboxes etc. which display the fields of the table. Now when i load the form i populate a recordset with a "select ... from... " query.
Dear All, i'm getting an error "index array out of bound" while saving the record.
sql server 2005 with service pack1 system is working fine. but at client place it is sql server 2005 with SP2. there we are getting error. any help....
windows 2003 sp1 at both the places. only the difference is in sql server. how can i handle this
Hello Everyone, I€™m trying to update my Target where the available value of each record is not equal to the Source records. But I€™m getting this error. The code is below.
UPDATE "ADMINDB"."BED_VISIT" SET "ADMIT_DATE" = ?, "ADMIT_ELIG" = ?, "ADMIT_LOCATION" = ?, "ADMIT_TIME" = ?, "ADMIT_TYPE" = ?, "AVAILABILITY_CDE" = ?, "BED_ARR_DATE" = ?, "BED_ARR_TIME" = ?, "BED_CLASS" = ?, "BED_CLASS_DATE" = ?, "BED_NO" = ?, "BED_OUT_REASON" = ?, "BED_READINESS" = ?, "BED_SPECIALTY" = ?, "CHIEF_COMPLAINT" = ?, "CONFIDENTIAL" = ?, "DIS_DATE_EST" = ?, "DOCTOR_CODE" = ?, "F_SOUNDEX" = ?, "HOSP_BED_NO" = ?, "HOSP_CODE" = ?, "IN_OUT_DATE" = ?, "IN_OUT_STATUS" = ?, "INPUT_BY" = ?, "INPUT_DATE" = ?, "INPUT_TIME" = ?, "ISOLATION_CODE" = ?, "LENGTH_STAY_E" = ?, "NO_OF_BEDS" = ?, "NON_DIS_REASON" = ?, "NURSE_STATION" = ?, "ORIG_DIS_DATE" = ?, "OUTSIDE_REF" = ?, "PAT_ACC_FLAG" = ?, "PHONE_EXT" = ?, "PROB_SPECIALTY" = ?, "REFER_DEPT" = ?, "REFER_DOC" = ?, "REFER_SOURCE" = ?, "SMOKER_FLAG" = ?, "SPEC_TRAN_DATE" = ? WHERE "PATNO" = ? AND "ADMIT_ELIG" != ? AND "ADMIT_LOCATION" != ? AND "ADMIT_TIME" != ? AND "ADMIT_TYPE" != ? AND "AVAILABILITY_CDE" != ? AND "BED_ARR_DATE" != ? AND "BED_ARR_TIME" != ? AND "BED_CLASS" != ? AND "BED_CLASS_DATE" != ? AND "BED_NO" != ? AND "BED_OUT_REASON" != ? AND "BED_READINESS" != ? AND "BED_SPECIALTY" != ? AND "CHIEF_COMPLAINT" != ? AND "CONFIDENTIAL" != ? AND "DIS_DATE_EST" != ? AND "DOCTOR_CODE" != ? AND "F_SOUNDEX" != ? AND "HOSP_BED_NO" != ? AND "HOSP_CODE" != ? AND "IN_OUT_DATE" != ? AND "IN_OUT_STATUS" != ? AND "INPUT_BY" != ? AND "INPUT_DATE" != ? AND "INPUT_TIME" != ? AND "ISOLATION_CODE" != ? AND "LENGTH_STAY_E" != ? AND "NO_OF_BEDS" != ? AND "NON_DIS_REASON" != ? AND "NURSE_STATION" != ? AND "ORIG_DIS_DATE" != ? AND "OUTSIDE_REF" != ? AND "PAT_ACC_FLAG" != ? AND "PHONE_EXT" != ? AND "PROB_SPECIALTY" != ? AND "REFER_DEPT" != ? AND "REFER_DOC" != ? AND "REFER_SOURCE" != ? AND "SMOKER_FLAG" != ? AND "SPEC_TRAN_DATE" != ?
The error:
[OLE DB Command [20874]] Error: An OLE DB error has occurred. Error code: 0x80040E10. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80040E10 Description: "ORA-01008: not all variables bound".
HI Gurus, Trying to alter an existing Trigger on an insert event on one of our tables. The following sql generates: "Msg 4104, Level 16, State 1, Procedure insxECSIBHEADER, Line 4 The multi-part identifier "INSERTED.PROJECT" could not be bound." Here's the SQL itself: set QUOTED_IDENTIFIER ONgoALTER TRIGGER [insxECSIBHEADER] ON [dbo].[xECSIBHeader] AFTER INSERT AS BEGIN IF SUBSTRING(INSERTED.[PROJECT],4,2) = 'DR' BEGIN UPDATE xECSIBHEADER SET xECSIBHEADER.[IBNUMBER] = LEFT(xECSIBHEADER.[PROJECT],2) + 'D' + Cast(xECSIBHEADER.[IBHeaderKey] AS VarChar(15)) FROM INSERTED INS INNER JOIN xECSIBHEADER ON xECSIBHEADER.[IBHeaderKey] = INS.[IBHeaderKey] WHERE INS.[IBNUMBER] IS NULL END ELSE BEGIN UPDATE xECSIBHEADER SET xECSIBHEADER.[IBNUMBER] = LEFT(xECSIBHEADER.[PROJECT],2) + Cast(xECSIBHEADER.[IBHeaderKey] AS VarChar(15)) FROM INSERTED INS INNER JOIN xECSIBHEADER ON xECSIBHEADER.[IBHeaderKey] = INS.[IBHeaderKey] WHERE INS.[IBNUMBER] IS NULL END END
I am trying to call a userdefined function and it excepts and date like 4/15/2007 but when i call my function i get an error
the multipart identifier datetime,now could not be bound and this is my codePublic Function GetPeriods() Dim myconnection As New SqlConnection(System.Configuration.ConfigurationManager.AppSettings(APPSETTING_DBCONNECTION)) Dim query1 As String = "Select dbo.udf_Quarter(Datetime.Now)" 'Max(PeriodId) From Period" myconnection.Open() Dim MaxPeriodId As Integer Dim cmd As New SqlCommand(query1, myconnection) cmd.CommandType = CommandType.Text MaxPeriodId = cmd.ExecuteScalar() Response.Write(MaxPeriodId) Session("PeriodId") = MaxPeriodId
Return (MaxPeriodId)
End Function any help will be appreicated Regards, Karen
I am trying to execute a query in SQL SERVER 2005 for calculating days difference for each. I created a function because there are a lot of calculations. In SPeriods table I have 4 fields that I want to pass as parameters in the table-value function TFC_date_diff, but I receive the error "The multi-part identifier "CC.start_period_no" could not be bound. Is there a solution for this ? SELECT CC.start_period_no, CC.end_period_no, CC.start_year, CC.end_year, TFC_date_diff.dates_differnceFROM dbo.[SPeriods] AS CC CROSS JOIN dbo.TFC_date_diff(CC.start_period_no, CC.end_period_no, CC.start_year, CC.end_year) AS TFC_date_diff_1 Thanks a lot in advance!
I am looking for detailed information with eaxmples, about creating extended stored procedures using sp_getbindtoken, sp_bindsession for our ASP.NET application.
Microsoft has very less info on that.
If anyone has some information or link to the article related to Using Bound Connections. please help me out.
I am doing a little research on Google about this topic and I ran intothis thread:http://groups.google.com/group/micr...dc13d4ee6758966I read SQL Server MVP Louis Davidson's post saying:"Actually they are more likely to drop the concept of bound defaults.Constraints are the standard way to do this, and really should be the wayyou create defaults anyhow."Even I read in the Microsoft SQL Server Introduction (SQL 7 bookpage 244, however we're using SQL Server 2000):"Constraints define rules regarding the values allowed in columns and arethe standard mechanism for enforcing integrity, preferred over triggers,rules, and defaults. They are also used by the query optimizer to improveperformance in selectivity estimation, cost calculations, and queryrewriting."Why constraint defaults are better? The second sentence about constraintshaving better optimization, I am guessing they don't mean this aboutDefault Constraints, rather the other type of constraints?Because I don't see how a Default Constraint have anything to do withperformance? Isn't default only to do with new records being created?At work we are setting all tables' columns to have constraint defaultsof 0 or ' ' (space character) in order not to have any column with theNULL value. Therefore we have dozens of files containing statements like:alter table TABLE1 add constraint TABLE1_ID_DFDEFAULT(' ') FOR IDgoalter table TABLE1 add constraint TABLE1_QUANTITY_DFDEFAULT(0) FOR QUANTITYgoFirst I was thinking to create 3 SQL Defaults called:DefaultZeroDefaultSpaceDefaultDateand then bind these defaults to all the columns of all tables excludingprimary keys. After creating the tables I would enumerate throughall the columns and bind one of these three Defaults based on theirdatatype:number = DefaultZerotext type = DefaultSpacedate type = DefaultDateAnd then unbind the ones that we specifically need to specify otherdefault values.So my question is should I do this by using sp_binddefault or stickwith using Default Constraints inside a table/columns loop code?Thank you
Hi All , i am getting this multi-part identifier not found error followingis my stored procedure
ALTER PROCEDURE [dbo].[Ab_LP]
@gyr char(4),
-- 'A', --'S', only submitted will be returned;
--A=All, or any other letter other than 'S', all will b returned;
@ExONo char(1)='A'
AS
DECLARE @sql VARCHAR(1500)
SET @sql = 'SELECT LTRIM(RTRIM(t1.Last_Name))+'',''+LTRIM(RTRIM(t1.First_Name)) as Name,t2.emaddr as Email,t3.* FROM namesTable as t2,PersonalTable as t1'
IF (@ExONo='S')
SET @sql = LTRIM(RTRIM(@sql)) + ',LePAb AS t3 WHERE t3.emaddr = t2.emaddr AND t2.code = ''ABC'' AND t1.Emplid=t2.Emplid '
ELSE
SET @sql = LTRIM(RTRIM(@sql)) + ' LEFT JOIN LePAb AS t3 ON
t3.emaddr=t2.emaddr WHERE t2.code = ''ABC'' AND t1.Emplid=t2.Emplid '
IF (@gyr <> 0)
SET @sql = LTRIM(RTRIM(@sql)) + ' AND t2.gyr='+@gyr
ELSE
SET @sql = LTRIM(RTRIM(@sql)) + ' AND t1.Code = ''ABC'' '
SET @sql = LTRIM(RTRIM(@sql)) + ' ORDER BY Name'
print @sql
EXEC (@sql)
I am geting an error as
The multi-part identifier "t2.emaddr" could not be bound. i have tried all the possible tricks but in vain. plz help.. thanks in advance
for Version_Resources_Allocation.week IN ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52], [53])) as pvt
inner join Version_Resources_Allocation on WorkTeam.workteam_id = Version_Resources_Allocation.workteam_id
inner join WorkTeam_Resources_Assignments on WorkTeam_Resources_Assignments.resource_id = Version_Resources_Allocation.resource_id
and WorkTeam_Resources_Assignments.workteam_id = Version_Resources_Allocation.workteam_id
inner join calendar on calendar.year = WorkTeam_Resources_Assignments.year
and calendar.month = WorkTeam_Resources_Assignments.month
and calendar.year = Version_Resources_Allocation.year
and calendar.week = Version_Resources_Allocation.week
inner join Version on Version.version_id = Version_Resources_Allocation.version_id
inner join Account on Account.account_id = Version.account_id
--hierarchy joins
inner join line_module on line_module.line_module_id = WorkTeam.line_module_id
inner join Account as acc on acc.account_id = line_module.account_id
inner join vp on vp.vp_identifier = acc.vp_identifier
inner join division on division.division_id = vp.division_id
--Resources joins
inner join msp_resources on msp_resources.emp_code = Version_Resources_Allocation.resource_id
where Version_Resources_Allocation.resource_id in (select top 100 percent emp_code from #staffEmployees where id between ((@quota * (@pageIndex - 1) + 1)) and (@quota * @pageIndex) order by res_name)
and
(
(Version_Resources_Allocation.year = @startYear and Version_Resources_Allocation.week >= @startWeek and Version_Resources_Allocation.week <= (case when @startYear = @endYear then @endWeek else @maxWeeksInFirstYear end))
or
(Version_Resources_Allocation.year = @endYear and Version_Resources_Allocation.week <= @endWeek and Version_Resources_Allocation.week >= (case when @startYear = @endYear then @startWeek else 1 end))
)
and
(
pct_availability!=0 or (pct_availability is null and staffing_pct_availability!=0)
)
and
(
(WorkTeam_Resources_Assignments.year = @startYear and WorkTeam_Resources_Assignments.month >= @startMonth and WorkTeam_Resources_Assignments.month <=
case when @startYear = @endYear then @endMonth else 12 end
)
or
(WorkTeam_Resources_Assignments.year = @endYear and WorkTeam_Resources_Assignments.month <= @endMonth and WorkTeam_Resources_Assignments.month >=
case when @startYear = @endYear then @startMonth else 1 end
I have a SqlDataSource that I need to remove the first 3 rows from before it is bound to a GridView. How would I go about doing this? (if I could remove them at the db level in the sproc I would, but right now that is not an option - so I need to do it once I've already received the data) Thanks.
I have several tables in a deployed database in which the primary key is of type int, and autoincrements by 1 each time a record is added. My question is, since ints are 32-bit, what happens when its value reaches 4,294,967,296? I know that seems like an extrememly large amount of records, but when we imported the data into the database it started at key value 1,000,000. I don't know how to make it use lower numbers which are currently not being used (numbers below 1,000,000), and I am worried I will have problems when I reach the upper bound. What kind of problems could this cause? Should I change the primary key's type?Thanks!
I try to automate ans insert (that ok) and update jobs
but these part is not working I got the following error
The multi-part identifier "t2.customerid" could not be bound.
any idea'll be welcomed I'm not so good in programming and sql
update [ADOS].[dbo].[Customers] set t2.companyname = t1.companyname where t2.customerid = (SELECT t1.companyname FROM [Northwind].[dbo].[Customers] T1 inner join [ADOS].[dbo].[Customers] T2 on (t1.customerid = t2.customerid and t1.companyname <>t2.companyname))
Hello, I am running Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright ( c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 260 0: Service Pack 2) on Windows. using jdbc <driver name="com.microsoft.sqlserver.jdbc.SQLServerDriver" tag="sqlserver" url="jdbcqlserver://[host]:[port];DatabaseName=[dbname];user=[user];password=[password]"> <jar subdir="sqlserver" name="sqljdbc.jar" /> </driver>
I print out the following info to make sure I get the right driver: 'JDBC driver details' , ' driver class name=com.microsoft.sqlserver.jdbc.SQLServerDriver driver majorVersion=1 driver minorVersion=1 driver a genuine JDBC CompliantTM driver=true'
I execute the following query SELECT p.cust_id, p.stock_units FROM dbo.customer_portfolio p WHERE p.stock_price = ?
and get this message: com.microsoft.sqlserver.jdbc.SQLServerException: The multi-part identifier "p.stock_price" could not be bound. stack trace: [com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source) com.microsoft.sqlserver.jdbc.SQLServerParameterMetaData.<init>(Unknown Source) com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getParameterMetaData(Unknown Source)
If I change it to:
SELECT cust_id, stock_units FROM dbo.customer_portfolio WHERE stock_price = ?
i.e. do not use correlation names, it executes as expected. Is there anything wrong with using correlation names in the query ? or is it a bug ?. Any help will be welcome.
I am trying to run a Common Table Expression (CTE) in SQL Server 2005 but I receive the error
'Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "ep.ProjectUID" could not be bound'.
What does this error mean?
My SQL is:
Code Snippet WITH Tasks (TaskParentUID, TaskUID, ProjectName, TaskName, Level) AS ( SELECT et.TaskParentUID, TaskUID, ProjectName, TaskName, 0 AS Level FROM dbo.MSP_EpmProject as ep INNER JOIN dbo.MSP_EpmTask as et ON ep.ProjectUID = et.ProjectUID WHERE et.TaskParentUID = et.TaskUID UNION ALL SELECT et.TaskParentUID, et.TaskUID, ep.ProjectName, et.TaskName, Level + 1 FROM dbo.MSP_EpmProject as ep INNER JOIN dbo.MSP_EpmTask as et ON ep.ProjectUID = et.ProjectUID INNER JOIN Tasks AS t ON et.TaskParentUID = t.TaskUID ) SELECT t.TaskParentUID, t.TaskUID, ProjectName, t.TaskName, Level FROM Tasks as t INNER JOIN dbo.MSP_EpmTask as et ON Tasks.TaskParentUID = et.TaskParentUID WHERE Level = 0
The TaskParentUID has data-type of UniqueIdentifier and it's child is TaskUID which is also a UniqueIdentifier type. My goal of this CTE is to list all child TaskUIDs for each TaskParentUID in a recursive fashion.
I have placed a textbox in a page footer of SSRS which is linked with a databound text box in the body of the report.. which i intended would give the report databound footer... but now the problem is tat they dont show up on every page.. earlier they use to mostly not show up at all or show up on the first or the last page...
could some1 help.??
once i render it to pdf.. all the page footers cant be seen.
I am trying to create a model using microsoft Linear Regression algorithm. But I want to constrain the coefficient of the parameters to non-negative value. There is concept of bound in SAS where we can specify the range of the coefficient. Does any of the SSAS mining algorithms support restricting the coefficient value?
I'm using VB.NET on VS 2005 with SQL 2005 Express. I have one master table with two child tables related with a FK. The master table in the SQL 2005 database has an Identity Column set up as the primary key with Increment as "1" and Seed as "1001". Each child table in the database has one Identity Column set as a primary key also with Increment = 1 Seed = 1001. Each child table also has a column set as a foreign key to the master primary key. The typed dataset was created in Designer and the table adapters, bindingsources, bindingnavigator and DGVs were created by dropping the tables on the form. I added some code for each child table in the bindingsource button save event for the two additional child tables: ' my added code child1BindingSource.EndEdit childT1ableAdapter.Update(ds.Child1Table)
Now the trouble. I changed the dataset (not the actual database) columns for each table's PK to AutoIncrement with Seed "-1" and Step "-1" to allow the client to create new records that do NOT match an existing key in the backend database. I've read on the forums where this is best pratice to prevent collisions. Please correct me if this is not true. When I create a new record by clicking the "+" button on the binding navigator, I see the "-1" inserted into the correct bound field for the primary key column of the parent table. I can then click into either or both the child DGV empty rows and see that same "-1" value appear in the correct FK column of the DGV. Everything looks good until I try and save the newly added rows were I get the "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Parent_Child" error. If I change the dataset back to Seed 1, Step 1 then it does not throw an error during save and does save the child data. However the value of the Primary key on the DGV cells displays the value "1001" (or the next auto incrementing value) and NOT "1" which is the seed value for that dataset's table's primary key column. I can change the Seed and Step back to "-1" on the dataset tables and get the error during save or change the dataset table primary key column to Seed 1, Step 1 and the data is saved but with an incorrect identity value. Why does it use the identity value from the database and not the dataset? It seems to me it should work the same no matter what I assign to the autonumber seed value.
I had previously posted this in the Windows Forms forum but with no solution.
I have an service that connects using a system DSN using the SQL Server 2000 driver. On my development system with SQL Server 2005 Express installed, the queries work fine. I prepare a statement and then bind the columns that will be in the result set. On my system, I get all the data as it should be but on a live system, I do not get the proper data for the last three rows. I don't get any error messages from the query or the column binding and there are also no extra information messages that I can retrieve.
The table is something like the following. The names are changed to protect the guilty.
On my system, running the exact same binary code as the client machine, I get all of the data from all of the columns as it should be. The problem is when I run on a client's system, the CLIENT_NAME, CODE, and PHONE columns return null values even when there is data there. On my development system the SQL Server instance runs on the same machine. On the client's sytem I am connecting to a remote instance of SQL Server 2000 on another system on the network.
My quandry is what could be different between the two systems that is causing me the problems?
I want to search for alphanumeric values between an upper and lower bound in a sql database.For example: search for a serial number like pvf-456-3b. Upper bound is q, lower bound is g.I should then get every serial number starting with g - q.If possible the bounds should be more specific like "search for serial number between gt2 and qy"Can anybody help me out?
Using 3.5 If I have a sqldatasource on the page, is it run if it is not bound to a data object like a gridview? Seems like if i want to access the data (like set a label text) from the sqldatasource I have to use code to first create a dataview then pick throught it. This seems like I'm running it twice. I'm new at .net so I dont know how to tell. I don't want to write data select code programatically when I can just through an SDS on the page, but wondered it it ran just because it's on the page.
Hi,We moved our stored procedure from sql 2000 to sql 2005 and we're getting few weird errors:Msg 4104. multi-part identifier /table.column/ could not be bound.Do we have to change anything in the stored procedure in order to make it work for sql 2005? Errors point to lines 25 and 68:25: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GC_Contacts_Table]') AND type in (N'U'))68: ELSEBelow is the code. Thanks in advance.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[sp_refresh_GC_Contacts]ASDECLARE@dropSQL varchar(2000)BEGINSET NOCOUNT ON;--SET IDENTITY_INSERT GC_Contacts_Table ON-- drop the fulltext indexIF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[GC_Contacts]') AND name = N'pk_gc_contacts')DROP FULLTEXT INDEX ON [dbo].[GC_Contacts] -- drop the unique indexIF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[GC_Contacts]') AND name = N'pk_gc_contacts')DROP INDEX [pk_gc_contacts] ON [dbo].[GC_Contacts] WITH ( ONLINE = OFF )-- If table exists truncate itIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GC_Contacts_Table]') AND type in (N'U'))BEGIN TRUNCATE TABLE [dbo].[GC_Contacts_Table]INSERT INTO [dbo].[GC_Contacts_Table] --insert sql next lineSELECT Title, FirstName AS First_Name, MiddleName AS Middle_Name, LastName AS Last_Name,Suffix, Company, JobTitle AS Job_Title, Email, PrimaryPhoneNumber AS Primary_Phone_Number, PrimaryAddress1 AS Primary_Address_1, PrimaryAddress2 AS Primary_Address_2, PrimaryAddress3 AS Primary_Address_3, PrimaryCity AS Primary_City, PrimaryState AS Primary_State,PrimaryZip AS Primary_Zip, PrimaryCountry AS CPrimary_ountry, Notes, Alias, FullName AS Full_Name,dbo.Addresses.Type AS AddressType, dbo.Addresses.Address1 AS Address1,dbo.Addresses.Address2 AS Address2,dbo.Addresses.Address3 AS Address3, dbo.Addresses.City AS City, dbo.Addresses.State AS State,dbo.Addresses.Zip as Zipcode, dbo.Addresses.Country AS Country, dbo.Addresses.PhoneNumber AS PhoneNumber,dbo.Addresses.FaxNumber AS FaxNumber, SubAward_Only = CASE SubAwardOnlyWHEN 0 THEN 'No'WHEN 1 THEN 'Yes'END,dbo.ContactsSTUDF.*-- IDENTITY(int, 1,1) AS GC_Contact_ID-- INTO dbo.GC_Contacts_TableFROM dbo.Contacts LEFT OUTER JOIN dbo.ContactAddresses ON dboContacts.ID = dboContactAddresses.ContactIDLEFT OUTER JOIN dbo.Addresses ON dbo.Addresses.ID = dboContactAddresses.AddressIDLEFT OUTER JOIN dbo.ContactsSTUDF ON dbo.Contacts.ID = dbo.ContactsSTUDF.EntityIDENDELSE BEGIN-- create the table from the querySELECT Title, FirstName AS First_Name, MiddleName AS Middle_Name, LastName AS Last_Name, Suffix, Company,JobTitle AS Job_Title, Email, PrimaryPhoneNumber AS Primary_Phone_Number, PrimaryAddress1 AS Primary_Address_1, PrimaryAddress2 AS Primary_Address_2, PrimaryAddress3 AS Primary_Address_3, PrimaryCity AS Primary_City, PrimaryState AS Primary_State, PrimaryZip AS Primary_Zip, PrimaryCountry AS CPrimary_ountry, Notes, Alias, FullName AS Full_Name,dbo.Addresses.Type AS AddressType, dbo.Addresses.Address2 AS Address2,dbo.Addresses.Address3 AS Address3, dbo.Addresses.City AS City, dbo.Addresses.State AS State,dbo.Addresses.Zip as Zipcode, dbo.Addresses.Country AS Country, dbo.Addresses.PhoneNumber AS PhoneNumber,dbo.Addresses.FaxNumber AS FaxNumber, SubAward_Only = CASE SubAwardOnlyWHEN 0 THEN 'No'WHEN 1 THEN 'Yes'END,dbo.ContactsSTUDF.*,IDENTITY(int, 1,1) AS GC_Contact_IDINTO dbo.GC_Contacts_TableFROM dbo.Contacts LEFT OUTER JOIN dbo.ContactAddresses ON dboContacts.ID = dboContactAddresses.ContactIDLEFT OUTER JOIN dbo.Addresses ON dbo.Addresses.ID = dboContactAddresses.AddressIDLEFT OUTER JOIN dbo.ContactsSTUDF ON dbo.Contacts.ID = dbo.ContactsSTUDF.EntityIDEND SET IDENTITY_INSERT GC_Contacts_table OFFSET ARITHABORT ONSET CONCAT_NULL_YIELDS_NULL ONSET QUOTED_IDENTIFIER ONSET ANSI_NULLS ONSET ANSI_PADDING ONSET ANSI_WARNINGS ONSET NUMERIC_ROUNDABORT OFF/****** Object: Index [pk_gc_contacts] Script Date: 10/11/2007 15:34:28 ******/CREATE UNIQUE CLUSTERED INDEX [pk_gc_contacts] ON [dbo].[GC_Contacts] ([GC_contact_id] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]-- add the fulltext indexCREATE FULLTEXT INDEX ON GC_Contacts([Title],[First_Name],[Middle_Name],[Last_Name],[Suffix],[Company],[Job_Title],[Email],[Primary_Phone_Number],[Primary_Address_1],[Primary_Address_2],[Primary_Address_3],[Primary_City],[Primary_State],[CPrimary_ountry],[Notes],[Alias],[Full_Name],[AddressType],[Address1],[Address2],[Address3],[City],[State],[Country],[PhoneNumber],[FaxNumber],[SubAward_Only])KEY INDEX pk_gc_contacts ON GCInquiryCatalogWITH CHANGE_TRACKING AUTOEND
