I am considering to port a VB-Access to a VB MS SQL Server 2K. The main reason for this is to achieve a robust database application that can be accessed by more than 10 users and also from the internet. One of the main goals is also replication/synchronisation facilities. Does SQL server 2K feat...more >>

I've got this query that works just fine in access. In it's query it makes use of some linked tables that it groups and sums, taking the query into sql-server doesn't work and I need some help understanding it As I understood from reading access uses (can use) functions such as sum and join...more >>

Hi, I had an assignment where I had to count the number of Characters in a string (Commas in my case), and I devised this method that I would like to share: -- Count the number of '0's in the string @s DECLARE @s VARCHAR(30) SET @s = '1001001001110011001' SELECT LEN(@s) - LEN(REPLACE(@s,'...more >>

Hi Can anybody help me diagnose why the following is happening I've checked the Current Activity listing thats displayed in SQLEM (when my application has established a connection to the server), and my process isn't showing up in the list. I can't really provide any other details at the moment...more >>

Hi All, I want all the employees working under the mgrid 2 from the following table emp. empid empname mgrid ----------- ------------------------- ----------- 1 A 0 2 B 1 3 C ...more >>

SELECT P.hostname FROM master.dbo.sysprocesses P WHERE P.program_name = 'MyAppName Would this be considered to be efficient enough to use? I'm trying to generate a 'Who's Online' listing for my application. Any suggestions would be greatly appreciated Best Regards Brad...more >>

can anyone help in finding some information about using BLOB in mySQL? am trying to implement a mysql database to manipulate .wav files and i have never worked with BLOB before. thanx ...more >>

I'm trying to figure out what the best way is to connect efficiently to a database -- and yet keep the code light when designing a website: It appears to me that (proxy code coming up...) for (int i=0;i<1000;i++){ x = new Connection(); do query... x.Close(); } would be total...more >>

Hi, i always create a temp table to store temporary records, the name for the table is tblTemp... But i wanna be safe, i wanna check if the table already exist in the database, how do i do that? Regards, Jacky ...more >>

I have noticed that when calling a User-Defined function I must qualify it with owner. For example: I created a stupid function CREATE FUNCTION AddOne(@i INT) RETURNS INT AS BEGIN return @i + 1 END I start query analyzer as dbo. When calling: select AddOne(1) I get error message...more >>

I have a database of part numbers (SQL 2000). In one of the fields (ItemID - nvarchar), each value has 3 extra characters at the beginning of the ItemID. I want to trim those three extra characters off each ItemID Can anyone help Thanks...more >>

I'm running into a UDF oddity. I have a UDF that executes 3 SQL statements (see below). When I do a Select against the UDF, it takes about 1 minute to execute. If I take those 3 statements and run them in Query Analyzer, it takes about 2 seconds. Could anyone possibly shed some light on why t...more >>

Hi, how to add a new sql user and assign "db_backupoperator" role to it ? do I still need to specify which DB or table the user can perform "backup" operation? Thanks... ...more >>

I'm starting to move my VB6 application from Access to SQL Server. In the beginning I'm going to use Access database with tables linked to SQL Server tables. Then form by form are going to be moved to direct SQL Server connection. When all of them are moved I delete Access. I have very weak expe...more >>

I know how to produce a calculated column based on other columns by doing simple addition or other arithmetic operations, but what if it's more complex as is represented by a program: while(!end_of_recordset) { if(condition1) cond_col.value = .... else(condition2) con...more >>

I know how to produce a calculated column based on other columns by doing simple addition or other arithmetic operations, but what if it's more complex as is represented by a program: while(!end_of_recordset) { if(condition1) cond_col.value = .... else(condition2) con...more >>

I have a view in SQL Server 2000 which uses 10 table joins.I have modified one table by adding a column to it.Does the view get refreshed on its own or do I have to drop and recreate it? Its a simple view with no options used. Pls let me know. Thanks and Regards, Shaleen ...more >>

I have a SP that calls an SP from inside an Insert into t exec sp_ .. The called SP also does and insert into temp exec(@sp SP1 insert int tabl exec sp2 parm parm par SP2 .. spstring = 'sp3 parm parm parm insert int #tem exec(@spstring This is causing a 8164 error, nested i...more >>

Thanks ahead of time for your help. Schema information is below. I have created a trigger (1) that is supposed to alert us if a change was made to a standard cost. I have to send the data as an INSERT to another table that I created because the schema is bound, but I am allowed additions. ...more >>

Hi, I'm currently developing my skills in SQL sever development. At the moment I develop all my front ends in MS Access unbound ADP's. However I'm wondering if it would be advisable to begin to look at developing applications in pure VB. To this end, I was wondering: * Does developing a ...more >>

I ask the following a little sheepishly, as it's probably a bit dumb...but it would be nice to achieve... I believe that if you want a recordset from a record for display that refers to a foreign table, you write it as: SELECT Contacts.*, Categories.Value as Category from Contacts as Categor...more >>

Hi all, I have a large table contained website visitor information, currently 303000 rows of data. I have a summary web page where a user can view a summary of years/months, ie 2004 March February January 2003 December November October and so on...this is generated from th...more >>

Hi, I'm thinking of using a trigger to write to a table whenever data is changed in a table. Since you can get a trigger to work for the insert, update or delete statement, is that the only way, through VB code, to make a change to the data in a table? Thanks, Ellie ...more >>

I have a SQL Server 2000 SP3 instance in which the SQL Debugger works fine in "new" databases, but not in some of my existing databases. Is there a database-level setting that controls whether debugging will work as opposed to affecting the entire instance? ...more >>

I'm wondering if my stored procedure which simply adds a record to a table can return the primary key value that is generated by SQL as the indentity value? Table1: custid (identity), customername (varchar) StoredProc: insert into table1 (customername) values ('John Doe') I'd like t...more >>

I have a stored procedure in which I am using the return value coming from an sp_OAMethod call to capture a string of data. The data type that I am using to capture the data is varchar(8000). Sometimes, however, the return value is larger than 8000 characters in length. Is there another da...more >>

Hi all I have 2 jobs with multiple steps, what I need to do somehow flag the second job not to run when a criteria is not met in the first job Any idea how to implement this from code point of view and EM Thanks in advance Shah...more >>

Hi. I have a view which looks down a long list of invoices and tells me how many days elapsed between invoices for each customer. Someone helped me with this code so I'm still trying to get a handle on it. In the meantime, I'm hoping to add one more feature; sequentially counting the numbe...more >>

What's wrong there as I know that this Column and table are ok Error Message: The Column Prefix 'dbo.Order' doesn't match with a table = name or alias used in the query Store Procedure: SELECT [dbo].[Order].[RegistryAllowed] FROM [dbo].[Order] WHERE = [UID]=3D@cartID SET @RegistryAllow...more >>

Hi Everybody, When this is going into the else for the if statement, it doesnt seem to be going through the update statement. Any ideas? Regards -- update the dates in the session table to reflect the new ones SET @EndDate = DATEADD(dd, (@intNumberOfDaysInOldSession - 1), @StartDate) ...more >>

Hi, I make an ASP.NET application, using MSDE as BE I develop the application on local PC, then when I have something ready I put it on a test web server, for others to test it That server has its one instance of MSDE This leads me to the following problem: When I update the applicatio...more >>

The odd thing is that the insert is successful only if the last_name and first_name are null: CREATE TRIGGER UpdateAgent ON [dbo].[Users] FOR UPDATE AS if(SELECT ins.Access_Level FROM INSERTED ins) = 4 begin DELETE FROM SalesAgent WHERE Agent_Code = (SELECT del.User_Id from Deleted de...more >>

In query analyzer, I'm connected to a specific server. How can I select from a different server within the same window? In a simple example, I want to see the contents of a particular table from each server: ...more >>

I have numeric values stored in a varchar data type. How do i convert the varchar values into an integer value using either the cast or convert functions. Please advise? Thanks, Brett ...more >>

Hi, I have a database and a user called 'test' Several users at the same time can log in using that username. Is there any way to get a description of the connection from a stored procedure, trigger,... I thought in using SUSER_SID but that always returns the same number for every connecti...more >>

Hi, Could ALTER TABEL "table name" works with table type declared just before ? What I need to do is DELET COLUMN from this table, and ADD this again - is there a way to do over @TAB ? -- best regards stic ...more >>

hi i need eliminate in a table the rows that has a value duplicate in a coloumn... see the example: is | cirio | sara | ------------------------------------------- 1 | 3 | 4 | 1 | 4 | 4 | 1 | 6 | 2 | ...more >>

Hello... I have a hex value stored in a column with a data type of varchar(50). Here is a sample value OX1F7. This code will be translated to a http error. If you execute the following statement.. this works: SELECT CAST(0X1F7 AS INTEGER) = 503 If execute this: SEL...more >>

Hi all, can anyone speed up the below query? It takes 2-3 minutes. The sql goes something like :- SELECT blah, blah from blah LEFT JOIN values v1 on blah.id = v1.id and v1.desc = 'Total 1' LEFT JOIN values v1 on blah.id = v1.id and v1.desc = 'Total 2' LEFT JOIN values v1 on blah.id = v1...more >>

Hello, Lets asume I have a Clients table which has Name, Sex, Bday, City, State, LastPurchase, Amount. This table has 3 million entries. So I create a View which filters acording to LastPurchase, this creates a View with 600,000 entries. Doing a quick analysis (using group functions) I rea...more >>

I'm using oledb & SS2000, and observed the following odd behavior with the procedure cache, by looking at syscacheobjects and the profiler In oledb, I Prepare a complex select statement, with several bind parameters. I then Execute it. In syscacheobjects, I see the usecounts go up on the Compiled ...more >>

The following can be found at: http://www.developer.com/db/article.php/10920_3099771_1 "SQL Server "Yukon" now has a robust development environment that allows programmers to branch out into the world of data. Many of these people have only learned what they need to know in order to coexist wi...more >>

I sure hope someone can help. I trying to figure out how to create a folder on a mapped drive when the name field in my database has been added to. For example, Paul Smith gets entered into the database and a subsequent folder on the P: is created named Smith. Any ideas or thoughts on where and h...more >>

Hello All. Could someone point me to the SQL Server table that will give me additional information relating to Foreign Keys -- specifically the action taken ON UPDATE or ON DELETE ? Thanks in advance, Heather ...more >>

how would i go about writing an update function to replace all numbers in a sequence, EXCEPT the last four digits, withg asterisks. the problem is that the string of numbers will vary in length. so for example: 12345678 would become ****5678 111222333444555666 would become ************...more >>

I have the following table [Prices] Date Symbol Price === ====== ====== 1/1/99 MSFT 50 1/1/99 ORCL 35 -- -- 1/2/99 MSFT 51 1/2/99 CSCO 65 --- --- Basically, a table with historical stock prices for 100...more >>

I will like to develop a simple secrete controll as like as window say user can maintain a group, and maintain user to perform pre-defined task, however, I really dont know how the table structure will look like, any suggestion? thx~ ...more >>

I have a simple table, not normalized (that's planned). I've been asked to find the most recent record matching a set of criteria. The table records information about a PC's AV compliance, and is populated by a logon script. It has the following columns: ID (auto-increments) date the...more >>

Unexpected and interesting behavior ... Let me know what you think: The following code produces the concatenated string as desired ... DECLARE @Trans_Id int, @Trans_Type_Desc varchar(1000), @NumParm int SELECT @Trans_Id=264, @NumParm=0 SET @Trans_Type_Desc='' SELECT @Trans_Type_Desc = ...more >>

Hi all, I want that my procedure to receive a table as paremeter from my application. I use vb .net and it passed as dataset type. Is that the best way to do it?? Is that possible??? ...more >>

Hi all I have this table below: I need advice what's best way to do this!! Key, FirstName, LastName, PTNumber GivenDate GivenTime Test Result UnitNum Pre POST 456 FNA LNA 123456A 12/4/2003 1300 HGB 9.8 457 FNA LNA 123456A 12/6...more >>

Sorry for the question, but I didn't find the answer in sqlbol :-( I need calculate field Type on base field Code like this: If first digits of Code is 81 then Type must be 'International' If first digits of Code is 80 or 82 then Type must be 'Republic' If first digits of Code is 8029 then T...more >>

How Can I create a transaction in a way that: if during the transation an error occurs the transation rolls back? Many thanks ...more >>

I have a somewhat tricky situation, and I just cant "bolt the bits" together into a working solution. I have a table, lets call it "Items". Items has two important columns, an ItemID and a ProjectID. ProjectID is a reference to a seperate table, called Projects. My general problem is that...more >>

I have trouble with a particular procedure. There is something missing to get the wanted result. I'm close though. What i want to do is query the Orders table for a particular Driver Number. Based on that Driver Number I want to show: 1.Each order in which This Driver participated (to Pick Up,...more >>

I am trying to create a query that will subtotal the values for sh_current_bal for each date (sh_ccun_biz_date). This is what I have so far. I am not sure how to apply a SUM function to get it to subtotal by date SELECT ccun_shares.sh_ccun_biz_date, ccun_shares.sh_current_ba FROM ga.dbo.ccun_shar...more >>

Hi, How can I get a listing of all users for any given database. TIA, Eric...more >>

I am trying to query between two Date ranges, but am having problems due to the dat format Table1.TransDate DateTime format ( yyyy-mm-dd hh:mm:ss.hhh StartDate varchar(10) format ( dd/mm/yyyy End Date varchar(10) format ( dd/mm/yyyy The startdate and End date ar...more >>

I am connected via VPN. I want to start execution of a stored procedure (it lasts for 10 hrs) and then close connection but let the procedure continue to execute at the server. Is there a way to do this? ...more >>