tsql

SQL Server View Primary Key

Is there a way to give a view a primary key in sql server. I know it is possible in oracle. I am not concerned about updates its a read only view but someone else is using it in ms access so I would like the constraint that I know to be correct to be shown. ...

Create a script in MMSE Express - question about certain part of script.

When I create a script with the Management Studio, it includes this part: WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70 ) What are those options and are they required? Thanks :) ...

Get index of row within a group?

Hello! I have two tables: Unit: UnitId int PK Title varchar UnitOption: UnitOptionId int PK UnitId int FK Title varchar Quote: QuoteId int PK UnitOptionId int FK Title varchar I want to create a scalar UDF that takes a QuoteId param and returns a varchar that contains the following description (pseudu): Quote.Title + '-' + Unit.Ti...

Limiting rowcount of SELECT statements, with linked servers

I have these linked servers, A and B. Server A is running SQL Server 2008, and B is running SQL Server 2000. I have written a script to migrate data from a bunch of tables from B to A. The data is quite sensitive so I'm afraid I can't tell you anymore about that. I can, however, tell you that we are talking about a lot of data, worst ca...

What is the Oracle equivalent of SQL Server's IsNull() function?

In SQL Server we can type IsNull() to determine if a field is null. Is there an equivalent function in PL/SQL? ...

SQL Count across 3 tables

I have a query that works fine when there is data but not when I have nothing in the charities table. Any help would be hugely appreciated. SELECT C.CategoryId , C.CategoryName , ISNULL(COUNT(CC.CharityId), 0) as CharityCount , C.IsDeleted FROM Charity.Categories C LEFT JOIN Charity.CharityCategories CC on C.CategoryId =...

One-to-Many SELECT statement in a view

There are 3 tables in a database: Users (UserID, UserName), Roles(RoleID, RoleName) and UserRoles(UserID, RoleID) How to create a view on Users table which will have IsAdmin column, here is a mockup: CREATE VIEW UsersView AS SELECT u.UserID, u.UserName, CASE WHEN ur.RoleID IS NULL THEN (0) ELSE (1) END AS ...

Insert into Table select result set from stored procedure but column count is not same.

I need something like that which is of course not working. insert into Table1 ( Id, Value ) select Id, value from ( exec MySPReturning10Columns ) I wanted to populate Table1 from result set returned by MySPReturning10Columns. Here the SP is returning 10 columns and the table has just 2 columns. The following way works as long...

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. ...

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? ...

How to replace CHAR(13) in DB varchar(6000)?

Using ColdFusion and Microsoft SQL we are exporting data to an Excel Spreadsheet using the cfx_excel plugin. The data contains a varchar(6000) which has CHAR(13)/line-breaks inputted in each entry. The line-breaks are appearing as square brackets every time the report is generated in Excel format. How would I go about removing the CHAR...

T-Sql query to clean up varchar column

I have a varchar column in one of my tables with data like: 1234abc 1234abcde456757 1234abc Supervisor 1234abc456 Administrator I want to "clean it" by removing any letters and numbers immediately following them so for the above examples I want to have: 1234 1234 1234 Supervisor 1234 Administrator In another word, I want to keep th...

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 ...

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...

SSIS OLE using DBCommand to call tsql function

I'm importing Oracle data into SQL Server. After my OLE DB Source that runs a query to grab the data to process, I'm trying to call a TSQL function on a field value somehow... I have an SSIS data flow where I have a datetime column called DepartureDateGMT, and an integer column called DepartureTimeZoneKey. I have a TSQL Function calle...

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. ...

Removing data from returned information.

Hi Guys, I am making an SQL Query that brings back a list of files and their paths. They have different file paths and different file names ofc. The file names are dates and time in the form of YearMonthDayHourMinuteSeconds. What I need to do is take the filepath that thas the latest date and time, strip off everything except the dat...

NVARCHAR(MAX) & Stored Procedures

Hi, I can't find an answer to this so if anyone knows of one please point me in the right direction. A friend has created a few tables for me and used nvarchar(max) on around 4 of the columns in the table. Inserts and Updates are handled using stored procedures. Am I right to worry that the executes of the stored procedures will error...

T-SQL Dynamic xquery

Hi All, I am trying to figure out how I can load my table variable with data from XML using dynamic xquery? I am getting a result set of nodes from the query and defining the value type of those nodes. It seems that it is the value definition of the nodes that it is blowing up on. Here is an example of the script that works, but is ...