sql-server-2005

Large table with no rows?

I have a Sql Server 2005 database that contains many tables which are taking up a large amount of space (combined over 10 GB), and these tables have no rows in them. I can see the space and row count with right-click, properties (Data space and row count items). Any ideas? ...

How to store timezone in SQL Server 2005

I am building a web application where users can enter events, including event title start date/time description The users would like to enter the start date/time including a timezone that corresponds with the location of the event. The events are worldwide so the timezone can change from event to event. In the SQL Server backend dat...

Sql Server 2005 AutoCompletion

Does anyone know whether SQLServer 2005 provide auto completion when typing stuff in?? I ve looked in the options many times but cannot find this option which is suprising as a lot of other sql editors do support this ...

Dynamic Pivot (in SQL Server 2005)

I'm writing a stored procedure for Microsoft SQL 2005 and I want to create a dynamic SQL Pivot: SELECT Book.ISBN, Book.Name StockMutation.StockLocation FROM Book INNER JOIN StockMutation AS sm ON Book.bookid = sm.bookid PIVOT ( COUNT(sm.NumberOfBooks) FOR sm.StockLocation IN (...) ) Preferable I want to r...

Editing table/adding records manually to sql server 2005

Is there any option in sql server 2005 management studio to change columns in a table by hand and by the sql commands alter table or insert into. If yes, then could someone please show how or link to some instructions? ...

sql query problem, manipulate data.

I have stored proc that will tell us if there is any new checks to be printed. Now I have to manipulate the data in database table in order to get at least one check to be printed. I didn't write this query. I tried but I didn't get at least one check to be printed. Can anyone help me. HERE IS THE STORED PROC CREATE PROCEDURE [proc_1250...

How to make a Winforms application with remote sql

I have written a simple program(Winforms.NET), that selects a random word from a dictionary (DB table) everyday. When the DB is embedded its fine, but if I want to make the program take the data from a remote server. I suppose I have to put the ip address of SQL Server host into my connection string, but if host allows remote connectio...

Database Server 100%, for no reason

Hi, We have a Windows Server 2003 (x64) running as a Database server. The Database is equipped with 32GB of RAM Usually the Database memory usage (Task Manager) is between 5-10%. However sometimes the Database shoots up suddenly to 100% and remains there, randomly and without any changes to code or executions. All sort of research, pa...

Database Design for Filtering Database

Hi All, I'm new to complex database design. I'm currently into a project where the user should be able to retrieve Instructions based on a combination of 18 columns. So my parameter Table has the following columns Job State Manager ProcessCode ProcessType (rest of the columns truncated). InstructionID (FK of Instruction Table) When...

Is it possible to have secondary server available read-only in a log shipping scenario?

I am looking into using log shipping in a SQL Server 2005 environment. The idea was to set up frequent log shipping to a secondary server. The intent: Use the secondary server to serve report queries, thereby offloading the primary db server. I came across this on a sqlservercentral forum thread: When you create the log shipping you ...

Use a LIKE statment on SQL Server XML Datatype

If you have a varchar field you can easily do SELECT * FROM TABLE WHERE COLUMNA LIKE '%Test%' to see if that column contains a certain string. How do you do that for XML Type? I have the following which returns only rows that has a 'Text' node but I need to search within that node select * from WebPageContent where data.exist('/PageCo...

Workflow Foundation Separate Schema

I'm working with the persistence and tracking services in Workflow Foundation and I thought it would be a good idea to use a separate schema in SQL Server just for workflow. Unfortunately, it appears they hard-coded the default dbo schema name into their code. One example is the SqlTrackingService class. Due to the way they wrote the t...

SQL: Best practice to store various fields in one table

Hi, I want to design a table for items. There are many types of items, all share several fields. Each type of item has it's own fields. I want to store the uncommon fields in a separate table. I thought of something like : ----Items +Item_id +Item_Type_Id +Item_Serial ... ----Item_types +Item_Type_Id +Item_Name ... ----Item_Fields +I...

SQL 2005 Pivot Query

Hello all. I am trying to get a grip on the pivot query syntax. I have about 20 fields worth of aggregate data all associated with 1 field, which is the category for those 20 fields (by fields I mean columns). It looks like this Category1 column1 column2 column3 Category2 column1 column2 column3 and so on.....

find duplicate insert into another table

I have a code that that loads my data into sql table. I want know that is there any way to load data into some other table if there is any duplicate value. if yes can you help me. My query is: "insert into t_DATA_DneFrc (RTN, AccountNbr, FirstName, MiddleName, LastName, Amount) values ('" & rcdDNE("RTN") & "', '" & rcdDNE("AccountNb...

SQL Query works quickly with 19 items in "IN" clause - much slower with 20. Why?

I have a query that includes this: ... AND Record.RecordID IN (1,2,3,10,11,12,13,16,17,18,26,27,28,557,31,32,33,36,37,93) AND ... The problem seems to be that if there are 20 items or more in that list, the query takes over 25 seconds to execute. If there are less than 20, it executes immediately. Any ideas on how to optimize? ...

SQL Insert trigger to update INSERTED table values

Hello. I want to create an Insert trigger that updates values on all the inserted rows if they're null, the new values should be taken from a different table, according to another column in the inserted table. I tried: UPDATE INSERTED SET TheColumnToBeUpdated = ( SELECT TheValueCol FROM AnotherTable.ValueCol WHERE Another...

Create a global static variable in SQL Server?

Is there a way to create a global variable in SQL Server so it's saved even the server is shut down, so I can use it in functions? Example from what I need: DECLARE @@DefaultValue bit This variable should never be deleted unless I explicityl do so. ...

Replicating database objects through c# code

Hi, I need to create database objects (table, index, keys, views, functions, etc.) through the c#.net code. The user specifies the name of the database object to be replicated and the new name given to the copied object. So suppose user selects existing table TableA and the new name TableB, my code should create TableB having the same ...

SQL Count, counting Halfs!

I have a rather strange problem: when certain sales are made (completed) a record is inserted with the event and ID of the sales person, currently this table is queried and the 'Count' is used (along with usual date boundaries) to calculate number of 'closed' sales. This is currently working fine. The problem now is some sales are 'sha...