I have a WHERE clause when the end user picks to see ALL records or a selected id records. I am using the below CASE Statement to try to accomplish this but if there are records with the contact_id = NULL they do not show up. My CASE statement when ALL is picked it does a comparision that sh...more >>

what is the t-sql command for getting rid of a Unique Identifier Constraint on a table? What I'm trying to do is append the contents of a table into another and I want the the unique identifiers populated as but cannot drop the constraint to do this within the same stored procedure. Plea...more >>

I'm upgraded my SQL Server 7.0 to SQL 2000, but I was force to back to SQL 7, because one (or more) T-SQL sentence generated wrong data. It didn't generate error messages, so, I worked a week with wrong data. The fail-response T-SQL sentence, is a complex, run-time-made sentence that seek ...more >>

I have a table with a column defined as Text, is there a way in a SELECT to get the length of the data stored in the Text column? Thanks, Matt ...more >>

<similar message posted in .Net Framework & .languages.vb> In a file from a COBOL dump, which is in ASCII, one of the fields is defined as S9(9) V99 Value +0. The value in this location is 0000018922D, which according to the author of the source, translates to 00000189224. I've imported the...more >>

Hello, I'm looking for some Microsoft SQL Server documentation tool that would allow me to create a documentation of tables, views and stored procedures with my descriptions. Do you know some good one? Thank you. Regards, Petr Palas ************************************************...more >>

Hi: How do I write a sql select statement to get all the field(column) names and their types of a table? Thanks Q. ...more >>

SQL 2K I got two tables - A and B Columns in A - id, route Columns in B - id, seq_num , pri_route, sec_route I need to update column 'route' in table A Data in both tables are stored like Table A -------------- id route 1 NULL 2 NULL 3 NULL 4 ...more >>

I'm not quite how to make this thing work, but I need to send a table name to a stored procedure as a variable. Kind of like this: select * from (@mytablename) where sdate> @stdate and edate < @endate ... When I try this SQL insists that the variable "mytablename" be declared, even when it I...more >>

How do I get the table create date without using sp_help? I have a website querying a sql server table, and I would like to have the page automatically pull the create date of the table instead of changing it manually. Thanks, Carlos ...more >>

Hi I have written a very simple stored procedure which returns the count based on some parameters. When I run it I get the error: Must declare the variable @count. Could anyone please help. create Procedure sp_search_patient ( @firstName varchar(30), @lastName varchar(30), @middlename...more >>

Ok, I am getting the following error from this script. I am not sure why. Everything looks ok to me. -----------------------------*/ Server: Msg 107, Level 16, State 2, Procedure AddContractProduct, Line 18 The column prefix 'a' does not match with a table name or alias name used in the q...more >>

Hi, In my upsize program I create text file (from VFP 8) and add data in SQL using BCP: EXEC Master..xp_CmdShell 'BCP MyDB..MyTable IN "C:\Temp\Src.txt" -c -e"C:\Temp\Src.err" -S"myserver" -U"sa" -P"" ' There is a character in text source data: ASCII 182 (musical sign). However after...more >>

I have a table with varchar column having date as 12/02/2004 I need to convert it to smalldatetime. I'm using query select convert(smalldatetime,col1) I get the following error : Syntax error converting character string to smalldatetime data type....more >>

This works good, thanks. One question though, is there any way to see the SQL with the substituted values in the statement? That is, i have a sproc with some statement that compares data to @SOME_VARIABLE. Is there a way i can see what the value of @SOME_VARIABLE that it executed with is? T...more >>

Hi I have the following code: select distinct object1type from ipvc_ee_mapping Which reutrns: IPVC.EE.Global.Action IPVC.EE.Global.Interaction IPVC.EE.Global.Patrol IPVC.EE.Global.ResponsePlan IPVC.EE.Global.Scenario IPVC.EE.Global.Trigger I am then lo...more >>

Anyone know how two join muliple records into 1. I have 3 tables like the Pubs DB. Authors Titles TitleAuthors This allows for many to many relationships. I need to be able to query this database so I can import titles & authors to a system that does not deal with many to many relati...more >>

Hi all, I'm having slow response times with the following query: UPDATE Records SET Flag = 1 FROM Records A, Updated B WHERE A.CATID = B.CATID AND A.NAME = B.NAME AND A.STATUSTIME = B.STATUSTIME Basically I have two tables - Records which has the actual records, and Updated which con...more >>

Hello there I have an update trigger is there a way to match the entire INSERTED and DELETED tables without maching each field on them? any help would be useful ...more >>

Hi, How do you join a table that is located on another server? When I run sp_linkedservers my server shows up ... but when I try to select from it ... select top 5 * from server004.fg_collections.dbo.addrnotes I get .... Server 'server004' is not configured for DATA ACCESS. any help w...more >>

I am changing existing database and I met the N'string' but could not find any help in msdn? Could anybody tell me what it means. Is it connected with unicode? ...more >>

I am trying to design a "roll down changes' where the user can click a button on as asp form and roll down the changes to other records. I have this long, huge stored proc, but when I run it, it grinds for about 2 minutes and then says there is not enough system memory to run it. Sorry for...more >>

hi out there! i'm getting frustated over a problem that looks quite simple: i have two related tables (mother & child) with a foreign key constraint: tabA - tabProductGroup: ProductGroupID; ProductGroup 1; Group 1 2; Group 2 tabB - tabProduct: ProductID; ProductGroupID; Product 1; 1...more >>

Hi, We're getting a new server that is a dedicated SQL box. We want to make 3 partitions. 1 to hold the data, the other to hold the indexes and the last to hold the transaction log. I wouldn't even know where to start to know how to do this. Can anyone help me. thanks rafael ...more >>

In relation to another thread... What would the proper educational/experiential path be to become a professional DBA? Thanks! ...more >>

Hi, I'm trying to detect failure from a DTS package I'm running from with the DTSRUN command line utility and xp_cmdshell. Can anybody tell me how I could do this...? Here's a snippet of code that calls the dts package... set @cmdRun = 'dtsrun /S' + @@SERVERNAME + ' /E /NGenerateTreadRepor...more >>

Books Online states - "All data types, including text, ntext and image, can be used as a parameter for a stored procedure." However, for the life of me, I cannot figure out how to get a table datatype as a parameter. What I am really trying to do is accept an array from VB6.0 as a pa...more >>

How do you view index's on a table programatically? (is there a stored proc to do this) ? ...more >>

what' s wrong with my sql statement select * from (select * from table1) ...more >>

Hi all I keep getting and error message that says that I am a Deadlock Victim. Is there anything that I can do about this. What is causing this. I have a VB6 application using ADO to connect to a MS SQL Server 2000 Database This always happens when accessing the same table. I am not s...more >>

Dear All, I am facing problem in using the "TRIGGER" There are 2 tables. Ex Table 1 Table 2 - (Insert Trigger written in this table) I am getting some value from previous page(asp) Following is my code structure ======================== <% Set ProductRs = Myconn.execute("...more >>

We have a big DTS package that runs nightly via SQLAgent and uses several global variables. The programmer changedsthem via script throughout the process and assumed that the changes would be retained the next time the package was executed. Of course the programmer has now left the firm. In ...more >>

I'm getting a number of timeout errors on an .asp page on a very simple update transaction: Microsoft OLE DB Provider for SQL Server error '80040e31' Timeout expired I then went to the SQAnalyzer, and ran the exact same query that was timing out (from a display in the prog). It took *foreve...more >>

I have a SQL2000 table (Appointments) with a column name of 'AppointmentType' as nchar(1) and the column is set to allow nulls. When I try to update the table it will not allow a null value. The collation for this column is set to Latin1 I have tried to create the null value via O...more >>

If I'm unioning 2 result sets that will never have the same row twice should I use UNION ALL? I know they will never be the same because one result set has a column with a fixed int value of 1 and the other has 2. Something like SELECT ID,1 FROM SomeTable UNION SELECT ID,2 FROM SomeOtherT...more >>

Hi group, I've been having problems with the locks in SQL Server. I have a visual basic aplication that runs a store procedure. This sp uses querys and updates. From my aplication is opened a transaction and is closed until the store procedure is executed clompleted. My problem is that whil...more >>

Hello Im currently using Finnish_Swedish_CI_AI_KS_WS and it orders W and V wrong like this: a c b ve w1 wa v0 which collation should i use instead? /Lasse ...more >>

Hi How do we get the actual field names from an SP? We can return all the parameters, but how about the underlying field names from a select withing the SP? Thanks Tim Cowan ...more >>

Hello, all. Is there a way to search for a string, such as "pidm", in the column names of all user tables of a SQL Server 2K database? We need to get the name of all tables with columns that contain that string. Thanks. ...more >>

I'm executing a stored procedure using sp_executesql, how would I get the return value of the stored procedure I'm calling...? ...more >>

I am thinking of using cascading as a solution for a 'single' problem. Up to now I did not consider cascading because I think cascading should be avoided. Cascading will only be used for the one specific problem, but when using cascading it is switched on for 'everyone'. And I am not to happy w...more >>

How can I store images on a CD or separate disk and reference the location within a column in a table in the database? As the number of images increase, will the size of the database increase to represent the actual sizes of the images? Or since we will only be storing the location of ...more >>

How would you use the 'exists' command as opposed to 'not in', when executing a query? ...more >>

Hi All, This is my situation: I have a field in a table called grpid VarChar(2000). This field holds unique ids(guids) separating each other by a comma. My .NET application Splits these elements when I need them and puts them together before Updating/Inserting them. I am trying to wri...more >>

I am working on a tool that helps our company maintain a huge database at multiple sites and I would like a way to compare any database structure to any other database structure but I am not sure how to approach the problem. I am using SQLDMO right now to maintain table structures in VSS but I thin...more >>

Hi I have written the following SQL query that is specific to the SQL server, to bring back a list of people with their titles entered in lower case. This coding appears to work in Oracle but not in SQL. Can you help? SELECT DISTINCT AgtRefNo, AgtTitle, (LOWER(AgtTitle)) FROM Agent WH...more >>

Hello All, I'm using in an update trigger the condition If SUBSTRING(COLUMNS_UPDATED(), 1, 1) > 0 begin bla bla bla end I've noticed that some times this "if check" doesn't returns always true even when a column is really updated, is it ever happened to you too? did find any s...more >>