.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Select Primary Key Column of Table returns 0 rows

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server

I have three tables: Design, Box, Wall.

Design has a column DesignId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false for compatibility with Entity Framework v4.

Box has  a column BoxId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false as well.

Box also has a column DesignId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Wall has  a column WallId that is a GUID. It does not allow nulls, is unique, and is the primary key. The identity is set to false as well.

Wall also has a column DesignId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Wall also has a column BoxId that is a GUID. It does not allow nulls, is not unique and is not the primary key.

Box.DesignId has been set as a ForeignKey relating to Design.DesignId.

Wall.DesignId has been set as a ForeignKey relating to Design.DesignId

Wall.BoxId has been set as a ForeignKey relating to Box.BoxId.


If I execute      SELECT DesignId FROM Design     I see the correct number of rows.

If I execute     SELECT BoxId From Box        I see zer

View Complete Post

More Related Resource Links

can alias in select be used for selecting other column in that table?

Hi All,I want to use an alias name in a select clause to select other column in that table? select   top 1 (   case when CreatedByName <> '' then 'yy'         else 'xx' end) as filName, (filName + 'xx')from Order       But it throws error like " Invalid column name 'fileName'."Could you please help me out?

How to get column value difference of rows in same table

Dear all, I need a TSQL statement to find the difference of values of two rows in the same table, by taking into consideration some conditions. I have the following rowss in a table named table1 dbname  sqlinst     size1   ddate sqldb1    inst1        200    1/1/2009 sqldb1    inst1        250    1/1/2010 sqldb1    inst1        170    1/1/2008 sqldb2    inst2        300    1/1/2009 sqldb2    inst2        340    1/1/2010 I need to find the difference between size1 values, for columns where their dbname and sqlinst are the same. I also need to define in TSQL that the ddate of row from where I subtract (size1) from is 1/1/2010 and that the ddate of the row I subtract (size1) is 1/1/2009 (e.g. in the above example: for sqldb1 inst1, I need to perform 250-200 and ignore 170, and for sqldb2 inst2  340-300). Please let me know if you have a solution for this. A million thanks!

Searching a NVarChar Column with multiple wildcards returns all rows

I'm building a stored procedure to return a set of records, yes nothing big. The column is an NVarchar column and I'm using a select statement of Select * from Table1 where Column1 Like @Column1 Table is currently one record containing the words:  ***Some Test Word***  Is my test word I've set the value of @Column1 to: Some - no records returned Some% - no records returned %Some% - 1 record returned  % - 1 record returned *% - no records returned %*% - 1 record returned %Not Here% - 1 record returned   Can someone tell me why if I have a leading and trailing wildcard I will get all records returned?  Does it have something to do with the '*' characters in the field because some of my users are using these characters. I've also tried changing the select to: where RTRIM(Column1) Like @Column1 with the same result and where RTRIM(Column1) Like N'%' + @Column1 with the same result   What way should I give a user the way to search for a substring inside of an NVarchar field? Oh, I tried using the Substring function and got the same result. Thanks Mike    

Caml returns table with column, whose value is empty string always, but it's not

I wrote caml query   SPSiteDataQuery query = new SPSiteDataQuery(); query.ViewFields = @"<FieldRef Name='Title' /><FieldRef Name='cmAssignedTo' Nullable='TRUE' />" + "<FieldRef Name='cmDueDate' Nullable='TRUE' /><FieldRef Name='Author' /><FieldRef Name='cmStatus' />" + "<FieldRef Name='cmControler' Nullable='TRUE' /><FieldRef Name='cmControlers' Nullable='TRUE' />" + "<FieldRef Name='FileDirRef' /><FieldRef Name='File_x0020_Type' /><FieldRef Name='cmIcon' /><FieldRef Name='cmPackageGuid' />" ; query.Webs = string .Format("<Webs Scope='{0}' />" , searchScope); query.Lists = "<Lists ServerTemplate='10553'/>" ; Fields cmAssignedTo, cmControler and cmControles return always "", but they include users! Definition of these fields is similar:   <Field xmlns="http://schemas.microsoft.com/sharepoint/" DisplayName="cmAssignedTo" StaticName="cmAssignedTo" Name="cmAssignedTo" ID="{B4B52176-1961-4b06-B7B4-C7DBB751A53F}" Type="User" List="UserInfo" ShowField="ImnName" Mult="TRUE" />   List, where i'm looking for in, includes 2 contenttypes. First one contains these

Delete objects from table using another table to select rows - help needed

I have one table named 'dbo.ac_Products'Within this table there is a column named 'ProductId'There is also another table named 'dbo.ac_CatalogNodes'Within this table there is a column named 'CatalogNodeId'and a column named 'CatalogNodeType'I need to delete all the rows from 'dbo.ac_Products' and 'dbo.ac_CatalogNodes' where 'ProductId' = 'CatalogNodeId' and 'CatalogNodeType' = 1Any help would be greatly appreciated!

Infopath 2007 Repeating Table - Multiple Value Column Text - Hiding Rows based on Column text values

Infopath 2007 browser based form Full Trust Example: I have a repeating table (FruitChoice) that has multiple columns. Both drop down list point to sharepoint list data sources. Choose your tree ft. drop down list – 6Ft Choose your Department drop down list - 103 This repeating table is conditional on the drop down values. This works great. Trees     Fruit       Cost   Date Ordered    Date Delivery Department 6Ft        Peaches                                                        103 3Ft        Apples                                                          102 3Ft        Peaches         &

select only 3 digits from the database table column ?

hi all i want to select data from database like that i have a column name ID , i want to select only first three digits from that column ? how can i do it ?

Its posible to do a select without the table and the column names into a stored procedure?



I need your help. Imagine that I have two tables:

  1. ALUMN wich has three columns: IdAlumn, Name and Address.
  2. SPORT wich has columns: IdSport, Name, Players and Description.

Supose that my tables could be differnced by a number 1 is for ALUMN and 2 is for SPORT.

And the columns of each table too. So 1 is for IdAlumn, 2 is for Name and 3 is for Address. 1 is for IdSport, 2 is for Name 3 is for Players and 4 is for Description.

For example the combination of table 2 and column 3 is SPORT-Players.

Then I want to do an stored procedure that receives two parameters (the table, the column) @tab and&

Conditional insert: If select return rows, insert rows to table otherwise insert specific row indica

This is what I have

Insert into ReportDetail( Partcipantid, Reportid)  

select distinct ParticipantID , 9 from OpenCredit

      except select ParticipantID, 9 from StoreCredit where Closed = 0

 Issue is that when above select statement returns no row, it seems like no record is

Using ntext column i a table with many many rows?


I want to know more about implications of adding a ntext column to an already existing table with 10000+ rows. My estimate is that only 1 out of 100 rows will haveanything stored in the ntext field. Does this mean that space required is counted for every row this table contains? Not only for those rows that have data in this field?

Shall I instead constuct a sub table storing the ntext column with a relation id pointer to the specific row in the mother tale to save space?


What is the best way to get the primary key column name of a table in a stored procedure ?


I'm writing a stored procedure which manipulates a table whose name must be given as a parameter. The stored procedure needs the primary key column name (by design the primary key must have only one column), and I thought I could get this from the system table instead of having to provide it as a parameter. So I need to put the primary key column name in a variable.

The best way I found so far is this :

declare @keyname nvarchar(1024)
table #keys (table_qualifier nvarchar(255),table_owner nvarchar(255),table_name nvarchar

Select second query if first query returns no rows



I need to return result of the second select statement if the first select statement returns nothing:

-- First statement
SELECT Salesmen.SalesmanID, Salesmen.FullName, 
  Salesmen.AssignedAppointments, Salesmen.Picture, 
FROM Appointments INNER JOIN
  Salesmen ON 
  Appointments.SalesmanID = Salesmen.SalesmanID
WHERE (Appointments.Closed = 1)
ORDER BY Salesmen.AssignedAppointments
-- Second statement
FROM Salesmen
ORDER BY AssignedAppointments

SSIS - Derived Column Error Rows will not redirect to table


I am new to SSIS and I have created a package that generally successfully takes a Flat File Source, runs it through a Derived Column transformation and then pushes the data into a SQL Server Database.

I have a Derived Column Error Output set up, but it is not working.  If the data has an error the entire package fails, not just the specific row.  I have tested the package to load successfully and then manually gone into the flat file and changed the data to purposely create an error in one row. Having cleared the load table I rerun the package and it fails entirely rather than redirecting the one bad row.

In the Derived Column Transformation Editor I set the Error and Truncation values to Redirect Row for all the columns.

In SQL Destination Editor for the bad rows I am only mapping the ErrorCode abnd ErrorColumn values to the destination table fields of the same name.  Could this be the problem? Should I map all the fields?  I would like to map all the source (derived) fields into a single text field in the destination table if possible.

The Advanced settings have only the Table Lock and Check constraints boxes checked.  Timeout is 30.

I am not really sure how to set the Advanced Editor properties.  I have left them at the default values.  Any suggestions here?



Hidding table rows if one column has no value


I have a form with a repeating table, which is pulling data from a SharePoint list.  The table has 5 columns, the first 4 columns are read only, populated from the SharePoint list, the last column is a text input field.  People input a number into the text field for some of the rows.  Is there a way to hide all the rows that do not have a number in the last column and only show the rows that have a number in the last column?

How to select all columns except one column from a table ?



I can't figure out how to do this and hope you guys can give me a hint....

I want to select all columns from a table except one column:

For example,

A table has 20 columns : {1,2,3.....20}

To select all of columns in a table, i could use
select * from table;

However, I want to select only column {1....19}

I do not want to type the column names in one by one in the SQL query,

is there a way to achieve this in a short form of Select?


the column in table tbl_table1 do not match an existing primary key or UNIQUE constaint


ID primarykey (autoinc)
UserId uniqueidentifier
IdNumber primarykey int

 I'd like to have a relationship between two tables.

Where is the problem?


Saving primary key constraint when creating new table using SELECT INTO statement - SQL Server 2008


Using SQL Server 2008:

We have a main database - MAIN.   For monthly data extracts, I create new tables with relevant data in another database called EXTRACT.

I use SELECT INTO statements to create the tables in the EXTRACT schema.  How do I preserve the primary key constraints in the EXTRACT tables?  Do I need to write separate queries to set them?


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend