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.
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?
Hi everyone, First of all I'd like to say I'm new to the forums, and in fact new to ASP.net. I've had a couple of applications running in VB.net for the last year or so that I now am looking to move on and into ASP.net (main reason is to do with implementation to remote sites in foreign countries). I'm currently following through the "Working with data and ASP.net 2.0" walkthrough on this site, which I must say has been a great help. I'll start with the background stuff first I have my main database on an SQL server. I also have 2 other databases on there - Customer and Employee. The reason for these are that they are shared amongst my VB applications and I kept them as seperate databases so that one database is all that needs to be administered and maintained, and these changes are then seen on both apps (takes away redundant data as well). In my main database, main table, is a field called CustomerID. This value relates to the CustomerID in the Customer table. In my VB app all I do is have the user select a customer name, and then the customer ID value is copied to the CustomerID field of my main table when inserting / updating a row. This is simply accomplished with 2 SQL connections and 2 dataAdapters. I also use 2 dataSets - one for Customer, one for WorkRequests (the main database)... I use ComponentOne stuff so that my grid will show the CustomerName instead of the CustomerID etc, and the same applies when I use my Employee database - I run many dataAdapters to this for things such as Account Manager, Technologist, Creator, UpdatedBy etc etc. Now, I'm new to VS 2005 - my apps were created in VS 2003 and .net 1.1. Can I easily re-create my apps in ASP.net? My primary goal at the moment is to make sure I can load a row, and display the "lookup" values instead of the "integer" value. As my tables are in seperate databases, I would like to know the best way of accomplishing this, if it is of course possible. Help and advice much appriciated. Kind Regards, Luke
Hello all. I have just built another PC (New PC)and have it connected to myOriginal PC via a LAN. I have my SQL Server 2000 installed on the OriginalPC. Both PCs use WinXP Pro OS. I believe I now have to install the ClientTools on the New PC but after that, how do I access the SQL Server that ison the Original PC. The SQL Server is setup with mixed (both WindowsAuthentication and SQL Server Authentication). Just how do I use the SQLQuery Analyzer that I just installed on the New PC to connect to andretreive data from the SQL Databases which are on the Original PC? Thanksin advance for any help, Jim.
Hey, Everyone - I have downloaded two of the sample databases and get the following error when I attempt to open them:
Unable to open the physical file "c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAAssets_Maintenance_log.ldf". Operating system error 2: "2(The system cannot find the file specified.)". (Microsoft SQL Server, Error: 5120)
So what am I doing wrong? The install of these sample databases is simple and straight-forward.
I am trying to determine if it is possible to add transaction support for a SSIS package. Here are the basic tasks:
Read New/Updated records from SQL Server Update changed records in Access Insert new records into Access Update new records in SQL Server with Access key assigned on insert in previous step Reset dirty_flag
I would like to wrap a transaction around this. If the package fails in the task that updates sql server with the Access key, the next time it runs, it duplicates the new records in Access because it did not finish the process. Is it possible to have a transaction wrap this entire process so I can undo the inserts to Access if I fail any subsequent tasks?
I've written this stored proc, which have to collect records in one table in several databases. These tables contains very few records (1 to 10 max). In SQL Server standard edition, this stored proc is executed instantaneously. But in the Express, it could take 1 minute.
Is there a solution to this problem ?
Thanks in advance,
Best regards,
Guy
ALTER procedure [dbo].[SP_CHECK_USERS]
AS
select 'FIDUCIAIRE' as code,moment,nom,machine from MERCATORFIDUCIAIRE.dbo.sessions s1 where (s1.inactif=0) and (s1.nom<>'MercatorIshop')
union select 'TEST1 ' as code,moment,nom,machine from MERCATORTEST1.dbo.sessions s2 where (s2.inactif=0) and (s2.nom<>'MercatorIshop')
union select 'TEST10 ' as code,moment,nom,machine from MERCATORTEST10.dbo.sessions s3 where (s3.inactif=0) and (s3.nom<>'MercatorIshop')
union select 'TEST11 ' as code,moment,nom,machine from MERCATORTEST11.dbo.sessions s4 where (s4.inactif=0) and (s4.nom<>'MercatorIshop')
union select 'TEST12 ' as code,moment,nom,machine from MERCATORTEST12.dbo.sessions s5 where (s5.inactif=0) and (s5.nom<>'MercatorIshop')
union select 'TEST13 ' as code,moment,nom,machine from MERCATORTEST13.dbo.sessions s6 where (s6.inactif=0) and (s6.nom<>'MercatorIshop')
union select 'TEST14 ' as code,moment,nom,machine from MERCATORTEST14.dbo.sessions s7 where (s7.inactif=0) and (s7.nom<>'MercatorIshop')
union select 'TEST15 ' as code,moment,nom,machine from MERCATORTEST15.dbo.sessions s8 where (s8.inactif=0) and (s8.nom<>'MercatorIshop')
union select 'TEST16 ' as code,moment,nom,machine from MERCATORTEST16.dbo.sessions s9 where (s9.inactif=0) and (s9.nom<>'MercatorIshop')
union select 'TEST17 ' as code,moment,nom,machine from MERCATORTEST17.dbo.sessions s10 where (s10.inactif=0) and (s10.nom<>'MercatorIshop')
union select 'TEST18 ' as code,moment,nom,machine from MERCATORTEST18.dbo.sessions s11 where (s11.inactif=0) and (s11.nom<>'MercatorIshop')
union select 'TEST2 ' as code,moment,nom,machine from MERCATORTEST2.dbo.sessions s12 where (s12.inactif=0) and (s12.nom<>'MercatorIshop')
union select 'TEST20 ' as code,moment,nom,machine from MERCATORTEST20.dbo.sessions s13 where (s13.inactif=0) and (s13.nom<>'MercatorIshop')
union select 'TEST21 ' as code,moment,nom,machine from MERCATORTEST21.dbo.sessions s14 where (s14.inactif=0) and (s14.nom<>'MercatorIshop')
union select 'TEST23 ' as code,moment,nom,machine from MERCATORTEST23.dbo.sessions s15 where (s15.inactif=0) and (s15.nom<>'MercatorIshop')
union select 'TEST3 ' as code,moment,nom,machine from MERCATORTEST3.dbo.sessions s16 where (s16.inactif=0) and (s16.nom<>'MercatorIshop')
union select 'TEST4 ' as code,moment,nom,machine from MERCATORTEST4.dbo.sessions s17 where (s17.inactif=0) and (s17.nom<>'MercatorIshop')
union select 'TEST5 ' as code,moment,nom,machine from MERCATORTEST5.dbo.sessions s18 where (s18.inactif=0) and (s18.nom<>'MercatorIshop')
union select 'TEST6 ' as code,moment,nom,machine from MERCATORTEST6.dbo.sessions s19 where (s19.inactif=0) and (s19.nom<>'MercatorIshop')
union select 'TEST7 ' as code,moment,nom,machine from MERCATORTEST7.dbo.sessions s20 where (s20.inactif=0) and (s20.nom<>'MercatorIshop')
union select 'TEST8 ' as code,moment,nom,machine from MERCATORTEST8.dbo.sessions s21 where (s21.inactif=0) and (s21.nom<>'MercatorIshop')
union select 'TEST9 ' as code,moment,nom,machine from MERCATORTEST9.dbo.sessions s22 where (s22.inactif=0) and (s22.nom<>'MercatorIshop')
I am using VS 2005 to develop a Pocket PC 2003 application and for some reason I dont have the ability to assign my datasource to a SQL Mobile Database?
When opening Server Explorer, right clicking Data Connection, Add New Data Source, Change Data Source, the only options I get are
Microsoft Access Database File Microsoft ODBC Data Source Microsoft SQL Server Microsoft SQL Server Database File Oracle Database <other>
I was trying to follow the tutorial
Step by Step: Developing a SQL Mobile Application with Visual Studio 2005 and SQL Server 2005 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/med302_msdn_sql_mobile.asp) and in that tutorial when they do the exact same thing it shows a screen shot that also lists 'Microsoft SQL Server Mobile Edition'
Any ideas why I dont have that option? FYI, I have SQL Server 2005 and have installed SQL Server 2005 Mobile Edition.
I have several database files, all in the same directory. I want to write SQL statements that access two databases at once. For example, if only one database would be involved, I would write:
SELECT Test_Source_ID_column, Data_column INTO Test_Destination_table FROM Test_Sources_table The above query copies two columns (Test_Source_ID_column and Data_column) from Test_Sources_table into a brand new table called Test_Destination_table. Now the above query is fine for both the old and new tables being in the same database. But what if Test_Sources_table is in file Old.dbf and I want the new table Test_Destination_table to be in New.dbf? Both Old.dbf and New.dbf are in the same directory. Therefore, there is no security issue about using ......etcpasswords. I should therefore be able to use relative paths. So, I also want to know how I can do that select on two databases in the same directory without typing long, long paths. Is there a way to do queries that access multiple database files (all database files being in the exact same directory) AND use relative paths or no paths (and certainly not type a full path)? NOTE 1: I'm using SQLEXPRESS, therefore do not have the SQL Server 2005 tools. I have Visual Studio 2005 Developer Edition with its tools. NOTE 2: One table is accessed through ODBC. The other table is accessed through SQLEXPRESS.
I have around 5 databases with same structure used to store data for different locations and services.
I have created an intermediate database which will have all the stored procedures to access data from above 5 databases.
My queries are common to all the databases. so,I would like to pass database name as an argument to my stored proc and execure query on specified database.
I tried this doing using "USE Databasename " Command. but it says ...We can not use "USE command " in stored proc or triggers. so ..what i did is ..
Based on our database infrastructure, we need to secure our SQL databases. The security issue concerns on allowing a limited number of Domain Admin users to access the SQL databases. We tried certain ways, based on the documents in the Microsoft web site, but we couldn€™t reach to the point of preventing the Domain Admin users accessing the SQL databases.
We have a system that uses 3 databases, one for Membership db standard MS membership only the application has access to that data, one with User Data which we would like to make multi-tenant using Schema-Separation, and a third read-only reference db which is Common Market data for all users.we anticipate Tenant numbers in the thousands.Current we have multiple queries which create joins between the Main db and the Reference database using something like
Selec S.*, M.ScheduleDate, M.substation from Sites S left outer join Market.dbo.MarketUnit M where S.MarketUnitID = M.MarketUnitID
i'm planning to have a new schema for each Tenant on the Main Database, so I would create a Schema T1 for the first customer, a user T1User with access to T1 schema. and grant T1User access to Market.dbo. My First question is are there any concerns about the above T1User setup? My second question is, are there any tools which would automate the setup of the multi-tenant with schema separation, or should I just script the whole Main Database schema creation and replace schema name globally and then execute the script?
My Third question, how about upgrade and updates... currently using VS to compare dev/qa/prod database to identify changed which need to be promoted, and pushing updates... this could be a big pain to promote code to thousands of Schemas. grantedwe will likely keep the overall number of schemas spread over different SQL servers.
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
i would like how to duplicate a database to another server with all datas, constraints, keys, indexes... should i use sp_attach_db, dts, backup/restore, sql scripts... ???
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:
-------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
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!
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!
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
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>" ?
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
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.
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?
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.
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?
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.
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.
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?
