SQL Server 2008 :: Creating Primary Key On Temporary Table?

Apr 30, 2015

Environment: Microsoft SQL Server Standard Edition (64-bit), 10.0.5520.0

I was doing a code review for another developer and came across this code:

CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)

[Code] ....

This EXECUTES with no error or warning message.However, if I change this to CREATE the PK in an ALTER TABLE statement, I get the (expected by me) error:

CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)
,OccurrenceSeqID INT

[code]...

==> Msg 8111, Level 16, State 1, Line 17 Cannot define PRIMARY KEY constraint on nullable column in table '#ABC'.

==> Msg 1750, Level 16, State 0, Line 17 Could not create constraint. See previous errors.

(note: As the #ABC table is an actual copy of a few of the columns in a "permanent" table, I will likely change the definition as follows such that the columns are defined to match the names / datatypes / NULLability:

SELECT TOP 0
CAST('01-01-1980' AS DATETIME) AS [ReportRunTime]
,SourceID
,VisitID
,BaseID
,OccurrenceSeqID

[Code] .....

View 9 Replies


ADVERTISEMENT

Need Help Creating A Temporary Table In MS SQL Server.

Oct 18, 2006

Hello,

I am working on a webapp using VB.net

Right now I am writing to a sql table during a process where the end user
starts entering the contents for a file that is going to be generated once he
finishes entering the data, but the problem is that if more than one user is
doing the same process the data would get mixed up.  To avoid this I
thought in creating a temporary table (its name will consist of a string
and the current date time). 

 I would like to see any tutorial
about creating and working with temp tables.  Or if you have any
suggestions, I will appreciate them.  Thanks

View 1 Replies View Related

SQL Server 2012 :: Creating And Dropping Global Temporary Table?

Apr 3, 2015

I want to create and drop the global temporary table in same statement.

I am using below command but I am getting below error

Msg 2714, Level 16, State 6, Line 11

There is already an object named '##Staging_Temp' in the database.

if object_id('Datastaging..##Staging_Temp') is not null
begin
drop table ##Staging_Temp
end
CREATE TABLE ##Staging_Temp(
[COL001] [varchar](4000) NULL,

[Code] ....

View 1 Replies View Related

SQL Server 2008 :: Add ID And Primary Key To Large Table(s)

Apr 24, 2015

Background:

* SQL Server 2008 R2
* Database was created from a third party product. The product writes to the 3 tables that I need to make changes to 24/7 and downtime is not an option. All changes must be done live.
* Database overall size is ~200 GB
* The 3 tables I must update make up ~190 GB of that space.
* Tables have no primary key or ID columns. Therefore, the data is highly fragmented.
* Of the ~190 GB of space allocated for the tables, there is roughly 70 GB of actual data.
* Rows of the table are not guaranteed to be unique. In fact, on one of the tables, tests were ran with a small sample of data and duplicates were very much evident.

What I'm trying to accomplish here is to get an ID column added to the 3 tables and set that ID field as the primary key. Doing so will force the data to become much less fragmented than it is currently and with purging and new inserts, eventually fragmentation will be nearly non-existent.

Problem:
Making table changes on tables this large while data is constantly being added poses many risks and can cause data loss. This was tried on a smaller table than these three and the entire table was lost in the process. Restore from backup was needed to get back to most recent log backup point.

Original Solution:
My original plan was to create a backup of each table and run the script below to migrate the majority of the data temporarily into the new table. I could then update the original table (which now would contain much less data) and then migrate the data back.

CREATE TABLE #temp
(
MsgDate varchar(10)
,MsgTime varchar(8)
,MsgPriority varchar(30)
,MsgHostname varchar(255)

[Code] ....

Original Solution Problem:
The problem with the solution above is that it calls the DELETE function on the original table using the values from the temporary table. When there are duplicate rows, which have not all been inserted into the backup table yet, they will all be removed from the original table because there is nothing unique to separate them out. In my testing, I had 10,000 rows in the original table and ended up with 9,959 rows in the backup table.

Question 1: Is my approach to making these table changes reasonable?
Question 2a: If so, how can I make sure I don't lose data as part of this temporary migration of the data to my backup tables?
Question 2b: If not, what would be a better approach that isn't going to cause disruption to the application that INSERTs data 24/7 and won't have any risk of data loss?

View 9 Replies View Related

Creating Temporary Table

Jul 20, 2005

Hi,How can I create a temporary table say "Tblabc" with column fieldsShmCoy char(2)ShmAcno char(10)ShmName1 varchar(60)ShmName2 varchar(60)and fill the table from the data extracted from the statement..."select logdetail from shractivitylog"The above query returns single value field the data seperated with a '·'Ex:BR··Light Blue Duck··in this case I should getShmCoy = 'BR'ShmAcno = ''ShmName1 = 'Light Blue Duck'ShmName2 = ''I want to do this job with single SQL query. Is it possible. Pls help.Herewith I am providing the sample dataBR··Light Blue Duck···0234578···BR··Aqua Duck···0234586···UB··Aqua Duck··Regards,Omav

View 1 Replies View Related

Creating Temporary Table With SELECT INTO

Jul 20, 2005

HiDose any body know why a temporary table gets deleted after querying it thefirst time (using SELECT INTO)?When I run the code bellow I'm getting an error message when open the temptable for the second time.Error Type:Microsoft OLE DB Provider for SQL Server (0x80040E37)Invalid object name '#testtable'.-------------------------------------------------------------------------------------------cnn.Execute("SELECT category, product INTO #testtable FROM properties")'---creating temporary TestTable and populate it with values from anothertableSET rst_testt = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTableSET rst_testt2 = cnn.Execute("SELECT * from #testtable") '----- ERRORopening the temporary TestTable for the second time (that where the erroroccurred)rst_testt2.Close '---- closing table connectionSET rst_testt2 = nothingrst_testt.Close '----- closing table connectionSET rst_testt = nothingcnn.Execute("DROP TABLE #testtable") '------ dropping the temporaryTestTable'-----------------------------------------------------------------------------------------But when I create the temp table first and then INSERT INTO that table somevalues then it is working fine.'-----------------------------------------------------------------------------------------cnn.Execute("CREATE TABLE #testtable (category VARCHAR(3), productVARCHAR(3))")cnn.Execute("INSERT INTO #testtable VALUES('5','4')")SET rst_testt = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTableSET rst_testt2 = cnn.Execute("SELECT * from #testtable") '----- openingthe temporary TestTable for the second timerst_testt2.Close '----- closing table connectionSET rst_testt2 = nothingrst_testt.Close '----- closing table connectionSET rst_testt = nothingcnn.Execute("DROP TABLE #testtable") '------ dropping the temporaryTestTable'-----------------------------------------------------------------------------------------Does any body know why the first code (SELECT INTO) is not working where thesecond code it working?regards,goznal

View 4 Replies View Related

Creating A Common Table Expression--temporary Table--using TSQL???

Jul 23, 2005

Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!

View 11 Replies View Related

Problem While Creating Index For Temporary Table...

Sep 27, 2007



Hi,

i have created index for a temporary table and this script should used by multiusers.So when second user connecting to it is giving index i mean object already exists.

So what i need is when the second user connected the script should create one more index on temporary table.Will sql server provide any random way of creating indexes if the index exists already with that name??

Thank You,

View 6 Replies View Related

SQL Server 2008 :: Does Change Data Capture Require A Table Have Primary Key

Jan 13, 2013

Or can it record before and after column changes based on the LSN only?

An extract from a file based legacy accounting system is performed every night. The system does not have a primary key because transactions are managed through program code. (the more things change...). The extract is copied to text in Unix and FTP'd to Windows, where the file is loaded into SQL Server by kill & fill. Because of the expense of modifying the source system, there is enormous inertia/resistance to injecting a primary key at the source, so kill & fill it stays.

In reading about Change Data Capture, it seemed to me that column level insert update and delete are stored in tables that remember the before and after content of each column tracked. In my reading I have seen many references to the LSN to decide when and what to record as changed, but I have not seen any refereference to the necessity of a primary key for Change Data Capture to work. This is in contrast to replication, where the requirement for the existence of a primary key is made plain.

Is it possible to use Change Data Capture against a table without a primary key? How to use it to change the extract from kill and fill to incremental.

View 9 Replies View Related

Difference In Creating Temporary Table By #table And ##table

Nov 29, 2006

Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference.
pls reply.
first:
create table ##temp
(
name varchar(25),
roll int
)
insert into ##temp values('banti',1)
select * from ##temp
second:
create table #temp
(
name varchar(25),
roll int
)
insert into #temp values('banti',1)
select * from #temp

both works fine , then what is the difference
waiting for ur reply
Banti"

View 1 Replies View Related

Creating Table With A Primary Key

Jun 29, 2006

Hi,

I need to create a new table in our database.
This table is not linked into the existing schema in anyway, so i'm not sure if I need a primary key or not.
either way, coudl anyone tell me how to create a primary key ni the CREATE TABLE statement.
I have tried searching but cannot find the answer.

many thanks,
Matt

View 7 Replies View Related

Creating A Table With A Dual Primary Key

Jul 12, 2004

This question may be a little complicated.

I am building a DTS Package that is moving data from our webstore (written in house) to a Warehouse Management System(WMS - Turnkey) and I've encountered a problem. both pieces of software have an orders table and an Ordered_Items table, related by the order_ID (makes sense so far). Here is the problem. The primary key on the webstore's Ordered_Items table is a single column (basically an Identity variable), while the primary key on the WMS's Ordered_Items table is a dual column primary key, between the Order_ID and the Order_LineID, so the data should be stored like:

OrderID Order_LineID
1 1
2 1
2 2
2 3
3 1
3 2
4 1

Get the Idea? So I have to create this new Order_LineID column. How can I accomplish this with a SQL statement?

Thanks!!!!!

View 5 Replies View Related

Creating A New Numbered Column As Primary Key In A Table

Jul 23, 2005

This is a fairly simple question, but what is the easiest way to:create a new numbered column (where value is simply the row number) inan existing table and setting it as a primary key?

View 1 Replies View Related

Script For Creating Table With Multiple Primary Keys

Feb 1, 2007

Hi,

I am trying to execute following sql script in sql-server 2000 query analyzer

CREATE TABLE user_courses (user_id varchar(30) NOT NULL PRIMARY KEY,
course_id varchar(10) NOT NULL PRIMARY KEY)

Its give's me following error :-
Cannot specify multiple primary key constraint

Hence I am not able to ceate table with multiple primary keys. So can any one tell me how to get this done?.

Secondly, Primary key must be unique i.e duplicate values are not allowed in P.K field. But in this case since I am declaring two fileds as primary keys.
Will it allow me to have following records in the user_courses table?
user_id(P.K) course_id(P.K)
bob CRS235
alice CRS235
Tim CRS235
tom CRS635

So, if we consider both the fields as primary keys together than I am not voilating Uniqueness constraint. But, if I look at course_id alone then I am voilating uniqeness property?

Thanks,

View 4 Replies View Related

SQL Server 2008 :: Change Primary Key Non-clustered To Primary Key Clustered

Feb 4, 2015

We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?

View 2 Replies View Related

SQL Server 2008 :: String As Primary Key

Feb 26, 2015

I have tables in database where a VARCHAR(50) string is unique identifier. The database currently has an integer identity column as clustered primary key, and there's a non-clustered index on string column. The customer always queries based on a defined set of the identifier (string) column.

I wonder if someone sees an advantage of adding a persisted computed column to the table as the checksum of the string column, and then create a non-clustered index on the checksum and the string. When a customer requests data, we would compute the checksum of the customer provided identifier and add to the where clause or join, that the checksum and string must match.

Will SQL Server perform checksum check (integer) and only if it succeeds, perform the string check, in which case I see an advantage of added the checksum column? Or will SQL Server always check for both the checksum and string, in which case the additional column only adds unnecessary overhead? To note is the fact that the table(s) will have millions of rows, but the customer will request data for at at most, 100 or so identifiers.

View 9 Replies View Related

Creating A Primary Key Column In SQL Server 2005

Feb 1, 2008

I have a varchar column in a table, which is an unique key column. Now when I design this table should I use this column as a primary key or should I add one more Integer column as a primary key column for the performance?

One more point is, when ever I do a search on this table I will search based on that unique varchar column values only, so even then if I add new integer column just for Primary key I will not use this column for searching values.

With these information, can some body help me in deciding this?

View 2 Replies View Related

Creating Custom Primary Key In SQL Server 2005

Mar 11, 2008

I currently have a website which is using ASP.NET 2.0, C#, and SQLServer 2005. The website will be used to enter grants for auniversity. When a new grant is entered, I need to generate a primarykey. The primary key will need to follow the format: Two digit forfiscal year, then number of the grant for that year. Example:Year 08 and 14th grant of the year would be: 0814How can I implement this. Right now, I have a "New Grant.aspx" pagewith a Submit button. I am guessing the date is going to be formattedin C#. How can I check what the last primary key in the database is?Also, it seems to me that SQL Server insists that the primary key be32 bits long, however my primary key will only be 4. How can Ioverride this? Thanks.

View 3 Replies View Related

SQL Server 2012 :: Creating Continuous Primary Key Integer Value

Jun 26, 2014

Discuss the following sql query with respect to performance in an applicaiton involving more number of concurrent users creating and deleting records. The objective is to create continuous primary key integer values.

Table name: SitePage

Column DataType
--------- -----------

PageID BigINT
PageName nchar(10)

Query to insert new record

DECLARE @intFlag INT
SET @intFlag = 0
WHILE (@intFlag =0)
BEGIN
BEGIN TRY

[Code] ....

We don't want to use auto increment integer value for primary key because of the following reason

[URL] .....

We also don't want to use SEQUENCE as we have to create 50 sequence for 50 tables

We can't do trace flag 272

View 9 Replies View Related

Creating View Help With Temporary Tables

Mar 11, 2008

Hi All,

In my SQL I am having temporary tables. And in Microsoft SQL Server Management Studio (Microsoft SQL Server 2005) whenever I execute sql statement its working fine & I am getting the records.

My SQL statement is using 2 databases as follows:
1.PerformanceDeficiencyNotice
2.HRDataWarehouse

Both the above databases are SQL SERVER 2000(80) with a compatibility level of 80.

The problem is when I am trying to create a new view with my sql statement and when I am saying “Verify SQL Syntax�, I am getting an error as “Invalid Object Name ‘#pdninfo’.

And when I am saying “execute SQL�, I am getting an error as “Unable to parse query text� but when I am continuing with the error, the sql statement is running and I am getting the data.

And now when I am trying to save the view I am getting the error as below
“Incorrect syntax near the keyword ‘INTO’�.
Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables.

Please suggest how to solve this problem. Any help is greatly appreciated.

Thank You

MY SQL Statement is as follows:


SELECT
pdn.transactionid,
pdn.employeenbr,
pdn.lastname,
pdn.firstname,
pdn.processlevel,
pl.facilityname as processlevelname,
pdn.department,
pdn.jobcode,
pdn.title,
pdn.supemployeenbr,
pdn.managername,
pdn.timeframe as pdn_timeframe,
pdn.actualeffectivedate as pdn_startdate,
/*actualeffectivedate is the start date for the pdn. starteddate is when info starts being put in the system*/
/*the pdn end date has to be calculated for the pdn based on the timeframe and actualeffectivedate*/
case when pdn.actualeffectivedate <> convert(datetime,'01/01/1900',110) then
case pdn.timeframe
when '30' then dateadd(month,1,pdn.actualeffectivedate)
when '60' then dateadd(month,2,pdn.actualeffectivedate)
when '90' then dateadd(month,3,pdn.actualeffectivedate)
else null
end
end as pdn_enddate,
pdn.status as pdn_status,
status.description as pdn_statusdesc,
pdn.managersignoff as pdn_managersignoff,
pdn.managersignoffdate as pdn_managersignoffdate,
pdn.associatesignoff as pdn_associatesignoff,
pdn.associatesignoffdate as pdn_associatesignoffdate,
pdn.witnessname as pdn_witnessname,
/*the start date for the extension has to be calculated by subtracting 30 days from the evaluationdate*/
/*where the evaluationtype = 'X' (Extension Final).*/
/*there is only one timeframe of 30 days for an extension and only one extension is allowed per pdn for an associate*/
case
when (eval.evaluationtype = 'X' and eval.status not in ('C','D','N')) then dateadd(month,-1,eval.evaluationdate)
else null
end as ext_startdate,
eval.evaluationdate as eval_evaluationdate,/*end date of the evaluation or extension*/
eval.evaluationtype as eval_evaluationtype,
evaltype.description as eval_evaltypedesc,
eval.status as eval_status,
status2.description as eval_statusdesc,
eval.effectivedate as eval_effectivedate,
eval.managersignoff as eval_managersignoff,
eval.managersignoffdate as eval_managersignoffdate,
eval.associatesignoff as eval_associatesignoff,
eval.associatesignoffdate as eval_associatesignoffdate,
eval.witnessname as eval_witnessname
into #pdninfo
FROM [PerformanceDeficiencyNotice].[dbo].[PDNMain] pdn
left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationsMain] eval
on pdn.transactionid = eval.transactionid
left outer join [HRDataWarehouse].[dbo].[ProcessLevel] pl
on pdn.processlevel = pl.processlevel
left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status
on pdn.status = status.status and status.type = 'PDN'
left outer join [PerformanceDeficiencyNotice].[dbo].[StatusDescriptions] status2
on eval.status = status2.status and status2.type = 'EVAL'
left outer join [PerformanceDeficiencyNotice].[dbo].[EvaluationTypes] evaltype
on eval.evaluationtype = evaltype.type
/*select active pdns from PDNMain (status: 'A' = Approved, 'S' = Submitted)*/
WHERE pdn.status in ('A','S')
/*select extensions from EvaluationsMain (evaluation type: 'X' = Extension Final; status: <> 'C' - Completed,*/
/*'D' - In Progress, or 'N' - Not started)*/
OR (eval.evaluationtype = 'X' and eval.status not in ('C','D','N'))

/*get last performance rating and last (maximum) performance review date from PerformanceReviewHistory*/
/*Note: A PerformanceReviewHistory record gets created within a couple of days after an associate is hired.*/
/* The rating and updatedate are null initially. Aggregate functions (i.e. MAX) ignore null values.*/
/* You must check for "updatedate IS NOT NULL" as shown below or the record will be dropped.*/
SELECT distinct(#pdninfo.employeenbr), perfreview.rating, perfreview.updatedate
into #perfreview
FROM #pdninfo, [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview
WHERE #pdninfo.employeenbr = perfreview.employeenbr
AND perfreview.updatedate =
(SELECT max(updatedate)
FROM [HRDataWarehouse].[dbo].[PerformanceReviewHistory] perfreview2
WHERE perfreview2.employeenbr = perfreview.employeenbr
AND updatedate IS NOT NULL)

/*select active pdns ('orig' = original)*/
SELECT 'orig' as orig_or_ext,
#pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate,
/*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/
emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi,
(SELECT emp2.lastname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname,
(SELECT emp2.firstname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname,
(SELECT emp2.mi
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi
FROM #pdninfo
left outer join #perfreview
on #pdninfo.employeenbr = #perfreview.employeenbr
left outer join [HRDataWarehouse].[dbo].[Employee] emp
on #pdninfo.employeenbr = emp.employeenbr
WHERE #pdninfo.pdn_status in ('A','S')

union

/*select extensions ('ext' = extension)*/
SELECT
'ext' as orig_or_ext,
#pdninfo.*, #perfreview.rating as lastperfrating, #perfreview.updatedate as lastperfreviewdate,
/*get empstatus, lasthiredate, originalhiredate, gender, race, middle init, supervisor name from Employee*/
emp.empstatus, emp.lasthiredate, emp.originalhiredate, emp.gender, emp.race, emp.mi,
(SELECT emp2.lastname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_lastname,
(SELECT emp2.firstname
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_firstname,
(SELECT emp2.mi
FROM [HRDataWarehouse].[dbo].[Employee] emp2
WHERE #pdninfo.supemployeenbr = emp2.employeenbr) as sup_mi
FROM #pdninfo
left outer join #perfreview
on #pdninfo.employeenbr = #perfreview.employeenbr
left outer join [HRDataWarehouse].[dbo].[Employee] emp
on #pdninfo.employeenbr = emp.employeenbr
WHERE #pdninfo.eval_evaluationtype = 'X' and #pdninfo.eval_status not in ('C','D','N')

drop table #pdninfo
drop table #perfreview

View 5 Replies View Related

SQL Server 2008 :: Violation Of Primary Key Constraints - Cannot Insert A Duplicate Key In Object

Sep 8, 2013

I have table variable in which I am inserting data from sql server database. I have made one of the columns called repaidID a primary key so that a clustered index will be created on the table variable. When I run the stored procedure used to insert the data. I have this error message; Violation of Primary key Constraint. Cannot insert duplicate primary key in object. The value that is causing this error is (128503).

I have queried the repaidid 128503 in the database to see if it is a duplicate but could not find any duplicate. The repaidID is a unique id normally use by my company and does not have duplicates.

View 9 Replies View Related

Creating Inter-table Relationships Using Primary Keys/Foreign Keys Problem

Apr 11, 2006

Hello again,

I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.

Here's the query for my Classes table:

Code:

CREATE TABLE Classes
(
class_id
INT
IDENTITY
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

class_title
VARCHAR(50)
NOT NULL,

class_grade
SMALLINT
NOT NULL
DEFAULT 6,

class_tardies
SMALLINT
NOT NULL
DEFAULT 0,

class_absences
SMALLINT
NOT NULL
DEFAULT 0,

CONSTRAINT Teacher_instructs_ClassFKIndex1 FOREIGN KEY (teacher_id)
REFERENCES Users (user_id)
)

This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:

Code:

CREATE TABLE Classes_have_Grades
(
class_id
INT
PRIMARY KEY
NOT NULL,

teacher_id
INT
NOT NULL,

grade_id
INT
NOT NULL,

CONSTRAINT Grades_for_ClassesFKIndex1 FOREIGN KEY (grade_id)
REFERENCES Grades (grade_id),

CONSTRAINT Classes_have_gradesFKIndex2 FOREIGN KEY (class_id, teacher_id)
REFERENCES Classes (class_id, teacher_id)
)

Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?

In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?

Thank you for your assistance.

View 1 Replies View Related

SQL Server 2008 :: Creating Tables From ERD

Mar 3, 2015

I need to write create table statements for the er diagram that I attached. I am new to sql and I have trouble integrating foreign keys with these bigger er diagrams.

These are the tables I need to create:
Create Table Author(...)
Create Table Writes(...)
Create Table Book(...)
Create Table Copy(...)
Create Table Loan(...)
Create Table Customer(...)

View 1 Replies View Related

SQL Server 2008 :: Creating And Transferring Permissions?

Mar 24, 2015

I have existing domain users with SQL logins and having different set of permissions, we are migrating to new domain, and we have new user ID's with new domain. Is there any way through query I can create an user and copy the permissions.

View 0 Replies View Related

SQL Server 2008 :: Creating New Category Name For Jobs

Jun 27, 2015

I created 2 jobs under sql jobs. How to set up the custom category for my new jobs. It is going under uncategorized but I want to create DBA Monitor category.

View 1 Replies View Related

T-SQL (SS2K8) :: Joining Results Of Two Queries Without Creating Temporary Tables?

Nov 16, 2014

In the T-SQL below, I retrieved data from two queries and I've tried to join them to create a report in SSRS 2008 R2. The SQL runs, but I can't create a report from it. (I also couldn't get this query to run in an Excel file that connects to my SQL Server data base. I've used other T-SQL queries in this Excel file and they run fine.) I think that's because I am creating temporary tables. How do I modify my SQL so that I can get the same result without creating temporary tables?

/*This T-SQL gets the services for the EPN download from WITS*/

-- Select services entered in the last 20 days along with the MPI number and program code.

SELECT DISTINCT dbo.group_session_client.note, dbo.group_session_client.error_note, dbo.group_session_client.group_session_id,
dbo.group_session_client.group_session_client_id, dbo.group_session.signed_note, dbo.group_session.unsigned_note
into #temp_group_sessions
FROM dbo.group_session_client, dbo.group_session
WHERE dbo.group_session_client.group_session_id = dbo.group_session.group_session_id

-- Select group notes

SELECT DISTINCT
dbo.client_ssrs.state_client_number, dbo.delivered_service_detail.program_name, dbo.delivered_service_detail.start_date,
dbo.delivered_service_detail.start_time,
dbo.delivered_service_detail.service_name, dbo.delivered_service_detail.cpt_code, dbo.delivered_service_detail.icd9_code_primary,

[code]....

-- Form an outer join selecting all services with any group notes attached to them.

select * from #temp_services
LEFT OUTER JOIN #temp_group_sessions
on #temp_services.group_session_client_id = #temp_group_sessions.group_session_client_id
;

-- Drop temporary tables

DROP TABLE #temp_group_sessions;
DROP TABLE #temp_services;

View 9 Replies View Related

Transact SQL :: Temporary Table Created In Linked Server

Sep 20, 2015

I'm executing a sp name  [uspGeneral_Getinfo]. It return a global temporary table's name and I want to get data from that table.

[192.168.2.11] = Linked Server

Declare@LinkedServer varchar(40)='[192.168.2.11].DBPharm.dbo.',@OutPutTableName varchar(50)=' ',@SQL nvarchar(max)Set@LinkedServer=Rtrim(Ltrim(@LinkedServer))Set@SQL=' Declare @OutPutTableName varchar(50),@SQL nvarchar(max) EXEC '+@LinkedServer+'[uspGeneral_GetDomainDataNew] 9,null, @OutPutTableName OutPut '+' Set @SQL='''+'Select * from '''+'+ @OutPutTableName'+' Exec sp_executesql @SQL 'Select@SQL

Above code are executing from another server [192.168.2.10].But getting an error that .I can't able to search where the temp table created in linked serve tempdb or ...?

Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Msg 208, Level 16, State 0, Line 38
Invalid object name '##table1FD1B81Bx4EAFx4FFDx9F6Fx15B77B6445F'.

View 8 Replies View Related

SQL Server 2008 :: Creating Multiple Copies Of A Database

Mar 21, 2015

I am creating a program that will take a master database and create separate databases for class room training.creating my own app to do this since it will have other stuff to do.i will have a master database that i will need to create multiple copies of. 2-20 copies, it is about 7GB large. it is used in a classroom training course for our company software. it will also copy a folder on the server onto multiple subfolders.each computer in the classroom will access its own copy of the database/windows folders.

What i am looking for is a fast/reliable way to create the multiple database copies. then when the training class is over and a new one is getting started, we will run my program to reset everything back to start.Should i detach/copy/attach or create a master backup and restore it 20 times. What kind of user access pitfalls will i need to look out for.

View 5 Replies View Related

SQL Server 2008 :: Creating Index Including Non-key Columns

Jul 9, 2015

Does including non-key columns work for the performance of an index?

View 8 Replies View Related

SQL Server 2008 :: Getting Error While Creating Linked Servers

Sep 15, 2015

Below is the syntax I am using for creating Linked server from SQL Server i.e windows 2008 R2 standard to Postresql database running on Linux 32 bit Debian (Linux turtle 3.2.0-4-686-pae #1 SMP Debian 3.2.46-1+deb7u1 i686 GNU/Linux) and the version of Postresql is 8.3

/****** Object: LinkedServer [HGCDEV] Script Date: 09/15/2015 17:03:37 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HGCDEV', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'172.16.20.159',@provstr=N'UID=web;PWD=dev123'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HGCDEV',@useself=N'False',@locallogin=NULL,@rmtuser='web',@rmtpassword='dev123'

This the error I am getting " Cannot initializee the data source object of OLE DB provider "MSDASQL" for linked server "HGCDEV".

How to setup the linked server........... Below are the drivers installed on the SQL server

PostgreSQL35W
PostgreSQL30

View 2 Replies View Related

SQL Server 2008 :: Creating Rows Between Dates In Single Statement

Apr 21, 2015

I am trying to find an easy way to create multiple of just two date in a single sql statement.

E.G.

A statement using the parameters

@StartDate = '2015-01-01'
@EndDate = '2015-01-05'

Ends up with rows:

'2015-01-01'
'2015-01-02'
'2015-01-03'
'2015-01-04'
'2015-01-05'

What would be the best way to do this ?

View 3 Replies View Related

SQL Server 2008 :: Backup Device Creating Backups With A New Transaction Log For Each Day

Jun 19, 2015

Having a lot of problems with backup device creating backups with a new transaction log for each day. This is causing the backups to grow way to fast. Seems to be random with our clients. Created new device backups but getting same problem. A manual backup selecting overwrite all existing backup sets will fix it. But starts the cycle all over again.

View 9 Replies View Related

SQL Server 2008 :: Creating Index - Add Unique Key That Will Cover 3 Fields

Sep 30, 2015

Table Name: Denominator
Already has the following constraint:
PK_Denominatorclustered, unique, primary key located on PRIMARY DenominatorID

How can I add a unique key that will cover the 3 fields --> MemberID,MeasureID,TimePeriodID

I also want to know whether we can include the " WITH ( IGNORE_DUP_KEY=ON ) "

View 3 Replies View Related







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