asked on

Viewing a unigueidentifier key field in MS SQL

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

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Howard Bash

ASKER

(1)
select
      FName,
      LName,
      CreateDateTime,
      EditDateTime
into PHONES
From OLD_PHONES

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

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Howard Bash

ASKER

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.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Howard Bash

ASKER

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

Guy Hengel [angelIII / a3]

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

Sharath S

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

Anthony Perkins

>>The problem has not been resolved.<<
Than I am afraid I have no idea what you are talking about.

Howard Bash

ASKER

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

Howard Bash

ASKER

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

Howard Bash

ASKER

sorry for the sloppy post.

Sharath S

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.

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial