Hi! I have just completed a database consolidation which combined the data of 2 databases same schema. I did it in such a way that the effort was quite manual. Steps like: 1. per table resequence the identity columns on the database tables to be inserted. 2. Then import the tables us...more >>

We have our crypto implementation written in .NET and made a COM Wrapper around it to use in SQL Server using sp_OACreate. Due to excessive nature of usage this object in SQL Server after certain point of time we get error while creating OLE object in SQL Server with message - Not enough stora...more >>

I need to get sequence number of rows in a query. Something like this 1 sadfds 10 2 dlfdfdf 12 3 sdfsdfsd 100 How can I do it? ...more >>

In SQL server stored procedures can you return back to program that executed the first procedure from the third nested one without falling back to procedure 2 and procedure 1? I am new to SQL Server Procedures and I could not find this in the manuals if it can be done. PROGRAM -> Procedure 1 ...more >>

I have a studnet table. Three Fields. The fields are SID,CID,MARKS respectively. SID stands for students unique id., CID stands for unique subject id. and MARKS stands for the number obtained by a student in that particular subject. In the table SID and CID makes a composite key. i.e. both the ...more >>

H I wish to run SQL Server on Linux. As I know without any 3rd party tools I cannot run. We use WINE for windows applications on Linux. Like WINE any tool to solve my problem Thanks in advance -Ramprasad...more >>

Hello I was wondering if you guys could help me with this easy one. i need to count the number of "unique" values in this: Foo 2 4 3 2 4 should return 3 TIA /Lasse ...more >>

My MSSQL database is hosted by a company and I only have limited space alloted on the server. How can I know the available server space remaining, using Enterprise Manager? ...more >>

Anyone know of a way/logic to FTP files to and from the SQL Server itself? ...more >>

Hi All I have a database which contains around 200 tables. Each table has a column called CreationDate. I originally used a smalldatetime for this, but now I want to convert all of them to a DateTime as I need the extra precision. Does anyone know a clever way of looping through the tables ...more >>

I am stuck at this isnull function. I am using OLEDB jet 4.0 access Access tables. I execute SQL statement in VB.net. I am trying to do this in code which I can do in T-SQL. select isnull(total, 0) from sometable It doesn't work. In OLEDB jet 4.0 isnull(exp) return 0/-1 which is to...more >>

I am running a stored procedure that is restoring transaction logs. Once in awhile I get the following error message: FILE = 14 [SQLSTATE 01000] (Message 0) The log in this backup set begins at LSN 38959000000075400001, which is too late to apply to the database. An ...more >>

Hello. Which is more efficient when selecting records from a table in a case-sensitive database: 1) SELECT * FROM tblQuotes WHERE UPPER(LastName) LIKE 'B%' OR 2) SELECT * FROM tblQuotes WHERE LastName COLLATE SQL_Latin1_General_CP1_CI_AS LIKE 'B%' Thanks in advance, Mike ...more >>

hi all, I have calc field in a view f3 = cast(f1/f2 as decimal(19,8)) The result should be like this x.12345698 but instead the result is 1.12345600 If i copy a number from f1 and f2 select 123.00 / 1253.256458799525 I got what I need. Any ideas why last 2 digits become 00 thanks ...more >>

Hi, I have a very simple problem: I'm trying something like this in my stored proc: select @tabCode = ltrim(rtrim(@tabCode)) + '-%' so if the @tabCode is 'MS' I want it to be 'MS-%' but the above statement results in only 'MS-' How do I add that '%' character? thanks...more >>

Hello I have this: CREATE TABLE #Players ( Player CHAR(1) NOT NULL ) CREATE TABLE #Scores ( Player CHAR(1) NOT NULL, Score int NOT NULL, Season smallint NOT NULL ) INSERT INTO #Players SELECT 'A' INSERT INTO #Players SELECT 'B' INSERT INTO #Players SELECT 'C' INSERT INTO #...more >>

How many of you DBA's or database developers keep a current data dictionaries of your databases? Do you have any pros or cons regarding having or not having them? Thanks, Richard ...more >>

Hi there I have a problem when I am using an indexed view I have a stored procedure that accepts an XML doc as a parameter. I then do some validation and insert the XML into a table called TW20001. What I had to do then, was to create a view based on a link with another table that summar...more >>

Hi, can you have nested cursors within a stored procedure? If so then how does @@fetch_status work with an inner While @@fetch_status and and outer While @@fetch_status? Thanks, Kevin. ...more >>

How do I write a join statement accross databases. I need to write a stored procedure in one of the databases that accesses a table in another database on the same server. Here is my best guess which doesn't work but should give you an idea of what I am trying to do: select a.field1, b.field...more >>

sql2k sp3 Ive got a proc that inserts/ updates some data on 2 tables. This is all done inside a transaction so data will either make it into both tables or neither one. Another proc selects from one of them just about the same time. Whenever the select runs while the data is being inserte...more >>

Hi, I am having a problem with my VB app calling a stored procedure. When I call the sp in QA, it will take next to no time (I can run it 2000 times in less than a second) When I call the sp from VB, it is taking a very long time. I have multiple databases with identical schemas and this ...more >>

When I run this query from the SQL Query Analyzer SELECT TOP 1 column_A FROM TABLE_X WHERE order by column_B DESC I get the following error: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '1'. I'm running SQL Query Analyzer Version SQL 8.00.194 Do I need to...more >>

Hello, I've some records in a table that come back largely as a list of numbers but there are one or two exceptions which mean the field needs to be a varchar. I want to pull the records out in numeric order so that 90% of the data is correct (looks good to the eye). I'm not to bothered abo...more >>

How to import/export stored procedures in SQL Server?? I used DTS, but it only transfers all tables, excluding views, stored procedures, etc... please help. thanks! ...more >>

i am an Oracle user now transfering to Sql server. a problem confusing me now is that when i create sql server function, i have to use dbo.function_name to execute it. but i would rather use function_name to invoke this function directly. if someone experienced this problem, please inform me of ...more >>

Using the pubs database I have created a query as select datepart(month,ord_date) as 'Month',Sum(qty) As 'Sales' from sale where ord_date BETWEEN '01/01/1993' AND '12/31/1993 group by datepart(month,ord_date which produces the results Month Sale 2 3 3 2 5 16 10 1 12 1 However, w...more >>

I have couple of Select statments i need to join together in to one output. First select has, for exemple, columns 1, 2, 3 Second select has columns 4, 5, 6 When i get the result it the header should look like this 1, 2, 3, 4, 5, 6 Is this possible? ...more >>

Can you bring back the data from the following select statement into a dataset. And if you can how would you access it. Naturally on a from you would not want to duplicate the data in the first 7 columns and would want to show the last column as three lines. thanks grs /* select_address...more >>

Hi all, I'm not certain, but I think this can be done in TSQL. I've got a table that I had not added a foreign key to when building and now need to get it populated. I can query the info necessary to determine which records should have which data but don't know the syntax to then assign the ...more >>

Perf Mon shows 2 active transactions under _Total but none for any of the databases including master, model, and tempdb. This "active" transactions have been there for hours, yet process info shows nothing. Is there a way I can track down the source of these zombie transactions and kill them? ...more >>

I have a program which I am using to load data into a SQL Server database, but because the data isn't all keyed, and works on "record A has keys used by B, C and D", I have ruled out BCP, Bulk Insert or DTS. Am I better using Stored Procedures for each insert than creating? Also, the process...more >>

Hello, I have the following table: CREATE TABLE SEARCH_REQUEST ( REF_ID int identity(1,1) primary key, DATE_CREATED datetime DEFAULT GetDate(), SEARCH_1 varchar(50), SEARCH_2 varchar(50), SEARCH_3 varchar(50), SEARCH_4 varchar(50), SEARCH_5 varchar(50) ) And I need to move it to ...more >>

Hi All, I would like to back up my database (.mdb, .dat) in MSSQL using Enterprise Manager. How could I do that? Also, for data restoration or rebuild, would this backup data be enough or do I need the backup of complete file structure including Index key tables? Appreciate your reply. Tom ...more >>

Hello: I have inherited a gigantic mess that I need to quickly sort through, including a DB with over 3000 SProcs. Is there a relatively painless way (without manually scanning) to identify the stored procedures for a database to identify which are query-only (read-only), and which perform ...more >>

Hi all, I have run into a little problem as such using this xp. It works fine when I specify a server name, no problems there, but we have a clustered environment, 2 servers in the cluster, and we were hoping we would be able to specify the @server variable to equal the cluster name, however...more >>

Tengo un problema y no se como solucionarlo. Tengo que subir un archivo de texto a una base de datos mediente Bulk Insert, se como programarlo pero la situación que debo resolver es la siguiente: En el archivo de texto tengo un campo de diez posiciones de las cuales las dos últimas debo interp...more >>

I'm having trouble getting a query for the following (wanted results at bottom): CREATE TABLE [dbo].[Prices] ( [PR_From] [smalldatetime] NOT NULL , [PR_PriceB] [smallmoney] NULL , [PR_PriceC] [smallmoney] NULL , [PR_PriceD] [smallmoney] NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[P...more >>

How do you launch the T-SQL Debbuger from the Query Analyzer? Thanks in advance ...more >>

I would like to create a nightly process that will Backup a Database on Server BVXX1 and restore on Server BVXX2. What is the simplest way to accomplish this task? I started TSQL statement to backup the database on Server BVXX1. BACKUP DATABASE MXX1 TO DISK = '\\BVXX1\e$\MOM.bak...more >>

I have a SQLServer 2000 with latest SP's. I have a table where network log packets are inserted, a trigger is executed on each packet insert that involves selects (with about 2 params on the where clause), and the packet is inserted into another table. The server has a gig of ram. The db size ra...more >>

Hi, I have encountered a very strange problem which I am hoping somebody could please help with. I am using sql 2000 service pack 3 on a windows 2003 server. My windows user account has been granted a login to sql server and added to the fixed server role sysadmin. Up untill recently all was...more >>

Hello, I'm doing the following: 1. using sp_detach_db to detach a database from SQL Server 2. moving the .mdf file to read-only media 3. using sp_attach_db to attach the .mdf file from the read-only media to SQL Sever (with Trace Flag 1807, just incase the media is UNC) What I wan...more >>

(SQL2K) I have a Names table with a unique key on Name (see below) There are 1031 recs with double spaces in the names that I need to remove. select * from names where name like '% %' order by name --1031 Problem is (e.g.) for "JOHN SMITH" (double space) there is an existing entry alre...more >>

I have a series of stored procedures that cause my t-logs to generate enormous size. So much that I need to truncate the logs daily, if not more frequently. Is there a line of code that I could use that would turn off the writes for a length of time and then reenable them? I am not in n...more >>

Hi, I'm trying to select particular columns out of a system stored procedure, sp_columns to be exact, for a table of mine. For example, I would like just the column COLUMN_NAME. I guess it would be something like this, Select COLUMN_NAME From sp_columns 'myTableName' But that will gi...more >>

How can I reorder physically reorder the colunms in a table? (I have an application that stores metadata about the schema in the production system and the application is sensitive to the physical ordering of the columns in the live data tables as it relates to the sorted order of the co...more >>

I'm fairly new at this so any direction is welcome! I have multiple threads that need to add or update rows in a table. I want a thread to either add a row if the row (with the same identity) hasn't been added yet, or update the existing row if it already exists. I started by creating a sto...more >>

Hi all, I have this stored procedure which takes a snap shot of a users data available to them, then updates the data stored in the our #temp table, and then returns the modified data set to the java client that invoked the call. Unfortunately, we still have sites running SQL 6.5 and SQL 7....more >>

Hi, I have a stored procedure that does a series of updates to multiple tables. I'm using @@ERROR to trap errors as I go. If I hit a referential integrity error I take a corrective action. All this works fine except that the calling application still receives the Referential integrity error. So t...more >>

Hi! I have a table Projects with field ProjectID,int as PK. The table has 100+ records. I want to select 5 random records from the table. Is it possible to write a single query or set of views that refer to each other, that would acomplih this task? Thank you in advance. Dejan...more >>

Hello, I have a stored procedure that is scheduled to run once a week on Sunday at 12:00:00 AM. The problem I am having is that it appears that the stored procedure runs twice. Once before 12 and once at 12. I have a similar problem with another stored procedure that runs once a week on M...more >>

In MSSQL, how can one grant execute permissions (at the admin level) for stored precedures like xp_sendmail, xp_stopmail etc...to other users?...more >>

Hello, does somebody know if it is possible to send an excel-attachment? It is no problem to send a textfile, see below (@type= N'text/plain',), but how can I send *xls attachment (@type=?)? Thanks in advance, Regards selcuk I have have found this and it works: declare @rc int exec @rc =...more >>

In MSSQL, how can I grant execute permissions for stored precedures like xp_sendmail, xp_stopmail etc...to other users? Individual users don't have the execute permissions....more >>

Hello Using SQL 2K sp2 on Win2K I am trying to implement a UDF as a Check constraint. The UDF goes through a table, and if there are duplicates, returns 1 else 0. It runs in about 1 second When I do the Alter Table Add Constraint Check (UDF = 0), it just keeps on going (24 minutes and counting...more >>

Hi I have a stored procedure which executes a series of updates against the database. I'm using @@ERROR to detect if an update fails (in this case due to a referential integrity error). My procedure then deals with the error and I want to exit the procedure WITHOUT indicating to the calling applica...more >>

hello, this might be hard sql, i don't know. i want to make waht is called a indented bill of materials. please look at the sql at the bottom. i want to make my output look like this below. you can play around with the looks of the outputs. it does not have to be exact please. level pa...more >>

Hi all, I have a table: PKID LASTNAME FIRSTNAME MEDID NOTES PROCESS_BY_PC PROCESS_BY_PC_DATETIME 1 AAAA BBBB 123 2 CCCC DDDD 234 3 KKKK WWWW 987 ....... ....... I have 3 VB clients running First client will get the first available record in that table and retur...more >>

Can Any one help me how do we get to know the Total Disk Space of all the drives where SQL Server is installed. There is an extended SP xp_fixeddrives which gives info about the Drive and Free space but I need to know the Total capacity of each Drive.. Thanks in Advance...more >>

Theres any way to find the path of sql by code? Thanks.......more >>

Hi how do we achieve the following TableA Full Outer Join Table B and TableA Full Outer Join Table B In the same query please provide syntax Cheers Sunny...more >>

Hello! We are developping a project using MS-SQLServer 7 and we need a process for the synchronization of 3 tables together. Inserts and updates in the table A should immediately and automatically occur on table C, and updates on table C should also automatically occur on table B. We think t...more >>

i am using a table designed by a college, but need to add a new field to be used as a unique field (TranRef). The problem is that there are already some 9500 records. Is there a quickfire way to updateall the field ( tranref ) for all the records, starting at record 1, tranref = 1 , recor...more >>

Hi, Can anybody tell me the difference between a primary key constraint and Not NULL Unique Key constraint with clustered index. Theoritically both looks the same, but am interested to know their differences in terms of their storage and performance. One more question that's runni...more >>