I have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueException when I try to GetInt32 but not when I try GetString. Why the difference?
Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.
Hi,
I want to define Primary Key non-clustered on Nullable columns with datatype
as varchar ( 50 ) .
When i try to assign primary key , it gives me an error that
' Cannot define Primary Key contraint on nullable columns in table 'Table 1 '.
Could not create contraint . See previous errors . '
Any ideas how to resolve this issue ?
Thanks,
Anita.
Is there a way to set a column nullable in a script if all I have is the column name, something like:
exec('ALTER TABLE xyz ALTER COLUMN ' + @col_name + ' NULL')
The problem is that ALTER TABLE requires the type, so you have to say something like:
exec('ALTER TABLE xyz ALTER COLUMN ' + @col_name + ' decimal(12,4) NULL')
But the data type is not given to the script, so it is stuck.
I realize that syscolumns provides some relevant information, but it is not clear how to convert its information to a string like "decimal(12,4)". It seems, for example, that a decimal type has syscolumns.xtype = 106; however I can find no documentation on this, nor am I assured I can get all the right codes by trial and error. Does anyone know of a clear means of getting this information?
What happens if a column which has been defined so that it should not be Nullable is passed a value which is null?
i.e. IDNumber - Primary Key and Nullable = No.
Thanks
I am trying to add a DateTime? parameter to SqlCommand. It works when the variable has a value, but when its null, an exception gets thrown saying that parameter was not supplied.What is causing this error?
View 5 Replies View RelatedHi all,
I am using a SQL statement with a JOINs to display a summary list of Work Orders with some information from the related Purchase Order. I don't require that a Work Order be attached to a Purchase Order, so the JOIN field, POLineItemID, may not be present. My current problem is that my summary page does not show Work Orders that have a NULL value for POLineItemID -- ie, the JOINing field becomes required.
How can I continue to use this JOIN structure but still return rows that don't have this JOIN field?
Thanks in advance for your help. Here is my SQL statement for this...
SELECT tblWorkOrder.WorkOrderNumber, tblWorkOrder.ActualCompletionDate, tblWorkOrder.EstimatedCompletionDate, tblWorkOrder.AssignedTo, tblWorkOrder.DelegatedTo, tblWorkOrder.Due, tblPurchaseOrders.PONumber, tblWorkOrder.POLineItemID, tblPOLineItems.POLineItemID AS Expr1, tblPOLineItems.PurchaseOrderID, tblPurchaseOrders.PurchaseOrderID AS Expr2, tblProducts.ProductID, tblProducts.ProductCategory, tblWorkOrder.ProductID AS Expr3, tblWorkOrder.WorkOrderStatusFROM (((tblWorkOrder INNER JOIN tblPOLineItems ON tblWorkOrder.POLineItemID = tblPOLineItems.POLineItemID) INNER JOIN tblPurchaseOrders ON tblPOLineItems.PurchaseOrderID = tblPurchaseOrders.PurchaseOrderID) INNER JOIN tblProducts ON tblWorkOrder.ProductID = tblProducts.ProductID)ORDER BY tblWorkOrder.Due
Is it bad design to allow nulls on a date field ? I can think of one case such as a sale of an item and populating a field for the date of purchase, only when the purchase took place (and null until then).
comments ?
Hi All!General statement: FK should not be nullabe to avoid orphans in DB.Real life:Business rule says that not every record will have a parent. It isimplemented as a child record has FK that is null.It works, and it is simpler.The design that satisfy business rule and FK not null can beimplemented but it will be more complicated.Example: There are clients. A client might belong to only one group.Case A.Group(GroupID PK, Name,Code…)Client(ClientID PK, Name, GroupID FK NULL)Case B(more cleaner)Group(GroupID PK, Name, GroupCode…)Client (ClientID PK, Name, ….)Subtype:GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)There is one more entity in Case B and it will require an additionaljoin in compare with caseAExample: Select all clients that belongs to any groupSummary Q: Is it worth to go with CaseB?Thank you in advance
View 20 Replies View RelatedHi,I've enherited a big mess, a SQL Server 2000 database withapproximately 50 user tables and 65+ GB data, no explicitrelationships among entities (RI constraints whatsover), attempt tocreate an ERD would more than likely kill the relatively complexedapp, the owner would want to drop out of window, so,I don't intend to do that, you get the picture, sorry no DDLs thistime, and many tables have many nullable columns, joins slows down thesystem quite a bit, what's my best bet to improve its performance?change most of these nullable columns into non-nullable with defaultvalue of something like ' ', any thoughts along this line would beappreciated.
View 6 Replies View RelatedHi All!I would like to have a composite PK on 3 columns, one of them is nullCREATE TABLE TableA (ColA int NOT NULL ,ColB int NOT NULL ,ColC char (3) NULL ,......)GOALTER TABLE TableA ADDCONSTRAINT TableA_PK PRIMARY KEY CLUSTERED(ColA,ColB,ColC)GOSQL Server does not allow having a composite PK with one nullable column:What is wrong to have values?1,100,NULL1,200,ABC1,200,ABD.....Code in C applies to Values in B and for some values in B the code does not exist.I can work out and define a special Code:NEV(not existing value), but in general I do not understand this restriction.Thanks
View 3 Replies View RelatedThis is what I would like to do...
1. Alter Table Status
Add ConsiderOpenFlag int null
2. UPDATE values...
3. Alter Table Status
Alter ConsiderOpenFlag int not null
Steps 1 and 2 are easy. What I cannot figure out is step three.
I don't want to have a default on that column, though I wouldn't mind adding it and then dropping it later if it would help.
Jonathan
Hi,
Could anyone tell me what governs whether a column is set as nullable or not nullable when creating a table using SELECT...INTO. It just seems to pick at random for me! I'm quite sure this is not the case. Is there a way to force a column to be non- nullable? I seem to be wasting a lot of time going through and altering the schema so I can use the columns in keys and indexes.
Dave
Hi,
I have a table name bla.
PKEY id, int, NOT NULL
group, int
name, string, NOT NULL
how do I compare them with int?
for example the following data.
1, NULL, 'freelance'
2, 1, 'group1'
3, 2, 'group2'
select * from bla where group<>1 <-- this fails?
What is the proper SQL Statement for this?
Regards,
Max
I have a producer table with a nullable column that stores SSN's. In some cases producers inherit SSN's from other producers. These records will have a null producer.ssn and a record stored in a child table to track the inheritance. Anyway, I've found two techniques to enforce uniqueness on a nullable column and wanted to get opinions as to which was better. First, write a trigger. Second, create a computed column that has a unique constraint on it. The computed column would use the SSN if not NULL Else use the PK identity value of the record. EXAMPLE DML:CREATE TABLE test ( ssn CHAR(9) NULL, testId INT identity(1,1) NOT NULL, ComputedConstraint AS CASE WHEN ssn IS NULL THEN CAST(testId AS CHAR(9)) ELSE ssn END, UNIQUE (ComputedConstraint)) Any comments would be greatly appreciated.
View 6 Replies View RelatedI have 4 particular columns (crt_dt,upd_dt,entity_active and user_idn) in many of the tables in my database. Now i have to find all the tables having four columns mentioned above and cases are
1) if the column is nullable., then it should result 'Y'
2) if the column is not nullable., then it should result 'N'
3)if column is not present., then it should display '-'
greetings
Sir i want to check and use the select statement to select which field have a nullable=false in a table
greetings
i am use this query to select the primary field colums in a table
"select Column_Name as PrimaryKeycolumn
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'tbl_Activity'
and Constraint_Name like 'PK_%'"
but i want to select the fields which have a nullable=false
for that i want know the information schema for null
thank u
Question.I have a new table that I am adding to a script that I wrote. Thistable has 3 fields, the first 2 fields are used in the on statement asbeing = other fields in the script.The first field always has data in it, but the 2nd field is sometimesnull.So my problem is if both fields have data in them and they both matchto the data in the fields that I am linking them to, then it returnsthe 3rd field without a problem. However if the 2nd field is null thenit is returning a null for the 3rd field. I have checked and the fieldthat I am linking to is null also.So if I haveselect t1.field1, t1.field2, t2.field1, t2.field2, t2.field3from table1 t1join table2 t2on t1.field1=t2.field1 and t1.field2=t2.field2with 2 records in each tabletable1: record1: data, datarecord2: data, nulltable2: record1: data,data,datarecord2: data,null,datawhat I get from the script isrecord1: data, data,data,data,datarecord2: data,null,data,null,nullI would expectrecord2: data,null,data,null,dataI hope this makes sense, I didn't want to post the entire actual scriptas it is about 150 lines long.Thanks in advance.
View 5 Replies View RelatedHello,
I am trying to recreate a primary key that I dropped in a table....
I'm using a statement like
ALTER TABLE temp WITH NOCHECK add CONSTRAINT PK__tempkey PRIMARY KEY CLUSTERED
(
num,
store
)
But apparently the "store" column was created without a "not null" and it let it create the PK during the table creation but now it won't let me add the PK with that nullable column..
Does anyone know how to get it to use that column to create a primary key after the initial table creation?
Here is the error i get
Cannot define PRIMARY KEY constraint on nullable column in table
Thanks a lot
I would like to dynamically copy a table in Transact SQL, like this:
SELECT * into NEW_TABLE from MY_TABLE where 1 = 2
This creates an empty NEW_TABLE as desired. However, NEW_TABLE retains the column nullability of MY_TABLE; I would like NEW_TABLE to have all its columns nullable.
I tried to write Transact SQL logic to update the isnullable column in syscolumns for NEW_TABLE, but was told that the entire SQL Server would have to be reconfigured to allow this.
Does anyone know of another way to do this?
I have a dimension with a date field (int key value actually) that is generally null that I'm using to link to a time dimension. Everything works great when I set Null Processing to UnknownMember in the Measure Group Bindings, but if I try to set it to Preserve or anything else, I get processing errors because it can't match the null attribute key to the time dimension. I think I understand this, but I'm concerned that I'm not doing it the best way. Also, why does this page say "UnknownMember causes a data integrity error at processing time" when that's the only setting that does not give me errors?
http://technet.microsoft.com/en-us/library/microsoft.analysisservices.nullprocessing.aspx
I have a case where a table has two candidate primary keys,but either (but not both) may be NULL. I don't want to storea copy of the concatenated ISNULL'ed fields as an additionalcolumn, though that would work if necessary. Instead, I triedthe following (this is a related simplified example, not myreal one):CREATE FUNCTION ApplyActionPK(@IP int = NULL,@DNS varchar(64) = NULL)RETURNS varchar(74) -- NOT NULLASBEGINdeclare @val varchar(74)set @val = str(ISNULL(@IP, 0), 10)set @val = @val + ISNULL(@DNS, '')return @val-- Also tried "return str(ISNULL(@IP, 0), 10)+ISNULL(@DNS, '')"-- Also tried "return ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"-- ... and other things...ENDGOcreate table ApplyAction(-- An action applies to a computerAct varchar(16) NOT NULL,-- The action to applyIP int NULL,-- The computer IP address, orDNS varchar(64) NULL,-- The DNS name of the computerTarget as dbo.ApplyActionPK(ComputerID, DNS), -- PK value-- Also tried "Target as ISNULL(STR(@IP, 10), ISNULL(@DNS, ''))"CONSTRAINT PK_ApplyAction PRIMARY KEY(Act, Target))SQL Server always complains that the primary key constraint cannot becreated over a nullable field - even though in no case will the 'Target'field be NULL.Please don't explain that I should store an IP address as a string.Though that would suffice for this example, it doesn't solve myactual problem (where there are four nullable fields, two of whichare FKs into other tables).What's the reason for SQL Server deciding that the value is NULLable?What's the usual way of handling such alternate PKs?Clifford Heath.
View 7 Replies View RelatedI recently ran into an issue with an issue with a query against our Data Warehouse. When attempting to sum revenue from a table, and using a WHERE clause on a field that contains NULL values, the records with the NULL values are suppressed (in addition to whatever the WHERE clause specified). I believe this is because a NULL value is unknown so SQL doesn't know if it does or doesn't fit the criteria of there WHERE clause so it is suppressed.
That being said, is there a way to avoid this instead of having to add an ISNULL function in the WHERE clause which is going to kill performance?
Code:
create table #nullTest (
name varchar(50)
,revenue int)
INSERT INTO #nullTest
Values ('Tim',100)
,('Andrew', 50)
,(null, 200)
SELECT sum(revenue) as Revenue FROM #nulltest WHERE name <> 'tim'
Ideally, I would want the SELECT statement above to return 250, not 50. The only way I can think to accomplish this is with this query:
Code:
SELECT sum(revenue) as Revenue FROM #nullTest WHERE isnull(name,'') <> 'tim'
I have a very large table that I need to partition. Ideally the table will write to three filegroups. I have defined the Partition function and scheme as follows.
CREATE PARTITION FUNCTION vm_Visits_PM(datetime)
AS RANGE RIGHT FOR VALUES ('2012-07-01', '2013-06-01')
CREATE PARTITION SCHEME vm_Visits_PS
AS PARTITION vm_Visits_PM TO (vm_Visits_Data_Archive2, vm_Visits_Data_Archive, vm_Visits_Data)
This should create three partitions of the vm_Visits table. I am having a few issues, the first has to do with adding a new clustered index Primary Key to the existing table. The main issue here is that the closed column is nullable (It is a datetime by the way). So running the following makes SQL Server upset:
ALTER TABLE dbo.vm_Visits
ADD CONSTRAINT [PK_vm_Visits] PRIMARY KEY CLUSTERED
(
VisitID ASC,
Closed
)
ON [vm_Visits_PS](Closed)
I need to define a primary key on the VisitId column, but I need to include the Closed column in order to partition on it.how I would move data between partitions on a monthly basis. Would I simply update the Partition function, or have to to some sort of merge, split, or switch function?
I need to programatically create a mdb file which will contain nullable columns. I am using C++ with ADOX for the table creation and ADO to perform the table update.
Although ADOX seems to create the table ok, Table->Columns->Appends does not set the fields as adColNullable as expected.
When I insert data using ADO::Recordset->AddNew the following error occurs :- "The field 'MyTable.Column 2' cannot contain a Null value because the Required property for this field is set to True. Enter a value in this field."
Am I on the right tracks here or do I need to adopt a different approach?
Code block to illustrate the problem :-
Code Block
ADOX::_CatalogPtr m_pCatalog;
ADOX::_TablePtr m_pTable;
ADOX::_ColumnPtr m_pCol1;
ADOX::_ColumnPtr m_pCol2;
ADODB::_ConnectionPtr m_pConn;
ADODB::_RecordsetPtr m_pRs;
//ADOX - Create Data Source
_bstr_t strcnn(("Provider='Microsoft.JET.OLEDB.4.0';Data source = C:\test.mdb"));
m_pCatalog.CreateInstance(__uuidof (ADOX::Catalog));
m_pCatalog->Create(strcnn);
m_pTable.CreateInstance(__uuidof(ADOX::Table));
m_pTable->PutName("MyTable");
m_pCol1.CreateInstance(__uuidof(ADOX::Column));
m_pCol1->Name = "Column 1";
m_pCol1->Type = ADOX::adVarWChar;
m_pCol1->DefinedSize = 24;
m_pCol1->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol1->Name, ADOX::adVarWChar, 24);
m_pCol2.CreateInstance(__uuidof(ADOX::Column));
m_pCol2->Name = "Column 2";
m_pCol2->Type = ADOX::adVarWChar;
m_pCol2->DefinedSize = 24;
m_pCol2->Attributes = ADOX::adColNullable;
m_pTable->Columns->Append(m_pCol2->Name, ADOX::adVarWChar, 24);
m_pCatalog->Tables->Append(_variant_t((IDispatch *)m_pTable));
//ADO - Data Access
m_pConn.CreateInstance (__uuidof(ADODB::Connection));
m_pConn->Open(strcnn,_bstr_t(""),_bstr_t(""),ADODB::adOpenUnspecified);
m_pRs.CreateInstance(__uuidof(ADODB::Recordset));
m_pRs->Open("MyTable", _variant_t((IDispatch *)m_pConn,true),ADODB::adOpenKeyset, ADODB::adLockOptimistic, ADODB::adCmdTable);
// Define a SafeArray that contains field names.
SAFEARRAY * psaFields;
SAFEARRAYBOUND aDimFields[1];
aDimFields[0].lLbound = 0;
aDimFields[0].cElements = 1;
psaFields = SafeArrayCreate(VT_VARIANT, 1, aDimFields);
// Create a SafeArray for values.
SAFEARRAY * psaValues;
SAFEARRAYBOUND aDimValues[1];
aDimValues[0].lLbound = 0;
aDimValues[0].cElements = 1;
psaValues = SafeArrayCreate(VT_VARIANT, 1, aDimValues);
long ix[1];
_variant_t var;
//Insert Data
ix[0] = 0;
var = "Column 1";
SafeArrayPutElement(psaFields, ix, (void*) (VARIANT *) (&var));
ix[0] = 0;
var = "test data row 1 col 1 Only";
SafeArrayPutElement(psaValues, ix, (void*)(VARIANT *) &var);
_variant_t vtFields, vtValues;
vtFields.vt = VT_ARRAY | VT_VARIANT;
vtValues.vt = VT_ARRAY | VT_VARIANT;
vtFields.parray = psaFields;
vtValues.parray = psaValues;
m_pRs->AddNew(vtFields, vtValues); //!! Fails Here !!
m_pRs->Update();
m_pRs->Close();
m_pConn->Close();
Having serious problems trying to insert date into database using sqladapter.update method gives an error saying "Converting DateTime from Character string". the funniest thing is that it works on my developement box, but when i upload to the server with thesame settings in my development box, it does not work.
View 2 Replies View RelatedHi,
I will give someone a script that creates a database using :
create database mydatabase
my question: can I use myDatabase.dbo...... and myDatabase..Whatevertable in order to manipulate the database objects or should I be careful with putting dbo in my script.
The reason is that I will have to give the following script to someone to execute on his instance and I don t want it to fail.
The script creates a database mosaikDB737, create a table called FileListInput in that database and populates a second table called DBlistOutput with the list of names of all databases in the instance.
Please let me know if there are any (BAD) chances for the following script to fail.
create database mosaikDB737
go
use mosaikDB737
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileListInput]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FileListInput](
[FileName] [char](50) NULL
) ON [PRIMARY]
END
use master
select name into mosaikDB737.dbo.DBlistOutput from sysdatabases where name not in ('master','tempdb','model','msdb')
select * from mosaikDB737.dbo.DBlistOutput
OK, so I'm new to SQL server, which I'm sure you'll all see from my question below.
I am trying to migrate an access DB with queries over to sql server 2005. simple queries I can handle, but I've come accross a query that calls another query and does an update based off of my first query. The below queries work perfectly fine in access but I dont know how to get this going in SQL server. From my VERY minimal understanding in of SQL server i thought we couldnt call stored procedure (query1) and have it update the underlying tables. If I'm wrong, please show me how its done, If I'm right please show me the right way of doing this.
If you see spelling errors in the queries please ignore, that is not the full queries, it is just a cut down version to explain what I need to be able to do.
Query1
SELECT table1.assettag, table1.City, table2.Status, table2.ScheduleItems
FROM Table1 Inner join on table1.assettag = table2.assettag
where Status = "Scrubbed" or Status = "Initial"
Query2
Update Query1
SET query1.ScheduledItems = True
Where query1.Status = Scrubbed
thank you for any information or help.
Hi,
I have never used coding before (just learning) and I need to collect username and password and check it against my SQL database. I am using the below code as a sample guide for me to figure this out. Does anyone point me to a sample code page that I may look at that actually is doing what I want to do??
Sondra
Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click
Dim myReader As Data.SqlClient.SqlDataReader
Dim mySqlConnection As Data.SqlClient.SqlConnection
Dim mySqlCommand As Data.SqlClient.SqlCommand
'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.
mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())
Dim sql As String = "SELECT UserLogonID, UserPassword FROM MyUsers WHERE UserLogonID = '" & Me.userid.Text & "' " And "Userpassword = '" & Me.psword.Text & "'"
mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)
Try
mySqlConnection.Open()
myReader = mySqlCommand.ExecuteReader()
If (myReader.HasRows) Then
'Read in the first row to initialize the DataReader; we will on read the first row.
myReader.Read()
Dim content As ContentPlaceHolder
content = Page.Master.FindControl("main")
Dim lbl As New Label()
lbl.Text = "The Last Name you choose, " & Me.dlLastName.Text & ", has a first name of " & myReader("FirstName")
content.Controls.Add(lbl)
End If
Catch ex As Exception
Console.WriteLine(ex.ToString())
Finally
If Not (myReader Is Nothing) Then
myReader.Close()
End If
If (mySqlConnection.State = Data.ConnectionState.Open) Then
mySqlConnection.Close()
End If
End Try
End Sub
I read a few articles on best SQL practices and they kept coming back to using a Least Privileged Account. So I did so and gave that account read only permissions. The articles also said to do updates use Stored Procedures - so I created stored procedures for updating/deleting data.So here's my problem - I connect to the database using the Least Privileged Account, I use the Stored Procedures, but .NET keeps saying I lack permissions. If I GRANT the Least Privileged Account UPDATE/DELETE permission on the table, the Stored Procedures run perfectly. But isn't that EXACTLY what I'm trying to avoid?My greatest concern is someone hacks my website and using the Least Privileged Account, they delete all my data using that account. So I don't want to give the Least Privileged Account the Update/Delete privileges.Thanks a MILLION in advance!
View 3 Replies View RelatedHello,
I am calling a sql 7.0 stored procedure (sp) from an active server page(asp).
The sp is a simple insert. I need to read the return the value of the sp in my asp. If the insert is
successful, my return value is coming back correctly (to whatever i set it)....but if there is an error
such as a Uniqueness Constraint, I can't get the return code(set in the SP) to come back to the ASP.
It comes back blank. (The literature I've read says that processing should continue in the SP, so you
can perform error processing...is that right?)
I set the return var in my ASP as:
objCommand.Parameters.Append objCommand.CreateParameter("return",_
adInteger,adParamReturnValue,4)
and read it back as:
strReturn = objCommand.Parameters("return").Value
In my SP I simply do;
INSERT blah blah
if @@error = 0
return(100)
else
return(200)
(Idon't ever get back "200")
Any ideas???
Thanks for your help.
Hi
I have study Microsoft online books for few days no, about repliction but I'am even more confused about replication. please help somebody
My goal:
I have been written a GPS program that has an database.
The database will be replicated to an central web server.
That will say one web server and x numbers of laptops that has same GPS program. :)
All laptops uses internet to replicate.
Now... I have no problem to create publications and subscriptions on server BUT HOW do I do it on client????? :confused:
Microsoft do not write nothing about client side of replication. everyting is SERVER, SERVER,SERVER and SERVER.The Microsoft HOW TO is only How to click forward, its dosen't really explain anything.
Problaby I need some kind of an database on client and subscription to make the replication. :confused:
please help me, I'am almost finished with my project only replication part is over my head :(
if someone can point me to right direction in this issue. I would be greateful. :cool:
Thanks
KK
I want to use xp_sendmail against a database other than the master. When I run a test using the master database, it sends a test message w/no problems. However when I try to use xp_sendmail against a database I've created, it gives me an error stating:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_sendmail'.
How can I use xp_sendmail using a dabase other than Master?? Please Help.