replication/performance questions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

replication/performance questions

Author

Topic

joleary
Starting Member

7 Posts

Posted - 2004-02-12 : 22:07:50

Our setup:
win 2000, 2 servers (dual zeon 2.5ghrtz with 3 gigs of RAM each machine) running one sql server instance on raid array(for failover). One web server with ASP application. Heavy updates on database through-out the day/night. Lots of intense querying and reporting. The reports/searches are using dynamic sql SP with use of temp tables (paging logic). No full-text searches.

What we tried:
Running profiler every few months to find and unnessacary table scans and solve through creation of indexes.
Heavy use of with(no-lock) in the SP's that are read-only to prevent contention issues.

Problem still to date:
Not seeing much in terms of locks on the DB, however still seeing lots of timeouts. Reporting taking a long time to return results. Even SP's using indexes still take a long time even though using idexes, and most of cost is coming from sorts and bookmark lookups according to profiler.

Questions:
Could we use replication to move this data in a reasonable time to another sql server (on another machine if nessacary)? I think we only need a one way replication, since i thought we could just use another copy of the DB for just reporting (not searches). reporting could be off by a minute from the rest of the database if nessacary i guess. What is the time it could be off?? What type of replication should we use? Do we even need replication? If not then what can you suggest?
Number of concurrent users is growing and growing, as well as the content in the DB they are pulling from the application. Seems like we cant tweak this current server to anymore and we have hit a wall, even after upgrading our hardware recently.
Im going to try and give as much info as possible, if i forget something, then ill post to the best of my knowledge.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-12 : 22:19:10

What do the performace monitors show on the existing db box??: Disk i/o, paging, cpu, mem utilization, etc...

joleary
Starting Member

7 Posts

Posted - 2004-02-12 : 22:21:49

This is not something i have actually checked in detail. Do you have any links/suggestions on the best way to tackle this? things to look for and what they might mean?
Thanks so much...

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-12 : 22:32:56

You can search these forums for "monitoring performance" to find many good threads refering to this topic.

Another good site for related info is:

http://www.sql-server-performance.com/

in particular: http://www.sql-server-performance.com/sg_sql_server_performance_article.asp

joleary
Starting Member

7 Posts

Posted - 2004-02-12 : 22:49:41

Thanks Ill run some performance tests, but we just tossed lots of new hardware at the problem in the last few months, and things just didnt improve that much..Still get some timeouts..Anyways ill chime in with the results hopefully very soon.

But wouldnt moving some of the load (reporting) to another sql server just help lesson the load no matter what our issue is at this point? considering what we have already done...

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-12 : 23:04:54

What is the size of your db??
What would you classify the app (DSS or OLTP)?

The monitors will tell you if the hardware is taxed or not. If not, perhaps you need to look for optimization in an alternate schema and/or indexing ( ie. less normalization to improve your reporting/searching/retrieval without impacting inserts/updates and use of composite keys or more appropriate keys for your searches). It is always a balancing act.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-12 : 23:09:25

quote:
The reports/searches are using dynamic sql SP with use of temp tables (paging logic).

You may want to devote some attention into exactly how much and what kind of dynamic SQL is being done there, and look to reduce or eliminate it. It isn't always a performance bottleneck, but it can't take advantage of cached plans as well as a non-dynamic stored procedure can. This is especially true of UPDATE/INSERT/DELETE statements...don't do them with dynamic SQL at all, get them into stored procedures right away.

Also look at how searches are being done...if you're using LIKE pattern matches, indexes almost never help. Same thing applies to WHERE clauses and functions/non-SARGable expressions:

SELECT * FROM myTable WHERE Year(OrderDate)=2003 --rewrite this to:
SELECT * FROM myTable WHERE OrderDate BETWEEN '01/01/2003' AND '12/31/2003'

Basically you want a column name, by itself, to be on the left side of a comparison operator (=, <, >, LIKE, etc.) This helps the optimizer choose indexes more readily. We recently tweaked a search procedure at my job from using LIKE to using BETWEEN, and improved the performance tenfold. Everything ended up doing index seeks instead of scans.

Even if you do put more load on a second (or third) machine, it's still worth the effort to improve the query performance as much as possible. Query tuning is a never-ending job, and hardware can't make up for software that is underutilized or inefficiently implemented.

joleary
Starting Member

7 Posts

Posted - 2004-02-13 : 11:10:49

thing is we dont have any table scans going on..Everything is using indexes. Also all of our dynamic sql queries are for searches/reports only. We have been constantly running the profiler and tweaking indexes but it just seems we are at a point where we cant increase the performance of a few searches and large reports..they are spread across so many tables with data up to several million rows..If anything we may have too many non-clustered indexes trying to cope with all the different variations of searches. We have very few clustered indexes becuase of the amount of updates. Again for what its worth the majority of our bottle neck seems to be in bookmark lookups according to the profiler.
our DB is about 15 gigs..

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-13 : 12:09:00

Book mark lookups are often used if columns used in query are not part of index key. Unless you are selecting few columns and you can create another index with all the columns (covering or composite index), bookmark lookup will probably be used. Also, If your covering index is a nonclustered index and you have a clustered index on your table then SQL Server may still use the bookmark.

Can you post the DDL and DML for the slow running search processes??

joleary
Starting Member

7 Posts

Posted - 2004-02-13 : 12:18:08

Little confused as to what it is you want to see in order to help.
How can i generate the DML and DDL info for you?would posting the SP work? do you also need to see info about the indexes/schema?
Sorry little new to this, but i definetly appreaciate your help and also the speediness of your replies..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-13 : 12:21:33

To answer your initial question, yes you can use replication. The data could be less than a minute old on the replicated database. For one-way replication, use transactional replication.

Replication is commonly used for a reporting server.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-13 : 12:37:44

Tara, Do you feel that replicating this data set will resolve the timeout problems?

and;

quote:
How can I generate the DML and DDL info for you? would posting the SP work?

Yes the SP and the table layouts including the indexes would be very useful for diagnosis.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-13 : 12:41:18

Yes I do. Reports can cause a lot of performance problems on the production database. This is exactly why reports aren't usually allowed to run on the production database. Typically, you have a reporting database setup on another server just for reports. The reporting database could be a snapshot of the production database each night or it can be within a few seconds of it using replication.

Tara

joleary
Starting Member

7 Posts

Posted - 2004-02-13 : 13:57:38

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ui_getmyprospects]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ui_getmyprospects]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE ui_getmyprospects
/* User: uimyprospects.asp */

(
@CurrentPage varchar(10),
@PageSize int,
@pagefrom char(2),
@exportoption int,
@userid int,
@loginuserslistall varchar(4000),
@multiuserid int,
@partnerid int,
@prospecttype int,
@myprostype int,
@country char(2) = "0",
@state char(5) = "0",
@companyname char(60),
@sort char(2)="FA",
@fromdate char(10),
@todate char(10),
@datasourceid int,
@probability varchar(50),
@firstname varchar(50),
@lastname varchar(50),
@telephone varchar(20),
@email varchar(50),
@allYN char(1),
@closefromdate char(10),
@closetodate char(10),
@quotemoduleYN char(1),
@industry int,
@recordtype varchar(1),
@referralcontact int

)

AS

declare
@str1 varchar(8000),
@str2 varchar(8000),
@str3 varchar(8000),
@orderby varchar(1000),
@companysel1 varchar(100),
@companysel2 varchar(100),
@companysel3 varchar(100),
@statesel1 varchar(100),
@statesel2 varchar(100),
@statesel3 varchar(100),
@countrysel1 varchar(100),
@countrysel2 varchar(100),
@countrysel3 varchar(100),
@prospecttypesel1 varchar(100),
@prospecttypesel2 varchar(100),
@myprostypesel varchar(200),
@fromsel varchar(255),
@tosel varchar(255),
@closefromsel varchar(255),
@closetosel varchar(255),
@myprospecttypename varchar(500),
@nextstepname varchar(500),
@count int,
@FirstRec int,
@LastRec int,
@TotalPages int,
@datasourcesel varchar(100),
@probabilitysel varchar(100),
@firstnamesel varchar(200),
@lastnamesel varchar(200),
@telephonesel varchar(300),
@emailsel varchar(300),
@nextstepdesc varchar(300),
@mycompanytelephonesel varchar(300),
@loginuserslistidsel varchar(5000),
@isnullidsrt varchar(300),
@PIPELINE varchar (20),
@PIPELINEPROJECT varchar (30),
@PIPELINEID varchar (50),
@PIPELINESID varchar (50),
@PIPELINEMYDATESTAMP varchar (50),
@PIPELINENOTES varchar(250),
@PIPELINEQUOTES varchar(100),
@PIPELINEACTIVEYN varchar(250),
@PMCUSTOM varchar(300),
@industrysel1 varchar(600),
@industrysel2 varchar(600),
@industrysel3 varchar(600),
@numresults int,
@Fetch int,
@cursormyprospectid int,
@cursormypipelineid int,
@cursorquotepiplineid varchar(25),
@exportid int,
@recordtypesel varchar(500),
@referralcontactsel varchar(500)

SET NOCOUNT ON

select @myprospecttypename = ", (NULL) as myprospecttypename"
select @nextstepname = ", (NULL) as nextstepname"
select @prospecttypesel1 = " and MYPROSPECTTYPE > 1"
select @prospecttypesel2 = " and MYPROSPECTTYPE = 1"
select @isnullidsrt = " null as isnullid, "

if @quotemoduleYN = "Y"
Begin

select @PIPELINE = "MYPROSPECTQUOTES"
select @PIPELINEID = "MYPROSPECTQUOTES.MYPROSPECTQUOTESID AS MYPIPELINEID"
select @PIPELINESID = "myprospectquotesid"
select @PIPELINEMYDATESTAMP = "MYPROSPECTQUOTES.DATEADDED AS MYDATESTAMP"
select @PIPELINENOTES = ""
select @PIPELINEPROJECT = " MYPROSPECTQUOTES.PROJECT "
select @PIPELINEACTIVEYN = " AND MYPROSPECTQUOTES.ACTIVEYN <> 'N' "
select @PIPELINEQUOTES = " AND (MYPROSPECTQUOTES.ACTIVEYN = 'Y' OR MYPROSPECTQUOTES.ACTIVEYN IS NULL) "
End
else
Begin
select @PIPELINE = "MYPROSPECTNOTES"
select @PIPELINEID = "MYPROSPECTNOTES.MYPROSPECTNOTESID AS MYPIPELINEID"
select @PIPELINESID = "myprospectnotesid"
select @PIPELINEMYDATESTAMP = "MYPROSPECTNOTES.MYDATESTAMP"
select @PIPELINENOTES = " AND (MYPROSPECTNOTES.MYPROSPECTNOTESID is null or MYPROSPECTNOTES.MYPROSPECTNOTESID = (SELECT MAX(MYPROSPECTNOTESID) FROM MYPROSPECTNOTES WHERE MYPROSPECT.MYPROSPECTID = MYPROSPECTNOTES.MYPROSPECTID)) "
select @PIPELINEPROJECT = " null AS PROJECT "
select @PIPELINEACTIVEYN = ""
select @PIPELINEQUOTES = ""
End

select @PMCustom = " null as PMCustom "

if @partnerid = 6875
begin
select @PMCustom = "(select codelabel from codes where codename = 'MISERVICES' and codevalue = MYPROSPECTNOTES.MIServiceType) as PMCustom "
end

if @probability <> "999"
Begin
select @probabilitysel = " AND " + @PIPELINE + ".PROBABILITY in ("+ @probability + ")"
End
else
Begin
select @probabilitysel = ""
End

if @firstname <> ""
Begin
select @firstnamesel = " AND C2.firstname like '%" + @firstname +"%' "
End
else
Begin
select @firstnamesel = ''
end

if @lastname <> ""
Begin
select @lastnamesel = " AND C2.lastname like '%" + @lastname +"%' "
End
else
Begin
select @lastnamesel = ''
end

if @industry <> "" or @industry is not null
Begin
select @industrysel1 = " AND (SELECT BUSINESS FROM COMPANY WITH (NOLOCK) WHERE COMPANY.COMPANYID = MYPROSPECT.COMPANYID) ="+ Str(@industry) +" "
select @industrysel2 = " AND COMPANY.BUSINESS ="+ STR(@industry) + " "
select @industrysel3 = " AND MYCOMPANY.BUSINESS ="+ STR(@industry) + " "
end
else
Begin
select @industrysel1 = " "
select @industrysel2 = " "
select @industrysel3 = " "
End

if @recordtype = "C"
Begin
select @recordtypesel = " AND MYCOMPANY.RECORDTYPE = 'C' "
End
else if @recordtype = "P"
Begin
select @recordtypesel = " AND (MYCOMPANY.RECORDTYPE <> 'C' OR MYCOMPANY.RECORDTYPE IS NULL) "
End
else
Begin
select @recordtypesel = ""
End

if @referralcontact <> "" or @referralcontact is not null
Begin
select @referralcontactsel = " AND MYCOMPANY.REFERREDMYCOMPANYID ="+ STR(@referralcontact) + " "
End
else
Begin
select @referralcontactsel = " "
End

if @allYN = "Y"
Begin
select @loginuserslistidsel = " AND MYPROSPECT.USERID in ("+ @loginuserslistall +") "
End
else if @userid = 1 --handle the shared PM/mymarketing
Begin
select @loginuserslistidsel = " AND MYPROSPECT.sharedmultiUSERID ="+ str(@multiuserid) +""
end
else
Begin
select @loginuserslistidsel = " AND MYPROSPECT.USERID ="+ str(@userid) +""
end

if @myprostype <> ""
Begin
select @myprostypesel = " AND MYPROSPECT.MYPROSTYPE ="+ str(@myprostype) +""
End
else
Begin
select @myprostypesel = ""
end

if @telephone <> ""
Begin

select @telephonesel = " AND dbo.namekey(C2.bizphone) like '" + dbo.namekey(@telephone) +"%' "
select @mycompanytelephonesel = " AND (dbo.namekey(C2.bizphone) like '" + dbo.namekey(@telephone) +"%' or dbo.namekey(MYCOMPANY.phone) like '" + dbo.namekey(@telephone) +"%' )"
End
else
Begin
select @telephonesel = ''
select @mycompanytelephonesel =''
end

if @email <> ""
Begin
select @emailsel = " AND C2.email like '%" + @email +"' "
End
else
Begin
select @emailsel = ''
end

if ((@sort is not null) and (@sort = 'MD'))
begin
select @orderby = " order by PMCustom desc, FOLLOWUPDATE desc"
end

if ((@sort is not null) and (@sort = 'MA'))
begin
select @orderby = " order by PMCustom asc, FOLLOWUPDATE desc"
end

if ((@sort is not null) and (@sort = 'FD'))
begin
select @orderby = " order by FOLLOWUPDATE desc, PROSDATEADDED desc"
end

if ((@sort is not null) and (@sort = 'FA'))
begin
select @orderby = " order by nulllast asc, PROSDATEADDED desc"
end

if ((@sort is not null) and (@sort = 'CD'))
begin
select @orderby = " order by COMPANYNAME desc, FOLLOWUPDATE desc"
end

if ((@sort is not null) and (@sort = 'CA'))
begin
select @orderby = " order by COMPANYNAME asc, FOLLOWUPDATE desc"
end

if @quotemoduleYN = "Y"
begin
if ((@sort is not null) and (@sort = 'TA'))
begin
select @orderby = " order by isnullid desc, myprospectquotesid asc, C3.lastname asc, MYPROSPECTCONTACTS.lastname asc, FOLLOWUPDATE desc"
select @isnullidsrt = "ISNUMERIC(" + @PIPELINESID + ") as isnullid, "
end
if ((@sort is not null) and (@sort = 'TD'))
begin
select @orderby = " order by isnullid desc, myprospectquotesid desc, C3.lastname desc, MYPROSPECTCONTACTS.lastname desc, FOLLOWUPDATE desc"
select @isnullidsrt = "ISNUMERIC(" + @PIPELINESID + ") as isnullid, "
end
end
else
begin
if ((@sort is not null) and (@sort = 'TA'))
begin
select @orderby = " order by TITLE asc, FOLLOWUPDATE desc"
end
if ((@sort is not null) and (@sort = 'TD'))
begin
select @orderby = " order by TITLE desc, FOLLOWUPDATE desc"
end
end

if ((@sort is not null) and (@sort = 'SD'))
begin
select @orderby = " order by estclosedate desc, FOLLOWUPDATE desc"
end

if ((@sort is not null) and (@sort = 'SA'))
begin
select @orderby = " order by estnulllast asc, FOLLOWUPDATE desc"
end

if ((@sort is not null) and (@sort = 'PD'))
begin
select @orderby = " order by isnullid desc, probability asc, FOLLOWUPDATE desc"
select @isnullidsrt = "ISNUMERIC(probability) as isnullid, "
end

if ((@sort is not null) and (@sort = 'PA'))
begin
select @orderby = " order by isnullid desc, probability desc, FOLLOWUPDATE desc"
select @isnullidsrt = "ISNUMERIC(probability) as isnullid, "
end

if ((@sort is not null) and (@sort = 'ED'))
begin
select @orderby = " order by isnullid desc, estcloseamount desc, FOLLOWUPDATE desc"
select @isnullidsrt = "ISNUMERIC(estcloseamount) as isnullid, "
end

if ((@sort is not null) and (@sort = 'EA'))
begin
select @orderby = " order by isnullid desc, estcloseamount asc, FOLLOWUPDATE desc"
select @isnullidsrt = "ISNUMERIC(estcloseamount) as isnullid, "
end

select @count = 0
if (@partnerid is not null and @partnerid > 0)
begin
select @count = count(*) from CMODCodes where codename = 'nextstep' and partnerid = @partnerid
end

if (@count > 0)
begin
select @nextstepdesc = ' (select codelabel from CMODcodes where codename = "nextstep" and CMODcodes.partnerid = "' + str(@partnerid) + '" and codevalue = nextstep) as nextstepname, '
end
else
begin
select @nextstepdesc = ' (select codelabel from codes where codename = "nextstep" and codevalue = nextstep) as nextstepname, '
end

if ((@sort is not null) and ((@sort = 'ND') or (@sort = 'NA')))
begin
if (@count > 0)
begin
if (@sort = 'ND')
begin
select @orderby = " order by NEXTSTEPNAME desc, FOLLOWUPDATE desc"
select @nextstepname = ", (select codelabel from CMODcodes where codename = 'nextstep' and CMODcodes.partnerid = '" + str(@partnerid) + "' and codevalue = nextstep) as nextstepname "
end

if (@sort = 'NA')
begin
select @orderby = " order by NEXTSTEPNAME asc, FOLLOWUPDATE desc"
select @nextstepname = ", isnull((select top 1 codelabel from CMODcodes where codename = 'nextstep' and CMODcodes.partnerid = '" + str(@partnerid) + "' and codevalue = nextstep), 'zzzzzzzzzzzz') as nextstepname "
end
end
else
begin
if (@sort = 'ND')
begin
select @orderby = " order by NEXTSTEPNAME desc, FOLLOWUPDATE desc"
select @nextstepname = ", (select codelabel from codes where codename = 'nextstep' and codevalue = nextstep) as nextstepname "
end

if (@sort = 'NA')
begin
select @orderby = " order by NEXTSTEPNAME asc, FOLLOWUPDATE desc"
select @nextstepname = ", (select top 1 codelabel from codes where codename = 'nextstep' and codevalue = nextstep) as nextstepname "
end
end
end

select @companysel1 = ''
select @companysel2 = ''
select @companysel3 = ''
select @statesel1 = ''
select @statesel2 = ''
select @statesel3 = ''
select @countrysel1 = ''
select @countrysel2 = ''
select @countrysel3 = ''
select @fromsel = ''
select @tosel = ''
select @closefromsel = ''
select @closetosel = ''
Select @datasourcesel = ''

if @datasourceid != 1 and (@datasourceid is not null) and (@datasourceid != '')
Begin
if @datasourceid = 2
begin
Select @datasourcesel = " and MYPROSPECT.MYPROSPECTDATASRCID is null"
end
else
begin
Select @datasourcesel = " and MYPROSPECT.MYPROSPECTDATASRCID="+ str(@datasourceid) +""
end
End

if ((@companyname is not null) and (@companyname != ''))
begin
select @companysel1 = " and RFP.RFPCOMPANY like " + QUOTENAME(rtrim(@companyname) + '%', CHAR(39)) + " "
select @companysel2 = " and COMPANY.COMPANYNAME like " + QUOTENAME(rtrim(@companyname) + '%', CHAR(39)) + " "
select @companysel3 = " and MYCOMPANY.COMPANYNAME like " + QUOTENAME(rtrim(@companyname) + '%', CHAR(39)) + " "
end

if ((@prospecttype is not null) and (@prospecttype > 0))
begin
if (@prospecttype = 1)
begin
select @prospecttypesel1 = " and MYPROSPECTTYPE is null "
select @prospecttypesel2 = " and MYPROSPECTTYPE = 1 "
end
else
begin
select @prospecttypesel1 = " and MYPROSPECTTYPE = " + str(@prospecttype) + " "
select @prospecttypesel2 = " and MYPROSPECTTYPE is null "
end
end

if ((@state is not null) and (@state != '') and (@state != '0'))
begin
select @statesel1 = " and RFP.LOCSTATE = '" + @state + "' "
select @statesel2 = " and COMPANY.STATECD = '" + @state + "' "
select @statesel3 = " and MYCOMPANY.STATECD = '" + @state + "' "
end
else
begin
if ((@country is not null) and (@country != '') and (@country != '0'))
begin
select @countrysel1 = " and RFP.LOCSTATE like '" + @country + "%' "
select @countrysel2 = " and COMPANY.STATECD like '" + @country + "%' "
select @countrysel3 = " and MYCOMPANY.STATECD like '" + @country + "%' "
end
end

if ((@fromdate is not null) and (@fromdate != '') and (@fromdate != '0') and (@todate is not null) and (@todate != '') and (@todate != '0'))
begin
select @fromsel = " and ((followupdate >= '" + convert(char(10),@fromdate,101) + "'))"
select @tosel = " and ((followupdate < '" + convert(char(10),@todate,101) + "'))"
end
else
begin
if ((@fromdate is not null) and (@fromdate != '') and (@fromdate != '0'))
begin
select @tosel = " and ((followupdate >= '" + convert(char(10),@fromdate,101) + "'))" --or (mydatestamp >= '" + convert(char(10),@fromdate,101) + "' and followupdate is null)) "
end
if ((@todate is not null) and (@todate != '') and (@todate != '0'))
begin
select @tosel = " and ((followupdate <= '" + convert(char(10),@todate,101) + "'))" --or (mydatestamp <= '" + convert(char(10),@todate,101) + "' and followupdate is null)) "
end
end
if ((@closefromdate is not null) and (@closefromdate != '') and (@closefromdate != '0') and (@closetodate is not null) and (@closetodate != '') and (@closetodate != '0'))
begin
select @closefromsel = " and ((estclosedate >= '" + convert(char(10),@closefromdate,101) + "')) "
select @closetosel = " and ((estclosedate < '" + convert(char(10),@closetodate,101) + "')) "
end
else
begin
if ((@closefromdate is not null) and (@closefromdate != '') and (@closefromdate != '0'))
begin
select @closetosel = " and ((estclosedate >= '" + convert(char(10),@closefromdate,101) + "')) " --or (mydatestamp >= '" + convert(char(10),@fromdate,101) + "' and followupdate is null)) "
end
if ((@closetodate is not null) and (@closetodate != '') and (@closetodate != '0'))
begin
select @closetosel = " and ((estclosedate <= '" + convert(char(10),@closetodate,101) + "')) " --or (mydatestamp <= '" + convert(char(10),@todate,101) + "' and followupdate is null)) "
end
end
select @str1 = "SELECT RFP.RFPID as rfpid, NULL as companyid,null as mycompanyid, RFP.RFPCOMPANY as companyname, " + @PMCustom +", null as ImportListSrc, RFP.TITLEPUBLIC as title, null as city, " + @PIPELINEPROJECT + ", MYPROSPECT.MYPROSPECTID, MYPROSPECT.USERID, MYPROSPECT.DATEADDED as prosdateadded, MYPROSPECT.MYPROSPECTTYPE " + @myprospecttypename
+ ", " + @PIPELINEID + ", " + @PIPELINE + ".NEXTSTEP " + @nextstepname + ", " + @PIPELINE + ".FOLLOWUPDATE, isnull( " + @PIPELINE + ".FOLLOWUPDATE ,'9999-01-01') as nulllast, " + @PIPELINE + ".ESTCLOSEAMOUNT, " + @PIPELINE + ".PROBABILITY, " + @PIPELINEMYDATESTAMP + ", " + @PIPELINE + ".ESTCLOSEDATE, isnull(" + @PIPELINE + ".ESTCLOSEDATE ,'9999-01-01') as estnulllast, " + @isnullidsrt + " MYPROSPECTCONTACTS.FIRSTNAME, MYPROSPECTCONTACTS.lastname, MYPROSPECTCONTACTS.bizphone, MYPROSPECTCONTACTS.email, C3.FIRSTNAME, C3.LASTNAME, C3.BIZPHONE, C3.EMAIL, C3.MYPROSPECTCONTACTSID "
+ " FROM PROSTYPES WITH (NOLOCK),MYPROSPECT WITH (NOLOCK) INNER JOIN RFP WITH (NOLOCK) ON MYPROSPECT.RFPID = RFP.RFPID LEFT OUTER JOIN " + @PIPELINE + " WITH (NOLOCK) ON MYPROSPECT.MYPROSPECTID = " + @PIPELINE + ".MYPROSPECTID " + @PIPELINEACTIVEYN + " LEFT OUTER JOIN MYPROSPECTCONTACTS WITH (NOLOCK) ON MYPROSPECT.MYPROSPECTID = MYPROSPECTCONTACTS.MYPROSPECTID LEFT OUTER JOIN MYPROSPECTCONTACTS as C2 WITH (NOLOCK) ON MYPROSPECT.MYPROSPECTID = C2.MYPROSPECTID LEFT OUTER JOIN MYPROSPECTCONTACTS as C3 WITH (NOLOCK) ON " + @PIPELINE + ".MYPROSPECTCONTACTSID = C3.MYPROSPECTCONTACTSID "
+ "WHERE MYPROSPECT.ACTIVEYN = 'Y' AND MYPROSPECT.RFPID IS NOT NULL "
+ "AND PROSTYPES.prostype=MYPROSPECT.MYPROSPECTTYPE and PROSTYPES.showYN='Y'"
+ @industrysel1
+ @loginuserslistidsel
+ @datasourcesel
+ @probabilitysel
+ @firstnamesel
+ @lastnamesel
+ @myprostypesel
+ @telephonesel
+ @emailsel
+ @fromsel + @tosel + @closefromsel + @closetosel+ @prospecttypesel1 + @statesel1 + @countrysel1 + @companysel1
+ @PIPELINEQUOTES + @PIPELINENOTES + " AND (MYPROSPECTCONTACTS.MYPROSPECTCONTACTSID is null or MYPROSPECTCONTACTS.MYPROSPECTCONTACTSID = (SELECT MAX(MYPROSPECTCONTACTSID) FROM MYPROSPECTCONTACTS WHERE MYPROSPECT.MYPROSPECTID = MYPROSPECTCONTACTS.MYPROSPECTID)) "

select @str2 = "SELECT NULL AS rfpid, COMPANY.COMPANYID as companyid,null as mycompanyid, COMPANY.COMPANYNAME as companyname, " + @PMCustom + ", null as ImportListSrc, COMPANYNAME as title, COMPANY.CITY as city, " + @PIPELINEPROJECT + ", MYPROSPECT.MYPROSPECTID, MYPROSPECT.USERID, MYPROSPECT.DATEADDED as prosdateadded, MYPROSPECT.MYPROSPECTTYPE " + @myprospecttypename
+ ", " + @PIPELINEID + ", " + @PIPELINE + ".NEXTSTEP " + @nextstepname + ", " + @PIPELINE + ".FOLLOWUPDATE, isnull( " + @PIPELINE + ".FOLLOWUPDATE ,'9999-01-01') as nulllast," + @PIPELINE + ".ESTCLOSEAMOUNT, " + @PIPELINE + ".PROBABILITY, " + @PIPELINEMYDATESTAMP + " , " + @PIPELINE + ".ESTCLOSEDATE, isnull( " + @PIPELINE + ".ESTCLOSEDATE ,'9999-01-01') as estnulllast, " + @isnullidsrt + " MYPROSPECTCONTACTS.FIRSTNAME, MYPROSPECTCONTACTS.lastname, MYPROSPECTCONTACTS.bizphone,MYPROSPECTCONTACTS.email, C3.FIRSTNAME, C3.LASTNAME, C3.BIZPHONE, C3.EMAIL, C3.MYPROSPECTCONTACTSID "
+ "FROM PROSTYPES WITH (NOLOCK),MYPROSPECT WITH (NOLOCK) INNER JOIN COMPANY WITH (NOLOCK) ON MYPROSPECT.COMPANYID = COMPANY.COMPANYID LEFT OUTER JOIN " + @PIPELINE + " WITH (NOLOCK) ON MYPROSPECT.MYPROSPECTID = " + @PIPELINE + ".MYPROSPECTID " + @PIPELINEACTIVEYN + " LEFT OUTER JOIN MYPROSPECTCONTACTS WITH (NOLOCK) ON MYPROSPECT.MYPROSPECTID = MYPROSPECTCONTACTS.MYPROSPECTID LEFT OUTER JOIN MYPROSPECTCONTACTS as C2 WITH (NOLOCK) ON MYPROSPECT.MYPROSPECTID = C2.MYPROSPECTID LEFT OUTER JOIN MYPROSPECTCONTACTS as C3 WITH (NOLOCK) ON " + @PIPELINE + ".MYPROSPECTCONTACTSID = C3.MYPROSPECTCONTACTSID "
+ "WHERE MYPROSPECT.ACTIVEYN = 'Y' AND MYPROSPECT.COMPANYID IS NOT NULL "
+ "AND PROSTYPES.prostype=MYPROSPECT.MYPROSPECTTYPE and PROSTYPES.showYN='Y'"
+ @industrysel1
+ @loginuserslistidsel
+ @datasourcesel
+ @probabilitysel
+ @firstnamesel
+ @lastnamesel
+ @myprostypesel
+ @telephonesel
+ @emailsel
+ @fromsel + @tosel + @closefromsel + @closetosel+ @prospecttypesel2+ @statesel2 + @countrysel2 + @companysel2
+ @PIPELINEQUOTES + @PIPELINENOTES + " AND (MYPROSPECTCONTACTS.MYPROSPECTCONTACTSID is null or MYPROSPECTCONTACTS.MYPROSPECTCONTACTSID = (SELECT MAX(MYPROSPECTCONTACTSID) FROM MYPROSPECTCONTACTS WHERE MYPROSPECT.MYPROSPECTID = MYPROSPECTCONTACTS.MYPROSPECTID)) "

select @str3 = "SELECT NULL AS rfpid, NULL as companyid, MYCOMPANY.MYCOMPANYID as mycompanyid,MYCOMPANY.COMPANYNAME as companyname, " + @PMCustom + ", MYCOMPANY.ImportListSrc, MYCOMPANY.COMPANYNAME as title, MYCOMPANY.CITY as city, " + @PIPELINEPROJECT + ", MYPROSPECT.MYPROSPECTID, MYPROSPECT.USERID, MYPROSPECT.DATEADDED as prosdateadded, MYPROSPECT.MYPROSPECTTYPE " + @myprospecttypename
+ ", " + @PIPELINEID + ", " + @PIPELINE + ".NEXTSTEP " + @nextstepname + ", " + @PIPELINE + ".FOLLOWUPDATE, isnull( " + @PIPELINE + ".FOLLOWUPDATE ,'9999-01-01') as nulllast, " + @PIPELINE + ".ESTCLOSEAMOUNT, " + @PIPELINE + ".PROBABILITY, " + @PIPELINEMYDATESTAMP + " , " + @PIPELINE + ".ESTCLOSEDATE, isnull( " + @PIPELINE + ".ESTCLOSEDATE ,'9999-01-01') as estnulllast, " + @isnullidsrt + " MYPROSPECTCONTACTS.FIRSTNAME, MYPROSPECTCONTACTS.lastname, MYPROSPECTCONTACTS.bizphone,MYPROSPECTCONTACTS.email, C3.FIRSTNAME, C3.LASTNAME, C3.BIZPHONE, C3.EMAIL, C3.MYPROSPECTCONTACTSID "
+ "FROM PROSTYPES WITH (NOLOCK),MYPROSPECT WITH (NOLOCK) INNER JOIN MYCOMPANY WITH (NOLOCK) ON MYPROSPECT.MYCOMPANYID = MYCOMPANY.MYCOMPANYID LEFT OUTER JOIN " + @PIPELINE + " WITH (NOLOCK) ON MYPROSPECT.MYPROSPECTID = " + @PIPELINE + ".MYPROSPECTID " + @PIPELINEACTIVEYN + " LEFT OUTER JOIN MYPROSPECTCONTACTS WITH (NOLOCK) ON MYPROSPECT.MYPROSPECTID = MYPROSPECTCONTACTS.MYPROSPECTID LEFT OUTER JOIN MYPROSPECTCONTACTS as C2 WITH (NOLOCK) ON MYPROSPECT.MYPROSPECTID = C2.MYPROSPECTID LEFT OUTER JOIN MYPROSPECTCONTACTS as C3 WITH (NOLOCK) ON " + @PIPELINE + ".MYPROSPECTCONTACTSID = C3.MYPROSPECTCONTACTSID "
+ "WHERE MYPROSPECT.ACTIVEYN = 'Y' AND MYPROSPECT.MYCOMPANYID IS NOT NULL "
+ "AND PROSTYPES.prostype=MYPROSPECT.MYPROSPECTTYPE and PROSTYPES.showYN='Y'"
+ @industrysel3
+ @loginuserslistidsel
+ @datasourcesel
+ @probabilitysel
+ @firstnamesel
+ @lastnamesel
+ @myprostypesel
+ @mycompanytelephonesel
+ @emailsel
+ @recordtypesel
+ @referralcontactsel
+ @fromsel + @tosel + @closefromsel + @closetosel+ @prospecttypesel1+ @statesel3 + @countrysel3 + @companysel3
+ @PIPELINEQUOTES + @PIPELINENOTES + " AND (MYPROSPECTCONTACTS.MYPROSPECTCONTACTSID is null or MYPROSPECTCONTACTS.MYPROSPECTCONTACTSID = (SELECT MAX(MYPROSPECTCONTACTSID) FROM MYPROSPECTCONTACTS WHERE MYPROSPECT.MYPROSPECTID = MYPROSPECTCONTACTS.MYPROSPECTID)) "

CREATE TABLE #TempItems3 ( seqid int IDENTITY (1, 1), rfpid char(25), companyid int, mycompanyid int,companyname varchar(512), pmcustom varchar(100), ImportListSrc int, title varchar(1024), city varchar(50), project varchar(150), myprospectid int, userid int, prosdateadded datetime, myprospecttype int, myprospecttypename char(100), mypipelineid int, nextstep int, nextstepname char(100), followupdate datetime, nulllast datetime, estcloseamount int, probability int, mydatestamp datetime,estclosedate datetime, estnulllast datetime, isnullid int, firstname varchar(50), lastname varchar(50), bizphone varchar(20),email varchar(50), notesfirstname varchar(50), noteslastname varchar(50), notesbizphone varchar(20),notesemail varchar(50),notescontactsid int)
execute ( "INSERT INTO #TempItems3 (rfpid, companyid, mycompanyid,companyname, PMCustom, ImportListSrc, title, city, project, myprospectid, userid, prosdateadded, myprospecttype, myprospecttypename, mypipelineid, nextstep, nextstepname, followupdate, nulllast, estcloseamount, probability, mydatestamp,estclosedate,estnulllast, isnullid, firstname,lastname,bizphone,email,notesfirstname,noteslastname,notesbizphone,notesemail,notescontactsid) (" + @str1 + " union " + @str2 + " union " + @str3 +")" + @orderby)
SELECT @TotalPages = (( SELECT COUNT(*) FROM #TempItems3 ) + (@PageSize - 1)) / @PageSize
select @numresults = ( SELECT COUNT(*) FROM #TempItems3 )

-- Validate paging and setup selection for current page
if (@CurrentPage is null or @CurrentPage = '' or isnumeric(@CurrentPage) = 0)
begin
SELECT @CurrentPage = 1
end

if (@CurrentPage < 1)
begin
SELECT @CurrentPage = 1
end

if (@CurrentPage > @TotalPages)
begin
SELECT @CurrentPage = @TotalPages
end

SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
SELECT @LastRec = (@CurrentPage * @PageSize + 1)

-- Turn NOCOUNT back OFF
--SET NOCOUNT OFF

select @str1 = 'SELECT seqid as ItemNo, numresults='+ str(@numresults) +', TotalPages = ' + str(@TotalPages) + ', CurrentPage = ' + str(@CurrentPage)
+ ', rfpid, companyid, mycompanyid,rtrim(companyname) as companyname, PMCustom, title, city, project, myprospectid,userid,convert(varchar,prosdateadded,101) as prosdateadded,
myprospecttype, (select proslabel from prostypes where prostype = myprospecttype) as myprospecttypename, ImportListSrc,
nextstep, ' + @nextstepdesc + ' mypipelineid as ' + @PIPELINESID + ', followupdate, nulllast,rtrim(estcloseamount) as estcloseamount, probability as probability,
convert(varchar, mydatestamp,101) as mydatestamp,convert(varchar, estclosedate,101) as estclosedate,estnulllast,firstname, lastname, bizphone,email,notesfirstname,noteslastname,
notesbizphone,notesemail,notescontactsid FROM #TempItems3 where seqid > ' + str(@FirstRec) + ' AND seqid < ' + str(@LastRec) + ' order by ItemNo'

if @pagefrom = 'PM'
Begin
execute ( @str1 )
end
else
Begin
--Store procedure call from export all page
if @exportoption = 1
Begin
CREATE TABLE #TempEntity(

itemNo int,
numresults int,
TotalPages int,
CurrentPage int,
rfpid char(25),
companyid int,
mycompanyid int,
companyname varchar(512),
pmcustom varchar(100),
title varchar(1024),
city varchar(50),
project varchar(150),
myprospectid int,
userid int,
prosdateadded datetime,
myprospecttype int,
myprospecttypename char(100),
ImportListSrc int,
nextstep int,
nextstepname char(100),
mypipelineid int,
followupdate datetime,
nulllast datetime,
estcloseamount int,
probability int,
mydatestamp datetime,
estclosedate datetime,
estnulllast datetime,
firstname varchar(50),
lastname varchar(50),
bizphone varchar(20),
email varchar(50),
notesfirstname varchar(50),
noteslastname varchar(50),
notesbizphone varchar(20),
notesemail varchar(50),
notescontactsid int
)

execute ( 'INSERT INTO #TempEntity (ItemNo, numresults,TotalPages,CurrentPage,rfpid , companyid, mycompanyid,companyname, pmcustom, title, city,project, myprospectid, userid, prosdateadded, myprospecttype, myprospecttypename, ImportListSrc, nextstep, nextstepname, mypipelineid, followupdate, nulllast, estcloseamount, probability, mydatestamp,estclosedate, estnulllast,firstname, lastname, bizphone ,email, notesfirstname, noteslastname, notesbizphone,notesemail,notescontactsid) ' + @str1 )

--Get the exportid for export all option
Exec ui_exportall_ins
@userid = @userid,
@exportoption = @exportoption,
@service = 'PM'

select @exportid = @@identity

DECLARE PM_Cursor CURSOR FOR
SELECT myprospectid,mypipelineid FROM #TempEntity
OPEN PM_Cursor
FETCH FROM PM_Cursor INTO @cursormyprospectid,@cursormypipelineid
SELECT @Fetch = @@FETCH_STATUS
WHILE (@Fetch = 0)
BEGIN
if (@quotemoduleYN = 'Y')
Begin

if (@cursormypipelineid is null or @cursormypipelineid = '')
Begin
select @cursorquotepiplineid = @cursormyprospectid

End
else
Begin

select @cursorquotepiplineid = ltrim(str(@cursormyprospectid))+":"+ltrim(str(@cursormypipelineid))

End
End
else
Begin
select @cursorquotepiplineid = @cursormyprospectid
End

Exec ui_export_history_ins
@exportid = @exportid,
@entityid = @cursorquotepiplineid
FETCH NEXT FROM PM_Cursor INTO @cursormyprospectid,@cursormypipelineid
SELECT @Fetch = @@FETCH_STATUS

END
CLOSE PM_Cursor
DEALLOCATE PM_Cursor

select @exportid as "id"

drop table #TempEntity

End

End

GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

joleary
Starting Member

7 Posts

Posted - 2004-02-13 : 14:01:35

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_COMPANYCODES_COMPANY]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[COMPANYCODES] DROP CONSTRAINT FK_COMPANYCODES_COMPANY
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_COMPANYCONTACT_COMPANY]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[COMPANYCONTACT] DROP CONSTRAINT FK_COMPANYCONTACT_COMPANY
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_COMPCON_COMPANY_EMP]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[COMPANYCONTACT] DROP CONSTRAINT FK_COMPCON_COMPANY_EMP
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_COMPANYRANKING_COMPANY]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[COMPANYRANKING] DROP CONSTRAINT FK_COMPANYRANKING_COMPANY
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_COMPANYRELATION_COMPANY]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[COMPANYRELATION] DROP CONSTRAINT FK_COMPANYRELATION_COMPANY
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_COMPANYRELATION_COMPANY_CHILD]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[COMPANYRELATION] DROP CONSTRAINT FK_COMPANYRELATION_COMPANY_CHILD
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_COMPANYWATCH_COMPANYID]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[COMPANYWATCH] DROP CONSTRAINT FK_COMPANYWATCH_COMPANYID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ EXPORTHISTORY _COMPANY]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[EXPORTHISTORYOLD] DROP CONSTRAINT FK_ EXPORTHISTORY _COMPANY
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_MYPROSPECT_COMPANY]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[MYPROSPECT] DROP CONSTRAINT FK_MYPROSPECT_COMPANY
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_NEWSRELATION_COMPANY]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[NEWSRELATION] DROP CONSTRAINT FK_NEWSRELATION_COMPANY
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_MYPROSPECT_MYCOMPANY]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[MYPROSPECT] DROP CONSTRAINT FK_MYPROSPECT_MYCOMPANY
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_MYPROSPECTCONTACTS_MYPROSPECT]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[MYPROSPECTCONTACTS] DROP CONSTRAINT FK_MYPROSPECTCONTACTS_MYPROSPECT
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_MYPROSPECTNOTES_MYPROSPECT]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[MYPROSPECTNOTES] DROP CONSTRAINT FK_MYPROSPECTNOTES_MYPROSPECT
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_MYPROSPECTQUOTES_MYPROSPECT]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[MYPROSPECTQUOTES] DROP CONSTRAINT FK_MYPROSPECTQUOTES_MYPROSPECT
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_MYPROSPECTQUOTESNOTES_MYPROSPECT]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[MYPROSPECTQUOTESNOTES] DROP CONSTRAINT FK_MYPROSPECTQUOTESNOTES_MYPROSPECT
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_BUYSELL_RFP]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[buyerseller] DROP CONSTRAINT FK_BUYSELL_RFP
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CARTLEADS_RFP]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CARTLEADS] DROP CONSTRAINT FK_CARTLEADS_RFP
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_CORPLEADS_RFP]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[CORPLEADS] DROP CONSTRAINT FK_CORPLEADS_RFP
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_ EXPORTHISTORY _RFP]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[EXPORTHISTORYOLD] DROP CONSTRAINT FK_ EXPORTHISTORY _RFP
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_MYPROSPECT_RFP]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[MYPROSPECT] DROP CONSTRAINT FK_MYPROSPECT_RFP
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_PURCHASEDLEADS_RFP]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[PURCHASEDLEADS] DROP CONSTRAINT FK_PURCHASEDLEADS_RFP
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_REFEREMAIL_RFP]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[referemail] DROP CONSTRAINT FK_REFEREMAIL_RFP
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_RFP_CATEGORY_RFP]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[RFP_CATEGORY] DROP CONSTRAINT FK_RFP_CATEGORY_RFP
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_RFQITEMS_RFP]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[RFQITEMS] DROP CONSTRAINT FK_RFQITEMS_RFP
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_SELLERLEADS_RFP]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[SELLERLEADS] DROP CONSTRAINT FK_SELLERLEADS_RFP
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[company_DateMod]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[company_DateMod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rfp_DateMod]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[rfp_DateMod]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[COMPANY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[COMPANY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MYCOMPANY]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MYCOMPANY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MYPROSPECT]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MYPROSPECT]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MYPROSPECTCONTACTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MYPROSPECTCONTACTS]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MYPROSPECTNOTES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MYPROSPECTNOTES]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MYPROSPECT_SALESSTAGES]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[MYPROSPECT_SALESSTAGES]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RFP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[RFP]
GO

CREATE TABLE [dbo].[COMPANY] (
[COMPANYID] [int] IDENTITY (1000, 1) NOT NULL ,
[COMPANYNAME] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATECD] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIPCODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COUNTRYCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COUNTRYGROUPCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAX] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DUNS] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CORPURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRESSURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ARTICLEURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INFOURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INFOURLDESC] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GENERALEMAIL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PARTNERSHIPEMAIL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SALESEMAIL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROVIDERID] [int] NULL ,
[BIZDESCRIPTION] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSINESS] [int] NULL ,
[SECONDARYBUSINESS] [int] NULL ,
[OLDSICCODE] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NUMEMPLOYEES] [int] NULL ,
[REVENUE] [int] NULL ,
[LOCATION] [int] NULL ,
[TICKER] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[YEARFOUNDED] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FILEDATE] [datetime] NULL ,
[COMMENTS] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SECTORTYPE] [int] NULL ,
[ACTIVEYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATEADDED] [datetime] NOT NULL ,
[ENTRYUSER] [int] NULL ,
[DATEUPDATED] [datetime] NULL ,
[UPDATEUSER] [int] NULL ,
[PHONE800] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATEDELETED] [datetime] NULL ,
[DELETEUSER] [int] NULL ,
[NOINFOYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NAMEKEY] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SALESAMOUNT] [decimal](7, 2) NULL ,
[SALESCURRENCY] [int] NULL ,
[SALESUNITS] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SALESTEXT] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DBANAME] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STINC] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROVIDERREFID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MERGEDPROVIDERID] [int] NULL ,
[MERGEDPROVIDERREFID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateMod] [datetime] NULL ,
[ADDRESSKEY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[POBOX] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sourcenewsid] [int] NULL ,
[RECRUITERGEO] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RECRUITERSALMIN] [int] NULL ,
[recruitertype] [int] NOT NULL ,
[TELECOMDISTCOLO] [numeric](18, 3) NULL ,
[TELECOMNUMLINES] [int] NULL ,
[DATASRCTRACK] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TELECOMCILLICODE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeDupKey] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[MYCOMPANY] (
[MYCOMPANYID] [int] IDENTITY (1000, 1) NOT NULL ,
[COMPANYNAME] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATECD] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIPCODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COUNTRYCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PHONEEXT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAX] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAXEXT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GENERALEMAIL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATEADDED] [datetime] NOT NULL ,
[URL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BUSINESS] [int] NULL ,
[ReferralSource] [int] NULL ,
[ReferralName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NumEmployees] [int] NULL ,
[ImportListSrc] [int] NULL ,
[companyid] [int] NULL ,
[importbatchid] [int] NULL ,
[trinetvsalesid] [int] NULL ,
[referredmycompanyid] [int] NULL ,
[RECORDTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MYPROSPECT] (
[MYPROSPECTID] [int] IDENTITY (1000, 1) NOT NULL ,
[USERID] [int] NULL ,
[RFPID] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COMPANYID] [int] NULL ,
[MYPROSPECTTYPE] [int] NOT NULL ,
[DATEADDED] [datetime] NOT NULL ,
[ACTIVEYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MYCOMPANYID] [int] NULL ,
[NEWSID] [int] NULL ,
[MYPROSPECTDATASRCID] [int] NULL ,
[AccountNumber] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[namekey] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[myprostype] [int] NULL ,
[myprospectdatasrc2id] [int] NULL ,
[sharedmultiuserid] [int] NULL ,
[DeDupKey] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MYPROSPECTCONTACTS] (
[MYPROSPECTID] [int] NOT NULL ,
[MYPROSPECTCONTACTSID] [int] IDENTITY (1000, 1) NOT NULL ,
[FIRSTNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MIDDLEINITIAL] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTNAME] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BIZPHONE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BIZPHONEEXT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAX] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAXEXT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMAIL] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACTUALTITLE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TITLE] [int] NULL ,
[TITLE2] [int] NULL ,
[RESPONSIBILITY] [int] NULL ,
[RESPONSIBILITY2] [int] NULL ,
[DATEADDED] [datetime] NOT NULL ,
[dateupdated] [datetime] NULL ,
[CELLPHONE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDRESS2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATECD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COUNTRYCD] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIPCODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TAUSERID] [int] NULL ,
[Salutation] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MYPROSPECTNOTES] (
[MYPROSPECTID] [int] NOT NULL ,
[MYPROSPECTNOTESID] [int] IDENTITY (1000, 1) NOT NULL ,
[NEXTSTEP] [int] NULL ,
[MYDATESTAMP] [datetime] NOT NULL ,
[FOLLOWUPDATE] [datetime] NULL ,
[COMMENTS] [varchar] (7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ESTCLOSEDATE] [datetime] NULL ,
[ESTCLOSEAMOUNT] [int] NULL ,
[PROBABILITY] [int] NULL ,
[DATEUPDATED] [datetime] NULL ,
[MYPROSPECTCONTACTSID] [int] NULL ,
[ENTRYUSERID] [int] NULL ,
[TRIProduct] [int] NULL ,
[TRISetupFee] [real] NULL ,
[TRIServiceFee] [real] NULL ,
[TRIOtherFee] [real] NULL ,
[TRIPayCycle] [int] NULL ,
[TRIAvgSalary] [real] NULL ,
[MIServiceType] [int] NULL ,
[MIContractLength] [int] NULL ,
[MIUnitPrice] [float] NULL ,
[MIUnits] [int] NULL ,
[lostTocompetitor] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[MYPROSPECT_SALESSTAGES] (
[MYPROSPECTSALESSTAGESID] [int] IDENTITY (1000, 1) NOT NULL ,
[MYPROSPECTID] [int] NOT NULL ,
[SALESSTAGEID] [int] NOT NULL ,
[DATEADDED] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RFP] (
[RFPID] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RFPNOBUYER] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TITLE] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DESCRIPTION] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DUEDATE] [datetime] NOT NULL ,
[REMARKS] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFPTYPE] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SOURCE] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SOURCECAT] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SOURCEURL] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PREBIDDATE] [datetime] NULL ,
[BUDGETAMOUNT] [numeric](20, 2) NULL ,
[BUDGETAMOUNTMAX] [numeric](20, 2) NULL ,
[INFOURL] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[INFOURLDESC] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFPCONTACTINFO] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFPCOMPANY] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFPADDRESS] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFPADDRESS2] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFPCITY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFPSTATECD] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFPZIP] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RFPCOUNTRYCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PHONE] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FAX] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMAIL] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOCZIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOCSTATE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NUMNOTES] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENTRYUSER] [int] NULL ,
[DATEADDED] [datetime] NOT NULL ,
[UPDATEUSER] [int] NULL ,
[DATEUPDATED] [datetime] NULL ,
[partnerid] [int] NULL ,
[PREBIDDELIVERABLE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FINALDELIVERABLE] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SECTORTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CONEMAILYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CONFAXYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CONPHONEYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CONMAILYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATAMODE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DESCRIPTIONPUBLIC] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TITLEPUBLIC] [varchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROVIDERSOURCEID] [int] NULL ,
[ESTIMATEDDUEDATEYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ACTIVEYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DATEPUBLISHED] [datetime] NULL ,
[SOLTYPE] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PRETEXTFORMATYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CONTRACTNO] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ANNOUNCEDDATE] [datetime] NULL ,
[DELIVERYDATE] [datetime] NULL ,
[LOCCOMPANY] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOCADDRESS] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOCADDRESS2] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOCCITY] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LOCCOUNTRY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SHIPINFO] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PACKINFO] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BODY] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACCEPTPARTIALYN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[COMPANYCONTACTID] [int] NOT NULL ,
[contacttitle] [int] NULL ,
[contactresponsibility] [int] NULL ,
[sourcewalkid] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[sourceBatchID] [int] NULL ,
[DELETEDATE] [datetime] NULL ,
[DELETEREASON] [int] NULL ,
[PHONEEXT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATEMOD] [datetime] NOT NULL ,
[sourcenewsid] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[MYPROSPECT_SALESSTAGES] WITH NOCHECK ADD
CONSTRAINT [PK_MYPROSPECT_SALESSTAGES] PRIMARY KEY CLUSTERED
(
[MYPROSPECTSALESSTAGESID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[COMPANY] WITH NOCHECK ADD
CONSTRAINT [DF__COMPANY__DATEADD__5FB4032D] DEFAULT (getdate()) FOR [DATEADDED],
CONSTRAINT [DF_COMPANY_DateMod] DEFAULT (getdate()) FOR [DateMod],
CONSTRAINT [DF__company__recruit__3D1F579B] DEFAULT (0) FOR [recruitertype],
CONSTRAINT [PK_COMPANY] PRIMARY KEY NONCLUSTERED
(
[COMPANYID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[MYCOMPANY] WITH NOCHECK ADD
CONSTRAINT [DF__MYCOMPANY_DATEADDED] DEFAULT (getdate()) FOR [DATEADDED],
CONSTRAINT [DF__mycompany__RECOR__4FEB7272] DEFAULT ('P') FOR [RECORDTYPE],
CONSTRAINT [PK_MYCOMPANY] PRIMARY KEY NONCLUSTERED
(
[MYCOMPANYID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[MYPROSPECT] WITH NOCHECK ADD
CONSTRAINT [DF__MYPROSPEC__ACTIV__42286450] DEFAULT ('N') FOR [ACTIVEYN],
CONSTRAINT [PK_MYPROSPECT] PRIMARY KEY NONCLUSTERED
(
[MYPROSPECTID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[MYPROSPECTCONTACTS] WITH NOCHECK ADD
CONSTRAINT [DF_MYPROSPECTCONTACTS_DATEADDED] DEFAULT (getdate()) FOR [DATEADDED],
CONSTRAINT [PK_MYPROSPECTCONTACTS] PRIMARY KEY NONCLUSTERED
(
[MYPROSPECTID],
[MYPROSPECTCONTACTSID] DESC
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[MYPROSPECTNOTES] WITH NOCHECK ADD
CONSTRAINT [DF_MYPROSPECTNOTES_MYDATESTAMP] DEFAULT (getdate()) FOR [MYDATESTAMP],
CONSTRAINT [PK_MYPROSPECTNOTES] PRIMARY KEY NONCLUSTERED
(
[MYPROSPECTID],
[MYPROSPECTNOTESID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[MYPROSPECT_SALESSTAGES] WITH NOCHECK ADD
CONSTRAINT [DF_MYPROSPECT_SALESSTAGES_DATEADDED] DEFAULT (getdate()) FOR [DATEADDED]
GO

ALTER TABLE [dbo].[RFP] WITH NOCHECK ADD
CONSTRAINT [DF_RFP_DUEDATE] DEFAULT (dateadd(day,30,getdate())) FOR [DUEDATE],
CONSTRAINT [DF_RFP_DATEADDED] DEFAULT (getdate()) FOR [DATEADDED],
CONSTRAINT [DF_RFP_DATEUPDATED] DEFAULT (getdate()) FOR [DATEUPDATED],
CONSTRAINT [DF__rfp__SECTORTYPE__355DD6AE] DEFAULT ('2') FOR [SECTORTYPE],
CONSTRAINT [DF__rfp__CONEMAILYN__3651FAE7] DEFAULT ('N') FOR [CONEMAILYN],
CONSTRAINT [DF__rfp__CONFAXYN__37461F20] DEFAULT ('N') FOR [CONFAXYN],
CONSTRAINT [DF__rfp__CONPHONEYN__383A4359] DEFAULT ('N') FOR [CONPHONEYN],
CONSTRAINT [DF__RFP__CONMAILYN__392E6792] DEFAULT ('N') FOR [CONMAILYN],
CONSTRAINT [DF__RFP__DATAMODE__2B0043CC] DEFAULT ('R') FOR [DATAMODE],
CONSTRAINT [DF__RFP__ESTIMATEDDU__2BF46805] DEFAULT ('Y') FOR [ESTIMATEDDUEDATEYN],
CONSTRAINT [DF__RFP__ACTIVEYN__2CE88C3E] DEFAULT ('Y') FOR [ACTIVEYN],
CONSTRAINT [DF__RFP__SOLTYPE__23150941] DEFAULT ('P') FOR [SOLTYPE],
CONSTRAINT [DF__RFP__PRETEXTFORM__24092D7A] DEFAULT ('N') FOR [PRETEXTFORMATYN],
CONSTRAINT [DF__RFP__ACCEPTPARTI__24FD51B3] DEFAULT ('Y') FOR [ACCEPTPARTIALYN],
CONSTRAINT [DF__rfp__DATEMOD__1C5E1BDB] DEFAULT (getdate()) FOR [DATEMOD],
CONSTRAINT [PK_RFP] PRIMARY KEY NONCLUSTERED
(
[RFPID]
) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [ESTDUECHECK] CHECK ([ESTIMATEDDUEDATEYN] = 'Y' or [ESTIMATEDDUEDATEYN] = 'N')
GO

CREATE INDEX [company_ind1] ON [dbo].[COMPANY]([COMPANYNAME], [ACTIVEYN], [ADDRESS1], [CITY], [COMPANYID], [STATECD]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [company_ind2] ON [dbo].[COMPANY]([COMPANYID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [dateUpdated] ON [dbo].[COMPANY]([DATEUPDATED]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [dateAdded] ON [dbo].[COMPANY]([DATEADDED]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_COMPANYNAMEKEY] ON [dbo].[COMPANY]([NAMEKEY]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [dateMod] ON [dbo].[COMPANY]([DateMod]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_COMPANYADDRESSKEY] ON [dbo].[COMPANY]([ADDRESSKEY]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_TICKER] ON [dbo].[COMPANY]([TICKER]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_CORPURL] ON [dbo].[COMPANY]([CORPURL]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [company_match] ON [dbo].[COMPANY]([NAMEKEY], [CORPURL], [TICKER]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_COMPANYPROVIDERREFID] ON [dbo].[COMPANY]([PROVIDERREFID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [company_search] ON [dbo].[COMPANY]([BUSINESS], [REVENUE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [COMPANY_IDXDEDEP] ON [dbo].[COMPANY]([DeDupKey]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_CITY] ON [dbo].[MYCOMPANY] ([CITY]) ')
GO

CREATE INDEX [IX_MYCOMPANY] ON [dbo].[MYCOMPANY]([MYCOMPANYID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_MYCOMPANY_1] ON [dbo].[MYCOMPANY]([COMPANYNAME]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_mycompany_recordtype] ON [dbo].[MYCOMPANY]([RECORDTYPE]) ON [PRIMARY]
GO

/****** The index created by the following statement is for internal use only. ******/
/****** It is not a real index but exists as statistics only. ******/
if (@@microsoftversion > 0x07000000 )
EXEC ('CREATE STATISTICS [Statistic_ReferralName] ON [dbo].[MYCOMPANY] ([ReferralName]) ')
GO

CREATE INDEX [MYPROSPECT_IDX1] ON [dbo].[MYPROSPECT]([USERID], [MYPROSPECTTYPE], [MYPROSPECTID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_MYPROSPECT] ON [dbo].[MYPROSPECT]([USERID], [ACTIVEYN]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [myprospect_idx_mycompanyid] ON [dbo].[MYPROSPECT]([MYCOMPANYID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_MYPROSPECT_1] ON [dbo].[MYPROSPECT]([USERID], [ACTIVEYN], [MYCOMPANYID], [MYPROSPECTTYPE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_MYPROSPECT_2] ON [dbo].[MYPROSPECT]([USERID], [ACTIVEYN], [RFPID], [MYPROSPECTTYPE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_MYPROSPECT_3] ON [dbo].[MYPROSPECT]([USERID], [ACTIVEYN], [COMPANYID], [MYPROSPECTTYPE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_MYPROSPECT_4] ON [dbo].[MYPROSPECT]([USERID], [ACTIVEYN], [NEWSID], [MYPROSPECTTYPE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [myprospect_idx5] ON [dbo].[MYPROSPECT]([MYPROSPECTDATASRCID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_MYPROSPECT_nk] ON [dbo].[MYPROSPECT]([USERID], [namekey]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [myprospect_idx7] ON [dbo].[MYPROSPECT]([sharedmultiuserid], [namekey]) ON [PRIMARY]
GO

CREATE INDEX [myprospect_idx8] ON [dbo].[MYPROSPECT]([sharedmultiuserid], [RFPID]) ON [PRIMARY]
GO

CREATE INDEX [myprospect_idx6] ON [dbo].[MYPROSPECT]([sharedmultiuserid], [MYPROSPECTID]) ON [PRIMARY]
GO

CREATE INDEX [MYPROSPECT_IDXDEDEP] ON [dbo].[MYPROSPECT]([USERID], [DeDupKey]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [idx_myprospect_activeyn] ON [dbo].[MYPROSPECT]([ACTIVEYN]) ON [PRIMARY]
GO

CREATE INDEX [IX_MYPROSPECTCONTACTS] ON [dbo].[MYPROSPECTCONTACTS]([MYPROSPECTCONTACTSID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [myprospectnotes_idx_myprospectnotesid] ON [dbo].[MYPROSPECTNOTES]([MYPROSPECTNOTESID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [myprospectnotes_idx_idx3] ON [dbo].[MYPROSPECTNOTES]([MYPROSPECTID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [idx_myprospectnotes_estclosedate] ON [dbo].[MYPROSPECTNOTES]([ESTCLOSEDATE]) ON [PRIMARY]
GO

CREATE INDEX [Rfp_idx1] ON [dbo].[RFP]([DATEADDED]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [rfp_idx2] ON [dbo].[RFP]([DATAMODE], [SOLTYPE], [DATEADDED], [ACTIVEYN], [SECTORTYPE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFP_IDX4] ON [dbo].[RFP]([DUEDATE], [ACTIVEYN], [DATAMODE], [SOLTYPE], [LOCSTATE], [RFPID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFP_IDX3] ON [dbo].[RFP]([DATEPUBLISHED], [ACTIVEYN], [DATAMODE], [SOLTYPE], [LOCSTATE], [RFPID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFP_IDX6] ON [dbo].[RFP]([DATEUPDATED], [UPDATEUSER]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFP_IDX5] ON [dbo].[RFP]([DUEDATE], [RFPNOBUYER], [RFPCOMPANY]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFP_IDX7] ON [dbo].[RFP]([COMPANYCONTACTID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFP_IDX8] ON [dbo].[RFP]([ENTRYUSER], [DATAMODE], [SOURCE], [SOLTYPE], [DATEADDED], [ACTIVEYN], [SECTORTYPE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFP_IDX9] ON [dbo].[RFP]([PROVIDERSOURCEID], [SOLTYPE], [DATAMODE], [RFPTYPE], [DUEDATE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [RFP_IDX10] ON [dbo].[RFP]([DATEADDED] DESC , [RFPZIP], [SOLTYPE], [RFPCOUNTRYCD]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [rfp-idx11] ON [dbo].[RFP]([LOCSTATE], [SOLTYPE]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

ALTER TABLE [dbo].[MYPROSPECT] ADD
CONSTRAINT [FK_MYPROSPECT_COMPANY] FOREIGN KEY
(
[COMPANYID]
) REFERENCES [dbo].[COMPANY] (
[COMPANYID]
),
CONSTRAINT [FK_MYPROSPECT_MYCOMPANY] FOREIGN KEY
(
[MYCOMPANYID]
) REFERENCES [dbo].[MYCOMPANY] (
[MYCOMPANYID]
),
CONSTRAINT [FK_MYPROSPECT_RFP] FOREIGN KEY
(
[RFPID]
) REFERENCES [dbo].[RFP] (
[RFPID]
),
CONSTRAINT [FK_MYPROSPECT_USERS] FOREIGN KEY
(
[USERID]
) REFERENCES [dbo].[USERS] (
[USERID]
)
GO

ALTER TABLE [dbo].[MYPROSPECTCONTACTS] ADD
CONSTRAINT [FK_MYPROSPECTCONTACTS_MYPROSPECT] FOREIGN KEY
(
[MYPROSPECTID]
) REFERENCES [dbo].[MYPROSPECT] (
[MYPROSPECTID]
)
GO

ALTER TABLE [dbo].[MYPROSPECTNOTES] ADD
CONSTRAINT [FK_MYPROSPECTNOTES_MYPROSPECT] FOREIGN KEY
(
[MYPROSPECTID]
) REFERENCES [dbo].[MYPROSPECT] (
[MYPROSPECTID]
)
GO

ALTER TABLE [dbo].[RFP] ADD
CONSTRAINT [FK_RFP_PARTNER] FOREIGN KEY
(
[partnerid]
) REFERENCES [dbo].[PARTNER] (
[PARTNERID]
),
CONSTRAINT [FK_RFP_PSOURCE] FOREIGN KEY
(
[PROVIDERSOURCEID]
) REFERENCES [dbo].[PROVIDERSOURCE] (
[PROVIDERSOURCEID]
),
CONSTRAINT [FK_RFP_SOURCENEWSID] FOREIGN KEY
(
[sourcenewsid]
) REFERENCES [dbo].[NEWS] (
[NEWSID]
)
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER company_DateMod
ON company
FOR UPDATE

AS

update company
set datemod = getdate()
from inserted where Company.CompanyID = inserted.CompanyID

update companyContact
set datemod = getdate()
from inserted where companyContact.CompanyID = inserted.CompanyID

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER rfp_DateMod
ON rfp
FOR UPDATE

AS

update rfp
set datemod = getdate()
from inserted where rfp.rfpid = inserted.rfpid

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources