Xp_AvailableMedia Returns 0 Rows In SQL Server 2005
May 2, 2007
Without systemAdmin role, xp_AvailableMedia can still run (after granting execute permission) but just return 0 rows.
It seems we have to give the user SystemAdmin role, in order to get the list of devices in SQL 2005, while in previous versions of SQL Server, the user granted the execute permission for xp_AvailableMedia will be able to get the list of devices.
Is this be design? Is there any way the a NON-SA user can retrieve device list? If yes, what extra permissions do we have grant?
Thanks in advance for your help.
Frank
View 3 Replies
ADVERTISEMENT
Nov 29, 2013
I am trying to do a bulk import of data from XML into sQL.
My query returns no errors but no data gets imported.
Here is my XML
?xml version="1.0" encoding="utf-8"?>
<status>
<connection_status>successful</connection_status>
<operation_status>successful</operation_status>
<CustomerDeposits>
[code]....
View 2 Replies
View Related
Oct 22, 2015
Here's what my table looks like;
UniqID | Code |
1 | ABC
1 | 123
2 | ABC
3 | ABC
4 | ABC
4 | ABC
I only want to UniqIds that only have the CODE of ABC... and if it contains ANYTHING other than ABC then It doesnt return that UniqID... Now keep in mind there's multiple different codes.. I'm just looking for a bit of code that drops any ID's that don't have my criteria.
View 9 Replies
View Related
Feb 13, 2015
comparing UNIQUEIDENTIFIER columns..This query returns several rows where the [ReportId] and [LastRunDate] columns are both NULL:
SELECT [c].[Name],[c].[ItemID],[xl].[ReportID]
, MAX([TimeStart]) [LastRunDate]
FROM [dbo].[Catalog] [c]
LEFT JOIN [dbo].[ExecutionLogStorage] [xl] on [c].[ItemID] = [xl].[ReportID]
WHERE [c].[Type] NOT IN (1,5) -- Not a folder or a data source!
group by [c].[Name],[c].[ItemID],[xl].[ReportID]
order by 4
However, trying to just list catalog reports with no execution history returns 0 rows, but I'm expecting it to return a row for every NULL [ReportId] from the above query:
SELECT *
FROM [dbo].[Catalog]
WHERE [Type] NOT IN (1,5) -- Not a folder or a data source!
AND [ItemID] NOT IN (SELECT [ReportID] FROM [dbo].[ExecutionLogStorage])
I even tried casting [ItemId] and [ReportId] columns in the 2nd query to VARCHAR(255), and still got no rows, but the following queries return 0 rows and 1 row (respectively).
select * from [dbo].[ExecutionLogStorage] where [ReportID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
select * from [dbo].[Catalog] where [ItemID] = '0BB2209C-7736-46C8-AD02-4614EBA4F0F1'
View 4 Replies
View Related
Dec 26, 2001
Hi!
I'm trying to create a plan using the maintenance plan wizard. Whenever I click the ellipses button after selecting "Use this directory" (in the Specify Backup Disk Directory screen), the enterprise manager hangs. After doing some investigation, it looks like EM calls the extended sproc "xp_availablemedia". When I try to run this manually, it hangs also. Anyone have any idea what this xp does and where I can find some documentation on it?
I'm using SQL 2000 SP1
Thanks!
View 1 Replies
View Related
Oct 14, 2007
I just installed Sql Server Reporting Services 2005 on a Windows Server 2003 R2 x64 server. I configured Reporting Services through the Reporting Services Configuration tool and everything has a green checkmark.
I'm trying to connect to the webservice, but keep getting 404 not found errors. I've tried putting the following into a web browser and all return a 404 error:
http://msql01/reports/
http://msql01/reportserver/
http://msql01/reportserver/reportservice.asmx?wsdl
http://msql01/reportserver/reportservice2005.asmx?wsdl
I've tried edting the RSWebApplication.config file using the following configurations (restarting IIS afterwards) without any luck:
<UI>
<ReportServerUrl></ReportServerUrl>
<ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>
and
<UI>
<ReportServerUrl>http://msql01/ReportServer/</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
<ReportBuilderTrustLevel>FullTrust</ReportBuilderTrustLevel>
</UI>
Any help is appreciated!
Thanks,
Rob
View 10 Replies
View Related
Jan 3, 2008
After changing the Service account for a SQL Server 2005 SP2 machine the DMV sys.dm_os_performance_counters
is returning zero rows. These values were availiable prior to change
The change in Service account was done via the Configuration manager and the Service account is also part of the Local Administrator group
Since this change the Performance Counters in Perfmon for SQLSEVERatabases and others are also not availiable
Any suggestions on where to start looking ?
Nadreck
View 4 Replies
View Related
May 7, 2007
Good day,
I just like to ask if anybody has experienced getting empty rows from SQL data adapter? I'm using SQL Server 2005. Problem is when the sql is run on Query Analyzer it retrieves a number of rows but when used in my application it returns 0 or empty rows.
I thought the connection is not the problem since I got my columns right. Below is my code snippet.
Thanks!
const string COMMAND_TEXT = @"select distinct somefield as matchcode, count(somefield) "
+ "as recordcount from filteredaccount where StateCode = 0 group by somefield having count(somefield) > 1";
SqlDataAdapter adapter = new SqlDataAdapter(COMMAND_TEXT, connection);
DataTable dt = new DataTable(sometablename);
adapter.Fill(dt);
View 13 Replies
View Related
Jun 12, 2007
Hi everyone:
I guess this should be a simple question but have not been able to find the answer, does anyone know how to make a SQL Sentence to return at least one row when counting?
SELECT COUNT(Id_Field), Field2 FROM Table1 WHERE Code_Field = 1 GROUP BY Field2
This will return 0 rows when the where criteria is not matched by any record on the Table1, but I would like to have one row counting 0 rows, in stead it returns no rows at all.
Thanks for any help.
View 4 Replies
View Related
Nov 1, 2005
Hi all,
I am trying to build a association table (t2) to store a list of users
have viewed an item in my records table (t1). My goal is to send the
UserID parameter to the query and return to the user a read / not read
marker from the query so I can handle the read ones differently in my
.net code. The problem is that I cannot work out how to return anything
but the read data to the client. So far my stored proc looks like this
DECLARE @UserID AS Int -- FOR TESTING
SET @UserID = 219 -- FOR TESTING
SELECT t1.strTitle, t1.MemoID, Count(t2.UserID) AS ReadCount,t2.UserID
FROM t1
LEFT OUTER JOIN
t2 ON t1.MemoID = t2.MemoID
WHERE t2.UserID = @UserID
GROUP BY t1.MemoID, t1.strTitle,t2.UserID
It works fine but only returns those records from t1 that are read. I
need to return the records with null values also! I may have built the
assoc table wrong and would really appreciate some pointers on what I
am doing wrong. (assoc table has rID, MemoID and UserID columns)
Please help!
Many thanks
View 2 Replies
View Related
Aug 15, 2003
Hi,
I have DB monitoring jobs which use Sysperfinfo to monitor some of the counters. On One SQL 2K Server since few days the select on sysperfinfo returns 0.
Do you think I need to start any service from the OS side to enable this?
Your input is highly apppreciated.
Thanks,:confused:
View 2 Replies
View Related
Oct 23, 2007
Hi,
I'm having a little trouble with the following code:
SELECT DISTINCT cd1.*, cd2.*
FROM Table1 cd1 LEFT JOIN Table2 cd2
ON cd1.RegNr=cd2.RegNr
WHERE cd1.RegNr = $RegNr
I want it to return the 2 rows that is present in the tables but it returns 4.
1262007-10-20 10:14:00
1262007-10-20 10:14:00
1262007-10-20 10:17:00
1262007-10-20 10:17:00
View 18 Replies
View Related
Feb 24, 2007
I have the following query:
select sq.*, p.numero, p.nombre
from paf p right outer join dbo.GetListOfSquaresForShippingLot(@lot) sq on sq.number = p.numero and sq.version = p.numero
The @lot parameter is declared at the top ( declare @lot int; set @lot = 1; ). GetListOfSquaresForShippingLot is a CLR TVF coded in C#. The TVF queries a XML field in the database and returns nodes as rows, and this is completed with information from a table.
If I run a query with the TVF only, it returns data; but if I try to join the TVF with a table, it returns empty, even when I'm expecting matches. I thought the problem was the data from the TVF was been streamed and that's why it could not be joined with the data from the table.
I tried to solve that problem by creating a T-SQL multiline TVF that is supposed to generate a temporary table. This didn't fix the problem.
What can I do? Does anybody know if I can force the TVF to render its data somewhere so the JOIN works? I was thinking a rowset function could help, but I just can't figure out how.
PLEASE HELP!!!!
Let me know if you want the code for the CLR TVF. This is the code for the T-SQL TVF:
CREATE FUNCTION [dbo].[GetTabListOfSquaresForShippingLot]
(
@ShippingLot int
)
RETURNS
@result TABLE
(
Number int, Version int, Position smallint,
SubModel smallint, Quantity smallint,
SquareId nvarchar(5),
ParentSquareId nvarchar(5),
IsSash smallint,
IsGlazingBead smallint,
Width float,
Height float,
GlassNumber smallint,
GlassWidth float,
GlassHeight float
)
AS
BEGIN
INSERT INTO @result
SELECT *
FROM dbo.GetListOfSquaresForShippingLot(@ShippingLot)
RETURN
END
View 6 Replies
View Related
May 3, 2006
I have installed the trial enterprise edition of sql server 2005. I am trying to recreate a dts packages. I am using an iseries OLE DB provider. In the data flow the connection to a physical file that has string data gets translated into binary data. Has any one gotten a download to work with the correct data?
View 1 Replies
View Related
Apr 24, 2008
I have a Select Distinct myfield that returns multiple rows with same value for myfield when it should only one. Why is this happening?
View 4 Replies
View Related
Sep 8, 2004
I maintain a simple employment (job) tracking web application.
Jobs can be set to 5 different statuses: Open, Closed, Filled, Pending, or Cancelled.
There is a table in the database called statusLog, which records everytime a job is set to opened, or set to closed, etc. It records the job number, the date it was changed, and what the job was changed to.
Here is a short example of what a few entries might look like:
Status Date Job number
Open10/6/2002 2:34:56 PMTEST2845
Hold10/11/2002 12:19:29 PMTEST2845
Closed10/29/2002 2:00:54 PMTEST2845
Pending10/25/2002 3:37:06 PMTEST2877
What I need to do is write an SQL query that will return to me all entries in this table, between two certain dates, that ONLY have entries during those dates.
Basically I need to know how many "new" jobs were set to open during a month. I can easily just do a count of how many jobs were set to open, but this will not give a count of "new" jobs.
Example: during june a job could be set to open. Then in july it could be set to pending. Then in august, it could be re-opened, and set to open.
If I ran this query for the month of August, it would return that job as being opened in august. But it wasn't a new job, meaning it had already been in the system in previous months.
Is there some way I can select all "Open" jobs, between a certain date, that do not exist anywhere in the table previous to the date it was entered? This would give me a result set containing only new jobs.
The only way I've thought of yet is to get a result set of all jobs simply set to open during a month, then one by one for each record, go back and run another SQL query to see if it exists in the table anywhere other than in that month.
This seems horribly inefficient to me however, as I do not want to be doing 34,000 independent SQL calls for every single "open" job it finds during a certain month.
thanks
View 2 Replies
View Related
Oct 25, 2004
We are running the following query, which has a unique index on Table_2 (col1 and sys1), and Column col1 from Table_1 is unique.
select top 100 s.*, x.col1
from Table_1 s
left outer join Table_2 x
on x.col1 = s.col1 and x.sys1 = 'SYSTEM0'
Unfortunately this query returns duplicate rows. And every time the result is different
But once we dbcc dbreindex the unique index on Table_2, the result will not have any dups.
Any ideas?
Thanks
Steve
View 1 Replies
View Related
Dec 3, 2013
Here is my query which returns multiple rows
SELECT
R.name, R.age,R.DOB,
ISNULL(D.Doc1,'NA') AS doc1,
ISNULL(C.Doc2,'NA') AS doc2
FROM
REQ R
inner join RES S ON R.Request_Id=S.Request_Id
inner join RES1 D ON D.Response_Id=S.Response_Id
inner join REQ1 C ON C.Request_Id=R.Request_Id
select * from RES1 where Response_Id = 111 -- return 3
select * from REQ1 where Request_Id = 222 --- returns 2
So at last inner join retuns 3*2 = 6 records , which is wrong here and i want to show 3 records in doc1 row and 2 records in doc 2 rows ...
View 5 Replies
View Related
May 8, 2008
I'm pretty new to .NET and am looking for advice on how to speed up a simple stored procedure that returns 35,000 plus rows. It's a super simple query that returns a client list. It's just that there is soooooo many rows, it's super slow when the page loads.
View 4 Replies
View Related
Jul 22, 2007
Hello All.
I am struggling with the below join block in my stored procedure.
I can't seem to get the duplicate row problem to go away. It seems that SQL is treating each new instance of an email address as reason to create a new row despite the UNIONs.
I understand that if I am using UNION, using DISTINCT is redundant and will not solve the duplicate row problem.
Primary Keys: none of the email address columns are primary keys. Each table has an incrementing ID column that serves
as the primary key.
I am guessing I am encountering this problem because of how
I have structured my Join statements? Is it possible to offer advice without a deeper understanding of my data model or
do you need more information?
Thanks for any tips.
Code:
select emailAddress from Users union
select user_name from PersonalPhotos union
select email_address from EditProfile union
select email_address from SavedSearches union
select distinct email_address from UserPrecedence union
select email_address from LastLogin) drv
Left Join Users tab1 on (drv.emailAddress = tab1.emailAddress)
Inner Join UserPrecedence tab5 on tab5.UserID=tab1.UserID
Left Join PersonalPhotos tab2 on (drv.emailAddress = tab2.user_name)
Left Join LastLogin tab4 on (drv.emailAddress = tab4.email_address)
Left Join EditProfile tab3 on (drv.emailAddress = tab3.email_address)
Left Join SavedSearches tab6 on (drv.emailAddress = tab6.email_address
View 8 Replies
View Related
Aug 9, 2014
I don't understand using a dynamic cursor.
Summary
* The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats.
* As far as I know a fetch-next-statement always returns a single row?
* Using a static cursor works as aspected.
* Works on production OLTP as well as on a local SQL server instance.
Now the Skript to reproduce the whole thing.
create database objects
-- create the partition function
create partition function fnTestPartition01( smallint )
as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;
[Code]....
Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.
View 8 Replies
View Related
Jun 18, 2015
I have a query that based 2 tables. I wrote a query with a left join on the base table but the result set returns multiple rows for each occurrence in the second table because of the left join. I want but to return all records from on table A and only matching records from table B which id but I would wan tit to keep return them vertically as the because it make it difficult to read when put in a spreadsheet. It want it to return the values horizontally so the rows are not increasing for each occurrence on table b.
View 5 Replies
View Related
Jan 19, 2015
I have a simple query that joins a largeish fact table (3 million rows) to a view that returns 120 rows. The SKEY in the view is returned via a scalar function. The view returns instantly if queried on it's own however when joined to the fact table in the simple query below results in a query execution plan that runs forever. Interestingly if I change the INNER JOIN to a LEFT OUTER JOIN the query returns the matched results almost instantly.
Select
Dimension.Age_Band.[10_Year_Age_Band],
Count(*)
From
Fact.APC_Episodes
Inner Join Dimension.Age_Band ON
Fact.APC_Episodes.AGE_BAND_SKEY = Age_Band.AGE_BAND_SKEY
Group By
Dimension.Age_Band.[10_Year_Age_Band]
I know joining to a view using a column generated by a scalar function is not a good recipe for performance. I also know that I could fix this by populating a physical table with the view first as I have already tested this though I hoping not to have to go down that route.
Why a LEFT OUTER JOIN works and not an INNER JOIN or anyway I can get the query optimizer to generate an execution plan that works?
View 9 Replies
View Related
May 20, 2008
I'm running SQL Server 2008 Feb08 CTP and I've got a tablix with column groupings and row groupings which works nicely, I'm a becomming a big fan of the tablix.
However sometimes the filter I have on a column group returns no records and the whole column group disappears.
Is there any way to make this column appear with but with empty cells as I have another tablix down the page with the same columns and I want the columns to all line up on the multiple tablix.
In Version 2005 I used to achieve the equivalent of a column group within a table by adding a list with filter to the cell, this meant the column always was shown, the tablix allows does this much more elegantly but I'll have to go back to the old method if I can't make the column remain.
Am I just missing a simple option?
Nathan
View 1 Replies
View Related
Jul 21, 2015
Our clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch (
Id int not null,
AllText nvarchar(400)
)
create unique index test_tfts on TestFullTextSearch(Id);
create fulltext catalog ftcat_tfts;
[Code] ....
View 4 Replies
View Related
Apr 14, 2007
I have a stored procedure on a SQL server, which is workign correctly to check some date/time parameters and then insert a row into a log table.
I am calling it from an ASP page. Initially I just called it and didn't worry about the return value. However the Stored procedure now will return a value to determine if it made the change or not and if not why (ie log entry was at incorrect time etc).
I woudl liek to capture this returned value in my code to display a message to the user but am havign problems finding the right way to get the value.
I am calling the SP as follows:
Shared Sub createlogentry(ByVal ID, ByVal tme, ByVal val) Dim result As String Dim cs As String = ConfigurationManager.ConnectionStrings("connecttion1").ToString Using con As New System.Data.SqlClient.SqlConnection(cs) con.Open() Dim cmd As New System.Data.SqlClient.SqlCommand() cmd.Connection = con cmd.CommandType = Data.CommandType.StoredProcedure cmd.CommandText = "CreateLogEntry" cmd.Parameters.Add("@ChID", Data.SqlDbType.Int) cmd.Parameters("@ChID").Value = ID cmd.Parameters.Add("@Value", Data.SqlDbType.Int) cmd.Parameters("@Value").Value = val result = cmd.ExecuteNonQuery().ToString End Using End Sub
I have tried amending the ExecuteNonQuery line to ExecuteReader()
Any help appreciated
Regards
Clive
View 3 Replies
View Related
Apr 6, 2006
Environment:
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
Problem & Info:
I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls
Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
Desired Help:
How to perform
1) stripping out all undesired rows
2) importing each column into sql table
View 1 Replies
View Related
Feb 7, 2007
I have a complex view in my sql 2005 database.
The view returns a column that could be null (as the result of a left outer join).
The coulmn that is returned is an integer.
Everything works fine if I run the view from SQL 2005 Management Studio.
My column value is always null if I use ADO.NET's SqlAdapter to return a DataTable.
Has anybody seen this behaviour before?
Any help appreciated.
Regards,
Paul.
View 2 Replies
View Related
May 29, 2008
currently i m developing a web application with using sql server 2005
and i was testing yesterday a sql update query with sql server management studio.
in my update query i forgot to put where condition and now all the rows of table are updated. is there any solution to undo this and retrieve all rows back?
Regards Selena
View 6 Replies
View Related
Feb 19, 2008
Hi,
I have a DB running for a while, but it reached 32767 rows and stopped.. The DB is only 20MB big. Where can i ajdust this setting to a bigger number of rows?
thxs.
View 3 Replies
View Related
Apr 14, 2008
Hi,
Good morning to all.My table: User_Group_Map(UserID UNIQUEIDENTIFIER,GroupID UNIQUEIDENTIFIER)
Now, I want to write one stored procedure that can insert rows into the above table, but more number of rows at-once.
Means, the program should allow multiple insertions without the need to call the stored procedure from front-end more number of times.
Can anyone please help me on this...
Thanks in advance...Ashok kumar.
View 3 Replies
View Related
Jun 14, 2007
I'm using Visual Studio 2005 and SQL Server 2005. I am creating a report to put on the Internet using C# and other .NET features. I'm building several charts using database information extracted from Microsoft Excel. I can make charts based on the data from columns using the categories, series, and values on the Data tab of chart properties. However, when I create a query that grabs a record ( a row) from a database, it forces me to use the field values even though each field contains only one value.
Basically, I want to take a row from a database and make it my x-axis data values and another row (record) from a database and make it my y-axis data values.
I'd prefer not to change the format in the database, if possible. Thanks in advance.
View 1 Replies
View Related
Jul 20, 2005
SQL Server 7.0If I run the following in Query Analyzer I get no records returned:exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176, 183'If, however, I run either :exec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '176'orexec GetLeadsOutcome_Dealer '1/1/2003','12/2/2003',10, '183'I get a single record returned in each case (which is what I wouldexpect).The SQL for GetLeadsOutcome_Dealer is:CREATE PROCEDURE GetLeadsOutcome_Dealer@FromDate smalldatetime,@ToDate smalldatetime,@OutcomeTypeID integer,@DealerCode varchar(8000)ASDECLARE @TotalLeads integerBEGINSELECT @TotalLeads=COUNT(fldLeadID) FROM tblLeads WHERE(tblLeads.fldDealerID IS NOT NULL)SELECT DISTINCT (dbo.tblDealers.fldDealerName),COUNT(dbo.tblLeads.fldLeadID) as LeadCount,@TotalLeads AS TotalLeadsFROM tblLeads LEFT OUTER JOINtblOutcome ON tblLeads.fldOutcomeID =tblOutcome.fldOutcomeID RIGHT OUTER JOINtblDealers RIGHT OUTER JOINtblRegion ON tblDealers.fldRegionID =tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerIDWHERE (((dbo.tblLeads.fldEntered) BETWEEN @FromDate And @ToDate)) ANDtblOutcome.fldOutcomeID=@OutcomeTypeID AND(dbo.tblDealers.fldDealerCode in (@DealerCode))GROUP BY dbo.tblDealers.fldDealerNameORDER BY dbo.tblDealers.fldDealerNameENDGOHowever, if I open EM, open a table in query view, and paste this intothe SQL window,SELECT DISTINCT tblDealers.fldDealerName, COUNT(tblLeads.fldLeadID) ASLeadCountFROM tblLeads LEFT OUTER JOINtblOutcome ON tblLeads.fldOutcomeID =tblOutcome.fldOutcomeID RIGHT OUTER JOINtblDealers RIGHT OUTER JOINtblRegion ON tblDealers.fldRegionID =tblRegion.fldRegionID ON tblLeads.fldDealerID = tblDealers.fldDealerIDWHERE (tblLeads.fldEntered BETWEEN '1/1/2003' AND '12/2/2003') AND(tblOutcome.fldOutcomeID = 10) AND (tblDealers.fldDealerCode IN (176,183))GROUP BY tblDealers.fldDealerNameORDER BY tblDealers.fldDealerNameI get two records returned.What is happening?TIAEdwardTABLE DEFS:if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblLeads_tblDealers]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblLeads] DROP CONSTRAINT FK_tblLeads_tblDealersGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblSystemUsers_tblDealers]') andOBJECTPROPERTY(id, N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblSystemUsers] DROP CONSTRAINTFK_tblSystemUsers_tblDealersGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblLeadNotes_tblLeads]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblLeadNotes] DROP CONSTRAINTFK_tblLeadNotes_tblLeadsGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[FK_tblDealers_tblRegion]') and OBJECTPROPERTY(id,N'IsForeignKey') = 1)ALTER TABLE [dbo].[tblDealers] DROP CONSTRAINT FK_tblDealers_tblRegionGOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblDealers]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblDealers]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblLeads]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[tblLeads]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblOutcome]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[tblOutcome]GOif exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblRegion]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[tblRegion]GOCREATE TABLE [dbo].[tblDealers] ([fldDealerID] [int] IDENTITY (1, 1) NOT NULL ,[fldDealerCode] [varchar] (50) NOT NULL ,[fldDealerName] [varchar] (50) NULL ,[fldDealerTel] [varchar] (20) NULL ,[fldDealerEmail] [varchar] (100) NULL ,[fldDealerContact] [varchar] (50) NULL ,[fldRegionID] [int] NULL ,[fldDealerActive] [smallint] NOT NULL ,[fldHeadOffice] [smallint] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblLeads] ([fldLeadID] [int] IDENTITY (1, 1) NOT NULL ,[fldAccountNo] [varchar] (50) NULL ,[fldDealerID] [int] NULL ,[fldStatusID] [int] NOT NULL ,[fldOutcomeID] [int] NOT NULL ,[fldContacted] [smallint] NOT NULL ,[fldDateContacted] [smalldatetime] NULL ,[fldAppointment] [smallint] NULL ,[fldShowRoom] [smallint] NOT NULL ,[fldTestDrive] [smallint] NOT NULL ,[fldSalesPersonID] [int] NULL ,[fldCustomerName] [varchar] (50) NULL ,[fldCHouseNo] [varchar] (50) NULL ,[fldCStreet] [varchar] (50) NULL ,[fldCDistrict] [varchar] (50) NULL ,[fldCTown] [varchar] (40) NULL ,[fldCCounty] [varchar] (50) NULL ,[fldCPostcode] [varchar] (50) NULL ,[fldPhoneInd] [varchar] (20) NULL ,[fldCTel] [varchar] (50) NULL ,[fldNewBusinessDate] [smalldatetime] NULL ,[fldAgreementType] [varchar] (50) NULL ,[fldCashPrice] [smallmoney] NULL ,[fldBalanceFin] [smallmoney] NULL ,[fldCustRate] [varchar] (10) NULL ,[fldOrigTerm] [int] NULL ,[fldPPPType] [varchar] (10) NULL ,[fldBalloonValue] [smallmoney] NULL ,[fldMonthlyInstal] [smallmoney] NULL ,[fldRegNo] [varchar] (10) NULL ,[fldRegDate] [smalldatetime] NULL ,[fldModel] [varchar] (50) NULL ,[fldDescription] [varchar] (50) NULL ,[fldTheoPIFDate] [smalldatetime] NULL ,[fldMonthsToGo] [int] NULL ,[fldBalanceOS] [smallmoney] NULL ,[fldLeadPrinted] [smallint] NULL ,[fldMergeFile] [varchar] (255) NULL ,[fldTPS] [varchar] (10) NULL ,[fldMPS] [varchar] (10) NULL ,[fldUpdated] [smalldatetime] NULL ,[fldUpdatedBy] [int] NULL ,[fldEntered] [smalldatetime] NULL ,[fldReasonLeadNotProgressed] [varchar] (5000) NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblOutcome] ([fldOutcomeID] [int] IDENTITY (1, 1) NOT NULL ,[fldOutcomeCode] [int] NULL ,[fldOutcome] [varchar] (100) NULL ,[fldConvertedSales] [smallint] NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[tblRegion] ([fldRegionID] [int] IDENTITY (1, 1) NOT NULL ,[fldRegionCode] [varchar] (10) NULL ,[fldRegion] [varchar] (50) NULL) ON [PRIMARY]GOALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADDCONSTRAINT [PK_tblDealers] PRIMARY KEY CLUSTERED([fldDealerID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADDCONSTRAINT [PK_tblLeads] PRIMARY KEY CLUSTERED([fldLeadID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADDCONSTRAINT [PK_tblOutcome] PRIMARY KEY CLUSTERED([fldOutcomeID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADDCONSTRAINT [PK_tblArea] PRIMARY KEY CLUSTERED([fldRegionID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOALTER TABLE [dbo].[tblDealers] WITH NOCHECK ADDCONSTRAINT [DF_tblDealers_fldActive] DEFAULT ((-1)) FOR[fldDealerActive],CONSTRAINT [DF_tblDealers_fldHeadOffice] DEFAULT (0) FOR[fldHeadOffice],CONSTRAINT [IX_tblDealers] UNIQUE NONCLUSTERED([fldDealerCode]) ON [PRIMARY]GOALTER TABLE [dbo].[tblLeads] WITH NOCHECK ADDCONSTRAINT [DF_tblLeads_fldOutcomeID] DEFAULT (10) FOR[fldOutcomeID],CONSTRAINT [DF_tblLeads_fldContacted] DEFAULT (0) FOR [fldContacted],CONSTRAINT [DF_tblLeads_fldAppointment] DEFAULT (0) FOR[fldAppointment],CONSTRAINT [DF_tblLeads_fldShowRoom] DEFAULT (0) FOR [fldShowRoom],CONSTRAINT [DF_tblLeads_fldTestDrive] DEFAULT (0) FOR [fldTestDrive],CONSTRAINT [DF_tblLeads_fldLeadPrinted] DEFAULT (0) FOR[fldLeadPrinted],CONSTRAINT [DF_tblLeads_fldTPSMatch] DEFAULT ('NO') FOR [fldTPS],CONSTRAINT [DF_tblLeads_fldMPSMatch] DEFAULT ('NO') FOR [fldMPS]GOALTER TABLE [dbo].[tblOutcome] WITH NOCHECK ADDCONSTRAINT [DF_tblOutcome_fldConvertedSales] DEFAULT (0) FOR[fldConvertedSales]GOALTER TABLE [dbo].[tblRegion] WITH NOCHECK ADDCONSTRAINT [IX_tblRegion] UNIQUE NONCLUSTERED([fldRegionCode]) ON [PRIMARY]GOALTER TABLE [dbo].[tblDealers] ADDCONSTRAINT [FK_tblDealers_tblRegion] FOREIGN KEY([fldRegionID]) REFERENCES [dbo].[tblRegion] ([fldRegionID])GOALTER TABLE [dbo].[tblLeads] ADDCONSTRAINT [FK_tblLeads_tblDealers] FOREIGN KEY([fldDealerID]) REFERENCES [dbo].[tblDealers] ([fldDealerID])GOalter table [dbo].[tblLeads] nocheck constraint[FK_tblLeads_tblDealers]GO
View 2 Replies
View Related
