Hi, On delete of a record I would like to check if a certain record in another table is present, if it is I would like the delete to be cancelled. Is that possible in a trigger ? How would that trigger code look like ? (I cant use relationship integrity) Lasse ...more >>

how can i find out all the databases in a mssql server with visual basic code? ...more >>

I understand proc cache is used to store the proc plan. what is data cache is used for. ...more >>

what is the diff between a DTS and a stored proc at core level. Does DTS also has a cache plan. ...more >>

Is there any adv. of extended stored proc over DTS. Which one should be used in which scenario. Thanks a lot. ...more >>

Does anybody know which system table stores the DTS definition....more >>

Which system table stores the transaction logs. I was looking for syslogs. ...more >>

Hi everyone, Supposing I was passing three input parameters to a stored procedure eg @value1, @value2, @value3. Now I want to be able to check the length of the input parameters and insert into a particular table depending how long the values in them are. Is this possible?? W...more >>

Hello how do i get the following result without using that INDENTITY(1,1) thing for rank: Rank Player Points 1 B 10 2 A 7 3 C 1 when i have this: CREATE TABLE #Results ( Player char(2), Points int ) ...more >>

Hello i have a tricky thing you guys might solve in notime....... tables dont look like this in real life, im just gonna show the problem with a small example...... I have 2 tables, first one is Results were all results go in, second is "Penalties" table, so if a team has "penaltypoints" ...more >>

Hello, How can I do to check that all my views are working... Any scripts around that do that? Regards Andrew. ...more >>

If I have a table, Id Product_Name 1 Apple 2 Orangle 3 tomato 4 cherry I declare a varchar(255) @list and how to make a @list store "Apple/Orangle/tomato/cherry" Thank You...more >>

Hi, Just bumped into something and I do not know whether my assumption is correct. Here is my select, DECLARE @POS INT DECLARE @ID INT SELECT @POS= -1, @ID = -1 UPDATE [TABLE] SET [position] = @POS, @POS = CASE WHEN @ID <> [oid] THEN 0 ELSE @POS + 1 END, @ID = [oid] ..... As y...more >>

I have a table 'PT' with columns trans_id, code, amount, ref_code. The data is in this format : trans_id code amount ref_code ============================ 100 1 8 null 101 5 7.5 null 102 24 8 ...more >>

I use the SQL Server sample source code and run it in another networked computer to connect to SQL Server, but it won't connect. I have the instance of SQL Server running, i've already set the protocols and alias on the SQL Server Client Network Utility and also the protocol on the Server ...more >>

Hi, A colleague has created a SP instead of a UDF to calculate the date difference between two dates which take into account the different start times or working hours for different part of the week (eg Mon-Fri 0800-1700 weekends 1000-1500) worked in our organisation. The simple test that w...more >>

What is sp_reset_connection used for? I found no documentation on this stored procedure in sql server. Thanks, Paul...more >>

From program code, both for the local machine and for other machines on the network? Thanks in advance, Jan ...more >>

Hello, I did a Procedure to find where a specific string on the database is. And I will like to make it a little bit better, to not display the empty tables. Any ideas? -- This Procedure is to find strings only -- Change the DATABASE_NAME to your database name -- Change the Andrew Retzla...more >>

Hi all, Without using data shaping in ado, how to retrieve hierarchical recordsets from MSSQL 2000? I find that data shape running quite slow in my apps. Any assistance is appreciated. Thank you Tom S. ...more >>

Hi All, How i can close all current connection to a database. I want to close all connection by T-SQL and execute RESTORE DATABASE command. Thanks. ...more >>

Hello, We have developed a ASP page with ADO transaction, like: -------------------------------------------------------------------------= -- dbConn.BeginTrans =20 dbConn.execute(sgSQL) if dbConn.Errors.Count > 0 then Response.Write "ERROR : " & Err.description & "<BR>" Err.c...more >>

Hi there, I am wanting to find all tables that contain a value in any of the column. or search for a value in all tables. i dont know where to start can anyone help. Thanks Stuart ...more >>

I have to convert an ORACLE DB in SQL Server. In the original ORACLE DB I Have the same table defined for more then one user, every user can create his own table with his own structure and I can make each user see only his data. At now I tried to do the same thing with SQL Server but each table I...more >>

sql2k sp2 proc1; Inserts into table1 & updates table2 all within 1 transaction proc2; execs proc1 as many times as input using a simple loop and counter. proc3; selects from table2, table1, table3, table4 using lots of sums(val1 * val2), isnulls, case, joins, etc. and selecting from...more >>

I know how to use CASE switch in SELECT part of the query, but I wanted to know if it can be used in the WHERE clause as well? Thanks. ...more >>

Hello, I would like to know if there is a limit to the number of predicates in the where clause of a TSQL select-from- where statement. I'm planning on building a dynamic sql string that may have many predicates in the where clause (ie: select ssn from students where ssn = ssn(1) or ssn =...more >>

Hi I've a table with following information and now I want to delete one row of duplicated rows by a select statement,how is it possible? table1 f1 f2 f3 ---- ------ ------ 1 qwe 34 2 sdf 3 fg 5 1 qwe 34 5 a 6 2 sdf desire...more >>

I have a requirment to migrate Watcom database to MS SQL. Does anyone know of a process or method to accomplish this task?...more >>

Hello, I am a developer on a business simulation software that generates around 2 million rows of time series data used in reporting. I have noticed that the first time i request the data from sql server 2000 for a report, it takes about 2X longer than the next request. I have been researchi...more >>

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'asp_FaktureraAbon03'. The stored procedure will still be created. I script all objects Why do I get this when I create a script for my database, in the script option I set "Generate scri...more >>

Hello everybody, I have to execute some Maintenance into my server, but i have some databases acessed by web and when i try do start the maintenance the jobs fails because some users are using my databases. I need to: 1) Kick the users logged in SQL 2) Restrict access 3) Start the maint...more >>

how can I determine if a date time stamp like this 028327-37655-5062037 is more than 30 days old ? ...more >>

Hi guys, I need some help. Is it possible to do a Sum of difference of motor run times in a query. Lets say I have two fields Datetime Value 2003-01-01 00:00:00 1 2003-01-01 00:00:59 0 2003-01-01 01:01:39 1 2003-01-01 02:34:00 0 2003-01-02 00:00...more >>

I was wondering if anyone could tell me what the correct syntax would be for using an input variable for a table name in a stored procedure. This is what I came up with and it doesn't work CREATE PROCEDURE FundSymbolLookup @SymbolName varchar(5) AS DECLARE @TableName varchar(10) SET...more >>

Does anyone have a comprehensive list of ER Tools that can be used to design MSSQL databases and will create and update the database as well? SQL EM doesn't give enough freedom. To simply add a table or a column, you can't just type its name now, and its attributes later. I'd like something mo...more >>

Does anyone know how to use the cdonts / cdosys objects to send mail form SQL Server, with attachments. I'd ideally like to create a dts pkg to send data to an excel file, then email the excel file as an attachment to an end user. ...more >>

what query can i write to retrieve all indexes on all user tables? i am using sql server 2000 ...more >>

I am using the Process Info tool in the SQL Server Management section to check up on failed process and processes that do not close. I see the error and which database is causing the error. But, when I select a process and open it I do not get the Process Details information. What can I set to a...more >>

I want to export a query to csv file. I try to use bcp command, also I found a T-SQL in SQL online help... bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout Authors.txt -c -Sservername -Usa -Ppassword the SQL QA return a message to me "Line 1: Incorrect syntax nea...more >>

I've got an Access application that exports data to a SQL Server DB and in doing so, creates a TEMP table with the user's network ID as the owner. for example, the table 'JSmith.HRData_TEMP' will be created. But when the same users runs a stored procedure from the same Access session, sh...more >>

Hi all, Trying to use a format file to skip the first column which is identity Column but, I keep getting an error. I followed the instructions at. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_impt_bcp_0fqq.asp The error is: Server: Msg 4866, Level 17,...more >>

Hello, I like to create a database in the default SQL server location, but specify a size. I tried CREATE DATABASE SGMTEST ON (NAME = SGMTest_Data, SIZE = 10, FILEGROWTH = 10%) LOG ON (NAME = 'SGMTest_Log', SIZE = 5, FILEGROWTH = 10%) but this won't work (missing...more >>

I would like to create a TSQL that would apply the trigger listed below by changing the table name and primary key name for each table with a primary key and table names beginning with MRR_ in a database? Please help me with this trigger. Thank You, Joe CREATE trigger tI_MRR_...more >>

Is there a way to set permissions on a specific table to prevent an 'sa' from accidentally deleting records? Even though the 'sa' is in a Public Role, denying delete permissions for Public members doesn't seem to get it. I'd like to be able to selectively turn this on and off as needed. Regard...more >>

Is it possible for files to be stored in a SQL 2000 database instead of a file directory? If so- what do I need to know to begin my search for help in books on line? Is it called something? If I can store files can a search be done on the text in them? Thanks for any information!...more >>

Hi All, Is there any possiblity to transfer Long data type value to SQL Server?. I'm using DTS package to transfer data. I have to use only SQL Server Tools or DTS package. -- Thank you, With Regards, A.chellammal Developer ...more >>

Does any body know which system table stores he plan of stored procedures. TIA. ...more >>

I have two queries that perform the same function, but one returns a more optimal plan, while another yields slightly better i/o (At least I think it does). Query 1 Results: Table 'Transactions'. Scan count 93, logical reads 281, physical reads 0, read-ahead reads 0. Table 'Visits'. Scan cou...more >>

Declare @lclaim table (age int constraint c1 check (age >35))...more >>

I have a SQL Server with over 32 SQL Server databases. I am trying to create a view that displays trigger names and state (off or on) is that possible or does any one have any recommendations on what table I should select from in each database?...more >>

I'm getting a headache thinking about this. I'm looking at the triggers in a big ugly database and I wanted to make a table of the triggers and what their dependencies. So I used sp_ depends to get the dependencies and did inserts like this: INSERT EXEC sp_ depends @tgrl -- @tgr is the tri...more >>

Hi, what is the sql to capture data of last 10 business days (or the data from the last 2 weeks i.e. most current 2 weeks). please help. thank you. create table #t(dt datetime) insert into #t values ('1/12/04') insert into #t values ('1/11/04') insert into #t values ('1/10/04') i...more >>

I have a field representing the second line of an address where the whole city, state and zip+ four is concatenated into one varchar. I would like to break the zip and + four data into a separate field. Is there a way to do this with T-SQL? I can consistently search on the zip part with a like expre...more >>

I need to return a qry in QA that produces the following and am finding it difficult I am new at this so I may not put it as clearly as I should. The table holds ping responses from an iis box to its nearest router and I am trying to return uptime of iis server and some statistics. Return a qr...more >>

I am trying to do a simple table update. What I want to do is read one table and update the contents of that table to anther table. This is the statement I am trying to use: UPDATE QMBOMWhatIfCost SET QMBOMWhatIfCost.WIBOMOperationCost = InvWhatIfCost.WhatIfLabCost,QMBOMWhatIfCost.WIBOMM...more >>

How do I find the name of the current database ? -- Michael Tissington http://www.oaklodge.com http://www.tabtag.com ...more >>

How can I create a composite primary key on a table variable. TIA. ...more >>

I have structure of my select statement like this: SELECT TABLE1.*, productID=( SELECT ID FROM products WHERE...), productName=( SELECT Name FROM products WHERE...) FROM ( SELECT .......INNER JOIN..... )AS TABLE1 Because the select statement (with many joins and conditions inside) for p...more >>

Hello, all. How can I get a "1 up Number" using a SELECT. Say I want a column from my query that simply is a incremental counter like the following: SELECT fldl, ??????? as fld2 from mytable... fld1 fld2 data 1 data 2 data 3 etc where fld1 is a field on my table,...more >>

Hi All, I have imported data from an excel spreadsheet into a Comment VARCHAR(4000) column. I have noticed in a VB.Net Application that it pulls in the field's data and present is a strange character. I'm thinking it is a return character of some type. It's something like "|||" accept the thre...more >>

I am trying to combine two text files. The HEADER FILE is created with parameters that will be used on every finished text file. The BODY FILE has conditions for each customer. This does not work... Set @sysCmd = @cHeaderFile + @cBODYFile Exec Master..xp_cmdshell @sysCmd Thanks. Lyn...more >>

All tables have about 100000 rows each with indexes on joins . I can't eliminate the like which is doing a scan from a Full name to match a similar last name . Please help query really bogs down under 10 concurrent user SELECT A.KEY_PROSPECT, A.NME_PROSPECT_LAST, ...more >>

an update question. If I'm updating one table and using mulitple tables in the from clause what locks are placed on the tables in the from clause? IE: update tableA set tableA.col=b.value from b where b=value2 what lock is on b for the transaction?...more >>

Hello, I got this error when I tried to create a clustered index on a view. Index on view 'view name' cannot be created because the view requires a conversion involving dates or variants. I've been able to create clustered indexes on other views with the same datatypes The table ha...more >>

Hi - I am doing a BCP of a daily file (about 2.7M records each) for an entire year into a table. I use a format file and the rest is pretty vanilla - there is an auto-number index column, but that's about it. Anyway, as I started this, everything was running fine. I've now gone through about ...more >>

I have two tables - tbl_Source and tbl_Destination. Both have the same fields and they have identical primary key pk_field. I would like to create a trigger on tbl_Source such that -- - On INSERT into tbl_Source, do the following: Insert the same record into tbl_Destination if tbl_Source....more >>

can anyone give me a query for following requirement To find foreignkeys starting with PF_ and create a script for the creation of the same. Primary target is to just get the FK name, FK table, referencing tables and columns on which it is created...more >>

How can I convert a given week back to date using SQL? Example : 015 in 2004 (week 1 day 5 - week starts on Monday) = 2 Jan 2004. Where can I find the syntax reference for the mentioned? Thank you....more >>

Question by example: Lets say I have 2 tables: create table #t(f1 char(2),f2 char(2)) insert into #t(f1,f2) values('a1','b1') insert into #t(f1,f2) values('a2','b2') create table #u(f3 char(2),f4 char(2)) insert into #u(f3,f4) values('c1','d1') insert into #u(f3,f4) values('c2',...more >>

Hi We are running SQL Server 7 and need to remove an identity column from a very large table (6.5Gb) via enterprise manager. It eventually runs out of log space due to transferring the data from the original to a temporary table. Is there any way of removing the identity column quickly ...more >>