tsql

How do I select from a stored procedure in Sybase?

My DBA has constructed me a stored procedure in a Sybase database, for which I don't have the definition. If I run it, it returns a resultset with a set of columns and values. I would like to SELECT further to reduce the rows in the result set. Is this possible? From this question it seems like I could insert the results into a temporar...

SQL Syntax: Select Results for each date between a range of dates

Hi, I have the following SQL (SQL Server 2005) that I use to calculate the total times for each column: DECLARE @Param1 DATETIME DECLARE @Param2 DATETIME DECLARE @Param3 DATETIME SET @Param1 = '2009-01-01' SET @Param2 = '2009-09-09' SELECT SUM(IdleSec) AS TotalIdleSec, SUM(ProductionSec) AS TotalProductionSec, SUM(Upline...

O/RM Choice (not reliant on linq)

Hey Everyone, I like using SQL, and I think I am pretty good with it. And I tried to use Linq (with c# and Linq2Sql, looks like it feels more SQL-like with VB.Net), I feel it is very frustrating, you cannot just run your query without compiling and executing the code. I am not so great with IQueryable, IEnumarable, IList etc. And I hav...

T-SQL ISNULL() Optimization

I have this clause on a join in a stored procedure I inherited: WHERE a.is_active = 1 AND b.due_date <= ISNULL(@due_date_limit, b.due_date) How would I re-write this to remove the ISNULL as it's causing severe performance problems? ...

SQL - Select statment using LastIndex

I have a resultset which has ProductURL stored : accessoires/abc/285/P187654 accessoires/abc/285/D18765432 accessoires/abc/285/A1876543 I need to get the final part of the URL i.e. anything that is after the final '/' that appears in the URL. I have a function which gives me the LASTINDEX : SELECT [LAST_INDEX] (ProductURL,'/') But...

How to create Linked Server with non-default port.

I want to create a Linked Server in MS SQL Server 2000 to a MS SQL 2005 Server which runs on port x (not the default port 1433). But it doesn't work, as I can't specify the port anywhere!? Using sqlcmd (specifying port x), I can connect to the server without problems - but I can't set it up as a Linked Server. How can this be done? ...

Inserting German date format in SQL using VBA

I'm pretty frustrated here, been trying this for days... All I want is for German users to be able to enter dates that should be stored in SQL Server. The dates are in the following format "dd.mm.yyyy". I always get error messages like this one: Syntax error in date in query expression '#23.01.2004 00:07:00#'. - using Access for now. I'...

Copy Table from one to another server

Can any body let me know how can we copy a table with the data in it exactly to another server. I would really appreciate your help. ...

tsql script to find tables not being used by stored procedures, views, functions, etc?

Is there a t-sql script to find out tables that aren't being used in sql server by stored procudures, views, functions, etc. I have a database that has 100s of tables, if not more and before I go dropping tables, I wanted to know if there was a script that could go through each object in the database and tell me if any tables are in use...

Use each row in one table by joining it to another table

I'd like a query that at its simplest will join 2 tables together with no other express relationship other than each row in what I will call the "pool" table will match to precisely 1 row in my other table. I don't care which row, I just want every row in my primary table to get a single row from the "pool" table and know that each row f...

Maximum Count Range Intersection (in T-SQL)

Let's say I have a table with a bunch of dates, e.g.: declare @tbl table { idx int primary key, startdate datetime, enddate datetime } And I want to find the largest set of rows where startdate and enddate intersect (in the real world, the start date and end date represents start and end times for events, and I need to fin...

Linq equivalent of TSQL query

What would be the Linq equivalent of the following TSQL query: SELECT c.[CustomerId] ,c.[Name] , (SELECT COUNT(*) FROM Incidents WHERE CustomerId = c.CustomerId) AS IncidentsCount , (SELECT COUNT(*) FROM Opportunities WHERE CustomerId = c.CustomerId) AS OpportunitiesCount , (SELECT COUNT(*) FROM Visits WHERE CustomerId = c.Custo...

5 joins? only result from one of them is needed, but order is important?

I am doing left outer join to my table with around 5 different tables and I need result from only one of these 5 tables. Now I know I could use Coalesce to get what I want. However, I was wondering if there is a way to tell SQL to not keep on joining as soon as one left join hits. However, the left outer joins need to occur in the same...

There must be a way to delete data in SQL Server w/o overloading the log

I need to delete a bunch of data, and don't have the disk space for the log to continue growing. Upon looking into the matter further, it looks like there isn't any way around this, but I thought I'd ask for sure; it's difficult for me to believe that something so simple is impossible. I tried looping, deleting in chunks, and calling s...

Optimizing SQL 2008 full text query (CONTAINSTABLE)

I've got the following query that uses a full-text index to search for the TOP 5 products (from the RawProducts table) matching the query, in a given Shop (populated by the @ShopId variable). At the moment I'm calling this procedure over and over again for every ShopId (there are 27 Shops) - which is a bit slow. My question is - could ...

Reordering data in a table

If I have this data in a table in MSSQL which represents a horizontal row of objects and their coordinates: row id value 35395 776130 0 35395 776131 20 35395 776132 40 35395 776133 60 35395 776134 80 35395 776135 100 35395 776136 120 35395 776137 140 35395 776138 160 35395 776139 180 35395 776140 200 An...

SQL Server 2008 - limiting query by FK

I have the following query, which performs a full-text search (CONTAINSTABLE) on the Products table, and returns 1 record. Each Product is referenced by the ShopProducts table (each ShopProduct represents a product in a shop and is has a foreign key to Products.ProductId. Each row in the ShopProducts table has a ShopId column. My quest...

SQL Compact (CE) problem with creating foreign key

I'm trying to create a foreign key in a SQL Compact database but I keep getting the error message "A foreign key value cannot be inserted because a corresponding primary key value does not exist." TableA is referencing TableB already and I'm trying to create a reference from TableC using the same primary key in TableB. Since I already h...

Data from 4 different database

I need to get data from 3 different databases on one event command. Can anybody tell me any efficient way besides I am querying to all three different database servers in a row:- Server 1 : Select * from .... Server 2 : Select * from..... and so on... Thanks very much ...

Understanding T-SQL stdev, stdevp, var, and varp

I'm having a difficult time understand what these statistics functions do and how they work. I'm having an even more difficult time understanding how stdev works vs stdevp and the var equivelant. Can someone please break these down into dumb for me? ...