How do I delete duplicate data in SQL Server?
I have sql table but, some value added more than once, I need to delete one of them. Can you give me a simple query? ...
sql-server
SSPI Connection in .Net 2.0 Web Service
I'm trying to test a .Net 2.0 Web Service I created in C#. One of our servers is on windows 2000 and can currently only support .Net 2.0 . I can test it local to my machine and it works fine but when I move it to the target server for production it produces an error. I want it to use Windows authentication, and have it defined as such i...
Looking for application that generates INSERT statements for data in table(s)
Looking for an application that allows me to connect to an SQL Server, select any number of tables, and then generate INSERT statements for the data contained in these tables. Anyone knows of such an app? Preferably freeware but commercial versions could be interesting as well. Thanks, Bruce ...
SQL Server - Selectively inserting fields into temp table
I am executing a SP within a SP. The SP returns say 10 params. I am interested in only 5 of them. How do I insert only these 5 into the temp table. The code I have so far: DECLARE @tmpUnion TABLE ( UnionCode VARCHAR(10), UnionDate DATETIME, UnionPosition VARCHAR(30), UnionInitFees BIT, UnionDues BIT ) --get...
MS TSQL for xml path question regarding xpath element
Hi there, I have the following TSQL statement: select tblName "TblName", structure "TblName/STRUCTURE", sqlRetrieve "TblName/SQLRETRIEVE", Identifier "TblName/IDENTIFIER", '2' "TblName/OBJECTTYPE" from configTable for xml path ('') which outputs: <TblName>PD_CODE_PRODUCTS <STRUCTURE>PD_CODE_PRODUCTS</...
Cube Processing SQL Server Developer Edition
Is this possible? It seems when I try and do this manually or via an SSIS task it does not work whereas it will be fines with other editions. (I'm using my local developer edition as a test environment) ...
Joining temp tables with different structure
I have 2 temp tables. One has data column wise. OrderCode OrderType ABC 1000 One has data row wise FieldName Value ORDER_CODE ABC ORDER_TYPE 2000 I need to write a query that essentially joins both of them and updates the value if it exists in table 2. Recordset result needs to be OrderCode OrderType ABC 20...
MSSQL - Split a field into 3 fields
I have a resultset consisting of 1 column and in this case 2 rows the single column [ProductDescription] is a varchar field that hold 3 pieces of information (I didn't design it) i need to get those three pieces of information out into 3 additional fields using a query before /------------------------------\ |ProductDescription ...
retrieving max records from sqlserver
I have been toiling with this for a while now. so decided to ask and get some help. I have two tables as such: tblTrans: (DocNumber field is always unique) DocNumber TransDate userId 66-FF-GHIP 03-05-08 someUser 55-RT-JHTP 03-09-09 someOtherUser 77-AF-KPWT 05-08-09 userId1 09-IO-TEAG 0...
Should I create several small application databases or one large one?
We are migrating several old mainframe applications for a customer to newer ASP.NET + SQL Server 2005 applications. Each application is generally thought of as standalone with no shared data between them, and do things like manage specialized inventory needs or vacation policy. The data is small and shouldn't outgrow a single SQL Serve...
How best to filter entities exposed by ado.net data services on top of entity framework?
I want to expose a simple set of blog posts, tags and categories through an API provided by ADO.NET data services. It looks easy in the demos: create your entity data model using the entity framework designer, add in the data service, point it to the entities, done. So far so good. But some posts, tags and categories are unpublished (p...
Cannot create index on view with User Defined Function in SQL Server
In SQL Server 2005, I'm trying to use a User Defined Function in a indexed view that will be used in a full-text index. I have been able to get the UDF to work with a stored procedure and the view in question. But, when I try to create an index on the view I get the following error... Cannot create index on view "DevDatabase.dbo.View_Pe...
SQL Constraints Question
In Sql Server 2005, I have a table with two integer columns, call them Id1 and Id2. I need them to be unique with in the table (easy enough with a unique index that spans both columns). I also need them to be unique in the table if the values are transposed between the two columns. For example, SELECT * FROM MyTable returns Id1 Id2 ...
How to Revoke SELECT Permission for system_views To public
I have the following T-SQL to display all the permissions granted to principals on my SQL server 2005: select dp.NAME AS principal_name, --1 dp.type_desc AS principal_type_desc, --2 o.NAME AS object_name, --3 p.permission_name, --4 p.state_desc AS permission_state_desc --5 from sys.database_permissions p left OUTER JOIN ...
Ideas for Combining Thousand Databases into One Database
We have a SQL server that has a database for each client, and we have hundreds of clients. So imagine the following: database001, database002, database003, ..., database999. We want to combine all of these databases into one database. Our thoughts are to add a siteId column, 001, 002, 003, ..., 999. We are exploring options to make thi...
linq to sql Insert not working on deployed server(IIS)
title speaks for itself, db.ExecuteCommand("INSERT INTO tCSVFile(fileName, fileContent, mimetype, timeCreated) VALUES({0}, {1}, {2}, {3})", filename, EncodeTo64(CreateCSVFile(header, rows)), "text/csv", DateTime.Now ); this works fine from the virtual server but on iis inserting causes nothing to happen. Also tried this.. tCSVFile...
Can't use IF for create or alter view depending on view's existense.
I'm trying to achieve what the code below suggests, but I'm getting the error Incorrect syntax near the keyword 'view' on both the create and alter lines. IF Object_ID('TestView') IS NULL BEGIN create view TestView as . . . END ELSE BEGIN ALTER view TestView as . . . END ...
SQL Server - Performance/Size Drawbacks of Null Columns
I'm working on a table design that could involve many NULL values in about 10 fields maybe 75% of the time the fields would be unused. I just generated some fake data (a million records) and could not sense any impact on SQL Server 2005. Size difference was in the KB. Performance - no measurable difference after adding an index to the 3...
Preparing to move to a single database
We have an application that has 1000+ databases and 600+ sprocs. Each database represents a different client. Problem: We need to move this to a single database while creating as little effect on the ui as possible, meaning dont change all the sproc signatures at 1 time. The connection string currently sets the database attribute, a ...
Linq to Sql - DateTime Format - YYYY-MMM (2009-Mar)
I want to write a Linq to Sql query that does a count and groups by a Username and DateTime. I want the DateTime to be formatted like following "YYYY-MMM" (i.e. 2009-Mar). I thought this would work but Linq to Sql can't parse the ToString "format" argument. dc.MyTable .GroupBy(r => new { ...