Accessing Another DB Through A View
Mar 12, 2008
I have to troubleshoot a problem with a complex sproc that is running against a number of views that select entire tables from another "production" database. The problem we are having is that while the views should be read-only, there are no transactions, and no locking, the application's process on SQL Server is blocking anyone else who tries to write data. I'm wondering if this method of using a view as a proxy to another DB is effecting the block. The reason for doing this was to improve performance by offloading the production DB, but since it's the same instance of SQL Server, does it really matter? THX for your advice.
Jerry O'Brien
View 1 Replies
ADVERTISEMENT
May 28, 2008
Hi all, I'm setting up an ODBC connection for the UPS WorldShip application. I created a view for the shipment data in my database. The user account I'm using can only access the view, and when I try to connect it says there are no tables. Is there a way to make ODBC "think" the view is a table? TIA! - Mark
View 4 Replies
View Related
Jun 7, 2004
Hiya folks,
I'n need to access a view from within a SProc, to see if the view returns a recordset and if it does assign one the of the fields that the view returns into a variable.
The syntax I'm using is as follows :
SELECT TOP 1 @MyJobN = IJobN FROM MyView
I keep getting an object unknown error (MyView). I've also tried calling it with the 'owner' tags.
SELECT TOP 1 @MyJobN = IJobN FROM LimsLive.dbo.MyView
But alas to no avail!
Any offers kind people??
View 12 Replies
View Related
Feb 15, 2007
How is it possible for others to browse to my localmachine and view the reports?
They tried browsing to http://machinename/reports
and http://10.1.3.123/reports
But the message the users get is: You are not authorized to view this page.
Then In my localmachine, I changed the IIS setting for the Reports and ReportServer to allow anonymous access. Then the users were able to see the Home page of the reports but not the reports themselves to click on for viewing. Any thoughts please?
Thanks
View 1 Replies
View Related
Jul 23, 2005
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies
View Related
Jul 24, 2012
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
View 2 Replies
View Related
Aug 24, 2007
Hi guys 'n gals,
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
View 3 Replies
View Related
Mar 9, 2006
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies
View Related
Aug 14, 2000
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
View 1 Replies
View Related
Feb 17, 2006
I have this view in SQL server:
CREATE VIEW dbo.vwFeat
AS
SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4
FROM dbo.Lk_Feat INNER JOIN
dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application.
Thanks for help
View 4 Replies
View Related
Sep 14, 2004
Good Morning,
I am wondering if anyone can help me/ Currently I have created an ASP.NET webform. Usingt the VS.NET C# IDE I have put a SQL connection and DataAdapter on the webform. I have then created a dataset which should hold the contents of a table on a SQL server db.
However when i run the page which just tries to fill the dataset i get the error below. I am running the page via IIS on WinXP Pro SP1 system and SQL Server is located on a Win2003 Svr.
Can anyone help?
Thanks & Regards,
Alan
Server Error in '/Quiz' Application.
--------------------------------------------------------------------------------
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.
Source Error:
Line 38: // Put user code to initialize the page here
Line 39:
Line 40: sqlConsumerDataAdapter.Fill(consumerDS11);
Line 41:
Source File: c:inetpubwwwrootquizwebform1.aspx.cs Line: 40
Stack Trace:
[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +484
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
Quiz.WebForm1.Page_Load(Object sender, EventArgs e) in c:inetpubwwwrootquizwebform1.aspx.cs:40
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
View 7 Replies
View Related
Oct 13, 2006
Hi!
I have two assets: a URL that points to an XML file, and a stored procedure that can accept this file as a text variable and store it in a SQL 2005 table.
create procedure [dbo].[insertObjects]
@availabilityXml text
as
DECLARE @xmlHndAdd INT
EXEC sp_xml_prepareDocument @xmlHndAdd OUTPUT, @availabilityXml
TRUNCATE TABLE Objects
INSERT Objects
SELECT *
FROM OPENXML(@xmlHndAdd, '//NewDataSet/Table1', 2)
WITH Objects
Now, I need to find a solution to combine the URL with the proc. Does anyone have any suggestions on how I can pass my URL as a text variable to the procedure? SSIS, vb-script, etc. are welcome!
Thank you!
View 5 Replies
View Related
Nov 13, 2006
I am trying to execute an SSIS package (which accesses a dbf file through an odbc connection) through a Sql job, but the package log reports an error of "Disk or netowrk error". When I execute the package in the IDE, the package executes fine. When I run the manifest on the DB server, I can execute the package with no errors. But, when I create the job, and try to execute the job, it fails. I thought at first that the user didn't have privileges to the directory that the file existed in, but that isn't the case.
Can anyone shed some light on how to accomplish what I am trying to accomplish? It seems like this would be a common use of SSIS, but I cannot seem to get it to work.
Thanks in advance for any assistance you can provide!
Craig
View 2 Replies
View Related
Sep 16, 2006
Hi, I am developing a small asp.net application where I read from a readonly omnis database via odbc then dependant on user input write records to a second database.My first idea was to use an odbc connection to Omnis database and write to ms sql database. There is quite a few queries that need to be made to the Omnis database and I am binding a datagrid to the MS Sql server database. I was hoping to use stored procedures to update the mssql tables. So there will be much opening and closing of connections. Should I go down that road or should i think of using an Access database, with linked table to the Omnis database, then only one connection needs to be made. And I can store my information in the Access database tables. Thanks.
View 3 Replies
View Related
Dec 10, 2006
Hello,I just uploaded a new SQL database to my hosting server.I am using SQL 2005 and SQL Server Management Studio.How
can I remotely access the SQL database, on my hosting server, to create
or alter tables and queries, using SQL Server Management Studio? Thank You,Miguel
View 2 Replies
View Related
Jan 24, 2007
I have a SqlDataSource that returns a list of companies and their details by ProductID. It also returns the name of the product associated with the ProductID as the final column (which means it appears for every record returned). I already have a way of determining how many rows were returned, and use that information in a label to say "Your search has returned x records". protected void dsGetSuppliersByProduct_Selected(object sender, SqlDataSourceStatusEventArgs e) { int RecordCount = e.AffectedRows; if (RecordCount == 0) { lblRecordCount.Text = "<p>No Records found"; } else { if (RecordCount == 1) { lblRecordCount.Text = "<p>Your search returned 1 record"; } else { lblRecordCount.Text = "<p>Your search returned " + RecordCount + " records"; } } string ProductName; }How can I access the ProductName value so that I can extend the label text to say "Your search has returned x records for <ProductName>" ?
View 1 Replies
View Related
Apr 7, 2008
Hey All
I wish to display the 3rd and 4th to last records of a table on a page. Is there anyway of doing this? I can access the last records by using Count and TOP 1 but i find that count - 1 as the ID to select can be errornous if records are removed from the table. I also tried using ORBER BY followed by LIMIT but kept getting an error in order by clause message.
Any help would be greatly appreciated
Thanks and Regards
Drew
View 6 Replies
View Related
Jun 11, 2008
Hi all,
I am experiencing the following error when I try to access a T-SQL UDF from within a SQL statement that I execute from a CLR UDF.Msg 6522, Level 16, State 1, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "HelloWorldCLR":
System.Data.SqlClient.SqlException: This statement has attempted to access data whose access is restricted by the assembly.
System.Data.SqlClient.SqlException:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages)
at Microsoft.SqlServer.Server.SmiEventSink_Default.DispatchMessages(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlDataReaderSmi.InternalNextResult(Boolean ignoreNonFatalMessages)
at System.Data.SqlClient.SqlDataReaderSmi.NextResult()
at System.Data.SqlClient.SqlCommand.RunExecuteReaderSmi(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteScalar()
at UserDefinedFunctions.HelloWorldCLR()I have tried the SQL BOL and Google with no luck.
Here is the sample code I am using to produce this error:
HelloWorldUDF.sqlIF OBJECT_ID (N'dbo.HelloWorldUDF') IS NOT NULL
DROP FUNCTION dbo.HelloWorldUDF
GO
CREATE FUNCTION dbo.HelloWorldUDF() RETURNS varchar(100)
AS
BEGIN
RETURN 'Hello World'
END
GOHelloWorldCLR.cs using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction( DataAccess = DataAccessKind.Read )]
public static SqlString HelloWorldCLR()
{
using ( SqlConnection conn = new SqlConnection( "context connection=true" ) )
{
conn.Open();
SqlCommand command = new SqlCommand( "SELECT dbo.HelloWorldUDF()", conn );
return new SqlString( command.ExecuteScalar().ToString() );
}
}
};
Test query: select dbo.HelloWorldUDF()
select dbo.HelloWorldCLR() Could some one please confirm that this should be possible or not? Thanks! Joe.
View 3 Replies
View Related
Dec 10, 2004
Hi
If my site is hosted with an external company web host can I use ASP.NET pages to connect to a MS SQL DB that is on my company's internal server if I had MS SQL installed on it?
My company's server is always on and always connected to the internet.
I have access to my company's server if I need to set anything up on it for this to happen.
There will not be any kind of overload as the site is not accessed constantly.
Also, would this cause any kind of security leak? If so how would I go about plugging this hole?
Our server does have confidential info on it.
Cheers
kefi2927
View 1 Replies
View Related
Oct 11, 2000
I am trying to open some DTS packages from another machine, I see the list but when I try to open one and I get the error: The Parameter is incorrect
I have look at permissions and everything looks good. (I can view about 2 out of 20 packages all packages have the same owner.)
Any would be great.
Thanks
Jacqi
View 1 Replies
View Related
Jul 9, 1999
I'm using OLEDB in my ASP code to connect to the SQL database. When I execute the page I got an error saying "438, Microsoft VBScript runtime error, Object doesn't support this property or method"
Anybody knows what this is in reference to.
I tested the same site in another server where IIS and SQL are in the same box and it's working fine.
Thanks for any input in this......
View 1 Replies
View Related
Jul 19, 2004
I need to write a VB app (VB6) to access and manipulate data within a MS SQL Sever 2000 database. I have experience doing this with MS Access. Is it much different/more difficult to do this with SQL Server?
Thanks
View 5 Replies
View Related
Nov 25, 2004
What is the easiest way to start executing SQL statements onto a SQL Server 2000 using C++ ?????
Are there any libraries or something I could use ?
View 2 Replies
View Related
Nov 24, 2005
Hi All,
Can I connect to the MS SQL database from the Redhat Linux 9.
Is there MS SQL client or database driver on linux available. I wanted to use the data from the MS SQL database in the C++ program running on Linux.
Thanks in advance,
torpedo
View 2 Replies
View Related
Jan 6, 2006
Dear All,
Can someone let me know how I can have a look at the transaction log in SQL server. I'd like to find out what INSERT, UPDATE, DELETE commands have been executed on a table today, yesterday etc.
Can I see this easily?
View 8 Replies
View Related
Apr 12, 2006
I'm new to databases as you probably can tell. A friend sent me three data bases, which in my mind should have been one database with all the tables included, but he didn't. So my question is, can you pull info from two databases in the same query?
I write the query acessing one database and the result chart must depend on a where predicate based on accessing tables in another database. If this isn't possible, how do I get the tables I need together, that presently reside in differing databases?
Thanks for the help in advance.
Milfredo
View 8 Replies
View Related
Jan 5, 2004
Hello all,
We are planning to migrate an application written in Perl + mySQL to .NET + MS-SQL Server. The test machine now has SQL Server running. During the development phase in .NET, we would want to access the mySQL database which is being updated at real time, from the Windows box. This is how I think it has to be done:
1. Use DTS to migrate data from mySQL. Does DTS take care of migrating schema and the difference in data types between the mySQL and MS-SQL? Is there any 3rd party software out there that takes care of the migration without any manual labor?
2. Synchronize the data between 2 DBs by creating some kind of a batch job? If so, whats the best way of doing this?
Instead, is it easier to access the live mySQL database on the Linux box from the .NET code and move the DB only after development in the new environment is completed?
Any help/ideas is greatly appreciated.
TIA
View 2 Replies
View Related
Mar 29, 2004
I want two things:
-Let the windows account be the login on the SQL server. This is not a problem.
1.But I also want to use the windows login as authentication method for the webpage. The users have to explicitly log-in.
2.Is it a problem to retrieve all users within a windows domain?
I’m using .net 2003.
View 2 Replies
View Related
May 7, 2004
hello all,
i had two stored procedures,where the 1st SP is calling 2nd SP(which returns 2 record sets).
how can i access the second recordset from the 1st SP
it would a great help if anyone help me out!!
thanx in advance,
ravi.
View 2 Replies
View Related
Apr 29, 2008
Hello, me and my friend has set up an SQL, I am wondering if it is possible to access the SQL server from my computer when hosted on his.
thanks
View 1 Replies
View Related
Dec 16, 2007
Hello everybody.
I'm having a problem:
As the result of Dataflow task I get a recordset with about 50 columns in it. Now I need to loop through each row in the dataset and make some manipulations with the data in all 50 columns in a single script task. So I need 'em as separate variables.
Ofcourse I can make a For Each Loop container with ADO enumerator, and map each column to specific variable. But that's not a very good thing, because if the format changes, i will have to remap everything again (takes a lot of time to remap 50 values)
So is it possible:
1. To map using not index of columns, but there names?
2. To pass actual row into the script task?
View 7 Replies
View Related
Apr 6, 2006
Hi
Would anyone be able to advise me on the best way in which i am able to display the information from a Search in sqlce database and allow the results be displayed in a specified location.
An example is in my program there is a list of id which the user can search. They search for a record by id so only one record will return. I would like this information to then be displayed in the textboxes which are on the screen.
Any problems understanding what i am trying to do please ask. Any help would be appreciated
Thanks
View 3 Replies
View Related
May 15, 2008
Hi,
I am trying access a FTP site present from a different server (remote work station). I am getting "The page cannot be displayed" error. I am able to access from my local machine.
Please help me solving this.
Regards,
Sriram.
View 3 Replies
View Related
