Hi! I'm wondering is there any simple way to achieve the following function call in SQL Server. The sentence to translate is (Oracle syntax): to_char(rownum, '000') rownum: number of the current row to_char: formats a number (the 1st param) according to the format defined in the 2nd p...more >>

I have a web site on a remote, shared host with a bunch of individual Access databases. I want to upsize my databases from Access to MS SQL and have some questions. I update some of my Access databases through web-based content management systems. Other databases I update locally (through a ...more >>

Is Microsoft full of #*$#*% (again) or am I badly misunderstanding something? Quote from Microsoft's T-SQL doc: > INSTEAD OF triggers are executed instead of the triggering action. > These triggers are executed after the inserted and deleted tables > reflecting the changes to the base table...more >>

When a row gets modified and it invokes a trigger, we would like to be able to update the row that was modified inside the trigger. This is (basically) how we are doing it now: CREATE TRIGGER trTBL ON TBL FOR UPDATE, INSERT, DELETE as update TBL set fld = 'value' from inserted, T...more >>

When I import data I first import it from a text file into a table of it's own, then using some logic insert some of the records into a permanent table. I am considering having the table that the data from the text file is placed in being there all the time and just clearing it out after I do ...more >>

I'm getting a type mismatch error on the mid(strRecord,1,1)="H" line. I used to do this all of the time, but I haven't done any VBScript for awhile, so I'm sure I'm forgetting something. While not objResults.EOF strRecord=String( 333 ,32 ) IF TicketID<>objResults.Fields("ticket").Value th...more >>

Is there a way to get the diagram of the MASTER Db in MS-SQL? When i right click on Enterprise Manager that option is not available but it is available in any other database (Model Db acts the same). Thanks, Gent ...more >>

Hi All, I am accessing a linked server (also a SQL Server) from a stored procedure. There is an insert statement that I run on a table in the linked server. This statement causes the Internal SQL Server error. But if I run the insert statement separately from Query Analyzer, it works fine! Al...more >>

Hi all! Are there any other way than using rights or Triggers to prevent a DELETE or an UPDATE on a specific column. The "problem" with rights is that they dont apply to all DB-users The "problem" with triggers is that they generate lots of extra SQL-code I would like a solution somethin...more >>

Greetings all, I am a network security professional rather than a MS SQL admin, so I apologize in advance if this is a bit of a basic question for this list. I also cross-posted this to microsoft.public.sqlserver.server, so sorry if anyone's read it already. I know an admin setting up a SQ...more >>

Using Query Analyzer, I can right click on an object and select "script object to new window as create" and I get the text of the object's definition (schema). Can I get same result from command line, i.e., from osql, I can get text output for the definition of the object (something like defnco...more >>

First posting to the group. I have received a lot of valuable info from you guys. Now, an OT question: What's a good tape drive to perform unmanned weekly backups for a Windows XP Pro box running SQL server 2000? -- Joel Farris | AIM: FarrisJoel ** Their Web. Your Way. http://getfiref...more >>

Hello I am tring to figure out why our SQL server is a bit sluggish from time to time. It is running a dual XEON, with 2.5 GB RAM, and a fast SCSI I/O sub system setup as follows. OS, mirrored 2 drives SQL DATA 16 HDD RAID 10 SQL LOG 4 HDD RAID 10 SQL tempdb 4 HDD RAID 10 OS = wi...more >>

would someone me so good as to help me out with the script for a basic customer table ? , usualy fields, userid (primary key) name, address, creditcard number, card type, phone number, email address? TIA ...more >>

I need to write a stored procedure to verify that a table exists and also that the user executing the stored procedure has access to the specified table. Any user can call this publicly available procedure and pass a database name, an owner name and a table name as parameters. The procedure ...more >>

Hello all! I have such question to all of you. I have some tables linked from MS SQL Server 2000. Is time of processing query based on these linked tables from MS SQL Server 2000, faster or slower than the time of processing the same query based on tables, which are not linked but imported ...more >>

HELP!!! I am trying to fine tune or rewrite my SELECT statement which has a combination of SUM and CASE statements. The values are accurate, but the query is slow. BUSINESS RULE ============= 1. Add up Count1 when FIELD_1 has a value and FIELD_2 is NULL, or both have a value. 2. Add up Co...more >>

I heard that you can store photos with the row in the new SQL Server (a photo data type?). Is this true? If so, is there a speed advantage if you have an e-commerce platform with thousands of products? Help appreciated. Thanks, Frank *** Sent via Developersdex http://www.developersdex....more >>

Can someone help with this syntax? I have a non-sensicle example below, but it illustrates the problem if you copy/paste into QA. ********************************** use pubs go update authors set address = 'some address' from authors a inner join authors a2 on a.zip = a2.zip -----...more >>

I am new to sql and very familiar with access. I am using a very large database(130M records) in ms sql2000 and think I need to front end it with access for reports and forms, etc.. I have some questions: 1) Is there a way in SQL to prompt a user for input at the running of a query like the ...more >>

Hi i have created a new database and a new linked server that points to an AccessDB using an ODBC DSN. Now inside that new sql db i have create i need to created a new view so i open EM went to views and paste the following select * from openquery (AccessLinkedServer,'select * from mytable...more >>

Can you call a DTS package using VBA (specifically from Excel). I would like to have a command button to launch a DTS package that extracts data from the spreadsheet to SQL Server. Today I have to open up Enterprise Manager to do this. Any code examples would be great too if this is possibl...more >>

I'm using SQL Server 2000 and yesterday I seem to have lost my diagrams. Not the diagram icons themselves, but the data diagrams you see when you double click the icon. I tried reinstallling Enterprise manager, unregistereing and registering the database without success. Any help appreciated...more >>

I am using SQL7 Query Analyzer. A simple select * from myMLSview and then I save results as a .csv file has the commas messed up in quite a few places. The data is messed up before I save it to the .csv file. So there are blank spaces being added here and there causing them to read as 'add comma...more >>

Im trying to recover my database using the mdf and ldf files. I dont have any backup and i have recovered two of the mdf files using a tool which "discovers" deleted files after hard drive formatting... It sounds cool, isnt it...:? :( Obviously, i get a "suspect" message when the server star...more >>

I have a SQL Server 2000 database and want to now split up the tables onto seperate file groups as well as some indices. How do you breakup an existing table to move it from one filegroup (Primary) to the new filegroup ? Thanks. Craig ...more >>

Hi all, I need some help to access an SQL db on another machine. I am using VB.NET and remoting to make a client/server connection...although I don't think this is relevant to the question. I have been asked to help with a small db project that will reside on our office server and have a...more >>

Hi all, I am pretty new to dB programming and a have a few questions which I will put in separate threads. I have only coded as a hobby and some limited work projects. I have recently been asked if I could help implement a simple help desk for work. This has only been running for a few week...more >>

Hi, I have the following query: SELECT p.employee_code, p.employee_name, CONVERT(VARCHAR(12),t.tran_date,101) AS TranDate, CONVERT(VARCHAR(12),t.post_date,101) AS PostDate, SUM(tobill_hrs) AS TotalHours FROM tat_time t, hbm_persnl p WHERE t.tk_empl_uno = p.empl_uno AND t.tran_date BETWEEN '...more >>

When I create a DTS to export a text file from a table, if I click on the Define Columns button and then Populate from source, then execute, it changes one of the types to not quotable and the size to 19, which in turn is changing the file width. The field that it is changing has a numeric data...more >>

I have two servers: progress as transational server and mssql as warehouse server. I did DTS that "pumps" data from progress to mssql (via ODBC). Copying the data has to be done once a day, but sometimes there is a need to do it on user's demand. I'd like to ensure that I properly understoo...more >>

I am in a situation where I need to get a copy of test database that is on production server running MSSQL 2000 Standard to my local machine running MSSQL 2000 personel. I tried to use the copy wizard where it appears I get connected to the source server OK but when I try to indicate the destin...more >>

Hi, As I wrote my message the solution came to me, so I thought I would post anyway for others to see in case it was useful: Here is some sample DDL for this question: CREATE TABLE Source ( my_value INT NOT NULL ) GO INSERT INTO Source VALUES (1) INSERT INTO Source VALUES (2) INSE...more >>

Hello! I have data exported from a Reference Manager 11, and need to import it into and SQL database. Each record has different number of fields. It is used to cite journal articles. (more about the format at http://www.adeptscience.co.uk/kb/article/A626) The format is very strange: ...more >>

Since there is no native support for 64bit integers in dbExpress, it is not trivial to fetch a BIGINT from SQL 2k. If it is possible, how can a BIGINT be fetched using TParam or TField? Any help appriciated...more >>

I'm trying to diagnose deadlocks in SQL Profiler. The deadlocks were generated by Loadrunner scripts (stress testing) simulating application SQL via an ODBC DSN connection. 2 things are puzzling me in the SQL Profiler traces that I have logged 1) There are a large number of Lock:Timeout even...more >>

I have a table where I store around 1 million rows for 7 days. I run a DBCC SHOWCONTIG every week and I noticed that the number of Rows and the Average Free Bytes are stable but the number of Extents and the Average Record Size keeps increasing. Any idea how to explain this incrase? and how to s...more >>

Hi MS Newsgroup I am using SQL 2000 SP3 + latest hot fixes and I am editing a DTS package. When I save the package I get a SQL Server Access Violation and cannot save the package. This does not crash SQL nor Enterprise Manager but no matter what I do I cannot save the package. This occurs for...more >>

Good Day; I would appreciate assistance developing a query that I haven't been able to develop without using a second table. I wish to count the number of records that are still open on the first of each month. Each record has an open date and a close date or the close date is null i.e., th...more >>

I am trying to assign @sql variable to @total, where @sql is a sql statement stored on the database, however what I am getting is its string value and not its calcuation. Could anybody help? DECLARE my_cursor CURSOR FOR SELECT sqlstatement from Sn_SalesReport declare @sql varchar(255), @tot...more >>

Hi there, I have an Access front end application that uses a SQL Server 2000 Desktop Edition backend. I have created a package with the Access 2003 runtime that I'd now like to deploy. I have created a global group in the domain and placed the users that I'd like to give access to the datab...more >>

Hello ! I have a DTS package which I can run it fine from command line using one SQL account who is not a also a user in the windows 2003 machine.If I try as another user that is a user or sysadmin account in the box then I get a login error from OLE. Any ideas? When I use the Enterprise Mana...more >>

Hi, Can someone give me advice ( or link to a webpage ) about how can I use primary key in my database and how can I use it for optimizing the speed of my database. Thanks. ...more >>

Can someone lend some assistance in this? It sounds like it should be able to be done. We have a large replicated database in SQL 2000. We need to move the mdf and ldf files to a location on another drive. Will doing this affect or break replication in any way? Is there any way to preven...more >>

Hi, I would like to prepare a data dictionary for my database (northwind). I have framed the below SQL SELECT 'NAME ' = a.name, 'DESCRIPTION' = b.value, 'Type ' = type_name(a.xusertype), ' ' AS 'Values', 'NULL ' = case when a.isnullable = 0 then ' ' else 'X' end, '...more >>

I have a tables called subsid that I need to change the datatype from text to int. I think I got them all but is there a query I can run that will check all fields call subsid accross all tables that are of type text. ...more >>

Hello. How can I call some functions on MSDE when working in Access (.mdb) that is connected to MSDE via ODBC linked tables ? Especially in-line functions, that I would like to use as recordset for my forms and reports. Can I call in-line functions using ADO ? I tried, but it seems that onl...more >>

COL1 xxxxxxxxxx&2005xxxxxxxxxxxxxxxxx&&10xxxxxxxx&&&15 xxxxxxxxxxxxxx&2005xxxxxxxxxx&&05xxxxxxxxxxxxxxxxx&&&27 xxxxxxxxx&2005xxx&&19xxxxxxxxxxxxxx&&&05 What SQL syntax is used in a view to display COL1 as: 10/15/2005 05/27/2005 19/05/2005 Thanks for any help! RBollinger ...more >>

If column1 in SQL Server column is text: 19980701 What is the syntax in the select statement to convert it to a date like: 07/01/1998 Thanks for any help Rbollinger ...more >>

Hi, I've got a production server with the default collation and User DBs with the different collation. Now when I create temporary tables and compare with the permanent ones we have collation problems. Is it possible to change tempdb ( and model) collations ? Would I need to change master and ...more >>

Hi, I've got a production server with the default collation and User DBs with the different collation. Now when I create temporary tables and compare with the permanent ones we have collation problems. Is it possible to change tempdb ( and model) collations ? Would I need to change master and ...more >>

Hi, I'm designing a simple database for filing system: There are two levels of files (both look_up tables): tlkpFile1, tlkpSubFile1 and a transaction table, tblFilings, for filings (when documents ready for filings, user just pick the file name from either look-up tables and insert to this ...more >>

I am looking for some assistance with an update query that needs to link 3 tables: This query ran and reported over 230,000 records affected but did not change the field I wanted changed, not sure what it did. I did notice that the "name" in "GM_NAMES.name" was colored blue in Query Analyz...more >>

I am importing a table where I need to convert a char(1) with the values of 't' or 'f' into a bit field with valies of 1 or 0. Is there a built-in function that does that? I've been searching, but I can't find an answer. ...more >>

I am trying to write a stored proc the calculates a moving average over three periods. In the following example, I need to stratify the data by personID and RecordID in the #Temp table, but I am not sure how to do it. Right now I am restricting the data I use to build my time series by personI...more >>

Hi Folks Could anyone please tell me if there is a maximum number of E-Mails that SQL Server Agent can send at any one time ? I'm thinking of using it to send out matches from the database that registered users have previously specified. Thanks David...more >>

I'm trying to learn how to make and use aliases for two tables in in this update statement: ALTER PROCEDURE dbo.UpdateStatus AS UPDATE dbo.npfields SET Status = N'DROPPED' FROM dbo.npfields NPF, dbo.importparsed IMP LEFT JOIN IMP ON (NPF.pkey = IMP.pkey) WHERE (IMP.pkey IS NULL) AND ((N...more >>

Hi, I have a database server (SQL 6.5 sp5a on NT4) and it is running ok, however when I tried to run sp_configure - it doesn't exist! It looks like a number of stored procedures are missing in the Master database and in order to recreate them (via script taken from other db server) i need to...more >>

I am going through a security audit on our servers. We use log shipping for a standby database. One of the questions in the audit has me looking for answers. "Are the transaction logs that are being shipped to the standby database encrypted?" I am assuming no. However, I need to know defini...more >>

I'm monitoring one of our servers, and on the whole it is performing well. However, I'm puzzled by the number of LockRequests/sec that Perfmon is recording. We frequently see values exceeding 50,000 and the current peak is 533,616 (the average, as I type this, is 35,102). There are only 40 ...more >>

Hello, I encounter a problem with a small portion of sqlcode. I try to go on database using "use dbname" but i always stay in master. I execute script with the sa user. declare @dbname sysname declare @ret_code int DECLARE db_cursor CURSOR FOR select name from master..sysdata...more >>

How should I set up a database to be able to efficiently maintain associations between related items? Example: Users shopping for Lord Of The Rings trilogy were also interested in The Hobbit. There will be many (20000+) items for sale and I need to do this efficiently, but I don't have an i...more >>