Hi, I have created a stored procedure that selects a number of fields basd on the user selecting a customers name. When I run the SP all the records are shown instead of just the one I wanted. When I run the select as a query I get the correct result. The SP executes fine but shows this ...more >>

I have SQL 2000 SP3 installed on my machine used for devel and testing, I have a client with a stratus box with sql 2000 sp3 installed, and same database for testing. I have a stored procedure that is nested and calls another. The stored procedure hangs when it calls the nested procedure, if...more >>

Hello all, I have seen several examples of pivot queries, I am not finding an example that will do what I need it to, and I am running out of time to find a solution. I am building a customizable application that allows someone to create a 'template' that can be later populated with data...more >>

Hi, I have a need to return a result set where a colum is distinct, so that I don't get back duplicates. The problem is that the result set contains an image col, and the SQL parser says that i cannot have a distinct because this data type is not comparable. Is there away around this......more >>

Although this statement execute without any errors: insert into dbo.Employees(LastName,FirstName) values('Test','Test') This produces error: execute ' insert into dbo.Employees(LastName,FirstName) values(''Test'',''Test'') ' also with ": execute ' insert into dbo.Employees(LastNa...more >>

How to query to tables and joint them from two different databases. System Database and Master Database. ...more >>

Hey all, I am trying to insert some data from one table (table 1) to another (table 2). The issue I have is, that in table 2 there is a "key" column which has a key that is incremented by our software (ie. no identity column in this table). It is a primary key column, and thus has a constrain...more >>

I am new to SQL triggers. I am used to Oracle Before or After AND Statement or Row based. I am guessing SQL's are After Statement Level. I am getting confused on how to handle multiple identity values in the Inserted table and how to identify what action occured insert/update/delete, and ...more >>

hi I am trying to run a simple query with a group by clause and below is the query and the error i get USE Mobile_Mentor_MSCRM SELECT ContactExtensionBase.New_ReferralMethod1, ContactExtensionBase.New_ReferralMethod2, ContactExtensionBase.New_ReferralPoints1, ContactExtension...more >>

Hope someone can help I've used Access for years, am just moving up to SQL-SERVER Express and = I cannot see what is wrong with the following example The following query runs fine=20 select description, qty*unitCost as cost from components but if I try to use the 'cost' alias in any subse...more >>

Greetings, Question: Is it possible to manually start a Job using T-SQL in Query Analyzer? If and when our main scheduled job ever fails, I want to set up a *.sql script that will run a manual Job that makes appropriate fixes. Right now, I'm stuck with going into Enterprise Manager, right-...more >>

With T-SQL, I would like to drop a foreign key constraint of which I do not know its name, but I know 1. the reference table, 2. the reference column, 3. the foreign table, 4. the foreign column. Up to now I did not find out any method to do so; I looked in system tables (e.g. sysobjects) but the...more >>

Hi all, Is there is some way to get set of recompiled stored procedures of some dataBase in some period of time?? I heard that this posible through system views in Sql Server 2005. Any suggestions?? TNX in advance??? -- Imagination is more important then knowledge. (A.Ei...more >>

Hello - I am attempting to find the sum of values of two columns and divide that sum by the amount of hours worked from another table to get a percentage of completed loans per day. The following code works, but only if the date entered covers one day. If I try executing it with the date ...more >>

are there any file properties held in a BLOB that can be extracted? = Specifically the creation date of the file. thanks Rob...more >>

Hi All, Trying to change datatype of a Column to Money from nvarchar ALTER TABLE TESTTABLE ALTER COLUMN TESTCOLUMN MONEY I get this message below? Server: Msg 260, Level 16, State 1, Line 1 Disallowed implicit conversion from data type nvarchar to data type money, table TESTTABLE, co...more >>

I have the following query that takes forever to run. I inherited this and don't know the best way to trim the time down. I am thinking a self join might be faster but have limited experience. The query is getting a list of people on site today (TIMEATT=1) but I want to filter this by peopl...more >>

There are lots of cases in MS SQL where I need some columns from a left- hand table, and some columns from *any one* of the rows in the right-hand table that match the left-hand table on some common column. I can do this with some complicated SQL that repeats some conditions in order to crea...more >>

Using SQL Server 2005, we're looking to move data from one database to another using SELECT INTO. One of the columns in the source table is a char(1) column with only the following values; 'Y' and 'N'. In the destination, we want that informtion to go into a bit column with 0 and 1 instead o...more >>

Is there any real way of figuring out which indexes in a db are being used (besides going through millions of rows of Profiler output)? In Oracle, you can query the compiled objects in the procedure cache to see which indexes the query plans are using. Is there any way of getting something ...more >>

Hello, some values in my table contain trailing spaces: 'Denver '. I tried to identify these values using Select City, Len(City) lenc from tblx where city like 'Den%' This returned all 6's for lenc even though a few values were 'Denver '. How can I identify the values that contai...more >>

Lets say I have the following table. --------------------------------------------- KEY | Value --------------------------------------------- 1000 1 1000 3 1000 5 1000 19 1000 22 200...more >>

Looking for a second pair of eyes to see why I am getting an error on a "Select" statement. Thanks in advance for your help. Jack /* Code Snippet Below */ Declare @ExclFam as int set @ExclFam = 1 Declare @TmpTblXS TABLE (Item1 varchar(27), Item2 varchar(27)) Insert @TmpTblXS ...more >>

When a record is updated, I need to populate a field (in the same record) with a calculated value. The problem is: You cannot modify the "inserted" table within an UPDATE trigger. Is there another way of doing this? What about an INSTEAD OF trigger? ...more >>

how to group the data by monthly? thanks... ...more >>

Hi, I have created this statement in one of my stored procs: /**************************/ SET @sqlString = 'select nbVisitors = count(1), nbPages = (select count(1) from vw_reports_logs b WITH (noexpand) where dbo.getDateHour(b.startTime) = dbo.getDateHour(vw_reports_ip.startTime)), db...more >>

we have very complicated application and cant do relationship between tables. I am wondering if i use triggers to enforce data consistency. for e.g. I have two tables. Parent table : Employee (emp_id, job_id) Child table : Job (emp_id, job_id) when user insert record in child table, trigger c...more >>

Does anyone have an example out there of updating MSSQL via BCP using IBCPSession interface stuff in C#? I've been through the docs, but as an VS/C# newbie I'm having some trouble connecting the dots. For starters I just want to populate a form field and send the text off to a database via thi...more >>

Hi -- I have an ASP.Net C# application on top of SQL Server 2005. There are stored procedures to insert new rows in a table. The user types in a code and the field:code is PK so I need to alert the user if a row already exists with that code he/she used. What is the best way to handle PK v...more >>

Hi guys, another stupid question :( I´m running SQL Server 2000, Ok, I'm trying to insert from a select statement in to a table, both fiels are varchar the source and the destination, the problem is tha my data gets truncated when ever the text has the character "/", sometimes it get's tru...more >>

I am trying to figure out how to do a boolean select that works in an IF statment. I'm just trying to see if a certain userID exists (actually if it DOESN't) in a table . How can this be accomplished? I can do a count into a variable and do the if on that variable, but it looks like it would...more >>

Hello All Is there any way to insert a 00/00/0000 as default date in a SQL Server Datetime field. For that matter is that possible in any database which has datetime fields? I think the answer is "no" but I would like to get a few other opinions too. FYI - I tried to insert it into a da...more >>

Hi, I have identified approximately 100 user tables in my database that "can" be deleted. However I have written the following query to add some semblance certainty and provide a simple report. My question is: if I have 100 tables(listed in excel sheet) to do script with, and I need a sing...more >>

Why does this work? declare @data xml set @data = '<?xml version="1.0" encoding="UTF-8"?><Stuff></Stuff>' Whereas this does not: set @data = '<?xml version="1.0" encoding="UTF-16"?><Stuff></Stuff>' Error: XML parsing: line 1, character 39, unable to switch the encoding Thanks! Ch...more >>

Service Broker and ServiceBrokerInterface class (C# 2.0) --------------------------------------------------------------------------------------- I have a proble with the ServiceBrokerInterface. Before use it, i have created 1 contract, 1 message Type, 2 queues and 2 services (no need code here...more >>

I've got a new project that includes collecting "medical record" data. Possibly this data will have the requirement that, once entered, it cannot be edited but only appended to. If so, I think I'd like to enforce this in the database. The simplest approach that occurs to me is to put an u...more >>

Hi, I have a quite disturbing behavior on my sql table (sql server 2000 enterprise) The "processed" column is of type : bit (nullable) When I launch : select count(*) 'total' from inetlog select count(*) 'not processed' from inetlog where processed = 0 select count(*) 'processed' from ine...more >>

I've been pulling what is left of my gray hair out on this one, can't = seem to get my query to work as I want. Source Data EMPLOYEE ID Name -------- ------------ =20 RX Johnson PJ Smith EMPLOYEE_Groups ID GroupID -------- ------------ RX 2 RX ...more >>

Hi, Is there an efficient way to Select data from a table in one database (db1) and insert it into a table from a second database (db2) using the INSERT command? Thanks, germ...more >>

Hi I cannot get a recordset returned from an SP if it is created in a temp table and I loop through it with a cursor and update the table. I note similar posts where the answer was 'set nocount on' which allowed me to return the recordset as long as I do not run FETCH NEXT FROM Cursor and begin...more >>

Hello - I am trying to find what percent of loans are approved. My code is as follows: Select Distinct lo.LoanOfficerFull, SUM(CASE WHEN l.DispositionID = 0 THEN 1 ELSE 0 END) /SUM(CASE WHEN l.LoanTypeID < 3 THEN 1 ELSE 0 END) From tblLoanOfficer lo Join tblLoans l On lo.LoanOffi...more >>

I have a developer who developed a stored procedure using a cursor over/through a linked server. The remote machine, a Unisys box, can be very slow at times. My question is this... when the cursor is executed ie... the cursor is 'opened'... 1. is the entire data set brought across the Lin...more >>

Hi, I have a report that accepts multiple keywords (keywords separated by commas into one string) into one parameter but the problem is that it can be any number of keywords. Then I need to compare these keywords to one of the other field names from my table. So I do this comparing inside...more >>

I have tried both of these queries but still it is listing SP's which have come along with SQL installation. Can somebody throw some light on this? 1. select * from sys.objects where type='p' and is_ms_shipped=0 2. select * from sys.objects where type='p' Regards Pradeep...more >>

Hello, I have a SQL Server 7.0 database running under Windows 2000 Server (SP4). If I create a new table and add more than 5 columns the Add and Update triggers stop working. The triggers do not fire and even with the triggers in place I can edit and save the table design. Just...more >>

Hi all, I am facing one problem while executing stored procedure. I have one stored procedure which is taking 3 input parameter and returning one output parameter. First time if i execute stored procedure it is taking lots of time for same parameter list but if i subsequently execute same...more >>

Hi I use SQL Server 2000 I have 3 sessions of query analyser open with results returned in all 3 windows (no query is currently running but I have datasets in the results panes from past run sql) Can anyone advise me whether this is a drain on the sql server resource and will possibly impact p...more >>

If a fact table in a star schema design is partitioned on a date surrogate key (date_SK), will the following type of query exploit the partitioning? select sum(sales) from fact_sales F, dim_date D where F.date_SK = D.date_SK and D.year_no = 2006 and D.month_no = 8 I've read that "the ...more >>

Lately our accounting system has been returning some ODBC Errors that are causing major issues and I am stumped. The two messages I see are Error: General ODBC Error [Microsoft] [ODBC SQL Server Driver] [DBNETLIB] connectionwrite (send()) Native Error 10054 Error: General ODBC Error [Mi...more >>

Hi, I have a table with no PKs. (and cant have them!) What I need help with is a query that will return a set of data without duplicate ItemGroup_id. NOT A Primary Key, but instead deleting the first of the duplicates. EG Where ItemGroup_id = 959 twice, 959 PG_1 ...more >>

hi i have the table : table1 ( id1 int IDENTITY(1,1) NOT NULL, id2 int not null ) with the constrain : CONSTRAINT con1 FOREIGN KEY(id2) REFERENCES table1 (id1) my question is : how to insert into this table a new row with id2=3Did1 (witch is compute= d by = server) ? thanks ...more >>

Hi all, One of our SQL Servers has had *cough* one or two *cough* logins created that are not required, we've gone from a list of about 30 to a list of over a thousand (most people in our organisation). I was hoping to clear this up and remove a load - what I'd like to know is if there's...more >>

hi there, i have a stored procedure that has a cursor to fetch data, store it into a temporary table, say #tempItems, then return that data to the user, and finally drop the table. as its over the web, if a number of users simultaneously hit this procedure, is there any possibility that...more >>

Hi, I'm implementing an image registry and am going to be storing path strings in an SQL (Express 2005) database. I would like to know whether I should set a limit on the length of a path string (the tree depth), or whether using varchar(max) would allow unlimited (after sanity checking) p...more >>

Hi all I run a sp with enterprise manager but it gives me timeout error on 30 seconds. I changed the Query Timeout in Options menu to 0 (unlimited). but it still occurs on 30 seconds. thanks in advance ...more >>