We have a table with 10 columns each column is of datatype int and can accept nulls, when I do a save is it better to have 0 inserted in to the column or just insert null?
View 9 RepliesWhen we use Partition switch and load the data to a table, can we refresh the indexes for specific partition, so that we don't need to rebuild / refresh for complete is this possible ?
View 1 Replies View RelatedI'd like to create a table that will store different order items. Several order items make up one single order. Order items can have 0 or more children (max depth will never be deeper than one). Order items can have up to 150 attributes/values. The way I think this should be done is using XML column instead of the EAV type of model. My table structure currently looks like this:
* child_order_item_id (PK)
* parent_order_item_id (FK to child_order_item_id)
* order_id (FK to Order table)
* product_id (FK to Product table)
* price
* attribute_XML
How my attribute_XML should look like or how to validate the xml.
We are storing changed data of tables into XML format for auditing purpose. The functionality is already achieved. We are using FOR XML Path clause to convert relational data of tables into XML format.
Now, a table is having column name with '(' . For example name of the column is, ColumnName(). In this case we can not convert into XML using For XML clause. Showing error as,
Column name 'columnName()' contains an invalid XML identifier as required by FOR XML; '(' (0x0028) is the first character at fault.
I have created NONCLUSTERED index on table but my report is taking more time that's why i created columnstore NONCLUSTERED index on the same table but i have one query, if any table have row and column level index(same columns in index) . Which index query will consider.
View 1 Replies View RelatedI just joined a bank related IT department where the existing IT team is already working on creating a new application since a year now and designed database on SQL Server 2012, the amount (currency related) column's datatype found different in tables some where it is decimal and somewhere found different. I want to suggest them Money datatype to choose where we are dealing with currency related columns. My question is, is that correct to choose Money datatype or should I ignore this?
View 3 Replies View RelatedWe have a typical issue with Column Store Index, we have a procedure which does 2 activities - Switch & Reverse Switch
Switch:
1. Fetch the Partitions needed to be switched
2. Switch the data from Main Table to Switch table
2. Disable the Column store on Switch table
SSIS Package:
3. Load data to Switch (Insert / Update)
Reverse Switch:
4. Enable the Switch
5. Switch back the data from Switch table to Main table
Issue: Some time the Column store is not getting disabled, and the package fails complaining try disabling the Column store index and try loading data.
If we re-run the procedure, the column store gets disabled.
Does null column take storage in table ? I created the following table:
drop table [dbo].[TBL_orig]
CREATE TABLE [dbo].[TBL_orig](
[1] [bigint] NOT NULL,
[2] [bigint] NULL,
[3] [bigint] NULL,
[4] [bigint] NULL,
[5] [float] NULL,
[Code] ....
Does null value will take a place even no data insert to this columns and if yes this answer is 4 mb is logical gap ?
How do I add column names as Total and SubTotal for NULL values.
SELECT DISTINCT
--[Group]
[Month]
,[Market]
,[Environment]
,[type]
, COUNT(*)
[code]....
I have a problem I 'get stuck on it. i hope someone can help me.
I have a SQLServer table with an ID-field with a primary key and identity on it, and a couple of other fields with defaultvalue "" and NOT nullable
When i like to update an record in an formview of ASP 2.0 and fill in all fields everything is updated and everything goes fine
but
when I leave one field blank then I get the message 'can not enter null value in column X'
even when I place in the Updateparameter in my code
<asp:Parameter Name="Name" DefaultValue="" /> ASP tries to update an Null value
when i place an space as default value like
<asp:Parameter Name="Name" DefaultValue=" " />
everything goes fine exept there is an space in my database.
How can I update my record, and leave some textboxes emtpy AND where my fields are NOT nullable
I'm getting desparate of this.
make my fields nullable is not an option because this is a 'customerswish'
Please help
i am using csFindProperty store procedure in my application. here there are several input parameter are there. my procedure structure are:
ALTER PROCEDURE [dbo].[csFindProperty]
@HouseNumber varchar(10),
@Street varchar(100),
@City varchar(100),
@State varchar(2),
@Zip varchar(15),
@County varchar(25),
@SquareFootageStart int,
@SquareFootageEnd int,
@Bedrooms int,
@Bathrooms decimal(18, 2),
@Garage decimal(18, 0),
@Basement bit,
@StyleID int,
@Subdivision varchar(50),
@SchoolDistrict varchar(50),
@MonthlyRentStart money,
@MonthlyRentEnd money,
@ListPriceStart money,
@ListPriceEnd money,
@PetsAllowed bit
AS
select distinct hd.housenumber,hd.street,hd.city,hd.state,hd.zip, rd.monthlyrent,sd.listdate, sd.listprice from housedata hd, rentdata rd,
saledata sd where hd.HouseNumber like @HouseNumber and hd.Street like @Street and hd.City like @City and hd.State like @State
and hd.Zip like @Zip and hd.County like @County and hd.SquareFootage >= @SquareFootageStart and hd.SquareFootage <= @SquareFootageEnd
and hd.Bedrooms like @Bedrooms and hd.Bathrooms like @Bathrooms and hd.Garage like @Garage and hd.Basement like @Basement
and hd.StyleID like @StyleID and hd.Subdivision like @Subdivision and hd.SchoolDistrict like @SchoolDistrict and rd.MonthlyRent >=@MonthlyRentStart
and rd.MonthlyRent <= @MonthlyRentEnd and sd.ListPrice >= @ListPriceStart and sd.ListPrice <= @ListPriceEnd and rd.PetsAllowed like @PetsAllowed
and hd.HouseDataID=sd.HouseDataID
Execute procedure structure:
DECLARE@return_value int
EXEC@return_value = [dbo].[csFindProperty]
@HouseNumber = N'1733',
@Street = N'N 40th',
@City = N'Kansas City',
@State = N'KS',
@Zip = N'%',
@County = N'Wyandotte',
@SquareFootageStart = 0,
@SquareFootageEnd = 0,
@Bedrooms = 2,
@Bathrooms = 1.00,
@Garage = 0,
@Basement = false,
@StyleID = 0,
@Subdivision = N'',
@SchoolDistrict = N'%',
@MonthlyRentStart = 1025.00,
@MonthlyRentEnd = 1050.00,
@ListPriceStart = 500.00,
@ListPriceEnd = 1000.00,
@PetsAllowed = false
SELECT'Return Value' = @return_value
GO
if the user enter in the column HouseNumber=1733 that value passed in sp @HouseNumber = N'1733', suppose user can not type any value in the column HouseNumber means, i assign the value HouseNumber='%'. HouseNumber is a varchar datatype.
what my question is: if the column datatype money or int or bit means what is the value i assign @Bedrooms = 2, this situation. since bedrooms is int datatype. if i assign @Bedrooms ='%', conversion error will occur. pls any one can help me come out this problem.
Hi
I want to know how do we store null in the database
i know Null is unkown value
but it has to be something so the SQL server can know its null ( computer only knows 0s and 1s )
is it pointers and null does not have a pointer?
or do we store something physically?
an example : we have string(varchar) column that has this value in it "Joey Tribbiani" that means in the hard disk we will see 14 bytes "Joey Tribbiani" in there, if we changed the value to "Monika" the 14 bytes will be replaced by 5 bytes "Monika", if we changed to empty strike then we will have 0 bytes ""
now if changed it to become NULL what is going to happen to the hard disk, what is going to be in there physically??
may anyone help me in this?
many thanks
HI,ALL:
I'm new to store procedure in sql 2000,now I create a sp to generate Balance report data ,
but when I run my sp in sql 2000 Query analyzer,I can get the data and some of data is null.
Now I want to replace null with '0' in my sp result data, is it possible to do it in store procedure in design model. ?
how to get the soluation ?
thanks a lot!
I have a form and a connectionString to a SQL database. If the textbox at the form is empty i want to store a null value there but when i pass this value as a parameter it brings the following error: Failed to convert parameter value from a String to a Int32..
cmd.Parameters("@Segundo_nombre").Value = txtSecondName.Text.ToString --> suposing is null it brings an Error.
cmd.Parameters.Add(Apellido)
How can i manage this? I want to store this value if it is null or not.
Also i don't know how to assign a null value to a variable. I tried with
v_flag = check_selection.check_string(v_idioma)
If v_flag = 1 Then 'la variable posee el texto Seleccione
v_idioma = DBNull.Value
but it's not working.
Thanks!!
Hi Experts,
I have an urgent needs.
I want to store null for decimal type to sql server. But I do not know how to do that. I get data from user input. If the user did not enter anything in the textbox, then I want to store null to sql server. I list a piece od code below. I got error in the last line fItemObject.ChargeAmount = null;.
if (!String.IsNullOrEmpty(txtDescription.Text)) myObject.LongDesc = txtLongDesc.Text; else myObject.LongDesc = null;
if (!String.IsNullOrEmpty(txtAmount.Text)) myObject.Amount = Convert.ToDecimal(txtChargeAmount.Text); else myObject.ChargeAmount = null;
// Then I submit the myobject to the sql server.
Thank you very much in adavance!
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim
Line 90: sContact.Email = sEmail.Text.Trim
Line 91: sContact.Save()
Line 92:
Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
It's giving me an error while I'm trying to change column value from not null to null..
'tblid' table
- Unable to modify table.
Cannot insert the value NULL into column 'ValidID', table 'Xe01.dbo.Tmp_tblid'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Dear folks,
please tell me the query for altering a column from not null to null
Vinod
Hi all,
One of my columns is the table has some Null values, and I Would like to stop having NULL values into that column any more.
I know, If I alter the column to NOT NULL will throw me an error, since it does a batch update.
Is there any way to achieve this...
Thanks...
Hi all,
One of my columns is the table has some Null values, and I Would like to stop having NULL values into that column any more.
I know, If I alter the column to NOT NULL will throw me an error, since it does a batch update.
Is there any way to achieve this...
Thanks...
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
I receive this message when I try to run any report. The reportserver and reportservertempdb databases were upgraded using backup/restore from SQL2000 to SQL2005 on a separate server which is running RS2005 . Please help. Thanks
View 1 Replies View RelatedOur company purchased a app. Is there any way to find out which store procedures were fired once a button was clicked?
View 3 Replies View RelatedI have been asked to look into using Filestream for centralising MS Office documents (mostly excel).I am worried about the "user interface" aspect.I read that there are "this and that" APIs to read/write data to the filestream but surely one would need to write a specific interface to Word/Excel... which feels like far too hard.I am a great admirer of SQL Server but is it the right tool for document management?
We use SQL Server 2012 and have offices round the world with various internet connection quality.Our main aim is to stop the current "spreadsheet nightmare" so common with Excel.
I do have very old versions of duplicate store procedures on my databases. I know there is no "safe" way to do this using DMVs, so I am planning to combine that with a trace. But I would like to get others opinions about that.
Here's the DMV I am planning to use:
SELECT
CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
,cached_time
,last_execution_time
,execution_count
[Code] ....
I will save that on a local table and run it every 5 min maybe? Or at an interval equal or lower than PLE?
While writing store procedure in db most of Time i will Use common Table to write select queries for selecting more than seven table whether it reduce speed performance or it won't
;with cte
{
}
We have an issue with the Version Store growing constantly. According to sys.dm_os_performance_counters, "Version Generation rate (KB/s)" is growing, but "Version Cleanup rate (KB/s)" isn't. We use read-committed snapshot isolation
While dbcc opentran and sys.dm_exec_requests don't show any long running transactions, I wrote a query looking at sys.dm_tran_ active_snapshot_ database_transactions. This shows a number of long running transactions but, according to sys.dm_exec_sessions, they are all sleeping. The transactions that are running come and go very quickly, as I would expect.Could these sleeping transactions be responsible for preventing the version store from cleaning up?
From my one app, a dynamic where clause will generate like below.
//where ordercity='london' and orderby='smith'
//where orderamount > 100 and shipcity='new york'
From server, I created a store procedure as below.
SELECT * FROM [Order] WHERE @whereSql
How to complete the store procedure so that I don't need to pass parameters one by one...
There are about 10 select statements in a store procedure.
All select statements are need.
Is it possible to output only the result of last select statement?
I am looking to store the different forms and data in our database. We have several different forms and contains different information. I am looking for different approaches to model this table structure.
Also, I need to make sure the table structure will allow for new forms.
In one store procedure I do insert same data into two tables (They have the same structure): OrderA and OrderB
insert into OrderA select * from OrderTemp
insert into OrderB select * from OrderTemp
And then got an error for code below.
"Multi-part identifier "dbo.orderB.OrderCity" could not be bound
IF dbo.OrderB.OrderCity=''
BEGIN
update dbo.OrderB
set dbo.orderB.OrderCity='London'
END
I have below code to send email in HTML table format with store procedure. from my
Database = "CreditControl"
Table = "Testing$"
and code as below
DECLARE @bodyMsg nvarchar(max)
DECLARE @subject nvarchar(max)
DECLARE @tableHTML nvarchar(max)
DECLARE @recipients nvarchar(max)
DECLARE @profile_name nvarchar(max)
[code]....
Whenever SQL Server get restarted, tempdb gets recreated with its last configuration.
let me know where SQL Server store tempdb configurations? How does it know how many Tempdb files it needs to create on restart?