How to remove not null constraint in sql server using query
I am trying to remove not null constraint in sql server 2008 without losing data. ...
sql-server
Formatting a Datetime returned from a SQL UD Function
Consider this simple user defined function: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER FUNCTION [dbo].[ufn_GetFirstDayOfMonth] ( @pInputDate DATETIME ) RETURNS DATETIME BEGIN RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME) END whi...
Exception with connection in ADO.NET
I am trying to learn ADO.NET to connect with SQL Server ... I install SQL Server with Visual Studio .... there is data base called "Northwind" as example with it ... I am trying to read this database , I wrote this code ... using System; using System.Data; using System.Data.SqlClient; namespace DataSetReaderFromSQL { class Prog...
SQL View where value appears in one of two tables or both of them
I have two tables, both with the same columns. The first table contains original source data, the second contains revisions to that data OR values inserted where no original data was present. The tables are to be used in a view where if the data has a revision that is shown - if not then the original source data is shown. Normally thi...
Database design for email messaging system.
I want to make an email messaging system like gmail have. I would like to have following option: Starred, Trash, Spam, Draft, Read, Unread. Right now I have the below following structure in my database : CREATE TABLE [MyInbox]( [InboxID] [int] IDENTITY(1,1) NOT NULL, [FromUserID] [int] NOT NULL, [ToUserID] [int] NOT NULL, ...
For each row in a table, get any one linked row in another table
Given the following (heavily simplified) tables: create table Tags ( TagId int Primary Key ) create table OrderLines ( Branch int, Station int, TransNo int, TagId int foreign key references Tags, primary key (Branch, Station, TransNo) ) I need a list of Tags along with an OrderLine which references each Tag. I am ex...
Best pattern for State Constants in SQL Server + DBProj?
Hi I have seen several patterns used to 'overcome' the lack of constants in SQL Server, but none of them seem to satisfy both performance and readability / maintainability concerns. In the below example, assuming that we have an integral 'status' classification on our table, the options seem to be: 1) Just hard code it, and possibly j...
RowGuide and UNIQUE KEY
Hi, i need a GUID at row level in my table (name column RowGuid) datatype is uniqueIdentifier. Do I need also state it as a UNIQUE KEY (alternate key)? Ex: RowGuid uniqueIdentifier UNIQUE KEY Thanks ...
PATINDEX in Sql 2005 with a CASE
Hello, im using the PATINDEX sentence into a case sentence: select Choosed1= CASE PATINDEX('%1|%',field1) //Here im getting an error: WHEN >0 THEN 'X' END from testtable How could i put the >0 condition to avoid the error? Thanks in advance Best Regards. Jose ...
Checking SQL server running on a remote machine
Hi to all, In my installer, I am using System.Data.SqlClient.SqlConnection(connectionstring) to check if I can connect to the SQL server instance. When a user provides the connection string, I remove the "initial Catalog=..." part and try an SQLConnection.Open(). If all is fine, then I proceed in setting up the database (i.e. attaching ...
SSIS, splitting a single row into multiple rows
My problem is as follows. I have a CSV file (~100k rows) containting history information with the column format of: ID1,History1,ID2,History2...ID110,History110 Each row may have anywhere between 0 and 110 history entries. Each separate entry requires a stored procedure to be called. If there were a small number of possible entries pe...
Pivot XML using XQuery and filter on attribute
Given the following XML (in an SQL column field called 'xfield'): <data> <section> <item id="A"> <number>987</number> </item> <item id="B"> <number>654</number> </item> <item id="C"> <number>321</number> </item> </section> <section> <item id="A"> <number>123</number> </item> ...
upload and execute a .SQL script to a web host to deploy a SQL database
Hi, I tried to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database following the steps outlined in Scott Guthrie's tutorial. When I try to view the page, I am getting an error page. What's the root cause, and how can this be fixed? Opening url http://www.elhayesetal.com/New_Members_ASPNETDB.MDF.sql An error occ...
FreeTDS translating MS SQL money type to python float, not Decimal
I am connecting to an MS SQL Server db from Python in Linux. I am connecting via pyodbc using the FreeTDS driver. When I return a money field from MSSQL it comes through as a float, rather than a Python Decimal. The problem is with FreeTDS. If I run the exact same Python code from Windows (where I do not need to use FreeTDS), pyodbc ...
FileStream: T-SQL vs Win32
One of our teams is building a database (and application) that will use the FileStream feature of SQL 2008 to store documents. According to the MS best practices for filestream, the Win32 APIs are the preferred way to access FileStream data, vs using T-SQL. However, using the Win32 APIs requires using integrated authentication to con...
joining 3 queries with outer join
Hi, I have created a table tblOperationLog and wrote triggers to fill it as a user deletes, updates or inserts a row in my main table. It’s the delete and insert one: CREATE TRIGGER FILL_TABLE ON Person FOR INSERT, DELETE AS INSERT INTO tblOperationLog SELECT SYSTEM_USER, 'user has inserted a row with ID = ' + Conv...
How to delete or truncate table in SQL Server?
I need to clear many tables (preferably truncate table). But tables have many FK constraints. I tried something like this, but failed:- ALTER TABLE Table1 NOCHECK CONSTRAINT ALL TRUNCATE TABLE Table1 ALTER TABLE Table1 WITH CHECK CHECK CONSTRAINT ALL This is the error i am getting:- Cannot truncate table 'Test' because it is being r...
Javascript in Sql Server?
T-SQL is nice. But I need a more flexible scripting language in Sql Server like javascript. Can anyone help? ...
What is the most efficient way to insert a large number of rows from MS SQL to MySQL?
I need to transfer a large number of rows from a SQL Server database to a MySQL db (MyISAM) on a daily basis. I have the MySQL instance set-up as a linked server. I have a simple query which returns the rows that need to be transferred. The number of rows will grow to approximately 40,000 rows, each row has only 2 char(11) columns. T...
T-SQL - string concatenation
Hello, Hope someone can help - I am a novice SQL hacker (and very bad at it indeed!) I have two tables on SQL Server 2005 TABLE 1 and TABLE2: TABLE1 COL1 COL2 1 10 2 20 3 30 4 10 4 20 5 20 6 30 7 10 7 20 TABLE2 COL1 CO...