Bit Column Behavior MSSQL7 And MSSQL2000
Nov 24, 2005
I was using MSSQL7 for a long period.
I upsized to MSSQL7 from Access some years ago. Without any particular reason when writing code in stored procedures, when I wanred to select some records having a bit column to true I used the syntax bitcolumn=-1 (and not bitcolumn=1). This behavior was used in Access. Everything worked fine. Then I moved to MSSQL2000 and by restoring the MSSQL7 database I had no problem.
However, in order to use some features of MSSQL2000 I had to run the sp
sp_dbcmptlevel <database>, 80
After that the condition bitcolumn=-1 didn't work.
Can anyone verify this behavior, since I have to make dozens of changes in my stored procedures and triggers???
Regards,
Manolis Perrakis
View 2 Replies
ADVERTISEMENT
May 11, 2007
Hi all,
I hv very little experience handling mssql7. Recently I was assigned a task to maintain a website that uses mssql 7. My system admin has already installed mssql7 server components on a Win2K box and I have only managed to install mssql7 client components on my winXP box.
Whenever I try to connect to the remote server using enterprise manager, I always encounter the same error message complaining that
Quote:
A connection could not be established to "Server Name" - Access denied
ConnectionOpen (CreateFile())..
Please Verify SQL Server is running and check you SQL Server registration properties (by right clicking on the GAMMA
node) and try again.
I have used the client network utility in my winxp box to set up an alias for the server using various network libraries such as TCP/IP and named pipe, but it keeps on returning the same access denial message.
I am running out of brain juice figuring about what's wrong. Could somebody please help me? Thanks in advanced.
View 1 Replies
View Related
Jul 6, 2006
Okay heres the problem I'm facing. I have GoDaddy as my hosting company. They only support MSSQL2000 but I just recently bought MSSQL2005 Developer Edition. And I was wondering what is the main difference between them two. I didn't find anything on Google. And the only thing is I can't find a better host. Cause I have 50GB of space and 500GB of bandwidth. And I don't want to lose that for just MSSQL2005 support cause my host doesn't/ Have that. So im stuck like chuck. Is there really any difference?
View 1 Replies
View Related
Jul 16, 1999
I have a SQL Server 7.0 installed on a Compaq PC running on Windows NT. This PC has 64 MB of RAM and it is a Pentium 166. There are 3 Clients connected to it. If there is only one PC running an apllication requesting data from the server, the performance is tolerable, but when there is more than one PC simultenously requesting data from the server, problems araise. Either all of the PCS running the application show zero records on the apllication or one of them show no records. Any mouse click action on the clients will bring the server to almost a halt. Can anyone tell me the reason to these problems?
View 1 Replies
View Related
Apr 27, 1999
While installing SQL 7.0 it turned out that the size of my primary patrtition where the Windows NT system files are {C: drive) does not have enough space to take the MSSQL7 system files it wanted to install there. Can the size of this partition be increased (am using FAT file system), can the MSSQL7 system files be installed somewhere else, or should I just do an f disk and start all over again with a new Windows NT installation? (No data on this server yet.) Thanks for your help!
View 2 Replies
View Related
Feb 23, 2001
We've experienced a problem with one of our MSSQL7 servers where transactions are not being committed to the database. We've experienced this in Query Analyzer -- where we run several (simple) insert or update statements. Although the script runs successfully, the rows do not appear in the tables. The implicit transaction setting is off for the server.
We have also experienced this problem with other tools that run against this server via ODBC.
I thought the default setting for MSSQL7 is to auto-commit transactions and I've seen nothing in the error log that indicates that the updates were rolled back. I have compared our server, database and client configuration options to other servers that do not have this problem and have no idea where to look next.
Has anyone encountered this problem? Or does anyone have some ideas about where I could look for more information?
Thanks!
View 2 Replies
View Related
Jan 4, 2000
Hi,
I am trying to figure out a way to link a MySQL database (running on Linux) to my MSSQL7 database. My ultimate goal is to synchronize inserted/updated/deleted data in the MSSQL7 DB to the MySQL DB.
I have tried a few things and have had a few ideas. They are:
1) Create table triggers in the MSSQL7 DB that will synchronize the data to the MySQL DB. I can't figure out how to run Transact/SQL statements (INSERT, UPDATE, DELETE, etc.) into an ODBC DSN pointing to the MySQL DB. I think this could be done with a linked server (see next item).
2) Add a linked server under Security->Linked Servers in the MSSQL7 Enterprise Manager. I have managed to get the remote MySQL server to link in here. I can even see the table names in the server browser, so that's encouraging! However, I cannot figure out the syntax of the fully-qualified table names in my SELECT statements. I have tried this:
SELECT * from LinuxBox..MyDB.MyTable
-and-
SELECT * from LinuxBox.MyDB.MyDB.MyTable
but I get:
Server: Msg 7312, Level 16, State 1, Line 1
Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
This statement:
SELECT * from LinuxBox...MyTable;
gives me:
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
Since I can see the table names in the linked server browser in Enterprise Manager, though, some kind of connection must be active. I just need to figure out how to refer to the remote tables.
A big problem is that when I supply a catalog name while defining the linked server, I get an error message from the Enterprise Manager:
Error 7399: OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
Therefore, I can't define a catalog with the linked server. I think a catalog is required for the "4-part name" to work in the SELECT statement above.
3) Write an extended stored procedure that uses the ODBC function library to make a connection to the MySQL DSN and have a table trigger call the ESP. Hoping to be able to do something simpler than this!
Any suggestions would be greatly appreciated!
Thanks,
Greg
View 1 Replies
View Related
Feb 6, 2004
Dear All,
could you please tell me if MSSQL7 is compliant with Windows 2000 ?
Which Service Pack is needed from both MSSQL and W2000 ?
many thanks in advance for your help ;)
View 2 Replies
View Related
May 23, 2000
I'm trying to bcp logins from 6.5 to 7.0 while maintaining the passwords. I tried doing a bcp in native format, but when I try to bcp in, I get a Dr. Watson. I figured out the issues with syslogins not be updatable on 7.0 because its a view. I'm also aware of the problems associated with the fact that sysxlogins on 7.0 has different column definitions than syslogins on 6.5. I just don't know how to get around these roadblocks. Anyone have any ideas??
Thanks in advance,
Darias
View 1 Replies
View Related
Feb 2, 2004
Hi there,
Does anyone know if it's possible to perform a MSSQL7 database backup on a network drive instead of the local drives ?
...and if yes, how to proceed ?
It seems to be possible under MSSQL2000.
Many thanks for your help in advance,
Matt
View 6 Replies
View Related
Jul 20, 2005
I have a table in mssql7 that has 7000000 record and I want to take100000 records out of it and place them into the new machine withmssql2000. The new machine will also have the same table name, so Iwant to append the 100000 records into that table.Thanks,Royal344--Direct access to this group with http://web2news.comhttp://web2news.com/?comp.databases.ms-sqlserver
View 1 Replies
View Related
Jul 20, 2005
Sorry about the huge post, but I think this is the amount ofinformation necessary for someone to help me with a good answer.I'm writing a statistical analysis program in ASP.net and MSSQL7 thatanalyzes data that I've collected from my business's webpage and thehits it's collecting from the various pay-per-click (PPC) engines.I've arrived at problems writing a SQL call to generate certainstatistics.Whenever someone enters our site from one of the PPC search engines, Iwrite out a row to the Hits table. In that table are the followingcolumns:HitID - the Unique ID assigned to each hit that comes into the siteKeyword - the keyword the user searched on when he or she came to thesiteSearchEngine - the PPC engine the user came fromSource - this is pretty much always 'PPC'...if we were to do otherthings, like a newsletter, then this would be different.TimeArrived - the date and time the user arrived at the website. Ihave no idea why I didn't call it "datearrived," since I use "date"and not "time" pretty much everywhere else...(I don't think the rest are important, but they might be, so I'llinclude them for completeness's sake)Referring URL - the URL the user came fromReferring Website - the string between the 'http://' and the first '/'in the URL. I know it's redundant information, but when I designedthis part, I didn't know how to parse it out afterwards, so I justfigured I'd duplicate it.Page Visited - the page the user first arrived atWhen a person comes to the site, I also write out a session cookiecontaining the user's hitID. If the person fills out an enrollmentform (a process which we refer to as "responding"), I attach thatsession ID to the form. The response form (and thus the responsestable) is long; these are the important fields:id - a unique ID for each responsedate - the date and time of the responsestatus - a varchar field containing a status code. I would have madeit a number, but I wanted it to be viewable from looking at the rawdatabase.hitid - the HitID of the user, taken from the session cookie. If thereis no session cookie (for whatever reason), the HidID is written outas 0. While it wouldn't occur often, I can't guarantee that there willnever be more than one response record attached to a singular hitid.Later, some of the responses turn into "confirmations", which meansthat they've actually ordered from us, not just filled out the form.This usually happens about three or four days after the initialresponse. When this happens, the status of the response is changed toa phrase containing the word "confirm" in it (there are a few of them,but they all contain that word).So now that we've collected all this marketing intel., I need toanalyze it.I've written a parser that takes reports from various pay-per-clickcompanies and puts them into a table called PPC. Information in thiscolumn is written out as one record per search engine per keyword perday. The schema is as follows:id - a unique ID for the record in the tabledate - the date to which the information in the record appliessearchengine - the PPC engine to which the information applieskeyword - the keyword to which the information appliesclicks - the number of clicks on the applicable keyword on theapplicable search engine on the applicable day.impressions - same as clicks, but for impressionscpc - the cost per click on the applicable keyword ...avgpos - (I don't always have a value for this field) The averageposition that the keyword was shown in for the applicable keyword ...With this data in, the last step is actually analyzing the threetables for useful statistics on the various keywords, search engines,and time frames. That's the step I've been trying to complete.So what I need is a SQL call that I can run that generates a tablewith the following information:SearchEngineKeywordCost / Click - When calculating the CPC, I can't just take an averageof all the records. I need to calculate the total amount spent per day(clicks * cpc), add that up for every day, and then divide that by thenumber of total clicks. Just doing an average doesn't take intoaccount the fact that some days we'll get more clicks than others.Total Spent - # Clicks * CPC#Responses - counting the number of records in the responses table#Confirms - counting the number of records in the responses table with"confirm" in their statusTotal Spent / #ResponsesTotal Spent / #ConfirmsOh yeah, and I want to be able to order by any four of the fields inany order, narrow my selection to only those keywords that either areor contain a user-specified string, further narrow my selection toonly those records that fit other user-specified criteria for any ofthe columns in the table I'm generating, and select only the top xrecords (where x is a user-specified number). I already haveuser-controls that output the SQL for all of these things, but I needto have places in which I may put that SQL in my call.After many trials and tribulations, I've come up with the followingSQL call. Right now, its output for nearly every row is incorrect, Ithink in a large part due to the fact that the method that I'm usingto generate the number of clicks is yielding incorrect values.If you'd like to help me and you think that modifying the followingcall is easier than writing a whole new one, be my guest; if you'dprefer to write a new one, I'm game for that, too. I'm just concernedwith its working right now, and any help you can give me is greatlyappreciated.Anyway, here's the call:/*sp_dboption @dbname='NDP', @optname='Select Into', @optvalue=true;*//*Running the above might be necessary to get the "Select Into"s towork*/Drop table ResponsesPPCDrop table ConfirmPPCDrop table TempPPCSELECT Responses.[ID] as [ID], Responses.Status, PPC.SearchEngine,PPC.KeywordInto ResponsesPPCFROM Responses, PPCWHERE Responses.HitID IN(SELECT Hits.HitIDFROM HitsWHERE Hits.SearchEngine = PPC.SearchEngineAND Hits.Keyword = PPC.Keyword)SELECT ID, Status, SearchEngine, KeywordInto ConfirmPPCFROM ResponsesPPCWHERE Status LIKE "%confirm%"Order by SearchEngine, KeywordSELECT PPC.SearchEngine, PPC.Keyword,SUM(PPC.Clicks), /*I noticed that thiscolumn gives me incorrect values(I don't need it in my final report, but it's useful for debugging).For some keywords, it gives me huge numbers(e.g. 265 clicks on one word that got ~10 clicks /day over five days),and for others, it doesn't give me enough. I think this is a majorpartof what's throwing off the rest of the statistics*/Case SUM(PPC.Clicks) WHEN 0 THEN 0 ELSESUM(PPC.clicks * PPC.cpc) / SUM(PPC.Clicks) END as CPC,SUM(PPC.clicks * PPC.cpc) AS TotalCost,count(ResponsesPPC.ID) As NumResponses,Count(ConfirmPPC.ID) As Confirms,(Case Count(ResponsesPPC.ID) WHEN 0 THEN 0 ELSESUM(PPC.clicks * PPC.cpc) / count(ResponsesPPC.ID) END) ASCostPerResponse,(Case Count(ConfirmPPC.ID) WHEN 0 THEN 0 ELSESUM(PPC.clicks * PPC.cpc) / count(ConfirmPPC.ID) END) AsCostPerConfirmFROM (PPC LEFT JOIN ResponsesPPC ON PPC.SearchEngine =ResponsesPPC.SearchEngineAND PPC.Keyword = ResponsesPPC.Keyword)LEFT JOIN ConfirmPPC ON PPC.SearchEngine = ConfirmPPC.SearchEngineAND PPC.Keyword = ConfirmPPC.KeywordGROUP BY PPC.SearchEngine, PPC.KeywordOrder by PPC.keyword desc/*Drop table ResponsesPPCDrop table ConfirmPPCDrop table TempPPC*//*I don't drop them right now so I can look at them,but normally, one would drop those tables.*/Thanks a lot for your help,-Starwiz
View 4 Replies
View Related
Oct 27, 2004
anyone has tried? please advice possible issues, workarounds
tks
View 1 Replies
View Related
Jan 8, 2004
Greetings!
I recently rewrote an old ACCESS 97 application to work with MSSQL 7.
The program works fine until I use a form whiche opens several linked tables at the same time. When closing the Form I get (At unpredictable moments) an ODBC SQL SERVER DRIVER update failure error message.
All rights are set on the tables for Public and even then i keep getting this error at very odd moments. I also had a look in Access at the ODBC refresh rate which is set at 10sec , OLEDB refreshrate 10 sec, and refresh is set at 5sec.
I tried different combinations of timeout settings and even renewing the MDAC to version 2.7.
Nothing helps.
Can anyone give me a tip as to look for the possible source of the error and how to fix it?
Thanks!
Vincent JS
View 2 Replies
View Related
Jul 26, 2007
Hi everyone!
I start to get panic now after 10 hours of different ways of trying to
get my old database from my [Server1]* to my [Server2]*. When Im
trying with the "SQL Server Management Studio" that included in
the MSSQL 2005 but that just tells me it dosn't support any version
before MSSQL2000.. I've also tryed with some third part softwares
like EMS but no joy!... Its 5 databases from the old server that I MUST
get into the new one.. Anyone know howto do this?
[Server1]
Windows 2000
MSSQL7
[Server2]
Windows Small Business Server 2003 R2.
Includes MSSQL 2005 Premium edition.
Kind Regards
// Magnus Dahl
View 4 Replies
View Related
May 9, 2008
Hi
May I ask how to export MSSQL2000 DB to *.sql with all existing data?
As I built the DB on local machine, the remote machine only provide SQL Server Web Admin which has Query Analyzer only. I can export a *.sql file, but there are no data.
Thanks for help
View 2 Replies
View Related
Oct 10, 2007
Hi!
I am a newbie using MSQL so please patience. I compact my database with
DATABASE SHRINK(DB,10)
BACKUP LOG data WITH TRUNCATE
DATABASE SHRINK(DB,10)
When I see the properties of my DB I see something strange
1- My log always has 1 mb of space
2- Database properties show me this information:
Size: 21639.23MB
Space Available: 782.23
Backup
Last database backup: 9/28/2007 1:44am
Last transaction log backup 6/9/2007 12:47 pm
I am so afraid because this says that I just have 782.23MB but my disk has 30GB yet.
I need to start to worry me ?
What can I do to solve this issue with my db?
TIA
:(
View 3 Replies
View Related
Feb 11, 2008
Hey Guys and Gals,
here's an interesting one for you.
Ok, so this 3rd party application we have exhibits some interesting behavior; when you amend any table through it's interface it drops and re-adds the object; meaning all permissions to the object are also removed. (Just take my word for it when I say that the table changes have to be done through this app).
Now, I've built a little application that hooks into this using an SQL Server authenticated user account with SELECT only permission on particular tables. Currently the app uses sprocs to access data. However, when changes were made to the schema last week; the application, obviously, received permission errors and ground to a nice halt.
I know that in 2005 we have the lovely EXECUTE AS statement; but I'm running a 65compaability database here and don't have that functionality.
Any ideas on how I can sort this mess out?
Hope I explained this well enough, let me know if you need any more info
Cheers,
George
View 9 Replies
View Related
Mar 4, 2008
Hi,
I have a bunch of data from tables on MS SQL 2000 and i want to transfer this data to my new database running on MS SQL 2005. How do i perform bcp on this? thanks :D
View 1 Replies
View Related
Jul 19, 2007
Hi Group,I developed a intranet site using MSSQL7/win2000 some time ago.The target environment used MSSQL2000/8.We were (almost painlessly) able to import the db-scheme and data from 7to 8. (Bravo MSSQL)Now I need to do some upgrading on the application and I would like tohave a copy of the database from MSSQL2000/8 to MSSQL7.Is that also possible?Or should I download Microsoft SQL Server Express and use that insteadof my MSSQL7? Is it better?I hope I can get the relationsheet too (that one with Foreign Keysmapped in a nice graphical way).Any advise highly appreciated.I am good with Postgresql, but my MSSQL skills leave a lot to bedesired. :-/For an outsider like me the many versions and OS's are quite confusing.Do I need special commands on MSSQL2000/8 to create a MSSQL7 compatibleexport?Thanks in advance!Regards,Erwin Moller
View 3 Replies
View Related
Jul 20, 2005
Hi, i am having problem to install sql2000 server, the steps i did are:1. Local machine -> next2. Create a new instance of SQL server -> next3. type name and company -> next4. accept -> next5. Server and client tools -> next6. Default -> next7. Typical -> next8. use the same account ... and Use the Local system account -> next9. Mixed mode , passward -> nextwhatever options i choose, the installation program of sql will exitafter step9. just don't know what the problem is. please help thanks:)
View 2 Replies
View Related
Nov 16, 2006
Hi.. Never used MSSQL 2000 db. I have this website that uses mssql2000. I want to export it and convert db in mysql. Please help me understand how i can do that. I have all login info..
View 1 Replies
View Related
Aug 10, 2006
I have mssql2000 running on a Windows 2003 server and now have a requirement to run an Oracle 10g database as well. Is it possible to run both mssql2000 and oracle 10g on the same server without running into any conflicts or will the two programs cause errors with each other?
Anyone have any experience with this? Oracle says it's technically possible but the tech had never seen it done.
Scott
View 4 Replies
View Related
Feb 11, 2007
Hi,
I'm executing a stored procedure in my local LAN which executes another one in a loop and I update a Table. The number of loops is about 6300.
This operation takes about 25 seconds in my local LAN.
Then I try to execute though in a VPN which has an upload speed of 256 kbps. I open query analyser connect to the remote server which is must faster than mine and I just write exec mystoredprocname in order to execute the procedure. The performance is very very slow.
In 7 minutes 180 loops are completed out or 6300.
I really cannot understand this. What is the reason of such slow perfomance?? My ADSL model displays no activity when the procedure is executed. I just use the PRINT method in MSSQL in order to display the progress of the operation. I tried to comment it out but with no difference.
I also use SET NOCOUNT ON in order not to display the update results.
Can someone explain me the cause for this? Are there some tricks in order to improve the performance when a slow connection is used like a ADSL with a static IP? It seems that something wrong is happening here.
Best Regards,
Manolis Perrakis
View 1 Replies
View Related
Jul 20, 2005
Hello!Does anybody know whether mssql2000 and emc mirrorvew _certified_ forjoint work?(Mirrorview is a fc-based remote mirroring solution)I mean is it supported from the MS point of view to put mssqldatafiles on emc mirrorview volumes?For example Oracle corp. has "Oracle Compatible Remote MirroringTechnologies" certification.But what about MS?
View 1 Replies
View Related
Jul 20, 2005
(MSSQL2000) I have read the transaction/locking sections in theMS-help, online and several books. What I want to understand is thetransaction behavior in single statements [not a BEGIN TRANSACTIONStatement1, Statement2... COMMIT].If I have a Table: "Letters" with 1 column "L" and the table presentlyhas rows{A,B,C,D}Case 1 (Insert):First start transaction T1 "SELECT * FROM Letters"Next start transaction T2 [separate connection] "INSERT INTO LettersVALUES( 'Z' )"Is it possible that T2 ends before T1 and the select returns{A,B,C,D,Z}Is it possible that T1 ends before T2 and the select returns{A,B,C,D} [No 'Z']Is this a race condition and I need to use a TABLOCK or TABLOCKX;and are TABLOCK/TABLOCKX only hints? I mean does the use of TABLOCKguarantee a lock on the table? Do I need to use 'SET TRANSACTIONISOLATION LEVEL SERIALIZABLE' and if I use 'TRANSACTION ISOLATIONLEVEL' is there a means of telling the system which tables I willtouch so that I can avoid a deadlock [upfront tell the system whattables I need to lock so there is not a race later]?Case 2 (Delete basically the same):First start transaction T1 "SELECT * FROM Letters"Next start transaction T2 "DELETE FROM Letters L = 'D'"Is it possible that T2 ends before T1 and the select returns {A,B,C}[No 'D']Is it possible that T1 ends before T2 and the select returns{A,B,C,D}Case 3 (Update basically the same):First start transaction T1 "SELECT * FROM Letters"Next start transaction T2 "UPDATE Letters SET L = 'Z'"Is it possible that T2 ends before T1 and the select returns{A,B,Z,Z} [Some letters were seen to become 'Z']Is it possible that T1 ends before T2 and the select returns{A,B,C,D}
View 3 Replies
View Related
Jan 31, 2007
Hi experts,
Here I got some problems with my application. (ASP & English Version SQL Server 2000)
As we are using English MSSQL Server 2000, we got some new functions and we have to facilitate support of Chinese characters in the DB. I have set the collation for those Chinese fields already and those queries or Stored Procs for Chinese are working fine, ONLY if I execute them in Enterprise Manager. Chinese characters can be displayed in the relevant tables.
However here comes the big problem and I got really frustrated. As we will provide user interface in ASP pages, we 'll let users to insert the information which will be sent to the DB. If there's Chinese characters in the query string, the Chinese characters added in the DB would be garbled.
e.g. EXECUTE proc_TestChinese 'XYZ', 'test123' (assume XYZ be those Chinese words)
I am wondering if there's any way I can solve this problem. Should I add special handling for these Chinese words? I have set the ASP pages in UTF-8 or Big5 encoding but it doesn't help. Hope you experts can show me the way out of the mess. Thanks in advance!
Manfred
View 4 Replies
View Related
Apr 4, 2006
Hi,
I have a datadabase with 1 datafile from 60Gb. Is it a good thing(preformance) to split up this datafile in smaller datafiles from 6Gb each?
I don't have separete diskslices so a can't spread my datafiles on my disks but i only need to know if a datafile from 60Gb sin't too big for MSSQL2000.
Thanks,
Ewoud,
View 3 Replies
View Related
Feb 17, 2004
I have a problem in connecting to my hosting database using .net file.
I am able connect to database with my ASP page.
Any sugession is greately appretiated!
Here is my code:
".net" page:
<%@ page language='vb' debug='true' %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script runat="server">
Sub Page_Load(Src As Object, E As EventArgs)
Dim MyConnection As SqlConnection
Dim da AS sqlDataAdaptor
Dim ds As DataSet
MyConnection = New SqlConnection("DSN=***;UID=***;PWD=***")
da = new sqlDataAdaptor("select * from city", MyConnection)
Try
MyConnection.open()
Response.write "Connection Success " & MyConnection.Database & "<br>"
catch sx AS sqlException
Response.write "Connection successful: <br>"
End Try
ds=new Dataset()
da.Fill(ds, "City")
With DataGrid1
.DataSource = ds.Tables("city").DefaultView
.DataBind()
End With
MyConnection.Close()
End Sub
</script>
<body>
<p>
<ASP:dataGrid id='DataGrid1' runat='server'/>
<p>
</body>
</html>
---------------------------------------------
"ASP" page:
Set DB = Server.CreateObject("ADODB.Connection")
DB.Open ConnectionString
SET RSCheckeMail = DB.Execute ("select * from city")
While not RSCheckeMail.eof
Response.write "City: " & RSCheckeMail("Name") & "<br>"
RSCheckeMail.Movenext
Wend
Thanks for your help in advance.
View 8 Replies
View Related
Apr 19, 2006
I can not get a multiple row resultset to display or even get sent to my client application running on coldfusion.
What is the problem with the code?
How do i display and return a resultset to my coldfusion client application?
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.nTransaction
(@pAccountNo varchar(30), @N int, @ntransCursor CURSOR VARYING OUTPUT, @nValue varchar(4000) OUTPUT)
AS
set @N = 0
SET ROWCOUNT @N
SET @ntransCursor = CURSOR FOR
-- FORWARD_ONLY STATIC
SELECT CONVERT(varchar,Eh.EntryID), Eh.EntryReference,E.AccountNo, E.Narrative, E.Amount, Eh.EntryDate
FROM entryheaders as Eh cross join entrys as E
WHERE Eh.EntrySerial = E.EntrySerial and AccountNo = @pAccountNo
ORDER BY Eh.EntryID DESC
SET ROWCOUNT 0
OPEN @ntransCursor
WHILE (@@FETCH_STATUS = 0)
BEGIN
FETCH NEXT FROM @ntransCursor into @nValue
END
CLOSE @ntransCursor
DEALLOCATE @ntransCursor;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Pls i need urgent help!!!
View 2 Replies
View Related
Apr 5, 2007
SELECT * FROM TA a WHERE a.rx=264886 and
AN= (select max(AN) FROM TA where rx=a.rx)
I have a table TA with 8+ million rows and there is clustered PK on (rx, AN) columns. The count on rx=264886 is 6000+ rows. This query takes about 1 to 2 minutes to fetch data. Can anyone suggest how to improve performance and fetch data faster?
Thanks, Vinnie
View 2 Replies
View Related
Jul 20, 2005
Hi AllI need to aggregate a query to produce the following:Workplace AvgM100 4.7M120 3.45Which would be a normal aggregate:SELECT Workplace, Avg(VALUE)FROM PRODGROUP BY WorkplaceHowever I need the average to only be based on the most recent 20results from each of the Workplace groups.I've never had to do something like this before so can't think of anyway to only take off the most recent 20 for each group (ordered byDate). It doesn't really matter if there were 25 spread across 2 daysI would just cut the list at 20 VALUEs as there is no time componentinvloved.Is there any way to do a sub-query that uses select top 20 ... foreach group that could then be aggregated?I would prefer to do it through a select statement rather than havingto use a stored procedure using and variables, etc which I can do. Thetable is not huge but is growing rapidly so I'm concerned thatanything using dyamic SQL or similar would be become painfully as thenumber of groups grows to 5,000 or more.If anyone has any ideas they would be greatly appreciated.Thanks in advance,Bevan
View 2 Replies
View Related
Jul 14, 2003
Hello,
Does anybody know, how can I execute stored procedures that are on MSSQL Server 2000 from Oracle9i SP ? Can you show me an examle ?
Thanks for any help.
View 3 Replies
View Related
