Hello: I am trying to create linked server ( another SQL Server) to my SQL Server, using Enterprise Manager, but new alias pointed me only to master database. How can I change settings of the link server to get access to pubs database? Thanks, GB ...more >>

as current time.. how can i set that in mssql 2005 ? ...more >>

/* Please help. Definition: I am trying to compose a query that will do part matching based on certain input criteria, by item class. I want to define a table called criteria and let users define sets of criteria that may satisfy a part match. if I have an input set of parameters,...more >>

Hi, In SQL 2000, the following query used to return all the database names: SELECT CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA However, in SQL 2005, it just returns "master" as the database (that too a number of times). Can someone please confirm if this is a bug in SQL 2005? Altho...more >>

Hi I trying register SQL Server 2005 Express Edition instance in Enterprise Manager 2000 without success. Enterprise Manager 2000 do see instance and lists its name in the list of available servers. But connection check returns error: "SQL Server does not exist or access denied. Connecti...more >>

I am writing my first function and it should be be a very simple one but I am getting the error: Server: Msg 245, Level 16, State 1, Procedure InvTypeUSR, Line 9 Syntax error converting the varchar value 'N' to a column of data type int. Below is the funtion and then the sleect statement tha...more >>

Hi I reckon this is a "how long's a piece of string"-type of question but I'll try it anyway. If you could provide any pointers, even if it is not a direct answer then I'd be really grateful. I've written an app generates SQL. I'm joining many tables and it's stable. However, I now need ...more >>

Hello: I create a key in one table, the field is uniqueidentifier, but I search very frequently for one [date] field, and I like to index this field with datetime datatype to optimize the search, how can I do that? Best regards, Owen. ...more >>

Hello, Below are 2 tables and the data (fictitious). I have a pretend flower shop. I sell various kinds of flowers for 5 days (mon, tue...fri). First I want to get a grouping of flowers sold per day, then I want to identify/isolate the day with the highest count of flowers sold - should ...more >>

I would like to know if anyone else has had the same experience where the Update portion runs even though the IF EXISTS returns nothing. The sql with the select works fine. Here is the sample script. use tempdb go Create table test ( id int identity, createdate datetime default getdate()) ...more >>

This question has been posted in sqlserver.reportingsvcs forum but it receives no respond. So I try my luck here: I have SQL 2000 reporting services installed on Win 2003 server. I am able to logon to Report Server but when I tried to logon to Report Manager it gave error message as below: ...more >>

Is there a good reason to use int over tinyint for my look up table primary keys? I usually use int, but most of my look up table are 20 - 100 items max. I know one reason would be that you could not use a -1 as it is an unsigned byte ( I believe). Thanks, Tom ...more >>

I need to find the length of a text field.The len function doesn't work with text fields. Is there an alternative? Thanks!...more >>

I am looking for some ideas. We have a table that is growing from about 7GB to 40+GB but most of the space is unused. I am reviewing the insert proc that loads it. The part that is puzzling we use message queing to initiate this insert on two identical databases. Only one is exhibiting thi...more >>

What the heck does sysname do? There is nothing in the documentation besides saying "its a system name"... that doesnt tell me much at all. thanks! ...more >>

I'm having trouble using xp_SendMail and the char(13) constant in the @message parameter. When I try to send an email message that contains a char(13) in the @message parameter, xp_sendmail gives me a syntax error at the '+' character. If I remove the '+ char(13)' from the string, the message ...more >>

Hi All, How is the syntax for a trigger when someone insert or update one of the fields of my table? Any links? Tks in advance JFB ...more >>

Does anyone out there have any tip on managing DTS packages programmatically? Specifically, I want to install and run a dts package using tsql through query anyalyzer. I know how to run it using the xp_cmdshell, but I don't know how to install it programatically from a Structured Storage Fi...more >>

I'm calling a stored procedure from Visual Basic 6.0 which uses xp_SendMail. The @message parameter was receiving a string variable which was built using vbcrlf constants to line feed the message. xp_SendMail would fail everytime I sent this string. If I removed the line feeds, the email was s...more >>

I need some help in writing a case stmt in a where clause. The select query is, select fldnames from tablename where IIF(datefield1 is not null, datefield1, datefield2) > '01/01/06' I know iifs don't work in sql, it's just a pseudocode.I am trying to use case stmnt in place of iif. Thanks...more >>

I am passing a datetime parameter to a select. DECLARE @TestDateTime DateTime SET @TestDateTime = '20050110' SELECT * FROM Table1 WHERE TestDateTime <= @TestDateTime There may not be a row with a datetime that matchs the parameter, in which case I want to select the next previous row...more >>

If have a tabled-valued function uCalendar that returns a two-column table with attributes dayno (number of days past 19000101) and caldate, which is a formatted date based on dayno. DDL for uCalendar: CREATE FUNCTION uCalendar (@startdate datetime = '19000101', @enddate datetime ) RE...more >>

Here's a programming puzzle for you gurus :-) 1) Is there any way to accomplish something like this in SQL? select top @topcount * from mytable I cannot use dynamic SQL because that will lock my stored procedure. I do not have a column in the SQL query that I can use to compare with (like ...more >>

I want to populate ULF with all the records from ULF_Backup in my License database. I can't get around this error, "OPTIMIZER LOCK HINTS" Here is the SQL code I'm running from Query Analyzer. sp_dbcmptlevel 'license', 70 GO SET IDENTITY_INSERT ULF ON INSERT INTO ULF (SPORTSMAN_KEY, LIC...more >>

Can someone point me in the right direction: How do I say: "Display all rows from MYTABLE where column x is the same but column y is different"? Thanks much....more >>

Hi Guys If i deleted a table data without taking query in ant transaction. Could it possible to recover data ? For Example I wrote query Delete From Table_A where Column_A ='SomeValue' but i forgot to add another condition and delete more recordes than needed now i want to roll back...more >>

I am attempting to load a table with a file that has duplicates on a composite primary key and only want to keep the newest record by a date field. Is there a way to do this with a query or will I need to set up a series of temp tables to accomplish the task. I also need to load subsequent file...more >>

I have a *potential* client that has an MS Access-based OLTP application. They currently have two .mdb files - one for the data that lives on a file server, and another .mdb file on each client workstation that contains the forms, reports, queries, and modules. They are planning to migrate the...more >>

Is there a third party tool that analyzes a database, then your code and links all dependencies, so if you change a view or sp, it'll tell you what code you should also check?...more >>

i have this function: CREATE FUNCTION user.split ( @list varchar(4000), @delimiter varchar(5)) RETURNS @listtable TABLE ( value varchar(100)) AS BEGIN WHILE (CHARINDEX(@delimiter,@list)>0) BEGIN INSERT INTO @listtable (value) SELECT value = LTRIM(RTRIM(SUBSTRING(@list,1,CHAR...more >>

Hello: Can anyone help me with this upload. I have a non-delimitted text file that i need to upload in a table. How do i separate the fields? Junior in SQL SERVER! thanks mx...more >>

Im trying to write a complicated SQL in analyser using several tables and sub selects to do some inserts. I have to do this very often and I only have a limited knowledge of SQL. Can you recommend a book I can go out and buy that will teach me how to write involved SQLs for a beginner plea...more >>

I am trying to find coverage dates for a given list of dates that may have breakage. For example person 123 has the below records: PersonID Date 123 10/15/03 123 11/15/03 123 12/15/03 123 3/15/05 123 ...more >>

Hi ; I'm using the release version of SQL Server 2005 tools (Microsoft SQL Server Management Studio 9.00.1399.00) and .NET Framework 2.0 (2.0.50727.42) on Windows XP SP2. My desire was to use the new tools against my existing SQL 2000 servers as well as newly installe...more >>

I have installation of SQL Server 2005 where I must use the machine name to reference SQL server. I want to be able to use (local), but it doesn't work. How can I make it work? ...more >>

I am trying to run this routine which works fine until I put the outside Select in. The function consists of 4 Select statements that I need to do to get counts of various comparisons in my tables. The problem is that the inside PositionID has to refer to the outside PositionID and that is wher...more >>

Hi, I am pretty sure this is fairly straightforward, I would like to use a trigger to delete an existing record from a table where the value of certain fields match those of the record to be inserted. Thanks. ...more >>

Hi, I want to 'move' a row up or down in a table (pk not carried with it). Strikes me that the easiest way to accomplish this is to copy the values for that row and the row above or below into variables then do updates on those two rows to copy the values over. Fine except I don't see any wa...more >>

I am currently rewriting a data access component to make use of the FOR XML SQL statement to return XML data as an ADO stream from a specified source. The older current component requests this data using an ADO recordset and then manually converts this to XML. I have run several performan...more >>

I have an application which is inserting/updating upwards of hundreds of thousands of records into a SqlServer table. I am using ADO, make my ADO connection, then use ADO commands to insert/update each record as appropriate. At the end of the insert/update I release the connection. I then pr...more >>

I have converted some legacy SQL to use the newer JOIN syntax, however the original SQL returns the required NULL values for the RolesToLinksXRefID column, but the newer SQL doesn't. --OLD SQL Select T.TempID, FT.Message, L.LinkID, L.LinkCaption, RLX.RolesToLinksXRefID From Templates T,...more >>

Can anyone pls help me with any SQL syntax / logic of extracting only the non-unique records from an SQL table ? Thanks Shekhar...more >>

I need to know the best way to code using vba to read a few records from an Sql database(one table), modify these records then add them as new records to the same table. Thanks,...more >>

Do I need to create indexes on a view to speed up ordering and searching, if tables already have needed indexes? For example, if I specify ordering on some column of a view (or searching on this column) and a source table already has an index on this column, I think, the SQL2K uses this index to...more >>