I am trying to break a concatenated address into 3 separate strings starting with the street address. For example- From the following string: '191 S. Central Expwy., Suite 26-Lewiston, ME 89004' I am trying to parse: '191 S. Central Expwy.' Using: LEFT (StrAdd, CHARINDEX(',', StrAdd)-1 bu...more >>

I have a database that I use to store infornation regards my DVD collection (sad I know!) and have recently added a new table that stores text regards "easter eggs" a DVD may contain. The table consists of the following: EggId INT IDENTITY(1,1) PRIMARY KEY NOT NULL DVDId INT NOT NULL Egg...more >>

Boy, this should be a simple bit of code but I can't figure out how to make it happen. I have a CSV file shows up in a directory that has 4 fields that need to be printed on labels. Each line of the CSV looks something like this: AcctNo, Name, junk, junk, Address, junk, PhoneNo I need to...more >>

Pardon my stupidity, but I am new at SQL and I wanted to find out how to reindex a database. I have made a backup of it and I would like to reindex the whole database, not just a table at a time. I have this script from a website but it does not work, it bombs out on me. Any help is appreciate...more >>

Hi, I need to update several columns of a row and then return the status of the update whether it was successful. I take it that I'd use a trigger to rollback the transaction and give a status of fail when the update is a no go. How would you script it? Thanks. -- bic...more >>

On the below, I'm trying to inner join on 2 fields. example data: stcdl.od = 'AZDB1234' itk.od = '1234' UPDATE itk SET itk.ta = stcdl.ta FROM itk INNER JOIN stcdl ON SubString(stcdl.od,5,4) = itk.od Obviously, what I'm trying to do does not work. What will work? thanks! ...more >>

I ran an delete statement and forgot to change one of my selection criteria before I hit F5 in query analyzer. I realized my mistake immediately and clicked cancel and it said 8500 rows affected. When I clicked cancel before the statement completed did it cancel and roll back the transaction o...more >>

I am getting an error when trying to update my local server using data from a linked server. I query the server before beginning a transaction, and it appeared to work fine the first time I ran the code, but it fails every time after the first. I neither want nor need a transaction on the li...more >>

ok i need to insert tracking numbers form our ups table into our shipping table. right now i have an update query that works it looks like this: update shmast set shmast.fmtrckno = upsin.fctrkno, shmast.fno_boxes = upsin.fnboxes,shmast. fshipwght = fnpkgwt, shmast.FSHIPMEMO = 'Amount: ' + ups...more >>

I got a Parent table and one that should be a child table of it but found the relationship missing. The fields to create an maintain it are there but the relationship itself is missing. Since this is on a production environment I want to make sure that the records in te table are OK and if n...more >>

Hi, I have SQL Server 2005. I have a database "A" with the default collation "SQL_Latin1_General_CP1_CI_AS", and another database "B" with the default collation "Danish_Norwegian_CI_AI". I'm running a lot of "INSERT FROM B..table1 SELECT * FROM A..table2", and apart from the collation...more >>

I claim that format files for bulk insert and BCP are not documented completely or correctly. In SQL 2005 BOL, in the index under Format Files [SQL Server] subtopic Skipping Data Fields, the example shows 4 for the "number of columns" (the thing in the second row of the .fmt file). Wh...more >>

Hi, I have a table like this: account#, invoicedate, product, invoice#, sales, quantity I like to find out one per account, on each product line, the price per quantity on each invoice on 05/01/06 and showing the total invoice sales and qty as well. The price should be sales/quantiey. ...more >>

Hello all, I find myself before a riddle of building a query consisting of two tables where in one table field is a string with values separated by a comma. Like this: table1: Record1: Field Prefixes has a string value '25,36,47' Record2: Field Prefixes has a string value '55,66,27' an...more >>

Hey guys I have a multiplayer game application i coded. All data to and from clients goes through the server app i coded but i do have a database back end. i want the clients to be able to access their Gamehistory and so on which is in the database. My question is should i: a) Get cl...more >>

Using SQL 2000 SP3 on Server 2003 Enterprise, on a change of data for a specific field can I get a trigger to run operating system shell command or script?...more >>

I have a tbale with "Text" column. The datalegth always returns 1024 for that column? Any ides if this is a confiuration porblme.. I have SQL server 2000 8.x...more >>

Hi, A user is having trouble using my app to connect to a SQL server database. My app reads a connect string from a file. Is there a easy way to have the user test the connection string using standard tools rather than my app? I'm looking for a way to say 'it's not my app'.... ...more >>

Hi All, I haven't used MS-SQL for many years and I'm now trying to port a Firebird database to MS-SQL 2000. Of course the syntax for store procedures and triggers is significantly different. I don't want to get into a here's_the_way_we_used_to_do_it_back_home pissing match, but here's an ex...more >>

I have a two tables: Users Table: UserName Security usr1 grp1,grp2 usr2 grp2 usr3 grp3 Menus Table: Name Security ABC grp1,grp3 DEF grp2 XYZ grp4 I want to display usr1 the menus that I can run. declare @s varchar(50...more >>

I am not sure if this is possible but here goes. I needed to import many DBF files into my SQL server 2000 database. DTS was not a good solution because the name of the DBF files vary and can change from day to day. I found a converter that converted the DBF file into a SQL script that cr...more >>

Is there a function that can take an @Variable that will drop a table? I can't seem to be able to get Drop Table @Variable to work because Drop Table doesn't want a string, it wants the plain table name. -- Michael Hockstein...more >>

Why does a semicolon appear at the end of some T-SQL statements in 2005 BOL? It doesn't seem necessary. ...more >>

Hi all, Trying to parse a comma seperated values correctly into more then one column in a Table Variable. Credit goes to this site for part of the code http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm This is what I have: Run in Query analyzer..... Declare @Passedin_Li...more >>

Hi all, I need to convert a value in hexadecimal to varchar, and this does not seem to be as easy as DECLARE @hex varbinary DECLARE @str varchar SET @str = 'test' --PRINT @str SET @hex = 0x0000000000000001FFFF07D600002710 SET @str = CAST(@hex as varchar) Any ideas? Has anyone tried t...more >>

I have this data create table abc (a char, b char) insert into abc values ('a', 'b') insert into abc values ('b', 'c') insert into abc values ('c', 'b') insert into abc values ('b', 'a') I want to return only 2 values from all 4 since a b and b a are repeting what is the way to do ...more >>

Hi, Would you please tell me how to specify the column data type when using BULK INSERT to upload data from a tab determinate txt file? Thank you hb...more >>

Hi, I know they changed this a bit for 2005 so I am hoping to get a quick answer. I have a username and password for a user. With that username and password I will connect to a SQL Server. Next what I would like to do is query the server for all of the db id's and names this user has acces...more >>

I have a questions and would greatly appreciate any help on this from the experts. I created a trigger (LOGtestfive) on table (testfive). How would I run this trigger? This trigger biscally displays the SQL statement, login name, user name and current time so I would like to monitor who mod...more >>

Hello all, I'm looking for some information about storing BLOBs in SQL. A bit of background: my company is developing a document management system. Previously, we had stored the files on the file structure and used a pointer in the database. We're not switching because the next iteration of ...more >>

We have a large database that gets queried often for reporting purposes. For data access, we are limiting the users to views only, no direct table access. The view definitions can be quite complex, incorporating as many as 20 tables or more. We have been limiting the view definitions to con...more >>

Hello all I'm trying to build a ranking from a query with an order by of quantity desc. The result layout would be like this: Rank code quantity ----- ------ -------- 1 543 10000 2 989 5000 3 121 10 how can I achieve this result namely the Rank col...more >>

Hi all, I'm in the process of creating a query to create, itterate and then update a group of 123 tables. Here is a shortened version of it to give you an idea of what I am trying to do... DECLARE @x as int SET @x = 1 SELECT identity(int,1,1), place1, place2, [value] *2 INTO flow1 F...more >>

Hello Fellow programmers, currently I am working a series of stored procs in SQL Server 2005 to gather record(s) from a database based on a set number of fields. All fields are not mandatory for the stored proc to work via the Coalesce function. If the param is not given a value it is assigned a...more >>

Hi, I need to join two big tables and return all rows of the tables. The problem is the two tables are big so the performance of the query is very bad, I plan to select from one table in a batch, put the batch in a temporary table, join the temporary table with the other table, drop the temporar...more >>

I have two questions and would greatly appreciate any help on this from the experts. 1. Sql server Logs the information at many places, we are trying to consolidate them. So I would like to know all the sources from where we can get the SQL server logs without missing any log information. ...more >>

I am trying to utilize the new GUID function on my tables (NEWSEQUENTIALID()) as my primary key field but I can't figure out how to return this value when I do an Insert in my stored procedure. Can anyone point me in the right direction? Thanks Mark ...more >>

Greetings All, I have a simple question (hopefully simple) that I am hoping has a simple answer. Let's say there is a C# application that calls a stored procedrue and it is wrapped in a transaction (from within C# the transactioin is initiated). If that stored procedure also had a transaction...more >>

Hi All, I am connection to SQL Server 7.00 using a SQL Server 2000 Query Analyzer. When i run a simple select satement and close the Queiry Analyzer window i get the following message : "The are uncomitted Transactions do you wish to commit these Transactions before closing the WIndow". ...more >>

I am using SQL Server 2000. Is there any scripts i can run and find out how many triggers in a database and n table I am a new DBA and I was told last DBA wrote some triggers on a certain tables so I want to locate those tables who has a triggers Farrukh...more >>

Hallo everybody. I am trying to make a schedule of database backup (SQL 2000). For technical point of view is very easy to configure that, but I have a doubt: which is tha best phylosophy for a complete backup ? My idea is: every 20 minutes backup of transaction log. (if I mantain 2 days,...more >>

Hi, I'm trying to execute the following query: select count(1) from (SELECT COL1,'aaa' + convert(varchar(255),COL2) from TAB) tableId As the table is empty, the following error message appears: No column was specified for column 2 of 'tableId'. What should I do to protect from such erro...more >>

I couldn't find any commands in SQL that allow me to directly send my query results to a text file. I do not want to use command line utilities and command shell procedure calls such as bcp. I can easily do so in MySQL eg. SELECT * FROM CUSTOMER TO OUTFILE TEST.TXT. Would appreciate as...more >>

Hi. I'm trying to do a pretty big update command and I'm looking for a way to do a batch update rather than having a seperate update comand for every row. Take the following two tables as sample data... table 1: place ratio ----------- --------------------------------------- 1 ...more >>

Hi Team, Can you please guide me why this portion of code generating errors? ===================================================== Declare @Where varchar(2000),@Query Varchar(8000),@outerQuery varchar(8000),@outerOrder varchar(8000),@rowCount int,@NQuery nvarchar(4000) Select @Where = ' where ...more >>

I have the following tables, and want to make it so that you get a combination of all possible values from the child table (RateCriteriaValue) based on the possible items in the parent table (rateCriteria) so for example if I had these two items RateCriteria - Cr1 - Cr2 and these value...more >>

Hi All, i should perform update to table of next sructure: Table1 ID | VALUE -------- | ------------- 1 | aaaaa 1 | bbbbb 1 | cccccc 2 | ssssss 2 | eeeeee ............................ ............................. ID ...more >>

Hi Everybody, I use MSSQL 2005 so LDF File size 116 GB and MDF file size only 2 GB How can i smaller LDF file size or empty PS: Error Code Autogrow of file 'MgzPro_L' in database 'MgzPro' was cancelled by user or timed out after 523047 milliseconds. Use ALTER DATABASE to set a smaller ...more >>

Hello, I am using SQL Server 2005. I want to write t-sql procedure to return a record from one table and copy some of the fields to a new record in another table. (Copying part data to a Purchase Order). The only way I can see to do this is using a Cursor or a temp table. However it seems that...more >>

Hello again, This time I am trying to use the case statement to determine an update. It won't work. It seems that case will only work with a field from a table and not a passed variable. Is there any way to make this work?: ALTER PROCEDURE [dbo].[spSupplierOrderDetail_AddUpdate] -- Add the...more >>