Solved

Viewing a unigueidentifier key field in MS SQL

Posted on 2009-04-12
17
339 Views
Last Modified: 2012-05-06
I have a table defined as follows in the code window.  I can insert values into the table, but cannot do a select * from the table and see the primary key uniqueidentifier column.

Please advise.

Thanks,
Howard


CREATE TABLE [dbo].[ClientLogin](

	[ClientGuid] [uniqueidentifier] NOT NULL DEFAULT (newid()),

	[FName] [varchar](50) NULL,

	[LName] [varchar](50) NULL,

	[CreateDateTime] [datetime] NOT NULL DEFAULT (getdate()),

	[EditDateTime] [datetime] NOT NULL DEFAULT (getdate()),

 CONSTRAINT [PK_ClientLogin] PRIMARY KEY CLUSTERED 

(

	[ClientGuid] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]
 

GO

SET ANSI_PADDING OFF

Open in new window

0
Comment
Question by:hbash
  • 7
  • 5
  • 3
  • +1
17 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 50 total points
ID: 24126563
can you tell/show us how you actually:
* INSERT into the table
* SELECT from the table
* the results from the select after the insert
0
 
LVL 1

Author Comment

by:hbash
ID: 24127635
(1)
select
      FName,
      LName,
      CreateDateTime,
      EditDateTime
into PHONES
From OLD_PHONES

(2)
  select ClientGuid from Phones <--fails with "Invalid column name 'PhoneNumberGuid"





0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 200 total points
ID: 24127701

In the original question, you have provided script to create table with name "ClientLogin".
But in your post (24127635), you mentioned some other table in the INTO clause (PHONES).
Do you want to extract the records from OLD_PHONES table and insert them into your "ClientLogin" table?
If yes, you can try like this.
-- You can create your table like this.

create TABLE [dbo].[ClientLogin](

	[ClientGuid] int identity,

	[FName] [varchar](50) NULL,

	[LName] [varchar](50) NULL,

	[CreateDateTime] [datetime] NOT NULL DEFAULT (getdate()),

	[EditDateTime] [datetime] NOT NULL DEFAULT (getdate()),

 CONSTRAINT [PK_ClientLogin] PRIMARY KEY CLUSTERED 

(

	[ClientGuid] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF
 

-- Insert the data into your table ClientLogin with the below statement

insert ClientLogin 

select Fname,Lname,CreateDateTime,EditDateTime from Old_Phones
 

-- Now query the table ClientLogin

select ClientGuid from ClientLogin

Open in new window

0
 
LVL 1

Author Comment

by:hbash
ID: 24129417
My mistake,  the table that I should have included the DDL for is the Phones table and the guid is of type "uniqueidentifier"  not  "int" and the select does not show this column.


0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 24131500
So where do we stand?  Has the problem been resolved and if not can you post the query and table structure that is causing you grief?
0
 
LVL 1

Author Comment

by:hbash
ID: 24131861
The problem has not been resolved.  As I stated,  the table with a uniqueidentifier for it's primary key does not show that column on a select * from tablename query.

I have posted the DDL and the query above.  The only differerence is that the table name is Phone not as listed above.

Thanks,
Howard
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24133330
ok, you write:

select
      FName,
      LName,
      CreateDateTime,
      EditDateTime
into PHONES
From OLD_PHONES

and you complain about the table "phones" not do have a ClientGuid column?

please clarify
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24133850

If you have table in hand and want to insert the data then use INSERT INTO TableName SELECT ... FROM OtherTable
If you don't have the table, and want to create and insert data from other table, use SELECT * INTO ... FROM ...
Here, you provided DDL script(ClientLogin) to create the table and, you are trying to insert data into some other table (PHONES).
The PHONES table does not have ClientGuid column, check your SELECT <columnlist> INTO PHONES FROM OLD_PHONES statement.
Please check the below script.
-- Create your table with your DDL script. I have changed the table name from ClientLogin to PHONES 

create TABLE [dbo].[PHONES](

	[ClientGuid] [uniqueidentifier] NOT NULL DEFAULT (newid()),

	[FName] [varchar](50) NULL,

	[LName] [varchar](50) NULL,

	[CreateDateTime] [datetime] NOT NULL DEFAULT (getdate()),

	[EditDateTime] [datetime] NOT NULL DEFAULT (getdate()),

 CONSTRAINT [PK_ClientLogin] PRIMARY KEY CLUSTERED 

(

	[ClientGuid] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

 

GO

SET ANSI_PADDING OFF

 

-- Insert the data into your table PHONES with the below statement. Mention a value for ClientGuid field also.

insert PHONES 

select NEWID(),Fname,Lname,CreateDateTime,EditDateTime from Old_Phones

 

-- Now query the table PHONES

select ClientGuid from PHONES

Open in new window

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24134267
>>The problem has not been resolved.<<
Than I am afraid I have no idea what you are talking about.
0
 
LVL 1

Author Comment

by:hbash
ID: 24134384
Ok.  Allow me to try again.  After I create that table named Phones with a pk uniqueidentifier and several other fields,  my select * from that table shows every column except the PK.

If you wanted to do that how would you?


sqlprob.docx
0
 
LVL 1

Author Comment

by:hbash
ID: 24134395
Here is the DDL for the table too:


USE [somedb]

GO

/****** Object:  Table [dbo].[Phones]    Script Date: 04/13/2009 21:41:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Phones](

	[PhoneNumberGuid] [uniqueidentifier] NOT NULL DEFAULT (newid()),

	[LocationGuid] [bigint] NULL,

	[CompanyGuid] [bigint] NULL,

	[CountryCode] [varchar](10) NULL,

	[AreaPrefixCode] [varchar](12) NULL,

	[DestinationPage] [varchar](50) NULL,

	[PublicFlag] [bit] NULL,

	[PhoneNumber] [nvarchar](50) NULL,

	[PhoneType] [varchar](12) NULL,

	[PhoneFormatName] [varchar](50) NULL,

	[PhoneDefault] [varchar](50) NULL,

	[PhoneURL] [nchar](128) NULL,

	[CreateDateTime] [datetime] NOT NULL DEFAULT (getdate()),

	[EditDateTime] [datetime] NOT NULL DEFAULT (getdate()),

 CONSTRAINT [PK_PhoneReference] PRIMARY KEY CLUSTERED 

(

	[PhoneNumberGuid] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]
 

GO

SET ANSI_PADDING OFF

Open in new window

0
 
LVL 1

Author Comment

by:hbash
ID: 24134416
sorry for the sloppy post.
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24134559

That is strange. Can you run these queries and check whether any record is returned.
USE [somedb]
select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Phones' and COLUMN_NAME = 'PhoneNumberGuid'
select *
  from sys.tables T
  join sys.columns C on T.object_id = C.object_id
 where T.name = 'Phones' and C.name = 'PhoneNumberGuid'

From the screenshot, i come to know that, you are working in Express edition, but as per my knowledge,that will not be a problem.

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 24134596
CREATE TABLE [dbo].[Phones](
      [PhoneNumberGuid] [uniqueidentifier] NOT NULL DEFAULT (newid()),
      [LocationGuid] [bigint] NULL,
      [CompanyGuid] [bigint] NULL,
      [CountryCode] [varchar](10) NULL,
      [AreaPrefixCode] [varchar](12) NULL,
      [DestinationPage] [varchar](50) NULL,
      [PublicFlag] [bit] NULL,
      [PhoneNumber] [nvarchar](50) NULL,
      [PhoneType] [varchar](12) NULL,
      [PhoneFormatName] [varchar](50) NULL,
      [PhoneDefault] [varchar](50) NULL,
      [PhoneURL] [nchar](128) NULL,
      [CreateDateTime] [datetime] NOT NULL DEFAULT (getdate()),
      [EditDateTime] [datetime] NOT NULL DEFAULT (getdate()),
 CONSTRAINT [PK_PhoneReference] PRIMARY KEY CLUSTERED
(
      [PhoneNumberGuid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF

Insert Phones (PhoneNumberGuid) Values (NEWID())
Insert Phones (PhoneNumberGuid) Values (NEWID())
Insert Phones (PhoneNumberGuid) Values (NEWID())
Insert Phones (PhoneNumberGuid) Values (NEWID())

Select PhoneNumberGuid
From Phones

Drop table Phones

Output:
EF737BB5-3466-4B6C-9F09-2B8651BF2270
DC3DA3F3-F222-4E23-918A-B054D9C79DBD
049CF85D-E8A4-45F2-9407-C07697755607
79B4191E-4A1A-4439-ADD5-E520FA33EE2F
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 200 total points
ID: 24134606
acperkins -  even i tested the same, by creating the tabe (with the DDL provided by asker).  
When the asker said, no PK column displayed with SELECT *, wondered whether SQL server behaving differently on different machines :)
0
 
LVL 1

Author Comment

by:hbash
ID: 24134738
how do we identify this setting(s) ?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 24134746
did you run my queries? what is the result?
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now