Dear fellows, I've got a stored procedure which receives from ASP page a dynamic range of values (array). In the sp we've put a 'plain varchar' without say to Sql Server how long it is. Does anyone know how could we do this easier? Thanks in advance and regards, ...more >>

I have a table defined with the following field names. TableID Varchar(20) CrcNbr Int RegionName Varchar(25) Sample Data: -------------- pr_mstr,11500,Test pr_mstr,11500,Trng prd_det,12000,Test prd_det,12005,Trng prd_det,12005,Prod I want to produce a report from t...more >>

I have a situation where I need to go through a table containing around 9 million rows, and filter rows that belong to a specific MessageID and that have been created X number of days after a specified start date. Because of certain peculiarities in the app, I have to use a DATEDIFF() in the ...more >>

I want to run a select query and also using that items keys get the count of the items in the inventory db, kinda like this below but does not Parse SELECT dbo.Pattern.Pattern_ID, dbo.ProductType.ProdType_ID, dbo.Manufact_Company.ManuComp_ID, XCount AS (SELECT ...more >>

Hi, I have a query reg the bulk insert. I have some data in text file with tab delimetered and using BULK INSERT to get this data into a phycical table. i have more columns in the table than in the file. Table is having 10 columns and text file is having only 8 columns. i am getting the...more >>

SELECT * FROM FUND INNER JOIN POSITION ON FUND.ACCT = POSITION.ACCT INNER JOIN SECURITY ON POSITION.SEC_ID = SECURITY.SEC_ID WHERE (FUND.MANAGER = 'smith') I get duplicate SEC_ID's with this query because Smith may be the manager of multiple funds that hold positions in the same security...more >>

When I run a view, I am getting an SQL (ODBC) error "Arithmetic overflow error converting real to data type numeric." Below is the line of code in the view that I think is causing the error (dbo.WorkerTimesheets.PayRate is a real data type and has a value of 730). Can anyone help me fix it? ...more >>

Has anyone working with the latest SQL Server 2005 beta encountered backward incompatible changes to the system tables? ...more >>

sql2k How could I use REPLACE (or something else like it) if I dont know what the value or length to REPLACE is? I will always REPLACE with the same value, so that parts not an issue. declare @Value varchar(128) set @Value = 'abc\123' This time I want "abc\" to be replaced with "test$...more >>

Hi guys, I used the following script, but sometimes I get error in the error log, Bypassing recovery for database 'Warehouse' because it is marked IN LOAD. what's wrong? use master if exists (select * from sysdevices where name = 'awT') EXEC sp_dropdevice 'awT' if not exists (sel...more >>

background: sql2k from the best practice perspective, which one is a better development solution? using Stored procedures to do most data manipulation process and pass the result back to front end (such as VB using a stored procedure call in code), or using imbeded sql in VB to manipute the d...more >>

Hi, I have this I need to be able to update the information based on custid. DECLARE @sql1 Varchar(8000) DECLARE @custid varchar (10) DECLARE @custname varchar (80) DECLARE @doc varchar(8000) SET @custid='000900' SET @sql1 = @sql1 + 'Select c.custname From dbo.tblArCust c where c.custid='...more >>

Hi, I have 2 identically defined tables that should have duplicate rows (majority). I want a way of displaying just the data that doesn't exit in either table, one table at a time for reporting purposes. I have coded this already using a 3rd table that holds all data that matches 2 tables and...more >>

Has anyone done this kind of thing ever in stored procedure or trigger ? I mean writing a cloumns with IMAGE dataType from sqlserver into local file disk? ...more >>

my network admin gives me two servers to set up for sql2k. one will be for dev/testing qa, and one will be for production (mostly reporting , not business operation type of stuff) one server is good (raid 5), and the other is very good (raid 10). the type of development will be in VB and data ...more >>

Hi. As I commented before, I'm new with all that and, with your help, I'm finishing my first SP. Now I want to wrap it inside a transaction, so if there was any error, make a rollback. If not, commit the transaction. How can I do that? -- Regards, Diego F. ...more >>

Hi What is the function that shows only the N digits after the decimal point ? For example: 1.4567 will result as 1.45 ...more >>

I'm getting an error here: select @old_id = tab_id from bdim.dbo.bdi_tabelas where tab_codigo = @tabla Am I assigning wrong the variable? -- Regards, Diego F. ...more >>

Hello: I want to make a query that return all record that don't have one valid cast, something like this: select * from table where cast(field as bigint) is valid how can I do that or some variant?. the real problem is to import some table in a dts but when I try to convert the str to big...more >>

I'm looking to get some information on the SQL server system tables. Specifically, I'd like what all the columns in the sysobjects and syscolumns tables are for. Any links?...more >>

With this clause: --- where CAST(substring(Column,1,1) as int) & 128 > 0 --- I try to find result where first bit is on. Is there a better way to write this to speed up ? Any help much appreciated. Regards. -- Fabri -Mandrake "A Poma', c'hai 'na faccia...!" -Pomata ...more >>

Hi, I have a stored Procedure I need to be able to save the data in a file , how can I do that through VB 6 DECLARE @hdoc int DECLARE @doc varchar(8000) SET @doc =' <STORE> <AUTH_KEY>XXXXX</AUTH_KEY> <REQUEST_ID> W;12345;1234512345;12345 </REQUEST_ID> <WEB_ORDER>VINET</WEB_ORDE...more >>

I have a INSTEAD OF UPDATE trigger that works great if you only update one row at a time but failes if I update more than 1 row. My trigger reads: CREATE TRIGGER [dbo].[SuppliersPricingUpdate] ON dbo.SuppliersPricing INSTEAD OF UPDATE AS BEGIN UPDATE SuppliersPricing SET SupplierID ...more >>

I have a following table and a trigger: create table sample_table ( p_row_id int identity, some_data char(40) not null, database_id decimal(18, 0), replication_id int, CONSTRAINT pk_sample_table PRIMARY KEY NONCLUSTERED (p_row_id) ) create trig...more >>

I have a table with CustomerID(int) ,File_Body(image) and some other fields.We insert zipped files into this table and File_Body contains actual Binary version of zipped files.I'd like to create a trigger that when the row is inserted into the table,the trigger writes that file into an specifi...more >>

I don't like the idea of using a cursor and then also using a while loop for each row. Is there a better way to do the same thing? The problem is that I have a table called TimePeriods that is the following: CompanyID TimePeriodID BeginDate EndDate I need a table that has each date of t...more >>

I've got numbers stored in my table as varchar which i need to cast to a datatype so that i can perform an arithmetic operation on. 34.2348905444367 45.08070345435 34.6546456354354353 43.6540697929 the problem is, I need them to be cast so that i can perform calculations on them. I nee...more >>

I am in the happy position of starting a new project from scratch. I have only VS 2003, do not want to dabble in VS 2005 Beta yet, but am keen to plan for using SQL Server Express 2005, which would be delivered to the (small) customer. Can I use 2005 Express with VS 2003 (and still maintai...more >>

Hi All, I have problem with my logs files, they are going so much especially when I import data. The best way that I found to clean the log files is: 1. Detach database 2. Delete the log file from the data folder 3. Attach database Obviously I can't do this during the day until nobody is u...more >>

I have a table I'm running a query on: SELECT DISTINCT guid, Department FROM table I only want rows with unique guid's to be returned (there are a couple rows with identical guids, and I can't fix the real problem of having multiple guids) This returns rows with distinct guids and depar...more >>

I have the following query and it works. Where the left part of the join does not have any records i'd like to return a zero. I tried the case but, .... thanks all kes select c.calId, c.calDate, c.calShortDesc, c.calDesc, c.CalHoliday, e3.evtAll, CASE e3.NS WHEN NULL then 0 else e3....more >>

I need some Microsoft Access / SQL Server 2000 help. I have a SQL Server 2000 database that is using Access as a front end (adp file). Since SQL server does not allow cycle cascading or circular reference, I am having problems. For example, suppose I have tableA, tableB, tableC, and tableD. tabl...more >>

I would like to find out how long the last execution of a scheduled task takes to run. Each job i'm looking at has only 1 task. I'm not crazy about the idea of creating a fake job step to be able to see when that step starts to know when the "real" job step ended. Is there a good way to find ...more >>

Hi all, In testing some logic to test for suspect databases, I noticed the following anomaly. To attempt to create a suspect database condition I: 1. Stopped the SQL Server 2. Renamed mytestdb.mdf to mytestdb.sav 3. Started the SQL Server The Enterprise Manager greys out the mytestdb d...more >>

Hi i am having problems trying to get top x amounts by groupings. I am trying to get a count of the top 3 revisions for each group, grouped by type, by name and the revision field sorted descending. something the equivalent off Select TOP 3 type, name, revision From agtest group by type,...more >>

A same query with a NOT LIKE statement and a wild character % returns different records result if a use N (that means that the string follow is unicode data) or not. You can try this directly on sql. Par example: select * from company where city not like N'%' select * from company wh...more >>

the objective: to load a set of records into the database (dts import text to temp table) IF the input record exists in SSCUSTOM, then update the SSCUSTOM rec and associated table(s). IF the SSCUSTOM:CUSTNO does NOT exist, this is a new CUSTOMER and we will add/append the record to the SS...more >>

A 'PRINT SUSER_SID()' query yields a binary value but how do I modify this query so that the output in the Query Analyzer is: ---------------------------------------------- SUSER_SID(): 0x010500005150000EE940207DCF4DC3B43170A32F401000 ---------------------------------------------- i=2Ee. I ...more >>

How do you write a query in Query Analyzer that extracts data from a table into a .CSV file. I need the data in that format in order to upload into an old Sybase database. Thanks...more >>

Dear all, We are transferring a table between two Sql Servers using a simple DTS. The first one is a sql200k and the destination is a sql7. The aforementioned table own a field with a 'text' as a type data. That table own 3 triggers (insert,update and delete) linked but we don't think t...more >>

Dear all, I have a table with a certain amount of data. One of the data column is named STATUS. by default all entry have a status of 5 (which means for the client application a waiting states) I need to schedule the change of this status from 5 to 1 automatically. For example I would lik...more >>

Hi all, I want to concatenate the results from a query in a string in the most performance-friendly manner. E.g. executing SELECT Code, Price FROM Inventory returns ABC, 120 EFG, 200 HIJ, 245.50 .... Instead of returning the result in the format above, I want it to look like t...more >>

I was looking on line trying to find a way to change the utc dates i a table to mountain time I found this CREATE PROCEDUR [dbo].[Convert_GMT_to_DateTime @dt_GMT as datetim A select DATEADD ( hh , (DATEDIFF ( hh , GetUTCDate(),GetDate() )) @dt_GMT G declare @dt nvarchar(20 Select ...more >>

Hi experts, I have being reading up on index, in particular the difference between a cluster index and non-cluster index, and how they should be used. However the more I read the more confused I am. Basically if anyone can advise me on this question: - Which kind of index should I use for...more >>

I wanna import data from foxpro to SQL Server 2000. I use ADO component and to be looping. but this way is very slow. it can take 8 minutes for 32500 record at PentiumIII. Can you give me solutions so I able to import <1 minute? ...more >>

I have VS2005 team suit beta2 installed. when I try to install sqlserver2005 july CTP, I am prompted to uninstall the dotnet framework. My question is after sqlserver2005 is installed, can vs2005 still be functioning? thanks in advance for your reply. -- Denys MCSD for .Net, MCDBA...more >>

Hello there I have Huge procedure that add at the end values to Constant Table As part of processing i'm using Temporary tables in side of the store procedure If i run another store procedure or function from the Main store procedure, is there a way that these store procedures will be abl...more >>

I know it's not the proper place for this question, but thank you if you can tell me the answer or instruct me where I shall post this question. -- Denys MCSD for .Net, MCDBA...more >>

hi maybe someone has a script which recreates the indexses on db? i think the best way is to use CREATE unique INDEX WITH DROP_EXISTING and the procedure sp_MSforeachtable the problem is that i don't know how to "join" these 2 options as every table has lots of indexses... curre...more >>

I am once again faced with what must be a very common design scenario, but have for a long time been on the fence as to which way to approach it. I don't know what the scenario / pattern is "commonly" called, so had trouble search for opinions on the best solution. The situation is where th...more >>

Hi there, I have created a DTS that copies data from a table in SQL Server to a table with the same structure, but in Oracle. Lets suppose I have 100 rows in the origin table. If the DTS fails in he middle of the operation, supposing the row 55 has an error, the previous rows are copied ...more >>