Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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:

SELECT Col1, Col2, Col3, Col4

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

Here's my fmt file - test2.fmt
8.0
4
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
           Field2Field3Field4
15        Field2Field3Field4
46        Field2Field3Field4
58        Field2Field3Field4


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
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
Issue With Oracle && OPENROWSET
 

hello,
 
I have to retrive data from oracle, i'm doing it as..
 

select * from

OPENROWSET(

'msdaora',

'Password=pass;User ID=sandeep;Data Source=my.server.com',

'select * from My_tbl where ROWNUM <= 10')
 

It is throwing error..

Msg 109, Level 20, State 0, Line 0

A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)
 

I found that this is some issue with SQL Server and is fixed in SP1 so i downloaded latest verstion of SQL Server Express Edition (which includeds SP1),

 
And it's still not working... Anybody knows the resolution ?
 
Thanks
Sandeep Giradkar

View Replies !   View Related
Update/Insert Issue With OpenRowset/BulkInsert
Hello,

We have a Windows 2003 Server with SqlServer 2000 Standart with sp4, We have the latest versions of MDAC 2.8 sp1 and JET 4.0 sp8. Client service is working on the server (VB.NET with 1.1.4322. framework). The service runs on the server and reads from files (tab delimited) and updates values to tables. The problem is, the service doesn't properly update "some" of the big files. It only updates 1-2 rows. The table that we update is a rather big table (about 12.000.000 rows).

We execute the following sp

  SET @sp = @sp + 'SELECT TMP_GsmNo, TMP_Status FROM OPENROWSET('
  SET @sp = @sp + '''MICROSOFT.JET.OLEDB.4.0'','
  SET @sp = @sp + '''' + @pathTotal + ''','
  SET @sp = @sp + '''SELECT * FROM [' + CONVERT(nvarchar,  @gatewayMessageID) + '#txt] WHERE TMP_Status IN(3,5)'')'

  INSERT #TEMP EXEC(@sp)

   UPDATE Reports SET RPT_Status=TMP_Status FROM
  (SELECT TMP_Status, TMP_GsmNo FROM #TEMP) TempTable
  WHERE RPT_MessageID=@messageID
  AND RPT_GsmNo COLLATE database_default=TMP_GsmNo

We tried to find out, if the problem is because of Reports table and added the following code before update statement.

  if @gatewayMessageID = 119044

      INSERT TempTable EXEC(@sp)

It only inserts 2 rows to TempTable. We tried the same code with BULKINSERT too

  SET @sp = @sp + 'BULK INSERT #TEMP FROM ' 
  SET @sp = @sp + '''' + @pathTotal + ''''
  SET @sp = @sp + 'WITH (FIELDTERMINATOR = '' '')'

  EXEC(@sp)

  UPDATE Reports SET RPT_Status=TMP_Status FROM
  (SELECT TMP_Status, TMP_GsmNo FROM #TEMP) TempTable
  WHERE RPT_MessageID=@messageID
  AND RPT_GsmNo COLLATE database_default=TMP_GsmNo

the results were the same.

We tried to call the sp from the code (dynamic sql) but the results were the same. But when we debug the code and call the insert or update portion of sql for an incorrect message, from the query analyzer it properly updates the db. 

Thanks.

View Replies !   View Related
Recurring Issue - OPENROWSET For Excel Query Stops Working , Cured By Restart Of SQL Server
Hi,
 
I am having a recurring issue that involves a stored proc using OPENROWSET to query an excel file. I used the surface area config to enable this on the server, and made sure that is still set. After an undetermined amount of time, the OPENROWSET query starts failing with this message:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I corrected this previously by restarting the SQL server, but not before I checked permissions, the excel file itself, etc, and that was the last thing to try.

I am using SQL 2005 with SP1 installed - my primary approach to tackling this issue is to install the SP2, but I did not find this bug referenced in the fixes, and was wondering if anyone else had further insight.

Thanks,
Darrell Young

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
Column Null Or 0 Default Value
Hi

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?

Thanks

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
hi,
 
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?
 
regards
Nongban

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
ANSI NULL DEFAULT Database Option
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
 

Hello
 
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 c#.net)
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)
Hello,

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?

Thanks,

View Replies !   View Related
Reading Default Values Instead Of NULL From A Flat File
Hi,
 
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
 
Ventsy

View Replies !   View Related
Date Selection Giving Default '1/1/1900' For NULL Values
'XXX_DTE' is character type which wont take NULL.

SELECT CONVERT(Datetime,XXX_DTE) FROM XXXX

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


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?


Thanks
Jeff

View Replies !   View Related
Default To NULL Instead Of Blank/empty String During Flat File Import
Hi,

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.

 

Thanks

Anatole

View Replies !   View Related
Importing Issue With Default Text Value
I have a table that I need to import daily from an ftp source and I need to provide an extra col named photo which I figure can be a varchar type but whose default value need to be:

http://somewhere.com/ + the itemnumber from col 1 (text) + .jpg

Basically, I need to have some text the value from another col and then more text.

Any ideas on how I can do this?

Thanks

View Replies !   View Related
Dts Null Issue
I'm trying to import a text file into a table via dts. the file i am importing uses a dash(-) when it has no data. I am running a preprocessor on the text file and I'd like to change all the dashes to be null, becuase the datatype i am importing to is an INT. so i've changed all -'s to be "". this doesn't work. i tried using the word null too. the text file is space delimited...is what i wnat to do possible? i know i can use activeX on the - to transform it, but this is a huge job, and the activex slows me down.

View Replies !   View Related
IS NULL Issue
Hi,

I have a temporary table in SQL Server 2005. I provide a start date and end date and populate the temp table with dates between the start and end dates...
 



Code SnippetWHILE(@StartDate < @EndDate)
BEGIN
  IF LEFT(DATENAME(dw,@StartDate),3) NOT IN ('SAT','SUN')
    INSERT INTO @temp(myDate) VALUES(@StartDate);
  SET @StartDate = DATEADD(d,1,@StartDate);
END
 
 
 
I want to compare these dates in the temp table with those in another table - NewsItemTable.
Then I want to return those dates that are not in NewsItemTable...





Code SnippetSELECT LEFT(DATENAME(dw, te.myDate), 3) AS 'Date Uploaded'
FROM @temp AS te
LEFT OUTER JOIN
NewsItemTable AS t
ON t.NewsDate = te.myDate
WHERE t.NewsDate IS NULL
 
 
I provide the below dates:
@StartDate = '02/27/2008 00:00:00'
@EndDate = '02/29/2008 00:00:00'

The dates returned are...
Wed 27 Feb 2008
Thu 28 Feb 2008

However, these dates exist in the NewsItemTable!!

The IS NULL is not working.

Any ideas???

Thanks.
 

View Replies !   View Related
Strange Printing Issue With Default Printer
Very strange problem here with Reporting Services SQL 2005 SP1 with latest patch KB 918222 on Windows Server 2003 SP1. The clients PC are Windows XP both SP1 and SP2 and that's not affecting our problems.

When my default printer is set to the "Printer 1" (HP LaserJet 9000), when i look a report's into the Report Viewer and i click on the print button (printer image button that use the ActiveX RSClientPrint), i let the default printer to "Printer 1". In this case, the report is printed correctly.

Again with my default printer set to "Printer 1", i look the same report into the Report Viewer and i click on the print button again. This time i select another printer "Printer 2" (an older HP LaserJet 8000 DN) and the report is printed correctly.

Now the problem is when i set the "Printer 2" as default printer. If i go to the Report Viewer and i click on the print button for the same report that was printed correctly before, now the report seem's to be ok into the preview before i print, but when i send the job to the "Printer 2" the report require a unknown paper format, so the "Printer 2" ask paper into the tray 1 (manual feed) and the result of the printed report is not good. The right and the bottom seem's to be cutted. I tried to reduce the report dimension without good results. The report is always cutted to right and bottom and the printer ask paper into the tray 1 (unknown paper).

Is't not the end, if i print the same report on the "Printer 1" when the default printer is "Printer 2", the report is printed correctly.

I have tried to use another printer than the "Printer 2" to see if the problem is the printer. I tried the "Printer 3" (HP LaserJet 9050) as default printer, i print the report on this one and i have the same problem than with the "Printer 2". And if i set "Printer 1" as default and i print on the "Printer 3" the report is printing correctly.

For all the printers i have the latest drivers. The same problem is occuring on all other PC other than mine.

We have checked the printers configuration and all seem's to have the same configuration.

Can this be a bug with the ActiveX RSClientPrint or what ?

Note that all previews before printing are good with all printers, but with the "Printer 2" and "Printer 3" set to default, the printed report was not the one i saw into the Preview.

Below a link to a sample report that we have. All our reports have the same global dimensions.

http://pages.videotron.com/poulinst/report.zip

 

 

View Replies !   View Related
Default Member And Time Series Issue
I am building a time series model from an existing cube. I set the default member of the Time dimension to the last month with data in the cube script as follows:



Code Block
Alter Cube CurrentCube
    Update Dimension [Date].[Month],
    Default_Member = Tail(EXISTS([Date].[Month].[Month].Members, ,'')).Item(0); 
 
 
Much to my surprise however, when I process the mining structure, it contains only one case €“ the one that corresponds to the default Date member. If I remove the Alter Cube statement, then the mining structure contains all dates as it should.
The processing query is:



Code Block
SELECT  DATAID ( [RPM].[Profitability Measures].[$Product.Product Reporting Group] )
  AS [sures0_0], KEY ( [RPM].[Profitability Measures].[$Date.Date Sur Key],0 )
  AS [sures0_1], AGGREGATE ( [RPM].[Profitability Measures].[Profit] )
  AS [sures0_2], AGGREGATE ( [RPM].[Profitability Measures].[Profit R12] )
  AS [sures0_3], DATAID ( [RPM].[Profitability Measures].[$Date.Date Sur Key] )
  AS [sures0_4]
   FROM [].[]
  GROUP BY [sures0_0],[sures0_1],[sures0_0],[sures0_4]
  ORDER BY  DATAID ( [RPM].[Profitability Measures].[$Product.Product Reporting Group] ) ASC
 
 


What can be done to overwrite the default member so processing a Time Series structure brings in cases for all dates in the cube?
 

View Replies !   View Related
Simple NULL Issue
I have the following Line of code in my .vb file
Dim ParentYID As New Guid(Request.QueryString("YID"))
Sometimes the QueryString will be NULL, and I get an error that says "Value cannot be null"
I want it to work with NULL's because the stored procedure that I pass this variable to accepts Null's and gives me the correct response
Here is what the entire block of code looks like:
 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  Dim ListItemsAdapter As New YagDagTableAdapters.ListItemsTableAdapter()  Dim ListItems As YagDag.ListItemsDataTable  Dim ParentYID As New Guid(Request.QueryString("YID"))
  ListItems = ListItemsAdapter.GetListItems(ParentYID)
  For Each row As YagDag.ListItemsRow In ListItemsResponse.Write("List Item: " & row.ItemName & "<br />")
Next
End Sub

View Replies !   View Related
Null Value In Column Issue
 

I have a flat file source. I tried to use a conditional split, which if found any null values in a column of data type string  then send it to error output, other wise send the rows to the db table. I used !ISNULL( <<Field Name>>) , but every time it's send all the column to the DB table, even the value in the column is null. Can some one give me some idea about what should I do?
 
Any help is appreciated.
 
Sabina_2008

View Replies !   View Related
Performance Issue Of Varchar Or NULL
We use SQL 7.0 and I want to know some guideline for choosing varchar or char data type. I heard that varchar takes more computation than char, then we should use all character type as a char. If then, it will takes more storage, so Is there any guideline for choosing varchar data type?
And also, Is the NULL value type same as varchar data type ?

View Replies !   View Related
Issue Inserting Null Value Into A Formview/gridview Control
Hi,
My formview or gridview control stops updating or deleting a record once the record has a null value.
I have table tblTest with the following
pkID int NOT NULL **IDENTITY COLUMN** string1 varchar(30) string2 varchar(30)
I then create a SqlDataSource with the statement:
Select * From [tblTest]
I have the insert, update and delete statements generated, and choose optimistic concurrency. I add a couple records of dummy data.
I then drag a Formview control onto the page, and bind it to the SqlDataSource I just created. I then fire it up in my browser, and I can then update, insert and delete records. However, as soon as I update a record with a null value, I can no longer update or delete that record.
So, if I had a record in my FormView like:
string1: foo string2: bar
I can update and delete normally. And when I update to:
string1: foo string2:
the database correctly inserts a null value into string2. However, once that null is in the record, I can't change anything about the record. If I try to delete the record, the FormView will then display the previous record, but I can still page to the record that should have been deleted, and it still exists in the db. If I try to update the record, the edits I make will not keep and the process will fail silently.
What am I doing wrong? Should i be binding to a different object?
Regards,
Chris

View Replies !   View Related
SQL 2005 Select Into Temp Table Then Insert Causes Null Issue
Here is the scenario that I cannot resolve

 

CREATE TABLE [dbo].[tEvents](

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

[EventName] [varchar](1000) NOT NULL,

CONSTRAINT [PK_tEvent] PRIMARY KEY CLUSTERED

(

[EventID] ASC

)WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

 

CREATE TABLE [dbo].[tEventSelections](

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

[EventID] [int] NOT NULL,

[StatusPID] [int] NOT NULL,

CONSTRAINT [PK_tEventSelections] PRIMARY KEY CLUSTERED

(

[EventSelectionID] ASC

)WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

 

then try this

 

SELECT e.eventName, es.statuspid

INTO #tmpTable

FROM tEventSelections ES

INNER JOIN tEvents E

ON E.EVentID = ES.EventID

INSERT INTO #tmpTable (eventName) values ('Another One')

DROP TABLE #tmpTable

 

this causes a null insert issue

(0 row(s) affected)

Msg 515, Level 16, State 2, Line 7

Cannot insert the value NULL into column 'statuspid', table 'tempdb.dbo.#tmpTable___________________________________________________________________________________________________________000000000130'; column does not allow nulls. INSERT fails.

The statement has been terminated.

 

So how do I allow the null, as the not null is coming from the ES table. But I want to allow the insert to happen without having to create the table first, this code works in SQL 2000 but fails in 2005, inserting all fileds into the insert also has it's own issues as some of the fields are delibertly left blank so in some circumstances the data returned to a grid displays correctly.

 

This method has been used in quite a lot of stored procedures and will be a nightmare to correct if each has to be edited.

 

One example of the use of is to return a dataset and then add a row at the bottom which is a sum of all the rows.

 

Regards

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


go

declare

@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

where

DR.[FileName] = @inFileName

AND DR.FileSize =@inFileSize

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

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

AND DR.[ID]= @ID

)

go

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:

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

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
OpenRowset
I can successfully retreive data using:

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

EXEC('SELECT *
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
Openrowset
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.


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

View Replies !   View Related
Openrowset
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
Openrowset
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.

SQL -
UPDATE OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'InternalRunTestATest.mdb';'admin';'', TestTable)
SET AName = 'Success' Where AID= 10

AID is the primary key.

Sridhar

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?

Stacy

View Replies !   View Related
OpenRowset
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

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.

View Replies !   View Related
OPENROWSET
Hi!
I decided to use OPENROWSET for importing data from an excel file into a sql table.When I import the data,I have a problem:

-not all the data is imported to my sql table
-some values in the sql table are different from the ones in the excel file
For example,a value that in the excel file is:87878787 will be in the SQL table:8.78788e+007.

Can somebody pls tell me what's the problem

View Replies !   View Related
Cannot Insert The Value NULL Into Column 'OrderID' -- BUT IT IS NOT NULL!
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

View Replies !   View Related
Should SQL Concatenation Return A Null Value If One Field Is Null?
In an SQL statement which concatenates several fields I get a nullvalue returned if any one of the fields are null.Is this to be expected?For example :SELECT tblMember.memberAddress + ' ' + tblMember.memberTown + ' ' +tblMember.memberCounty + ' ' + tblMember.memberPostCode + '<br> ' +tblMember.memberCountry + '<br> ' + tblMember.memberInstitution ASconcatAddress FROM tblMember WHERE memberSurname='Cardy'returns a null value if eg tblMember.memberInstitution is null.Am I doing something wrong, if so I would be grateful for your help.Otherwise it would be useful to know if there is some kind of workaround which can be used within the SQL statement (which is beingused in a stored procedure),Best wishes, John Morgan

View Replies !   View Related
Help Altering Database Table From NULL To NOT NULL
I would drop and add the table but the data can't be deleted. So if anyone could help with the statement it would be greatly appreciated. Thanks

View Replies !   View Related
Sp_option... 'concat Null Yields Null'
I'm trying to set the concat... option to OFF
all the time and in all my databases

I tried this command

USE master
EXEC sp_dboption 'DatabaseName', 'concat null yields null', 'FALSE'

but it doesn't change anything

Select NULL + 'TOTO'
----> NULL

(it should be 'TOTO')

View Replies !   View Related
&#34;concat Null Yields Null&#34; Does Not Work
I am trying to get my SQL7 server to set the db option "concat null yields null".
The command passes without error and I even confirm the value under sysdatabases table.
The only way I can get Null to concat is to set the compatibility level back to 6.5, but this is not what I want to do.

Have anyone been successful in setting the sp_dboption "test","concat null yields null","false" to work?

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved