sql-server

SQL Server: Retrieve the duplicate value in a column

Hi all, How could I filter out the duplicate value in a column with SQL syntax? Thanks. ...

How to find the port for MS SQL Server 2008?

I am running MS SQL Server 2008 on my local machine. I know that the default port is 1433 but some how it is not listening at this port. The SQL is an Express edition. I have already tried the log, SQL Server Management Studio, registry, and extended stored procedure for finding the port. But, I could not find it. Please help me. Thanks...

getting error on while inserting a single quotation values

Using SQL Server 2000 and VB6 Table1 EmpName1 Raja Rav'i Ramu'i Rajes'ih Table2 EmpName2 .... When I inserting table2.empname2 from table1.empname1, It inserted only raja, remaining rows was not inserted it showing error in '(single quotation) "Showing error as incorrect syntax near rav" vb6 code. INSERT INTO table2 (EmpNam...

How to display ndf files using SMO??

Hi I am using SMO to populate the list of database files log files. i am using filegroups but it displays only mdf and ldf files. I have ndf files also it is not dispalying ndf files?? what i have to do any suggestions plz??? string dbname = string.Empty, DatabaseInfo = string.Empty; Server srv = new Server(insta...

SQL Server 2000 DTS - Cannot resolve collation conflict for equal to operation.

I have a SQL Server 2000 DTS package. One of the steps of this package has the following SQL: SELECT * FROM [Crocus_Limited$OrderRequestDetail] WHERE (rep_updated > GETDATE() -2) AND NOT EXISTS (SELECT OrderID FROM NavisionUpgrade.navision4.dbo.[WEBOrderDetails] rd WHERE rd.OrderID = [Crocus_Limited$OrderRequestDetail].OrderID ...

SQL Server: should I use an "Agent Job" or a "Maintenance Plan" to delete old data?

I'm looking for a way to periodically (e.g. weekly) run some SQL statements in a database to delete old data. As far as I can see, there are (at least) two ways to do this: using a "Maintenance Plan" and a "Execute T-SQL Statement Task" using an "SQL Server Agent Job" and specify the statements in a "Step" of that job My question is:...

How do I convert this T-SQL query to use an explicit join syntax?

I have the following Select statement, but want to change it to use iner joins as I believe they are more efficient, but not too sure where to start. DECLARE @myNameID int DECLARE @myAddressID int DECLARE @myFirstName nvarchar(256) SET @myNameID = 1 SET @myAddressID =1 SET @myFirstName='Nathan' SELECT @myNameID = myNameID FROM ...

SQL Server: Optional variable in a stored procedure

I would like to know if there is anyway I can set one of my stored procedure parameter as optional. IF @thing_id <> '' BEGIN SET @sFiltre = @sFiltre + ' AND OPERES.OPE_THING = ' + CONVERT(VARCHAR,@thing_id) END ...

Where to find the list of error code and its messages for C#, Sql server ?

How can I find a list of error codes for sql server, socket exceptions, web exceptions or any exception. For sql server I am not able to find all the error codes in sysmessages like for instance errorcode 53, 17 so where do we find such codes? For example System.Net.Sockets.SocketError sockererror is an enum which contains most of the ...

creating a table if it doesn't exist

Given the following: if object_id('MyTable') is null create table MyTable( myColumn int ) Is it not possible that two separate callers could both evaluate object_id('MyTable') as null and so both attempt to create the table. Obviously one of the two callers in that scenario would fail, but ideally no caller should fail, rather one sh...

A single sql query which can handle both null or valued date range in sql server

Using SQL Server 2008. I have a stored proc which has start and end date as input parameters for date range. Looking for a single sql query which has a between start and end date in the where clause which can handle both cases where the dates are either both null or both have values. I don't want to use an IF statement. ...

How can I select the first day of a month in SQL ?

I just need to select the first day of the month of a given datetime variable. I know it's quite easy to do using this kind of code : select CAST(CAST(YEAR(@mydate) AS VARCHAR(4)) + '/' + CAST(MONTH(@mydate) AS VARCHAR(2)) + '/01' AS DATETIME) but this is not very elegant, and probably not very fast either. Is there a 'better way t...

delete row from selected gridview and database

i am trying to delete a row from the gridview and database... It should be deleted if a delte linkbutton is clicked in the gridview.. I am gettin the row index as follows: protected void LinkButton1_Click(object sender, EventArgs e) { LinkButton btn = (LinkButton)sender; GridViewRow row = (GridViewRow)btn.NamingCon...

the world of MDX queries

I am looking for SSAS cube and MDX (Multidimensional Expressions) sites like our StackOverflow site. Any other resources for SSAS cubes would be welcome as well. ...

Is it possible to store database connection string information in Active Directory?

Let's say you have many applications in your environment all of which store their connection strings to SQL Server in a web config. Is it possible to have the application retrieve those connection strings from Active Directory? We're moving some database servers around and would like a way to have them read from a central location. Acti...

SQL server Agent not starting

The SQL server Agent not starting ... its giving error message The SQLSERVERAGENT on server SAFORTGJ is started and stopped... Any idea why this is not starting ...

How to call a sql script from another in SQL Server 2005

Hello to all, I am in need of testing several different processes for the application we're builduing. Each process requires a particular table in our database to have data and all of these tables have foreign key constraints from other tables as well. I've written sql scripts that populate the table I'm interested in as well as its de...

How to get procedure text before ALTER from DDL trigger

I am creating a trigger to track how procedure text has been ALTERed. Inside a database DDL trigger, it's possible to access current procedure Text through /EVENT_INSTANCE/TSQLCommand. Even after investigating EVENTDATA(), it did not contain values for previous definition of procedure before ALTER. Is there a way to retrieve previous ...

SQL Server query - Selecting COUNT(*) with DISTINCT

In SQL Server 2005 I have a table cm_production that lists all the code that's been put into production. The table has a ticket_number, program_type, and program_name and push_number along with some other columns. GOAL: Count all the DISTINCT program names by program type and push number What I have so far is: SELECT DISTINCT COUNT(*...

SQL SERVER Procedure Inconsistent Performance

I am working on a SQL Job which involves 5 procs, a few while loops and a lot of Inserts and Updates. This job processes around 75000 records. Now, the job works fine for 10000/20000 records with speed of around 500/min. After around 20000 records, execution just dies. It loads around 3000 records every 30 mins and stays at same speed...