I need to get dummy values if they do no rows returned from table. The If exists works by itself, but gives error with a Union. Can someone please guide me with a solution or a workaround? create table test1 (col1 varchar(10)) create table test2 (col1 varchar(10)) create table test3 (col1 varchar(10)) insert test1 values ('tes...
I have a bunch of records I want to move to another database and I just want to create a bunch of inserts that I can copy and paste. I've seen someone do this before but I can't figure it out. I'm not getting the escapes right. It's something like this where 'Code', 'Description' and 'Absent' are the columns I want from the table. S...
I m passing a variable to stored procedure, i want the proc to make a look up to another table and get the primary key of the table and insert that value to the table. Table A: pk_id int, user varchar Table B: userKey int locationKey int Someotherthings varchar Table C: pk_id int, Location varchar When i invoke sp_howto, and pa...
In MS SQL Full-text search, I'm using ISABOUT in my queries. For example, this should return the top 10 ProductIDs (PK) with a RANK Field in the ProductDetails Table SELECT * FROM CONTAINSTABLE( ProductDetails, *, ISABOUT("Nikon" WEIGHT (1.0), "Cameras" Weight(0.9)), 10 ) However, according to the SQL Documentation ISABOUT is depr...
It's not hard to find developers who think cursors are gauche but I am wondering how to solve the following problem without one: Let's say I have a proc called uspStudentDelete that takes as a parameter @StudentID. uspStudentDelete applies a bunch of cascading soft delete logic, marking a flag on tables like "classes", "grades", and s...
I am importing a table from one database to another in SQL Server 2008 by right-clicking the target database and choosing > Tasks > Import Data... When I import the table I get the column names and types and all the data fine, but I lose the primary key, identity specifications and all the default values that were set in the source tab...
I'm learning SQL Server Compact for a program I'm writing that requries a local database. I have multiple tables, each with different columns and I'd like to mark each column as being a certain type (not a data type, just an integer tag) to let the program know what to do with it. I'm clueless about SQL. How does one do this? ...
I need to check whether a SQL Server database is up and running properly using .NET windows application. ...
Hi I have an idea and I want to apply it to my Application (C# .NET). When we connect to a DB (MS SQL Server 2008) in VS 2008, the ConnectionString saved in the Application Setting and it's a static varriable (no one can edit it unless you edit it inside VS 2008). I want a way to let my Application search for MS SQL Server and save it...
In sql 2005, instead of building a query from dateparts year, month and date, is there an more succinct way of writing the where clause? many thanks ...
Hi. Does SQL Server maintains any history to track table alterations like column add, delete, rename, type/ length change etc? I found many suggest to use stored procedures to do this manually. But I'm curious if SQL Server keeps such history in any system tables? Thanks. ...
I have a very simple CLR Function for doing Regex Matching public static SqlBoolean RegExMatch(SqlString input, SqlString pattern) { if (input.IsNull || pattern.IsNull) return SqlBoolean.False; return Regex.IsMatch(input.Value, pattern.Value, RegexOptions.IgnoreCase); } It allows me to write a SQL Statement Like. SEL...
I want to insert an attribute in every xml node. As xml is changed every time some developer do a change so I want to retrieve xml node path dynamically first and then insert the attribute in it. Any idea how can I have a dynamic path of an xml instead of passing a absolute path. I want to have the following piece of code in t-sql..As ...
I have a number of .sql files which I have to run in order to apply changes made by other developers on an SQL Server 2005 database. The files are named according to the following pattern: 0001 - abc.sql 0002 - abcef.sql 0003 - abc.sql ... Is there a way to run all of them in one go? ...
Hi Guys, i tried to open the website from broswer project is deployed at IIS i am getting this exception Exception information: Exception type: SqlException Exception message: Cannot open database "TestDB" requested by the login. The login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. any solution? Regards k...
How do I query a MS Access database directly from SQL Management Studio, without using a linked server? Ie. something like SELECT * FROM ["C:\Data\Accessdb.mdb"].[SomeTableInAccessDB] Obviously this won't work but is there a away to specify the access database details within a sql query? ...
FORMSOF THESAURUS throws errors for specific character. E.g. FORMSOF (THESAURUS, hel!lo) throws an error whereas FORMSOF (THESAURUS, hel?lo) works. However, I did not find any documentation about which characters are allowed. Can you help me out? ...
Hi I am creating a stored procedure to which I want to pass as variable a comma delimited list of Ids. I want to use the Ids into a select statement, something like: Create Procedure up_TEST @Ids VARCHAR(MAX) AS SELECT * FROM ATable a WHERE a.Id IN(@Ids) Obviously I get the error that @Ids is a varchar and not an INT but how can I...
I am doing a SQL Insert to populate my table. I have a unique generated ID in one table that I would like to use in another table for my join. Is this possible? .NET MVC -- using (SqlConnection connect = new SqlConnection(connections)) { SqlCommand command = new SqlCommand("ContactInfo_Add", connect); command.Parameters.Add(n...
I'd like to create a table: CREATE TABLE sfc.OpenId ( Url VARCHAR(255) PRIMARY KEY, UserGuid uniqueidentifier NOT NULL references dbo.aspnet_users(userId), ) ...with an index on UserGuid. Is it possible to create that index in the create table statement? ...