sql-server

Best way to write union query when dealing with NULL and Empty String values

I have to write a query that performs a union between two tables with similar data. The results need to be distinct. The problem I have is that some fields that should be the same are not when it comes to empty values. Some are indicated as null, and some have empty string values. My question is, is there a better way to perform the...

Optimal DB structure for a membership website with free and paid subscriptions

There is a regular Users (UserID, UserName, Email, Password) table which handles user registrations. Which other tables should be added in order to handle paid membership with 2 types of paid subscriptions - Monthly and Yearly. ...

How can I get the label of a Range Bar Chart to move off of its default position with SQL Server Reporting Services

I have tried using both CustomAttributes settings and Smart Labels settings, but neither are working. Any thoughts? ...

str_to_date function in sql server?

MySQL has a function called STR_TO_DATE, that converts a string to date. Question: Is there a similar function in SQL Server? ...

Generate MD5 hash string with T-SQL

Is there a way to generate MD5 Hash string of type varchar(32) without using fn_varbintohexstr SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', '[email protected]')), 3, 32) So it could be used inside a view with SCHEMABINDING ...

CSV import in SQL Server 2008

I have a csv file that has column values enclosed within double quotes. I want to import a csv file from a network path using an sql statement. I tried bulk insert. But it imports along with double quotes. Is there any other way to import a csv file into SQL Server 2008 using an sql statement by ignoring the text qualifier double quot...

mysql/sql server: SELECT @@identity

is there a difference in the way this functions between the two databases mysql and sql server: SELECT @@identity ...

How to display data from related tables in a WPF datagrid

I'm just finding my way with WPF/Datagrid/Linq and I'm trying to find out how to display data from a related table in my datagrid. For example I have 3 tables: Customers: id, name Products: id, name Orders: id, customerId, productId and I've set up the relations in the DB tables. I've added the tables as a C# DataSet which has cre...

Condition in sql query

Hi I want to insert in sql query something like that: Select * from Users where id=[if @userId>3 then @userId else "donnt use this condition"] and Name=[switch @userId case 1:"Alex" case 2:"John" default:"donnt use this condition"]; How can i do it? yet another similar question When showAll is false it works ok but when showAll i...

HOW do i use SELECT @@identity in sql server?

i am currently calling SELECT @@identity from VBA in mysql: Set rs = cn.Execute("SELECT @@identity", , adCmdText) but since i am going to be working with sql server db instead of mysql, i would like to know how to make this statement sql-server friendly would it just be Set rs = cn.Execute("SCOPE_IDENTITY()", , adCmdText) ?? ...

Using Pivot in SQL Server to Display dates as days of the week

Hi, I have table that has a column of type DateTime, I would like to pivot on the datetime column so that the dates are displayed as columns (as days of the week Monday, Tuesday etc). So for example I have a table like this (can't remember how SQL Server displays full datetimes but you get the idea): BugNo | DateOpened | TimeSpent 12...

Saved Broken Stored Procedure.

I received a ticket today concerning a broken application that was caused by a broken stored procedure. The stored procedure had been working for a few months now, but today when I examined it I found a few bugs. first there was a query like this. SELECT a.a , b.b , a.b FROM table1 a JOIN table2 b ON. a.a...

Are Ado.net (2.0+) Connection Pools pre Application Domain or per Process

I'm trying to understand the pooling theory w.r.t. to interactions between ADO.NET and SQL Server much better and haven't found the definitive answer. I have always assumed per process but it's just occurred to me that it could be per AppDomain. Any in depth references would also be appreciated. ...

getting all chars before space in SQL SERVER

i am trying to get all chars before the space: SUBSTRING(reporttime,1,CHARINDEX(reporttime,' ',1)) but it is not working. please help! example data: 7/8/2010 11:47 AM 7/8/2010 10:55 AM ...

why does SELECT SCOPE_IDENTITY() return null?

in what case does SELECT SCOPE_IDENTITY() return null? i am doing this: Set rs = cn.Execute("SELECT SCOPE_IDENTITY()", , adCmdText) capture_id = rs.Fields(0) and i getting capture_id=null ...

sql server: how do i get the last record that was inserted?

i am doing this: With rs .AddNew ' create a new record ' add values to each field in the record .Fields("datapath") = dpath .Fields("analysistime") = atime .Fields("reporttime") = rtime .Fields("lastcalib") = lcalib .Fields("analystname") = aname .Fields("reportname") = rname .Fields("batchstate") = b...

show create table tablename (how do i do this in sql server) ?

in mysql it is possible to do show create table tablename what is the sql server equivalent? ...

Need to create a function in sql to find a string

Hi guys, I am trying to write a function which can tell me whether there is something after second ; in the string or not.For example: the sample string is "2:00AM;3:00PM;". So the function in this case needs to return false. ...

GUID: varchar(36) versus uniqueidentifier

I'm working with a legacy database that stores GUID values as a varchar(36) data type: CREATE TABLE T_Rows ( RowID VARCHAR(36) NOT NULL PRIMARY KEY, RowValue INT NOT NULL ) INSERT T_Rows (RowID, RowValue) VALUES (NEWID(), 1) I would assume that storing a GUID as a uniqueidentifier would be preferable since it's o...

merge tables from two backup files - SQL Server

Hi, I have two .bak files These files contain different tables in them, and it is guaranteed that indexing is consistent across both files I can easily restore database A from A.bak and have tables a1, a2, a3 and likewise table B from B.bak with tables b1, b2, b3 What I really want is to restore database AB from both A.bak and B.bak ...