Correct Procedures For Testing Against NULLs From SQL Server

Jul 7, 2005

Hi all,

I have some C# code that is pulling data from a database where a majority of the values being retrieved are NULL , yet their initial column data types are both string and int, which means that I have to temporarily store these NULL's in int and string data
types in C#. Later on in my code I have to test against these values,
and was wondering if I am doing it correctly with the following code.

The following statement the variable or_team_home_id is of a string data type, but may have had a NULL value assigned to it from the database
if (!or_team_home_id.Equals(DBNull.Value)) {}

The following statement the variable or_manager_id is of a int data type, but also may have a NULL value assigned to it from the database.
if (!Convert.IsDBNull(or_manager_id)){}

Are these the correct way to test against NULL values retrieved from
teh database and that are stored in their respective data types.

Tryst

View 1 Replies


ADVERTISEMENT

Testing Permutations Of Nulls And Not Nulls

Feb 17, 2008

is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?

View 4 Replies View Related

SQL Views And Testing For Nulls

Jul 6, 2004

Pop quiz hotshots:

I am creating a View in SQL2000 where i concatenate values from multiple tables. My problem is that if one of the values is null then the whole string is null, so I would like to know how to test for nulls within the Select part if the View.

Select
Titles.Title + ' ' + User.Forename + ' ' + User.Surname AS FullName,


For example;
If User.Forename is null, I still want to return Titles.Title and User.Surname as a concatenated string.

I’ve tried I few things like places if statements within the Select part but I can’t get it to work.

Many thanks

View 3 Replies View Related

Stored Procedures -- Correct Practice?

Jan 16, 2008

Hi,

I have 3 tables: authors, companies, and countries.

I have a stored procedure defined as:

PROC addAuthor(AuthorName, CompanyID, CountryID)
INSERT INTO authors (author_name, comp_id, country_id) VALUES( authorName, CompanyID, CountryID)
END PROC

....So the ID of the company and the country are the parameters. This makes it easy since I can just do a direct INSERT statement into the authors table.

Would it be better practice if the parameters asked for the name (not the ID's) of the company and country instead? This way we do not have to memorize the ID's for everything. So for example

PROC addAuthor(AuthorName, CompanyName, CountryName)
SELECT COUNT(*) FROM companies INTO v_numRows WHERE companies.company_name = CompanyName
IF v_numRows = 0
-- error
END IF

SELECT COUNT(*) FROM countries INTO v_numRows WHERE countries.country_name = CountryName
IF v_numRows = 0
-- error
END IF
END PROC

yeah, I might have the SQL syntax wrong up there (not entirely sure how it works for variables) but the gist of it is that I will need to include validation code within the stored procedure.

What do you guys think of this? Which method do you prefer? Is there a significant performance decrease with using the second method?

Thanks for any insights

View 8 Replies View Related

Determine Correct Flow Of Stored Procedures

Mar 12, 2015

We have some pre-defined stored procedures(around 4-5) which deletes/ truncates the tables.

- Is there a way I can know the correct order of running the SP.May be I should check that child tables are deleted first and then the parent tables.

- How can I test the same. Since I am not aware of the flow thereby should I try running with NO eligible records for deletion. Will this ensure that I have sufficient privileges for the SP or do I need to delete a record (may b an older record).

View 3 Replies View Related

Sysdepends And Compiling Stored Procedures In The Correct Order

Nov 9, 2000

Hello:

We are presently testing various upgrade scripts to our current application which is scheduled to shortly be upgraded to mssql 7.0. We are testing under mssql 7.0,sp 2.

As it works now, I receive some existing scripts that have been modified and some stored proceures that are new. For the existing stored procedures, I usually take my best guess as to what the order of creation will be, test it in my script for recompile(actually all are dropped first and then the guesswork on the creates). This is usually trial and error as I run the script, see any sysdepends errors such as:

"CREATE PROCEDURE: ep_invoiceheaderformat_spv0101
Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'ep_assumepay"

And then move the order of the create procedures around in the script and try again until I get a clean run in a test database I use just to syntactically test the scripts.

I looked at the sysdepends table for the database and pretty much decided that the object numbers and stuff was pretty much incomprehensible to me.


Alternatively I could compile each one separately but I would have the same problem subsequently trying to generate a script of the al of the create procedures... in the right order which would not

My question is:

1) Is there a way I can read and understand what the data means in sysdepends?

2) Figure out a way to utilize the data there to create or generate the create stored procedure text in the correct clean compile order?

3) Any other suggestions?

Any information which can be proveded will be greatly appreciated. Thanks.


David Spaisman

View 1 Replies View Related

How Can I Execute Stored Procedures With Correct User Rights?

Sep 3, 2007

Hi,

I have a problem with sp execution.:







objects of [dbo]

Tables of [nuran]

Grants of [nuran]


[dbo].tabloA

[nuran].tmptabloA

Select,insert, update on [dbo].tabloA


[dbo].tmptabloA



Deny for [dbo].tmptabloA


[dbo].sp_yordam



Grant for executing [dbo].sp_yordam









(1)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN

BEGIN TRANSACTION @Tran1
€¦€¦€¦€¦€¦. €¦€¦€¦€¦€¦€¦ €¦€¦€¦€¦.

INSERT INTO [tabloA]
(, ,)
SELECT ,,
FROM [tmptabloA] WHERE ......

€¦€¦€¦ €¦€¦€¦ €¦€¦€¦.

DELETE FROM [tmptabloA]

COMMIT TRANSACTION @Tran1


When user [nuran] execute the procedure sp_yordam by a VB program, the procedure use [dbo].tmptabloA not [nuran].[tmptaboA]. If there are data in the [dbo].tmptabloA, the procedure insert data to [dbo].tabloA from [dbo].tmptabloA. But when I checked user name in the procedure during execution, the user was [nuran].

If I write the procedure like that:

(2)
create PROCEDURE [dbo].[SP_tmpSil]
AS

declare @tablo1 as varchar(50),
DECLARE @sil as nvarchar(max)
select @tablo1='[tmptabloA]'

SELECT @sil = ' DELETE FROM ' + @tablo1 + ';'
EXEC (@sil)

END

And it executed by user [nuran],then it used the correct table [nuran].tmptabloA

Is there any way to use user€™s table in an stored procedure without using the user name :
(3)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN

BEGIN TRANSACTION @Tran1
€¦€¦€¦€¦€¦. €¦€¦€¦€¦€¦€¦ €¦€¦€¦€¦.

INSERT INTO [tabloA]
(, ,)
SELECT ,,
FROM [nuran].[tmptabloA] WHERE ......

€¦€¦€¦ €¦€¦€¦ €¦€¦€¦.

DELETE FROM [nuran].[tmptabloA]

COMMIT TRANSACTION @Tran1


I don't want to use (2) and (3) code methods, I prefer to use (1) script. Is there any compilation method, or any aditional way for using script (1) with correct user rights?

Thanks a lot

Nuran

View 4 Replies View Related

Column Allows Nulls I Want To Change No Nulls Allowed

May 16, 2006

When i do a select on my emplee table for rows with null idCompany i dont get any records

I then try to modify the table to not allow a null idCompany and i get this error message:

'Employee (aMgmt)' table
- Unable to modify table.
Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails.
The statement has been terminated.

This sux

View 4 Replies View Related

Do Not Keep NULLS Using SSIS Bulk Insert Task - Insert Empty Strings Instead Of NULLS

May 15, 2008

I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.

The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.

The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.

So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).

I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.

Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?

From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.

View 2 Replies View Related

SQL Server V7.0 Testing Software

Jan 5, 2001

We are looking for a software program that will allow us to test our SQL server. We are looking for the most thorough tool that is available. I was asked to post a message, so I do not have too many details.

Thanks!

Jeff

View 1 Replies View Related

Newbie Set Up And Testing Server

Mar 31, 2003

My previous experience is with php and MySQL.

I currently have Apache, MySQL and php running on my local machine to enable me to test php code on localhost

I've been asked by a colleague if I'll work on an SQL 2000 database they're having trouble with. They said they will provide Visual.net, SQL 2000 and any other software I need.

I'm completely in the dark on this, but have some starting questions:

To test my code, will I need to set up a sever on localhost?

Will I need other software than Visual.net and SQL 2000? I'm using DW MX for my web stuff.

Whre can I find a basic introduction to ASP / SQL 2000 in the form of a tutorial?

How fast does this combination run? The database has 25,000 rows and 6 columns. What would be a reasonal expect time for a result?

Any help appreciated

H

View 3 Replies View Related

Testing Connections To SQL Server

Jun 30, 2006

Is there a way to test to see if there is a connection to a database in SQL through programming or dos commands?

View 4 Replies View Related

Stress-Testing SQL Server

Jan 28, 2005

What do you recommend for stress-testing the performance of key stored procedures (they have been identified) for our application? The parameters can be programatically selected, for example:
Select 'exec my_proc @id = ' + Cast(id As varchar)
From myTable
Where foo = 'bar'
I have the Support Tools Available For Stress Testing & Performance Analysis (http://www.microsoft.com/downloads/details.aspx?FamilyId=5691AB53-893A-4AAF-B4A6-9A8BB9669A8B&displaylang=en) from Microsoft's site and they are pretty good.

Recommendations appreciated, and thanks in advance!

View 2 Replies View Related

SQL Server Admin 2014 :: Failed To Prepare Storage For Testing On Node Server Name

Feb 23, 2015

I Run All checks for Validation cluster.I get Error On Disk Lists And Validation failed.With This error : Failed to prepare storage for testing on node "server name" The security account manager (SAM) or local security authority (LSA) server was in the wrong state to perform the security operation.

View 2 Replies View Related

MSDE For Testing, SQL Server For Deployment...?

Jan 9, 2004

I'm going to be starting a web application this week. I have quite a bit of experience with Access Databases, and I've also used MySQL (back in my PHP days, thank GOD for ASP.NET).
The server we use to host our websites allows us to use SQL Server databases, so I want to move in that direction, but I'm not really sure where to start.
If I download MSDE, and start a database with it on a site I will be developing locally (on my XP box), will it be difficult for me to transfer the database (or layout of the database) to a production server running SQL Server. I know they're basically the same database, but I'm just afraid to start with an MSDE database and then get the site nearly complete, and then not know how to transfer it to a SQL Server DB.

I'm particularly interested to know if the majority of the database creation will be done through queries (similar to MySQL) because I figure if it is, then I can use the same queries to create the database on both the local and production server.

I just want to make sure that downloading MSDE will be a step in the right direction.

Perhaps links or tutorials or some information on things to watch out for as I take on this process would be so greatly appreciated.

Thanks very much.

View 2 Replies View Related

Testing Linked Server Connection

Jan 16, 2001

I have a server with several linked servers. Before I execute sql against any of these linked servers I want to check to make sure the connection is active.

I created a small stored procedure that takes the server name. I am trying to get it to run a simple select statement. If the select statement runs without errors (meaning the linked server is active) I want to return a value for success. If the select statement fails I want to return a value for a failure.

The problem is that I am having trouble with the error. When I shut down the linked server and run the select statement I get the following error returned:

Server: Msg 11, Level 16, State 1, Line 1
General network error. Check your network documentation.

The stored procedure I have only returns this message and does not send the return value that I set. How do I get my procedure to return a failure value instead of the following error above? Is there a better way for me to check for this type of error?

Thanks

View 1 Replies View Related

SQL Server 7.0 Beta/Testing ASP Application

Apr 7, 2004

Hi Group,
I'm trying to test an ASP application in 'isolation' and seem to be having problems with connecting to the Server.
The setup is EXACTLY like the production environment except for the fact that I am using the Beta version of SQL.

The error refers to SQL Server not existing. I get this error also if I try to open the Query Analyzer without FIRST starting the Services via the Service Manager - so I think it is probably something wrong with the Beta Installation rather than using the BETA version as a back-end.

I've tried using the Computer name as the Server to connect to as well as 127.0.0.0 - neither seem to want to connect.

I can connect manually and see the Database objects. I've even connected, left the connection open and tried running the application again. I still get the same error.


Has anyone experienced this before? i.e not being able to connect to a BETA version of SQL to test an ASP application locally.



TIA

View 3 Replies View Related

Setting Up A Testing Server To MS SQL Using Dreamweaver MX

Apr 7, 2004

Hi,
I'm new to the whole MS SQL server thing, have been using Access in past developments via the development platform in Dreamweaver.

Now I'm making the leap to MS SQL and still want to use Dreamweaver to handle some of the development aspects, does anyone know how or what i would need to do to have a direct connection with the server in dreamweaver?

I've setup an ODBC connection with my hosting providers already.

THANKS!

View 3 Replies View Related

Testing A Connection To A SQL SERVER 2005 DB

Sep 18, 2007

What is the syntax to telnet a database?I triedtelnet <hostname> 1433 - but get this error:telnet ws1234 1433Connecting To ws1234...Could not open connection to the host, on port 1433: Connect failedMy computer name is ws1234.comHow do I test a connection to a sql server database instance?Thanks,VVvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvWW

View 2 Replies View Related

Testing Mode 90 In Sql Server 2005

May 10, 2006

Our database is in Sql Server 2005 Mode 80 at the moment and we need to switch it to Mode 90. We need a lilst of all incompatibilities that exist.

Essentially, we need a list similar to what the Upgrade Advisor provides for 2000 databases. The only problem is our DB is already in 2005 mode 80. Switching to mode 90 is easy but does not identify all potential problems for us.

Is there a tool that is similar to the Upgrade Advisor that we can run on 2005 Mode 80 databases?

Thanks

View 1 Replies View Related

Testing Using SQL Server 05 Developer Addition

Feb 2, 2008

Are there any differences between the Developer Addition and Enterprise Addition of 64 Bit SQL Server 05 that would prevent me from testing full capabilities of my production Enterprise Addition in a QA environment utilizing Developer Addition? Any known restrictions on number of DB connections, memory, CPUs etc??

View 4 Replies View Related

SQL Server 2005 32bit To 64bit Testing

May 20, 2008

Hi,

Recently, we have migrated our database from 32bit to 64bit. So far the application is working fine.

But I want to confirm that by any tool or automated process.

Is there any way or tool to check that the migrated data is correct and also to check if migrated database objects will work fine in 64 bit environment.

Any timely help would be greatly appreciated !!

Thanks,
Bala

View 4 Replies View Related

SQL Server Admin 2014 :: Migration And Testing Workload?

Aug 27, 2014

We are using Sql Server 2008 R2. We are planning to move to 2014 either through in-place o side-by-side. Mostly side-by-side.

Here first we want to test that 2014 and availability groups. First we are doing in dev box to test. Also we need asynchronous replica because just we need to use that as reporting server.

We want to take work load. For the dev boxes, the applications doesn't connect. Then in that case what the people will do.

If We take database backup from prod to that dev and run some queries in the loop and run the trace for some time and do either in-place migration or side-by-side migration and use the same back to restore and doing the running the same queries and compare this trace to previous trace will work?

View 0 Replies View Related

Concatenate Nulls In SQL Server

May 17, 2004

I have a problem with a view in MS SQL Server 2000. The View concatenates 3 fields (prefix, partnumber, suffix) using hte "+" operator and one or more of these fields may sometimes be null. The problem is that a null value in any of the three fields causes the concatenation to return null even if there are valid values in on or both of the other fields. I thought I might be able to work around this by creating a view containing CASE statements to render the null values as zero length strings, which concatenate properly (e.g., "SELECT CASE WHEN PREFIX IS NULL THEN '' ELSE PREFIX END 'prefix2' FROM [table_name]"). But SQL server will not let me save a view containing a CASE statement. Anybody know how to resolve this problem?

By the way, I also tried to use UNION views to work around this but SQL server 2000 will not let me save views with UNION sataments even though it runs them properly when views with UNION statements that were created in SQL server 7.0 are imported. What's up with the inability to save a view just because it can't be rendered in the gui pane of the query builder??

View 7 Replies View Related

SQL Server 2008 :: Testing A Script In SSMS Supplied From SSRS

Oct 2, 2015

Because of a performance problem, somebody has given me a script which came from a SSRS report.

The code as supplied does not work when multivalued parameters are used.

Testing/tuning/building in SSMS is far superiour than in SSRS. So that's why I like to use SSRS for building the code/script/sql-statement.
Offcourse parameters have to be set correctly. (That is no problem).
Splitting of the multivalued parameter is not a problem either.

View 5 Replies View Related

Importing Report Content Direct To SQL Server For Testing Purposes

Jan 8, 2007

Hi

I have what seems a simple requirement. We want to import the contents of a SQL Server 2005 Reporting Services report into a SQL Server 2005 database, in order to perform some checks on reports displayed to users. Is there an easy way to achieve this? XML would seem appropriate, but I can't find a step-by-step guide on how to achieve this. Any pointers/suggestions would be appreciated.



Thanks



Neil

View 4 Replies View Related

Testing A Stored Procedure With Output Params In SQL Server Managment Studio

Sep 25, 2007

I have an SP like this (edited for brevity):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TESTING_SP]
@Username MediumText,
@Password MediumText,
@UserKey int OUTPUT,
@RoleKey int OUTPUT,
@UserGroupKey int OUTPUT,

AS

BEGIN

SELECT
@UserKey = UserKey
FROM UserProfile
WHERE Username = @UserName
AND [Password] = @Password
END

I want to execute this sp in Managment Studio (MS) and see what is being returned but I'm getting this error:

Msg 201, Level 16, State 4, Procedure TESTING_SP, Line 0
Procedure 'TESTING_SP' expects parameter '@UserKey', which was not supplied.

How do I set up the output parameters and then select the values in MS for testing purposes?

Thanks a ton for helping a noob.

View 2 Replies View Related

Including Columns With SQL Server Allow Nulls Checked

Jun 13, 2006

Why is it that when I include a column from my SQL Server database table, which has it's Allow Nulls checked, in the data source of a control that the record becomes not update-able?  How do I get around this?

View 1 Replies View Related

SQL Server 2012 :: Fetch Next Value Ignoring Nulls?

Jul 4, 2014

I'm trying to find the previous / next values of a field when it is having null values. I'm not able to use lag / lead as they don't ignore nulls.

declare @t table (v1 int , v int, v2 datetime)
insert into @t values(4,10, '2014-01-01 10:00:00')
insert into @t values(5, 20,'2014-01-01 10:05:00')
insert into @t values(6, null, '2014-01-01 10:10:00')
insert into @t values(7, null,'2014-01-01 10:15:00')

[code]....

The next values are having nulls. i'm trying to get next value of 'v' ignoring nulls.

View 6 Replies View Related

Correct Practices With SQL Server

Sep 5, 2007

I am hoping someone can give some advice on the following things:

I have read a few times about a data access layer in an n-tier application. I am assuming that this should be done
using sprocs. Is there an advantage of using sprocs instead of views ( in situations where the same thing could
be accomplished using either)? Will a sproc run faster than a view? Can any share any info?

Are sprocs best suited for data access and to enforce business rules?

I know SQL Server has reserved words that shouldn't be used. I am wondering what the best thing to do is
in the following situation? What is the best way to handle storing a customer or clients address? I am working from a book that shows the name of a column as "Address". I have found that with SQL Server 2005
Express that this is a reserved word(it is shown in blue in the query window). I want to keep my names short. I am trying to avoid a name like "StreetAddress". Is my book teaching bad habits?
...........................................thanks...........................................................

View 3 Replies View Related

Including SQL Server Allow Nulls Fields In Updateable Controls

Jun 13, 2006

When I include a field from my SQL Server database, which has it's Allow Nulls value checked, in the data source of any type of control with it's Enable Editing property check, I then can not edit the record!  If I remove the Allow Nulls field I can then edit it!  What am I missing here?

View 1 Replies View Related

SQL Server 2008 :: Column Does Not Allow Nulls / INSERT Fails

Jan 4, 2010

I'm a developer working on PHP - MSSQL 2008 combined platform. While running a procedure from a webpage I'm encountering the error as below.

My Query:
DECLARE @ReturnValue INT
EXEC @ReturnValue = S_AccountsBatchActivation @FirstName='abc', @LastName='abc', @Address='abc', @City='abc', @State='abc', @Country='India', @Phone='1', @BatchId='6502'
SELECT ReturnValue=@ReturnValue

Error
Cannot insert the value NULL into column 'TariffId', table 'tempdb.dbo.#tbl
column does not allow nulls. INSERT fails.

Though the same query when run from SSMS runs fine:

Execute S_AccountsBatchActivation
@FirstName='abc',
@LastName='abc',
@Address='abc',
@City='abc',
@State='abc',
@Country='India',
@Phone='11',
@BatchId=6502

[code]...

On my end I've tried out with ANSI_NULL AND ANSI_NULLS settings being both ON and OFF but it didn't worked.

View 9 Replies View Related

SSIS Import Text File To SQL Server With Nulls?

Jan 23, 2007

I have a text file I am trying to import into SQL Server using OLEDB connection.

It's a fixed field text file, ragged right format. One of my columns maps to a numeric column in the DB. In some spots in the file, it is blank, in others there is actual numeric data.

I can't get it to import. If I set the text file column to numeric, I get an error "That value could not be converted because of a potential loss of data." If I set the text file column to string, I get a similar error from the OLE DB provider, "Invalid character value for cast specification"

I have tried telling it to retain nulls in the data flow and the other way as well. Can someone tell me what I am doing wrong?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved