tsql

How to select only those rows which contain only substring of input's parameter

I have a table in MSSQL that has several columns. It looks as follows: col1| col2 | col3 ------------------ HSP | full | "" HSP | full | "UE" HSP | min | "" HSP | min | "PS" CC | full | "UE" My select query has 2 parameters, which receives from web app: @col2 = { full, min } @col3 = e.g. "PE+UE+STR" or "PE" or "" (in general - c...

Howto call views inside another SELECT statement (SQL Server)?

I created a view. The view's Query works fine. Now I want to write another Query which uses the first one. As far as I know it should look like this: SELECT * FROM myView; Now there is an Error returned: "Ungültiger Objektname 'myView'" in English its something like "unvalid object name 'myView'. How do I refer to or call views the r...

What do the *= and =* operators do in T-SQL?

Possible Duplicate: SQL: What does =* mean? I am going through some legacy code and found a query like this: SELECT * from foo_table, bar_Table where foo_table.id *= bar_table.fac_id What does the *= operator do? ...

Sql server - "Map" function?

I have a stored procedure that takes a product_id and returns some data about the product. I would like to make a query or procedure that maps this stored proc over a "select * from products" query. Is there a way to do this? ...

How to identity the character (set-based)?

Recently a friend of mine has shown me a question as under declare @t table(data varchar(50)) insert into @t select ' _ |_ |_ ' God knows from where he got this idea ! How can I find that it is CL How to do that in a set-based manner? Thanks a lot ...

SQL Server Full Text Search - Create one computed column

Hi all, I am currently working on a project where I want to search for employees via just one input search term. For this I am using the SQL FTS. The table schema looks like this Employee table EmployeeId, Firstname, Lastname Sample data 1, John, Miller 2, Chuck, Norris Address table AddressId, EmployeeId, CityId, Street, Str...

How do I compare against the current week using SQL Server?

How do I compare an SQL Server date column against the current week? For instance: WHERE [Order].SubmittedDate = *THIS WEEK* ...

Sql Server : Lower function on Indexed Column.

I found one big issue. I have added the Lower function to indexed column of one of the table to fetch the data. The table contains more then 1 lac records. While fetching the records, the cpu usage goes to 100%. I could not understand, how this much drastic change can happen just because of Lower() function. Please Help! ...

Is it possible to add a identity to a GROUP BY using SQL?

Is it possible to add a identity column to a GROUP BY so that each duplicate has a identity number? My original data looks like this: 1 AAA [timestamp] 2 AAA [timestamp] 3 BBB [timestamp] 4 CCC [timestamp] 5 CCC [timestamp] 6 CCC [timestamp] 7 DDD [timestamp] 8 DDD [timestamp] 9 EEE [timestamp] .... ...

Role Permissions Filtered View t-sql

Is it possible to filter the content of a t-sql view based on SELECT GRANTS assigned to the database roles of end users without generating SELECT permissions exceptions? If so how? Pseudo: CREATE TABLE Beer(a(x), b(y)); GRANT SELECT ON Beer to BeerOnlyRole; CREATE TABLE Wine(a(x), b(y)); GRANT SELECT ON Wine to WineAndBeerRole; GRANT...

OLE Error in SQL

I have Windows Server 2003 Standard x64 Edition SP2 with Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) I downloaded CAPICOM Platform SDK Redistributable: http://www.microsoft.com/downloads/details.aspx?FamilyId=860EE43A-A843-462F-ABB5-FF88EA5896F6&displ...

How to remove ROWGUID designation on a PK column/key

I am attempting to strip a database of its current UID pks (while retaining the column) as part of a conversion process which will use all int keys. SSMS 2008 (SQL Express 2008) - Database originally created in SQL 2000. SQL 2008 full also installed if it helps. A number of the tables have a UID pk which is ROWGUID=yes It seems the R...

How to check .txt file existence with TSQL?

I have a SP reading a .txt file from File System and using Bulk Insert. I just wanna make sure if file exists before executing bulk insert command. How do i do that? ...

Select Dates Between Two Column Values

If I have a table with a StartDate column and an EndDate column can I produce a query that returns a set including every day in the range. I could use a table variable and do some procedural code but I'd like to know if there's a way to do it in a query. E.g. StartDate = 1/1/2010, EndDate = 1/5/2010, result would be: 1/1/2010 1/2/201...

wait for transactional replication in ADO.NET or TSQL

My web app uses ADO.NET against SQL Server 2008. Database writes happen against a primary (publisher) database, but reads are load balanced across the primary and a secondary (subscriber) database. We use SQL Server's built-in transactional replication to keep the secondary up-to-date. Most of the time, the couple of seconds of latency i...

SQL Server, T-SQL is there a faster way to substring the following string in my question?

I have strings like OPEN SYSTEMS SUB GR (GM/BTIB(1111)/BTITDBL(2222)/BTVY(4444)/ACSVTYSAG) in my database under my GROUPS Column. What I want to do is to extract the 2222 from that string. The code I am using is like that. SELECT SUBSTRING(GROUPS, CHARINDEX('(',GROUPS, CHARINDEX('(',GROUPS, CHARINDEX('(',GROUPS,0)+1)+1...

How to transpose a XML table with T-SQL XQuery?

Let's say we have the following XML: <root> <row> <column>row 1 col 1</column> <column>row 1 col 2</column> <column>row 1 col 3</column> </row> <row> <column>row 2 col 1</column> <column>row 2 col 2</column> <column>row 2 col 3</column> </row> <row> <column>row 3 col 1</column> <column>row 3 col...

Want data from table in SQL server

I have table data like this id id1 name 1 1 test1 1 1 test1 1 2 test2 2 1 test1 2 2 test2 3 1 test1 3 2 test2 3 2 test2 now from table i want the data as below like for id = 1 order by id1 asc the first name = test1 so i want the first two row id id1 name 1 1 test1 1 1 test1 not third r...

(T-SQL) Check if a procedure exists before creating it.

I decided to edit my post since people didn't quite get what I'm trying to do (I suppose it was not irrelevant to say why I needed it after all): Ok, there we go. The thing is, I have a HUGE SQL script which many clients use and it has to be ran thoroughly every time a client executes the "database management" functionality that our sof...

TSQL Selecting 2 & 3 week intervals.

I am attempting to populate a table based on 2 and 3 week intervals for a semi-monthly pay period in TSQL. The table should populate, 2 week date 2 week date 3 week date 2 week date 2 week date 3 week date ..based on the first date I supply, subsequently adding 2 or 3 weeks to the last date supplied. I should be able to supply a star...