I am new to SQL and Visual Basic. I have been given the following project Convert a flat file to comma delimted and import into SQL Database format Users need to lookup records on the table by name or zipcode The name and zipcode will change constantly. The end user will not write SQL queries eac...more >>

Hi, I have three tables with the Structures. Table A : (EmpId , SystemId) Table B : (SystemId) Table C : (EmpId, SystemId, ImpSystemId, IsCompleted) Table A is the Master table it is having all the rows. Table B refers the Master table A which SystemId refers A.SystemId...more >>

Hi all I am using SQL Server with ADO Recordset. I am having insert statements and update statements in this stored procedure (SP). I need to return a recordset from this SP. The last statement of this SP is: Select @svOutPut As Status, @lngReturnTransactionNo As TransactionNo, @svErrCod...more >>

Hi All, I have the following data in a table called Product Product Table Customer_No Query_No Date_Logged Code 57153 3951254 21-Jan-03 31 57153 3951254 21-Jan-03 44 57153 3951254 21-Jan-03 32 57914 ...more >>

I am trying to get a list of dates into a result set *without* creating a new table. I came up with this idea declare @date as datetime set @date = 0 while @date < getdate() set @date = DateAdd(dd,1,@date) continue end How do I access the @date variable while it is in the 'While L...more >>

Can someone give me some pointers on writing DLL's for and using them from T-SQL? Are there any special considerations (aside from COM registration) for DLL's written in DotNet? Thanks in advance to anyone who can help me out, Bob ...more >>

I am fairly new to SQL Server 2000, coming over from a MS Access. I use to create a UNION Query in MSAccess and then turn around and import the query into a DB as a table. However with the 2GB size limitations in MSAccess I purchased SQL Server 2000 Standard but I am unable to figure out how ...more >>

Hello, I have SQL Server 2000 with the latest service packs. I also have Visual Source Safe 6.0. My application has a lot of Stored Procedures and user defined functions with SQL Server. What is the best way for me to track changes of these procedures within VSS. I am developing in VS .NET 2003....more >>

What is an easy way to obtain only the "last payment" made to each unique individual and the date the amount was paid. Is the use of MAX() or is there a better function to use. I do not need totals, just last actual payment. Database cover multiple thousands acct_id amount_paid pos...more >>

Greetings, I need to pick up the last 2 digits of a year in a dateTime field as a char(2). In VBA there is a Format function ... Format(datevar, "yy") which would give me '04' from '1/1/2004'. What I have been doing for Sql Server is ....Substring(Cast(myDatafld As varchar(11)), 10,...more >>

Why is it, Microsoft manage to write operating systems and office applications with every bell and whistle facility known to man. Yet, even after years and years of development they still cannot produce a decent version of MS SQL Server, one without a plethora of flaws and limitations? Persona...more >>

I am trying to index a calculated field in a view and running into troubles. Can someone tell me how I can create an index on nUselessDiscount and yTotalOrdered? The ultimate goal: sum( [Order Details].UnitPrice * [Order Details].Quantity * (1 - [Order Details].Discount ) ) as yTotalOrdered, ...more >>

hi, i'm storing temp records using þ as the first char to make sure they are sorted after z (the non temp ones). but i just found out it comes before th (thorn i suppose). I'm using the collation sql_latin1_general_cp1_ci_as. so what char is after z-Z? i hope i dont have to change the collation....more >>

Hi, I am using stored procedures in my application to keep the end users from having permission on the database that they should not (like delete rows and such) so the only thing the end user has access to is execute permission on the stored procedures... BUT the problem is it sometimes (not a...more >>

I have a giant calculation from hell that's bombing with the below error, and I have zeroed in on this call to convert(). Any thoughts on why these two simple statements fail? SELECT CONVERT(DECIMAL(38, 37), 999999999997) SELECT CONVERT(DECIMAL(38, 37), '999999999997') Server: Msg 8115, Le...more >>

Is there a way using replace to change the following: "Some text "some more text"" to "Some text some more text" where Some text and some more text can be any combination of characters. I tried to break it into pieces even and couldn't get it to work. For example to get rid of the d...more >>

Does anyone know the proper syntax using the sp_rename to rename a primary key on a table from lets say "PK_shopper_id" to "PK_shopper_id_0609"? Please advise... Cheers! Brett ...more >>

I am new to SQL programming after inheriting a SQL 2000 server 2 years ago. I have been writing Crystal reports and it is getting me more and more in to SQL statements. I've been getting through not bad with texts by Rover Vieira but I'm really stumped on this scenario: TableA - Customer...more >>

Hi, where can I download yukon beta online? ...more >>

I'm having trouble trying to get this sp to work, I get a 'Syntax error converting datetime from character string', anybody tell me how to add the dates? CREATE PROCEDURE sp_getstuff @Start_Time DateTime, @End_Time DateTime AS DECLARE @strSQL as varchar (4000) DECLARE @strStartTim...more >>

I have a table with these fields and values: GuestID Check_In_Date Check_Out_Date ABC 2003-01-10 2003-01-13 ABC 2004-03-10 2004-03-13 ABC 2004-06-01 2004-06-05 DEF 2003-11-24 2003-11-28 I would like to find out guestID - ABC, how many days ...more >>

I am a helpless newbie, but after researching books, newsgroups, query wizard in Access, etc. I finally hit on a solution to this problem. I can't explain how or why it works and am only posting this for the benefit of anyone who might also encounter the same problem. Those more experienced sh...more >>

Hi I am looking into linked servers, and have yet to find a comprehensive discussion of them. I have cobbled together a growing sense of the scope of what's required from a number of disparate sources (which I find hugely irritating and not confidence inducing) Anyway, I find some conflicts...more >>

Using SS2000 Standard. I need to output the results of a series of queries to different worksheets in an Excel document. I need to have some control over where the data goes in the spreadsheet. I worked a little bit with the Excel objects and Access. Is there anything built in to SS to do this Tha...more >>

I have a table with a trigger. When i insert the data into my main table the trigger also inserts a row into an audit table. Problem is that both tables have an Identity column. So, when I try to get to the identity (using Set @ID = @@identity) I get the ident of the row in the audit table ...more >>

I am in need of a custom function that can convert local time to GM dbo.GMT('06/09/2004 12:00:00 PM' RETURNS '06/09/2004 5:00:00 PM'...more >>

I have a column whose data is a string then a dat ex. Comm Carryover from 02/28/200 I need to remove the dates, but the strings are not always the same lengt ex. Comm Carryover 12/31/2003 (as compared to above And some have no dates, so just Comm Carryover How can I get just the dates int...more >>

I'm trying to grant execute on a user-defined function that returns a table, but I'm receiving this error: Granted or revoked privilege EXECUTE is not compatible with object. I've granted execute to functions before, so I'm not sure what the problem could be here. Thanks! Melanie...more >>

Say you have: Insert into test(Portfolio_ID,Account_ID,Statement_Date,[Description],Commited,ROR,Unit_Value,ROR_Netoffees,Unit_Value_NetofFees SELECT distinct @Client_ID,@account,@asofdate,@mneumonic,'*', Round((#returns.Riturn*100),3),Round((((Round(#returns.Riturn,6))+1)*@OldUnit),3), Round...more >>

Hi Everyone, What would be the most efficient way to find out when a stored procedure was last executed? Thanks in advance Larry...more >>

Howdy, If I do this in a vb app call to sql server (tbl1 contains say 20 records) Dim ... .... cmd.CommandText = "Select * From tbl1" Set RS = cmd.Execute(RecordsAffected, , adCmdText) Debug.Print RecordsAffected the debug window shows -1. If I do debug.print RS.RecordCount - the d...more >>

I have dateStart and dateEnd field in my table. For example: dateStart=09.06.2004, dateEnd=16.06.2004 I would like to get the monday of the week of start date and sunday of the week of end date: Something like: SELECT monday(dateStart) as start,sunday(dateEnd)as end FROM table result: ...more >>

I have several tables and indexes that I'd like to move from their current filegroup FG1 to a new filegroup FG2. I can't seem to find the correct ALTER syntax to do this. Can you help? ...more >>

Hi, I have been looking into the use of a separate server for an archive partition. I have read up on sp_addlinkedserver and sp_addlinkedsrvlogin. In particular, the ability to map all users on the primary server to use a single login on the archive server via sp_addlinkedsrvlogin is quite att...more >>

Good Morning, I have a question. Is there a function like ISNULL but for the space value(example ISSPACE)? THANKS ...more >>

Hi everyone, I have a table that consists of a person's name and up to 4 violations. Where each violation can be attendance, conduct, or safety. Name Violation1 Viol2 Viol3 Viol4 Jim Safety Conduct Safety Tom Attendance Safety Safety Safety Jack Safety...more >>

Hi I'm working with SQL Server 2000 and want to get following result: this is Mytable called T1 code description qty ------- ---------- ------ 1 a 2 2 b 3 4 r 55 5 d 7 8 e 9 9 f 1 10 h 1 17 z 1 and I want to show numbers are in gap range of code column ...more >>

I have a column that stores SSN's with hyphens 555-66-7777 I need to remove the hyphens from these fields. I've looked in books online and realize I need to use UPDATETEXT, but I need someone to explain thi UPDATETEXT { table_name.dest_column_name dest_text_ptr } { NULL | insert_offset } ...more >>

Hi I want to know what is command that i add to the Query Analyzer until i tell to the database that it Shrink my DB every 1 day I know it can be done from all Tasks > Shrink Database .. But i want to know the Code commands for that for using in Query Analyzer Thanks ...more >>

Is there a query that i can write to find out the recovery models for all of my system and user databases on my server? There has to be a better way then manually going through the databases one by one through Enterprise Manager. I am using SQL Server 2000. Please advise... Cheers! Bre...more >>

I know this is invalid syntax but it shows what I am trying to do. i.e. find the distinct names in table1 and table2 and create a new table containing the unique union of both. How can I do this? select distinct name1 from table1 UNION select distinct name2 from table1 into names ...more >>

Having a brain fart and I know this is simple but I've already wasted a couple hours - so I was wondering if someone could help. Example Source Table: Id: Company: StatesCovered: 1 ABC WA 2 Car Toys IL 3 Car Toys ...more >>

How can I find out what are available indexes for a given view? We have a view but I am not sure what are the existing index of that view. Second question, how do I utilize a view in my sql query? Thanks in advance!...more >>

Hi How can i get field Description ? i now one way to get this with "sysproperties" table. But i know this is not good way,because Microsoft recommend with information view or sys. stored proc. so which way i have to do this ? Thank you ...more >>

I have this main stored proc to process different stored procedures depending on the flag it receives: CREATE PROCEDURE sp_DetermineClients (@entityflag int,@dtstart datetime,@dtend datetime) begin declare @sp sysname select @sp = case @entityflag when 1 then 'sp_Aclients ...more >>

I have an SQL server with data files which have name_LOG.ldf files much much bigger than the data files themselves. How can I delete them or crunch them? If I just delete them it appears that the data file can no loger be read. Thanks, Raul Rego NJPIES rrego.njpies.org ...more >>

Hi all, I am testing for probable corrupt or dirty data, which prevents my user-defined function (dbo.as400_date) from working correctly. I am using the following: SELECT dbo.AS400_Date(BBMPCE) AS Production_Date FROM STAGE.HVBBREP where isdate(dbo.AS400_Date(BBMPCE)) = 0 wh...more >>

Hi, We've got a problem where one of our tables is getting cleaned out at a certain time each day. Obviously this is some scheduled item on some machine somewhere, but we're not sure which machine is doing it. What's the easiest way to find out what the offending machine's IP is? Regar...more >>

Remote Object Instantiation from TSQL procedure using OLE automation by calling sp_OACreate ? How to: ?? regards...more >>

How to ? please thanks...more >>

Hello All, How can I concatenate multiple TEXT lines (the maximum number of lines can change and can be huge) stored in multiple rows into one single line in one single row.Which data type variable can this single line be assigned to in one go? Thanks in advance....more >>

I have a table with a clustered index. That table is the record source for an Access form. Is there a way I can determine the row number of a specific record in the table? For example, let's say I have a table of employee names. I would like to know the row number of the record contain...more >>

I have a table defined a CREATE TABLE Table1 [ID] [uniqueidentifier] NOT NULL [ID1] [uniqueidentifier] NOT NULL [ID2] [uniqueidentifier] NOT NULL ) ON [PRIMARY Both the ID1 and ID2 reference to a 2nd table defined as CREATE TABLE Table2 [ID] [uniqueidentifier] NOT NU...more >>

Dear All, I created a view with the 'WITH SCHEMABINDING' option. As I test I went to the table which the view depends upon and deleted the column the view depended upon, and it worked. I then went back to check on this and looked at the views code, the 'WITH SCHEMABINDING' had been taken...more >>

I am confused when creating relationships between tables. My question is when creating an identifying relationship between two tables I understand that the Pkeys of the parent become the pkeys of the child. However, I am using Erwin modelling tool and it seems when I add this identifying relations...more >>

--Hi. I posted this week and was advised to post a DDL, so I've made my first attempt at the DDL and I hope the explanation below is adequate. I was told that the naming conventions are a little screwy but that's largely out of my hands. I'll see what I can do. I also realize I have Accoun...more >>

Hi, I am creating random numbers by casting a newID() to a varbinary and then to a bigint, this all works great. The problem comes when I try and use this in a case. The simple broken example is select case abs(cast(cast(newid() as varbinary(16))as bigint))% when 0 then when 1 then...more >>

All I have data in a table as illustrated by the DDL below and I am looking to populate the sequence number field for each department in a products table starting at 1 to n for each department. What I am trying to achieve is update the sequence number as below ie. the seq number starts...more >>

Dear All Actually I am interested to know what are the benefits of a FileGroup.And how it can be useful in SQL serve databases Any help very much appreciated Cheers Sunil Sabir...more >>

In BOL it says that many clients can be used to load a table with bcp or BULK INSERT, using tablock hint for best performance. mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\adminsql.chm::/ad_impt_bcp_1968.htm I am confused as I would have thought that will caus...more >>

Hi I have a SQL file with several SQL commands Is there a SQL commad that permits to execute this SQL file at time Thanks....more >>

Hello, I know that syscomments stores the stored procedures in chunks of 4000 characters. Some of my Stored proc's are >4000 characters.I use a temporary table to stock them after some modifications made to them.How can I execute them one by one, do i need to concatenate one whole stored proc's t...more >>

....why these results are different SELECT REPLACE('ab' COLLATE Latin1_General_CI_AS, 'a', 'a ' SELECT REPLACE('ab' COLLATE SQL_Latin1_General_CP1_CI_AS, 'a', 'a ' SELECT REPLACE('ab' COLLATE Latin1_General_CI_AS, 'a', N'a ' This is on SQL2k sp3 (build 8.00.919 Thank Dere http://www.sqlp...more >>

hi there I've a simple question,but trying to solve this problem since... lngFormDistCompany has the data type: text (NOT: long!) When i'm starting the statement and try to take all records with empty field lngFormDistCompany, i never get any results.. How have I to change the statem...more >>