I've been struggling with many problems around importing a small one table database in sql 2005 to a new database in 2005. A reply in a thread in the dts group told me I needed to select "copyschema". That helps so the import ran with out an error as long as nothing else was selected, hence,...more >>

Fumo 10 euro 0,8 grammi Maria 10 euro 0,8 grammi Cocaine 100 euro 1 g...more >>

First, thanks to the guys for helping me earlier just to get my XML output into element form. I'm new to XML, so I appreciate all the help and hope I'm learning. My next question has to do with nesting the data that comes out as a result of several JOIN's Here is my code: DECLARE @t TABL...more >>

hi all, i was little bit confused when my Manager told me that we use GOTO to branch to a particular label,especially while tracking errors in procs.This is clear, am not confused, but he also added an point that if the procs does not produce any errors , then also the label wil be executed...more >>

I have a varchar field called calltime that contains data that looks like this "03:34:55". I have other fields called login and logout. The calltime field is calculated by subtracting the login field from the logout field. So if I have login "11:45:21" and logout "11:45:55", the calltime shoul...more >>

I have a transaction where I am doing the follwing in this order: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN 1. Read table A 2. Insert 5 rows in table B 3. Update 5 rows in table A with keys created in table B to create a 1 to 1 relationship COMMIT TRAN I need to prevent...more >>

I'm getting an overflow error (see ERROR below) when trying to calculate myPercent (see MY CODE below). I've tried using CAST (see MY CODE below) but can't get a successful execution. I posted my DDL (see DDL below). Can someone please help me get over this overflow? I've lost a day trying to...more >>

I am trying to create a simple trigger that will act upon update of a record. I have existing VB6 code to do the parsing, which I can convert to VB.Net(2) The trigger must determine what record was edited that caused the trigger. Then it should capture the value of a string field from Column1 ...more >>

Here's a good one guys... I inadvertently created a column name with a suffixed space. Here is an example. CREATE TABLE [TEST] ( [ID ] [CHAR] (10) NOT NULL ,[DEF] [CHAR] (1) NOT NULL DEFAULT ('N') ,[NAME] [CHAR] (50) NOT NULL DEFAULT ('') ) ON [SYSTEM] GO N...more >>

My application calls OpenSchema(adSchemaColumns, ...) on an ADO Connection object. Using Profiler I can see that under the hood of SQL Server 2005 it's running "exec [dbname]..sp_columns_rowset N'tablename',N'dbo',NULL". When I'm logged in as "sa" it correctly returns column default info (C...more >>

Hi, Using SQL 2000. I have two non-indexed views. When I write a query like this: select * from view1 where colX = 5 union all select * from view2 where colX = 5 results come back quickly. However, if I create a third view: create view view3 as select * from view1 union al...more >>

Folks, Some newbie question I guess... Assuming the following query SELECT CAST(COALESCE((SELECT SUM(qAmmountReceived.comm) FROM qAmmountReceived WHERE (NoPol = cases.csePolIdx) AND (YC = cases.cseYear)), 0) AS float) AS ...more >>

Guys I have a data table that contains emails that could come from one or more source. I want to add a column to the table to reflect the combination of sources that the record came from. Basically I am trying to create the hybird_src and the hybrid tables from the data_table. Then I want...more >>

I've created a dynamically generated query (see below) which works and returns a column, but am at a loss how to make a view out of it. I've thought of wrapping the query in a UDF, but am only familiar with returning single, scalar values, not a column. Is this possible? Please let me know ...more >>

Hello Group, I am using isqlw from a Windows batch file to execute a query; something like: isqlw.exe -E -i C:\purge.sql -o C:\Temp\purge.tmp -FA Is it possible to read the value of a batch variable inside the .sql? Example: In the batch file, I would do: set var1=M and in the .sql, ...more >>

Is there any way to retrieve AD MultiValued Attributes using T-SQL? These are the messages you get when you try: Could not get the data of the row from the OLE DB provider 'ADSDSOObject'. Could not convert the data value due to reasons other than sign mismatch or overflow. Data status ret...more >>

I have a strange problem. I have a sql2000 db with a list of machine names in a column in a table. On at least one of the data import files, some of the machine names had the first two characters of the machine name left off {before it got to sql}. Because the data was still in the correct for...more >>

Hi can anyone help me with this? I have would like to be able to output some data in a very specific way and unfortunately my SQL isn't quite up to it. The database is not SQL Server so in this case only SQL can be used. I have 2 Practically Identical tables that record test results from ...more >>

In SQL Server Management Studio how do I print messages to the Messages window during development? ...more >>

I have a table with no index, When i try to create one to Check data integrity on 3 column that suppose they are unique collection (Court_ID,Case_No,Case_Date ) i failed due to duplicated values.. I know i may ignore duplicate values but i want to find it to delete it.. So How could i write ...more >>

I may just be dense, but this eludes me. I believe there should be a relative straightforward set based solution, but my procedural brain can't see it. Here is the starting point: SELECT EMPLOYEE_ID,TRANSACTION_DATE,HOURS_WORKED FROM LABOR_TICKET WHERE EMPLOYEE_ID BETWEEN '1' AND '100' AND...more >>

Could someone post a constraint I can use to validate UK postcodes and telephone numbers please? :O) Thanks. I've found a site with some good masks but can't get them to work with Microsoft SQL (http://www.regexlib.com/RETester.aspx?regexp_id=695 ) DJW ...more >>

I need to phrase an expression that gets just the filename portion of a UNC path. For example, where the NVARCHAR value contains \\server\share\dir\dir\frodo.txt I need to get just the "frodo.txt" part. So I have to find the position of the last (if any) "\" character. I can't seem to fin...more >>

Hello All, I have multiple VIEWS based on a calendar table in my database. All of them require the weeks to start on Sunday (Default SQL Server). However, one view (only) that I created requires Mon-Sun week. SET DATEFIRST 1 is not allowed in Views nor Functions. Is there any workaround...more >>

I am working on queries for a SQL database. I have much experience with Access and am trying to ween myself off of it. The query I am working on now is supposed to return a set of data like the following: LatestVersion(Ver)/VersionUpdate(Update)/Location(SheetNumber) A/aa1/3 This works fin...more >>

Hello Everybody, I am trying to export xml from my table using BCP using following command bcp "MyStoreProcedure" queryout c:\Test.xml -w -r "" -SServerName -T It does export xml to a file but file format is not good. It is not breaking xml, it looks like, it writes everything in a one ...more >>

Is there any benefit to sizing a varchar in proportion to 2^x. For example, Name varchar(64), Description varchar(256), .... Also, does it help when pulling this data over a network?...more >>

I tried attach a database (with its 2 files, mdf and log) and an error occurs, but this is not explained in BOL. Any ideas and/or solution? 9003: the Lsn passed to log scan in database contabilidad is invalid Regards, Miguel Castanuela ...more >>

Hello, I created a view in SQL Server Express 2005 that contained CASE END statements. I use this view in a .NET 2005 project, which works fine on my machine. When I deploy my project to a machine that is running SQL Server 200, I get the following error: The Query Designer does not suppor...more >>

On an XP Pro machine with a current and synchronized system time GMT (-06:00) Central Time my use of the Visual Studio 2005 Website Administration Tool (WAT) is writing users into the SQL Server DevEdition twice and recording them 6 hours into the future. The wierd time is occuring whether ...more >>

I am trying to find out how to access WMI using sp_OACreate in SQL 2000, to get a list of services from a remote machine and the service status. Does anyone have any links or code sniplets that can help point me in the right direction?...more >>

Let's say I have a table with gobbles of stuff in it, has a clustered PK as well as some other indexes, but I want to get a random sampling of records. I know I can say SELECT TOP 1000 or something, but that's not a random sampling of the whole table. Alternatively, what else might work he...more >>

I am importing a query to a .txt file and for some reason my file is being written double spaced! Is there a parameter I am overlooking that will control this?? I do not know how long my lines will be so I am setting them to be arbitrarily long so that I know that the data will not force a car...more >>

Hello, Thank you for taking time to read my question. I have somewhat a similar problem like this person in devarticles forum - http://shorterlink.com/?NY7F6G In my case, I have some cities, and every city will have Status column with values 1/2/3/4 like this: NYC 1 11/4/2005 NYC ...more >>

I need to pass the following parameters to a udf . Starting Date, Ending Date, Ending Time, Total Rent If these dates are not in the same month I need to know the number of days for each month as well as allocating the total rent betweeen the months. How do I do this in a udf so I can ...more >>

I have a simple Service Broker model in place that sends a basic message between SQL Server instances. I believe I have everything setup correctly but when i send the message i get the error: Dialog security is unavailable for this conversation because there is no security certificate boun...more >>

I have 58 GB of free space on my server and I created a database and tried to restore a 500 mb database and it tells me that it requires 85071298560. The database i am restoring is quite small and I have space. I dont understand whats the problem. ...more >>

Is there any news group for SQL Server 2005 on this server (msnews.microsoft.com) ? Thank you, Smith ...more >>

I have the following stored proc CREATE PROCEDURE [dbo].[GetPartHistory] @PartID int AS DECLARE @tmpStepData table(Station_Name varchar(20), Step int, Step_Description varchar(200), Station_Step_Description varchar(200), Result varchar(100), Operator_Name varchar(50), Result_Date date...more >>

Hey everyone, I am rather new to this, but I'm making way and learning a great deal. However, after much research I've become lost on a topic and I'm hoping you can help me out. I have the following query: SELECT Title, [Date], Number FROM ( SELECT 'OptOuts' [Title], CONVERT(varcha...more >>

I'm trying to copy some files around before doing a restore. But the the job step keeps falling over because if can't find the path. I've tried UDC and the local path but it won't have it either way. Can anyone help? all the best Spike...more >>

In an inventory database, I have a table that contains StockMovements (deliveries and credits). There is a column called OriginalQuantity, which is positive for deliveries and negative for credits. What I need to do is populate the AdjustedQuantity column. So if you had a delivery of 5 and ...more >>

I have a “users” table and will synchronize it with the SQL Server 2000 users. I have created a stored procedure to insert users in the table that also create the user in the database with the sp_addlogin system procedure. Now I want to create a trigger for inserts on the table that do t...more >>

Hello, I want to use the new SQL 2005 operator Pivot in a stored procedure. The pivot operator requires a parameter named <pivot_clause> ::= ( aggregate_function ( value_column ) FOR pivot_column IN ( <column_list> ) ) The <column_list> is the list of column names...more >>

I have some DML statements that can cause some errors. I want to handle them gracefully and go ahead with the rest of the SQL statement. What is the best approach for this? Assuming that in a loop I am doing some delete that can cause some foreign key violation. Delete from MasterTable --Th...more >>

Dear all, What is the way under sql to extract the month from a date and then defined a querry whcih fetch only data based on that particular month ? thaks for your help regards...more >>

I'm new to XML, and have been digging around the web looking for answers, but to no avail. Hope you guys can help. I'd like to create an XML output like this: <Author> <au_id>409-56-7008</au_id> <au_lname>Bennet</au_lname> <au_fname>Abraham</au_fname> </Author> However, all the e...more >>

Hello, I would like to fetch rows from a table. I want first to fetch the first ligne then the next up to the last row of a table. I know we can do that with a cursor and Fetch next statement but how can we do that(extract rows) in a another way??? I have a trigger after update on tabl...more >>

Hello, I want to insert set of rows IN THE SAME TIME in a table. Like this: insert into tableA select * from tableB tableB contains 10rows. In this case(insert into ... select...from...) the trigger or the body of trigger is called once or for each row???? thanks ...more >>

Hello, I want to do a backup and restore operations of my base, using SQLCMD.exe. For that, I use backup database mybase to disk='File' Then, I try to do a backup of transact log but like this: backup log mybase to disk='File' But I have this message : Cannot do a backup log on databa...more >>

Hello, I use the statement "Bulk insert" to insert data from a file to one table on SQL Server. I do like this: bulk insert MyBase.dbo.Clients from 'D:\DataMyBase\TableClients.txt' wiht (FieldSeparator = ',', Rowseparator='\n') Table Clients(num int, name char(50), comment varchar(m...more >>

Hello, Me again..sorry about that! Here is an excerpt from my stored procdure: DECLARE @NbRecs int, @StartTime datetime, @EndTime datetime, @TableName varchar(50), @JobLog varchar(50), @DBName varchar(50) @SQL varchar(8000) SET @JobLog...more >>

I have an AFTER INSERT trigger on table A than calls a stored procedure which performs a DISABLE TRIGGER ALL on table A, does some actions then finally does an ENABLE TRIGGER ALL. This works fine normally except, when I'm stepping through the SP code, the entire IDE hangs when executing the DI...more >>

Hi, Can anyone help me with what I should be looking for with this error? (I have also tried putting single quotes around the search number in the query - with the same results) Thanks! Rich. An unhandled exception of type 'System.Exception' occurred in receivingdb.dll Addition...more >>