How To Detect NULL In SQL-table With VB.net ?

Oct 29, 2005

Hi,I want to check with VB.net whether a field in a SQL-table is NULL or not.This code doesnot work:If xxx = NULL then<statements>End IfI got the error, that NULL is not supported ?How do I code the check ???Help is appreciated, Gr.

View 2 Replies


ADVERTISEMENT

Uniqueidentifier Field In SQL With Null Value - Cannot Detect This In Code... Why?

Nov 3, 2007

OK, I use asp.net membership and have created a new table that links together related users by use of the uniqueidentifier field as a foreign key.  However, it is possible to link to a not-yet existent user (by their email) so that when that user creates an account and gets a guid, it will be updated into my table of related users and finalise the link.  Trouble is, I'm trying to stop people adding the same "pending user" multiple times, so in my BLL logic I look for the entered email address for the current user and I want to check on the foreign key (the GUID field) so that I can tell the user that they've already added this user, but they haven't signed up yet (if they haven't got a foreign user key with a guid in it, they haven't signed up yet).
However, when I check the foreign key guid value (shows as null in sql enterprise manager) for null values, it never goes down the "true" logic channel... what I mean is that I've debugged it, hovered over the item I've read (it says it is system.dbnull, which it is) and yet when I do "isDBNull(myGUID.Value)" it never equates to true!
Why not?

View 1 Replies View Related

Detect And Convert An Empty String (from Textbox) To Null?

Jun 9, 2007

 Hi all,I have this code that I use for my Search function:SELECT DISTINCT [MUSIC_TITLE], [MUSIC_ORIGINAL_SINGER], [MUSIC_PERFORMER]FROM t_musicWHERE (@MUSIC_TITLE IS NULL OR [MUSIC_TITLE] LIKE '%' + @MUSIC_TITLE + '%') AND (@MUSIC_ARTIST IS NULL OR ([MUSIC_ORIGINAL_SINGER] LIKE '%' + @MUSIC_ARTIST + '%' OR [MUSIC_PERFORMER] LIKE '%' + @MUSIC_ARTIST + '%')) But right now if I don't enter anything in one of the textbox (2 have two, either of them can be left empty), the above Sql statement doesn't return anything since ADO.NET can't tell an empty textbox and treat it at null... So anyone please help me how to detect an empty textbox and set that to null for the above SQL statement to work. (It work in SQL Manager Studio, when I set one of the parameter = null.) I'm very new to ASP.NET stuffs, so if someone can help me to convert that function to code-behind and help me to call it from the .aspx, that would be even better as I don't want to put the code in my .aspx page... But I'm not quite there yet. Thank you all,Kenny.  

View 19 Replies View Related

Why Bcp Cannot Detect Temporary Table ?

Jul 22, 2006

hi, good day, i facing a problem


Code:


CREATE PROCEDURE [SP_TEST]

AS

SELECT *
INTO #TMP_CUSTOMER
FROM O_CUSTOMER

-------------------------------------------------------------------------
SELECT DISTINCT
COUNT(CUST_CD)

FROM
#TMP_CUSTOMER
GO




when i run in query analyzer , using "exec SP_TEST" , it work and display result

but when run
Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:TEST.TXT -c -Slocalhost -Usa -Ppassword'

it give error "SQLState = S0002, NativeError = 208"
and show
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.


thanks for guidance

View 4 Replies View Related

Querry 2 Detect Doubles In A Table

Jul 6, 1999

Hi,


I have a table which contain a field called 'company' of type 'int'.
I want to make a querry which returns all doubles ... for example:

I have: after querry the return must be:

company company:
1 1
3 3
3 3
11 3
7 2
3 1
2 2
1
4
2
5

Thank you,
Sebastian

View 1 Replies View Related

Detect Changes On Indexes In A Published Table

Jul 27, 2006

Hi,

Regarding to my previous post at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=553652&SiteID=1, just wondering if there's a way to detect any changes which is made on the existing indexes in a published table so that the device can determine whether reinitialization of subscrption is needed before every synchronization.

Cheers,
Justin

View 3 Replies View Related

How To Detect That A Sqlserver Table Row Has Foreign Keys And Cannot Be Deleted?

Mar 27, 2006

What is the best way to detect that a sqlserver table row has foreign keys and cannot be deleted?Thanks,Keith

View 1 Replies View Related

Help: How To Detect Inserts, Updates, Deleted On A Table From Within C++ Application?

Jul 20, 2005

Hopefully someone can at least point me in the right direction for moreresearch (e.g.: correct terminology). My only previous experience was justdumping data into a database using ODBC, and that was some years ago so nowmostly forgotten.I need to write an NT Service/Application (in C/C++) that will be gettingdata sent to it via SQL Server 2000. The data will arrive in my SQL Server(read-only access), via replication of tables from another remote SQLServer.My application needs know when new row are inserted, or updated so it can toread this data (needs to be quick/timely so hopefully no polling) to theninterface with other remote proprietary systems.T.I.A.PS: If you can recommend appropriate books on SQL Server 2000 that wouldalso be useful.

View 2 Replies View Related

SQLSERVER 2000,How Can I Detect The Last Time Where The Table Has Changed (updated)?

Jan 2, 2008

Hi, I wonder where I can see last update over a table. Maybe sqlserver write some information about every table.

For example there are 300 tables in a database an I want to execute some query to see the last tables updated (list of tables updated > 2008-01-02 )

Hope to be clear,
Best regards
Ariel

View 4 Replies View Related

Help Altering Database Table From NULL To NOT NULL

Jun 28, 2004

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

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

Sep 20, 2006

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 3 Replies View Related

How Do I Enter NULL In A Table Cell In The Enterprise Manager UI For Table Data Entry?

Sep 9, 2005

I have a column defined as smalldatetime. Default length (4), and "allow NULLS" is checked.In the Enterprise Manager UI, when i enter data into that table row, if i just tab past that column, all is well, and the value is represented in the UI as <NULL>.The problem comes once i ever enter a date into that column.  Say i have entered a date (all is well), and now i want to remove that entry and go back to NULL (after the date value has been committed, different entry session, say).How is that done?It seems to me, once a date has ever been entered into that column, now, if i try to remove it, i get the error "The value you entered is not consistant with the data type or length of the column, or over grid buffer limit".  I have tried deleting the value, entering spaces, entering the string NULL or the string <NULL>; maybe some other tries as well, but none works, i always get that error message and am not allowed to proceed past that cell until i restore a date value to it.  I want to get back to <NULL>.Anybody know?Thank you.Tom

View 1 Replies View Related

What's The Accepted Way To Retrieve Records In A SQL Table With Null Values Using A Visual Studio 2005 Table Adapter?

Jan 21, 2008

I'm using an ObjectDataSource in Visual Studio to retrieve records from a SQL Server 2005 database.
 I have a very simple dilemma.  In a table I have fields FirstName, Surname, Address1, Address2, Address3 etc. None of these are mandatory fields.
It is quite common for the user not to enter data in Address2, Address3, so the values are <null> in the SQL table.
In Visual Studio 2005 I have an aspx form where users can pass search parameters to the ObjectDataSource and the results are returned according to the passed in parameters.
The WHERE clause in my Table Adapter is:WHERE (Address1 LIKE @Address1 + '%') AND (Address2 LIKE @Address2 + '%') AND   (Address3 LIKE @Address3 + '%') AND (FirstName LIKE @FirstName + '%') AND (Surname LIKE @Surname + '%')
If, for example, I simply want to search WHERE FirstName LIKE ‘R’, this does not return any results if the value of Address3 is <null>
My query is this: Could someone please show me the best way in Visual Studio 2005 to return records even if one of the Address fields is <null>.
For reference, I have tried: Address3 LIKE @Address3 + '%' OR IS NULLThis does work, however itsimply returns every instance where Address3 is <null>  (accounting for about 95% of the records in the database). Thanks in advance Simon
 

View 9 Replies View Related

SQL Server 2008 :: Non Blank / Non Null Value From Table To Update Same Table

Mar 13, 2015

Here is the sample data for table.

create table #sample (emp varchar(max),data1 varchar(max),data2 varchar(max), Rdate date)
insert into #sample (emp,data1,data2,rdate)
values('john','','',getdate())

insert into #sample (emp,data1,data2,rdate)
values('tony','','',getdate())

[Code] ...

I need to update the data1, data2 from yesterday or before but they should be non blank and non null and update to todays data.

It means, I can't update to 2015-03-12 values to 2015-03-13, as they are blanks or nulls.

So I need to go 2015-03-11 and check the values, if they are not blank , i need to update with todays values.

Before the update :

empdata1data2Rdate
john3/13/2015
tony3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015

After the update:

empdata1data2Rdate
johncd3/13/2015
tonyab3/13/2015
john3/12/2015
tony3/12/2015
johncd3/11/2015
tonyab3/11/2015

View 4 Replies View Related

Problem: Must Detect Db Changes

Jul 20, 2005

HelloI´m using MS-SQL Server 2k with a custom application connecting to it.I need a quick and simple way to detect any change (insert, update, delete)to my database (not just to a single table). The purpose is to notifysomehow different instances of the application about the data changes,since they should refresh their local query results.Is there any function, stored procedure, system database entry, etc. I canexploit to get the job done? Can you suggest me the most suitable mechanismto implement this?T.I.A.AndreA

View 1 Replies View Related

How To Detect *.cer Association

May 29, 2007

I am entering to administration of
SS2005 SP1 (Windows 2003) having files mdf, ndf, ldf in
C:Program FilesMicrosoft sql serverMSSQLData
This dir also has two *.cer files.

Apparently no encryption is used


How can I get known what these *.cer files are for?

View 4 Replies View Related

Detect Licensing

Aug 4, 2006

Hello

I am trying to find out if versions of SQL SERVER 2005 we have installed have full or Developer Licenses.

They were installed previously by someone who no longer works here, and we do not the versions on record.

We have enough full licenses, so we just want to make sure the correct ones are set up on our servers.

Anyone know how/where we can find this information?



View 1 Replies View Related

Null && One More Table

Jul 20, 2005

Hello,I want to store two information (so 2 columns) for 2/3rd of the rowsthat will be in a tableand only one information (1 column is suffecient) for 1/3rd of therows of the table.ex:jb_id, previous_jb_id -------- for 2/3rd of the rowsjb_id -------- for 1/3rd of the rowsWhat is better#1) add both the column (jb_id, previous_jb_id ) in the table itself.disadvantage:By this for 1/3rd of the rows, previous_jb_id will be NULL#2) create different table for storing previous_jb_id for requiredrows only.disadvantage:1) more table---> more Joins & more table to update2) more Joins --->slow& more Joins ---> bit difficult to write "select" queries.Thanks in advance.waiting for reply.

View 3 Replies View Related

Detect And Or Delete Dups

Oct 30, 2002

Hassle free locating / reporting or deletion of duplicate rows in a SQL table based on the uniqueness of a column or columns that the user provides with a few nifty hands off features. Keywords: delete unique column record records

(This code may also be viewed at http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=576&lngWId=5


/*Description: UTILITY - Locate in MASTER


Syntax: EXEC sp_RemoveDups TableName, DupQualifierFieldNameList, DeleteDups, UniqueColName, CreateIdentColIfNeeded, StoredProcedureResult
Only the first two arguments are required
For HELP, enter the stored procedures name without any arguments or see the PRINT statements below
NO DELETION WILL OCCUR by default - only duplicate recordset returned. To delete records, pass in a 0 for the DeleteDups argument.

Example: EXEC sp_RemoveDups 'MyTable','LastName,FirstName,HomePhone'

Purpose: Allow removal of duplicate rows where
1. We define what fields qualify the duplicate
2. We select the unique rowid or it is detected automatically else no action takes place

Method:Delete by RowID all duplicate rows except the highest RowID (in alpha-sort sequence)
of each group of duplicates.

DATEBYCHANGE
09-23-2002FrankOriginal v1.0
09-23-2002FrankChanged the name from sp_RemoveDupsByRowID to sp_RemoveDups
10-8-2002Sean P. O. MacCath-MoranMade @UniqueColName optional
Added logic to auto-detect RowGUID and Identity columns
Added logic to check for unique value column if no RowGUID or Identity column exists
Added logic to create a temporary ID field as a last resort if no unique column could be located
Added HELP

*/

CREATE PROCEDURE sp_RemoveDups
@TableName as varchar(50) = NULL,
@DupQualifierFieldNameList as varchar(200) = NULL,
@DeleteDups as bit = NULL,
@UniqueColName as varchar(50) = NULL,
@CreateIdentColIfNeeded as bit = NULL,
@StoredProcedureResult int = NULL OUTPUT

AS
SET NOCOUNT ON

DECLARE @SQL nvarchar(2000)
DECLARE @SQL_DetermineUniqueTemplate nvarchar(2000)
DECLARE @TempIdentColName varchar(20)
DECLARE @HostName varchar(50)
DECLARE @ActionText varchar(10)

DECLARE @SUM int
DECLARE @COUNT int
DECLARE @NextColumn varchar(50)


/*==================================================================================*/
/*========================VARIABLE INITIALIZATION AND SETUP========================*/
/*=================================================================================*/
/*If no unique column is located then a temporary Identity column will be created useing the name specified in this TempIdentColName string*/
SET @TempIdentColName = 'TempIdentityColXY123'

SET @SQL_DetermineUniqueTemplate = 'SELECT @COUNT = COUNT(Count), @SUM = sum(Count) from '
SET @SQL_DetermineUniqueTemplate = @SQL_DetermineUniqueTemplate + CHAR(13) + '(SELECT TOP 100 PERCENT <COLUMN_NAME>, COUNT(*) as Count FROM ' + @TableName
SET @SQL_DetermineUniqueTemplate = @SQL_DetermineUniqueTemplate + CHAR(13) + ' GROUP BY <COLUMN_NAME> ORDER BY <COLUMN_NAME>) a'

/*Retrieve the Host Name. This will be used later in this SP as a test to determine if the user is making this call from within SQL Query Analyzer*/
SELECT @HostName = hostname FROM master..sysprocesses WHERE spid = @@SPID AND program_name = 'SQL Query Analyzer'


/*Set ActionText to be used in message output*/
IF (@DeleteDups IS NULL) OR (@DeleteDups = 1)
SET @ActionText = 'Selection'
ELSE
SET @ActionText = 'Deletion'

/*If a value is specified for use by UniqueColName it cannot exist in the columns from the DupQualifierFieldNameList*/
IF CHARINDEX(@UniqueColName, @DupQualifierFieldNameList) > 0
BEGIN
/*The value in UniqueColName was detected in DupQualifierFieldNameList.*/
IF NOT (@HostName IS NULL) PRINT 'The UniqueColName provided (' + @UniqueColName + ') must not exist in DupQualifierFieldNameList (' + @DupQualifierFieldNameList + '). Other solutions will be sought automatically.'
SET @UniqueColName = NULL
END


/*If UniqueColName is provided then perform check to ensure that all the values in that column are, in fact, unique.*/
IF NOT (@UniqueColName IS NULL)
BEGIN
SET @SQL = REPLACE(@SQL_DetermineUniqueTemplate,'<COLUMN_NAME>', @UniqueColName)
/*Perform a check of this column to determine if all of it's values are unique*/
EXEC sp_executesql @SQL, N'@SUM as int OUTPUT,@COUNT as int OUTPUT',@SUM OUTPUT,@COUNT OUTPUT
/*Test to determine if this column contains unique values*/
If @SUM <> @COUNT
BEGIN
/*The column specified by UniqueColName does not contain unique values.*/
IF NOT (@HostName IS NULL) PRINT 'The UniqueColName provided (' + @UniqueColName + ') does not contain unique values. Other solutions will be sought automatically.'
SET @UniqueColName = NULL
END
END


/*==============================================================*/
/*======================HELP OUTPUT TEXT======================*/
/*==============================================================*/
IF (@TableName IS NULL) OR (@TableName = '/?') OR (@TableName = '?') OR (@DupQualifierFieldNameList IS NULL) OR (@DupQualifierFieldNameList = '/?') OR (@DupQualifierFieldNameList = '?')
BEGIN
IF NOT (@HostName IS NULL)
BEGIN
PRINT 'sp_RemoveDups ''TableName'', ''DupQualifierFieldNameList'', [''DeleteDups''], [''UniqueColName''], [''CreateIdentColIfNeeded''], <''StoredProcedureResult''>'
PRINT '====================================================================================================================================================================='
PRINT 'TableName: Required - String - Name of the table to detect duplicate records in.'
PRINT 'DupQualifierFieldNameList: Required - String - Comma seperated list of columns that make up the unique record within TableName.'
PRINT 'DeleteDups: Optional - Bit, Set to 0 to delete duplicate records. A value of NULL or 1 will return the duplicate records to be deleted.'
PRINT 'UniqueColName: Optional - Bit - A table must have a unique column value in it to perform the deletion logic. If no UniqueColName is provided then an attemp will be made to locate the RowGUID column. If that fails then an attempt will be made to locate the Identity column. If that fails then all of the columns of the table will be examined and the first one with all unique values will be selected.'
PRINT 'CreateIdentColIfNeeded: Optional - Bit - By default this SP will create an identity column if no unique column can be located. Pass in a 1 here to run this feature off.'
PRINT 'StoredProcedureResult: Optional - OUTPUT - Int - Returns a 3 if an error occured, otherwise returns a 0.'
END
SET @StoredProcedureResult = 3
RETURN
END


/*========================================================================*/
/*======================DETECT USABLE UniqueColName======================*/
/*========================================================================*/
IF @UniqueColName IS NULL
BEGIN
/*Check for a RowGUID or Identity column in this table. If one exists, then utilze it as the unique value for the purposes of this deletion*/
IF EXISTS(SELECT * FROM SysColumns WHERE ID = Object_ID(@TableName) and ColumnProperty(ID,Name,'IsRowGUIDCol') = 1) SET @UniqueColName = 'RowGUIDCol'
IF EXISTS(SELECT * FROM SysColumns WHERE ID = Object_ID(@TableName) and ColumnProperty(ID,Name,'IsIdentity') = 1) SET @UniqueColName = 'IdentityCol'

IF @UniqueColName IS NULL
/*If no RowGUID or Identity column was found then check all of the columns in this table to see if one of them can be utilized as a unique value column*/
BEGIN
/*Select all of the columns from the table in question...*/
DECLARE MyCursor CURSOR LOCAL SCROLL STATIC FOR SELECT name FROM syscolumns WHERE OBJECT_ID(@TableName)=ID

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @NextColumn
WHILE @@fetch_status = 0
BEGIN
/*Create SQL string with correct column name in place.*/
SET @SQL = REPLACE(@SQL_DetermineUniqueTemplate,'<COLUMN_NAME>', @NextColumn)
/*Perform a check of this column to determine if all of it's values are unique*/
EXEC sp_executesql @SQL, N'@SUM as int OUTPUT,@COUNT as int OUTPUT',@SUM OUTPUT,@COUNT OUTPUT
/*Test to determine if this column contains unique values*/
If @SUM = @COUNT
BEGIN
/*A unique values column is detected. Use it and break out of the loop UNLESS column is specified in DupQualifierFieldNameList*/
IF CHARINDEX(@NextColumn, @DupQualifierFieldNameList) = 0
BEGIN
/*NextColumn was NOT detected in DupQualifierFieldNameList, so this is the column we will use.*/
SET @UniqueColName = @NextColumn
BREAK
END
END
ELSE
FETCH NEXT FROM MyCursor INTO @NextColumn
END
CLOSE MyCursor
DEALLOCATE MyCursor

END
END

/*If no UniqueColName has been found then create one UNLESS @CreateIdentColIfNeeded = 1*/
IF (@UniqueColName IS NULL) AND ( (@CreateIdentColIfNeeded IS NULL) OR (@CreateIdentColIfNeeded = 0) )
BEGIN
/*Add a sequence column to the table...*/
IF NOT (@HostName IS NULL) PRINT 'Creating temporary identity column in the ' + @TableName + ' table named ' + @TempIdentColName + ' for use in this ' + LOWER(@ActionText) + ' process...'
EXEC('ALTER TABLE ' + @TableName + ' ADD ' + @TempIdentColName + ' [int] IDENTITY (1, 1)')
SET @UniqueColName = @TempIdentColName
END


/*============================================================================*/
/*======================EXECUTE DELETION OR SELECTION======================*/
/*===========================================================================*/
IF @UniqueColName IS NULL
BEGIN
/*No UniqueColName was provided by the user and none were detected by the script. This deletion algorythm cannot run.*/
IF NOT (@HostName IS NULL) PRINT 'Could not perform ' + LOWER(@ActionText) + ' process. No unique columns were located and the UniqueColName flag is set to 1 (False).'
SET @StoredProcedureResult = 3
RETURN
END
ELSE
BEGIN
IF NOT (@HostName IS NULL) PRINT 'Performing ' + LOWER(@ActionText) + ' utilizing the unique values in the ' + @UniqueColName + ' column as a reference...'
/*
Create and execute an SQL statement in the form of:

SELECT * (or DELETE)
FROM TableName WHERE UniqueColName IN
(
SELECT UniqueColName FROM TableName WHERE UniqueColName NOT IN
(
SELECT MAX(Cast(UniqueColName AS varchar(36))) FROM TableName GROUP BY DupQualifierFieldNameList, DupQualifierFieldNameList, etc
)
)
*/
/*Delete all duplicate records useing @UniqueColName as a unique ID column */
IF (@DeleteDups IS NULL) OR (@DeleteDups = 1)
SET @SQL = 'SELECT * '
ELSE
SET @SQL = 'DELETE '

SET @SQL = @SQL + 'FROM ' + @TableName + ' WHERE ' + @UniqueColName + ' IN '
SET @SQL = @SQL + CHAR(13) + CHAR(9) + '(' + CHAR(13) + CHAR(9)
SET @SQL = @SQL + 'SELECT ' + @UniqueColName + ' FROM ' + @TableName + ' WHERE ' + @UniqueColName + ' NOT IN '
SET @SQL = @SQL + CHAR(13) + CHAR(9) + CHAR(9) + '(' + CHAR(13) + CHAR(9)+CHAR(9)
SET @SQL = @SQL + 'SELECT MAX(Cast(' + @UniqueColName + ' AS varchar(36))) FROM '
SET @SQL = @SQL + @TableName + ' GROUP BY ' + @DupQualifierFieldNameList
SET @SQL = @SQL + CHAR(13) + CHAR(9) + CHAR(9) + ')' + CHAR(13) + CHAR(9) + ')'

EXEC (@SQL)
IF @@ERROR <> 0
BEGIN
IF NOT (@HostName IS NULL) PRINT @ActionText + ' process failed.'
SET @StoredProcedureResult = 3
END
ELSE
BEGIN
IF NOT (@HostName IS NULL) PRINT @ActionText + ' completed successfully with this SQL: ' + CHAR(13) + @SQL
SET @StoredProcedureResult = 0
END
END


IF (@UniqueColName = @TempIdentColName) AND ( (@CreateIdentColIfNeeded IS NULL) OR (@CreateIdentColIfNeeded = 0) )
BEGIN
/*Remove the sequence column from the table...*/
IF NOT (@HostName IS NULL) PRINT 'Removing temporary identity column named ' + @TempIdentColName + ' from the ' + @TableName + ' table...'
EXEC('ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @TempIdentColName)
END
GO







Edited by - emanaton on 10/30/2002 11:28:34

View 11 Replies View Related

Detect Connectable Db Servers

May 16, 2006

Hello,how I can detect via code (C++, C#) which sql servers like oracle, mssql,mysql are close to me and connectable within one or two second (timeout towait for answers should be variable)?Thank you.regardsMark

View 3 Replies View Related

Detect Installed Versions

Apr 24, 2008



Hi Guys,

I need to detect what version/ or versions of SQL Server are installed on a machine before I can install my software. I am currently looking for SQL Server 2000 / SQL Server 2005 /SQL Server Express Edition & SQL Server 2008 CTP (for the time). How do I find this out. Is SQL-DMO the way forward or should this be some WMI script. I would appreciate any pointers/scripts etc.

I have read a lot of people mentioning using @@version from a query window but as I need the software to do this on first start up can this work for all versions

Thanks in advance

View 7 Replies View Related

SQLEXPR_ADV.EXE Does Not Detect WinXP SP1

May 17, 2006

Running SQLEXPR_ADV.EXE against Windows XP SP0 causes the setup to exit gracefully with a message indicating the the right service pack is not installed.

However, running SQLEXPR_ADV.EXE against Windows XP SP1 does not exit and continues to install. Then, when you try to run a script against the installation, you get the message:

[Microsoft][ODBC SQL Server Driver][DBMSLPCN][SQL Server does not exist or access denied].

This will become very problematic when installing SQL Server Express as the database for small installations.

The setup really needs to be updated to look for all the system requirements that it can detect. Evidently it can detect the Service Packs. The Wise Installer is not able to script that requirements unless there is some way to make the Windows Installer look for it that is not documented.

Pat

View 3 Replies View Related

Detect Last Insert In Trigger

Jan 11, 2008

I need to make a control in a trigger, but there is a sequence of N INSERT inside oneself transaction and I should only control when it is the last register.
I ignore the quantity of INSERT, it can be one or several. And in the values there is not any indicator that allows me to deduce if it is the last one.
It would seem that I need a trigger for COMMIT...
Is there some form of making this?

View 3 Replies View Related

Launch Condition To Detect SQL CE?

Jan 29, 2007

I have written a .NET application that uses SQL Server Compact Edition. It's deployed by a MSI file (setup project in VS - not ClickOnce).

How do I add a SQL CE launch condition to my setup project?

View 1 Replies View Related

How To Detect Whether Installed The Sqlce 3.5 RTM Or Not ?

Jan 29, 2008

How to Detect whether installed the sqlce 3.5 RTM or not ?

View 7 Replies View Related

Detect Installed Version?

May 8, 2006

Today I installed SQL Server 2005 developer edition on a test server. Afterwards I also installed the SP1. During the installation of SP1, the installation program said the server should be restarted afterwards, which I did.

Now when I look at the Add/Remove Programs dialog I have several items which mention SP1 on the SQL Server items.

But when I request the server properties in Managemnt Studio I see the version number 9.0.2047. Shouldn't this be 9.1.XXXX?

How can I be sure the SP1 is correcty insatlled?

Stievie

View 8 Replies View Related

Insert Into From Another Table - NULL IDs!!!

May 31, 2006

Hi friends,
I am trying to add records from one table into another.

SQL
use riqdb2
INSERT INTO tblItemGrades
(chrvItemGradeStaticComment,decItemGrade)
SELECT chrvItem2StaticComment,decItem2Grade
FROM tblItems2
GO

Error msg
/*------------------------
use riqdb2
INSERT tblItemGrades
(chrvItemGradeStaticComment,decItemGrade)
SELECT chrvItem2StaticComment,decItem2Grade
FROM tblItems2
GO
------------------------*/
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column 'ItemGradesID', table 'RIQDB2.dbo.tblItemGrades'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Please advise how to insert new records - Is it because I havent set up Identity (1,1)????

Thanks
Cm

View 9 Replies View Related

Best Way To Detect If SqlServices Has Been Installed On A Given Database

Feb 2, 2008

I am currently writing an install wizard for my web application.  One of my steps is Installing asp.net membership, roles, and session state to the new database.  My wizard detects if this step has already been completed through the page_Load event.  Currently I am writing makeshift code just to see if the tables exist.Does ASP.Net provide a way to detect what services have been installed on the database?  So far I have only found Install and uninstall functionality in the System.Web.Management Namespace.I have been searching for this for a while, I am sure this question has been answered somewhere, but there is so much base information on how to initially set up membership that I cant seem to find the answer. 

View 1 Replies View Related

From Client, How Can I Detect Which Services&#39;re Running Or Not?

Aug 4, 2000

From client, how can I detect which services're running or not? Example for SQLAgent, MS DTC..
Thanks.

View 1 Replies View Related

Jump Or Detect Corrupt Lines In DTS

Mar 7, 2001

Hi,

I import from CSV files into MSSQL tables.
My hierarchy absolutely wants to keep the CSV standard format.
Some string have quotes inside.
With Sybase it jumps these corrupted lines.
With MSSQL and DTS, it breaks all the importation.

Does anyone know how to jump (aloso is there a possibility to detect the number of corrupted lines, Sybase can do that) ?

Thanks for your answers

Best Regards

Axel

View 1 Replies View Related

Correct/detect Improper Case

May 22, 2008

I have been through our database and we have a culprit who has been inputting all names in full capitals which I have always found particularly annoying. I want to convert them all to proper case for which I found the code below. However how can I detect the set where they have inproper case.

CREATE FUNCTION dbo.pCase
(
@strIn VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
IF @strIn IS NULL
RETURN NULL

DECLARE
@strOut VARCHAR(255),
@i INT,
@Up BIT,
@c VARCHAR(2)

SELECT
@strOut = '',
@i = 0,
@Up = 1

WHILE @i <= DATALENGTH(@strIn)
BEGIN
SET @c = SUBSTRING(@strIn,@i,1)
IF @c IN (' ','-','''')
BEGIN
SET @strOut = @strOut + @c
SET @Up = 1
END
ELSE
BEGIN
IF @up = 1
SET @c = UPPER(@c)
ELSE
SET @c = LOWER(@c)

SET @strOut = @strOut + @c
SET @Up = 0
END
SET @i = @i + 1
END
RETURN @strOut
END
GO

View 3 Replies View Related

How To Detect The SQL Express & Yukon Installation

Nov 10, 2005

Hi,

I have a need to check whether SQL Express 2005 or Yukon is installed on a machine or not.

Can anybody suggest what is right method to check the existence of these installations.

If possible suggest the registry keys which can be used for this installation check.

Thanks a lot
krishna.s

View 1 Replies View Related

Detect No Time Entered For Datetime

Jul 20, 2006

I have a VB.NET program that displays the time extracted from a SQL Server database datetime datatype by way of a User-defined scalar function I created. However, sometimes information is entered into the system through the program that does not have a time-- only a date. SQL Server automatically assigns a time of 12:00 AM to these values (since they're a datetime). Is there any way to detect when this happens in my user-defined scalar function so that when I try to extract time values, I can instead return a message/time of my choice? I would rather not assume that all 12:00 AM values are automatically inserted by SQL Server since this might not actually be the case.

View 4 Replies View Related







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