I have 2 criteria(input from a user) to be searched for in a database, each criteria will be weighted (e.g 25/75, 50/50). So i was wondering if there is someone way to capture this in a SELECT statement?? Thanks, Kyle ...more >>

Hi, I have several queries in a complicated database that join almost 10 tables each. The query plans are good because there are a lot of index seeks and merge/loop joins (Hash join have been eliminated by appropriate indexes). It seems it doesn't lack any index. But as an example, a particul...more >>

If I run EXEC sp_MSforeachtable 'DBCC DBREINDEX ("?")' in QA I get messages indicating the results of each table that's reindexed. If I run the same code from an exe how can I get at those messages to either log them in a text file or display them to the user? Thanks, Keith ...more >>

Friends, Using T-SQL in QA I have added a new user (NewUser), login, and pwd. I also added a role and added NewUser to that role. Finally, I granted SELECT on Table1 to that role. But when I try to connect to SQL Server through QA using the new login and pwd, I get error message 18452, ...more >>

Has anyone tried to use C# or another .NET language as the primary database programming language? What I have seen regarding performance is that C# stored procedures perform at least twice as slow as their T-SQL equivalents. I've ensured that test cases are simple (e.g., select newid()) wra...more >>

Hi, I'm having a problem with ADO using C++, can anyone help? The code is as follows: _CommandPtr cmd; cmd.CreateInstance(__uuidof(Command)); cmd->ActiveConnection = con; // con is the connection set up earlier cmd->CommandType = adCmdText; cmd->CommandText = "declare @sys_key va...more >>

Hi All I have a table like this CREATE TABLE dbo.employee ( empid int identity primary key, empname varchar(50) ); I have to model where employee has subordinates and he can be subordinate to others. My problem is each employee can be reporting to many reporting to many employees. ...more >>

I need to return a table of numbers based on a given increment. i will accept a variable that will be a percentage (example 0.02 or 2 percent) i want to return 300 rows of numbers starting at 0.01 and adding the input percentage to the previouos value. ID, value 0.01 1, 0.0102 ...more >>

Has anything new been introduced in SQL Server 2005 to handle multiple values passed into a Stored Procedure or do we have to jump through the same hoops as in 2000? Something like: CREATE PROCEDURE test (@a varchar(100)) AS BEGIN SELECT f1 FROM table1 JOIN string_to_table(@a) a ON tab...more >>

Hello, I am trying to create CLR table valued function using third party assembly(.dll) developed in .NET 1.1 and I cannot figure out how to add reference to it in VS 2005 database project. I have: 1.Added reference path to the .dll in the project properties 2.Registered .dll in the GAC. ...more >>

I'm looking at designing a database that can be easily extended by users to track additional data. For instance, extending a user's profile to track the number of children. I can see three ways to do this. First, allow users to make modifications to the database structure through my progr...more >>

Hello, I have xp_sendmail working fine from inside my stored procedure, however now I would like to have the text marked up with html tags. When I add the tags, they appear as plain old text in the message when I receive it. How can I accomplish getting the text to be recognized as HTML in...more >>

Hello! I am doing initial research on usage of BLOB datatypes in SQL Server 2005. I was wondering if anyone can tell me what major improvements have been made in terms of performance, easy of use in regards to BLOB data types in SQL Server 2005. Will it be more efficient to use other al...more >>

Is it possible, if so, how do you exec a SP and have the results from it be usable so it could be joined into another table? Even if I have to store the results into a temp table first is fine..... ...more >>

I have the below code which is part of a larger piece of code that the @col_name variable holds the name of each column in a table. Problem is, the len and max values are evaluating the column name, not the column data itself. DECLARE @col_name varchar(100) select max(len((@col_name))) from ...more >>

Hi all, I have this problem : I have two Servers with SQL Server 2005 : one dev and one prod. I'd like to do the following : Update my table 'Titles' on prod so that it insert any row not found on prod but that appears on dev. Something like : INSERT prod.Titles SELECT * FROM dev.Titles WH...more >>

I have a table of items where it is possible that various groups of items have identical name and description. I want my select query to return a single representative item for each group of such items. So I run a job that creates an itemHash column for each item (itemHash is based on item...more >>

I can't seem to figure out my syntax error in the following SP: /* Stored Procedure that will assign RadReports to a coder and log the event in the event history -- Jason Wilson 10/4/2006 */ CREATE PROC PR_AssignReport @Coder varchar(36) AS BEGIN --** Find Place Of Service, Patien...more >>

Hello, I run a nightly query that produces a table called CJN_BUCKET2. It contained 22 records today. When I execute the following joins I pick up 1 extra row after the first join and 2 extra rows after the second join, resulting in an employee having 2 or 3 identical rows that I then have ...more >>

I have a web app that will be an online store. As part of a user's profile, they have their credit card stored on our system. The app is using SecurityLib as the data encryption tool. However, what has to happen on the back end of this application, is that another platform (SAP) has to re...more >>

Just curious about your opinions. I have a column that will store characters. The varibility of the length of the data is small (it could be between 2-5 characters but no more than 5). Would you use varchar(5) or char(5)? What factors do i need to consider? Thanks all ...more >>

Hi! In the sample code: -------------------------------------- OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor; WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM Employee_Cursor END; .... -------------------------------------- is it possible to not have two FETCH statements a...more >>

I need to deploy my UDT to my user database and the tempdb database because I need to create temporary tables that have the UDT as a column. I am having trouble with that manual technique. But, from within Visual Studio I am able to deploy to my user database easily, and can deploy to temp...more >>

I am using SQL Query Analyzer to select a couple of columns...one of them being a varchar 1500 column. Within SQL Query Analyzer, the text is cutting off at a certain point and I want to be able to bring the data into an Excel spreadsheet and compare it with some other data I have utilizing an...more >>

I have a query that return the following results: ItemNum ShortName RecData CD Cap 23 CD Volt 120/240 CD UL yes Is there a way to twist the data around so it would look like this?: ItemNum Cap Volt UL CD 23 120/2...more >>

Hi, I have a stored procedure that looks like the following... CREATE PROCEDURE dbo.p_sel_RateMatrix_Factors @pgm_type varchar(20), @n5 varchar(20), @sec_adv_opt varchar(10), @sec_adv_num varchar(10) AS SELECT rm_rate_factor, rm_sec_dep, rm_adv_pay, rm_sec_dep, rm_cost_lower, rm_cost...more >>

WSDL.exe generates a proxy that includes a class "xml", a class that wrappers an array of XML Nodes that is used to communicate XML data to a database web service that requires an XML input parameter. Question: How does one structure this array of nodes for a complex XML document? Breadth ...more >>

Hi all, Im trying to select data from a database via OpenQuery (ODBC, Progress), When doing this I get an error indicating that data in a column formatted as datetime i wrong so query fails. Is there a way to do selects with option like "On error resume next"? I want to skip the value settin...more >>

Hello, I have recently restored a database on a test server from a backup of a production server. I did this to basically update the test with the production data. I chose to do this over a simple import/export data package because I wanted to choose the objects I was bringing over. M...more >>

I would like to put myself on a SQL DBA track. I am currently a Programmer/Analyst within the SQL Server world and would like to take the next step into becoming more technical. Can anyone out there suggest some courses or training that I might be able to take that would me to get on this t...more >>

I need to Write into a file in Visual Basic. Have to open the file and the contents of the file are based on a SQL query. How is this possible? ...more >>

I need a query that returns only the last/lates (most current) by date for a computname. Table TableID, Computername, IPAddress, Date_Time Data: 1, computer01, 192.200.130.100, 10/01/2006 09:00:00 2, computer 01, 192.200.130.100, 10/03/2006 08:30:00 3, computer02, 192.200.130.200, 09/3...more >>

I have two tables, a document table and a pages table. I have a query(below) that joins them together and a sub query used to limit the 2nd table to the minimum page number. I need to get the sourceid field from the page table for the minimum page for each document. The problem is that quer...more >>

Greetings, all. I am trying to get a rolling average of turnaround times. For graphing purposes, I would prefer to have this automated. I would like to have the rolling average for the last 7 days. For instance, today I would have the average turnaround time for days 0 through 7. For yes...more >>

I have a SQL SERVER query. I can also save this query as a stored procedure. Based on this query the result has to be written into a text file. How do I go about starting this. Any ideas Pls? ...more >>

Hi, I'm looking for a design pattern (preferably leveraging SQL Server 2005 features) to create a batch job which will process 1000s of different user-defined search criteria against a large DW. The data warehouse schema is classical star schema in a healthcare setting: Claims, BillLines, D...more >>

Hopefully someone knows the answer to this. The SQL Management Object (SMO) is usually coded in Visual Studio .Net, but the only examples I have of it from my "Upgrade Skills to SQL 2005" class are in VB .Net. Does anyone know if this can be coded in C #? Our company is becoming a C # ....more >>

Hi We've moved a copy of our live database to our dev server for testing, ranaming it to TEST on the way. We now get the message Could not find server 'ABC' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. ABC is the name of the LIVE server. We also do not use or...more >>

Sorry for being dense but I can't find any info on this. Do I have to do all string concatenation into a single variable before passing the value to a stored proc, Or is there another syntax. This is what seems natural (From sample code below) exec dbo.test_proc @s1 + @s2 Thank you for ...more >>

HI All, I have a deadlock which Im unable to simulate or get a trace. So what I require are the steps which you think would cause the deadlock. One of the transactions participating in the deadlock does the below. DELETE FROM Table1 WHERE X = 1 AND Y = 2 INSERT INTO Table1 (X, Y, Z) VAL...more >>