Limitation On Conn.Execute In Classic .ASP && ADO With SQL Server
Aug 13, 2004
Hello,
I have a project (using classic ASP & SQL Server) which adds one execute sql statement at a time to a temporary array, and then I join that array with a chr(30) (record separator), to a string variable called strSQL. I then run the following line of code:
conn.execute(strSQL)
I was wondering if there was any limitation to how large the strSQL variable can be? Reason I ask is because thru log writes I can see all of my sql execute lines exist in the variable strSQL prior to running the "conn.execute(strSQL)" command; however, not all of the lines run at the time of execution. Remember, this bug only is occuring whenever I have say over 600 sql lines to execute.
My understanding is that there was no limitation on the size of the string strSQL; however, in the interest of getting the bug fixed quick enough, I decided to just run a loop for each sql statment and run "conn.execute(strSQL)" every 50 times. This, in turn, has solved the problem and I do save all of my data; however, my original bug still exists.
Does anyone know why I have to split the sql commands and execute.com every 50 times instead of just being able to do it once ?
Please let me know. Thanks in advance.
View 8 Replies
ADVERTISEMENT
Oct 17, 2007
Hi,
Im just wondering, does the Execute SQL task have a length limitation of how long the SQL statement can be, I tried to copy and paste a very long SQL statement from my DTS execute job task to a new SSIS execute SQL task and the statement was truncated and I cannot enter anymore text after that
anyone have experience with this ?
View 1 Replies
View Related
Aug 31, 2007
Hi All,
I'm having trouble getting at SQL Server 2005 Express via ASP code.
I'm using the following connection string in my ASP code:
Set objConn = Server.CreateObject("ADODB.Connection")
objConn = "Provider = SQLNCLI;" & _
"Data Source = .SQLEXPRESS;" & _
"Initial Catalog = myDB;" & _
"User ID = DB_reader;" & _
"Password = a#koddkobn5;"
I keep getting the error:
Microsoft SQL Native Client (0x80004005)
Login failed for user 'DB_reader'. The user is not associated with a trusted SQL Server connection
I have made sure that the user exists in SQLEXPRESS and that server Security is set to SQL Server and Windows Authentication mode.
Can anyone think of anything I'm missing or that I should try?
We've lost our SQL Server admin and I'm trying to develop locally using Express while we hunt for a new admin.
Thanks so much for any assistance you have time to offer.
Very best,
Thom Cox
View 9 Replies
View Related
Aug 3, 2006
I am trying to return multiple recordsets to a classic ASP web page from SQL Server 2005 and then use GetRows() in ASP to fill 2 arrays with the data. I'm using a command object to run a stored procedure which performs 2 simple selects in SQL Server. The stored procedure works fine in SQL Server Management Studio so I'm guessing it's an ADO issue.
The ASP code looks like this: strConnect = "DRIVER={SQL Native Client};SERVER=MyServer.IsAtMyIsp.com;DATABASE=MyDb;UID=Me;PWD=MyPwd; MARS Connection=True;"
Set conn = Server.CreateObject("ADODB.Connection")
conn.ConnectionString = strConnect
conn.Open
Set objCommand = Server.CreateObject("ADODB.Command")
Set objRecordset = Server.CreateObject("ADODB.Recordset")
Set objRs = Server.CreateObject("ADODB.Recordset")
With objCommand
.ActiveConnection = conn
.CommandText = "sp_GetShowList"
.CommandType = adCmdStoredProc
If Len(strBeginDate) And IsDate(strBeginDate) Then
.Parameters.Append .CreateParameter("@i_DateStart",adDate,adParamInput,,strBeginDate)
If Len(strEndDate) And IsDate(strEndDate) Then
.Parameters.Append .CreateParameter("@i_DateEnd",adDate,adParamInput,,strEndDate)
End If
End If
End With
Set objRs = objCommand.Execute
arrEvents = objRs.GetRows()
objRs.NextRecordset
arrSched = objRs.GetRows()
The error message below occurs at the 2nd GetRows() command. Operation is not allowed when the object is closed.
Can anyone tell me what I might be doing wrong?
-Dan
View 3 Replies
View Related
Aug 20, 2006
I'm considering shifting my database server to 64-bit MS SQL Server 2005 for improved scalability and performance. I'm concerned, however, that my classic ASP website (which sits on a separate server) may have problems communicating via ADO/OLEDB because of communication problems between 32-bit IIS on the web server and 64-bit MS SQL Server on the database server.
My current set up (which works fine) is:
Web Server: Windows Server 2003, Standard Edition, SP1 - running IIS with a set of ASP websites
Database Server: Windows 2000 SP4, running MS SQL Server 2000
Connection String:
MyConnection="Provider=SQLOLEDB;Network Library=DBMSSOCN;SERVER=192.168.0.1;INITIAL CATALOG=MyDatabase;UID=MyUserID;PWD=MyPassword"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open MyConnection
My core question is:
If I change my database server to new machine with 64-bit Windows Server 2003 running 64-bit MS SQL Server 2005, will my (32-bit) web server be able to connect from ASP as it does now?
Thanks for your help!
Jed
View 2 Replies
View Related
Dec 17, 2006
Hello, We are re-writing our site in asp.net using sql server. Most of the site uses asp classic and it was to an access database. During the conversion we have everything working correct to SQL Server except for the asp.net connection string. There is an important part of the application using asp.net which works fine with our connection string to the access database. To recap our problem is the connection string from asp.net to sql server. This code works fine for the asp.net to access in the web.cnfg file </microsoft.web> <connectionStrings> <add name="SalesConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:inetpubvhosts hemarketingvp.comsubdomainsvphttpdocsfpdbsalesMain.mdb" providerName="System.Data.OleDb"/> <add name="ODBCSalesConnectionString" connectionString="DRIVER={Microsoft Access Driver (*.mdb)};DBQ=URL=E:inetpubvhosts hemarketingvp.comsubdomainsvphttpdocsfpdbsalesMain.mdb"/> <add name="RawConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:inetpubvhosts hemarketingvp.comsubdomainsvphttpdocsfpdbsalesMain.mdb" providerName="System.Data.OleDb"/> <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|inventoryStatus.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings> <system.web> The code in the global.asa which works fine for classic asp to sql server also works fine and is as follows '--Project Data Connection Application("sales_ConnectionString") = "Driver={SQL Native Client};Server=DMSERVER01;Database=SQLsalesMain;UID=nTrack;PWD=nTrack2k3" Application("sales_ConnectionTimeout") = 15 Application("sales_CommandTimeout") = 30 Application("sales_CursorLocation") = 3 Application("sales_RuntimeUserName") = "" Application("sales_RuntimePassword") = "" Our programmer who set this up is out for a couple of weeks and I would appreciate any help in the correct connection string from asp.net to the sql server database in the web.cnfg file Thanks
View 5 Replies
View Related
Dec 1, 2005
I migrated my SQL Server 7 database to the new SQL Server 2005 that I installed on my PC. I have classic ASP programs on my PC that used to access the SQL Server 7 database. However, global.asa and these ASP programs can no longer connect to the new SS 2005 database.
View 9 Replies
View Related
Dec 19, 2007
Hello everyone,
I have the sql connection string configured in my web application as
data source=(local); provider=sqloledb.1; user id=<<user id>>; password=<<password>>; database=<<database>>
My understanding is that when (local) is used, the connection method will fall to shared memory / named pipes. I have names pipes and TCP/IP enabled in the configuration manager.
My machine is also running Comodo Firewall .
Now this is what is happening. When I try to access the asp.net application, I get a component exception in comodo firewall that indicates w3wp.exe is trying to access the sql server via the machine's IP over port 1433. This happens when I try to load the application in the browser.
What I dont understand is that when (local) is configured, it should only access thru shared memory / named pipes. Why is it trying to connect thru TCP port. Is this because I have the sql username and password? My SQL server is configured for mixed mode authentication.
Thanks
RK
View 5 Replies
View Related
May 7, 2004
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection
Newbie here, so thanks in advance for any help. SQL Server 2000 on Windows 2000, Mixed Mode authentication IS enabled.
System DSN set on 8 workstations, a mix of 2000 - 98 and XP Pro
App uses DSN connection, specifies sa user and password. DSN's all set up the same, Client config is TCP/IP, SQL Server also set to only accept TCP/IP.
Trying to authenticate using SQL Server Authentication. XP Pro machines generate the above error. 98 and 2000 machines connect flawlessly.
I am stumped. Have read the MS knowledgebase articles, read threads here. Nothing seems to apply. It's an Internet app, so using Windows Authentication is not desired. I just don't understand why XP pukes while the other MS platforms connect just fine.
If anyone has any thoughts, I'd appreciate suggestions!
Thanks
View 6 Replies
View Related
Apr 2, 2007
HI Everyone,
I understand that there is a 4GB size limitation on SQL Server Express edition. right?
What I want to know is what if a database file created in SQL Express is hosted with SQL Server 2005 will the file still have the 4 GB size limitations?
Thanks
View 9 Replies
View Related
May 11, 2007
Hello,
It seems that there is some kind of limitation in SQL Server agent job log. For any job there is not more than 1000 rows of history. This can be seen with the following query:
SELECT j.name, count(*)
FROM sysjobhistory jh,
sysjobs j
WHERE jh.job_id = j.job_id
GROUP BY j.name
HAVING count(*) >= 1000;
How to modify the limitation? We really need more than 1000 rows.
r,
J
View 5 Replies
View Related
Oct 7, 2015
I've never worked with the XML data type in SQL Server, although I know its been there for a few iterations of SQL SErver. Now I've got a situation in which it might store some configuration data as XML, since that's the way it comes. (We had thought about storing the data in a VARCHAR(MAX) field.)
The first question is does the XML data type have a size limitation? For example do you do something like:
ConfigFile XML(1000) NULL
Or is it just something like this:
ConfigFile XML NULL
The second question is persisting the data to a file. As the name I choose for the variable suggests, we want to save the data from a configuration file into a SQL Server database. How do we go about doing that? We'll be developing a C# application, it will read and write the data both from the SQL table and the user's local HD.
View 5 Replies
View Related
May 21, 2007
Hello,
Say that I have 100,000 attributes/feature selections for my SQL Server Neural Network Algorithm.
Customer Attr1 Attr2 Attr3 ..... Atr100000
==============================
Jack 1 0 1 ..... 1
Sam 0 1 1 ...... 0
Mary 1 1 0 ...... 1
Knowing the fact I can't fit those info on a table and SQL Server's Neural Network does not support table prediction . What's an alternative to use Neural Network in SQL Server 2005 to solve my problem?
Please assist!
Mary
View 5 Replies
View Related
May 14, 2007
I want to know if it is possible to connect from classic ASP to a SQL 2005 database, and if so, can someone provide a sample connection string, I'm not sure if it is my connection string or server config. Thanks, Ed.
View 5 Replies
View Related
Jun 24, 2006
Hello!
I'm trying to move from MSDE to SQL Everywhere in my project, which requires using of ADO classic and i have some problems storing ntext data in SQLEv database using parametrized command.
Could it be that the problem is because of ADO classic? Is it ok to use old ADO with SQLEv OLEDB provider and will compatibility with old ADO be maintained in SQLEv?
Below is JScript code illustrating the situation:
-------------------------------
var oConn = new ActiveXObject("ADODB.Connection");
oConn.Open(sConnStr);
var oCmd = new ActiveXObject("ADODB.Command");
oCmd.ActiveConnection = oConn;
var sText = "test text";
oConn.Execute("create table tblTest(sText ntext)");
oCmd.CommandText = "insert into tblTest(sText) values(?)";
oCmd.Parameters(0).Value = sText;
oCmd.Parameters(0).Size = sText.length;
oCmd.Execute();
-------------------------------
It gives me 'Microsoft SQL Server 2005 Everywhere Edition OLE DB Provider: The given type name was unrecognized. [,,,,,]'.
If i remove 'oCmd.Parameters(0).Size = sText.length;' error changes to 'Insufficient memory to complete the operation.'
The same code works ok for MSDE and SQLExpress.
OS:Windowx XP SP2
MDAC version: 2.8
Many thanks!
View 3 Replies
View Related
Dec 9, 2014
how to know the limitation of number of objects(Maximum no.of objects allow tempdb database) in a tempdb database?
View 2 Replies
View Related
May 3, 2007
Hi I have written a piece of code for Login form which reads the user id and password from db. It works fine with the Sql server 2000 but I get a error with Sql server 2005. SqlConnection conn = new SqlConnection("Data Source=D\SQLEXPRESS;Initial Catalog=model;Integrated Security=True"); SqlCommand cmd = new SqlCommand("Select * from JsLoginDetails", conn); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if ((Login1.UserName == dr.GetValue(0).ToString()) && Login1.Password == dr.GetValue(1).ToString()) { Response.Redirect("MainJs.aspx"); } else { Login1.FailureText = "Invalid Userid Or Password"; } } dr.Dispose(); conn.Close(); } I get and error Invalid object name 'JsLoginDetails'. pls help thnksdiv
View 1 Replies
View Related
Nov 16, 2005
Migrating a site to a new server. Moved data to SQLExpress. Changed connection string to point to SQLExpress and now I am getting this error.
Microsoft OLE DB Provider for SQL Server error '80004005'
Cannot open database "PHCSQL" requested by the login. The login failed.
/func/inc_DatabaseFunctions.inc, line 5 Do I need to do permissions differently than MSDE?Did connection strings change?Help?
View 1 Replies
View Related
Apr 28, 2006
hi,i'm using classic asp to try and connect to a sqlexpress database on a development server. i get the following error:
Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
/dbtest.asp, line 8
I'm using the following script which runs fine against a regular SQL server (version 8) on the network.
<%@LANGUAGE="JAVASCRIPT"%>
<%
var strCon, conn, sql;
strCon = "Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=rapidHB;User Id=rapid;Password=xxx";
conn=Server.CreateObject("ADODB.Connection");
conn.Open(strCon);
sql = "SELECT product_code FROM products WHERE product_type = 1";
var results= conn.Execute(sql).GetString();
Response.write(unescape(results));
%>
I have tried changing Data Source to servernameSQLEXPRESS, changing initial catalog to master, using a user name defined on the database and changing the provider to SQLNCLI but nothing has worked.
Anyone got any idea what I'm doing wrong? Using ASP.Net is not an option.
Rgds,
lukemack
View 9 Replies
View Related
Oct 6, 2007
Is there a way, other than simply changing the format of the SRS report, to create an old fashond Paged style report - similar to the ones you can get from Crystal Reports?
View 6 Replies
View Related
Nov 13, 2006
If I install SQL 2005 Standard on Windows 2003 Standard, is SQL limited to 4 gigs of physical RAM?
I'm planning a new system that will run SQL 2005 Standard edition on a Windows 2003 Standard platform. The spec calls for 8 GB of RAM. My experience would lead me to suspect I need to install Windows 2003 Enterprise to take advantage of all the installed memory.
View 3 Replies
View Related
Feb 21, 2008
I have a Classic ASP page that provides me a view on Orders posted by customer for a selected month and year from a SQL Server 2000 database. This ASP page has a Stored Procedure that returns the orders posted by month and year. However, my needs are to be able to display the view by month, day and year to month, day and year, For example January 15, 2008 to February 14, 2008.
The current Classic ASP page has a dropdown to select the month and year from this dropdown only displays months and years for the months and years the customer had posted orders.
What I’d like to know is how to add to the classic asp page the means to input a date range on the fly that would return the report by month, day, year to month, day, year.
View 4 Replies
View Related
Apr 26, 2008
Classic problem..
Say I have two simple tables (one with members the other with status
changes):
CREATE TABLE [dbo].[member](
[memkey] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mname] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dob] [datetime] NULL,
[effective] [datetime] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[memberstatuschange](
[memkey] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[transtype] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL,
[date] [datetime] NOT NULL
) ON [PRIMARY]
sorry missing referential for simple example.
select m.memkey,m.effective,s.transtype, s.date from member m left
join memberstatuschange s
on m.memkey = s.memkey
produces:
001 2006-11-01 00:00:00.000 1 2007-12-01 00:00:00.000
001 2006-11-01 00:00:00.000 2 2007-02-02 00:00:00.000
001 2006-11-01 00:00:00.000 1 2007-02-05 00:00:00.000
002 2006-11-02 00:00:00.000 1 2007-06-01 00:00:00.000
003 2006-11-03 00:00:00.000 1 2007-07-01 00:00:00.000
003 2006-11-03 00:00:00.000 2 2007-08-02 00:00:00.000
004 2006-01-01 00:00:00.000 NULL NULL
005 2007-01-01 00:00:00.000 NULL NULL
006 2007-02-05 00:00:00.000 NULL NULL
transtype 1 = stop, 2 = restart.
effective date marks the start the original start of membership. So
in the case of memkey 001 he started on 2006-11-01, stopped on
2007-12-01, restarted on 2007-02-02 and stopped again on 2/5/2007.
memkeys 004,005 and 006 don't have NO status changes.
presuming that we should never have a status code of restart without a
stop but that the data may have violations of that.
How can I query my tables to return every date of ACTIVE participation
during two dates?
So for example if I say give participations criterias of 11/7/2006
and 2/7/2007, it should pull two records for memkey 001 as follows
memkey, days, start, stop
001, 6, 11/1/2006, 11/7/2006
001, 2, 2/5/2007, 2/7/2007
Thank you for any help or information!!.
View 1 Replies
View Related
Oct 1, 2007
Microsoft VBScript runtime error '800a01fb'
An exception occurred: 'EOF'
Used the same connection string and DSN (i.e pointing to the same database/server) in both environments. ASP works in Test Environment. Fails in Production Environment.
Test and Production have same versions of software.
Environment Details:
OS: Windows 2003 SP2
MDAC: 2.8
Database: HP Neoview
Any help appreciated.
Thanks,
Venkata.
View 1 Replies
View Related
Jun 14, 2007
Hi forum, thought about time I updated from OleDB to SqlClient.
My remote host company supports MS SQL Server 2005, I have created a testsql.mdf, now I want to connect to that database,( im used to writing Access conn like below and have updated my OleDB code to Sql ).
<%@ Import Namespace="System.Data"%><%@ Import Namespace="System.Data.OleDb" %>
strAccessConn ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\inetpub\vhosts\blablabla\httpdoc\App_Data\testdb1.mdb";
How do I write the SQL connection string?
<%@ Import Namespace="System.Data"%><%@ Import Namespace="System.Data.SqlClient" %>
strSqlConn =?????? this bit ?????
This is the remote host path
LocalSqlServer
data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true
This my first SQL data application so need some help thanks Paul
View 1 Replies
View Related
Apr 7, 2005
Hi All -
Prior to ASP .Net I use to create a connection object for SQL Server in my global.asa file and I would reference the obj whenever needed. how can I do the same in asp .net.
Here is my old asp sample:
Global.asa file:
Sub Session_OnStart
set session("conn") = createobject("adodb.connection") session("conn").Open "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=xxx.xxx.xxx.xxx;DATABASE=xxxxx_db;UID=xx;PWD=xxxxxxxx;"
End Sub
Call from Other Pages:
sqlstr = "select * from members WHERE memberid = '" & session("mbr_memberno") & "'" set objRS = session("conn").Execute(sqlstr)
... How can I do this in .net?
rich
View 5 Replies
View Related
Nov 1, 2007
Hello everyone. I'm stuck with what must be a common sql challenge.
I've got this single table that looks like this:
Store, Original Store, Product, Sale type, Method, Amount
There will be two product types (prodA and prodB)
There will be two Sale Types (New and Recharge)
There will be two Method (Cash and Credit)
The tricky part is I also need to report sales activity when a store
was the Original Store for a customer Recharge at another Store.
The report needs to look like this:
Store , Product, Cash New Total, Cash Recharge Total, Credit New
Total, Credit Recharge Total, Outside Recharge Total
Grouping/Totally on Store/Product.
In the above "Outside Recharges" means the Store was the Original store
and another store performed a recharge on it's customer.
Thanks for any help or information
View 3 Replies
View Related
Oct 23, 2004
I have downloaded some code on how to upload images to a sql database and I am getting
this error message
"SQL Server does not exist or access denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied."
I have opened Enterprise Manager and made sure I was a "user" and everything seems to be ok there.
The connection string looks like this
"server=localhost;uid=sa;pwd=;database=ImageUpload"
Could anyone give me an idea on what to check or try next
Thanks
View 2 Replies
View Related
Dec 26, 2004
My MS SQL Server was working perfectly fine locally when I used the following connection string:
server=(local)\DB;Integrated Security=True;uid=;pwd=;database=AspNetDb
When I moved my SQL server to another machine within my LAN, it stopped functioning.
server=(192.168.1.3)\DB;IntegratedSecurity=True;uid=;pwd=;database=AspNetDb
Could someone kindly give me some advice? I appreciate your assistance.
View 1 Replies
View Related
Apr 19, 2000
Friends,
Does anyone know how to create trusted connections?. What I want to do is to have
connection to a sql server that's in a different domain as I am (a NT workstation).
I tried to create a login id on the server with my nt id but got an error:
"15401: NT group or user not found"
I msut be missing some steps.
Thanks in advance.
View 2 Replies
View Related
Mar 12, 2007
What is the impact of the differences in the following 2 connection strings:
Initial Catalog=xxx;Data Source=xxxxxx;Trusted_Connection=True;
Driver={SQL Server};Server=xxxxx;database=xxx;Trusted_Connection=yes;
To outline the differences I'm concerned with are
Missing Driver on first string
Difference in Trusted_Connection parameter (true vs. yes)
Initial Catalog vs. database parameter
View 1 Replies
View Related
Aug 9, 2007
Hi Forum,
I am having issues with hosted Sql05 server conn. Ive made the move from Oledb to Sql, so am unsure if trouble is permissions or connection related!
Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.
<connectionStrings> <remove name="LocalSqlServer" /> <add name="ConnectionString" connectionString="Data Source=**.**.**.**;Initial Catalog=***_Database.mdf;Integrated Security=True;" /> </connectionStrings> <system.web> <roleManager enabled="true" defaultProvider="SqlRoleProvider"> <providers> <clear /> <add name="SqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ConnectionString" /> </providers> </roleManager>
Ive tried some conn varients re security id, but its always the same error. I can use SQL Server Management Exp to login/manage database, using SQL authentication.
The web app is the conn I cant open, hosting company support useles, now thats something new, cheers P
View 4 Replies
View Related
Dec 16, 2007
Just installed AJAX for VS2005 and started to build a AJAX enabled web site. I got the following message after creating a database (SQL) and Dataset "FOLLOWING ERROR OCCURED WHLE GETTING CONNECTION STRING INFORMATION FROM CONFIGURATION:'CANNOT GET WEB APPLICATION SERVICE'.
I didn't have this problem before,can it be related to AJAX?
Thank you in advance,
View 1 Replies
View Related
