Simple Question: ON PRIMARY?
Jul 23, 2004
Hi,
Im really new to MS SQL - actually I just have a schema that I need to convert to Postgresql format. The MS SQL database already exists - I need to use a Postgresql web database to hold part of the master database's information.
So, I've converted most of the "CREATE TABLE" schema, but right at the end it says:
Code:
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I have NO idea what the ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] bit means. Can someone please explain to me? And what part this would play in my Postgresql conversion?
Ive been reading up a lot and find that the ON [PRIMARY] bit is pretty much standard with all tables, and TEXTIMAGE_ON [PRIMARY] is used a lot as well. But I'd just like to find out the meaning.
So far I'm presuming I'd just have to delete that line since it doesn't apply to Postgresql. Am I right?
Also, there was no primary key in the schema.. how does MS SQL identify the primary key for a table? I thought that every table needs at least one primary key ?
Thanks a lot in advance!
View 1 Replies
ADVERTISEMENT
Jan 11, 2007
Uma writes "Hi Dear,
I have A Table , Which Primary key consists of 6 columns.
total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.
may i convert Composite Primary key into simple primary key in thr table like this.
Thanks,
Uma"
View 1 Replies
View Related
Sep 12, 2006
Ive added a primary key called ID to my table, now my insert stored procedure dont no longer work.
i want an unique identifier for each row.
heres my stored procedure:
CREATE PROCEDURE composeMessage
-- Add the parameters for the stored procedure here
@username varchar(24),
@sender varchar(24),
@date dateTime,
@subject varchar(255),
@message varchar(2500)
AS
BEGIN
insert into Messages(
"Username",
"Sender",
"Date",
"Subject",
"Message"
)
values (
@username,
@sender,
@date,
@subject,
@message
)
END
GO
heres my sqlcreate table:
USE [Messenger]
GO
/****** Object: Table [dbo].[Messages] Script Date: 09/12/2006 15:13:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Messages](
[Username] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,
[Sender] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,
[Subject] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[Message] [varchar](2500) COLLATE Latin1_General_CI_AS NOT NULL,
[Date] [datetime] NOT NULL,
[ID] [int] NOT NULL,
CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
As primary keycan't be null, what do i put for primary key for my insert to work?
hope you understand what i mean?
View 3 Replies
View Related
Dec 15, 2014
What is the simple way to remember Primary and foreign key?
Why is the ID column in a table declared as a integer datatype?
View 2 Replies
View Related
Apr 25, 2008
We have the following scenario,
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
Thanks Much
Tejinder
View 6 Replies
View Related
May 26, 2004
Hey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
View 1 Replies
View Related
Aug 28, 2002
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
View 9 Replies
View Related
Aug 13, 2007
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
View 7 Replies
View Related
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
Feb 21, 2007
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
Any help is appreciated.
View 7 Replies
View Related
Aug 19, 2006
Hi,
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
I have:
Master table Hotel
table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)
table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
any clue?
thanks
View 5 Replies
View Related
Jan 28, 2004
Hi all
I have the following table
CREATE TABLE [dbo].[property_instance] (
[property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
[application_id] [int] NOT NULL ,
[owner_id] [nvarchar] (100) NOT NULL ,
[property_id] [int] NOT NULL ,
[owner_type_id] [int] NOT NULL ,
[property_value] [ntext] NOT NULL ,
[date_created] [datetime] NOT NULL ,
[date_modified] [datetime] NULL
)
I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id
In this specific instance
- property_instance_id will never be a foreign key into another table
- queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
- Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified
I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.
What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?
Thanks Matt
View 5 Replies
View Related
Mar 24, 2007
...when I started this endeavor.
I have a previously developed Lotus Notes App. The idea was simple; as
I am sefl taught on Lotus Script, I figured I'd be able to stumble my
way through VB.Well,
it started OK. I used VB Express to get familiar with the stuff, but
decided to go with a full version of VS 2005 and try and get this thing
properly developed as a web app. I purchased several reference books
etc., and have become relatively familiar with the forums here.First
issue I have is that I simply want to use code to update or add records
to an SQL DB. I know about datagridview etc., but I want to update the
DB using forms, not the tabular view those controls provide. I thought
it would be relatively straight forward, but found my ignorance runs
deeper than I thought. When I tried to do so I am finding I am not
really clear on where to make declarations etc. in the web app. If anyone could point me in the right direction that would be great. The
issue with searching these forums is most posts deal with datagridview
or something similar.I have spent a ton of time trying to find relevant
posts or articles, but have had no luck yet.Again, all Ireally
need is a nudge in the right direction. I am more than willing to plod
through reference materials or articles/posts to find what I need to
know, I just can't find where to even start on the info I need. Regards, Joe
View 3 Replies
View Related
Feb 24, 2008
Hi
I have a problem with my sql WHERE query, if i manually type ([Area] = 'The First Area') then it is okay but if i try and pass the variable 'The First Area' using the
([Area] = @Area) it doesnt work.
ALTER PROCEDURE dbo.StoredProcedure1(@oby nvarchar,@Area char,@Startrow INT,@Maxrow INT, @Minp INT,@Maxp INT,@Bed INT)ASSELECT * FROM(SELECT row_number() OVER (ORDER BY @oby DESC) AS rownum,Ref,Price,Area,Town,BedFROM [Houses] WHERE ([Price] >= @Minp) AND ([Price] <= @Maxp) AND ([Bed] >= @Bed) AND ([Area] = @Area)) AS AWHERE A.rownum BETWEEN (@Startrow) AND (@Startrow + @Maxrow) Please Help I know it must be something simple as the sql works but not when i pass the variable.... Thanks In Advance
View 2 Replies
View Related
Dec 30, 2004
How can i get an output like this from this sql??
HEM_PATIENT_ID is primary key...
Output:
First row: initial values of the fields
Second row: average of the same fields
Please help me...
select * from (
select HEM_LOKOSIT, HEM_NNS
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 33
union
select AVG(HEM_LOKOSIT), AVG(HEM_NNS)
from LPMS.HEMOGRAMS
where HEM_PATIENT_ID = 33)
order by HEM_LOKOSIT desc nulls last;
View 1 Replies
View Related
May 20, 2001
Am new to SQL Server , can any1 tell me is there anyway to display date in this format month and year like Apr 2000 ,excluding the day.
eg: 01/28/2000 should b displayed like Jan 2000
View 5 Replies
View Related
Jan 25, 2001
Hi,
I'm relatively new to SQL7 but I did use 6.5 a fair bit.
I'm trying to test the restore of the Transaction log backup and having a bit of difficulty. The idea is that I make a complete database backup at 1am backup the transaction log every 30 minutes between 7am and 7pm. I need to be able to restore the database to a known state between 7am and 7pm with a max data loss of 30 minutes.
What I am trying to achieve is (as a test):
1)Create a small test database with a test table
2)Add some data to the test table
3)Back up the transaction log
4)Restore the transaction log to 'undo' the data added in step 2
Should be simple I think !!! The problem I am encountering is that in step 4 it won't let me restore only the transaction log (a tick automatically appears in the database backup as well). Bah !
Can someone please tell me what simple steps are required to get this to work. I need specifically on what options to chose during the backup and restore processes.
Thankyou,
Tim
View 4 Replies
View Related
Nov 8, 1999
Given one table with one column and two rows, one containing the string 'Bill', one containing the string 'Gates'.
Write a select statement which gives you the result 'Bill Gates'.
???+
View 1 Replies
View Related
Apr 2, 2003
Hi All,
I have a table with 2 columns which looks like the following.
IDText
-------------
1AAA
1BBB
1CCC
2DDD
2EEE
2FFF
3GGG
3HHH
3III
Each ID can have multiple texts associated with it. I want to write a query that gives me the following output.
IDText
-------------
1AAA; BBB; CCC
2DDD; EEE; FFF
3GGG; HHH; III
I appreciate your help
Thanks
View 1 Replies
View Related
Apr 2, 2003
Hi All,
I have a table with 2 columns which looks like the following.
IDText
-------------
1AAA
1BBB
1CCC
2DDD
2EEE
2FFF
3GGG
3HHH
3III
Each ID can have multiple texts associated with it. I want to write a query that gives me the following output.
IDText
-------------
1AAA; BBB; CCC
2DDD; EEE; FFF
3GGG; HHH; III
I appreciate your help
Thanks
View 1 Replies
View Related
Jan 11, 2007
Hi,
I have a table with two columns. I need to find distinct value of col1 and the correspondin repeated value of col2 for that col1 value with comma seperated list. Is there any function
for this in MS SQL?
I need somethgn like
a 1,2,3
b 4,5
c 7
d 5,55,5
I can do that with creating 2 cursors but looking for some easy way around.
Any suggestion and help highly appretiate.
Thanks
View 2 Replies
View Related
Jan 20, 2006
incorrect syntax near #
how do i fix this and did i make any other errors?
SELECT Master.CheckNum, Master.CheckDate, Master.ExpenseType, Deal.Alias, Detail.InvoiceDate, Detail.InvoiceAmount, Detail.Person, Detail.Deal, Detail.InvoiceNum, Detail.Reference, Detail.idDetial
FROM Master INNER JOIN (Deal INNER JOIN Detail ON Deal.Deal = Detail.Deal) ON Master.CheckNum = Detail.CheckNum
WHERE (((Master.CheckDate)>#12/5/2005#) AND ((Deal.Alias)="aic"));
View 3 Replies
View Related
Apr 15, 2008
Originally i had:
DELETE FROM #RptDetails WHERE StructureType <> @StructureType
AND #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null
But it didnt delete the structure types i changed to :
DELETE FROM #RptDetails WHERE StructureType <> @StructureType --AND
DELETE FROM #RptDetails WHERE #RptDetails.TraderId <> @TraderId OR #RptDetails.TraderId is null
and it did, how do i format the 2nd sql into 1 statement and what was i doing wrong?
View 1 Replies
View Related
Apr 5, 2006
Hi,
How do I sum all of the returned values into my output param ? This returns multiple rows, all data is oftype decimal.
Thanks
Bob
ALTER proc
spPSICalcA9
@iReturn int output,
@Contract varchar (8)
as
Select
sd.HoursLostRain,
sd.HoursLostMaxT,
sd.HoursLostMinT,
sd.HoursLostFrost,
sd.HoursLostWind,
sd.HoursLostVis
from
SiteDiary sd
where
sd.Contract = @Contract
"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com
View 5 Replies
View Related
Apr 5, 2006
Hi,
I need to return all records where ..
Contract = @Contract
AND
CrossReference is null or ""
I have this but I dont think its right..
Bob
where
a.Contract = @Contract AND a.crossreference is null OR a.crossreference = ""
"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com
View 8 Replies
View Related
Mar 7, 2007
I think it's simple, but I can't get it to work.In English its: find records in TableA where the field [Field1] hasmore than one unique value in Field2sample records in TableAField1 Field22241 123452241 123452242 123452242 99856desired return (2 records)2242 123452242 99856thank you for your helpPaul
View 3 Replies
View Related
Jul 20, 2005
I have a form that after being filled out has its contents written to adatabase and then goes to confirmation .asp page. I want the confirmationpage to be personalized but I am not sure how to pass the "name" parameterto the confirmation.asp page. I know I am going to kick myself whrn I seethis.Thanks, Houston
View 7 Replies
View Related
Feb 11, 2008
Greetings,
I'm trying to find out if a particular column in a table has more than 10 digits. for example: the TN column.
How can I write this query? The data type for this column is bigint. Also, would it be better to change this data type to char(10) instead as it's used primarily for phone # lookups? Thanks in advance!
ID TN
1 7132483234
View 3 Replies
View Related
Aug 31, 2006
Hello all,I'm taking over a project from another developer and i've run into a bit of a problem. This developer had a bad habit of not using primary keys when designing various databases used by his programs. So now i've got approx 1000 tables all of which do not have primary keys assigned. Does anyone know of a tsql script that i can run that will loop through each table and add a primary key field?Thanks in advance?Richard M.
View 2 Replies
View Related
Aug 16, 2007
I have a Department Table.
Can any one tell me its Primary Key.
I have the order
AutoNumber, D + AutoNumber, Code,
Can you help me regarding this.
Because some people never like to use AutoNumber.
That's why I am confused.
View 3 Replies
View Related
Nov 8, 2007
Hi..
I'm going to build database of university, but I have problem with primaru key,
This is the situation:
there are many faculities and each one has many departments,
each department has many courses,
each course has many sections..
The problem:
I want to make those fields in the same table and make the primary key generate from other fields,
(i.e)
I want the faculity be integer from 4 digit "Example the first faculity start with 1000 the second 2000 and so on" and the the department of each faculity will generate its value from the faculity number+interger number from 3digit "Example the department of the first faculity start with 1100 and the second on will be 1200 and so on "
the same thing will repeate for courses and sections so the sectionsID will be the primary key.
Do you know hoew this idea can be implement by SQL server 2005?
Please help me as soon as possible.
View 13 Replies
View Related
Mar 23, 2005
A column will be Primary Key. Others are B and C. I want A will contain B and C. I mean B data is X, C data is Y, A will be XY. How can i do this? Can i set in MSSQL or need ASP.NET?
View 1 Replies
View Related