I am trying to fire a query having the WHERE clause on Date Time field. But with Latvian Regional settings the date value comes as 'yyyy.mm.dd.'. The last dot (.) in the string creates problem. Does anyone knows how to tackle this?...more >>

Hi all, I want to return text datatype from stored procedure. So what i did is used varbinary as output paramter to stored procedure. My Store procedure is CREATE proc test_text @outtext varbinary(16) output AS begin declare @ptr varbinary(16) select @ptr = TEXTPTR(addr) from emp ...more >>

Hi All, I have a database with about 250 stored procedures. I need to give some users execute permissions on all of these procedures. Can anyone tell me if there is an easy way to select a group and grant execute permissions to it for all 250? I basically need the group to be able to exe...more >>

I am writing Stored Procedures on our SQL 2005 server that will link with data from an external SQL 2000 server. I have the linked server set up properly, and I have the Stored Procedures working properly. My problem is that to get this to work I am hardcoding the server.database names. I ...more >>

I have a stored procedure which goes like the code below but with real database field names and so forth. It all goes swimmingly until it reaches the DEALLOCATE statement to deallocate the cursor where it stops. I know this because that's what Activity Monitor is telling me. It's a SQL2k dat...more >>

Given 3 INSERT statements in a stored procedure where: The first INSERT runs without errors. The second gives an error like "Error converting data type varchar to float.". The third procedure has no errors and could be executed. Is it possible to ignore the error in the second statement and ...more >>

Ha all, As I understand there should be no difference in SQL 2000 Developer edition and the SQL 2000 Enterprise edition (except for the License). On my workstation (XP) I have the Developer one and on the production server (W2K3) the Enterprise one. Both are version 8.00.2039 with SP4 applie...more >>

I am attempting to use SQL Server Mobile 2005 in a Windows Forms application. I have used the wizards to define a table structure and the connections test was successful. I have attached a DataGridView to the table. I try to add rows to the table from another dataset. The 24,500 rows added...more >>

I have a list of values Customer Item ---------- ----- 1 A 1 C 2 A 2 B 3 A 3 C 4 A And would like to end up with A B C Count -- -- -- ----- x ...more >>

Just starting a new thread here because the original one may have gotten lost in the myriad of threads :) Is it possible to count the total bytes of actual data in a database without reindexing or using "sp_spaceused" ? Cheers, Peter -- fiddlewidawiddum...more >>

Hello all, I have two tables, table1: Customers, table2: Orders. In table 1 as the name says I have a list of all of our customers, table 2 cotains the order all the orders that each customer has places. What I need to do is go thru everyrecord in the customer table and find the last order r...more >>

Hi Friends, SQL2005 I am trying to insert into a table with identity column: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[BulkMailExportJobLog]( [jobID] [int] IDENTITY(1,1) NOT NULL, [BulkMailTypeID] [int] NOT NULL, [startdate] [datet...more >>

I have a stored procedure which returns some xml data using the FOR XML AUTO, TYPE, BINARY BASE64 syntax. One of the fields is an image field, and gets sent back correctly. I have another procedure that is trying to insert that data back into a table, but when I perform the insert, it does...more >>

Hi, I have a statement like that, but every time that i run this statement it shows records that are in that specific cursor, How it is possible to do not display that records.Is there any thing like "set nocount on" or "echo off" while @@fetch_status = 0 begin fetch next from acur...more >>

I have 2 SQL 2005 servers in the following configuration: - SQL2005-1 includes a linked server to SQL2005-2 (using SQL Native Client provider) - SQL2005-2 has a database named 'filesearch' (with stored procedure named 'app_filesearch') and an index linked server named 'qasite1' From SQL20...more >>

I want to create a package that will use the view of one database to insert data into the table of another database. I would normally empty the destination table, then insert the entire data from the source to the destination. Is there any way to simply update the destination with the data ...more >>

Hi, If I have Chinese data in SQLServer database, do you know how the sql should be? I tried the following: SELECT * FROM Customer WHERE NameChinese='$B@.gP3$L#(B' Please help. ...more >>

Hi, I've developed a small .net application library. Now I am trying to call the library from within a CLR stored proc. I've managed to make the stored proc working OK. However,I can't call the my ..NET component. To do this I need access to library Microsoft.VisualStudio.SourceSafe.Int...more >>

I ran the SQL script and looking at the execution plan. I am not very clear what to see and diagnose for better performance. Please give me some suggestion/advise on this. Thanks, Tim ...more >>

What is the best way to select a date field that returns 2007-02-28 00:00:00.000 as formatted as mmddyyyy or mmddyy? Thanks in advance....more >>

I'm attempted to run an installation of SQL Express 2005 from the command prompt. I've downloaded the redistributable SQLEXPR32.EXE and now want to get my hands on setup.exe, template.ini, etc.. that I will run from the command prompt. I can see them being created in a temporary folder when I ...more >>

A SQL Server 2005 database has 4 tables - 'Products', 'OrderCart', 'CustomerDetails' & 'Basket'. The 'Products' table has the following columns: ProductID varchar(50) PRIMARY KEY ProductName varchar(50) Description varchar(200) UnitPrice money The 'Basket' table contains these columns: ...more >>

can someone tell me what table or stored procedure that the Job Activity tool uses to show the activity for the jobs on the db server? I'm asking because I'm creating a web form that list all db servers on the network and all the jobs under SQL Server Agent, and I need to show the status of th...more >>

Hi, I recently removed a considerable number of rows from my MS SQL Server database because I was getting quota warnings from my ISP. I'm aware that running the stored procedure: sp_spaceused gives me the total amount of space that is used by the database and index. The problem is th...more >>

It's only a couple of weeks that I'm using Sql Server, and I have problems with the database integrity. This is our database situation : Dossier : PK SysId Config : PK SysId FK DossierSysId ConfigDescription : PK SysId FK ConfigSysId...more >>

Hi, I encounter a problem when I attempt to open a recordset against a SQL Server 2005 connection (Trusted connection) I run the following VBA code from an Access adp project with reference to Microsoft ActiveX DataObjects 2.7 Library. Dim rst As ADODB.Recordset, SQLStatementDetail As S...more >>

Hello I have a script that copies all eventlogs from servers to my sql2005. Now I want to extract some info. I want to get the Printername from the message column (varchar 1024). I use this query: select charindex(' was printed on ',Message)+ 16 as startpos, (charindex(' via port ', Messa...more >>

Does anyone know why all of a sudden a udf is slower than just running the same code as a script? My problem is I have some code that when run as a script it take less than 1 second, but when I wrap the same code into a udf it take atleast 4 seconds. The udf is a scalar function and the server...more >>

do you have to install SP1 before SP2? We installed a new cluster and went straight to SP2 now auto updates is telling me to install SP1?! ...more >>

HI all, I have been given a myssql script file, that creates tables and does inserts. I need to run thi on our server, a rudementry import. Is there a quick and easier way of correcting the syntax so that this sql file could be run in MSSQL Thanks Robert here's a small example of the...more >>

Hi all, I have the following query which works as expected select * from division where cast(divkey AS varchar(5)) in ('1327','1342','1411') or divkey = 1345 ORDER BY depth desc only problem is that the in predicate should look like '|1327|1342|1411|', which is a value in a particular fiel...more >>

sql server 2005 sp1 I need to retreive the definition of routines stored in syscoments's text column and then ALTER the routine in an automataed way. The problem I face is that definition of routines come out in a sinle line which poses the following issue; the first commnet in the routine...more >>

I have a server/client application that has multiple users with different access rights to the data in a database. Access is granted on a column level. First I was planning on writing my own autorization/ authentication layer. Then I was told that the right way to do this is by creating views th...more >>

Do you know of any applications such as RedGate's SQL Compare that are less expensive or free? <%= Clinton Gallagher NET csgallagher AT metromilwaukee.com URL http://clintongallagher.metromilwaukee.com/ MAP http://wikimapia.org/#y=43038073&x=-88043838&z=17&l=0&m=...more >>

Hi all, I have a SQL table which needs to act as a "queue". Multiple threads/connections will be requesting the next record from the table. What is the best way to structure the table so that unique records dequeued to users (i.e. 2 users never have access to the same record). Would run...more >>

I am having a conversion problem. See below: Code snippet from encryption routine: case @tvByteUnencrypted when 'a' then char(157) when 'A' then char(144) Code snippet from decryption routine: case @tvByteUnencrypted when char(157) then 'a' when char(144) then 'A' When executing ...more >>

I have a table that keps tracks of employees punch ins and outs. [code] CREATE TABLE [EmployeePunch] ( [scalldate] [varchar] (20) NULL , [sEmployeeId] [varchar] (20) NULL , [TotalHr] [numeric] (10,4) NULL , [sLogin] [varchar] (20) NULL , [sLogout] [varchar] (20) NULL ) INSERT [Em...more >>

Hi all, I'm trying to construct a sql statement in my stored procedure. My application calls the stored procedure and will pass it a concatenated string containing guids.... These should 'not' be returned in the results of the query, the problem I'm having is the conversion between the ...more >>

I have a sophos (Antivirus) database installed by the Sophos software. The databases are flagged as suspect I do not seem to be able to run a "dbcc checkdb" .... the database cannot be accessed because it is suspect so I can't fix the corruption ??? It must be a 2000 MSDE install I guess...more >>

we recently migrated the database. we have a huge tables rows and some of them have many duplicate records Is there any query to identify duplicate rows? thanks...more >>

I have SQL 2000 and use Enterprise Manager - no problem! I just gained access to SQL 2005 for testing. Cannot connect to SQL 2005 With EM. I installed SQL Server Management Studio Express, connected, but I don't see where I can create DTS packages. I don't see any of the Wizards that were...more >>

Hi, I have a table with two non-clustered indexes. by default, the query use the first index. How do I dictate the query to use the other index? Thanks ...more >>

Hello, I have a class in my ASP.NET project. I made this class Serializable. I now need to Serialize it and sent it to an SQL 2005 database table. Does anyone knows such examples? And what data type should I use in SQL 2005 to hold this? Image? Thanks, Miguel ...more >>

Hi. I need to parse/replace/manipulate strings > 8000 characters. I understand you really can't accomplish this w/o breaking up the string into smaller pieces, but all the references i found in this group are for MSSQL 2000 or older; are there any new ways to deal with this in 2005? ...more >>

Hello, I am having a problem with a user procedure in an SQL Server 2000 DB. The procedure should update some columns in a table for the record that matches the WHERE statement. When I run the proc I just get the message '0 records affected'. The proc is run from an Access 2000 DB by using ...more >>

After upgrading from sql server 2000, I read that i need to update statistics and rebuild indexes. Are there commands that do this for all tables? Are there anything that I need to do after the upgrade that I am forgetting? Thanks ...more >>

I have searched BOL & must not be using the correct verbage.. How can I search a database for all indexes in a database? I am confident it is a system sp or some kind of call to the system tables.. Thanks in advance, Claudia....more >>

Is it possible to write sql that is able to work on MS SQL 2005, Postgres and MySQL without translation or modification? ...more >>

Hello, I'm testing service broker on two machines in the same domain. I'm using windows authentication. When I begin a dialog on serverA the message is immediately put into the sys.transmission_queue with a blank transmission_status, and is_conversation_error = 0. Also, I specified the br...more >>

I'm trying to create an end-user style data dictionary. In the Table Designer you can enter a description for a column. How can you get it back? I can get extended properties for tables but can you do the same for columns, (I don't think columns have extended properties, but I've been wrong...more >>