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


Post New Web Links

Getting null when using dm_db_partition_stats with #temp table

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

drop table #TEMP
select * into #TEMP from tblEmp
Select SUM(row_count) from sys.dm_db_partition_stats where object_id = OBJECT_ID('dbo.#TEMP')
AND (index_id = 0 or index_id = 1)



View Complete Post


More Related Resource Links

How to deal with NULL values in a SQL table

  

 Hi all

In order to add/update/delete data from a table I build 2 classes: 1 class containing all the table fields and another class with methods to add/insert/update/delete records. I am learning how to avoid errors when your table must use SQL NULL values. One error is when I call my UPDATE method (see below):

  public void UpdateItem(OcItemConstraintDetails item)
 {
  SqlConnection con = new SqlConnection(connectionString);
  SqlCommand cmd = new SqlCommand("ItemConstraints_Update", con);
  cmd.CommandType = CommandType.StoredProcedure;

  cmd.Parameters.Add(new SqlParameter("@itemConstraintId", SqlDbType.Int, 4));
  cmd.Parameters["@itemConstraintId"].Value = item.ItemConstraintId;

  cmd.Parameters.Add(new SqlParameter("@path", SqlDbType.NVarChar, 150));
  cmd.Parameters["@path"].Value = item.Path;
...

if you watch the SQL profiler trace you will see that I am passing a value of 'default' ;so that causes me problems.

exec ItemConstraints_Update @itemConstraintId=1,@path=default


With that said, what are the best practices when your SQL table accepts NULL values and when:
- you are passing an empty value to your stored procedure;

SELECT statement to return NULL by matching data from another table.

  
Hi,I am fairly new at SQL and I have been struggling for days now trying to find an answer to my problem and i have come to the point where i have run out of ideas and about to give up. I'm hoping someone can put me in the correct path. The problem I have 3 table Table 1 Department" has the following columns: REF, NAME Table 2  "Department_Collection" has the following columns: REF, DEPARTMENT_REF, MANAGER_REF, STORE_REF, ACTIVE Table 3 Store" has the following columns: REF, NAME, STORE_ID  What i am trying to do is to take all the rows in the Department table and get a matching row (DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF) from the Department_Collection table, if it does not match any then still display DEPARTMENT.NAME but mark DEPARTMENT_COLLECTION.REF as null. I have tried the following select statement but it seem to remove all null values when supplied with a 'storename' SELECT DEPARTMENT.NAME, DEPARTMENT_COLLECTION.REF FROM DEPARTMENT_COLLECTION right outer join DEPARTMENT on DEPARTMENT_COLLECTION.DEPARTMENT_REF = DEPARTMENT.REF left outer join STORE on DEPARTMENT_COLLECTION.STORE_REF = STORE.REF where STORE.NAME = 'storename' order by DEPARTMENT.NAME   Any help will be greatly appreciated. Thanks

Invalid object error on temp table on a particular environment

  
Hi, I am using a temp table in my sp. I have written the following tsql before and after using the temp table. IF OBJECT_ID('tempdb..##TEMP') IS NOT NULL DROP TABLE tempdb..##TEMP; Data will be inserted to the temp table consecutively using a cursor. I use SELECT * INTO tempdb..##TEMP FROM EXEC('query') to fill this temp table for the first time and INSERT INTO tempdb..##TEMP(<fields>) EXEC('query') for the remaining times. The SP is working fine in my laptop, but I am getting an Invalid Object error on this temp table name in the production server. Could anyone tell me the possible causes please? Thanks Sree

Oracle ref cursor or temp table?

  
I have to return a large amount of data from a complicated query to an asp.net web page. It is currently doing it by running a procedure to fill an Oracle temp table and pulling all of the data from the temp table for display. I was wondering if using a ref cursor would be faster and more efficient. Does anyone know?

Update producing invalid object name for temp table

  
If IWantThisDSN = 0 Then SQLString = "SELECT importparamsID, ESubmitType, Description, ImportFileName,ClientCode, Notes00, Notes01, Notes02, Notes03, Notes04 into #t0 from Importparams " Else SQLString = "SELECT importparamsID, ESubmitType, Description, ImportFileName, ClientCode, Notes00, Notes01, Notes02, Notes03, Notes04 into #t0 from Importparams " End If cn.Execute(SQLString) SQLString = "UPDATE #t0 Set Description = ' ' WHERE Description IS NULL" cn.Execute(SQLString) I'm getting the error in the last cn.Execute(SQLString) line...   Message=[Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '#t0'. I'm using VB 2010 and SQL Server 2008. This worked fine before with VB 6 and SQL Server 2000 but with the "upgrade" it's not working... If you need any more info let me know what I may have left out.  Thanks!

insert mulitple comma seperated values in a temp table

  
I have a reporting services report with a single parameter that can take up to 10 numbers which are comma delimited. I need to insert those numbers into a temp table with one column. So if the numbers were 1 - 10 I would expect to see something like this if querying the temp table Acct_Num 1 2 3 4 5 6 7 8 9 10 Is there a way to do this insert into the table using a comma delimeted string of numbers? ThanksFJK

if NULL in database table - display empty string?

  

I have a table in an Sql Server Database from which I want to fetch data to a form. 

[Headline] [varchar](max) NULL,
[Text] [varchar](max) NULL,

[ID] [int] IDENTITY(1,1) NOT NULL,

[Headline] [varchar](max) NULL,

[Text] [varchar](max) NULL,

I want to check both Headline and Text to see if they are null, and if so display an empty string each for them. I've looked at some tutorials but don't yet understand really how to use it. DBNull? DataRow.IsNull? Something else? Can I check both Headline and Text at the same time or do I need do check them separately?


Executing sql statment stored in temp table (without variables)

  

How could I execute a sql statement stored in a temp table? something like

 

select

 

'select * from sysobjects' as test into #tmp

exec

 

(sel

Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


Performance Drop when switching from a #temp table to a @temp table variable

  

I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted to a @temp table variable.

Here are some steps I have already taken...

The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."

Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.

Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!

The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.

The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).

Does anyone have any suggestions (or hope) for me?

Thanks,
Jim


create a temp table using a name passed as a param

  

I am trying to move VB script to sql server and I am not sure how to do this one:

create proc CreateTable(tempTable as varchar(50)
)
AS

SET @TempTble = 'tempdb..' + @tempTble

IF object_id(@TempTble ) IS NULL
 BEGIN
  CREATE TABLE @tempTble
  (
   id INT, fldName NVARCHAR(255), Balance FLOAT, 
   SName NVARCHAR(100), eventDate Datetime
  )
 END


How to get Default value in table to work when NULL in DataSet

  
Is this possible? If the parameter value is NULL in DataSet, the default value is not assigned.

Inserting null value into foreign key table with allow null enabled

  

Hey guys, 

I have 2 table Table A, Table B.

Table A - Parent Table

ID - Uniqueidentifier not null(PK)

Table B -Child Table              

ID - uniqueidentifier null(FK, TableA)


I have a stored procedure to insert data into Table B, but when I tried to insert a null value into column ID of table B, this error came up:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tableB_tableA". The conflict occurred in database "database name", table "table A", column 'ID'.

The statement has been terminated.

The insert statement in asp.net is like so:

sqlcommand.parameter.add(New Sqlparameter("ID", Nothing))


I am guessing adding nothing to the null value column is the problem, but i don't know how to fix it... can any1 help me?


Zeph

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tableB_tableA". The co

C# How to check prammatically if null value exists in database table (using stored procedure)?

  
Folks!

How to programattically check if null value exists in database table (using stored procedure)?

I know it's possble in the Query Analyzer (see last SQL query batch statements)?

But how can I pass null value as parameter to the database stored procedure programattically using C#?

Although I can check for empty column (the following code passes string.Empty as parameter but how to pass null value?), I cannot check for null value in the following code snippet:

SQL Queries:

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
DROP DATABASE [ExampleDatabase];
GO

CREATE DATABASE [ExampleDatabase];
GO

USE [ExampleDatabase];
GO

IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable')
DROP TABLE dbo.ExampleTable;
GO

CREATE TABLE
dbo.ExampleTable
(
ID INT IDENTITY(1,1) NOT NULL,
UserID INT NULL,
Name NVARCHAR(50) NULL,
DateOfBirth DATETIME,
IsActive BIT,
Phone NVARCHAR(50) NULL,
Fax NVARCHAR(50) NULL,
CONSTRAINT PK_ID PRIMARY KEY(ID),
CONSTRAINT UNIQUE_Phone UNIQUE(Phone),
CONSTRAINT FK_UserID FOREIGN KEY(UserID) REFERENCES ExampleTable(ID),
CONSTRAINT FK_Fax FOREIGN KEY(Fax) REFERENCES ExampleTable(Phone)
);
GO

INSERT INT

Problem inserting a null value into date column in SQL table

  

Hi all,

I am having a hard time inserting a null date into a SQL table.  Please be aware that I am using the Microsoft Application Data Blocks and not pure ADO.net.  I find a million examples of how to do this with ADO.net but cannot find anything about this using the MS Data Application Blocks.

Here is my VB.net code:

Save(Session("ref_no"),  txtdischarge_date.Text) 

Sub Save(ByVal ref_no As Integer, discharge_date As Date)

        SqlHelper.ExecuteNonQuery(connString, ref_no, discharge_date), "MyTableInsert")

End Sub

The textbox txtdischarge_date can either be a valid date or blank.  If it contains a valid date then the insert works properly.  But if the textbox  is blank, then I want to insert a null value into my date column.  I cannot get this to work.  I get this error when I run this and txtdischarge_date is blank:

Conversion from string "" to type 'Date' is not valid

Here is the code for my stored procedure, MyTableInsert:

ALTER Procedure MyTableInsert
   (@ref_no int,
    @discharge_date datetime = NULL)

How do display null rows returned from my table valued function?

  
DECLARE @techNumLoop VARCHAR(25)
	 
DECLARE @myTemp TABLE
(
 Tech Varchar(25) 
,Item_Count Integer NULL 
,Expenses MONEY NULL
--,...
)

DECLARE techCursor CURSOR 
FOR 
SELECT DISTINCT t.Case_Number Tech
FROM tblTechnology t
OPEN techCursor;
FETCH NEXT FROM techCursor INTO @techNumLoop;
WHILE @@FETCH_STATUS = 0 
BEGIN
INSERT @myTemp
SELECT
 @techNumLoop
,Item_Count,0
,Expenses 
FROM ufn_Find_Some_Stuff(@techNumLoop)

FETC

Breaking long query into small one using SQLDataSource (using Temp table)

  

Currently, I have long script which does major processing and then puts data into temp table.

Finally I read data from this temp table to show on the chart, based on user's selection of parameter. So I can divide my query into two parts and while I am researching this, I will appreciate any pointers/guidance.

 

Is it possible to do processing in one Datasource and read table from second datasource?

Is it even possible?

If not, what is recommended method for this (breaking large processing into small one)?


Categories: 
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