I currently have a SQL query that returns results based on a dynamic number of keywords passed in. I convert the list of keywords into a table and join to it. SELECT * FROM Table1 INNER JOIN dbo.udf_List2Table(@Keywords, ',') ON (Field1 LIKE '%'+Keyword+'%') This is working fine but it returns all rows that contain an...
I have the following update statement: update db1.dbo.table1 set db1.dbo.table1.col = db1_bk.dbo.table1.col where db1.dbo.table1.id = db1_bk.dbo.table1.id no error.. it just won't update the table row. however if i run update db1.dbo.table1 set db1.dbo.table1.col = '2010-01-01' where db1.dbo.table1.id = 2 it does work. any ideas?...
I have two tables: User --------------------- id primary key name varchar mobile varchar Sample data: (1, airtel, '9887456321,6985326598,88523695874') Client --------------------------- id primary key clientname varchar mobileno varchar Sample Data: (1,John Doe, 98874563...
I've got a simple table "Logins" with two columns: username (nvarchar) logged (datetime) It's really simple, just records the username and datetime when someone logs into my web application. Sometimes, however, people login a few times in one minute... I want to try to run a query to filter those results and only have it return one ...
I'm using SQL Query Analyzer to build a report from the database on one machine (A), and I'd like to create a temp table on a database server on another machine(B) and load it with the data from machine A. To be more specific, I have a report that runs on machine A (machine.a.com), pulling from schema tst. Using SQL Query Analyzer, I l...
Is there a way to connect to a remote sql server using management studio with windows credentials other than the current login credentials? Right now i remote desktop into the sql server machine and login with the windows credentials that I want to connect with. Thanks. ...
I want to know if it is possible to show a parameter with some T-SQL statement. For example, there is a PARAMETERIZATION parameter. One can set it with such statement: SET PARAMETERIZATION FORCED How to know parameter's status? ...
Hello, I'm trying to write a query that gives me a percentage (i.e. something like .885, for example) by dividing 2 aggregate numbers I selected via SUM. But my results are coming out as 0.0 instead of the correct number. So just for reference, starting with 2 queries, I have: SELECT SUM(CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1...
I am supporting an ETL process that transforms flat-file inputs into a SqlServer database table. The code is almost 100% T-SQL and runs inside the DB. I do not own the code and cannot change the workflow. I can only help configure the "translation" SQL that takes the file data and converts it to table data (more on this later). Now that...
I have a table var with some rows but only one column of type DATETIME, like this: [Day] 2010-08-03 2010-08-04 2010-08-10 2010-08-11 I need to show on some columns but in only one row. My result set will be limited to 5 rows, then I can limit to 5 columns too. Example of what I need: [Day1] [Day2] [Day3] [Day4] [D...
Is there anything I can do in Access that would mimic the behavior of the TSQL ROW_NUMBER() function, when doing an INSERT query? ...
Hello, I have a view in SQL server that translates from one schema version to another. Currently, the view looks like this: SELECT newValue AS oldValue FROM dbo.MyTable The trouble is that, in the new schema, newValue is not nullable, so we set it to -1 to denote empty fields, but in the old schema, it was nullable. How can I do s...
I'm interested in the most elegant way of creating a solution using all T-SQL that will allow me to target any view that is currently in the database using the WITH SCHEMABINDING option and dropping it then recreating it. We need to assume here that we don't have access to the original DDL scripts so the Views must be recreated only from...
I have a question, but let me first say that this is being performed on a database which I inherited and I am currently trying to improve the design on. Also the reason for the syntax is the fact that there are a lot of tables that the datatypes have been 'tweaked' by tech support people (/facepalm) causing lots of issues. IF NOT EXISTS...
Once upon a time, I remember running some TSQL which would display which network protocol (e.g. named pipes, TCP/IP) was being used by each active session - but I can't remember the actual code. Does anyone know how to do this ? An input parameter might have been SPID. The code was useful for diagnostic purposes. ...
Table: Customer Name Type Amount James P 125.00 James P 125.00 James P 125.00 James R 225.00 James R 225.00 Rajiv R 155.00 Rajiv R 155.00 Rajiv R 155.00 Rajiv P 150.00 Rajiv P 150.00 Saran R 175.00 In this table structure I want a output which will give each person’s count of P, count...
Hi, I need to delete some rows from table where indexes are equal indexes in table variable declare @m_table as table ( number NUMERIC(18,0) ) ... inserting some rows into @m_table ... DELETE ct FROM [dbo].[customer_task] ct inner join project_customer pc on pc.id_customer = @m_table.number inner join cust...
I have a table that have 3 columns id, company and adress i found a bug today that saved the adress in the company-column and company in the adress-column SOMETIMES, i have corrected the bug and now im trying to put the data in the right places every adress has a number in it so my guess is that the easiest way is to switch adress and...
Hi, I am developing some stored procedures in SQL Server 2008. Some of our customers are running SQL Server 2000. Is there a way to check whether my queries are compatible without having to install SQL Server 2000? Maybe a transact sql tester? Regards, Michel ...
What is the best way to write a dynamic parametrized query for sql server 2005 where passed parameter value may be null? ...