I have a problem with sqlserver. We user server as data / transaction / report server . Our clients (40-50?) enters transactions. We use client server model generally. We use sqlserver mainly as a data server. But we have some report stored procedures, these procesdures makes some calculati...more >>

I get an error 'Multi-part identifier '#tmp1' could not be bound. select #tmp1.col1 as mycol, other columns... from #tmp1, realtable left join table2 on table2.id = #tmp1.col1 .... where realtable.id = table2.realtable If I reverse the order in the from statement everything is ok but my r...more >>

Hi, I have four tables: companies, sites, networks, ips Here's is a statement to generate a summary from the first three tables: SELECT dbo.Companies.Company_Name AS Company, dbo.Sites.Site_Name AS Site, dbo.Networks.Network_IP AS Network FROM dbo.Companies INNER JOIN dbo.Si...more >>

Are temp tables stored in memory or are they written to a file? Is it very expensive to use them? We currently have 4 being created. The 4th is utility created from the 3rd. Once created we drop the others. ...more >>

TableA Name Date Value A 1/25/05 2 A 1/24/05 1 A 1/23/05 22 A 1/21/05 4 A 1/20/05 3 B 1/25/05 21 B 1/23/05 23 B 1/21/05 24 B 1/19/05 2 C 1/25...more >>

I am doing a project and may need help from some sql expert, where can I found someone to work as freelancer? I may only need a couple sql statements. Alejandro ...more >>

if i have a table with a datetime field how can i group by day and get a count of records for each day? if i group by the field it self it doesnt work because the hour/minute/seconds make each date unique ...more >>

Hello All, Is there a way to just use a template along with a basic script to that would against a server ? Someone asked me this question and I told them that the trace file that we create using sp_trace_generateevent sp_trace_setevent, sp_trace_setfilter, sp_trace_setstatus, sp_trace_cr...more >>

Hi , How to trace on a specific Table's Event ? means I want the trace report when pubs.Titles table is execute (INSERT/UPDATE/DELETE Action) Thanks ...more >>

Dear Sir, My name is Sureshkanan working as programmer cum network administrator. I hav been using VB 6 & Ms Sql server 7 (now 2000) for the programs. I have so many problem to be solved. If u don't mind, please help me in this regard. Problem 1. I hav a varchar field in sql server, ...more >>

I'm setting up a trace in Query Analyzer and having an odd problem. The entire trace script is below. It was originally created using Profiler, but it's been modified slightly by hand. The trace includes a filter on logical reads so that only events where logical reads are >= 1000 should be...more >>

Hi ! Please advise which libs and include files are required to compile extended stored procedure file for SQL Server 2000 in VC++. Thanks in advance: ...more >>

Given this table CREATE TABLE SearchFieldDetail (ExpSetNo int, TableAlias char(10), Operator char(2)) which has multiple rows for each ExpSetNo, I want to find each ExpSetNo that has only one TableAlias among all of the same ExpSetNo rows and which has 'CC' as the Operator in all those rows...more >>

Hi All, Is there a way to do this? declare @P1 varchar(100) set @P1 = '3,5,7' Select * from my_table where [id] in (@P1) here the datatype of id is numeric(8,0) and it gives me an error Server: Msg 8114, Level 16, State 5, Line 3 Error converting data type varchar to numeric. Than...more >>

Hi All, I have a very big database (110GB+). It takes a long time to restore or even just copy... Now, I have a process that will alter a lot of data in this DB over a lot of tables. And this process has a bug in it... In order to debug this process, I would need to have to keep resto...more >>

How can I determine the columns that will normally be returned from a particular stored proc without actually executing it? I understand that a proc may return no recordset, one recordset, or multiple recordsets, but assuming that none of my procs return more than one recordset, is there a w...more >>

Following code is just select category 1 but for example What can I do if I want to select 1 and 2? declare @CategoryID as int set @CategoryID = 1 SELECT * FROM Northwind.dbo.Products WHERE CategoryID in (@CategoryID) ...more >>

I have a interesting situation, where XML is stored inside of SQL NText field. Unfortunately, I need to extract that data as SQL from the XML. Here is the data in the BillingAddress field: <?xml version="1.0" encoding="utf-16"?> <Address> <ID>-1</ID> <FirstName>Joe</FirstName> ...more >>

I am trying to use a script to create a table that already exists in one DB in another DB. I used Enterprise Manager, "copied" the table and pasted it into notepad which gave me the following script. However, I cannot get SQL query analyzer to accept this script. Not sure what I am doing wron...more >>

Hi, how to write a query can group customer sale by month? please help. ...more >>

SQL 2K We developed a VB.NET application (SQL 2k db) where users can log into the system and extract reports. We are planning to create a report with user list who accessed the application in last 30 days. Can this be achieved by querying directly to the database ?? Thanks John ...more >>

I have a query running on MS Access like this : TRANSFORM Count(ActivityClass) AS CountOfActivityClass SELECT ActivityClass FROM Category GROUP BY ActivityClass PIVOT Type but how it's can be run on SQL Server, is there any function on SQL to run the Transform function? Thanks ...more >>

Hi all, I have found that adding a column to an existing table (tested on a table with >1M rows >200 bytes long) behaves as follows: 1. If the added column does not allow NULLs and has a DEFAULT value, the operation actually changes the row structure. I can see that from the time it takes t...more >>

We have a create table query against 2 large tables (about 12 gig) that balloons our tempdb to about 17 gigs. Is there a way to constrain the growth of tempdb by turning off logging of some operations. The query is fairly clean and the join and group by clauses are fully covered by indexes....more >>

Hi, I just had a strange occurrance on one of my SQL 2000 SP3 machines. (Win 2003) I changed the underlying table structure of two table, inserting a field in the middle of the tables. All of the functions that referenced these did not recompile when I next ran them. They starting giving...more >>

Is there a NET STOP/START command (syntax) or OSQL to stop and start an sql server remotely on a network? I can only get it to work on my local system and I ned to be able to do this remotely on various servers. ...more >>

Does anyone know whether (and if so how :) it is possible to call external functions from SQL's SELECT statement in a similar way that MS Access allows you call code in its VBA modules? For example, I'd like to be able to do this: SELECT Name, DOB, VeryComplexFunction([Table.Numeric]) AS N...more >>

Hello... Am I able to grant permissions to sql server user logins to create and manage database diagrams without making them db_owner or system administrator? I am using SQL Server 2000. If I am able to, what do I need to do to grant the permissions? Please advise... Thank you in ad...more >>

Hi, the query with parameter exec sp_executesql N'SELECT*FROM rplRaumEvent WHERE [rplRaumEvent_id]=@P1', N'@P1 nvarchar(11)', @P1 = N'D0000001241' is 10 times slower than exec sp_executesql N'SELECT*FROM rplRaumEvent WHERE [rplRaumEvent_id]='D0000001241' without parameter. Why? I w...more >>

Hi I can do a select top 100 records from a table. But how do I do a select top 101 to 200 records from a table?? Thanks SELECT TOP 100 Names from tableDetails how do I do 101 to 200??? ...more >>

Hi, I have to build a table for something like 1.000.000 books. I need to use keywords for each book (to be able to search with the keywords in an intranet). I wonder the best solution to achieve this: *Add a new text field (varchar) and then use Full Text Search index *Add two tables, one...more >>

how to solve that problem? i have 2 databases, in few tables from one database i need foreign keys from other databases. ...more >>

How can I find out if a specific column is an Identity column or which column in a specific table is the Identity column? Thank you. ...more >>

I've been frustrated by a query where I need to get rid of paragraph characters within a particular column. But dang if I can figure out how to get SQL to find the paragraph marks. I've tried Replace(expression, chr(13), '') and the same thing with chr(10) and vbCrLf. Am I just stupid o...more >>

Hi, I have two questions. 1) What is the advantage of setting isolation level to READ UNCOMMITED if there are noupdates for the records that we are selecting? Does it make the select faster when we don't issue any shared lock? 2) I found this statement in books online: 'if an update a...more >>

Hello, I just need to rename a column in a database (for the moment I'm not sure the table has data or not). The statements I need to store it in a text file (a.sql) that is read and executed through an OleDB Connection. I tried something like this : alter table "table1" add ...more >>

Hi, I have a job that executes the TSQL below. Do I need to do the errorchecking myself (as below) or does the transaction automaticaly rollback if a statement fails? Feel free to comment on the script. (Its never too late to learn). Yours sincerely, Jo SET NOCOUNT ON SET TRANSAC...more >>

Hi. I am trying to write a single stored procedure which would trace the changes made by a user on a table. I would like this implemented on multiple tables having the most efficient code possible. Is it possible to browse to a table and extract all its columns (column_name from information_sc...more >>

I have a rather complex storeped proc that does many calculations and returns a row of data. Now I need to take that result set and use it along with another result set by way of a select statement. A simplified example: exec sp_GetAmounts @ID returns: 100, 300, 400 , 500 select * ...more >>

hi i am using convert (vatchar(32), @iuid) in curosr. this cursor is trigger whcih fire into message table. when i am inserting, or deleteing or updating i am getting "in sufficeint space to convert uniqueidentifier value to char" please advise me how to troublehoot this issue....more >>

I have been using databases (moderately) for a while--just your basic stuff, but I have been reading a lot of messages that reference query plan's and SQL generates a plan... What exactly does this mean? Is there any good reference material that covers what exactly this is? I think I und...more >>

I have this table called Location. There is a column called IsInactive. The value can contain a NULL,0, or 1. Why does this statement work: SELECT * FROM Location WHERE IsInactive <> 1 I have 3 location records where IsInactive = NULL and it doesn't return them. Just curious. Davi...more >>

I need to exchange image data from a SQL Server database with a client using XML. The client will also be sending me image data (converted to base 64) in an XML document. The export has been made very easy using the FOR XML, BINARY BASE64 clause. However I'm having trouble with the import ...more >>

Which is faster using user defined functions to grab the element you need out of another table or including the table as part of a view? We have an application that uses both and would like to optimize the routines as much as possible. ...more >>

hi I just want to be sure there is no way to do it inside the DB: I have a view which return 1000 numbers and amounts like this: Number Amount 000 234 001 3456 .... ... 999 464 I have only two very long columns. is it possible to re-arra...more >>

Hello, SQL 2000 SP3a EE running on Windows 2000 server. Instead of doing this select f_docnumber ,CONVERT(datetime, '1/1/1970') + a.f_entrydate as date FROM f_sw.doctaba a I am trying to do something like this: select f_docnumber ,dbo.testdate(f_entrydate) FROM f_sw.doctaba The testd...more >>

The following query works perfectly (returning all words on a list called "Dolch" that do not contain a form of "doing"): SELECT 'Dolch' AS [List Name], dbo.Dolch.vchWord FROM dbo.Dolch LEFT OUTER JOIN dbo.CombinedLexicons ON CONTAINS(dbo.Dolch.vchWord, 'FORMSOF(INFLECTIONAL, "d...more >>

Greetings - I'm working with a SQL Server 2000 database via an Access front-end and I'd like to check if the user has a login (via a pass through query I imagine) on the database before attempting to pass the connection string. The user login name will be the same as their PC login name ...more >>

I have a table that I want to disable the identity column that will be disabled across different sessions. I cannot use SET IDENTITY_INSERT table off as it does not persist across columns. I also do not want to create a tmp table without the identity being turned on, populate it and then rena...more >>

Hi, i've created a trace file which shows columns EventClass, TextData, SPID and ServerName. I can save this to a sql table by using profiler, save as, trace table option. If however i try and create a bcp process to do this automatically, it keeps on failing with: SQLState = S1000, Nativ...more >>

modulo (check figures are rounded to nearest 10) I have a couple of ideas, but they seem very heavy in traffic. There are 20 columns in a table, where the values entered are money and need to be rounded to the nearest 10. About 40 concurrent users will generally be editing line by line, e...more >>

Is it possible to write a macro statement using Transact-SQL? Imagine that we have a table named tblA and fields with the almost same name, for example Field01, Field02,...Field20. (I have named fields on that way for the better explanation). Now, suppose that we want to do almost the same u...more >>

hi , the log file of a DB has grown in size. i need to now truncate this file and reset the log file size. the db has been configured for unrestricted file growth by 10% for the log file. how do i do this. rgds, anu...more >>

Hello, I have a table like that: ID | Date | Value ------------------------------------------ 1 2004-12-01 12 1 2004-12-02 13 1 2004-12-03 11 2 2004-12-01 23 2 2004-12-02 21 2 2004-12-03 ...more >>

Hi, As far as my understanding goes, cursors can be used to fill in variables as it iterates through a pre-defined hardcoded query. Would like to know if I can make a dynamic query within a cursor and subsequently pump values obtained from this dynamic query into their respective variables...more >>