I am doing a join on two tables. One is a user's table, and the other a list of premium users. I need to have the premium members show up first in my query. However, just because they are in the premium user table doesn't mean they are still a premium member - there is an IsActive field that also needs to be checked.
So basically I need...
I'm pretty sure this is not the right way to do this so I'm looking for some suggestions.
I don't think my problem so much is that I'm trying to solve a spatial problem. I'm just not sure of a good way to take the latitude and longitude and return a geography data type in a select statement. I have successfully created a geography col...
I've got the a SQL Server stored procedure with the following T-SQL code contained within:
insert into #results ([ID], [Action], [Success], [StartTime], [EndTime], [Process])
select
'ID' = aa.[ActionID],
'Action' = cast(aa.[Action] as int),
'Success' = aa.[Success],
'StartTime' = aa.[StartTime],
'EndTime' = aa.[EndTime],
'Process'...
How can I escape a bracket in a full-text SQL Server contains() query? I've tried all the following, none of which work:
CONTAINS(crev.RawText, 'arg[0]')
CONTAINS(crev.RawText, 'arg[[0]]')
CONTAINS(crev.RawText, 'arg\[0\]')
Using double quotes does work, but it forces the entire search to be a phrase, which is a showstopper for multip...
I have a SQL table which has a number of fields
ID | Value | Type
A typical record may be :-
1000,10,[int]
a second row may be:-
1001,foo,[string]
a third row may be:-
1002,10/12/2008,[DateTime]
I have been asked to look at this as at the moment, each time we wish to select from this table we have to cast the value to the type spe...
I have some funny deadlock caused by a stupid simple SQL UPDATE query, on a flat plain table, under default "READ COMMITED" transaction.
UPDATE table SET column=@P1 WHERE PK=@P2;
While PK varchar(11), has a clustered index on it.
no trigger or table relation..etc on the table.
I did some check and find that the deadlock happen ...
Apology for a lengthy post but I needed to post some code to illustrate the problem.
Inspired by the question What is the reason not to use select * ? posted a few minutes ago, I decided to point out some observations of the select * behaviour that I noticed some time ago.
So let's the code speak for itself:
IF EXISTS (SELECT * FROM...
Solution:
I've misinterpreted the example from SQL Books Online. Yes, that below the section Errors Unaffected by a TRY…CATCH Construct ... I'm so sorry :(
I was just trying to execute this simple example in SQL Server Management Studio:
USE AdventureWorks;
GO
BEGIN TRY
-- Table does not exist; object name resolution
--...
I'm trying to find out the most efficient (best performance) way to check date field for current date. Currently we are using:
SELECT COUNT(Job) AS Jobs
FROM dbo.Job
WHERE (Received BETWEEN DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)
AND DATEADD(d, DATEDIFF(d, 0, GETDATE()), 1))
...
I have this bit of code I found on the web at the end of each of my stored procedures:
ROLLBACK TRANSACTION
PRINT '-----START: ERROR DETAILS-----'
PRINT ERROR_NUMBER()
PRINT ERROR_SEVERITY()
PRINT ERROR_STATE()
PRINT ERROR_PROCEDURE()
PRINT ERROR_LINE()
PRINT ERROR_MESSAGE()
PRINT '-----E...
Hi there
I have a SQL Datetime field in sql 2000.
I have a stored proc called addPresentation.
From my .Net code I add a sqlparameter as:-
new SqlParameter("Date", SqlDbType.DateTime).Value=DateTime.now
When I call the stored proc from code and observe sql profiler it is attempting to perform the insert as :-
exec addPresentation @Na...
If I have a table where the cells in a column should not have the same values, how do I check this and update? (I know I can set constraints in the settings, but I don't want to do that.)
Say the column name is called unique hash name and contains
Peter
Peter
Peter
Dave
Dave
and so on. I want that to transform to:
Peter
Peter1
Pe...
I have an MS SQL server application where I have defined my relationships and primary keys.
However do I need to further define indexes on relationship fields which are sometimes not used in joins and just as part of a where clause?
I am working on the assumption that defining a relationship creates an index, which the sql engine can r...
I need to create a trigger in every database on my sql 2005 instance. I'm setting up some auditing ddl triggers.
I create a cursor with all database names and try to execute a USE statement. This doesn't seem to change the database - the CREATE TRIGGER statement just fires in adventureworks repeatedly. The other option would be to pr...
Please can someone explain what the following statement does in SQL Server 2005:
GRANT ALL TO pax_writer
pax_writer is a database role previously created using the statement
CREATE ROLE pax_writer AUTHORIZATION dbo
...
Hi all. Due to weird request, I can't put null in database if there is no value. I'm wondering what can i put in the store proc for nothing instead of null.
for example: insert into blah (blah1) values (null). Is there something like nothing or empty for blah1 instead using 'null'.
...
A puzzler from a coworker that I cannot figure out...
update btd.dbo.tblpayroll
set empname = ( select b.Legal_Name
from ( SELECT Legal_Name,
Employee_ID
FROM Com.dbo.Workers
WHERE isnumeric(Employ...
I tried to make the title as clear as possible... here is my scenario:
I have 2 tables (let's call them table A and table B) that have a similar schema. I would like write a stored procedure that would select specific columns of data out of table A, and insert this data as a new record in table B.
Can someone point me in the write dire...
I'm using MS Sql 2005.
Why does this give me the correct results (returns 169 rows)...
select
*
from
[import_Data]
where
[import_Data].name not in
(
select
[import_Data].name
from
[import_Data]
inner join [resource] on [import_Data].name = [resource].name
where
[import_Data].Provide...
I am trying to select a record out of the table1 by joining table2 and using table2 columns in the WHERE statement. Col1 and Col2 in table1 can be stored in col1 of table2. I need to join col1 of table2 with either the value of col1 or col2 in table1
here is the sql statement I created so (pseudo):
SELECT
t1.Col1,
t1.Col2,
t...