OPENROWSET Default Null Issue

(Sql Server 2005 sp2)

I have this really strange issue with Inserting with OPENROWSET.
The problem is that even if I make a field nullable, when the data file has a blank for that field, the insert puts a 0 for that field instead of null. This happens when I mention the column names in my select, as in SELECT Col1, Col2, Col3, Col4 . This DOES NOT happen when I use SELECT *, which I thought was strange. Am I skipping an option somewhere? Thanks...

USE test;


CREATE TABLE myTestFormatFiles (

Col1 bigint null,

Col2 varchar(6),

Col3 varchar(6),

Col4 varchar(6)



INSERT INTO myTestFormatFiles



FORMATFILE='test2.Fmt', CODEPAGE='RAW') as t1 ;


so if I use the above-mentioned code, I get the null value, which is what I obviously want. But initially I had this select:

SELECT Col1, Col2, Col3, Col4

And it returned a 0 instead of null for that field.

Here's my fmt file - test2.fmt
1 SQLCHAR 0 10 "" 1 Col1 ""
2 SQLCHAR 0 6 "" 2 Col2 ""
3 SQLCHAR 0 6 "" 3 Col3 ""
4 SQLCHAR 0 6 "
" 4 Col4 ""

Here's my dat file - test1.Dat
15 Field2Field3Field4
46 Field2Field3Field4
58 Field2Field3Field4

View Replies


Subscription Issue With Null Default Parameter - Key Cannot Be Null

I have a report that is run on a monthly basis with a default date of null. The stored procedure determines the month-end date that it should use should it be sent a null date.

The report works fine when I tell it to create a history entry; however, when I try to add a subscription it doesn't appear to like the null parameter value. Since I have told the report to have a default value of null it doesn't allow me to enter a value on the subscription page.

Now, I suppose I could remove the parameter altogether from the stored proc, but then the users would never be able to run the report for a previous time period. Can someone explain to me why default values aren't allowed to be used on subscriptions when they seem to work fine for ad hoc and scheduled reports? This is really quite frustrating as most of my reports require a date value and default to null so that the user doesn't have to enter them for the latest data.

An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help

Key cannot be null. Parameter name: key

View Replies View Related

Change Not Null To Null, Default Value To Empty

hi,my structure table in database:Amount float(53) not null default 0when i try to run his script:alter table ABC alter column Amount float(53) nullit can only set the Amount to allow null, but can't set the defaultvalue to empty.anyone know how to set the field to allow null and default set toempty, no value.thanks

View Replies View Related

Default Is NULL

Also, SQL server defaults to 'allow nulls' when creating tables. (Itusually is when nothing is specified then 'not null' is default value).for example;create table t1(c1 int, c2 int)insert t1(c1) values(0)select * from t1c1 c10 null

View Replies View Related

Default Date When Not Null?

I have a table that has a date field that can't be null.  When a new record is created, if a date is not passed in, I want the default to be the current date.  How can I set that?  I tried Now, Today, GetDate, etc.  Diane 

View Replies View Related

NULL As The Default Value Of SP Paramters


View Replies View Related

Column Null Or 0 Default Value


I have a table with the list of employee and 15 column with data type float or money , that represent the number of hours, airfare,gas,parking food, etc....

I have 2 choice:
-Put 0 as a default value for each column, like that I do not have to use coalesce when I do SUM for each column.
-Leave the default value null but I will have to use coalesce.

In term of performance, what is the best solution?


View Replies View Related

Allow NULL Or Define DEFAULT Value

I am designing a new table with a few columns that may or may not havea value on each row that is inserted.What issues determine whether to allow a NULL value to be inserted forthat column or define a default value to be used?I want to think through the repercussions of this decision before I getinto production.

View Replies View Related

Default Value For Int Column That Allows Null ?

If no value is supplied on an insert for an int column that allows nulls, will the value be null or 0 ?

View Replies View Related

Default Value For NULL Value Input


I am using sql as a back-end server. My application requires to insert a default string values when the user do not provide any input or if the inpur is NULL. I have used the following code, but the defaul values are not inserted. I am not getting the desired result.

CREATE TABLE Employees1 (

EmployeeName varchar(50),

State char(3) NOT NULL DEFAULT 'NSW' ,

PostalCode int default 2000,

Country varchar(32) NOT NULL default 'Australia');

insert into Employees1 values ('diana', ' ' , 2,' ' )

The output is: EmployeeName State PostalCode Country

diana 2

Can anyone tell me what is that i am missing?


View Replies View Related

Accept NULL Value And Convert It To Default Value...

Is there any way to make a column to accept NULL but stores it as default value of Column Property?

View Replies View Related

Alter Table To Allow Default For Null Value

how can you alter a table so that you can add a default value whenever the field is NULL? for example, whenever the table is brought up in a query, NULL is replaced with UNKNOWN

View Replies View Related

Question On &&"Not Null With Default'

I have defined 8 varchar columns as not null with default, i.e.

[Namad8] [varchar](40) COLLATE Latin1_General_CI_AS NOT NULL DEFAULT (' '),

I run a script transformation (Flat file source, OLE DB dest) that splits a crlf delimited field an array, and then loads each array element into one of the not null table columns as above. However there may be less than 8 elements present in the input field, which means the remaining columns are not populated.

When I run the data flow task, I get the error

Error: 0xC020901C at Capture Assessments, OLE DB Destination 1 [1]: There was an error with input column "Namad8" (24) on input "OLE DB Destination Input" (14). The column status returned was: "The value violated the integrity constraints for the column.".

I know I'm trying to insert a null value into the column, but my understanding was that SQL would default the column to spaces. If I define the column to allow nulls, the data flow works fine.

Any idea what I'm doing wrong here?

View Replies View Related


Hi,I am using SQL Server 2005 32 bit version. Could any one clarify methe caption database option with some examples? I am quite confusedwith the explanation given in MSDN. Kindly help me.Thanks in Advance.Om Prakash

View Replies View Related

CANT We Set Default Value Null To Parameter In Stored Procedure


I've written a stored procedure with 4 parameters

create procedure dummy
( @a int, @b int, @c varchar(50), @d varchar(50))

now from front end(I'm using
I want to send the values according to some criteria
So in the process...I've only values for @a & @c ...

so In order to reduce the code of sending Null values explicitly to other parameters...

can't I set like default values for it so that If I don't send values to certain parameters it will have the default value or Null value.

like I want something like this:

create procedure dummy
( @a int NULL, @b int NULL, @c varchar(50) NULL, @d varchar(50) NULL)

View Replies View Related

Change NULL Values To Default In SELECT Statement

I have a stored procedure with a SELECT statement, that retrieves 1 row.
SELECT name FROM tblNames WHERE nameID = "1"
I want all the NULL values in that row to be change in some default values.
How do I do this?

View Replies View Related

- Using Inner Join When The Field In My Data Table Has The Null Default Value:

I have a datatable : Data_Table  and a look up table: Lk_table. Myfield that I
use in Inner Join  is defined in both the
data and look table.


So I build my query like this:

SELECT     * FROM         dbo. Data_Table  INNER JOIN

dbo. Lk_table ON dbo.Data_Table.MyField = dbo.Lk_table.Myfield


The pb, sometimes  I
have myfield still with its default null value in the datatable: Data_Table.
So, I end up getting 0 record when I execute the query shown above.

How do I turn that around so that even if myfield in Data_Table
is Null, I still get the records from Data_Table. (I don t want a set of
records including all possible values from the look up table: Lk_Table)

View Replies View Related

Quick Question... How To Use Default Values (after Allowing NULL)


I have a BIT column which accepts NULL values.

What would be a good method to allow an INSERT (or UPDATE) statement to insert NULL into this column but then automatically change the NULL to 0 (zero). In other words, test for NULLs after INSERT (or UPDATE) and change the value to 0 (zero).

Not exactly sure how to do this with a Trigger. Also, what is that [Formula] option used for (column properties in the Table Design view)... and would this apply with my problem?


View Replies View Related

Reading Default Values Instead Of NULL From A Flat File


I have the following problem: I'm connected to flat file source and trying to read data that is later inserted in an MS Access database. Everything wokrs fine instead of one thing - when I have null values in the flat file, I want those NULL values to be inserted in the MS Access db, instead of that what happens is that I actually get the default values for a column type from the flat file and later insert that defalut value. For example if I have a null value in an four-bite-signed-integer column of the flat file, I get 0 as value.

I thouth of solution using a "Derived Column" transformation which can transform the zeros into nulls, but decided to check with you guys if there is a smarter thing to do (for example to edit the flat file source to read the NULLs correctly).

Any advice is appreciated! Many thanks


View Replies View Related

Date Selection Giving Default '1/1/1900' For NULL Values

'XXX_DTE' is character type which wont take NULL.


I get result as : 1/1/1900

Why is it so.....

What I expect is '0000-00-00 00:00:00.000'

View Replies View Related

Data Flow Task - Have Null Values Take Table Default


I have a data transform from a flat-file to a SQL server database.
Some of the flat-file fields have NULL values. The SQL table I'm
importing into does not allow NULL values in any field, but each field
has a Default value specified.

I need to have it so that if a null value comes across in a field using
the data transform, it takes the table default on import. I could of
sworn I had this working a few days ago, but I get errors now that
state I'm violating table constraints. Has anyone done this before?


View Replies View Related

Default To NULL Instead Of Blank/empty String During Flat File Import


In SSIS flat file import using fastload, I'm trying to import data into SQL 2005 previously created tables.

The table may contain column that are NULLable BUT there is NO DEFAULT for them.

If the incoming data from flat files contains nothing either between the delimeters, how can I have a NULL value inserted in the column instead of blank/empty string?

I didn't find an easy flag unless I'm doing something wrong. I know of at least two ways to do it the hard way:

1- set the DEFAULT(NULL) for EVERY column that needs this behaviour

2-set up some Derived Column option in the package to return NULL if the value is missing.

Both of the above are time consuming since I'm dealing with many tables. Is there a quick option to default the value to NULL WHEN there is NO data ELSE insert the data itself? So the same behavior that I have right now except that I want NULL in place of empty string/blank in the varchar(x) columns.



View Replies View Related

Problem With Isnull. Need To Substitute Null If A Var Is Null And Compare It To Null And Return True

Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you

set ansi_nulls off



@inFileName VARCHAR (100),

@inFileSize INT,

@Id int,

@inlanguageid INT,

@inFileVersion VARCHAR (100),

@ExeState int

set @inFileName = 'A0006337.EXE'

set @inFileSize = 28796

set @Id= 1

set @inlanguageid =null

set @inFileVersion =NULL

set @ExeState =0

select Dr.StateID from table1 dR


DR.[FileName] = @inFileName

AND DR.FileSize =@inFileSize

AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)

AND DR.languageid = isnull(@inlanguageid,null)




set ansi_nulls on

View Replies View Related

Any Improvements To This: Cannot Apply Value Null To Property Login: Value Cannot Be Null.

Looks like there was a fix and then I read this fix is not a fix. Does anyone know how this can be rectified? Does it mean that only Windows authentiation is the only way it works. The Software is over 2 years old, there are no excuses.

View Replies View Related

Returned SQLParam.SqlValue Is {Null} But Can't Test For Null?

I run a stored procedure for which I have a return variable. The stored procedure returns the ID of a row in a table if it exists:


The m_sqlCmd has been fed an SQLParameter with direction set to output.
When the stored proc returns, I want to test it. Now when there IS a row it returns the ID ok.
When the row doesn't exist, in my watch I have:

m_sqlParam.SqlValue with value {Null}

I can't seem to work out how to test this value out.
I've tried several things but none seem to work.

This line compiles ok, but the following runs into the IF statement as if the SqlValue is null??

if (m_sqlParam.SqlValue != null)....

// I'm here!! I thought the watch says this is null???

Sorry if this is obvious, but I can't work this one out!!

View Replies View Related

OpenRowSet BUG?

Hi Folks,I am trying to load data from a table in MS Access to SQL Server 2000using T-SQL OPENROWSET. When I select data from the remote database (MSAccess) using SQL Query analyzer, the columns do NOT appear in the sameorder as seen in Access directly.For e.g. if Access table has columns Cy, Cx, Cz the output in Queryanalyzer appears as Cx, Cy, Cz. It appears to arrange the fieldsalphabetically. This causes problems when I do a 'insert into select *from' as the field definitions do not agree.Is this a bug or is there a setting in Access/SQL which I am missing?Also, please let me know if there is a workaround for this issue.Thanks in advance!Bhaskar

View Replies View Related


I can successfully retreive data using:

Declare @Path varchar(100)
Declare @CommandString varchar(100)
Set @Path = ''
Set @CommandString = 'Select * from [myTable.csv]'

from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=\myServermySharedb' + @Path + ';'',''' + @CommandString + ''')')

Does anyone know an easy method to get column names into a table using OpenRowset :confused:

View Replies View Related


how can i use openrowset. my aim is to develop a import and export data from diff. server . i am using 2003 , sql sever 2000.
when i am running
openroeset function with window or server authontication it is showing
'OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error.

anybuddy help me , pls. tell me the right solution.

View Replies View Related


How come when I run the script:
select * from openrowset ('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=\inawwwpro01d$atrUploadACK102.xls' , 'select * from [sheet1$]') from Server A I get a RESULT, and when I run from Server B I get the following error:
Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.
Both servers are using IDENTICAL SQL Logins.
Both servers are SQL 2000 SP2.

When I run the script on Server B logged in as SA, then I get a Result!!!

View Replies View Related


I am trying to update rows in MsAccess from SQl server for any new row created in SQL Server. I am using the following sql to update. When i run this sql as a query using SQL query Analyser, the query works fine and updates the MsAccess database. If i use the same sql inside a trigger or a stored procedure i get an error 'The current transaction could not be exported to the remote provider. It has been rolled back.' Can any one help me? Thanks in advance.

'InternalRunTestATest.mdb';'admin';'', TestTable)
SET AName = 'Success' Where AID= 10

AID is the primary key.


View Replies View Related

DTS And Openrowset

I have a data transformation set up in DTS. I have an ODBC (frm and as/400) which is where the data is coming from. I want to select records form this file based on a condition that is based on a file on the sql server 7.0 Basically what I'm trying to accomplish is this.

Select * from iptsfil.ipwkhst where
year - 2000 = (Select year from dbo.Calendar where priorweek = '1')

where the ipwkhst file comes from my odbc connection and calendar comes from the sql server. But for some reason it thinks the calendar file should also be using the odbc connection. How do I tell it to use the sql server instead?


View Replies View Related


Is it possible to use openrowset with a trusted connection?

I am running the following query and getting "Login failed for user ''"

select s1.sid from
openrowset('sqloledb','Server=Server1;Database=mas ter;Trusted_Connection=yes',
'select sid,name from syslogins') as s1

I have an account on both machines.
The NT Account that is used for the MSSQLServer service is the same on both machines.
I am aware of linked servers but I would like a more ad hoc method for running linked queries.

View Replies View Related

Copyrights 2005-15, All rights reserved