sql-server-2005

SQL statement to select group containing all of a set of values

In SQL Server 2005, I have an order details table with an order id and a product id. I want to write a sql statement that finds all orders that have all the items within a particular order. So, if order 5 has items 1, 2, and 3, I would want all other orders that also have 1, 2, and 3. Also, if order 5 had 2 twice and 3 once, I'd want ...

VS2010 Database Project Schema View "red cross" against object

Having just created a new database project in VS2010 using the wizard I've noticed that in the schema view some objects have a red cross in the object icon. Does anyone know what this is supposed to indicate? TIA ...

Execute SQL query from Rails when connecting to SQL Server 2005 through activerecord-sqlserver-adapter

So I'm connecting to SQL Server 2005 from my Rails app via the activerecord-sqlserver-adapter. I'm able to execute stored procs by doing Mymodel.execute_procedure("thisProcedure", param1, param2) But I have a stored proc that has a SQL INOUT variable that I'm having trouble with. I execute it, and I'm not seeing that variable returne...

XXX Schema default

In Microsoft SQL Server, I have a schema XXX. Q: How do I create a user XXX such that issuing the following command: SELECT * FROM Table is the same as SELECT * FROM XXX.Table Here's what I have so far: CREATE SCHEMA XXX authorization dbo -- I think CREATE LOGIN XXX WITH PASSWORD = '123'; CREATE USER ItDontMatter FOR LOGIN XXX WIT...

sql constraints

Why do i have to drop all constraints (keys in general) before i can drop a table in sql server. I dont understand why this would be necessary if I have permissions to drop the table and the know how to do it why not drop all constraints for me? Is there some sort of technical or database design reason for this? ...

Crystal Reports and MS SQL servers

We are having some major problems with reports at this time. We have some very large databases that have been using the same reports for years. Lately with our newwer builds we find that some reports will not work. We would go in and set the overridden qualified table names and set DNS to false, rebuild the views and then the report woul...

SQL Server: How to list all CLR functions/procedures/objects for assembly

Question: In SQL Server 2005, how can I list all SQL CLR-functions/procedures that use assembly xy (e.g. MyFirstUdp) ? For example a function that lists HelloWorld for query parameter MyFirstUdp CREATE PROCEDURE HelloWorld AS EXTERNAL NAME MyFirstUdp.[SQL_CLRdll.MySQLclass].HelloWorld GO after I ran CREATE ASSEMBLY MyFirstUdp FROM ...

Managing an SQL 2005 instance from management studio that ships with SQL Server 2008 R2

Is there any one using the management studio that comes with SQL Server 2008 R2 to manage an SQL server 2005 SP3 instance?. Are there any issues to watch out for? Any recommendations? Thanks. ...

converting a string in a time data type

I am making an application which requires entering time into the system. I made the time field in the database as nvarchar(5). When I input data into this field using the form in the application it is entered as a string is there a way in which I can convert it into a string into time format? ...

SQL Server timestamp column not supported in Mono?

I am trying to run a .NET command line application in Linux using Mono. It accesses SQL Server 2005 Express database and, when querying some data it throws the following exception: System.NotSupportedException: Unknown Type : timestamp at Mono.Data.Tds.TdsMetaParameter.GetMetaType () [0x00000] at Mono.Data.Tds.Protocol.Tds70.Write...

SQL Server 2005 XML Query problem with ".exist" method

I have this XML Query in SQL Server 2005: SElECT XmlField FROM tablename WHERE xmlField.exist('(/Root/Name[id="10")[1]') = 1 However, I want to replace the value "10" with a parameter that I pass to the Stored Procedure. How do I achieve this? I have tried using "@variablename" but it doesn't work. Thanks in advance. ...

SQL: not exist with openquery not working as expected

Hi, I do have an oracle 8 database from which I want to fetch data to SQL Server 2005. The following statement works fine, if the table on SQL Server 2005 is empty. If I run with let's say one entry missing, it does not work. Please let me know, if any additional information would be usefull! SELECT wdmsoracle.NO FROM (Select * from OP...

Country, state list for SQL Server

Hi In a application i need to list all countries in the world , we i select a country all the states corresponding to it should be listed, if i select as state all the cities in that state should listed. I have created the tables with references in SQL Server 2005. I need the complete data list of countries , states, cities Is there...

I have the following table and want to turn the rows into different columns

ID Code Date 1 3101 10/2/2010 1 3101 15/2/2010 2 3101 18/2/2010 2 3101 25/4/2010 2 3101 21/4/2010 into the following ID Date1 Date2 Date3 Date4 Date5 1 10/2/2010 15/2/2010 2 18/2/2010 25/4/2010 21/4/2010 ...

help with recursive tsql query

Using Sql-Server 2005. I have Users table with 3 columns. userID, parentUserID and userEmail. Top level users have null in parentUserID, child users have some userID. I want to write a query that counts children up to lowest level of certain userID. Select user with all his children, their children .... so on. I just need count of tho...

SQL query to avoid duplicates and order by a row

Need a SQL query using joins I need a help Table name: RVW_TSK RVW_ID UPC_CD CMPL_DATE 00001  10101010  10-10-2009 00002  10101010  13-10-2009 00003  20202020  5-11-2008 00004  20202020  8-11-2008 Expected result is like: RVW_ID UPC_CD CMPL_DATE 00002 10101010 13-10-2009 00004 20202020 8-11-2008 I want the lates...

Can’t locate Local Publications folder under Replication folder in SQL Server 2005

Can't locate Local Publications folder under Replication folder in SQL Server 2005. Replication components are installed. I am using SQL Server 2005 not express edition. I just installed SQL Server 2005 with replication compoenents and right click on Replication folder, there I can see just 3 options. 1) Update Replication Password. 2) ...

SQL Search column for each variable in CSV string

I have a variable passed to stored procedure Ex: @keywords = 'val1, val3, val5' And i'm trying to see if column named Title contain any of them in it Ex: Title1 - 'Hello val1' Title2 - 'Hello val3' Title3 - 'Hello val1, val3' Title4 - 'Hello' SO my results should return values Title ------ Hello val1 Hello val3 ...

SQL Server 2005 Network IO Wait Times (ASYNC_NETWORK_IO Wait Type) Problem

Hi, We are having performance issues on our web app during peak times, which is currently split between one 2003 IIS6 Web Server, and one SQL Server 2005 DB Server. The query times on the DB server look fine (around 30ms), and the CPU is low (under 20%), yet queries can take a long time to execute on the web server (over 1 second). I w...

How to implement badges?

I've given some thought to implementing badges (just like the badges here on Stack Overflow) and think it would be difficult without Windows services, but I'd like to avoid that if possible. I came up with a plan to implement some examples: Audobiographer: Check if all fields in the profile is filled out. Commentor: When making a comm...