tsql

SQL Server 2008 FTS CONTAINSTABLE Not Returning More Than Five Rows

I have a single table called "Indexes", it contains one nvarchar and three ntext columns (all Full Text Indexes). Index is up to date. CONTAINSTABLE(Indexes, *), 'test', 5) //5 results No matter what I change the above keyword too, it only returns the first 3-5 results. It should roughly return 90-120 results, for the above query. SE...

How can I get all the database names in a sql server instance using tsql?

Hi guys, How can I get all the database names in a sql server instance using tsql? Great thanks. ...

In SQL Server, how can I execute a piece of tsql against all databases in an instance?

Hi guys, In SQL Server, how can I execute a piece of tsql against all databases in an instance? Great thanks. ...

why t-sql patindex not work with declared variable?

why this query is not work correctly? declare @s nvarchar set @s = 'abcd' select patindex('%b%', @s) it is return zero. ...

In SQL Server 2005, how can I use database_b, do something, then use the old db database_a in TSQL?

Hi guys, In SQL Server 2005, how can I use database_b, do something, then use the old db database_a in TSQL? The following is my code but there is some problem with it. Who can help me to identity the problem? Great thanks. DECLARE @old_database_name VARCHAR(200) SET @old_database_name = db_name() use mydatabase create table t1(id in...

T-SQL Right Joins to ALL Entries inc Selected Column

Hi Experts, I have the following Query which produces the output below; SELECT TBLUSERS.USERID, TBLUSERS.ADusername, TBLACCESSLEVELS.ACCESSLEVELID, TBLACCESSLEVELS.AccessLevelName FROM TBLACCESSLEVELS INNER JOIN TBLACCESSRIGHTS ON TBLACCESSLEVELS.ACCESSLEVELID = TBLACCESSRIGHTS.ACCESSLEVELID INNER JOIN TB...

TSQL to combine a date field and a time field

Using logparser to import IIS logs to a db results in one column that has the date value and a second field for time: 2010-05-25 00:00:00.000 2010-01-01 11:11:58.000 I'd like to code an INSERT AFTER trigger that combines the 2 fields. ...

How to use a varying database?

I want to use a database which name is stored in a variable. How do I do this? I first thought this would work but it doesn't: exec('use '+@db) That will not change database context Suggestions anyone? ...

TSQL - How to URL Encode

Hello Everyone, Looking for a bug free tested sql script that i could use in a UDF to encode a url through sql. Function would take in a URL and pass out a URL Encoded URL. I have seen a few, but all i have come across seem to have some flaws. Thanks in advance, Billy ...

What's the meaning of the GO-statement in TSQL

I'm new to TSQL and wondering what the GO statement really means. To me it just seems thrown in there where ever it seems to fit. I guess it somehow tells sql server to run the previous statement? What happens if you don't use them at all? Could someone give an example where a statement will break if I don't use a GO. Please elaborat...

SQL Server 2000: Why is this query w/ variables so slow vs w/o variables?

I can't figure out why this query would be so slow with variables versus without them. I read some where that I need to enable "Dynamic Parameters" but I cannot find where to do this. DECLARE @BeginDate AS DATETIME ,@EndDate AS DATETIME SELECT @BeginDate = '2010-05-20' ,@EndDate = '2010-05-25' -- Fix date range to...

Creating SQL table using Dynamic variable name

I want to create backup SQL tables using variable names. something along the lines of DECLARE @SQLTable Varchar(20) SET @SQLTable = 'SomeTableName' + ' ' + '20100526' SELECT * INTO quotename(@SQLTable) FROM SomeTableName but i'm getting Incorrect syntax near '@SQLTable'. It's just part of a small script for maintence so i ...

AVG time spent on multiple rows SQL-server?

I have a table tblSequence with 3 cols in MS SQL: ID, IP, [Timestamp] Content could look like this: ID IP [Timestamp] -------------------------------------------------- 4347 62.107.95.103 2010-05-24 09:27:50.470 4346 62.107.95.103 2010-05-24 09:27:45.547 4345 62.107.95.103 2010-05-24 09:...

if else within CTE ?

I want to execute select statement within CTE based on a codition. something like below ;with CTE_AorB ( if(condition) select * from table_A else select * from table_B ), CTE_C as ( select * from CTE_AorB // processing is removed ) But i get error on this. Is it possible to have if else within CTEs? If not is there a w...

SQL Select Permissions

I have a database that I need to connect to and select from. I have an SQL Login, let's call it myusername. When I use the following, no SELECT permission shows up: SELECT * FROM fn_my_permissions ('dbo.mytable', 'OBJECT') GO Several times I tried things like: USE mydatabase GO GRANT SELECT TO myusername GO GRANT SELECT ON DATABASE...

Update a single row with t-sql

I want to update a row in my date base. The problem is, through a mistake on my part, I have two identical rows of data. How do I run the update on just one row? ...

Stored Procedures In Source Control - Automate Build/Deployment Process

My company provides a large .NET service-oriented solution. The services layer interact with a T-SQL back-end consisting of hundreds of tables and stored procedures. Our C# code is in version-control (SVN) but our stored procedures and schema are not. After much lobbying of expedient upper-management, I was allowed to review our (non-ex...

Start position for a reused t- sql cursor?

I'm working on a stored procedure that uses a cursor on a temporary table (I have read a bit about why cursors are undesirable, but in this situation I believe I still need to use one). In my procedure I need to step through the rows of the table twice. Having declared the cursor, already stepped through the temporary table once and c...

Bulk update + SQL + self join

Hello All I would like to update a column in Table with reference to other column(s) in same table. Ex: As in figure below - I would like to update effective endate with min date whichever is greater than effective Start Date of currrent record. How can this be acheived in T-SQL. Can this be done with single update statement ? Th...

how can i substitute a NULL value for a 0 in an SQL Query result

SELECT EmployeeMaster.EmpNo, Sum(LeaveApplications.LeaveDaysTaken) AS LeaveDays FROM EmployeeMaster FULL OUTER JOIN LeaveApplications ON EmployeeMaster.id = LeaveApplications.EmployeeRecordID INNER JOIN LeaveMaster ON EmployeeMaster.id = LeaveMaster.EmpRecordID GRoup BY EmployeeMaster.EmpNo order by LeaveDays Desc ...