Solved

Viewing a unigueidentifier key field in MS SQL

Posted on 2009-04-12
17
361 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:Howard Bash
  • 7
  • 5
  • 3
  • +1
17 Comments
 
LVL 143

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:Howard Bash
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 1

Author Comment

by:Howard Bash
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:Howard Bash
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 143

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
 
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:Howard Bash
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:Howard Bash
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:Howard Bash
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:Howard Bash
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 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