Hi! I have a varchar field called 'error_code' that looks like this ,C101,C102 etc It can also look like C100,C101,C103 depending on the validation errors. I need to remove the , when is the firt character. I tried using ltrim but it does nothing. I also tried using left(error_...more >>

userid date 1 2006/01/01 1 2006/04/04 2 2006/01/01 3 2006/01/03 3 2006/01/04 3 2006/01/05 how to retreive the latest date (one record only for one user) for each user e.g 1 2006/04/04 2 2006/01/01 3 2006/01/05 ...more >>

Is it possible , that .net app uses different executin plan hwile query analyzer sues different plan? ...more >>

Hello everyone, I'm using MSSQL 2005. I'm trying to perform an insert into a table that expects int, int, int. I'm coming from a table with int, nvarchar, nvarchar. The tables are survey data, uniqueid, questionid | questionlabel, response. I'm trying to transfer data from our raw table...more >>

HI all, How can I change the date format the the my server is expecting Cuurenty I think it sits at americn format, I would like ti to expect British format, DMY, so that when an insert into table values('24/02/2006') would be correct and acepted as 24 Feb 2006, without any date conversions ...more >>

I would like to know if one column is numeric or not. If I use SELECT isNumeric(column) from table I get false even if column is integer type(if only one of them is null). The problem is that column allows nulls. I can use : SELECT isNumeric(column) from table where column is not N...more >>

Hi All I have a sales table which is an aggregation of sales by day. I am having trouble writing a query that will return the current sales as well as the sales for the same week and day of the previous year. Below is the schema of the table and some sample data as well as the result that...more >>

Hey guys I have a load of data that is stored and timestamped across 3 tables. I need to retrive this data from these tables so tha i can iterate through in date order. Any idea how to do this? To explain further imagine this scenario: Table1: User Pass TimeStamp Table2: UserAct...more >>

Hi I am new to this group. I have two tables tblActivities and tblTourActivities tblActivities has the follwing fiels ID (primary key) Name Location Desc ------------------- tblTourActivities has the following fields ID (Primary Key) Tour Activity (I used lookup of ms access to...more >>

Hello All From an ASP page, I upload a CSV file into a SQL SErver staging table. The data loaded into the table needs to be validated for proper data types before migrating it into the final table. What is the best way to do it? I need to print an error listing on the website for data element ...more >>

I have dates in my database that are formatted as 12/12/2096 I need to change the date from 2096 to 1996 all of my dates are in the year 2000 and I need them in 1900's...more >>

I have the following query that currently doesn't work the way I want it to because the column processdate is formatted as mm/dd/yyyy hh:mm:ss AM/PM, and to get the grouping to work, I need to only work with the date part of the field, not the time. How do I ammend this query to pull only the ...more >>

I have a small text file that I want to import into a SQL Server 2005 table (one time only). What's the quickest way to accomplish this? I'm taking my first look at SSIS and it appears that I have to create a project and save it and all that. Am I missing something? I was hoping to find som...more >>

We just upsized from MS Access to SQL server 2005 using the Upsizizng Wizard. Everything appeared fine. I get correct results from al access queries, can look at the tables, create new queries in Access, run existing queries, etc. However, When I try to connect using my ASP page, I get an err...more >>

Does anyone know how to write a query that will find those values which could be converted into an integer? For example say field1 contains the following data: 123456789 abcde1234 Therefore, the query would return 123456789....more >>

select ...., count(level = 1) as expr1 .... ..... where testbankid = 12 basically want the amount of questions with a level of 1 for testbank X how do i write the expression in the count function? the rest I know or cant figure out pretty well....more >>

Hi, I have a web application that using SQL 2005 asa backend. I have to store XML documents coming from a third party application in the db. I am looking for a good solution to store the XML document because I do not want to parse it into a table. Is the best option to store it as a stri...more >>

I am trying to put together a query where I group data representing the last 12 months. My question is this. I have a couple of months where there were no records generated. I want all 12 months to show up and a zero for the data. How can I get all 12 months listed? This seems like a clas...more >>

I need to report on all replications defined in our site (all instances). I am reading the SQL2005 distribution database tables to fetch this info: Select * from distribution.dbo.MSpublications Select * from distribution.dbo.MSsubscriptions This gives me most of what I need to know, but for s...more >>

Need help with the logic of a stored procedure (SQL Server 2000 / ASP). The ASP script calls a stored procedure called WEB, which calls several stored procedures. The area that I'm having problems with is a call to a sp that inserts a buyers first name, last name, phone number and ssn. If a seco...more >>

How do I make this work? declare @status char(10) declare @str char(100) select @str = 'master..xp_servicecontrol ''QueryState'', ''SQLServerAgent'' ' set @status = EXEC(@str) print @status ...more >>

We are going to buy some Red Gate tools: SQL Compare SQL Data Compare SQL Log Rescue (maybe) SQL Dependency Tracker (maybe) Are there any better alternatives? Is anybody actually using Log Rescue and Dependency Tracker? I would appreciate any feedback. ...more >>

I would like to know if it is possible (without using the enterprise manager tools) to write the syntax for adding a new column and specify where the column should be. For example, consider this two column tables: CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, Co...more >>

I've been trying to obtain a list of SQL users with a specific role, for example 'datawriter' for all databases on the server. I have been able to obtain this information for just the one database, but does anyone know of a script that can pull this info from all databases? ...more >>

I'm writing a white paper on Database Design ROI Of course we all believe that good database design is important, but to quantify that assertion to upper management is a different question. How would you justify the time spent on improving the database design? It seems the ROI segments int...more >>

Hello I'm trying to update dynamically a few columns of a table, dynamically because I don't know in the beggining the number and name of the columns I try to serialize the name. but it isn't working I only get a bunch of nulls, can anyone help me. Thanks for your time and effort. ...more >>

I am referring to an old project for an application using Access 2003 and I came across this code in VBA. In the following subn routine, I do not understand the SQL statement "SELECT * FROM qryASPFindApplRec WHERE False". The sun routine is Private Sub Clear_Click() 'Clear controls in form he...more >>

Hi, I have been trying to write a procedure (in sql 2k) using sample code from CELKO that will return the statistical median value for each date. (once this works I'd like to add another layer of grouping and have the median for each group on each date, which I hope won't be a problem) ...more >>

SQL 2K I have a job with 8 steps, the last step is currently running over 15 hours. First 7 steps are very critical for reporting and we are planning to divide this one job into two different jobs. I need the first job (7 steps) to always start at a specific time and the second job (Ste...more >>

A fellow developer is having the following problem - We are upgrading an existing application for a client and he has written a stored procedure to update a table in an existing database in SQL Server 2000. If he runs the stored proc through QA with the 'sa' user account the table is being ...more >>

I have created this class in VB Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Data.SqlClient Imports System.Runtime.InteropServices Imports System.IO Imports System.Security.Permissions 'The Par...more >>

I have a data table that looks something like this, that tracks a history of item movement: item date location 1 7/20/2006 a 1 7/21/2006 b 1 7/22/2006 d 2 7/20/2006 e 2 7/21/2006 b So the current locations would look like this: item ...more >>

hello, i'm having some difficulty with a query. i have a table with 50000 records. when i group by field1 my record set returned is 2500 records. when i group by field2 my record set returned is 5 records. what i'd like is a break down of how many of the 2500 unique field1 records belong t...more >>

I am trying to write a complex group by type query and know what I want to achieve but am having trouble writing the correct sql to make it happen. Below is an example of what I am trying to do. If anyone can help me I would really appreciate it. Cheers Stephen Create table #TempAudit (...more >>

Let me preface this by saying that am relatively new to SQL. I have a database that is updated every10 to 15 seconds with broadcasts from our customer. These broadcasts come from 2 distinct points in their operation. The first point - 39 - tells me what to build and ship to them. The second...more >>

Looking for syntax or examples to do the following: From outside of SQL Server - Run a view within a SQL Server , convert result set to Excel, and email spreadsheet to users ...more >>

Hi All, I have a table that needs to be populated, in the following form: Attributes Product1 Product2 Product3....... Attribute1 Attribute2 Attribute 3 ..... The source of info for the above table is the table below: Products Attribute1 Attribute2 Attribute3 ..... Product1...more >>

Hi I'm trying to update a local table based on matching values in a linked server table. First I tried my usual JOIN method (don't like subqueries if I can avoid them): UPDATE LocalTable SET ReflectedToRemote=1 FROM LocalTable INNER JOIN [Linked Server].[db name].dbo.[tblname] RemoteT...more >>

Hello - I am a SQL Server 2000 programmer and am currently in a situation where there is no DBA -- guess I'll have to assume that function. In that regard, I am in need of further education. Can anyone recommend some good DBA books or courses? -- Sandy...more >>

Can you finish this query so that IsInPrimaryKey will return 0 or 1 (true/false). I simply want a query that will tell me if a column is involved in a primary key. This query is for Northwind database. I picked the Order Details table because it has two columns as primarykey. SELECT sc....more >>

Hi, I'd like to do the following queries: SELECT * FROM MYTABLE WHERE MYFIELD LIKE = '%+ @Param + %' But I also want to ignore brakets so that if I have a record like : 'some value (more stuff)' I would like it to be returned if in the front end the user typed 'some value more stuff' . Ho...more >>

I have a form, frmtest. On frmtest are 4 textboxes text16 and text19 are months text21 and text 23 are years I want the sql code to find the records inbetween the dates I have. This works, but doesn't work out inbetween, (IE it doesn't count the month and the year as one) SELECT *...more >>

Hello! I have checked some queries in the Tuning Advisor of SQL Server 2005 and in some recommendations I shall create a nonclustered index containing the search column and the primary key column. CREATE NONCLUSTERED INDEX [MyIndex] ON [MyTable] ( [SearchField] ASC, [ID] ASC ) GO...more >>

With the Enterprise Manager a "create object command" for a table can be created by rightclicking onto the table -> all tasks -> generate SQL Scripts. Is it possible to create the same object using the Query Analyzer? Is there a command to create the script? Thx for your help. Marcel ...more >>

Hello, I've a table with an interger column that accept NULL values. When records are inserted, this column is not specified. I would like to assign a "record number" to each row through this column starting with 1 to each record with a single UPDATE command. For instance, if the table contain...more >>

I have problem i have two table identical to each other like Table A And Table B. both has 50 fields. i want to insert data from A to B. I dont have unique ID to connect these two tables for some reason. i want to insert data but first check that this record is already present in B if it is...more >>

Haven't seen an 8624 in a while, but I got one trying some ideas for using PIVOT twice in one query to aggregate on more than one column. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=170390 -- Fails with Error 8624 on version 9.00.2047.00 SELECT * FROM ( ...more >>

Hi everyone, I've got a question about the performance of triggers. All my tables in my database keep two specific fields, an update counter and an updatedatetime. Both are used for some kind of version control of the items in the database. In order to easily keep track of changes I defined ...more >>

Hi all. I have a column in a table that is of type datetime. The data within this column looks like this: 22/04/2004 8:52:32 AM 22/04/2004 8:52:32 AM I need to join a nvarchar column to this column to make a unique column. I used the following sql: dbo.Vic_Harness_19606.RACE_CODE + ...more >>

Hello, I have created my own type in .NET assembly: namespace DemoSQLServer { [Serializable] [StructLayout(LayoutKind.Sequential)] [SqlUserDefinedType(Format.Native, Name="Sex")] public class Sex : INullable, IBinarySerialize { SqlBoolean sex; public...more >>

Hey guys Are there any ways or any common procedures on storing secure data. I have data such as credit card numbers etc that require being stored in a hashed form, and some other data that must be heavily encrypted. Thanks ...more >>

Hi Is it possible to state when a query returns no records? e.g USE pubs SELECT au_id FROM authors WHERE city = 'x' Is there anyway I can return a message to be used in Crystal Reports, that states there are no records retrieved? Kind Regards Ricky (SQL2K/WIN2K) ...more >>

Hello My Table T1 is having attendance details of the employee . there are everday attended hours of the employee.For the complete one week the cumulative hours are to be found. Here is the explanation for what i want cumulative hours for monday = attended hrs cumulative hours for tuesday...more >>

Hello there I ran store procedure. In the store procedure an error occur and the system is in deadlock how can i kill the transaction without stopping the server? ...more >>

Some of my sps are showing high reads if executed from a >net application. Like if sp is executed from .net app it shows some wayy around 5000000 reads and does not complete even in 10 min. Same sp , same time on th e same server executes in 10 sec using sql query analyzer, and shows 30000 read...more >>