sql-server

Good methods or tutorials for understanding JOINs in SQL

Hi All, As we know that there are different types of JOINs in any RDBMS, for eg:- Left join, outer join, inner join, left outer join etc. Invariably we use JOINs for lots of our business logics in our projects. However very few people have complete understanding or mastery over these JOINs. Usually people with half known knowledge on JO...

Compute hash-value of entered value when insert or update using sql server 2008 by triggers

Hi, i have a table with two column {FlatContent, HashedContent}. Now i want to automatically compute hash value of FlatContent when new row was inserted or an existing row was updated. To date, i'm never used from trigger, so i can't do this by trigger or another approach which is exist to solve this issue. Thanks if anybody can help me...

SQL duplicate data for excel dynamic table

First thanks for reading me and sorry for my bad english. I have the following code on sql server 2008 r2 SELECT FA960.*, UMCONVPZ.UMFR, UMCONVPZ.UMCONF AS Piezas, UMCONVPL.UMCONF AS PL, UMCONVCJ.UMCONF AS Cajas FROM FA960 FA960 JOIN UMCONV UMCONVPZ ON FA960.RECURV = UMCONVPZ.UMRESR JOIN UMCONV UMCON...

ALTER TABLE Executing regardless of Condition Evaluational Results

Below is an excerpt of a SQL Query that I am using to update a table to the correct datatypes if needed. If NOT Exists(Select * From Information_Schema.Columns Where Table_Name = N'RXINFO' And Table_Schema = N'scriptassist' And Column_Name = N'LastChanged' And DATA_Type = N'TIMESTAMP' AND IsNull(CHARACTER_MAXI...

Unable to update sys.columns - any other approach?

Hello! I just found out, that since I created my DB on a different server that had a different collation, then immigrated to a new server with new collation, now I keep on having trouble, and I decided to change all the values of the old collation. So I tried to execute this query: sp_configure "Allow Updates", 1 GO RECONFIGURE WITH O...

SELECT INTO USING UNION QUERY

Hi, I want to create a new table in SQL Server with the following query. I am unable to understand why this query doesn't work. Query1: Works SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2 Query2: Does not Work. Error: Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near ')'. SELECT * INTO [NEW_TABLE] FROM ( SELECT *...

Get records whose count matches max(count) for a category

Given the following rows of course,section,grade,count of grades within course section: course SECTION grade gradeCount ----------------------------------- 1301 001 C 3 1301 001 C+ 3 1301 001 C- 4 1301 001 D 5 1301 001 D+ 3 1301 001 D- 2 1301 001 F ...

SQL Server 2005: Attach database using sp_attach_db with full text catalog

Having issues attaching a Database and full text catalog from server A to server B with the "sp_attach_db" command in SQL Server 2005 SP3. All the database files (data, log, full-text catalog) have been copied from server A to server B. I am guessing that this is indeed possible and that my syntax is just incorrect, but I can't seem to f...

SQL 2008 Express & Intellisence?

Does the SQL 2008 Express have intellisence? ...

Complicated SQL Query--finding items matching multiple diferent foreign keys

So imagine that you have a table of Products (ID int, Name nvarchar(200)), and two other tables, ProductsCategories (ProductID int, CategoryID int) and InvoiceProducts (InvoiceID int, ProductID int). I need to write a query to produce a set of products that match a given set of invoice ids and category ids such that the list of produc...

Why is there a "no such file or directory" error when the .so file does exist?

I'm trying to connect from Ruby 1.8.7 on RHEL 5 to MS SQL Server. I'm using FreeTDS and the tiny_tds RubyGem. I got it working fine on OS X. On Linux I installed FreeTDS and confirmed that it can connect to SQL Server from the command line no problem. And gem install tiny_tds went fine. However, when I deploy to Linux and try to open ...

How to export a SQL Server 2008 Database Diagram to another DB?

I use the handy Database Diagramming tool in SQL Server 2008 for creating and managing relationships. I have exported the sourceDB to the destinationDB but the diagram doesn't come across. I am looking around trying to figure out how to export just the diagram I have in one database to another... This online KB article fails since sel...

fetching rows without any filtered child row

The application manages (for the sake of simplicity) Topic and Messages posted to it by Users. Its structure is rather simple, and enforced by Foreign keys: There are 3 tables, Topics, Users and Messages. The Topic and User entities are linked through an (,) cardinality link embodied by the Message Entity. Message: (IdTopic,IdUser,Mes...

displaying boolean values from SQL Server as checkboxes

Consider an Access front end and SQL Server 2008 backend. Access boolean datatype values are displayed as checkboxes on their forms. I want similar functionality except that my data backend is SQL Server 2008 One of my fields is a 0 or 1 in SQL Server, and when I display this data on my form in Access I want it to be displayed as a chec...

Exception executing a stored procedure with CASE switching from C# (T-SQL)

I have a NVARCHAR(max) column in a table and a stored procedure that would update this column as well as any other column in the table using CASE switching: CREATE PROCEDURE updateTable @columnName sysname, @value nvarchar(max) AS UPDATE [dbo].[TestTable] SET BigNvarcharValue = CASE @columnName WHEN 'BigNvarcharValue...

System.Data.SqlTypes and NULL

If I have a variable, X, of type SqlDecimal, that was instantiated without providing a decimal value parameter to the constructor, then the .IsNull property is True: Dim X As New SqlDecimal '.Value = Null If I have a Stored Procedure that acepts a single parameter, @SqlDecimalParameter, of type Decimal, the value of this this paramete...

Adding values in a table in SQL 2008

Hi all, Trying to get a basic understanding of T-SQL here in SQL Server 2008. Suppose I have a table named "Issues" with columns such as: Priority User 1 Foo 1 Foo 2 Foo 5 Foo 4 Bar 5 Bar 1 Bar 1 Fuz and I wish to display a count of the Priority for each User, along with a break...

concatenating string

Is there a way in SQL sever that can write the output as follow: select events from mytable original output events -------- 123456 894531 985233 829292 920202 392939 299223 desired output '123456', '894531','985233','829292','920202','392939','299223' select '' + CustomerID + ',' from dbo.Customers customerid ALFKI, ANATR, ANT...

Importing data from SQL Server DB to another using SSMS Import/Export wizard

Hi When I'm importing data from SQL Server 2008 DB to another using SSMS 2008, I get errors during the importation because it tries to insert data in a "read only" fields, or cuz some conflicts of relationships between tables' keys. I'm wondering, how could I close the eyes of the SSMS until he finish the transformation :D Thanks, Reg...

single quotation in SQL Server

Possible Duplicate: concatenating string I posted a similar question earlier and asked about using SQL server to output the result with a single quote. Could someone edcuate me please? select ' ' + CustomerID + ',' from dbo.Customers customerid ------------ ALFKI, ANATR, ANTON, AROUT, BERGS, Would like to see the ...