Hi, Is it possible for server A to call store procedure that reside in = server B? I've a job in server A, where upon successful completion of = it, it'll need to run the sp in server B. thx in advance...more >>

hi i have made this transaktion not in the DB but just as a sql query it is working fine but i was just wondering if it could bee done more elegant ?? Begin tran declare @la NVARCHAR(32) declare @lockTable TABLE (ok bit,laastAf nvarchar(32)) select @la = laastaf from ...more >>

This is an example: CREATE PROCEDURE Test AS INSERT INTO tmp1(num1, num2) VALUES(12, 23) GO When I execute this proc from VB: With Adodc1 .Visible = False 'Our connection string uses OLEDB version 3.51 .ConnectionString = tmpConnStr .RecordSource = "EXEC [DIARY].[dbo...more >>

Hi everyone How can I search database records in table1 such that the query returns the matched records and their percentages to which they are alike. Actually the site administrator wants a search facility such that whenever he wants to register a new customer, fill out the whole reg...more >>

I have a identity column, it starts with one and the seed is 1. However when I insert values, the values are incremental but not sequential. There is will be a gap between the identity value inseted into the table. Eg:- 1st insert statement, the identity column will have the value as 1. Then the 2...more >>

I have 1 table with following columns: Part number Product code qty ref I want only one entry for part number + product code and delete extra records. I tried this way but I'm getting nowhere: select * from table1 group by part_number, Product_code Please help. thx ...more >>

Hello, I'm attempting to put together a query in T-SQL for a system I've developed and having trouble. If anyone could help with this I would greatly appreciate it! The Database Tables: CREATE TABLE [dbo].[People] ( [PersonID] [int] IDENTITY (1, 1) NOT NULL , [AccountID] [int] NOT NULL...more >>

The following SQL Statement ..... returns the resultset as the bottom of this post. ---------------------------------------------------------------------------- Select '27BARCODE' as 'LOC_CODE', Rack, PartNumber as 'Part', Location, Line as 'DEPT', Count(*) as 'OH' From V_BC Where Status = 'IR'...more >>

VB6 , sql 2000 After truncating a table and trying to enter data into an empty table I get a duplicates ID on the AccountID (a no duplicate indentity clustered key). How can you get a dup from an empty file. I just updated from SQL 7, and it worked on it. cn is an ADODB.Connection cn...more >>

I am now trying to figure out how to design the Addresses table to work in such a way as to allow an address to be used for multiple different types. For example, a customer has only one address which they are using for both their shipping and billing address (billing address being the addre...more >>

Basicaly, if I run this procedure on a database, it will defrag all the indexes on that database. I need some suggestions on this code. What do you think? Is it good or bad? thanks create proc sp_defrag @table varchar(100) = '%%' as set nocount on declare @db nvarchar(100) declare @...more >>

Hi I have the foll please help m select sum(quantity + overage - notdeliv - short - damaged)INTO pos_temp from OPENQUERY(PROGLINK, 'select quantity,overage,notdeliv,short,damaged fro history where id = "s0912070"') I am getting this error Server: Msg 8155, Level 16, State 1, Line No colum...more >>

I know you can Use the WITH ENCRYPTION option in procedure so when user use sp_helptext <proc name> , then nothing will show up. But if I'm an owner of proc, how do I unencrypt my procedure now? ...more >>

i want to select like below(using english): select * from table where value startwith("something") How can i do that? Thanks. ...more >>

I have a stored procedure that queries records from a table and for each record, it calls an external function in a COM object to parse the record information. The stored procedure works fine except that every time that it's running, SQL server took 100% of the CPU causing an unacceptable slo...more >>

A colleague asked me how to do this, and the only thing I could get to = work required using a cursor and dynamic SQL. Is there a way to write = this without cursors and/or without dynamic SQL? =20 Here's the code I used (works): -------------------------------------------------------------...more >>

I would like to create a trigger: CREATE TRIGGER updateSkladisca ON [dbo].[skladisceIzdelek] FOR INSERT, UPDATE,DELETE AS and insert the effected row into table history and insert the action description (update, insert,delete) something like this (column are: id,name,reason) 1 simo...more >>

I have table like this: fld1 fld2 1 'aaa' 1 'bbb' 1 'ccc' 2 'ddd' 2 'eee' I try to use ONE select statement to get this result: 1 'aaa,bbb,ccc,' 2 'ddd,eee,' how can I do this? Thanks! Jason ...more >>

Hi, I have a select statement that have a case statement in the where clause. Logic: If user provide Account number and Sub Number then search for that particular record, otherwise select all records. USE Northwind SET OrderID = '' SET @ProductID ='' SELECT OrderID, ProductID FROM [o...more >>

I try to do this in Query analyser: CREATE TRIGGER SetRealEnd ON [dbo].[Permissions] FOR INSERT, UPDATE, DELETE AS update permissions set PE_RealEnd = (select top 1 B.PE_Begin - 1 from Permissions B where B.PE_SNID = A.PE_SNID and B.PE_Granted = 1 and B.PE_Begin > A.PE_Begin and B.PE_Begi...more >>

Here is my problem. I could run the same T-SQL commands from query analyzer in few seconds whereas if I run it as a stored procedure it takes more than 10 minutes. Has anybody faced this type of problem before? I'd really appreciate any answers. Thanks, Jyothi...more >>

Hi, I have written a stored proc that does copy (inserts&deletes) data from one server to other server using linked server. I have constructed a while loop to control the number of records to copy. With in the while loop I will begin the transaction and commit the transaction record by reco...more >>

This sounds stupid, but I cannot find a way to just get today's date without the time to pull a date range... I have tried convert (datetime, GetDate(), 112) and some others, all to no avail... isn't there just a date function that doesn't include the time? ...more >>

Forgive my ignorance - SQL novice.. Is there a quick and simple way to select the items in a column and return the result as a single string? In other words: I have a simple table: ID Name === ====== 1 John Doe 2 Jane Doe 3 Barry White I would like to perfor...more >>

Hello there I'm working on prosedure of getting data from other system For this i need a procedure of clearing all data in my database The problem is that this data is related to many other data on another tables or having problem of triggers. The main error is the conflicting between the...more >>

Hi I want to send a View by mail to a number of people as a spreadsheet I think this is possible however the universe I am working in uses Lotus notes and I am not sure if is possible to send email this way asd it asks for and Outlook account I would be grateful to know if it is possible ...more >>

Anyone know how to import a text file to a temporary table within a DTS? I'm currently creating the temp table with a SQL task and then trying to point the text file to pump into that table. Problem: the temp table currently isn't an option for the destination. Even after I've manually created...more >>

When createing a new SP, the is a template i EnterPrise Manager, can you change this, and if so How??? + We want to forfill a coding standard, can u put a trigger in the server somewhere, so you can run a source formater on a SP when this is added to the system Please help me Med venlig hi...more >>

Hi, How do I revoke execute permission on a procedure? ...more >>

Can a view contain a variable? I am trying to create a view for use in making a Word mail merge label page, but I need a way to break it down so I do not print labels for every company in the database. Here is what I tried. CREATE VIEW dbo.DriverName AS SELECT dbo.DriverTable.DriverLa...more >>

Hi everybody, in my database, there is quite a number of user tables. Each of them has four columns apart from the "real" data: CreatedOn, CreatedBy, ChangedOn and ChangedBy. What I want to achieve is that on every update or insert in one of my tables these fields get updated/set appr...more >>

I have a column status that I need to sort by. There are different levels of status, FRD, CON, SUP, PAT, etc.. Is there a way to specify a sorting order that isn't alphabetical, such as order by status ( frd, sup, pat, con) ? Jon...more >>

Hi, Are there any simple query statements out ther to calculate daily balance of several entries: Source Date hour 03/25/04 1.5 03/25/04 0.75 03/25/04 2.0 03/24/04 5.0 03/24/04 2.0 as Presentation Date hour daily balance 03/25/04 1.5 4.2...more >>

Hi, Can anyone give me some examples on a proper design for a security model for a web application? We have to restrict particular functions based on the user but to make it easier to manage, I'd like to user a user/group model. Any ideas? Khurram ...more >>

Hello All.. today I was given a task to take a previously ASP hosted site, and make it "installable" in client locations. I've got most of the problem licked.. but I'm stuck on how to deliver a the SQL database. There are ~ 75 items (tables, views, SP's) in the structure, and it's not a huge de...more >>

Please Help! I have a column of type CHAR(255). However, a situation has arose in which it needs to be larger. I would like to able to change the type to TEXT, but I can't find any way to do this. What can I do? I'm using 6.5 SQL Thanks for any help. ...more >>

I have a field in a table that contains date data in the following format: 20040301 what is the best, low impact method for converting this to a useable date field? ...more >>

if i had two variables, one storing a uniqueID for a particular row in a table, and the other storing the name of a column in that table, is it possible to obtain the value of that particular column in the row?...more >>

I would like to get a query which does the following I have 2 tables with the following design: Table name: t1 Fields in t1: a, b, c F1, F2 Table name: t2 Fields in t2: a, b, c F3 I would like to do a join for t1 and t2 where their common fields will be a, b and c. and with a res...more >>

Hi Everyone, I have two tables: tb_1 and tb_2 Create Table tb_1 ( id_tb1 int,--PK name varchar(16), address varchar(64) ) GO Create Table tb_2 ( id_tb2 int,--PK id_tb1 int,--this column is FK to tb_1.id_tb1 --I need a second column to reference to the --same col...more >>

Hello and thanks for Help in Advance I am trying to remore some garbage from text fields I have some data that looks like this... I would like to remove everything after the space..... 4004 . 6005-1 .11 4604-1 .3 5604 .4 4804-1 .17 5404-1 .4 Here is a script which contains the replace c...more >>

I'm setting up merge replication between a couple of SQL 2000 servers using DMO and want to use the automatic rather than scheduled option - which EM lets you set when you configure it manually. However, I cant see in DMO what setting I have to make - even when dumping the properties of an EM ...more >>

I want to get the backup set name from a database backup file. How can I do? Thanks, Bruce ...more >>

Hi all, Is there a way to remove spaces from a char column using an sql statement? I have a column in a database table filled with numbers like '34512 34 3456 3'. What I would like to do is to translate these strings to numbers in like '345123434563'. In SQL Server Books Online I only found...more >>

I'm writing a sproc and attempting to Insert a record using info that user has passed in and info from current record. Is there a way to use a temp table field as source for values of the Insert statement. I've tried the following and rec'd error that Select statements aren't allowed in Inserts ...more >>

hi all, I have the simplest possible query SELECT col_a, col_b FROM my_table WHERE col_a IN (1, 2, 3) AND col_b IN ('alpha', 'beta') is it possible put this SQL statement in a storproc with two parameters @par_1 and @par_2 that contains the two arrays of integers (1, 2, 3) and nvarchar...more >>

Hello I am programming an application in wich I need and embedded database. I am looking into either turbodb and vistadb. I would like to know if msde could be run as an embeded db instead of a service ? and if msde and sql server could be installed on the same machine ? thanks ! ...more >>

I sure hope someone can help... I have a program that generates a "WHERE" clause based on user input, and then is sent to a SQL stored procedure where a dynamic SQL string is then executed with the user- defined "WHERE" clause. This is the problem, I have 91 records for Vendor ALCOA HOME ...more >>

Hi. Any help with this would be great. I have a query that lists a bunch of accounts, when they trained on a medical procedure, and where. Some account trained on more than one date and/or at more than on place. I'm trying to show just the earliest date on which they trained which I was ab...more >>

Please I am execute store procedure and present locks, when i look spid in enterprise in the column "wait type" present state NETWORKIO What happend in this case Thank you Andres ...more >>

I have two separate tables in two separate databases. One the databases contains the user info that I need, while the second table contains their production. I need to insert data that contains the agent info from the first table and summaries of production from the second into a third separate ta...more >>

Is there a way to use variables in a where clause. I would like to use two variables in the where clause. One for a literal and the other as a column name. Can this be done? If so please explain. Thank you kindly....more >>

Hi, Problem in importing the data from .dast file to sql server by osql, bcp and bulk insert using the sql server client. I am working in my machine, call it as testuser (machine name). And sql server is located in the machince, call it as testserver (machine name). OSQL Query: ...more >>

I have used a trigger to insert a new record in an employee vacation request table and mark it as current request. Now I am trying to mark previous requests as non- current. How would you recommend proceeding? Create a store procedure and execute it from within trigger? or just append the u...more >>

Hi, can somebody help me figuring out a little of logic for this query... Lets say i got a table with 3 Columns ( Product Name,Date,Total ) i need a sproc where i pass a @startdate and an @enddate where it will list me grouped by Product, the Total sales for each product ( so far really easy )...more >>

Hello, The environment is very simple. I was running a SQL script in SQL Analyzer on my desktop machine against a database which is on another server. The script fetches data from one table, looks up for keys in other two tables, and finally, inserts one record in the final table. It is a lon...more >>

Hello, As I understand it there is an undocument store procedure that moves a profile trace file on the hard disk to a database table. Can anyone shed any light where it is ? Thanks Peter...more >>

Hi I have a column named zip in a table named customers now all zip codes entered into this column have characters either no spaces or spaces in various locations I would like to change the zip data so it is 3 or 4 characters long a space then 3 characters so 'mk42 oeg' if anyone can ...more >>

In order to improve performance for a SQL Server DB which resides on an internet server, I am looking for a method in which I can refer for the FROM clause to a local recordset instead of a table. Some large tables have to be accessed more than 1000 times within a loop. Therefore I want to creat...more >>

-2147467259 (80004005) Microsoft OLE DB Provider for SQL Server [DBNETLIB][ConnectionRead (WrapperRead()).]General network error. Check your network documentation. I am getting the above error when I try to run: sp_setapprole 'RoleName', {Encrypt N'Password'}, 'odbc' I am callin connection...more >>

Hi reader I developed Oracle8i application for a long time. My client now wants to change to SQLServer for using both of them. So I am meeting a problem about outer join in the query structure language with Oracle8i. Because, the outer join in Oracle8i DBMS has syntax is (+,-) follow...more >>

I am testing the performance of a VB-SQL Server DB over internet DSL connection. To my surprise, there is no diference in speed at all for application of VB inline SQL statements compared to application of the same with stored procedures. So far I've done the tests with openForward, ReadOnly cur...more >>

In SQL Server I am creating a table for a database and I have two fields Type and Description. The Type field is an 'int' datatype and is the primary key. I am writing asp code to add a row to the database using the details from a textbox on a web form. I only want to have to enter information in ...more >>

Hello I wrote a script for inserting some data. If I run the script, i get the error that a string can't be converted to datetime. The string is 'Mar 18 2004 10:21AM'. If I try the same insert with 'Feb 18 2004 10:21AM' or any other month different than march it works ?! Could somebody please...more >>

Hi. I want to change my database name with sp_renamedb..while doing so database name gets changed but i also want to change names of physical files with new one..plz tell how to do tha ...more >>