I am running MS SQL 2000. I recently ran a procedure in Query Analyzer from the Master db to clear out all replication information so I could start/recreate it again. After I ran this procedure Enterprise Manager no longer showed the registered server in the tree. When I tried to re-regist...more >>

Hi all, You're all stars, really helpful. Maybe one day i can be :) My question is regarding the authentication in Windows SQL Server 2005. We have setup a sa account. We purchase software from a third party that runs on it. Before their software logs on the sql server as sa, and does its...more >>

Hi. I'm sorry to bother all of you, but I have spent two days looking at code samples all over the internet, and I can not get a single one of them to work for me. I am simply trying to get a value returned to the ASP from a stored procedure. The error I am getting is: Item can not be found in t...more >>

Hi, I am trying to: 1. Create a SQL database (I am working with SQL 2005 Express) 2. with a C# code 3. when the user is not the computer administrator. I have managed to create the database file (code below). I am not sure it is the right way. Can you take a look please? I would like to ...more >>

hi, 1. is there a statement in ms sql, what creates a sequence? cant find anything in web :-( -> oracle: CREATE SEQUENCE XYZ INCREMENT BY 1 START WITH 1 NOCYCLE CACHE 20; -> ms sql: ??? 2. hwo do i create a link to another ms-sql database thx a lot need help, urgend :-) ...more >>

Hi. I utilize the Calendar table, and I'm able to find how many working days between 2 dates, but does anyone use this table to find the 2nd or 5th working date? if 11/30/06 then 12/4/06 I'm sure it's not too difficult but i can't seem to get it to work (select caldate from calendar wh...more >>

Hi there, I struggle to get this going i would like to insert data into 2 tmp tables in a view. If i run the code on it's own it works perfectly until i want to create a view it complains about the INSERT this is my code Create view dbo.vew_Switches As INSERT INTO tmpIns Sel...more >>

Hi =20 Im not an SQL n00b, but I'm no SQL guru... I'd like to work out the SQL code required to achieve the following goal = in pseudocode: Grap a record Using info from that record, add (the width) to a running total. When the running total exceeds a certain value, stop grabbing records...more >>

Panicked, as usual. Who says it's great being a sole proprietor? Using MS-SQLServer: Three tables: A_Appointment, AX_Appointment_Entity and E_Entity. AX_Appointment_Entity is an intersect/association table between A_Appointment and E_Entity. One appointment may have many attendees (Appointm...more >>

Do aggregate functions (sum, count, min, max) inherently cause table locks? More concretely, would the following query typically result in a table lock? select sum(quantity) as total from products Thanks, Scott ...more >>

We are facing design issues, Could you please advice us how to proceed? Problem description: Web App will pass a complex dynamic SQL query to backend and it should return result set as fast as it can Issue 1: SQL query will have lot of JOINS and WHERE clause Issue 2: Each Table contain million...more >>

Hi I've got a quandry - I have a detailed database that handles advert orders between a design agency and printers / magazines etc. I want to add up the total spent by the client and put the results to a field. I've actually done that using a query table in access - it should be quite ...more >>

Dear All, One of our employee made a mistake and deleted something from database. I would like to recover that log file without restoring the backup and the other log files. The reason I want to do this is our database is getting real time information I cannot shut down the database. Is there ...more >>

Hello all, my first post here...hope it goes well. I'm currently working on stored procedure where I translated some reporting language into T-SQL The logic: I have a group of tables containing important values for calculation. I run various sum calculations on various fields in order...more >>

Hi I am trying to perform a search that will return records based on a fiscal year search of the bill_Date. The user gives the year then I want to search based on the fiscal year (July 1 - June 30) for the year given. The table looks like this Bill Table id_Num bill_date bill_am...more >>

Hi guys, I am pretty happy with the new system views in SQL 2005 but there is one thing I could not figure out: What is the view that let you see what object belongs to what DB? Sorry for the simple question, Cheers, G. ...more >>

I am trying to update 1 table with the top records from another table for each record in the first table UPDATE HPFSLOWMOVING SET TOP 1 LASTRCTDATE = (SELECT DOCDATE FROM IV30300 INNER JOIN HPFSLOWMOVING ON HPFSLOWMOVING.ITEMNMBR = IV30300.ITEMNMBR AND HPFSLOWMOVING.LOCNCODE = IV30300.LOCN...more >>

I was wondering if it is possible to continuously query a real-time datastream using SQL Server. Does anyone have any experience of this? I have found LINUX based systems such as Borealis and STREAM. I would prefer to use a Windows based system as the program using the query results is Windows b...more >>

UPDATE Sales.DeliverySchedule SET DeliveryList.modify('declare namespace df=http://schemas.adventure-works.com/DeliverySchedule; replace value of (df:DeliveryList/df:Delivery/df:Address)[1]' with "7194 Fourth St., Rockhampton"') WHERE Sales.DeliverySchedule.ScheduleID = 1 This query gives ...more >>

This query SELECT DeliveryList.query('declare default element namespace http://schemas.adventure-works.com/DeliverySchedule; <DeliveryRoute> <RouteNo>{ sql:column("D.DeliveryRoute") }</RouteNo> for a$ in /DeliveryList/Delivery/Address return <Address>a$</Address> </DeliveryRoute>') as Re...more >>

I was having this problem: http://groups.google.com/group/microsoft.public.sqlserver.server/msg/e36e423972323378?dmode=source with it taking an inordinate amount of time to enumerate the databases on my local SQL Server instance, so I went through all the local databases and unchecked "Auto ...more >>

Hi, I have written some batch scripts using osql to run some sql statements. They are basically something simple (such as running a stored procedure etc). I want to convert them to run using vbs. What kind of connection option do I have? I notice we can use DMO but I guess they are now phased...more >>

Is there an equivalent in mssql to SQL Anywhere's GET_IDENTITY which reserves the next autoinc value for a table? Yes I know about @@Identity and SCOPE_IDENTITY. I need to get the next autoinc value _before_ I insert the record due to the way the existing application works. I've seen DBCC C...more >>

Hello there, I have about 30 sql servers runnign multiple databases amd i need to audit them so what i want to do is to log onto each server and then maybe export the database properties into an excel sheet or text file. Anyone know how to do that i am quite new to sql any help will be awes...more >>

Hello, I have following Situation: I have a Dimension "SourceDirectory", whis has Elements "SourceDirectory1", "SourceDirectory2", ... until "SourceDirectory10". My Statement in MDX is whis: SELECT {[Measures].AllMembers} ON columns, {[SourceDirectory].Children} ON rows FROM [CheckstyleError...more >>

Dear All, I'm attempting to create a query that will transpose repeated fields into a single table structure. Can anyone think of how this can be done as I'm stumped at the minute? I'd like to do this without having to create a cursor due to the overheads and performance issues associated wit...more >>

Hi, I have three tables with there fields given in brackets: User: [userId as int] (PK), [userName as navarchar] Divisions: [divisionId as int] (PK), [divisionName as nvarchar] DivisionsOfUsers: [userId as int],[divisionId as int] the "DivisionsOfUsers" tables has many-to-many relationships b...more >>

Hello people, I might sound a little bit crazy, but is there any possibility that you can incorporate 4^15 (1,073,741,824) tables into a SQL Database? I mean, is it possible at all? There might be a question of where anyone would want so many tables, but i'm a bioinformatics guy and I'm tryin...more >>

I have a stored procedure that (after selecting, incrementing and updating) returns the next sequential filename. I haven't been able to plug it in to the destination textfile filename in my DTS package. I know it has something to do with Dynamic Properties and I can navigate to the prop...more >>

Ik wil uit een tabel het gehele getal halen, de waarde staat als 05612123 maar wanneer ik dit opvraag in bv. de Query Analyzer wordt de waarde gegeven als 5612123. De 0 welke ervoor staat wordt dus niet afgebeeld. De tabel in de kolom heeft als Data Type char, de Length staat op 10 en Allow Nul...more >>

Hi all, At this moment I'm trying to get database mail working. According to some people it should be easy. Well...that is not the case for me. I'm having the following error: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (200...more >>

I have never used replication before and I have been asked to consider it in a project I am currently working on. I have created an application for a sales team which is loaded on their machines, it uses ms sql as its data source and connects via the internet back to the central server in the ...more >>

Hi All, My SQL server transaction log is getting bigger every day and my HDD if running out of space. So i follow the MS KB about how to Shrinking the Transaction Log. After doing so the log is much much smaller as i can see the size of it under enterprise manager. The problem is that the ...more >>

/* Subject: How to build a procedure that returns different numbers of columns as a result based on a parameter. You can copy/paste this whole post in SQL Query Analyzer or Management Studio and run it once you've made sure there is no harmful code. Currently we have several stored procedu...more >>

Hi. Somebody can tell how to deal with this error. When I try to connect from an asp page to update a record wich has a ntext field it give me this error... I tried with sp_configure 'remote query timeout',0 but no works... What can I do ???? thanks ...more >>

i have another problem.and it's now on converting a char(4) to datetime here is the situation J_TIM < F_TIM J_TIM is datetime while F_TIM is char of 4 example J_TIM = 20:30 F_TIM = 2030 how can i convert F_TIM to datetime so that i can compare them. ??? thanks ...more >>

hi, i need some help with this. basically, what I need is to select all of the rows from one table in a cell ie col1 col2 nam1 a nam1 b nam1 c nam2 a nam2 b I need to make it look like nam1 a,b,c nam2 a,b thanks for your assistance... ...more >>

I have the following XML in an XML column in a SQL 2005 Database. <DeliveryList xmlns="http://schemas.adventure-works.com/DeliverySchedule"> <Delivery SalesOrderID="43659"> <CustomerName>Steve Schmidt</CustomerName> <Address>6126 North Sixth Street, Rockhampton</Address> ...more >>

I'm trying to write a stored proc... Basically, I have a tblItems table which contains a list of every item available. One of the columns in this table is the brand... for test purposes, I hardcoded the BrandID=1... tblItems also contains a category column (int) which contains a categoryID...more >>

Hi All, As per BOL, XML data type can store up 2 GB of data. My question is when a row is inserted in a table, for its xml column, 2GB of space will be resered. In other words, how xml is internally stored. Is storage allocation is similar to varchar(max) data type? Thanks in advance for ...more >>

Hi, I have an old 2000 bak file for a db that I would now like restored onto sql server 2005. I keep erroring out though, this is what I'm doing: Went to Restore Database in sql 2005, selected the db I want it restored to, and File (say c:\temp\xyz.bak) Checked the restore checkbox, went to o...more >>

Can anyone offer some advice on how to reduce the size of a database log. The database is mirrored on another server using the logs. Thanks ...more >>

Hey there :) Sorry if I'm asking a dumb question here, but I'm still quite new to MS SQL, so this problem might appear larger to me than it really is. I'm trying to create a performance test environment for a Ruby on Rails and Mongrel setup with an MS SQL Server 2000. The adapter, mssqlcl...more >>

select convert(float,'1.2334e+006') 1233400.0 select convert(decimal(20,2),'1.2334e+006') Server: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric. Is there any way around? Is there any set options? I tried arithabort or arithignore and they don't work. T...more >>

Hello, I have an application that uses SQL Server express. I'd like to be able to package the SQL Server Express set up file and deploy it with my application but all the searching I've done only tells me how to deploy SQL Server Express manually (i.e. how to set it up yourself). I can sc...more >>

I have a database which contains more than 20000 stored procedures which were created with ansi nulls off. This i found out using the query SELECT name,AnsiNullsOn FROM ( SELECT name, OBJECTPROPERTY(id, 'ExecIsAnsiNullsOn') AS AnsiNullsOn FROM sysobjects WHERE type = 'P' ) A WHERE AnsiNullsOn...more >>

Sqlserver is case insensitive in this way ? for example: select....where col1 like '%BURG%' will return 'Burger King' ???!!! ...more >>

Hi I am attempting to take a manual backup of an SQL 2005 database, via Right Click on MyDatabase --> Tasks --> Backup [Specify the filename path and type of backup] However, when I try to specify the filename or, in fact, do anything, I receive the following error Property BackupDirec...more >>

Using SQL Server 2005 Express. I need to periodically duplicate an entire database and rename it. Most tables are empty but some are to be prepopulated. I was thinking of having a "template" database called perhaps "EmptyDatabase", and then copy that into a freshly created database with a...more >>

I'm trying to help a client export some data from SQL 2005 to an Access database or even an Excel file. Either way I get an error like this: - Setting Destination Connection (Error) Messages Error 0xc0204016: DTS.Pipeline: The "output column "press_release_body" (1500)" has a length that is n...more >>