I found some hints, how to create a database using SMO The following: Server server = new Server("localhost"); Database database = new Database(server, "TESTDATABASE"); FileGroup fileGroup = new FileGroup(database, "PRIMARY"); DataFile dataFile = new DataFile(fileGroup, "TESTDTABASE_DATA", ...more >>

I am wondering if it is possible to output another field in a database from within a case statement, i.e.: CASE When Exam_Requirements = 'Y' then Exam_Comments The goal would be for each return of a yes in exam requirements the exam comments would extract? Thanks, George ...more >>

Need help in understanding a query which is as follows which has 2 join conditions Select C1,C2 From T1 INNER JOIN T2 ON T2.C1=T1.C1 INNER JOIN T3 ON T3.C1=T2.C1 ON T3.C2=T1.C2 ...more >>

i have nvarchar(japanese code page) data field . how can i display the japanese characters in query analyser.? they shows as ?? thx ...more >>

I have the exact same database on two different servers...but when I run the same query against both of them...they have different execution plans. One of the queries returns in 1 second...where the other one returns in 4 seconds. Is the normal? how can I get the slow server to run with t...more >>

I have 2 identical databases, db1 which is edited with updates and inserts and db2 which is read-only. Each table has an identifier column and a date-time-edited column. Can anyone provide the basis of an sql script which would edit a table in db2 to insert new rows from db1, and update ...more >>

I have a table with two varchar fields (a and b) like this: a, b ----- 1, 10 1, 20 2, some_text 2, some_more_text I want to sum up all the type answers where a='1' (so from the above it should be 10+20=30) Field b is varchar so I do: SELECT SUM(CONVERT(int, b)) WHERE a = '1' ***bu...more >>

I want to INSERT records into #TEST so that there is a record for every date within my date range. The date range is all dates between and equal to @StartDate and @EndDate. The inserted records will be a duplicate of the record determined by @CurrentDate, except for the TestDate field which is my...more >>

I'm trying to determine why a process is failing, but one thing I noticed right away is that it takes 40 seconds in SQL 2005 and takes 11 in SQL 2000 (both standard version), client is VB 6. I haven't looked through all the lines of code, but do know of at least one performance issue in 2005 ...more >>

I have a sql jobroute table that consists a variety of information which include the fields job number, operation number, and last code. I need to be able to locate the last operation for each job and update the last code field with a "Y". Here is what I have: Job Operation La...more >>

So, I have a query like this: SELECT DISTINCT PCM.iOwnerID, PCM.chProductNumber, count(PCM.iHierarchyID) AS Num FROM (SELECT * FROM dbo.vOnyx_Products WHERE (iHierarchyID IN (199))) PCM LEFT OUTER JOIN (SELECT * FROM dbo.vOnyx_Products ...more >>

have a problem with time series. This is my problem : Scenario 1 – I have the output below which is made up intervals with hourly repeating values StartDate EndDate DMask MW1 MW2 MW3 MW4 ...more >>

I need some help with formatting a float data type to be displayed a certain way. First allow me to list the fields involved, their data types, a sample value and the end result I desire. I'm trying to simulate an expression used in Access and would like to attempt the same sort of expression ...more >>

Hi all, I have the following text in a Varchar(200) column in a table and want to parse out just the first set of numbers: Example data Col1 Order the 90311 - Prov Order Notify User of Ack 8894 Order 30511 was assigned. 1 tsirry 2000-2-5 00:00: 2003-12-29 1:56:23.827 pwilliams NULL 18...more >>

Hi All I have a table in SQL Server 2000. I want to copy the data to my SQL Server 2005. Is there anyway I can do it. This table in 2000 SQL server has foriegn key constraints. I do not want to import the other tables. Just the data. Please let me know. Thanks ...more >>

Hello, I am doing some data corrections and tried to run this: UPDATE tablename SET Provider = 'Burkers' WHERE Provider = 'Burker's' Got this error message: Server: Msg 170, Level 15, State 1, Line 3 Line 3: Incorrect syntax near 's'. Server: Msg 105, Level 15, State 1, Line 3 Unclosed ...more >>

Is there an event I can watch that SQL Server Fires when it goes off line? I need to watch for an event to monitor SQL Server status, and when it is not online, switch to a backup server. I am using .NET (C# or VB) to do this. Can this be done? John Wright ...more >>

Hi All, Please help. Example : I have a table "ABC" --- -- Only one row for the ID ID = 5, ColX, ColY ID = 6 ColX, ColY I have another table 'XYZ --- Many row for the same ID ID = 6 ColX = 'E' ID = 6 ColY = 'E' ID = 6 ColZ = ' ' ID = 5 ColX = '01' ID = 5 ColY = '0...more >>

HI All, I'm trying to use the following script as a job which should be running each first day of the month, at midnight, to repartition the database. This script suppose to disconnect all database users currently connected to the database, put the database into single user mode, and after do...more >>

Is there any difference in these 2 ways of coding? Does it matter if the qualifier is in the WHERE clause a opposed to being in an "AND" statement in the JOIN? Sample A SELECT A.Col1, B.Col2 FROM TableA A INNER JOIN TableB B ON A.Col1 = B.Col1 AND B.Col5 =...more >>

Hello, In SQL Server, in the diagram, I can setup constraints between tables. I can tick the following boxes in the properties of the constraint: -check existing data on creation -enforce relationship for replication -enforce relationship for INSERTs and UPDATEs -cascade Update Related Field...more >>

Hi, I have two tables (county(countyID, countyName) and results(resultsID, countyID, resultDate, fileName, rptYearQuarter). Each county can have zero or more records in the results table. I need to be able to get the most recent fileName for each county for a specific rptYearQuarter. I...more >>

I am trying to create a trigger that rolls back updates to a specific columns in a table under certain conditions. The area I am having trouble with is only rolling back updates to these columns and allowing updates to other columns in the table. The table is very simple Company Contact ...more >>

I believe there is a bug in the SQL 2000 parser that causes the following SQL exception: System.Data.SqlClient.SqlException: Invalid operator for data type. Operator equals boolean AND, type equals numeric. Invalid operator for data type. Operator equals boolean AND, type equals numeric. ...more >>

Environment: Windows 2003 Enterprise Server (Production Server) SQL Server 2000 Standard Edition (with Service Pack 4 installed) Windows 2003 Enterprise Server (Test Server) SQL Server 2000 Enterprise (with Service Pack 4 installed) I'm getting three different ODBC error: ...more >>

I have a database structure that was built to be flexible and store extended properties on items without having to change the database when a new extended property added by a data import process. These extended props were not originally meant for direct querying and now we do want to start que...more >>

Sorry, I've been stuck in a SQL Server 2000 and vb6.asp world and a bit isolated. My app is web based. It's 99.9% Transact-SQL creating HTML output and .1% vb.asp. My VP.asp does two things: 1. Calls a stored proc and does a response.write. 2. On submit it captures form field values a...more >>

Using SS2000 SP4. I have a stored procedure that I created a week or so ago and it stopped working this morning. I use it in a report and the report stopped filtering data and I traced the problem back to the sp. This is the sp: CREATE PROCEDURE [dbo].[uspReportBillingDetail] @StartDate dat...more >>

Hi All, We have a Discount scheme that can be given for all possible combination of all customer, area and product hierarchy (attached below). for example : Promotion No.001 =>for sales on : CustomerGroup 'A', Area 'B', and ProductBrand 'C' will get discount 2 % Promotion No.002 =>for s...more >>

Hello I'm trying to use a cursor and some dynamic sql to updtate a table, but I'm having a error Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'periodo5'. the code.... --declare @jp_temp2 as int declare @sub_linhas as int declare @jp_colA as...more >>

Say, for example, I've got two stored procedures in my dabase c# project, can one of them call the other directly or do I need to use a SqlCommand? Example: I've got this stored proc: [Microsoft.SqlServer.Server.SqlProcedure] public static void UserInsert(Guid id, String name) { ...more >>

What are the "best practices" for this? I'm using SQL Server 2000. I basically want an identical database, but with a new name and new datafiles. Right now, I have copied the template db's mdf/ldf files to new ones, and issued the following statements: sp_attach_db @dbname =3D 'newdb', @...more >>

Hi, I want to change the identity values of an identity column of a table that already has rows in it, such that, it begins at zero and goes up in even numbers - that is, like 0, 2, 4, 6, 8, etc.... instead of the usual 1, 2, 3, 4, etc.... The current values of the identity s...more >>

Is it true that there's a performance degradation if the stored procedure name begins with SP? A colleague at work read that recently, but couldn't find the article. I just wanted to verify it. Thanks....more >>

Hello.. does anyone know how to deploy the ASP.Net applications using visual studio 2005 thanks sasi ...more >>

Where I work we write hundreds of procedures which run against different databases depending on the connection string passed in. To manage this, there is a user function which returns the string for the linked server, eg. '[x.x.x.x].somdb.dbo.' which can be prepended to tablenames in a string....more >>

I am try to raise a error to sql server from a clr procedure. I tried SqlContext.Pipe.ExecuteAndSend(new SqlCommand("raiserrro("foo", 16, 1))); try { p.ExecuteAndSend(cmd); } catch { } which casuse a message to be printed in query analizer but it doesn't seem to get caught by a begin try ...more >>

Maybe a stupid question: when I write a clr enabled stored procedure, I can of course call it the usual way using a SqlCommand("Exec MyProc"). Is there another way to call this clr stored procedure? Maybe by somehow referencing the database assembly? It would be nice to have parameter check...more >>

Hello, everyone. Do everyone know the link to free ebook of sqlserver 2000? Thanks in advance. ...more >>

I have a very large table (about 200,000 records), but there are only 2 fields in the table only one populated with data. I need to update the 2nd field with the first field's data. UPDATE Table1 SET field2 = field1 This is taking a really long time to run about 3.5 minutes. Is this normal...more >>

Hello, Can someone tell me, what are the various alternatives (free) for accessing SQL Server database from Mac OS X. Currently I am using SQL Server 8.x. Thanks in Advance Regards -Amit Gupta ...more >>

Hi All, Can i make Primary key and Composite ket seprately in one table or elaborating it can i make one field as primary key and three field as composite ket so that it is to be differenticate seprately....more >>

Hi guys, Is there any way in which we are able to keep parameter as optional and mandatory in stored procedure, if any body know please let me know and if possible please send me sample code....more >>

Is there a way to check when a record was last updated without using a date field? Cheers, Cain. ...more >>

Hi All How do I check if a database role exists already using TSQL script Below is my current script .... I need to check if they exist so I can bypass the step or Delete then and recreate them I can't get the role line 'If not exists...' to work Regards Steve USE [master] GO ...more >>

I am using MS SQL 2000 database I have a query which returns data which is used in a report in a VB.NET program. The query basically is as follows SELECT Moncode, PremName, Tech, InvoiceAmount, DateCompleted FROM tblServiceWork WHERE DateCompleted = @DateDone ORDER BY Moncode This w...more >>

Hello, I have this DECLAR @myBit bit SELECT @myBit = 1 --This means true Now I want to do the negative out of it like SELECT NOT (@myBit) but this does not working. How can I do this? Thanks for any help in advance! Regards Andreas klemt ...more >>

onecorp 6/28/2006 5:57 PM PST Question I have a stored procedure which compares one column of a table against each of the other columns in the same table. If it finds a 'one' in each column, it counts the number of 'paired data' . As there can be up to 60 columns and 60 rows in...more >>

I have a table that contains approx 200 thousand records that I need to run validations on. Here's my stored proc: [code] CREATE PROCEDURE [dbo].[uspValidateLoadLeads] @sQuotes char(1) = null, @sProjectId varchar(10) = null, @sErrorText varchar (1000) out AS BEGIN DECLARE @ProcName sysname...more >>

Experts, I have a problem that's twisting my mind up. The summary of the problem is that I have table of organizations, each of which can function in one of two roles at any given time - call them Role A and role B. These organizations will have relationships between them (I imagine it prog...more >>