I have declared a temporary table (@mytable) in a sp and want to use it within a dynamic SQL statement. It doesn't seem to like it. Complains that the vaiable @mytable is not defined. What is wrong? -- Jonathan Blitz AnyKey Limited Israel Tel: (972) 8 9790365 "When things seem bad...more >>

I am trying to figure out how to recall old AOL IM (Instand Messanger) in the command prompt since I believe that there is no way to do it through windows. If anyone knows how please give details....more >>

I write a trigger on a table and update another table in a linked server. There is an error message . SQLSTATE = 37000 [Microsoft][ODBC SQL Server Driver][SQL Server] Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consiste...more >>

Hi Guys, Can any one help me in solving the issue. The issue is I need to write the SQL command to load a million record from one table to another table but the tricky is destination table has a column called Batch_No in which i need to put the batch number for each 5000 records. I know thi...more >>

Hi, all. I am trying to build a query that returns the following record set. vendor|prod1|prod2|prod3|prod4 ____________________________ Ace|1|2|null|3 Beta|2|null|null|1 tblVendorProfile VendorID|VendorName _______________________ 1|Ace 2|Beta tblProdListing ProdID|ProdName 1|...more >>

Here's the code: CREATE PROCEDURE dbo.LoadStandardSampleFile ( @sampleSourceTypeKey INT, @filename NVARCHAR(255), @productTypeKey INT, @associateProduct BIT, @characterEncodingKey INT, @codepage INT, @batchSize INT ) AS DECLARE @sampleSourceKey INT DE...more >>

Hi, I am trying to calculate a field with a select statement. I have the following query. SELECT dj_number, hend_number, description, '$' + convert(varchar(25),net_cost,1) as net_cost, '$' + convert(varchar(25),list_price,1) as list_price, '$' + convert(varchar(25),net_cost/.66*1.08...more >>

Hi, Can I create a view in a store procedure as below scripts. use pubs go create procedure myTes as create view myView as select * from sales go Tanks ...more >>

Hi, I have a conversion issue in my SQL statement. I have a table Employees(using example here) Emp_ID is Primary Key & has a data type of varchar Emp_ID Emp_Dept 100 Acct 101 Acct 102 FIN 103-A HR 103-B HR 104 Tax 104-A Tax 104-B Tax 10...more >>

Hi, I ran "select serverproperty ('licensetype') and it returned Disabled What does it mean? How and Why don't I have a license? I have 4 proc licenses. Is there a way to switch the license to my 4 proc licenses from Query Analyzer? ...more >>

Hello, is it possible to find out the deadlocks like in Enterprise Manager, Current Activity, Locks/Process ID? The current activity sometime times out. I need to query the database and kill the spid that is blocking or blocked Any suggestions? ...more >>

Is it possible to use an alias for a table name throughout a stored = procedure? I have a stored procedure that receives the name (or part of = the name) of a table as a parameter, but within the stored procedure I = need to read/write to the table. So if the parameter is sent as...=20 @tbln...more >>

Hello, Is there a way to write a query or using Enterpise Manager to find what type of license I have on my SQL Server? Thanks in advance. ...more >>

Can someone please tell me what's wrong? I get the error "Invalid object name '##AAA" when using BCP like this bcp "exec MyDatabase.._AAA" queryout aaa.txt -SMyServer -T -c where _AAA is a stored procedure that looks like this (in MyDatabase) CREATE PROCEDURE dbo._AAA AS BEGIN if obj...more >>

Hello. I am looking for a function to compute the number of years. I know I have 2 options, both which will not work for me. 1) DateDiff(..) This will not work because it only checks to see if the date crosses a year boundry. (i.e. DateDiff ('year', '12/30/2004', '1/05/2005') = 1 ) 2) ...more >>

Scenario... I open up Query Analyser and run SELECT @@connections. This return 1 (let's assume the counter was at zero before I started). This makes sense because Query Analyser has an open connection. I then run my VB 6 application which connects to SQL Server using ADO through OLE DB. My...more >>

Hello again, I am working on an application that would allow users to port schema (or parts thereof) from one database to another. The port is not immediate; schema is stored in the recordset before it is moved to another database. Schema retrieval and re-creation is done with SQL-DMO. ...more >>

I need to export data from one SQL server database and import it into another. Before I can import the data, I need to pad some fields with zeros - some text strings will need to be right justified, with leading zeros (ex. 000000AAAAAA) and some numeric fields will need leading and trailing z...more >>

I've seen something that has said that varchar field lengths are retained in packed bytes. Can anyone for certain confirm or deny this? For example, if a varchar length is set to 30, then the smallest value 30 can fit in is a byte of 6 bits long (32), so the location that stores the length of ...more >>

Hello, I have a db with a PLANS table, and a PLAN_LOCATIONS relational table which simply has a PLANID, CODE field. What i would like to do is to make a view which flattens this out in such a way that the CODE (these are state codes: NY, CT, etc.) are turned into a concatenated list. In ...more >>

I tried this inside a trigger: EXEC @RC = procMyProc @a = 'test', @DT = getutcdate(), @b = @local_var but it (Query Analyser) won't let me 'compile' the trigger, throws a syntax error. I removed the braces, it 'compiles' but throws a 'Could not convert char datatype to datetime' t...more >>

Hi, I am multiplying two decimal values but the result set only returns 6 digit on scale and rest of them are zero. How to get all 12 digit after the decimal point? Sql2k Sp3. Here is the example: declare @x decimal (28, 12) declare @y decimal (28, 12) declare @a decimal (28, 12) set @x = 2...more >>

Hi, I have a stored procedure that takes an optional input parameter. what I would like to do is filter using the where clause if the parameter is passed in (ie is not null) however if the totally disregard the where clasue if the parameter is null (not passed in) I thought I could use a cas...more >>

Hi Elementary question, but I've executed the sp_addlinkedServer, but I'm having problems pulling data from multiple servers??? (SELECT Contact_ID FROM ukpivsat.Live_ED.dbo.Contact) What is wrong with the above? Regards -- Rikesh (SQL2K-SP3A/W2K-SP4) ...more >>

Hello, all. We have a table that indicates the people in the SQL Server 2K db who are duplicates. We'll need to go through a dozen or more tables replacing anyone with that id # with the correct one. However, this will be painful with the current foreign key relationships we have. We'd have to do...more >>

I have a column, ID, that may be null or not null. If null, then the context is that the row represents a default value. Otherwise, not null relates to another table. The Java code calling the stored procedure may be passing in either a null to get the default values or will otherwise pass in a ...more >>

First I have a count of records by day. I need to sum the counts and show the total for all counts with a date less than the current rows date. so I get an output first of 9/1/2004 2 9/2/2004 3 9/3/2004 6 9/4/2004 1 then I need to sum the records with dates less than the current re...more >>

Is it possible to have a trigger fire off when a table is created? Another words I need some way of monitoring table creation. Thanks....more >>

Hello, I have problems to make a request allowing me to extract from the data which contain a given whole word. For example with a SELECT, I would like to find all the lines which have in MY_FIELD the word ' LISA '. But I don't want the lines which contain ' Elisabeth' nor ' lisa' I te...more >>

IS_MEMBER works for the current user, and has the following syntax: IS_MEMBER ( { 'group' | 'role' } ) I need a function that can be used by an admin user to determine whether a specified domain login is a member of a database role. i.e. something like: IS_MEMBEREX('ADatabaseRole', 'My...more >>

Can a stored procedure be dropped if it is referenced/used in other stored procedures, triggers or views ? ...more >>

Is there any way to set the transaction isolation level for an entire database rather than just by connection or transaction? Thank you, Heidi...more >>

I'm still relatively new to SQL Server. I'm trying to convert an existing Access application. One of the requirements is that all of the dates be returned as formatted date strings. The existing Access application actually uses a vba function as part of the query for formatting the dates. Hop...more >>

How do I specify the field type when creating a temp table? (I am doing a Select into statement) Thanks ...more >>

Hi, I am tryind to delete from a table using a Stored Procedure, but I don't know how to do the following: The DELETE STATEMENT must be: DELETE FROM MESSAGES WHERE MSSGID IN (@MSGIDS) The problem is that @MSGIDS always is something like this: 1,3,5,7,11,12. It's VA...more >>

I've got a problem where one of the databases on my SQL 2000 SP3a Server (running on W2K SP4) won't allow any new connections in the morning. Something is happening overnight which is causing this. Every morning we reboot the server and all is well until the next morning. Mostly there is nothing...more >>

I have table1 on SQL server1 and table2 on sql server2, so 2 different SQL servers in different locations. TABLE1(column1,column2,column3) TABLE2(column1,column2,column3) Table2 is copy of table1, except the column3. column3 in table1 is timestamp column, column 3 in TABLE" is varbinary(...more >>

I'm trying to do an UPDATE that involves 2 different tables... here's the query I'm using: UPDATE ArticleTable SET ArticleType=2 WHERE EXISTS (SELECT ArticleTable.ArticleID FROM ArticleTable,ArticleSubSections WHERE ArticleTable.ArticleID=ArticleSubSections.ArticleID AND ArticleSubSections.Su...more >>

Hi I need some pointers please! I'm lost! I'm running SQL Server 2000. I want to do something like this. First here is a bog standard query to look at ------------------------------------------------------------------------------- SELECT ProductID, Start Date, End Date, Status FROM Produc...more >>

Can someone direct me to a good article(s) for performance tuninng in SQL Server. For example, on a day to day routine what are the things to monitor on big DBs?? I have seen people talk about memory, I/o etc. but what specific things in memory, or on Drive or what kind of NT counter shoul...more >>

I'm new to SQL, is there are maximum number of fields that can be defined in a particular table. I have an application that requires almost 600 fields and I am at a point (about 500) now where I cannot add any more.... any suggestions ?...more >>

Folks, I'd like to be able to output to the job history, from my ActiveX Script step. It there a simple way of doing this? Many thanks, Tim...more >>

i have 50000 rows and 32 fields of data in MS SQL, Since the data so important and i have to display it in one big list, i select all the rows with all fields, using comon select * and finished at 9s. then i use query analizer and i browse the table using open menu and it finished not more than ...more >>

Hi All, I have an interesting request. I need to modify an access program to force the users to change thier password every 6 months. I am thinking of creating a trigger on the table that holds the login info (username date time etc) when the user logs in, I created a trigger to load a tem...more >>

Is there a way to restore the mdf without the ldf from a sql backup file? ...more >>

Hello Experts, If I want to permit to a user, to Restore a database or databases, which Server or Database role I have to apply? Thanks in advance....more >>

Hi, Does anyone have a script which kills all the processes using a particular user DB? I would like to (part) automate an emergency DB restore task for future use, just in case. Thanks in advance, Richard --- Richard Quinn MCSD, MCDBA firstname.lastname@ieee.org...more >>

Hi I am using a function to split the comma separated parameters to insert in the child table. Here is the function, CREATE FUNCTION FnSplitCode (@param varchar(7998)) RETURNS TABLE AS RETURN(SELECT substring(',' + @param + ',', Number + 1, charindex(',', ',' + @param + '...more >>

Hi, I have a table in my database that logs activity in our system. The common profile is that we undergo huge bursts of activity, followed by periods of several hours of relative quiet. As a result the datetime info for an average day contains activity down to the millisecond level. I nee...more >>