sql-server

Stored procedures and functions

What are the differences between stored procedures and functions. Whenever there are more input, output parameters i go for stored procedure. If it is only one i will go for functions. Besides that, is there any performance issue if i use more stored procedures? I am worried as i have close to 50 stored procedures in my project. How ...

Is there an exact equivalent for the .NET Double type in SQL Server?

Possible Duplicate: What represents a double in sql server? Is there an exact equivalent for the .NET Double type in SQL Server? Failing that, is there one which gives a good approximation? EDIT: It looks like this link gives the most definitive answer: Mapping CLR Parameter Data ...

Getting measures from parent table using child table dimensions

I have a cube built in SSAS2008 that is built upon 2 tables - Accounts and Payments. Both tables have dimensions and measures, and it appears to be working relatively well - I can get payments for accounts broken down into dimensions for either payments or accounts, and account measures broken down into account dimensions. What I can't...

pulling a column total into another column as part of the ref

Hi there again, have been puzzling this query and haven't quite got it. Below is where I am up to and below that is the result. It returns the line total and not the column total, any help appreciated. SELECT TOP (100) PERCENT 'SA' AS Doc_Type1, 'A' + SUBSTRING('000000', 1, 6 - LEN(CAST(dbo.companies.companyId AS VARCHAR(10)))) + CA...

Check if SQL Server 2005 XML field is empty

Hello, I just did this: Delete FROM MyTable WHERE ScopeValue = "" Delete FROM G_Scope WHERE ScopeValue is '' Delete FROM G_Scope WHERE ScopeValue = empty Delete FROM G_Scope WHERE ScopeValue is empty I want to delete all rows with xml field (not nullable) where ScopeValue column has empty entries means zero chars. Anyone knows? ...

Calling a SQL Agent Job from another Job on a remote server?

Is there a way to trigger a job on a from another job on a remote server without using Linked Servers? The reasoning is that the job being triggered executes an SSIS package on 2008. The calling job resides on a 2005 server, so cannot execute the job directly. The servers are not linked, and I was hoping there was a way to call one f...

isnull vs is null

I have noticed a number of queries at work and on SO are using limitations in the form: isnull(name,'') <> '' Is there a particular reason why people do that and not the more terse name is not null Is it a legacy or a performance issue? ...

Adding table lock manually to specified table in SQL Server

I want to INSERT into one tables but prevent INSERTING to another one. It is possible to LOCK for example table a for INSERTING, INSERT to table b and then UNLOCK table a? TABLOCK can lock only the table I am INSERTING in. Thanks Martin Pilch ...

SQL - Where Clause by Another Select Statement?

I have the following query: select r.people_code_id [People Code ID], r.resident_commuter [Campus6], c1.udormcom [AG], aR.RESIDENT_COMMUTER [AG Bridge], ar.ACADEMIC_SESSION, ar.ACADEMIC_TERM, ar.academic_year, ar.revision_date from RESIDENCY r left join AG_Common..CONTACT1 c1 on r.PEOPLE_CODE_ID=c1.key4 left join AG_Common..CONTACT2 c2 ...

How can I efficiently compute the MAX of one column, ordered by another column?

I have a table schema similar to the following (simplified): CREATE TABLE Transactions ( TransactionID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED, CustomerID int NOT NULL, -- Foreign key, not shown TransactionDate datetime NOT NULL, ... ) CREATE INDEX IX_Transactions_Customer_Date ON Transactions (CustomerID, Tr...

blank spaces in sql server data

HI ALL, I am using sql server express to store some data but it also store spaces with data. for example if a have a nchar(20) column in a table and i store "computer" (8 characters) to this column, then remaining character (20-8=12) is filled with blank spaces. Is there any way to over come this problem. Because when I shows this data ...

Roles of parentheses in SQL Server SELECT queries?

Hello, The following query returns no result and no error on SQL Server 2008 (tested on SP1), you can run it against any database, even master: WITH computed_table (id) AS ( SELECT id FROM this_table_does_not_exist ) (SELECT * FROM computed_table) UNION (SELECT * FROM another_table_that_does_not_exists) On SQL Server 2005, you ge...

How can I do this in SQL? I need to find possible permutations of a small table of data

Hi All, I have this small table of data. Dir LinkL LinkH East 19 27 East 27 29 East 29 31 West 46 49 West 49 51 West 51 61 These represent possible trips. How can I query this? For instance if you started at station 19, you could go from 19->27, 19->29, and 19->31. Three possible "trips" But starting from 2...

Querying index server catalog on network

I have a website that is running on windows 2000 and sql 2000. The application and the db resides on the same server. The site has a search feature that is a combination of dbsearch and pdf search. for the pdf search i created a linked server that points to index server to do the pdf search. Wrote a stored proc that will combine both th...

How do you get all ancestors of a node using SQL Server 2008 hierarchyid?

Given a table with a hierarchyid type column, how do you write a query to return all rows that are ancestors of a specific node? There is an IsDescendantOf() function, which is perfect for getting the children, but there's no corresponding IsAncestorOf() function to return ancestors. (and the absence of a GetAncestors() function seems l...

How to generate id for easily sorting purpose in SQL Server?

Hi everyone, I'm making an website and I have problem generate the parent/child tree like: Page 1 ---Sub page 1 ------Sub page 1 - 1 Page 2 Page 3 ---Sub page 3 ------Sub page 3 - 1 ------Sub page 3 - 2 If I use UID, it's impossible to write the "ORDER BY" t-sql to make the tree. I'm thinking of a function that can generate the ID (v...

How can I create a series of months to join sparse data to?

I think this is a pretty common issue, but I don't know what the process is called, so I'll describe it with an example. The concept is that I want to join a sparse dataset to a complete series, such as the days of the week, months of the year, or any ordered set (for example, for ranking). Empty positions in the sparse data will show as...

T-SQL 2005: Counting All Rows and Rows Meeting Criteria

Here's the scenario: I have a table with 3 columns: 'KeyColumn', 'SubKeyColumn' and 'BooleanColumn', where the first two are the primary keys of the table. For my query, I'd like to count the number of rows there are for any given value in 'KeyColumn', and I'd also like to know which ones have a value of true for 'BooleanColumn'. My i...

How to concatenate multiple rows?

I have the following query which returns the salary of all employees. This work perfectly but I need to collect extra data that I will aggregate into one cell (see Result Set 2). How can I aggregate data into a comma separated list? A little bit like what Sum does, but I need a string in return. SELECT Employee.Id, SUM(Pay) as Salary F...

Trying to insert data correctly

Hello, After receiving very good correction from fuzzy lollipop, I amended my code to create an insert statement for every row of data. This is the code that I entered: INSERT DEPARTMENTS (Department_Id,Department_Name,Manager_Id,Location_Id) VALUES ('D0001','Think Tank',NULL,'L0001') GO INSERT DEPARTMENTS (Department_Id,Department_N...