sql-server

Too many lookup tables

Hello, What are the adverse effects of having too many lookup tables in the database? I have to incorportate too many Enumerations, based on the applications. What would experts advice? ...

Two triggers on one table sql server

I have two triggers on one of the tables in my database. One is for insert trigger and the other is an after update trigger. The insert trigger will update the table with values. My question is this; Is it possible that that the update trigger is firing at the same time that the insert is doing its job? ...

SQL Server syntax question

USE AdventureWorks; GO IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL DROP TABLE dbo.AWBuildVersion; GO Whats the N symbolize in N'dbo.AWBuildVersion' & N'U' ? ...

Not getting back enough columns

My query is as follows: select [Transaction].id, [User].name, EventType.EnglishDescription from [Transaction] join [User] on [Transaction].userid = [User].id join EventType on EventType.id = [Transaction].EventType I'm expecting to get back Transaction.id, User.name and EventType.EnglishDescription in the colum...

Visual Studio 2010 built-in fake/test data generator for SQL Server

I few months back I attended a Microsoft EnergizeIT seminar where they were showing some of the capabilities of their latest development products. One of the things they had shown that really caught my eye was a built in mock data generator tool to generate mass amounts of fake data from a few seed lists. I don't remember the name of the...

Basic LINQ to SQL Question: How to call stored procedure and retrieve single return value.

I'm new to LINQ and am having a problem getting proper results from a simple (working) stored procedure that takes no parameters and returns a single Integer. When calling this sproc with LINQ its returnvalue is always 0. When run using tableadapter or directly on the sql server it works, returning 6 digit values like 120123. Here is ...

Compare Every Record from Two Tables

Assuming I have two SQL tables consisting of a single column, i.e. Table 1 Table 2 a1 a2 b1 b2 c1 c2 Is there a succinct SQL command to compare each record in one table against each record in the other? (and return true if any record from table 1 matches any recor...

Elegant way to delete rows which are not referenced by other table

Hi, I have two tables (Tasks and Timeentries), which are connected by a foreign key (TimeEntries.TaskID references Tasks.ID) Now I'd like to delete all rows from Tasks which are not referenced by the TimeEntries table. I thought that this should work: DELETE FROM Tasks WHERE ID not IN (SELECT TaskID FROM TimeEntries) But it affects ...

Rails on windows connect to Microsoft SQL Server - "no such file to load -- odbc"

I am trying to connect a Rails app on a Windows machine to SQL Server using activerecord-sqlserver-adapter. I have set up a DSN that works great. When I try to run a migration (or any database operation), I am informed: "no such file to load -- odbc" My database.yaml file has this: development: adapter: sqlserver mode: odbc dsn...

Integration Service: Best Way to Compare Two Tables to Retreive Missing Keys For Use In Later Decisions

I am currently trying to develop a SSIS Package and am lost because I am a no0b. I have two tables, one is updated and one needs to be updated. I need to compare these two tables and find the primary keys that have been added to the first table and that are not present in the second table. I need these primary keys in the next set of qu...

Deploying an asp.net website on DomainHut.com hosting

I've called their tech support 3 times this morning and spent over $50 in long distance and have had no results. I was wondering if anybody here has deployed an ASP.NET website with DomainHut.com's hosting services. I've set up my database on their server correctly, all tables, stored procedures and aspnet schema are there just like on...

SQL Server: Import and Export CSV

I am working on SQL Server 2000. As you know, there is an application that's shows query results (Query Analyzer) The data is shown there, on a grid, and you can save that grid in a csv file. That's great. Now, I want to INSERT those csv values into a Table. Does SQL Server provide any method?? And I mean, automatic or semi-automatic me...

SQL Server Query Help

My problem is I am comparing two records from different tables and deleting those that match. I have found this bit of code : DELETE emails FROM emails INNER JOIN CustRecords ON CustRecords.Email = emails.email Which works well, but i would also like to have a return value of how many records have been deleted. Now if I do an ...

Visual Studio 2010 database edition schema compare where target is dbproj

I'm using visual studio 2010 database edition and running a schema compare against a SQL database instance to sync up new objects on the database which arent yet in my project (dbproj). My solution contains many projects some of which reference each other via database references. When I write updated from my schema compare to my target p...

How does SQL Server treat indexes on a table behind a view?

So I'm trying to understand how SQL Server makes use of indexes on tables behind views. Here's the scenario: Table A has a composite clustered index on fields 1 & 2 and a nonclustered index on fields 3 & 4. View A is written against Table A to filter out additional fields, but fields 1-4 are part of the view. So we write a query th...

Form a query to find duplicate zipcodes

Hello, I have a zipcode table. I want to look through this table and find every zipcode that is in it twice. The table does have a primary key set on it(zipcode_rid). How would I do this? Also, I am aware that there are official multi-state/city/county zipcodes. That is what I'm wishing to find. My table structure is like so: zipcod...

Is it possible to retrieve the line number from an OleDbException caused by calling ExecuteNonQuery()?

We are calling ExecuteNonQuery on all files in a certain folder for version scripting and if there is a syntax error an exception is raised. I have been scanning MSDN for a way to get the line number but haven't been able to find anything yet. There is a 'SqlException' class which does contain the line number but the shared base class 'D...

Problem select query for number 2?

I just creates this stored procedure in selecting palletnumber=datatype is int. SerialNumber is varchar. Then pallet numbers are 1 - 200..But if i enter 2 it shows 2 with other palletnumbers. I change @search to nchar and its ok but serialnumber is has error. Im new in stored procedure. ALTER PROCEDURE [dbo].[sp_SearchFresh] -- Add ...

Database field type for storing web pages

What database field type should I use to store web pages (html, pdf, text) files in the same field? nvarchar(max)? ...

View is not schema bound ?

Hi All, I have a select query to retrieve data from tables. It is working fine, but when i give some where condition to select some 3 values, it is not giving me the result. it says, "Query processor ran out of Internal resources". I thought through INDEX, it may work fine, then i created view with that select statement. But i couldn'...