Hi guys, In SQL Server, there are some *DMV*s that appear in all databases and have the same content in it. What's the purpose of this approach? For example, the following 2 query will give the same result. select * from master.sys.databases select * from tempdb.sys.databases Thanks. ...
I have sales table data as shown below I want it to display group wise sales according to Date. Sales table contains data for different groups but my query shows only two rows. The SQL Query: select i.gName, sum(Quantity) as '180ml', isnull((select sum(Quantity) from saleslog where BillDate='12-10-201...
I am passing parameters to a stored proc. The parameters code block on the asp.net side is: SqlConnection con = new SqlConnection(strConn); string sqlItemSearch = "usp_Item_Search"; SqlCommand cmdItemSearch = new SqlCommand(sqlItemSearch, con); cmdItemSearch.CommandType = CommandType.StoredProcedure; cmdItemSearch.Parameters.Ad...
I have a set of sql scripts which I send to SQL server using a SqlCommand object in C#. These scripts create stored procedures and as long as I just create the procedures, everything works finde. If my scripts contain the usual "if exists ... drop XYZ; create procedure XYZ ..." block, I get an error which tells me, that create must be th...
hi all, please help me with this query select pName,Price, (Price*Quantity) as SalesValues from saleslog where BillDate='12-10-2010' and pGroup=15 group by pname, Quantity, Price; This query is intended to show sales summary according to date but it is showing duplicate Products Name. Output of above query MAK 2 T OIL 5LTR. 635 ...
Hi folks, i'm having problem with a query generated by mondrian OLAP Server . it's taking too long. the query look like as follows. select "TABLE1"."column0" as "c0" from "FACT_TABLE" as "FACT_TABLE", "TABLE7" as "TABLE7", "TABLE6" as "TABLE6", "TABLE5" as "TABLE5", "TABLE4" as "TABLE4", "TABLE4" as "TABLE3", "TABLE2" as "TABLE2", "TA...
Hi guys, I want to use sp_msforeachtable to do some work for some tables in a database. I use the IF statement to filter the tables. But it doesn't give me the right answer. As the following script shows, I use AdventureWorks to do the testing. I want to do some work on every table except Person.Address, Person.Contact, Person.CountryRe...
Let's say I have a table like this: Task Type Variable Hours Duration One A X 10 5 One A Y 40 15 One B X 100 29 Two A X 5 2 Two B X 15 9 Two A Y 60 17 Three A Y 18 5 W...
I'm using DBDeploy.NET for change control management in my T-SQL codebase. DBDeploy works by the developer maintaining a numbered set of change scripts. When a deploy is triggered (via NAnt), DBDeploy looks at a changelog table and determines which patches need to be executed to bring the instance up to date. The issue I have is with ...
I have a query that returns a result set of one column, but I want it to be converted into one long string for use as a subquery. I.E. I want to be able to do: SELECT user.*, (SELECT group_name FROM user_groups WHERE userID = user.ID) AS Groups FROM user However, that will fail because a user can belong to more than one group. For exa...
this question is based on answer got from another SO question, can be found here. I have managed to write a query myself based answer provided there Select s.pName, s.ProductCode, min(s.Price) as MinPrice, sum(s.Quantity) as SalesQty, sum(s.Price * s.Quanti...
I am new to writing SQL fucntions and trying to write a scalar function in T-SQL. The function should insert leading zeros into an input field of numbers the length of the input can vary in length, i,e,. 123, 1234567, 9876543210 and so forth. The input field is defined as varchar(13) here is the code that I have written for the function...
Is it possible to assign a DB role to a DB user in Sql Server 2008 using t-sql? If so, how? ...
I'm attempting to create a T-SQL case statement to filter a query based on whether a field is NULL or if it contains a value. It would be simple if you could assign NULL or NOT NULL as the result of a case but that doesn't appear possible. Here's the psuedocode: WHERE DateColumn = CASE @BitInput WHEN 0 THEN (all null dates) WHEN ...
Is it possible to use CTE in a WHERE clause e.g. SELECT * FROM Table1 WHERE Table1.PK IN ( WITH Cte AS ( -- root selection (dynamic, generated in code) SELECT Bla FROM Table2 -- recursive part UNION ALL SELECT …..) SELECT Bla FROM Cte) The reason I’m asking is that I need to use a recursive query and the ...
Okay so i've got a stored proc which does a bunch of things (not important). Now, the stored proc is quite verbose, it prints out a lot of things (progress, rowcounts, etc). As you all know, using PRINT with variables is extremely painful (requiring you to have a CAST party). So, ive used RAISERROR to print these friendly messages (ev...
Hi I have two table in a sql databse one has "institutionName, ipAddress" columns and the other one has "ipAdddress, totalHits". I want to join the two table based on ipAddress field, but the problem is the ipAddress in the first table is in a for of "xxx.xxx.xxx.xxx" but in the second table it is in three forms ie "xxx.xxx.xxx.xxx" or ...
I'm using SQL-Server 2005. I have two tables Users and Payments which has a foreign key to Users. Both Users and Payments have a date column (Users set their value during registration and Payments gets a value during payment). Users has a column called isPaymentsRecurring as bit which tells me to renew the user or not. The value is 1 on...
I have 2 tables: T1(IDa,IDb) : has data like this IDa IDb 1 2 3 4 5 6 7 8 and T2(IDc,IDd) : with data like this IDc IDd 1 2 4 5 3 6 7 8 and the Identity for each table is the pair of IDs: T1, Identity is IDa and IDb T2 is IDc and IDd The question is:...
I have a function call in a query, and that result has to be returned with two names. Example: SELECT myFunction(column1) as Name1, column2 as Name2 FROM myTable I want the result of myFunction to be returned in two different columns. Is this possible without making another function call in the column list? ...