Find what table a specific index belongs to
If I have the name of an index, what query can I use to find what table the index belongs to? ...
sql-server
Distinct records when joined with a table in SQL Server
How do I get top 1 record for each product from the query below? SELECT DISTINCT o.product, o.orderID, od.qty, od.amount FROM orders o, orderdetails od WHERE o.orderID=od.orderID Product OrderID Qty Amount Pen 11222 10 100.00 Pen 11223 5 50.00 Pen 11224 1 10.00 Book 22222 1...
Performing search on SQL Server 2008 database
We have built an ASP.NET application (with C#.net language) and hosted on Windows Server 2003 Operating System. The database is SQL Server 2008. Now we need to do a search involving 4 tables and one of the column of table is varchar (2400) and the other columns are of normal lengths (E.g. Varchar(50) etc.). This search gets fired when...
How to redefine Distinct
My boss has given me an assignment that I'm not sure is possible since after about two weeks, I can't figure out a solution, so I'm throwing this out to ask for any sort of help from the SO group. If this breaks your brain, I apologize. A little background first: We develop a database querying application that allows users to get back...
How to protect SQL code in SQL Server
Is it possible to protect SQL 2008 stored procedure code from anyone`s eyes? Maybe some encryption, or assembling like dll? ...
PDO Connection string for SQL Server 2005
What should my PDO constructor/DSN/connection string look like to connect to a SQL Server 2005 database? $dbh = new PDO('??'); ...
SQL Server 2005 View vs Materialized View vs Stored Procedure
If I have a table that contains tens of thousands of records of accounts for multiple locations. Is there any speed difference if I query a view that selects all accounts for a specific location and a stored procedure that with the same SQL statement? EDIT: I ran across this Materialized View. It seems like this would always be used. ...
How do I create a simple table in SQL Server 2005?
I need to "use a database" and create a table with the following attributes in SQL Server 2005: Table name is "quiz_mailing_list" Fields are: id (typical auto-increment primary key) email (varchar size 256) optIn (boolean or tinyint size 1) referringEmail (varchar size 256) Can someone give me the command to "use the database" and the ...
What is the WITH statement doing in this example? I'm trying to randomly generate data.
INSERT INTO files (fileUID, filename) WITH fileUIDS(fileUID) AS ( VALUES(1) UNION ALL SELECT fileUID+1 FROM fileUIDS WHERE fileUID < 1000 ) SELECT fileUID, TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdefgHij', '1234567890' ) FROM fileUIDS; ...
Can you create nested WITH clauses for Common Table Expressions?
WITH y AS ( WITH x AS ( SELECT * FROM MyTable ) SELECT * FROM x ) SELECT * FROM y Does something like this work? I tried it earlier but I couldn't get it to work. ...
Self Joins instead of subqueries
Can someone figure out how to get rid of the NOT EXISTS statements in the WHERE clause? SELECT TOP 100 PERCENT Ftm.AcctID AS [Acct Id], Act.AccountNumber AS [Account No.], Act.AccountTypeId, ISNULL(Cnt.FirstName, '')+ ' ' + ISNULL(Cnt.LastName, '') AS [Full Name], Ftm.FinTransTypeCode AS [Trans Type], Ftm.F...
SqlException (0x80131904): Line 28: Incorrect syntax near '('.]
Please someone help me! I have an app running on windows 2003 with sql server 2005. When I try to deploy this some app in other server on windows 2003 with sql server 2000, some pages of the app shows the message bellow: Server Error in '/' Application. Line 1: Incorrect syntax near '('. Description: An unhandled exception occurred dur...
uniqueidentifier vs identity
I noticed asp_membership uses uniqueidentifier, and to me, it seemed like a waste of space, because I could not think of a particular reason to not replace it with identity. However, SQL Server is not letting me change it to int. It says "Conversion from uniqueidentifier to int is not supported on the connected database server". After...
Convert SQL Server signed int to Binary(4)
I messed up. I wanted to store IP addresses compactly in SQL Server and chose 'int' for the column type. 'int' are 32 bit signed integers while IPs really are 32 bit binarys. My question is: How do I convert my existing signed int into Binary(4) in SQL Server and how should I properly parse the string-IP representation from .Net 'Reques...
SQL - Updating records based on most recent date
I am having difficulty updating records within a database based on the most recent date and am looking for some guidance. By the way, I am new to SQL. As background, I have a windows forms application with SQL Express and am using ADO.NET to interact with the database. The application is designed to enable the user to track employee att...
SQL Server XML Data Type query issue.
Please see below SQL Server 2005 script Declare @xmlData XML SET @xmlData = '<?xml version="1.0"?> <bookstore xmlns="http://myBooks"> <book genre="autobiography" publicationdate="1981" ISBN="1-861003-11-0"> <title>The Autobiography of Benjamin Franklin</title> <author> <first-name>Benjamin</first-name> <l...
Recommended approach on handling SqlExceptions in db applications
Hi, I work on a database application written in C# with sql server as backend. And for data integrity, I try to enforce as much as possible on database level - relations, check constraints, triggers. Due to them, if data is not consistent, the save / update / insert can fail, and app throw SqlException. I do various validations both i...
SQL Server 2005 loading data from an external server
Have a new project with the following setup and requirments:- My client has a MSSQL 2005 server (A) in their office. Their vendor has a MSSQL 2005 server (B) in another part of the world, which contains real-time transactional data. My client wants to load the data from (B) to (A) on a daily basis during non office hours. They have data...
Why is SQL Server deprecating SET ANSI_PADDING OFF?
According to MSDN BOL (Books Online) on SET ANSI_PADDING, In a future version of Microsoft SQL Server ANSI_PADDING will always be ON and any applications that explicitly set the option to OFF will produce an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. I h...
Which schema does this associative table belong to?
I was going over AdventureWorks2008 database and wanted to create a new table that associates a product to a sales person. There is a many-to-many relationship between those tables. The question is, Of two schemas, Sales and Production, does ProductSalesPerson table belong to? ProductSalesPerson doesn't neccessarily belong to either ...