tsql

How can I get a username and password from my database in C#?

I have the following code in my btn_click event: Sqlconnection con = new Sqlconnection("server=.;database=bss;user id=ab;pwd=ab"); con.open(); SqlCommand cmd = new Sqlcommand("select * from login where username='" + txt4name.Text + "' and pwd='" + txt4pwd.Text + "'", con); SqlDataReader reader = cmd.execute Reader(); Where login is ...

Attaching DB with full-text catalogs under a different name?

I have a database with regular full-text catalogs. I want to detach this database, copy it to a different server and attach it under a different name (same name but with '_BAK' appended to it). I am using SQL Server 2005. Here is the error trying to attach DATABASE with full-text catalogs under the name DATABASE_BAK Warning: Identity ...

Recursive SQL CTE's and Custom Sort Ordering

Image you are creating a DB schema for a threaded discussion board. Is there an efficient way to select a properly sorted list for a given thread? The code I have written works but does not sort the way I would like it too. Let's say you have this data: ID | ParentID ----------------- 1 | null 2 | 1 3 | 2 4 | 1...

Advanced SQL INSERT

I have this query that works fine. It selects rows into the SY.UserOptions table for the ‘Jeff’ user. However, I created another query that I want to do the same thing, but for every user. So I added SY.Users to the query, which in effect mulplies the 2 tables together. However, it gives me an error that I do not understand. -- This wo...

scope_identity vs ident_current

After much research I am a little confused by which identity tracker I should use in sql. From what I understand scope_identity will give me the last id updated from any table and ident_current will will return the last id from a specified table. So given that information it would seem to me the best version to use (if you know which ...

SQL IsNumeric Returns True but SQL Reports 'Conversion Failed'

Assuming the following data: Column1 (data type: varchar(50)) -------- 11.6 -1 1,000 10" Non-Numeric String I have a query,which is pulling data from this column and would like to determine if the value is a number, then return it as such in my query. So I am doing the following SELECT CASE WHEN IsNumeric(Replace(Column1,'"','')...

What is the best way to assign the returned value of a stored proc to a variable in SQL?

I have a stored procedure that returns a valueI call it from other stored procedures that need to retrieve this value. The calling stored procedure is inside a transaction, the stored procedure that returns the value (and actually creates the value and stores it in a table that no other proc touches) is not inside its own transaction, bu...

How do I suppress the results from a stored procedure from within a stored procedure?

I've got a stored procedure (we'll call it A) that calls another stored procedure (we'll call this one B). B includes a SELECT that I do not want to send back to the caller of A. Here is some really rough pseudocode, but it should get the idea across. PROCEDURE A CURSOR CALL B -- I WANT TO SUPPRESS THE RESULTS FROM B EN...

Reset primary key (int as identity)

I have a table with a primary key as bigint (identity property is Yes and staring from 1 by 1). This table is in production and has been updated on daily bases: lots of deleting and inserting. The problem is that this key is growing too big right now with 8 digits. I worry about overflow one day eventually. Fortunately, this key is not...

SQL Server file and filegroup

Hello everyone, I can not think of any reasons why we need to have multiple files inside a file group. The reason why I think of this way is we can control from T-SQL (end user) level about file group, but can not control from T-SQL (end user) level about individual files of a file group. Any comments or ideas why files are still needed...

Easy way to add an ID column when there is data present

Is there an easy way to add an ID (Identity(1,1) & PK) column to a table that already has data? I have picked up a project that was freelanced out to a horrible developer that didn't put a PK, index or anything on the tables he made. Now that I am LINQ-ifying it, I have no PK to insert or update off of. ...

Convert date in TSQL

I am moving some data and I need to come up with a TSQL statement to convert dates currently in a datetime field to another database field with the varchar MM/yy format. This statement needs to work on both SQL Server 2k5 and also SQL Compact Edition 3.5 - so the answer needs to be "set" based and not include cursors etc that are not s...

Select COUNT(*) of subquery without running it twice

I've got a procedure to return a result set which is limited by page number and some other stuff. As an OUTPUT parameter I need to return a total amount of selected rows according to the parameters except the page number. So I have something like that: WITH SelectedItems AS (SELECT Id, Row1, Row2, ROW_NUMBER() OVER (ORDER BY Row1) AS Po...

SQL Server Procedure returns multiple tables - Insert results into tables

I have a procedure that returns multiple tables; eg: PROCEDURE Something AS BEGIN SELECT 1,2,3 SELECT 4,5 SELECT 9,10,11 END I would like to take each table from the result and insert it into a series of tables/temp tables - one for each record set. Is this possible? ...

How can I return multiple rows as a single row in T-SQL?

A few months ago our vendor added a capability to our ticketing system which lets us add any number of custom fields to a ticket. I'd like to query these fields out along with the other call information for reporting purposes, but each extensible field is stored as a row in the database. So basically you have something like this: ext_...

SQL select where repeating, but distinct on another column.

I have the following table: uid | key | email ----------------- 1 | 1 | [email protected] 2 | 2 | [email protected] 3 | 3 | [email protected] 4 | 4 | [email protected] 5 | 4 | [email protected] 6 | 4 | [email protected] 7 | 6 | [email protected] 8 | 7 | [email protected] I'd like to grab all of the rows with distinct key but repeatin...

Adding a LineString to a MultiLineString

With SQLServer 2008 I'm trying to transform two LineStrings to a multiple LineStrings (preferably a MultiLineString), by splitting them up in part based on each other. L1 |---------------| L2 |----| = |----|----|-----| Thinking in generic terms I can get the centerpart by using L1.STIntersection(L2). The two other parts I can ...

What are your naming conventions for SQL Server objects?

There are seemingly a lot of very diversive naming convention recommendations for sql server objects. I was wondering if there are some formal guidelines other than not prefixing stored procedures with sp_ ? As pointed out by Matt, there is already a similar question: http://stackoverflow.com/questions/7662/database-table-and-column-n...

T-SQL - how to swap rows and columns

I have a resultset structure like this ID Value Name 1 Oranges Reponse 1 42 Count 2 Apples Reponse 2 65 Count 3 Figs Reponse 3 74 Count and I want to get to this: ID Response Count 1 Oranges 42 2 Apples 65 3 Figs 74 using SQL. Is there a way to do this? thanks! ...

How can I have multiple common table expressions in a single SELECT statement?

I am in the process of simplifying a complicated select statement, so thought I would use common table expressions. Declaring a single cte works fine. WITH cte1 AS ( SELECT * from cdr.Location ) select * from cte1 Is it possible to declare and use more than one cte in the same SELECT? ie this sql gives an error WITH cte1 a...