Hello, Imagine I have a table called countries. Every country is the world has a unique name so should my table just have one field called ie CountryName or should I have 2 fields including eg Countryid? I mean, if the countryname fields are unique anyway what is the point of creating an i...more >>

I was reading how to find extended prop in db and found the following line. But when I run I get no output even I have tons of extended prop set on tables and sps. Please help what is wrong with MS Sql Docs. Thanks, Pohihihi SELECT * FROM ::fn_listextendedproperty(default, default, def...more >>

I have two problems with the BCP utility: 1. I have a data file that sometimes does not have a value for some of the fields. I am looking at uploading that data into SQL Server 2000 using BCP. If it happens to be the last column in the row that doesn't have any data, BCP complains about ...more >>

The following query does not compile: SELECT * FROM Food WHERE FoodName IN LIKE ('%Beer%', '%apple%') OK, so it might it been a silly attempt, but seriously... I need to return all the foods that have the words 'Beer' Or 'Apple' anywhere in the name including words like 'Rootbeer'. The rea...more >>

Example, 1) Two transactions, A and B have a Shared Lock on a resource 2) Both intend to update the resource 3) Transaction A comes in an places an Update lock on the resource 4) Transaction A then tries to do a Exclusive Lock on the resource. Shouldnt step 4 cause a deadlock since Transact...more >>

Hello, Not sure what I am doing wrong here. Got this from Alejandro. Works pretty well. I am just not sure where I am going wrong. CREATE TABLE [dbo].[Employee] ( [EmployeeName] [varchar] (50), [ActiveFlag] [smallint], [ActiveTS] [datetime] ) insert into Employee (employeename,act...more >>

Good day all.. I'm working on a query that the PHB would like me to extend a little bit further. I need to be able to select Customer names in an "alpha" range, i.e., all names between a-m inclusive. So I've got Select * from customers where customer_name like '[A-M]%' which works l...more >>

I keep getting an error of Column prefix " t" doesnt match with a table name or alias name with the query with this sql query (part of a c# script): "SELECT d.SalesRep_id,s.fname,s.lname,s.Total_BonusPerc,s.ID,s.Hire_date,s.Term_date, d.ddate,d.salesOff_loc_ID,d.SplitGross, d.SplitRep_ID,d.S...more >>

I'm trying to control the accidental entry of special characters (carriage return) from getting into my data. I thought I could write a check constraint like what follows [name] <> '%' + char(13) + '%' but this doesn't work. Tried several permutations but the carriage return is always ...more >>

Suppose that my application allows a user to query some table information by allowing him or her to enter an arbitrary number of words on a search textbox. If the user typed the string "one two three" on the textbox, I could retrieve the information using a statement such as: Select * F...more >>

Hi there, I'm building a ClassLibrary for a WebApp which should encapsulate all data access. The design should be such, that multiple records are fetched using the SqlDataReader, since this seems to be the fastest option around (and performance is a critical issue here) and read-only, for...more >>

how can I resolve this error message coming from the line of code used to build up a whereclause ? error message ---------------------- syntax error converting the varchar value ' and IDUser >= 1 ' to a column of data type int sproc --------------------- .... IF @ClientID <> -1 BEGIN ...more >>

How can I prevent a table from being dropped or truncated Thank you Mike...more >>

I want to EXEC sp_grantlogin from within a stored procedure. I'm passed a username, but also need the domain - as per the BOL ===================== Syntax sp_grantlogin [@loginame =] 'login' Arguments [@loginame =] 'login' Is the name of the Windows NT user or group to be added. The Window...more >>

Hello, I am getting this error from running active server pages: Response object error 'ASP 0251 : 80004005' Response Buffer Limit Exceeded /Aspsql/loadresults.asp, line 0 Execution of the ASP page caused the Response Buffer to exceed its configured limit. My Hosting provider ...more >>

Hi, I need help with sql update statement. Every month, I will update a table, look for field 'location', if location is '0',or '1', or'2'....'9', I will update it to '00',or'01',or'02'....'09. Which means that I will update all the location less than 10 to '0x'. update table set locatio...more >>

i am sending a database name to a SP , i need that the SP executes some statements over that database, can anyone help me to fin how can i do this? ...more >>

This: SELECT MAX(TheDate) FROM MyTable or this: SELECT TOP 1 TheDate FROM MyTable ORDER BY TheDate DESC As a follow up question to save me having to post, if I want a different field from the result set of a MAX query, how do I do it? ie. I want the "Condition" field of the reco...more >>

I've got a database that was ported from a PICK system that was built a few years ago, so isn't ideal, but it's been working fine. However, I've got one query that seems particularly slow, and looking at the graphical execution plan in SQL Query Analyser I can see that where the estimated exec...more >>

Sometime our users would make a backup and send it to us so we can trouble shoot the problem. The program currently uses MS Access database and it simply makes a copy of the mdb file and zip it up. We are currently working on MS-SQL and I wonder how user from the client side can backup the d...more >>

I have some Lotus Notes files that I saved as Excel spreadsheets. I migrated the files to SQL using DTS. One of the files has a table with 2 columns as follows: Room ID Number of Beds 201 3 202 2 I need to add a column to tha...more >>

I have a csv file with comma delimited fields. Some of the fields have comma in between the text. So the fields appear as follows 100, EUROCLEAR, EUROPEAN INVEST BK 200, EUROCLEAR, "FINLAND, REPUBLIC O" I've created a file format use bcp.exe which looks like this 8.0 3 1 SQLCHAR ...more >>

Is there an easy way to convert a datetime field to a date in a query? I work with an ERP system that stores dates in datetime fields. All I need is the date portion. Thanks ...more >>

I have a t-sql process that generates a flatfile and sends to non-secure ftp via xp_cmdline (ftp.exe). We have moved to a secure ftp site and now this process doesn't work. Does anyone have any experience with writing a push/pull from secure (SSL) ftp? I'm open to t-SQL or DTS, or whateve...more >>

I was just wondering, is there any advantage of using a UDF instead of a SP? SPs have more power since they can alter/edit tables whils UDFs can only query data. Do UDFs have a performance advantage? I was wondering because I have some UDFs that have lots of optional parameters and I keep f...more >>

Hi all, I have a table: Field name: AutoID LNAME FNAME MEDID NOTES PROCESS_BY_PC PROCESS_BY_PC_DTIME AutoID LNAME FNAME MEDID NOTES PROCESS_BY_PC PROCESS_BY_PC_DTIME 1 AAAA BBBB 123 2 CCCC DDDD 234 3 KKKK WWWW ...more >>

Isn't it better to backup databases and transaction logs to a different drive than the data files? Both for reliability (if the drive dies you're hurting since the backup is there as well) and effeciency (less contention on the data drive). If you're backing up everything to the same drive as...more >>

I tried to create a view with the next select, but the Enterprise Manager send a message, "El Diseñador de consultas no admite la interpretación SQL de CASE." Not accept a CASE sql. I need your help to try other way. Thanks. SELECT 'Raz_01'= CASE WHEN TOTAL_ATUAL_MES_01 >= 0...more >>

I got this error message "[ODBC SQL Server Driver][SQL Server]The object 'PK_ClassRooms' is dependent on column 'id'." When I tried to do this "ID field length has been increased from 16 to 25 in class rooms table" Is there a command like in MySQL which is Set Foreign_Key_Checks= 0 to ignore i...more >>

background sql2k on win2k3 server can i still use the linked server if the sql server security was set up as 'windows only'? if so, how? (i've been using mixed mode (sql and windows) in the past and was able to establish linked server from another server by mapping an host account to sa accoun...more >>

I'm not familiar with triggers, but from what I understand a trigger would be the best tool for the job I want to perform. Basically, I have a table with an account number field. The account number has a format of 000-0000, but oftentimes a record will be added without the hyphen. I want some ...more >>

Hello Experts, below I give an example of data and I explain what I need to retrieve: Select 1 [ID], 'A' Descr, Null ParentID Into #TempTbl Union Select 2 [ID], 'B' Descr, 1 ParentID Union Select 3 [ID], 'C' Descr, 2 ParentID Union Select 4 [ID], 'D' Descr, 3 ParentID Union Select 5 [ID...more >>

Is there any way to get the SQL Server's name without running SELECT @@SERVERNAME? I want a DTS package that I am working on to be able to migrate from the dev server to the live server without having any connections to the dev server. To run the query to find the server name, you need a SQL...more >>

Hi All, I have 4 tables in which i have primary keys now i want a procedure inbult cursor to be created which will get the first record from first record and search in the second table and from there it will take the unique record and search in the third table and show me the calculation of ...more >>

I would like to enforce integrity across two databases on the same server. As far as I know there is no way to create a FK constraint pointing to another database. So my options are either a trigger (which I would like avoid) or a check constraint. Does anyone have any expericence/recommend...more >>

I'm investigating why our cache hit ratio is low. Using Profiler, I'm watching cache hits and misses. When an object is shown as a cache miss, the Database ID = 0. When the object shows a cache hit, the object name is blank but the object ID is populated, however, I can't find any objects w...more >>

Hi folks, I need to find out how many connections are made from my application to the SQL Server at a given point of time. I tried @@CONNECTIONS,it returns the number of connections since SQL Server was last started. But I need the concurrent connections at a given point of time. Thanks in...more >>

Hello, I have a field in my db that I am pulling data from. When I put the data on a webpage everything is displayed. When I query the db through either enterprise manager or query analyzer not all of the data is displayed. I also have this same problem if I pull the data from the db and...more >>

Hi, I want to loop through the records of the result set of sp_who. Can this be done? How it can be done? Regards, Sevugan.C...more >>

Hi! I have a couple of processed dataa items that are commonly used throughout many views, to convert a system keys to a business keys and do concatenation of fields etc. to make the application easier to code. Is this what SQL functions or udf's are for or is it just for calculations? Can ...more >>

Hi All! I have question about creating an SQL CLR assembly in which I use HtmlDocument Library from third party vendor. This library refers to few .NET Framework libraries: System.Windows.Forms Accessibility System.Drawing System.Runtime.Serialization.Formatters.Soap When I'm trying ...more >>

I have a stored procedure that creates a temporary table, populates it, deletes certain records from it and then selects all the data from it. In query analyzer I get a resultset, however in my VB6 code it doesn't return a resultset, the recordset isn't even open after running it. If I run...more >>

I need to be able to view users code on the fly to re-solve occasional blocking issues, I can use dbcc inputbuffer(spidid) and EM to view a users query but this does not show the whole query is there a command any one knows off to enter a spid then be able to review the users code thanks fo...more >>

Hi, I would like to kill a process using the KILL Statement. But the Process id is dynamic. How can I kill that process? Example: Declare @spid Numeric SELECT @spid = spid FROM SPIDTBL KILL @spid Is this possible? How can I do this? Sevugan.C...more >>

I have a table with 15 columns. Among these are 2 text columns - acctnum and invnum. If any rows are the same, I want them removed. Thus, acctnum invnum cprice amtpaid address 1234 33688 5.99 12.97 22 Hope Street 9876 33688 4.22 1.97...more >>

Dear All, Please can you help me. I am trying to use parameters for my stored procedure by using a parameter table DNA_Sys_Param. The Sting @strProdClass contains 'BW01','BWI1,'CW01','CWI1','RS01','RSI1' And later on I use it to update another table, using an IN statement, i.e where Pr...more >>

ERROR Error Type: ADODB.Command (0x800A0BB9) Arguments are of the wrong type, are out of acceptable range, or are line : .Parameters.Append .CreateParameter("@inparm", adInteger, adParamInput) MYPROC IS CREATE PROCEDURE st_myProc @inparm INT , @outparm INT OUTPUT AS SET @outp...more >>

Hi, I have a field datatype = datetime The field looks like 5/11/05 00:00.000 When do the select statement where the field = '5/11/05' I got no return. I don't know why. Thanks for your help ...more >>

I would like to log all field changes of all my tables in a structure looking like this: TableReference RecordReference ChangedBy ChangeDateTime LogType Field Name New Value I guess that I have to create a delete, update and insert trigger. What is the best way to do this? How do...more >>

Hello When I do: ----- SELECT TOP 10 * FROM USERS ----- I get the top 10 records. Is there a way of getting the LAST 10 records with a simple query like that? Or should I sort all the records in desc order, and then select top 10? (Reversing the list, making the bottom 10 come to the top, ...more >>

hi I agree with the answer. but just wondering if the question didnt specify " You want to enforce this rule while minimizing disk I/O. " then what are the ways of doing this in real life? also how does option C minimize disk IO. thanks ICHOR 44. (44) You are a database consultant. You hav...more >>

I have a query that returns a result in approx 1 second when run in the new view design window in enterprise manager but that takes about 40 seconds to respond in query analyzer after the view has been created any thoughts? tia eddiec :-) ...more >>