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;

GO

CREATE TABLE myTestFormatFiles (

Col1 bigint null,

Col2 varchar(6),

Col3 varchar(6),

Col4 varchar(6)

);

GO

INSERT INTO myTestFormatFiles

SELECT *

FROM OPENROWSET(BULK 'test1.Dat',

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

GO

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:

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

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

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

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.

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.

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.

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

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?

I have a datatable : Data_Table and a look up table: Lk_table. Myfield that Iuse in Inner Join is defined in both thedata 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 Ihave 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_Tableis Null, I still get the records from Data_Table. (I don t want a set ofrecords including all possible values from the look up table: Lk_Table)

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?

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).

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?

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.

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

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.

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:

m_sqlCmd.ExecuteScalar();

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??

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

how can i use openrowset. my aim is to develop a import and export data from diff. server . i am using vb.net 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.

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!!!

Hi,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.

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.

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?

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

Note: 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.