Is there any date function available to get the last date of a month. If not , How can I get it ? ...more >>

CREATE PROCEDURE GetData AS SELECT * FROM Bogus When I am executing above procedure I got following error. Server: Msg 208, Level 16, State 1, Procedure GetData, Line 3 Invalid object name 'Bogus'. I want to Insert above sql generated error message to a table from a procedure (Ver.SQl ...more >>

Hello everyone, I've written a procedure to generate XML dynamically(structure). The procedure has a parameter which says the no of levels the xml would have. so i've wriiten the query as string in a loop declare @query varchar(8000) while @min<=@max begin set @query=@query+'seleect ....more >>

Hi, I am trying to add a row to my table but I get the error message "invalid column name SOBN and BN1" on this statement. Basically, I am trying to add the row into the same table that I am searching if it does not find SOBN = 5 and BN1 =3. What is the problem? strCommand = "INSERT INTO t...more >>

I try to use the table datatype instead of temp tables (using the #syntax) when I need to work with a small recordset (fewer than 100 rows). However, I seem to have come across an unusual issue. I am attempting to update the columns in my @table from a table in the database, by Query Analyze...more >>

Hello, my question is how can I get my data to read like this: Cat Small 10 BIG 5 Dog Small 7 Big 1 Is there a way to only list the main topic once and then the detail can be indented like so? ...more >>

I have a single table which we use to keep track of data from different physical machines. The columns are: TheDate, TheName, & TheNotes. The Date column is datetime and the rest are char. Every day each machine we collect data on gets a row of information: The date of the collection, the mach...more >>

Conceptual and speed question Hi, My program seems to slow down drastically because as I fill my array and table with many values, the program suffers tremendously. The first thing my program does is to search the jagged array to try to find an element in that array. If it does n...more >>

I am wondering if there is any risk involved using LTRIM or RTRIM that cut off data. What I mean by "cut off data" is that by using LTRIM (or RTRIM), data (other than empty space) is lost or deleted. And why should we not just use TRIM instead of using RTRIM and LTRIM together? For instance...more >>

I'm trying to test for concurrency, using a SQL Stored Procedure on a RowVersion (timestamp) Field. The vb code I'm using is as follows Dim cmd As New SqlCommand("ConcurrencyCheck", cn) Dim intID as integer = MyDataset.Tables("MyTable").Rows(0).Item("ID") cmd.CommandType = Command...more >>

I am doing a DTS from table "source" to table "destination". However "destination" has a primary key of data type uniqueidentifier and the source doesn't have a corresponding map field. So I thought setting the "destination" primary key to have a default value of newId() will automatically i...more >>

I'm running the Best Practice Analyzer on my sql 2k server and one of many issues is about a bunch of cursors. The issue is for this cursor in an SP. Actually there are the same 2 issues listed for a lot of SPs they are: This cursor is declared as updatable (either explicitly or implicitly)...more >>

I have several databases (sql 2k) that appear to have stored procedures created by/for a source control. However, I have not assigned any source control (that I'm aware of) to these databases. Not all the dbs have these SPs, just a few dbs do. There names are like this: dt_addtosourcecontr...more >>

Hello: I know this is very simple request, but I can't figure out how to do it in SQL, and it seems silly to write VB or something to do this. I have a table of Referrals (100,000 records - referrals to social service agencies), and a tblReferralFrequency which I created with Select Distinc...more >>

Please check this BOL example of IF UPDATE trigger which I am trying to use it but not getting the desired results. It is under Trigger Programming. A. Use the IF UPDATE clause to test data modifications: CREATE TABLE my_table (a int NULL, b int NULL) GO CREATE TRIGGER my_trig ON my_t...more >>

I am implementing a multi-variable search SP to which I need pass single values and arrays as arguments, e.g. exec mySP 'abc', {3,5,8}, 'en-US', {4,5}, ... etc How to pass the args to mySP and how to retrieve the values from the arrays inside the SP? TIA ...more >>

Hi, I'm new to SQL. I like to pick up the previous date. How can I do this? I have a date field in my table with mm/dd/yy 12:00 AM. I want to do a query to get the previous day's date. How do I convert the date to the previous one? For example: today is 11/18/05 12:00 AM, I like to get 1...more >>

I've got this error message doing something like this: table A inside a SQL DB, table B inside a linked ORACLE DB; begin transaction; insert into table C inside a SQL DB select fields from A join to B I've got the problem only in a particular configuration: SQL 2000 sp4, Oracle Client 9.2,...more >>

Do you think user-defined function has better performance than stored procedure in SQL Server....more >>

Okay, i have a problem, which would be easy in a well-normalized db, but im working in a legacy app here, and can't normalize the db because it would break the existing apps around it. I have a manifest table which essentially acts as both a header & line-items table. There is a MANIFESTID f...more >>

How do I display time from 23:00:00 to 11:00pm or vice versa, Thanks, Lam...more >>

Hello, I have this alter proc MyProc @Param int = NULL as begin if @Param is null begin set FMTONLY ON goto FinalStruct end -- some calculation based on some tables, let's say declare @Cnt int select @Cnt = count (*) from s...more >>

Folks I'm currently working on a project where I will need to store hashed passwords in a table using SHA-256 neither MD5 nor SHA-1). I'd be glad to hear any suggestion as of the best way of implementing this, both in terms of ease of coding and throughput. Targeting 2000 only - .NET fra...more >>

Is it possible to create a CLR Table-Valued Function in SQL 2005 in which the fields can be defined at runtime? In other words, table-valued function must define a table definition in the [SqlFunction] attribute, which makes the definition of the columns you are returning from the function sta...more >>

Greetings- We've recently had a problem at two different customer sites (that we know of) where the query optimizer seems to be ignoring an index for a simple join. The query is simply: SELECT tbl2.* FROM tbl1, tbl2 WHERE tbl1.col1 = tbl2.col2 AND tbl1.col3 = ?someinteger Col1 has a...more >>

This is really a conceptual question; I'm after the best way to update a table whilst using a lookup in another table. Imagine that I have table A with 4 columns and table B also with four columns Conceptually, what I'd like to do is the following: Update table A Set A.field1 = B.field...more >>

I need to join two tables I guess in a rotate fashion but have not been able to figure it out. We have a Taxo_Status table that contains up to 2 entries per species. These records need to be joined Taxo_Status_LUT table such that one record is turned per species with a state status description...more >>

Hi I am looking for info. on how to synchronise a database on SQL Server 2005 Express, to SQL Server Mobile on my PDA. I want to have an application which can either update the data on either version, and have them synchronise back to each other. Can anyone point me in the right direction...more >>

Hi, I am a real newbie when it comes to this stuff. I have 2 databases in MS Access. (I know this is a SQLServer group but I need to learn to walk b4 I try to run) Each has a table called taps. These tables are almost exactly the same except a couple columns. I imported taps from one database in...more >>

Hello, I got this massage, 'Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes' How I can do it? Thanks, JN ...more >>

I'm having a problem getting the types right for a CLR stored procedure. First, do you always have to use nchar/nvarchar parameters? I was getting errors trying to use varchar parameters in the T-SQL. Second, I can create the proc, but when I execute it, I get the following error: Msg 6...more >>

Hello, We developed a web based application using utf-8 char set. How to configure sql server to run ok with utf-8 char set? Andrej Zeleznik Ljubljana, Slovenia ...more >>

Hello this is weird when I run this on a Friday SELECT DATENAME(dw,5) --> returns 'Saturday' Select DATEPART(dw,GETDATE()) --> returns 5 Can anyone explain why this would occur? ...more >>

How I can "select" from stored procedure-something like: select * from sp_tables_ex @table_server='x' Thanks. ...more >>

If I have a SQL table with Columns A B C D and an Primary Key of A then which of the following would perform better - OK I know it depends on volumes etc, but just by examining the query is it better to update more than actually required so you can use the primary key and therefore index seek,...more >>

OK... I'm trying to import data that is sent to my company in an XML file into an SQL database. My first solution was to write a VB app that used SQLXML Bulk Load 3.0 and that didn't work, so I tried another option provided to me in this newsgroup and that didn't work. Now I am now tryin...more >>

Hello, friends, We call stored procedures from our app (asp.net), and use Try...Catch to handle any possible DB error. But, can we handle all errors in a stored proceudre? In another word, all DB errors should be caught within a stored procedure, and return back to callers gracefully, as if...more >>

So we upgraded. Now I'm stuck with Management Studio - which seems to be a great tool. However, I spend most of my time performing one-shot queries against one or more of my client's databases and Managment Studio is like using a jack-hammer to put in a nail. It works but all you needed was...more >>

Hello, friends, We are calling stored procedures (sp) from our web app (asp.net). Since using Try...Catch... in asp.net with C# or VB.net is very expensive, we are considering to handle all database error in sp so that web app won't see any DB error. Do we have a way to catch all erros i...more >>

I have a table (inherited) that gets data from a DTS package. The problem is that the table that the data is being read in to wasn't created with a unique id field. Is there a way that I can add a unique id field and have it populated? This table has 86000 records in it already. TIA...more >>

Hello, I want to restore a customer database on a empty database of my server. The two bases have different names and different paths for datas & log. How to do that with SQLDMO? Thanks...more >>

I am using SQL Server 2000. Is there a way to have a field default to the value of another field if it is not provided? and how? -- TIA Altman ...more >>

Hi... this is a multi-part question on the topic of setting up referential integrity between two tables, one on each separate DB, on the same server. First, is this even possible? If yes, then how would the foreing key be referenced? And what if the referential integrity were to expand betwee...more >>

I am very new to development with SQL Server but I have lots of experience with Access. I am producing a report in VB.Net using CrystalReports that will display a company's address differently depending on the contents of the data. For example, if the second address line is NULL then I don't wan...more >>

Hello everyone. I've been wrestling with this one for a while now and am hoping you can help. My company is moving from InterBase to MS SQL Server 2005 in the very near future and all has gone well so far, but there is a stored procedure on the InterBase database that I have had trouble findi...more >>

Hello, I would like to find and alternative to using a cursor and updating my table with the calculated values. The following code snippet is from my store procedure. -- Calculate and Update LEWeightedTerm and LEWeightedRR DECLARE LECursor CURSOR FOR SELECT TransactionID, Term, Busine...more >>

Hi Sorry to those caught in this cross post, but the setup group is a bit dead...you guys are usually spot on. I am trying to set up a hot spare server. The production database is set up with server collation Compatibility_52_409_30003 (and for teh database too). I am having a problem...more >>

Hello! I am trying to read the not for replication setting of an identity column from a Sql Server 7.0 database but I cannot find that piece of information. I check the information_schema tables that look approriate and the sp_pkeys procedure. But no luck. Has anyone an idea? Thanks f...more >>

Hello, I'm quite basically new to working with SQL databases, coming from a Microsoft Access background. As we've got the SQL server sitting here, I've been asked to get the MS Access db's moved across onto it. I've had no problem with the tables, but as the majority of the reporting comes ...more >>

Sorry for the Off-Topic post, but is this now the recommended place to post re SQL 2005 new development features; SQLCLR, Service Broker etc? Niels -- ************************************************** * Niels Berglund * http://staff.develop.com/nielsb * nielsb@no-spam.develop.com * "A F...more >>

Hi, I have Instead of Delete Trigger in my Table. When i delete any row from the table it shows the No of Rows affected ex:- 1 Row affected But when i look into the table the Deleted Row is still Present. My Question is If there is Instead of Delete Trigger then is it not possible to...more >>

Hi! Is there anyone that have figured out how to add a servers and groups to the "Registered Servers" in Management Studio using SMO. I'm having problem understanding how to use the RegisteredServer and ServerGroup objects in SMO. Examples in VBScript would be really nice but all pointers a...more >>

Dear folks, I’m wondering how do I obtain full scripts of all the current databases in a Sql Server of an automated way instead of choose one in one out from EM. Does anyone knows or is awared of any mechanism for that? Thanks in advance and regards, ...more >>

Hi, I've been looking around for a while now after an efficient way to enumerate tables, rows and sprocs of a MsSQL database. The best I could come up with was SQL DMO, but one cannot have it installed without installing SQL Server itself or MSDE, so I'm looking for alternative. After a ta...more >>

Hello, I've got some question about XML. I have such script - declare @table table (i int, x xml) insert into @table (i, x) values (1,'<a>av</a>') insert into @table (i, x) values (1,'<b>bv</b>') insert into @table (i, x) values (2,'<c>cv</c>') select * from @table and I want to get ...more >>

Hi, Is there a way that SQL Server 2005 generates insert statements for the data it holds? Forexample I have a table with some default values that I want to export to. On the instalation I would like to be able to run the sql script instead of having to restore a backup. Thanks, -Mark...more >>

Hi All, In the below code, I declare variable @var2 inside the else part of the statement. The code is written in such a way that the flow of control does not go into the declaration part. I expected an error saying variable not declared, but the code runs without any error. Could some...more >>

i have this: if exist(select * from sysobjects where name= 'table' and type = 'u' ) begin update table set a=1 where b=2 end and its giving me an "INVALID COMUN NAME B" error,because of course it does not exists but in sql 2000 its works perfectly, i noticed that in 2005 on another datab...more >>

After many tries, I have gotten a SQLXML Bulkload to function in my VB app. However, it is now giving me an error from some of the files that I can't understand. The function is: Private Function importToSQL(ByVal importXML As String) Dim noErrors As Boolean D...more >>