When I tried to save a stored proedure (in SQL Server 2005 Express), I got an error saying "The operation could not be completed. Not enough storage is available to complete this operation." Any idea what is causing this error to pop up? I still have about 4GB disk space left. Thanks....more >>

Hi, I am saving the data of profiler into trace table every day and then export this data into table called RepositoryTable. RepositoryTable has exactly same structure as profiler trace table. Every day I am inserting 10 lakhs rows of trace events in the Repository table. Repository tabl...more >>

Hi all, I want block attempt to drop any objects(tables, sp, views...) of my data base. Have way to do this? I did see same thing about DDL trigers, any one can sed-me one sample? Thanks to all ...more >>

In a case where I'm not sure how many columns Im going to use should I just use select * from table1 or select col1, col2, col3, col4... from table1 Is there any disadvantage for using select * in terms of performance or security? Thanks, Howard ...more >>

It seems that if I am pulling data from a linked server down to my local server, all works fine. But pushing data up to the linked server takes 100 times longer. How can I efficiently write this statement to insert records from my local server to the linked server? Actually, even without the j...more >>

Hi all, I have one Data Base, Today one strange thing occur, one table of my data base disappear , I didnt drop it, then i would like know if have way to determine when this occur; The last week same thing occur whit one store procedure. Any one knows, how to help-me. I use SQL S...more >>

I have a Country table with (surprise!) countries, and a Locale table with states, territories, provinces, etc. I also have another table, Port, that usually contains Country information but only occasionally contains Locale information. My question is, how do I set up the foreign keys maintai...more >>

I installed SQL Server 2005 with all the server tools selected (other than the sample databases) on my server and then installed all the connectivity and tools on my own machine (ie, no database, BI, Reporting Services, etc). I want to program against the Server Management Object (SMO) model ...more >>

I am declaring a dynamic generated cursor in a nvarchar string and passing it to sp_executesql. I then want to open it and loop over the results using FETCH INTO. This worked fine in SQL 2000. In SQL 2005 I get the error message that I put in the header. It thinks that the SET OPTIONS have...more >>

Thanks in advance. I have an instance of Enterprise Manager for SQL Server 2000 on my desktop that does not list the above databases. When I attempt to add any of them, I receive: "Error 5123: CREATE FILE encountered operating system error 32.". I see all the databases in the server's copy o...more >>

Hi All, Newbie here, and in typical newbie fashion, has managed to down our "live" server. People are back on Monday, so I have this weekend to fix the problem. Was running an operation on the DB via my ERP s/w (deleting a user created field) which was taking far longer than normal. Being i...more >>

Hi, I am using SQL server 2005 express edition on win XP installed on my machine. when I use the SQL server Management studio, I have to enter machinename\SQLExpress .How can I connect using just machinenname and omit the SQLExpress instancename?what settings do i need to do? Thanks for y...more >>

Hi, We have just migrated from MSSQL 2000 to MSSQL 2005. Everything seemed to be working for about a week, when we realised that... no actual data in the DB is updated. The problem is quite difficult to find, because the failure happens in triggers. I will try to simplify the actual case to...more >>

Hi, All! I have many tables with name convention tYYYYMMDD and set of SPs to perform some calculations. Ideally I would need "temporary view' functionality. CREATE PROC MyProc (@tablename) AS CREATE TABLE #mytable (dt DATETIME) EXEC ('CREATE VIEW #myview AS SELECT * FROM ' + tablebame) ...more >>

It seems that if I am pulling data from a linked server down to my local server, all works fine. But pushing data up to the linked server takes 100 times longer. How can I efficiently write this statement to insert records from my local server. I believe I cannot use OpenQuery because I hav...more >>

I', trying to get the following case staement to work and am not sure if what I'm trying to do is valid. Based on one fields valur I want to perform an operation on another fied of the sam record, see query below. SELECT LOWER(R.name), (CASE LOWER(R.Name) ...more >>

I am using ms sql 2000. THIS IS THE PART OF THE TABLE I HAVE 18 2 Bytes Per Cluster 19 2 Drive Type 20 2 File System Type 21 2 Free Clusters 22 2 Free Space 23 2 Letter 24 2 Name 25 2 Percent Used 26 2 Sectors Per Cluster 27 2 Serial Number 28 2 Total Clusters 29 2 Total Space 30 2...more >>

Create table test (col1 varchar(50),col2 int) insert test values ('ABC',1) insert test values ('ABC',2) insert test values ('XYZ',1) insert test values ('XYZ',1) I would like the output to report those unique values in Col1 that have different values in Col2.. So the output based upon ...more >>

Here is the BOL example for the PIVOT function: SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5 FROM (SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) p PIVOT ( COUNT (PurchaseOrderID) FOR EmployeeID IN ( [164], ...more >>

Hi all, I'm working on a query which will ultimately be used in a stored proc for a report. This report will quite simply list the components that are unique to a particular customers goods. I've created a mechanism for traversing our BOM structure, and getting a list of all components ...more >>

Strategy should I use the following to select the last posting in my forum group in order to get the last poster I use the function that follows or should I use the select at end that subqueries everything?. view= SELECT TOP 100 PERCENT dbo.tposts.thread, MAX(dbo.posts.pid) AS lastp, dbo.f...more >>

i'm developing an application that currently creates tables in the database after a user has completed the design for a workflow. having never created an application like this i keep having second thoughts about whether this is a good idea or not. i keep debating with myself whether i should...more >>

I'm using an INSERT INTO...SELECT * FROM bulk statement to insert records into a table. If I write a trigger on the target table that executes on INSERT, will the trigger fire for each row inserted? If not, how can I get the trigger to fire for each row? Thanks in advance ...more >>

Using SS2000 SP4. I ran this update statement "Update tblCompanyContacts SET varCompContactPhone =REPLACE(varCompContactPhone,'abcdefghijklmnopqrstuvwxyz()-_+=!@#$%^&*,.?/:;','') " and it fires off this trigger CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts] FOR I...more >>

I'm sure this is an old question. I want to exclude duplicates in one column while still pulling all columns as if I used SELECT *. Can this be done? Thanks, Bank...more >>

Hello I'm relatively new to SQL Server/T-SQL and find myself stuck with this problem: I need to do something like this: Declare @someValue NVarchar(100) Declare @someFunction Varchar(100) -- -- Assign value to @someValue from a Cursor -- -- -- Assign name of the...more >>

hi, I am running the following query and getting this error. Could not create an instance of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. OLE DB error trace [Non-interface error: CoCreate of DSO for Microsoft.Jet.OLEDB.4.0 returned 0x80040e73]. I am using windows xp SP2 and Access 2003 an...more >>

I have a feeling this isn't allowed, but is there any way to update columns in two different tables through a join in the update syntax? TIA...more >>

This is prolly more of a gut check, but needed to know if this looks right. I am making another Scalar function.. CREATE FUNCTION [dbo].[EvalTradeCode] ( @tradeSymbol char(15) ) RETURNS int(1) AS BEGIN Declare @intOffset int If (left(tradesymbol, 1) = '@') If (isnumeric(left(right...more >>

Let's say I have a table: create table example ( id int, createtime datetime, name varchar(20)) go create index ix_createtime on example(createtime) go create index ix_createtime_name on example(createtime, name) go Is the index ix_createtime redundant because the createtime colum...more >>

I tried posting this once but I had no luck as I don't think it appeared. How does one do the following considering that an identical table in Database2 also exists in Database1? INSERT INTO Database1.Table1 SELECT * FROM Database2.Table2 WHERE rows in table2 do not exist in ta...more >>

I would like to split a Name column which holds the full name with spaces into 3 seperate columns. ie Title, Firstname, Surname. Regards Mike...more >>

Quick Dumb Question I was wondering if there was a way to revel what format a query returns per column. I am running into the situation where I am pulling data from a huge list, and occasionally I get the error "Implicit conversion from data type sql_variant to varchar is not allowed. Use t...more >>

I have a query that returns 7 columns, the first is a date and the other 6 are money columns. Now if the total for any of the money columns is 0, i dont want to have that column selected. I have my original select and a table variable to collect the data from the select and i was trying to use...more >>

Is there a best syntax for writing a query (specifically in sql server with t-sql)? I've written a few with the JOIN keywords but I mostly write them using '=', '*=', etc.... Is there a more 'sql-compliant' way or is it just a matter of preference? THanks....more >>

Hi all, This is probably a simple question but I still new enough that I can't figure it out (this is only my second real query i'm so REALLY new). I have a table that looks like this: CustKey InvoiceDate 01 2006-05-19 02 2006-05-19 03 20...more >>

Get active directory users from SQL Query Analyser -- I am trying to do a select in SQL Query Analyser to get the list of users in active directory. I use the following code: sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource' SELECT [Nam...more >>

I have the table persons with fields lastname - varchar id - numeric i want to update last name to null if id and lastname are equal. i tried the following query, but it not works update persons set lastname=null where lastname like id; I really appreciate you if you could help me. ...more >>

Hello Everyone, This is probably a simple fix so be kind when/if you reply. The query below retrieves information for individuals based on a column named ATTRIB_DEF, and assorted other columns; however, the ATTRIB_DEF column is important one. Currently the query gets all individuals that hav...more >>

hi also i want to know what have more performance : a table variable or a temporary table in a stored procedure the rows in the tables are approx. 1000 thanks michel...more >>

Dear all, I have table called CATEGORY, which is defined as follows: CREATE TABLE CATEGORY ( CATEGORY_ID INTEGER IDENTITY(1,1) NOT NULL, CATEGORY_NAME VARCHAR(40) NOT NULL CONSTRAINT UC__CATEGORY__CATEGORY_NAME UNIQUE, PARENT_CATEGORY_ID INTEGER, CATEGORY_ICON I...more >>

I am having trouble getting this dynamic query in a sp working and the code is below so can someone please help me? It would be very much appreciated. I want to build this query with only values that have values that are passed, if they are null, no need to be included in query. This almost s...more >>

Here is my problem and I suspect this is a simple question for the experts. My query attempts to get the names of players that played in a game during the past x minutes and then the average scores of that player for ALL games played by that player. The query I have right now looks somethin...more >>

Is there a way to get SQL 2000 Query Analyzer or Visual Studio 2003 to automatically re-format SQL keywords to upper case as you type? I can do this in Ultra-Edit by editing an external file that contains all of the keywords. Thanks....more >>

Using SS2000 SP4. I have a question about the following code. CREATE PROCEDURE dbo.usp_GetSelectTempTasksUser @intUserId int AS BEGIN Declare @SQL VarChar(1000) SELECT @SQL = 'SELECT TOP 100 * FROM (SELECT dbo.tblTempTasks.numCompanyId, dbo.tblTempTasks.numContactId, dbo...more >>

Hello, I have a piece of 3rd party software that we recently upgraded to a new version, the new version takes a long time to load up each order because every time it loads the order entry module it runs a Select Count(*) From Table (I discovered through a trace). I know this is bad programmi...more >>

I have written a stored procedure but i cant get it to function with dates and i get the following error: Error converting data type nvarchar to datetime. If the stored proc had a sql string like SELECT T.Summary, T.Priority, T.DateRequested, T.AssignedTo, P.ProblemType, T.Descriptio...more >>

I'm using ms sql 2000. First here is a sample of my XML input: - <scan ID="18.0" Section="System Restore"> - <scanattributes> <scanattribute ID="18.0.0.0" ParentID="" Name="Description">Removed ClientScanServiceSetup</scanattribute> <scanattribute ID="18.0.0.0.0" ParentID="18.0.0.0" Name...more >>

Hi, My web server has MsSql at the back-end and asp at the front-end. A UserID, Password, and a dynamically generated code field are present on the website for the user to logon. UserID field takes an EMAIL address as input. There are other features in the website such as search n advanced ...more >>

Requirement: 1 billion records to be inserted every day 180 days data to be maintained 180 * 1 billion records = approx no of records = defines the size of the database Need to design the database/process to maintain so huge data? - R...more >>

:|Hello, After a long search, I found this article i thought to solve my problem. I am using Win 2000 Advanced Server, SQL Server 2000 standard edn. dotNET F/W 1.1 My table structure is empid integer empname varchar(30) empname_chinese nvarchar(20) >From the asp.net pag...more >>

Is it possible to 'query' an mdf file using MS Jet Provider or somethnig like jet provider without having to 'attach' the mdf as a database on SqlServer. Thanks. ...more >>

Dear All, We're trying to resolve a rather odd performance issue on one of our servers. We have a COM+ component calling a stored sprocedure in a SQL Server database. The COM+ component is on a different machine, so the call is going across a network. The number of logical reads on the data...more >>

Hi When running the command xp_loginconfig 'Default domain' on SQL Server 2000 and Windows 2003 Domain controller, the expected result is <Domain name>. This works on 2 out 3 of our setups, but on the last setup the command returns the hostname of the machine running the SQL Server instanc...more >>

Hi i have a varchar variable called @sql @val1 varchar @val2 varchar set @sql=val1 + val2 the problem is when either of one has null value suppos val1 has null value then @sql not accept val2 and put null value i dont know why its doing like that if val1 is null then it should take val...more >>

Hi, Looking for a proper way to change a SQL login ( not a domain user ) from DBO to SA ( and back if a curtain action has finished ) using TSQL. This user is NOT connected to any role therefor sp_droprolemember/ sp_addrolemember is not appropriate. Can you help me out guys? G Kramer ...more >>

Hi all, I have a problem whereby i need to convert multiple row data into a single row for instance if i have a table CliID Code V D T 1 A 100 1 B 01/01/06 1 C Beer 2 ...more >>

I have table of structure as create table (AccountID int, CompanyID tinyint, Account_No char(20), Status char(1)) and having primary key with AccountId Sampla date 12001, 1, 1000-010, a 12002, 1, 0710-207, a 13012, 2, 123-9410-034, a 32412, 1, 854-6011-520, a now i have created a view ...more >>

Good Morning I have the following table format, whereby I need to stamp all records for that policy with the most current transaction's Master_Flag. Audit_Number Policy_Number Master_Flag Trans_Date 1 CC123 4 27/12/2005 1 ...more >>

Hey guys I see google returns multiple links for a bug that is not corrected in SP1 for SQL2005? If you use a predifined View / FUNC and USE a combination of TOP ?? AND Order by ?? on the view or func the rows returned are incorrect! (ie. Either the data is incorrect or the Column in ques...more >>

I have a sp which return mutiple result set. Is there any way by which i can export the multiple resultset data in ms access using some tool, DTS etc. ...more >>