I currently administrate a database for a training provider/college.
We've been running on Interbase for about 2 years and the size of the database has obviously grown over time.
I've just had a conversation with our suppliers and they seem to think that our database is too large to run on Interbase anymore. The database is currently 375meg and grows everyday.
We're going to have to think about changing from interbase in the not too distant future or start Archiving. Would you recommend we stay with interbase and have two separate databases or change to Microsoft SQL Server?
I have written several DTS pakages to copy data from an Interbase/Firebird db to SQL Server. They execute under Enterrpise Manager perfectly but when scheduled return an error.
I am aware that permissions are a normal source of these problems but I have hopefully excluded them from teh equation by using a local admin/domain/sysadmin user throughout for SEM, SQLServerAgent and even the SQLServerAgentProxy.
The problem appears to be related to the interaction between the Microsoft OLE DB Provider for ODBC Drivers and the Interbase/Firebird Driver. I have checked several forums and the MSDN website but few people seem to have experienced this type of problem in this environment.
Error msgs:
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers Step Error Description:[Easysoft][InterBase]unavailable database Step Error code: 80004005 Step Error Help File: Step Error Help Context ID:0
OR
Step Error Source: Microsoft OLE DB Provider for ODBC Drivers Step Error Description:unavailable database Step Error code: 80040E4D Step Error Help File: Step Error Help Context ID:0
My Setup:
MS SQL Server 2000 with SP3 MDAC 2.8 RTM Interbase v6 /Firebird 1.03 db Server
Hello All, I am having trouble importing a table from Interbase. There is one problem field, I think the problem may be that the field has carriage returns in it. It keeps complaining that the length is not valid. The interbase field is some char type of 8000 and I used nvarchar(max) on the sql side.
I cannot get it through the Source Query to do any Data Conversion on it.
select top 10 * from dccs_danville.INTDCCS_DV.gdb.tblload select top 10 * from dccs_danville.INTDCCS_DV.dbo.tblload select top 10 * from dccs_danville.INTDCCS_DV...tblload select top 10 * from dccs_danville.INTDCCS_DV.tblload select top 10 * from dccs_danville...tblload select top 10 * from dccs_danville.INTDCCS_DV.tblload
also tried EXEC sp_tables_ex 'dccs_danville' tblload is able to be queried.
VS 2005 SQL 2005 Reporting Services Firebird.Net Data Provider 2.0 Firebird Database 1.5
This is what I am trying to do:
In SQL Server 2005 Analysis project,
Create a Data Source Create a Data Source View And Create a Report Model
I have successfully created the data source. When I try to create a Data Source View, it lists all the tables in the Firebird database. In the last step, when VS tries to import the metadata to its storage (I think), the Firebird Client throws the following exception:
at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior) at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.DataWarehouse.Design.DataSourceConnection.FillDataSet(DataSet dataSet, String schemaName, String tableName, String tableType) at Microsoft.AnalysisServices.Design.DataSourceDesigner.AddRemoveObjectsFromDSV()
===================================
Exception of type 'FirebirdSql.Data.Common.IscException' was thrown. (FirebirdSql.Data.FirebirdClient)
------------------------------ Program Location:
at FirebirdSql.Data.Client.Gds.GdsConnection.ReadStatusVector() at FirebirdSql.Data.Client.Gds.GdsConnection.ReadResponse() at FirebirdSql.Data.Client.Gds.GdsDatabase.ReadResponse() at FirebirdSql.Data.Client.Gds.GdsStatement.Prepare(String commandText) at FirebirdSql.Data.FirebirdClient.FbCommand.Prepare(Boolean returnsSet) at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteCommand(CommandBehavior behavior, Boolean returnsSet) at FirebirdSql.Data.FirebirdClient.FbCommand.ExecuteReader(CommandBehavior behavior)
<Error end>
Few notes:
If I remove the checkbox of €śCreate logical relationships by matching columns€?, the wizard proceeds smoothly. But when I open the data source view, there are no tables there and if I try to add more tables, I receive the same error as above.
I am also trying to follow this up the Firebird Client project since the error was thrown by Firebird DLL. However, has somebody done this before and/or can provide me some direction/information as to what I might be doing wrong?
Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks
Msg 7399, Level 16, State 1, Procedure tr_cpD, Line 14
The OLE DB provider "SQLNCLI" for linked server "S2" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Procedure tr_cpD, Line 14
Invalid use of schema or catalog for OLE DB provider "SQLNCLI" for linked server "S2". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.
Hi, I have a SQL Server 2005 Database that I would like to export to SQL Server Express. How do I go about this? I've tried backing it up, creating a new (blank) SQL Server Express Database and trying to restore it, but get the following message: TITLE: Microsoft SQL Server Management Studio------------------------------ Restore failed for Server 'PRODSOL-LAPTOP1SQLEXPRESS'. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 ------------------------------ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'ProdsolPGC' database. (Microsoft.SqlServer.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476 Neither of the links provide any more information and I'm sure that I'm missing a step out. I've tried scripting the existing database and then running the script against the new database, but I can't get that to work either - these blooming newbies! Please help! Thanks very much. Regards Gary
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with: WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc. Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below) Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government] Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc. Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies. Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
This Audit Trigger is Generic (i.e. non-"Table Specific") attach it to any tabel and it should work. Be sure and create the 'Audit' table first though.
The following code write audit entries to a Table called 'Audit' with columns 'ActionType' //varchar 'TableName' //varchar 'PK' //varchar 'FieldName' //varchar 'OldValue' //varchar 'NewValue' //varchar 'ChangeDateTime' //datetime 'ChangeBy' //varchar
using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Server;
public partial class Triggers { //A Generic Trigger for Insert, Update and Delete Actions on any Table [Microsoft.SqlServer.Server.SqlTrigger(Name = "AuditTrigger", Event = "FOR INSERT, UPDATE, DELETE")]
public static void AuditTrigger() { SqlTriggerContext tcontext = SqlContext.TriggerContext; //Trigger Context string TName; //Where we store the Altered Table's Name string User; //Where we will store the Database Username DataRow iRow; //DataRow to hold the inserted values DataRow dRow; //DataRow to how the deleted/overwritten values DataRow aRow; //Audit DataRow to build our Audit entry with string PKString; //Will temporarily store the Primary Key Column Names and Values here using (SqlConnection conn = new SqlConnection("context connection=true"))//Our Connection { conn.Open();//Open the Connection //Build the AuditAdapter and Mathcing Table SqlDataAdapter AuditAdapter = new SqlDataAdapter("SELECT * FROM Audit WHERE 1=0", conn); DataTable AuditTable = new DataTable(); AuditAdapter.FillSchema(AuditTable, SchemaType.Source); SqlCommandBuilder AuditCommandBuilder = new SqlCommandBuilder(AuditAdapter);//Populates the Insert command for us //Get the inserted values SqlDataAdapter Loader = new SqlDataAdapter("SELECT * from INSERTED", conn); DataTable inserted = new DataTable(); Loader.Fill(inserted); //Get the deleted and/or overwritten values Loader.SelectCommand.CommandText = "SELECT * from DELETED"; DataTable deleted = new DataTable(); Loader.Fill(deleted); //Retrieve the Name of the Table that currently has a lock from the executing command(i.e. the one that caused this trigger to fire) SqlCommand cmd = new SqlCommand("SELECT object_name(resource_associated_entity_id) FROM ys.dm_tran_locks WHERE request_session_id = @@spid and resource_type = 'OBJECT'", conn); TName = cmd.ExecuteScalar().ToString(); //Retrieve the UserName of the current Database User SqlCommand curUserCommand = new SqlCommand("SELECT system_user", conn); User = curUserCommand.ExecuteScalar().ToString(); //Adapted the following command from a T-SQL audit trigger by Nigel Rivett //http://www.nigelrivett.net/AuditTrailTrigger.html SqlDataAdapter PKTableAdapter = new SqlDataAdapter(@"SELECT c.COLUMN_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , INFORMATION_SCHEMA.KEY_COLUMN_USAGE c where pk.TABLE_NAME = '" + TName + @"' and CONSTRAINT_TYPE = 'PRIMARY KEY' and c.TABLE_NAME = pk.TABLE_NAME and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME", conn); DataTable PKTable = new DataTable(); PKTableAdapter.Fill(PKTable);
switch (tcontext.TriggerAction)//Switch on the Action occuring on the Table { case TriggerAction.Update: iRow = inserted.Rows[0];//Get the inserted values in row form dRow = deleted.Rows[0];//Get the overwritten values in row form PKString = PKStringBuilder(PKTable, iRow);//the the Primary Keys and There values as a string foreach (DataColumn column in inserted.Columns)//Walk through all possible Table Columns { if (!iRow[column.Ordinal].Equals(dRow[column.Ordinal]))//If value changed { //Build an Audit Entry aRow = AuditTable.NewRow(); aRow["ActionType"] = "U";//U for Update aRow["TableName"] = TName; aRow["PK"] = PKString; aRow["FieldName"] = column.ColumnName; aRow["OldValue"] = dRow[column.Ordinal].ToString(); aRow["NewValue"] = iRow[column.Ordinal].ToString(); aRow["ChangeDateTime"] = DateTime.Now.ToString(); aRow["ChangedBy"] = User; AuditTable.Rows.InsertAt(aRow, 0);//Insert the entry } } break; case TriggerAction.Insert: iRow = inserted.Rows[0]; PKString = PKStringBuilder(PKTable, iRow); foreach (DataColumn column in inserted.Columns) { //Build an Audit Entry aRow = AuditTable.NewRow(); aRow["ActionType"] = "I";//I for Insert aRow["TableName"] = TName; aRow["PK"] = PKString; aRow["FieldName"] = column.ColumnName; aRow["OldValue"] = null; aRow["NewValue"] = iRow[column.Ordinal].ToString(); aRow["ChangeDateTime"] = DateTime.Now.ToString(); aRow["ChangedBy"] = User; AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry } break; case TriggerAction.Delete: dRow = deleted.Rows[0]; PKString = PKStringBuilder(PKTable, dRow); foreach (DataColumn column in inserted.Columns) { //Build and Audit Entry aRow = AuditTable.NewRow(); aRow["ActionType"] = "D";//D for Delete aRow["TableName"] = TName; aRow["PK"] = PKString; aRow["FieldName"] = column.ColumnName; aRow["OldValue"] = dRow[column.Ordinal].ToString(); aRow["NewValue"] = null; aRow["ChangeDateTime"] = DateTime.Now.ToString(); aRow["ChangedBy"] = User; AuditTable.Rows.InsertAt(aRow, 0);//Insert the Entry } break; default: //Do Nothing break; } AuditAdapter.Update(AuditTable);//Write all Audit Entries back to AuditTable conn.Close(); //Close the Connection } }
//Helper function that takes a Table of the Primary Key Column Names and the modified rows Values //and builds a string of the form "<PKColumn1Name=Value1>,PKColumn2Name=Value2>,......" public static string PKStringBuilder(DataTable primaryKeysTable, DataRow valuesDataRow) { string temp = String.Empty; foreach (DataRow kColumn in primaryKeysTable.Rows)//for all Primary Keys of the Table that is being changed { temp = String.Concat(temp, String.Concat("<", kColumn[0].ToString(), "=", valuesDataRow[kColumn[0].ToString)].ToString(), ">,")); } return temp; } }
The trick was getting the Table Name and the Primary Key Columns. I hope this code is found useful.
if (!Page.IsPostBack) { if (Session["users"] != null && (Session[flag"] == "true")) { String IP = Request.ServerVariables["remote_host"].ToString(); String Datee = DateTime.Now.Date.ToString(); OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" + HttpContext.Current.Server.MapPath("~/App_Data/result.mdb")); OleDbCommand cmd = new OleDbCommand("Insert Into KullaniciSayisi (IP,Datee) Values (IPDatee)", con); cmd.Parameters.AddWithValue("IP", IP); cmd.Parameters.AddWithValue("Datee", Datee); con.Open(); intresultt = cmd.ExecuteNonQuery(); con.Close(); Session["flag"] = "false"; } } I want to convert this code to SQL sever . However I can not do it? Can you help me?
Hi, Is there a tool that anyone here is aware of which converts SQL, stored procedures and such stuff from Oracle compatibility to SQL Server compatibility? I have some Oracle based SQL that I wud like to convert into T-sql instead of re writing the whole T-SQL.. Thanks,
Hello does anybody know how to convert an SQL Server file into a DBF or an Excel sheet? I need to do this programmitically with C# or python. Preferrably C#. Also, the converted file must be placed into a different folder. Thanks.
I have to downgrade a eCommerce database from Sql Server 2005 to Sql Server 2000 due to hosting server's support compatibility. Almost all features in the original (2005) version are available in 2000. Any recommendations as on an easy conversion? Thanks a lot. -Yubo
I want to be able to create a trigger that updates table 2 when a row is inserted into table 1. However I€™m not sure how to increment the ID in table 2 or to update only the row that has been inserted.
I want to be able to create a trigger so that when a row is inserted into table A by a specific user then the ID will appear in table B. Is it possible to find out the login id of the user inserting a row?
I believe the trigger should look something like this:
create trigger test_trigger on a for insert as insert into b(ID)
We have a database on a 2005 box, which we need to keep in sync with one on a 2014 box (until we can turn off the one on 2005). The 2005 database is still being updated with changes that must be applied to the 2014 database, given the nature of the data (medical documents) we need to ensure updates are applied to the 2014 database in very near real time (these changes are - for example - statuses, not the documents themselves).
Cunning plan #1, ulgy - not at all a fan of triggers - but use an after update trigger to run a sp on the remote box via a linked server in this format, with a SQL Server login for the linked server with permissions to EXEC the remote proc.
CREATE TRIGGER [dbo].[SourceUpdate] ON [dbo].[SourceTable] AFTER UPDATE AS SET XACT_ABORT ON; SET NOCOUNT ON; IF UPDATE(ColumnName)
[Code] ....
However, while the sp can be run against the linked server as a standalone query OK, when running it in a trigger it's throwing
OLE DB provider "SQLNCLI" for linked server "WIBBLE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure TheAfterUpdateTrigger, Line 19
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "WIBBLE" was unable to begin a distributed transaction.
Whether it actually possible to call a proc on a remote box via a trigger and if so what additional hoops need to be jumped through (like I said, it'll run OK called via SSMS)?
I'm new to SQL. I have a scenario, Where customer want to move all the jobs from original SQL server to some remote SQL server and want to trigger jobs on remote server to do its work on original server.
I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:
create TRIGGER [trg_connection_MyServer] ON ALL SERVER WITH EXECUTE AS 'Smith' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'Smith' begin if exists (SELECT 1 FROM sys.dm_exec_sessions WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') ) ROLLBACK; end
I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.
Sorry if this has been answered before. Could not find any answers. OS Vista Business, SQL Server 2005 Express. I have a CSV file which I imported to Access only 100 records with 8 fields. Then ran Tools >> Database Utilities >> Upsizing Wisard. The result was I exported the table field headers but not the data. Is there any method on how I can get the field headers and data into SQL Server Express?
Hello There, I would really appreciate if you can help me with this problem. I am currently using SQL Server 2005 Express for one of my web application. I have a .bak file (backup file) provided by my client which I want to use for testing purpose. Now I just want to know how can I use that backup file without installing SQL SERVER 2005 as one way to do this I know is to restore the database in SQL Server 2005 Enterprise Manager but I can't install SQL Server 2005. To my knowledge, SQL Server Express uses .mdf file for database but what I have is .BAK file.. I would be really glad to see your quick answers. Thank you for reading this far. Sincerely, Zulfiqar
can someone please supply some information to help with this??
I am moving data from db2 8.1 for windows. the dates in db2 are defined as timestamp. i want to convert these to sql server datetime format in sql server 2000 using dts and sql.
