sql-server

Stored Procedure return multiple result sets

I need a SP to return multiple sets of results. The second set of results would be based on a column of the first set of results. So: declare @myTable1 table(field0 int,field1 varchar(255)) insert into @myTable1 select top 1 field0, field1 from table1 declare @myTable2 table(field0 int,field3 varchar(255)) insert into @myTable2 selec...

Why is @@Identity returning null?

I have a .NET 2010 app hitting a SQL2000 db. The code is pretty basic. When I insert a record, the record is inserted, but the id is not returned. The id column is an int and it is an Idetity. Here is the stored proc... ALTER PROCEDURE Insert_Vendor @CorpID as varchar(255), @TaxpayerID as varchar(255) AS Insert into dbo.Vendor ( vdr...

Sum() function error while inner joining 2 tables

Hi, I have problem with inner joining 2 tables. Table Lookup has only creative name and perf table has the creative name as well as the values to it. i need need to get the values into lookup table (where both the table has common creative name). Also, creative names have duplicates, so i need to sum them up and get the value. Below is...

SQL Server, can't insert null into primary key field?

I'm about ready to rip my hair out on this one. I'm fairly new to MS SQL, and haven't seen a similar post anywhere. When I try to do a statement like this: INSERT INTO qcRawMatTestCharacteristic VALUES(NULL, 1,1,1,1,1,1,1,'','','', GETDATE(), 1) I get the following: Cannot insert the value NULL into column 'iRawMatTestCharacte...

Convert varchar to date.

I (unfortunately) have some dates that were saved into varchar columns. These columns contain dates in the following format: mmddyy For example: 010110 I need to import these values into a table which is set to datetime and formats dates like this: 2010-09-17 00:00:00.000 How can I convert the string above into a dateti...

after joining two tables the result does not match with the original table

I have problem with inner joining 2 tables: LOOKUP and PERF. LOOKUP ...has only creative name and "perf." table has the creative name as well as the values to it. I need to get the values into LOOKUP (where both the table has common creative name). FYI: UCID is nothing but creative name Also, creative names have duplicates in both the...

Database Dependent Caching w/ Multiple Databases/SqlServers

I'm working in an environment where a single web application (running in a web-farm) dynamically connects to different SQL Server databases (possibly on different servers) based on user selection (this part is non-negotiable, unless you can gift me $1,000,000 and/or some kind of time altering device). I'd like to cache some of the commo...

Query to find all FK constraints and their delete rules (SQL Server)

In SQL Server 2005, can I issue an SQL query to list all FK constraints on tables within the DB, and show the delete rule? (ie nothing, cascade, set null, or set default) The output I'm looking for is something akin to: FK_NAME ON_DELETE ================================== FK_LINEITEM_STATEMENT CASCADE FK_ACCOUNTREP_...

Shrink data base SQL Server 2008

HI I have made a maintenance package in that have used shrink database task for specific database, it ran successfully, found slight increase in previous db size. Initial size(129 gb) after running the package(130gb). I am expecting after shrinkning it should shrink? what might be happen? am sure package scheduled to run and check the...

C# abstract base class for common columns in LINQ

This is what I have so far using System; using System.Collections.Generic; using System.Data.Linq; using System.Data.Linq.Mapping; using System.Linq; using System.Text; namespace Firelight.Business { public interface IBaseEntity<K> { K Id { get; } } /// <summary> /// Base business database connection object...

time format in SQL Server

Does anyone know how can I format a select statement datetime value to only display time in SQL Server? example: Table cuatomer id name datetime 1 Alvin 2010-10-15 15:12:54:00 2 Ken 2010-10-08 09:23:56:00 When I select the table I like the result will display as below id name time 1 Alvin 3:12PM 2 Ken ...

questions on copying SQL Server database

Subquestioning [2] While copying Resource.mdf [1], I noticed that: 1) It is possible to copy Resource.mdf without stopping SQL Server instance (I attached one having copied from running instance and it works after attaching) . 1a) Should I understand that it as general possibility for all read-only databases or is it only in some v...

IIS 7 PHP Unable to load dynamic library

I installed PHP and PHP Manager on my IIS machine through the web installer thing. I can run a web page with php in it fine, but it wont load libraries correctly. I want to connect to an MS SQL database, so i got the dll and put it in the ext folder but it wont load: PHP Warning: PHP Startup: Unable to load dynamic library 'C:\PHP\ext...

import data using ssis?

trying to set up an import form live database to our development database. I trying to do this using SSIS, but the import is failing because of constraints. E.g. I have the following tables: * Customers * Products * OrderLines * Orders Importing Customers and Products is fine, because they do not depend on anything. But when importi...

Does Transact-SQL have a similar function to MS Logparser Quantize?

If you are familer with Microsoft Logparser you probably recognize the Quantize function which will truncate a value to the nearest multiple of another value. It is quite handy for grouping date-time fields into increments. Date-Time Count 1/1/2010 00:00 100 1/1/2010 00:15 134 1/1/2010 00:30 56 .......

TSQL Computed column limitations

CREATE TABLE [dbo].[MembershipModule]( [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL, [ParentId] [uniqueidentifier] NULL, [TargetId] [int] NULL, [WebContentId] [uniqueidentifier] NULL, [Name] [varchar](35) NOT NULL, [NameUpper] AS (isnull(upper([Name]),'')) PERSISTED NOT NULL, [UriPrefix] [varchar](max) NULL, [UriText] [varchar](...

How can I join 2 table with 2 join condition?

Does anyone know how can I join 2 table with 2 join condition? Exmaple: table 1 date name 2010-01-01 Ken 2010-01-01 Alvin 2010-01-03 Alvin 2010-01-04 Ken 2010-01-07 Amy table 2 date name count 2010-01-01 Ken 5 2010-01-01 Alvin 4 2010-01-04 Ken 1 2010-01-03 Alvin 0...

connecting to sql server express remotely

i have a beginners question, and i apologize if it is stupid. i am a beginner at sql server. i can do sql pretty well, but i dont know much about connecting. i have: Microsoft SQL Server Management Studio and SQL EXPRESS what is the process of allowing remote connections to it? i would like to leave my laptop online at home with t...

why is the engagement of OLAP practically neglected?

With astonishment observe that: 1) that no answer to question Transactional And Reporting Databases - How? mentions SSAS databases from MS BI (Business Intelligence) platform: SSAS (SQL Server Analysis Services) databases (OLAP, datawarehouses, datamarts) + DM (DataMining). SSRS - SQL Server Reporting Services SSIS - SS Integration S ...

Using the result of an Stored procedure in a Select statement

i have an stored procedure wich return a Dataset(Table) how i can use the result of this stored procedure in a select statement? i need something like this Select T1.* from Dummy T0 INNER JOIN (EXEC [dbo].[SPGetResults] '900',300,'USD') T1 ON T1.aKey=T0.aKey i ' am using sql server 2005 ...