I have been developing applications with Microsoft Access for 8 years. Due to not seeing much demand for Access developers in the current job market, I was thinking it might be better to learn SQL Server. The Office 2000 Developer's Tools I purchased contains a SQL Server 7.0 Developer Edition CD....more >>

Hi all! Please - I really hope someone can help me... As part of a document management tool, I am trying to read and write files (of varying content - some txt, bmp, jpg, doc, xls, xml, etc) to and from the windows filesystem and a SQL database. The file content is stored as an ntext in a...more >>

Hi, I am unsure if this is the right group for my question... but I think so... I would like to perform a backup and send the backup file across a TCP/IP network (ie: over the Internet). Right now, I only figured out how to save to the local (server) machine. Is there an easy way to do ...more >>

In my database when i delete data from a table how can i program it in such a way that the change will also effect all other tables where this data is present....more >>

Is there any function in SQL Server to convert decimal number to hexadecimal number ? ...more >>

Hello, we need to track 4 pieces of information on each row in the table: creation date, created by, modification date and modified by In fact creation date and created by are used on inserting row and the rest on updating row, so I can create one trigger for insert and one for update for the...more >>

I have a trigger that does additional validation on the inserted rows. If violations of the biz rules are found I execute a rollback and raise an error. In test cases when I intentionally violate the rules the batch terminates immediately. If I take the rollback out of the trigger and simply ...more >>

We are doing some address cleanup on a clients system and trying to cleanup mispelled address elements, primarily city names. I know the ISO-3166 database provides codes for most country and state/province names. Does anyone know if there is another code database for all city names that have a ...more >>

I have two tables. One contains general information, the other contains historical transactions. I want to query the general table with a join to the history table, but I only want to return the last (most recent) history record. I know this is standard stuff, but I do need some help here....more >>

Hi, I'm trying to make recursive stored procedure with dynamically created cursor (in each iteration that cursor have diffrent name) and i do all with that only ... i can't retrive data from that dynamically created cursor. My problem is that i can't (i don't know how) to call the FETCH NEXT...more >>

Bulk insert to sqlserver2k using db-library bcp_* functions does not work for varchar or char field with length > 255. It runs without error but leaves the field = NULL. Is there any workaround? Thanks for help! Tuzo ...more >>

Hi, I have a procedure that executes an UPDATE and after, some output variables are set with determined values. The problem is, after the UPDATE statment, all following variables are reseteds to null value! If I remove/comment the UPDATE code, the output variables are returned OK to my app...more >>

Hi there, I'm using SQL Server as well as DB2. DB2 offers a special database object called SEQUENCE. It's some kind of a non-roll-back-aware counter. Using SEQUENCE, I can retrieve unique numbers or ranges of numbers. One SEQUENCE guarantees to give each number once. Is there anything simi...more >>

Hey all, Running into a jam with a full text search we are running. Everything seems to work fine except when someone tries searching for a series of single letters (ie: "J R M"). The information I have: Error: -2147217900 - Search on full-text catalog 'FullTextGG' for database ID 9, ...more >>

I have three table @temp, @client_sbill and @tempsbill A user pass a list of values and I need to find which @temp record is missing these values For Example in this senario I look record in @temp then find all corresponding record in @client_sbill and then compare it with the list provid...more >>

I'm sure I should be able to do this without a cursor ... I have a table, Tables with one column, TableName, varchar(255) SELECT TableName from Tables WHERE TableName IS NOT NULL then I want to do something like For Each Row in the above select EXEC DROP TABLE TableName Any ideas please? ...more >>

Hi, Consider a situation where once a connection to the database is opened one or more insert, update, or delete statements are issued against a database. These statements cause triggers to run on the tables they are effecting. These triggers need to have access to the name of the user that is...more >>

Hi, I'm just curious, is there any difference in declaring a table column either as VARCHAR(10) or VARCHAR(8000)? I mean, do performance/disk memory requirements increase by using "bigger" declarations (supposed you store the same data in both kind of columns)? Thanks for enlightening me, ...more >>

Let's suppose we have an stored procedure that make an insert in a table. That insert fail (primary key constrain). This stored procedure is called from C using ODBC with hr = SQLExecuteDirect(hstmt,L"{call MyProc }",SQL_NTS) If the insert from MyProc fail, i will obtain an hr != S_OK ...more >>

is there a good document describing how to use sql profiler to tune a query? there gobs of relatively useless info in the books online that came with sql server....more >>

Does anyone know how to pass dynamic array type parameters to a stored procedure or UDF? Thanks in advance. ...more >>

I've been working on creating a function that uses a cursor to read through multiple records and return a single value from the multiple reads. I have a table similar to this below: proj po# amt 1 a1 10 1 a2 10 2 b1 10 3 b1 10 I would like to crea...more >>

I'm looking at several queries and most of them use the Distinct and distinctrow keywords. The queries that use these words process tens of thousands of records. Do they make them slower? I was taught at another company to avoid distinct and distinctrow, especially since the query can be buil...more >>

Hi, I have a list of names in my database, this is a few thousand long. I need to display chunks of about 20 at a time on my web page ordered alphebetically. I can order them fine by using an ORDER BY CLAUSE however I am unsure how to start the search at a cetain name. for example in an a...more >>

Hi, I am programming in C++Builder and using TADOCommand to save my Unicode data in an SQL server 2000 database. When I send the data to the SQLServer, all languages characters except English are show by '?'. The Unicode field is of type "nvarchar". Would you please any body to read this code ...more >>

I'm working on an app (C#) where I need to access a table from 2 different databases (same MS-SQL box). What I need is to have a SELECT string with an INNER JOIN based on these 2 tables. If they were in the same DB this would be no problem, but I can't figure out how to get this via C#. I k...more >>

Hello all, Can someone tell me what the difference is between sp_execute and sp_executesql? TIA, Darias Brown ...more >>

Hi, I have an employee table having employee records along with their history records as well. I will take along one employee as an example: PK EmpID Name Salary ChangeDate Active Committed --------------------------------------------------------------------- 13 1442 ...more >>

Hi, I have a query that isn't grouping as I want it... I am using two fairly simple case statements, but these are affecting the way the group is calculated. example: select PRONUM 'PRONUM', case when VendorCode = 'ALPHA' then 'ALP' when VendorCode in (...more >>

I posted this a week ago on servers, but no-one answered and I'm still having the same problem. Hoping someone with an idea will see it. We have 2 SQL 2000 SP3 servers on Win NT 4.0 SP6. In Enterprise Mgr I have set up a linked server on each one to the other server. Also I have tried it...more >>

I have a table valued function 'f_prod'. Can I call it inside a SELECT statement passing as a parameters a field value from another table in the statement, like this: SELECT Reports.id, Reports.date, Reports.part, prod.l, prod.r FROM Reports CROSS JOIN f_prod(Reports.date, Reports.part) AS pro...more >>

I wanted to check if anyone had written a UDF or some SQL to convert Unix Epoch time to "human" time before I went ahead and tried. Thanks! ...more >>

What am I missing here? Declare @OldValue varchar(50), @FieldName varchar(50) select @FieldName = 'email' Select @OldValue = (Select @FieldName from Contacts where contactid = 32) print @OldValue OldValue always returs as "email" instead of "emaaddress@somwhere.com" I am writing a ...more >>

I have a table with the following columns: FolderID (int, unique), ParentFolderID (int), Name (varchar) Sample data: (but folders can be any depth) 2,1,TopFolder 3,1,FolderA 4,1,FolderB 5,4,FolderBA 6,5,FolderBAA 7,1,FolderC 8,7,FolderCA For a given FolderID I'm looking for a query...more >>

How can a strip a string of special characters to create a new string that I can use as a Table name? Thanks. -- Michael Tissington http://www.tabtag.com http://www.oaklodge.com ...more >>

Hi All, I want following Results from a query without using any tables or cursors month_id month_nme ---------- ---------------- 1 January 2 February 3 March 4 April 5 ...more >>

Is there a function in SQL2k to find whether a string is alphanum. appreciate any help. thanks...more >>

I am working with SQL 7. I want to transfer sysdtspackages from one server to another. Can someone tell me what the deal is with the Destination tab in the Data Pump Task? I have seen a ton of threads saying that the table name dropdown can be manually typed into, but that doesnt seem to be the c...more >>

Hi folks When I call the "backup database" transact sql routine (mssql 2000) and specify the file path for the backup file then it is placed on the server and not one the local client. How can I do that instead ? \Anders ...more >>

How can I take the values from multiple rows and display them in a single row? I have this: ordertype productclass productsubclass rangemin rangemax price --------- ------------ --------------- ----------- ----------- ------------- -------- ALAB LI STD 4 ...more >>

I have a bunch of attribute/value data and I need to query it to find records that have some of the attributes. I have simplified my problem/datastructures to something easier to work with and should be able to expand it to fit what I need once I figure this out. I guess I need the query I cam...more >>

I think this ought to be an easy thing for some of you experts out here. I need to to debug a third party .JSP application that writes to a sql db, but involves hundreds of files and I'm having trouble finding the include file that is causing me problems. I was hoping that I could write a tr...more >>

I have a table that has the following rows TransactionID PK FSRID int SiteID int CostAccount varchar(15) ChargeAmount (money) BeginTransactionDate datetime TransactionCompleteDate datetime (default to GETDATE()) EffectiveDate Notes I need to update this table once a week using the foll...more >>

I am by most measures an SQL NOVICE, I wish to create a DELETE trigger that will log to a separate logging table the table name and the primary key value of the record being deleted. This way I have an audit log table that I can reference to know if a specific record ever existed in a specifi...more >>

I have the following sql that needs to be altered to do some additional date processing. It has to be done in one query because it's being used behind a PB datawindow and in a view and I don't know if it's possible so looking for help from the sql experts. This is the current query: (wha...more >>

hi i had read somewhere that naming all your stored procedures with a sp_ prefix gives it some special previliages which the other stored procedures dont have. but i cant recollect why its an advantage to add the sp_ prefix thanx chris ...more >>

How do i execute a stored procedure from an asp web page. I want to have a web page on load start a stored procedure preferably in asp or asp.net with vbscript. -- Posted via http://dbforums.com...more >>

Can someone help me with including a variable name in my storedProcedure? CREATE PROCEDURE sp_OrgChart AS declare @TableName as nvarchar(50) SET @TableName = 'OrgChart' + replace(GetDate(), " ", "") DECLARE @DepartmentLeaderCount int SET @DepartmentLeaderCount = (SELECT COUNT(*) FROM ...more >>

Hi, I wonder what is the SQL equivalent to the INSTR function in Access? Thanks Deke...more >>

SQL2000 and Visual Studio .NET generate create script in unicode format. The options tab on the Generate Create Scripts dialog under file Options sets the default File Format to Unicode. It can be changed to Dos or ANSI format also. Some of our software is not unicode compatible. Is it possible to c...more >>

Hi, I can't start MSSQLServer, it shows as "stop" status. What's wrong? Another way other than reinstall? How to start it? Thanks. ...more >>

Hello everyone, I was wondering if is there a way to read a text file (SQL Commands saved as .SQL File) on a network location and execute the commands in the SP. Is this possible? If it is, can i get a sample code on how to perform this task. Thanks Server: SQL 2K/SP3/Win 2K ...more >>

From the command line, ">dtsrun /S sqlserver\sqlcluster /Udts_runner /Ppassword /Npackage_name" works great. From Query Analyzer, I use "EXEC master..xp_cmdshell 'dtsrun /Ssqlserver\sqlcluster /Udts_runner /Ppassword /N package_name' ". No file is written by the DTS package, but result window sh...more >>

Hi, I've been having a lot of troubles with aliases in SQL statements (within a stored procedure). More specifically, I'm having troubles relating conditions in the WHERE clause to aliased columns. Is there any way I can directly relate to an aliased column to use a WHERE statement on i...more >>

Is it possible to determine the login name for the dbo through QA? If so, how is it done? Thanks. Brian ...more >>

Hello I had a small question, I am trying to replicate SQL transactions from SQL Server to foxpro the query which i use is declare @SSN char(9) set @SSN = '520861364' Update openrowset('MSDASQL', 'Driver=Microsoft Visual FoxPro Driver; SourceDB=d:\; SourceType=DBF', "select * from...more >>

Is there a way to save the output from an extended stored proc into a table variable?...more >>

I need to vary the sort order based on user criteria. i have tried two different attempts; and ideas? ORDER BY case WHEN (:arg_sortby = 'O') then site, org_to_charge, ro_nbr, due_date WHEN (:arg_sortby = 'T') then site, trouble_code, ro_nbr, due_date ...more >>

Hi All, We have ISNULLs used for all most all columns in all retrieval(SELECT) stored procs. Though it is easy to code that way, I would prefer doing IS NULL checking in the middle tier (Business layer or data access layer) fot the reasons that code in middle tier is compiled to machine ...more >>

Case Description Sql Server 2000 Build 8.0.760 (Sp 3a) Problem: Query returns unexpected results when using subquery with In operand. We Use The Following Query: Select Distinct Fld1, Fld2, Fld3 From Table Left Join Table1 on Left Join Table2 on Left Join Table3 on Wh...more >>

Hi All, I created a script to basically recreate a database structure, including objects, on one server to another. Everything goes fne accept I am getting a warning that I have not seen before. Can anyone explain this to me and suggest a correct course if any? ------------------------------...more >>

I get from Internet XML file with current bank exchange rates. It consists of 2 tables: 1. table number, date of publication 2. containing exchange rates (country name, rate, etc.) I create DataSet using XmlReader. Then I want to save these data into 2 tables in SQL2000. First (master) table has I...more >>

how can i combine these two queries? I would like the result to be : CUSTNMBR THISYR LASTYR XYZ 1000 20000 ABC 2000 4000 --------------------------------------------------------- SELECT sum(SMRYSALS)AS THISYR, CUSTNMBR FROM RM00104 where YEAR1= (select YEAR1...more >>

I am using SQL Server 2000 and I am creating a stored proc. Which is the RIGHT way to check is a variable is null: 1. if @var = null ..... or 2. if @var is null ..... thank you -cmc ...more >>

Hi, I fill DataSet with 2 DataTables. I would like to know how many data is sent to and from server to my application. I was trying to use SQLProfiler and Performance Monitor, but I couldn't find anything good for me. Przemo...more >>

got slightly similar table. #Temp Table: name,name1,name2,exam,score A,A1,A21, A,A1,A21,math100,88 A,A1,A21,math101,56 A,A1,A21, A,A1,A21,math102,67 A,A1,A21, A,A1,A21,math104,45 A,A1,A21,Chm100,55 A,A1,A21, A,A1,A21,chm101,86 A,A1,A21,chm102,44 A,A1,A21, (skipping) A,A1,A22,math100,...more >>

hi, I have an index on datetime column (myColumn) when I write this query: select * from myTable where myColumn > '2003-10-10' it uses the index when I use a parmeter: declare @mydate datetime set @mydate = '2003-10-10' select * from myTable where myColumn > @mydate the index i...more >>

Hi I keep getting an error whenever I run the folllowing it is a problem with the syntax but am not sure how to fix it any ideas please update ce_cust set gp_pct_ytd = ( case when revenue_ytd =0 then 0 else ( gp_ytd/ revenue_ytd) end) * 100 end from ce_cust error Server: Msg 156, ...more >>

The basic scenario is that I have a function that generates unique = values that are used as primary keys based on the combination of a the = current date and a counter from a permanent table. The function is = passed the getdate() as a parameter and uses it to compute the = timestamp. Then a ...more >>

got slightly similar table. #Temp Table: name,name1,name2,exam,score A,A1,A21, A,A1,A21,math100,88 A,A1,A21,math101,56 A,A1,A21, A,A1,A21,math102,67 A,A1,A21, A,A1,A21,math104,45 A,A1,A21,Chm100,55 A,A1,A21, A,A1,A21,chm101,86 A,A1,A21,chm102,44 A,A1,A21, (skipping) A,A1,A22,math100,...more >>

Hi, I would like to have 1 output for two fields. For example select Surname & Lastname as "Name" from ... It works except when one of the fields has value NULL, then it always returns NULL. this is just an example, in reality we have two fields where one of the two (depending on cert...more >>

Hi, We work in defferent databases and oracle has e DECODE function in the select, but I cannot find the equivalent in SQL server it does the following for example select DECODE (TABLEmarriedYesNo, "1", "Married", "0", "Single") "Status" So when the TablemarriedYesNo field is "1" you ...more >>

Well, this is interesting. I can debug any stored proc that I want within QA, and all works well. This tells me that I have it set up properly on my machine, and I can also debug a stored proc on a selected remote server. However, if the stored proc fires a trigger, I start to see real troub...more >>

I can not reply when i do this i get a server error ???? fn_split is ok if i can define a delimiter example : mainstreet 4 can be split up in 'mainstreet' and '4' but what about king mainstreet 4 this has to become 'king mainstreet' and '4' thanks ...more >>