We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System; using System.Collections.Generic; using System.Text; using System.Net; using System.IO;
namespace Predict.Services { public static class Foo { public static string GetIntradayQuote(string symbol) { string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close(); response.Close();
return returnQuote; } } }
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?
I have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
I am in the process of planning a server upgrade to sql2005 x64.
I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.
I have the following error when I query the linked servers. OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error". OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.
I am aware of KB 906954. http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.
However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.
Hi, I am new to SQL Server 2005. I tried connecting to my local machine by using my machine name as Server name and then tried running the following query: SELECT * FROM SYS.Objects. It gives me following error: Invalid object name 'sys.objects'.
Whereas, if I connect to my local machine using mahcinenameSQLEXPRESS, then the above mentioned query runs fine.
Why is this difference? What is the difference when I login in these 2 different ways. Any help would be appreciated.
I'm trying to make a searching tool in my web site that will search in one table in databaseWhat i want to do is to search by three columns and shows the result if the string found in one of this three columns, so here what i tried to and its not working okay, coz sometimes it works and other not while entering correct stringstring Search = Request.QueryString["s"].ToString(); string Sql = "SELECT * FROM Topics WHERE TopicName LIKE '% " + Search + " %' OR";Sql += " TopicIntro LIKE '% " + Search + " %' OR TopicBody LIKE '% " + Search + " %'";I also wanna know how to remove quotations and other stuff that shows error message if entered in the TextBoxThank you alot
HelloI am getting this err msg when trying to logon to my website on the web - on my machine there is no prob, An error occurred while established a connection to the server.When connection to Sql.S 2005, this failure may be caused by the fact thatunder the default settings sql.s doesn't allow remote connection.(provider :Sql.S Network interface, error:26- Error Location Server/Interface Specified)(the files path is like in the web host s)
Hi everyone. This is my first post here. I did a quick search and didn't find what I as looking for, so I'm hoping you all can help me.
I'm working on a project that will allow my company to upload images to a database and have the website pull the images from the DB. We are planning to incorporate some type of cacheing, however I'm skeptical as to if it is really necessary. I imagine that I'm certianly not the first to do this and I'm wondering if someone can let me know if I truly should expect a performance hit, or if I shouldn't really worry about it b/c this is a very common mantra.
Thanks all. I've been pretty vague and haven't explained all of the details. I can write more, but I'm just looking for generalizations right now.
I am new to .NET and the Visual Web Developer Express and what not...I have created a website on my machine which works wonderfully...however, and I'm sure this is somewhere but I can't find it, I cannot get it to work on my host server...the Default page comes up, but when I try to sign in it says: "An attempt to attach an auto-named database for file c:hostingwebhost4lifememberharrison0801App_Dataaspnetdb.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share." The host support tells me to change the db name from aspnetdb to something else...I do this and when I run the site again or try to log in, I get the exact same error message...then they go on to tell me that I have to remove the LocalSqlServer in the web.config file which now looks like this: <connectionStrings> <remove name="LocalSqlServer"/> <add name="LocalSqlServer" connectionString="Data Source=.SQLEXPRESS;Integrated Security=True;AttachDbFilename=|DataDirectory|aspnetdb.mdf;User instance=true;" providerName="System.Data.SqlClient" /> <add name="Example" connectionString="Data Source=.SQLEXPRESS;Integrated Security=True;AttachDbFilename=|DataDirectory|example.mdf;User instance=true;Initial Catalog=example;" providerName="System.Data.SqlClient" /> </connectionStrings> Of course this isn't working either...otherwise I would off playing on my server, and not posting here. Also, when I set up all the roles, users, and what not on my machine...does this carry over to the host machine? Desperately needing massive help here.... Not a DBA and no expert in .NET Thank you very much in advance, Darren
Hi I want to have a report on a website page that will respond to user input.
Basically a page that will displays requests when the user inputs a user_id; the user will then need to be able to drill down and select a specific request to display and print that request.
It's been a long time since I last experimented with SQL Server (2001) so I am not sure of new product features or developer techniques.
A client of mine is wanting to deploy an E-Commerce website and one if the core requirement is that the site must integrate with his internal MS SQL Database i.e. Orders placed online must be seamlessly entered into his internal Sales database. Ideally this would not require any re-keying or daily batch imports.
Many web hosts offer SQL hosting so how (if it is possible) could I link the hosted database to the internal database?
I have a web page where my users are selecting a bunch of filters to generate a report. The page then pulls the initial data from the database, applies some adjustments to the records, and then puts the data in a temporary table for that user. I want to create a report template (or a report saved on the report server) for this report. I want to use the same report for each user but be able to change the datasource for the report on the fly for the current user's temp table in my webpage before displaying the report. Is this possible?
Ok, I have this up and running thanks to the help of lots of users in this forum (Thank You again)
I deployed some reports, and everything looks fine, however to add another twist to this, I would like the website to look prettier, by adding something like a welcome page,and some folders and such.
Does anyone have any ideas on how to do that ??? DO you have any material or a website, where I can get some info ??
I wanted to make a login for custom content on my asp website. I installed 2005 sql, gave it a named instance(sql34). I set the connections to local and remote for tcp only in the "surface area config", and made the tcp enabled in "sql manager". All of the sql services are started. So i open up the "server management studio" and it prompts me for a connection. I sellect "database engine" because thats what i installed. I typed the server's name in the "server name" and select sql server authentication for the "authentication", and use my username and password for the login. Then I get a message cannot connect: the default settings sql server does not allow remote connections(provider:named pipes provider,error:40-could not open a connections to sql server)(microsoft sql server, error:2) What is the deal with this?
Another problem was i saw the sql express does not integrate with the real sql 2005. So how do i make mywebsite's web.config(express edition) to integrate for my real sql 2005 server? An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified
I'm a newbie with SQL Server Reporting Services. I've played around with creating reports and I'm now ready to move on with calling reports from my C# .Net website. Does anyone know of a good tutorial to get me started? I'm specifically interested in building the SQL string on the fly using responses from the my website user and then using that string to populate the report. Thanks!
Hi, Im currently running a website in conjuction with MSSQL Express. However at the moment the website/database seems to 'sleep' when no-one is using it, then there are huge dealays accessing the data while the database/website kicks in again. I tried to google this but truthfully i don't know what i am looking for. Does anyone have any ideas? Or whether this problem is Database or website driven? I thought there might be a control in the web.config but i haven't found a solution as yet! Thanks Daniel
Hi, I'm new in asp.net and I have few questions. My hoster have the SQL server and the webserver on different machine. I need to use a login to restrict acces to some webpages and I see that asp.net is storing that data in App_data/ASPNETDB.MDF which have the connection string set to local server. Can asp store that data on a remote database?
Since from using my local host to view my website, I am assuming I already have SQL Server installed on my computer? Is it possible to have users access my website files directly onto there own computer, say if they type my ip address or domain name into their address bar? What I am actually wanting to do is to host my website from my home computer... Any ideas, or good slash easy to understand articles on how to do this?
Hi! Not sure if this is the right place to post. I need to copy a website that I created on a CD. It is using SQL Server as the database and VB, ASP as front end. However, the problem is that I need to copy it in such a way so that it can be viewed from the CD itseld without needing MSDE or SQL Server. Need help. Thanks.
Hi! Maybe I ask simple question. But for me it’s not so. The matter’s I plan organize search at my website. It has catalog of firm’s and good’s in SQL Server. I plan to build dictionary table with two fields’ search_item and link. But problem is that I should put all search variant’s in field search_item(name of firm, name of boss, name of good etc.) Maybe there’s another concept to build search? Thank’s for any answer.
Hello everyone..... First of all i have to say that a network engineer not a programmer. So here is my question..... A contractor built a web application in visual studio 2005 and sql server 2005 expess. It is on a windows xp pro developement box.... and the site runs in VS2005 but when it is put into IIS i get SQL errors and nothing runs. Can anyone help me sort out this issue because it has been dumped in my lap and i'm clueless....... when you goto the site you are supposed to be able to loging to a server page ( not windows Authentication) however, when you enter a name and password to log in it returns a sql error. i'm really getting tired of this issue so any help would be appreciated. I have included the SQLexpress error below.... thanks in advance!
Server Error in '/' Application.
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. 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: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: