The Fastest Normal ADO.NET Or Microsoft Best Practise EnterpriseLibrary
May 12, 2006
So I got 2 classes one I wrote to interrogate databases using normal ADO:
Mine:
SqlConnection myConnection = new SqlConnection(m_sConnectionString);
SqlCommand myCommand = new SqlCommand(sQuery, myConnection);
myCommand.CommandTimeout = 120; // 60 Seconds Timeout
myConnection.Open();
SqlDataReader result = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
return result;
Microsoft Way
SqlDatabase dbSvc = new SqlDatabase(m_sConnectionString);
DbCommand dbCommand = dbSvc.GetSqlStringCommand(sQuery);
return ((SqlDataReader)dbSvc.ExecuteReader(dbCommand));
What's faster?
My way:
SqlConnection myConnection = new SqlConnection(m_sConnectionString);
SqlCommand myCommand = new SqlCommand(sQuery, myConnection);
myCommand.CommandTimeout = 120; // 60 Seconds Timeout
// Use a DataTable – required for default paging
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
DataTable myTable = new DataTable();
myAdapter.Fill(myTable);
myConnection.Close();
myConnection.Dispose();
myConnection = null;
return (myTable);
Microsoft Way:
SqlDatabase dbSvc = new SqlDatabase(m_sConnectionString);
DbCommand dbCommand = dbSvc.GetSqlStringCommand(sQuery);
DataTable dtData = null;
DataSet dsData = dbSvc.ExecuteDataSet(dbCommand);
dtData = dsData.Tables[0];
return (dtData);
Comments? Ideas?
Al
View 4 Replies
ADVERTISEMENT
Jul 19, 2007
I hope this is the right place to ask this:
I am using the microsoft enterpriselibrary for accessing a sql server database. when I use the following code, I receive an error 'ExecuteScalar: Connection property has not been initialized.':
---------------------------------------------------------------------------
Database db = new SqlDatabase(p_ConnectionString);
DbCommand cmd = db.GetSqlStringCommand(p_Sql);
output = cmd.ExecuteScalar();
----------------------------------------------------------------------------
but when I use this code, everything works correctly:
---------------------------------------------------------------------------
Database db = new SqlDatabase(p_ConnectionString);
DbCommand cmd = db.GetSqlStringCommand(p_Sql);
output = db.ExecuteScalar(cmd);
----------------------------------------------------------------------------
I was assured by my supervisor that both snippets' syntax are correct. Can anyone shine some light on why the first snippet will throw an exception?
Thanks in advance,
Drew
View 4 Replies
View Related
Aug 2, 2006
Hello
I created a wrapper class for a function, and exposed it through CLR. However, if I call this function form SQL it blows up but if I call directly from a test Windows Form the call works fine.
The blow up is related to EnterpriseLibrary.Data, where my Queue class uses that library to do all data access call ops
Here's my wrapper class:
namespace inlineCLRsql{
public static class Wrapper{
public static void CallQueueEntry(int queueId, int deskNo, int missed){
inLineLib.Queue oQueue;
inLineLib.QueueEntry oQueueEntry;
oQueue = new inLineLib.Queue(queueId);
oQueueEntry = oQueue.callQueueEntry(deskNo, false);
Microsoft.SqlServer.Server.SqlContext.Pipe.Send(oQueueEntry.queueNum.ToString());
}
}
And this is my CLR SQL creation code:
CREATE PROC sp_CallQueueEntry
@queueId int,
@deskNo int,
@missed int
AS
EXTERNAL NAME inLineLib.[inlineCLRsql.Wrapper].CallQueueEntry
GO
sp_CallQueueEntry 4,2,0
Here is what I get as a result
System.NullReferenceException: Object reference not set to an instance of an object.
System.NullReferenceException:
at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseConfigurationView.get_DefaultName()
at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseMapper.MapName(String name, IConfigurationSource configSource)
at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.ConfigurationNameMappingStrategy.BuildUp(IBuilderContext context, Type t, Object existing, String id)
at Microsoft.Practices.ObjectBuilder.BuilderBase`1.DoBuildUp(IReadWriteLocator locator, Type typeToBuild, String idToBuild, Object existing, PolicyList[] transientPolicies)
at Microsoft.Practices.ObjectBuilder.BuilderBase`1.BuildUp(IReadWriteLocator locator, Type typeToBuild, String idToBuild, Object existing, PolicyList[] transientPolicies)
at Microsoft.Practices.ObjectBuilder.BuilderBase`1.BuildUp[TTypeToBuild](IReadWriteLocator locator, String idToBuild, Object existing, PolicyList[] transientPolicies)
at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.EnterpriseLibraryFactory.BuildUp[T](IReadWriteLocator locator, IConfigurationSource configurationSource)
at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.EnterpriseLibraryFactory.BuildUp[T](IConfigurationSource configurationSource)
at Microsoft.Practices.EnterpriseLibrary.Common.Configuration.ObjectBuilder.NameTypeFactoryBase`1.CreateDefault()
at Microsoft.Practices.EnterpriseLibrary.Data.DatabaseFactory.CreateDatabase()
at inLineLib.Queue.getNextQueueEntry(Int32 servedBy)
at inLineLib.Queue.callQueueEntry(Int32 servedBy, Boolean callMissed)
at inlineCLRsql.Wrapper.CallQueueEntry(Int32 queueId, Int32 deskNo, Int32 missed)
What can I do to fix this?
Cheers
M
View 1 Replies
View Related
Sep 27, 2007
in the class library i written the code name :customer is the lib name
using System;
using System.Collections.Generic;
using System.Text;
namespace Customer
{ class Entites
{
public int inTest;
public int inTest2;
}
}
Now in the Class1.cs i written the code
i am getting the data from the database by using enterprise lib 2006 connection function
now HOW TO BIND THE DATA TO LIST AND RETURN TYPE IS LIST
PLEASE CHECK THE CODE AND REDEFINE THE CODE
using System;
using System.Data ;
using System.Collections.Generic;
using System.Collections.Generic;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using System.Collections;
using System.Xml.Serialization;
using System.Data.Common;
using Customer;
namespace Customer
{
class Class1
{
public List<Entites> getdata(int id)
{
Database db = DatabaseFactory.CreateDatabase("mycon");
System.Data.Common.DbCommand cmd ;
cmd = db.GetStoredProcCommand("GET_CUSTOMER");
cmd.CommandType = CommandType.StoredProcedure;
db.AddInParameter(cmd,"@CID",System.Data.DbType.Int32,id);
List<Entites> objEntites = new List<Entites>();
using (IDataReader dr = db.ExecuteReader(cmd))
foreach (Entites obj in dr)
{
objEntites.inTest = obj.inTest;-----------------------------------------------ERROR LINE
// objEntites.Add(obj);
}
return objEntites;
}
}
}
Error 2 foreach statement cannot operate on variables of type 'System.Data.IDataReader' because 'System.Data.IDataReader' does not contain a public definition for 'GetEnumerator' D:KOTI_PRJSEnterpriseCustomerClass1.cs 34 13 Customer
View 1 Replies
View Related
Oct 11, 2006
Hi
I am having diffuculty with multi owner stored procs with enterprise library deriveparameters.
Eg:
Create proc dbo.test @p1 int as select 'hi'
create proc bob.test @p1 int as select 'hi'
Deriveparameters appears to tell us the wrong number of parameters. I gather it is getting ALL the parameters for all procs called 'test' regardless of the owner.
If anyone can give me some guidance on this I would appreciate it!
Rich
View 2 Replies
View Related
Oct 13, 2006
Hi!I have 6-7 tables total containing subobjects for different objects like phonenumbers and emails for contacts.This meaning i have to do some querys on each detailpage. I will use stored proc for fetching subobjects.My question therefore was: if i could merge subobjects into same tables making me use perhaps 2 querys instead of 4 and thus perhaps doubling the sizeof the tables would this have a possibility of giving me any performance difference whatsoever?As i see pros arefewer querys, and cons are larger tables and i will need another field separating the types of objects in the table.Anyone have insight to this?
View 5 Replies
View Related
Apr 30, 2008
I want to log all changes made to a table (only updates, since there will be no deletes or inserts).
I would like to see the user who changed it, the date and time, fieldname, old value, new value.
If more fields are changed during the update, than add more records into the logging table.
What is best practise to achieve this?
Thanks,
Frans
View 13 Replies
View Related
Nov 6, 2007
Hi,
I have some data that is updated every day but I don't know when. I'm trying to make a solution that runs a SQL query to check if this data has been updated. If it has, I'll send the updated data with FTP as as text file.
How would you solve this?
My idea is to have 2 SSIS packages.
- Package1: One runs at the same time every day (inserts any missing updates to a table)
- Package2: One runs every hour to check the missing updates table, and runs Package1 if any update for a missing data is found.
My only worries is if Package1 is running and at the same time Package2 decides to run Package1 then I could get into trouble if I'm using temp tables with the same name for the text file updates etc.
Thank you.
View 1 Replies
View Related
Jul 27, 2007
I need to load a lot of Excel, CSV, ... etc. files. These files have hundreds of columns and I need to validate the data. Some are simple range type checking, some are more complex checking involve multiple columns.
There may have several hundreds of such rules. And I may need to let the program to automatically correct some invalid data in the future.
Where to implement it in SSIS?
Or just load the files without any checking (all type to text), and checking using T-SQL?
(BTW, I don't have biztalk server).
Thanks in advance.
Read more >> Options >>
View 5 Replies
View Related
Feb 6, 2002
Hi Folks,
I am about to upgrade my main database server (5 db's - largest 16Gb) from
NT 4 SP 6a / SQL Server 7 SP3 to Windows 2000 SP 2 / SQL Server 2000 SP 2
I am planning to detach the db's, backup to tape a few times and then totally
trash the server, rebuilding it with the new software, restore the db's from
tape and the reattach the db's.
Any reason I should not use this method and can folk advise the best practice
way of achieving this?
Regards
Paul
________________________
Paul Starling
ERP Database Administrator
View 1 Replies
View Related
Aug 10, 2007
Hi, hoping I can get a few view on a question I have relating to the above.
I am new to Stored Procedures and Triggers and I am trying to understand 'best practice' a little better. Here is my question: If I have a table that stores information, and when any field in that table is updated (and changes) I would like to inactive the row, prior to change and then add the change by way of a new, active row. This way I can see what it was before and that it's inactive, and what the active value is.
Hope this makes sense, if this is the wrong way to manage change history any suggestions would be appreciated.
A second question I have is as follows: If I have a table that stores a number, based on that number, what would be the best way to create new records in a different table that pulls from the first table. Where the number stored in table 1 represents how many times the record is to be created in the second table.
Thanks. If anyone needs more data, please feel free to ask, I will help as best as I can and appreciate any advice & comments that you can give.
Paul
View 3 Replies
View Related
Jun 15, 2007
As title, what's the best practise of importing XML data into SQL Server 2005?
I have found this one. http://support.microsoft.com/default.aspx/kb/316005. Is it good enough?
The XML is generated by another system on daily basis. So, the import should be able to handle insert and update cases. How can I do that? Thanks!
View 2 Replies
View Related
Mar 5, 2007
I have master tables that I will be updating from our ERP system. Some examples I have seen take an approach of dropping a table in SQL server then creating it again before importing; some, and probably my choice, append and update; I have not seen an example where records are all deleted then the data appended afterwards. Of the three approaches which is generally regarded as best practise / most efficient?
View 1 Replies
View Related
Jan 2, 2008
Hi,
I work with a large team developing ASP.NET application that has a large database with over 50 complex stored procedures. It is proving more and more difficult and time consuming to centralise the development and update of the database changes and I was wondering if there were any best practises/tools that could be recommended. I have looked on the web for good articles and haven't found anything difinitive (except that Team Foundation Server is the way forward)..
A brief background to the current process is that everyone develops on the same database, and then updates the stored procedure scripts in source safe (manually). Then when we do a new release someone builds a script of all the database updates and runs it. There are issues related to developers updating there stored procedures over other peoples and other concurrency.
I am looking to move all the developers to start using local databases so that there work only effects them, but then this brings up problems of keeping all the local databases up to date whenever they get the latest source code. The only way I currently see is to build a database update program, that will run and update to the latest version.
Surely this must be a common issue? Anyone have any good ideas/concepts?
Also our setup is Visual Studio 2005, SQL Server 2005 and Source Safe 2005.
Cheers,
Andrew Thomas
View 5 Replies
View Related
Nov 23, 2005
Hi. Sorry if I am asking a stupid question since I am an absolutely beginnerin SQL Server. Here is the question . . .About 13 hours ago, I got my SQL Server 2000 to index a table which has 104million records. At first the CPU usage was high. But after an hour or two,the process has seemed dead and the Enterprise Manager has had no response.The CPU usuage dropped to zero and has been jumping between 0 to 5%. Theharddisk indicator has been blinking at a rate of roughly three times pertwo seconds.Is this normal? Has anyone got any idea how long the process would take? Ihave assigned 1.8GB of RAM to the SQL service and is currently taking upabout 1GB.
View 7 Replies
View Related
May 14, 2008
right now I have a stored procedure that goes through each of the Line and Body fields using a cursor. The problem is that this method is very slow. How would you experts solve this problem? any Hints or suggestions?
BEFORE
EXAMPLEPartLineBodySeriesEngineYear
11234A,BWETC1998
25678991,93,94,95WET01997
3345656S,R5,6,12WENC1995
AFTER
EXAMPLEPartLineBodySeriesEngineYear
11234AWETC1998
11234BWETC1998
25678991WET01997
25678993WET01997
25678994WET01997
25678995WET01997
3345656S5WENC1995
3345656S6WENC1995
3345656S12WENC1995
3345656R5WENC1995
3345656R6WENC1995
3345656R12WENC1995
View 4 Replies
View Related
Dec 7, 2003
Impossible to connect to my mssql 2000 database
web.config connexion string >>>
<add key="DataSourceSql" value="Initial Catalog=angiras;Data Source=localhost;Uid=sa;Pwd=;" />
or
<add key="DataSourceSql" value="Initial Catalog=angiras;Data Source=localhost;" />
connexion code >>>
Private ConnexionSQL As SqlConnection
Public Sub OpenConnexionSQL()
ConnexionSQL = New SqlConnection(ConfigurationSettings.AppSettings("DataSourceSql").ToString)
ConnexionSQL.Open()
End Sub
when I try >>>
OpenConnexionSQL
I get an error
any idea ?
thank you
View 1 Replies
View Related
Feb 1, 2008
Hi guys, I've been thinking about this problem now for some time but somehow I don't know if my "solution" for it is right. I'd like to read your opinion.
There is a Capital table with Capital_Nr, Capital_Name, Capital_Population, Country_Nr and Country_Name as attributes.
I know the table is chaotic so I brought it to 3NF :
Capital table : Capital_Nr, Capital_Name, Capital_Population, Country_Nr(foreign key)
Country table : Country_Nr and Country_Name
Ok so I guess the table should be now in 3NF, but what intrigues me is in what NF the table originally was. I tried then to use Codd's definition of 2NF : "a 1NF table is in 2NF if and only if none of its non-prime attributes are functionally dependent on a part (proper subset) of a candidate key". In my opinion the original candidate keys could only be {Capital_Nr},{Country_Nr} and {Country_Name}, each one of them single, i.e. separate from each other. So, as there is no composite candidate key, I can affirm that the original table was in 2NF. Am I right ?
Thanks !
View 3 Replies
View Related
Apr 15, 2008
I am wondering what normal disk I/O should be.
i know it verys depending on use but im looking for an average.
here is an idea of what we have
there is about 10 centers doing replication to our primary server.
we have about 80 users connecting directoy to our primary server using MS Dynamics through CITRIX.
we have a few other apps use the database as well however i am fairly certin its Dynamics generating our disk IO
Hardware wise we have a powerful blade connected to a raid 5 SAN with 15000 rpm disks.
normaly the disk IO stays fairly low but every so often it goes crazy and im thinking it shouldn't
Below is a sample of our disk IO from perfmon over 2 minutes or so. as you can see everything looks ok untill 04/15/2008 10:12:49.470 when the Disk I/O % goes above 100%
"04/15/2008 10:12:23.470","2.8300181121159178"
"04/15/2008 10:12:24.470","0"
"04/15/2008 10:12:25.470","3.950025280161793"
"04/15/2008 10:12:26.470","0"
"04/15/2008 10:12:27.470","0"
"04/15/2008 10:12:28.470","5.9800382722449426"
"04/15/2008 10:12:29.470","7.7400495363170325"
"04/15/2008 10:12:30.470","3.4500220801413128"
"04/15/2008 10:12:31.470","0"
"04/15/2008 10:12:32.470","4.180026752171214"
"04/15/2008 10:12:33.470","3.8600247041581071"
"04/15/2008 10:12:34.470","0"
"04/15/2008 10:12:35.470","142.71091334984544"
"04/15/2008 10:12:36.470","0"
"04/15/2008 10:12:37.470","1.1500073600471041"
"04/15/2008 10:12:38.470","0.81000518403317789"
"04/15/2008 10:12:39.470","0"
"04/15/2008 10:12:40.470","2.1400136960876548"
"04/15/2008 10:12:41.470","10.230065472419025"
"04/15/2008 10:12:42.470","4.5800293121875981"
"04/15/2008 10:12:43.470","0"
"04/15/2008 10:12:44.470","0"
"04/15/2008 10:12:45.470","14.500092800593926"
"04/15/2008 10:12:46.470","6.730043072275663"
"04/15/2008 10:12:47.470","1.6300104320667652"
"04/15/2008 10:12:48.470","7.3500470403010585"
"04/15/2008 10:12:49.470","264.66169383484055"
"04/15/2008 10:12:50.470","324.18207476527851"
"04/15/2008 10:12:51.470","536.17343150996169"
"04/15/2008 10:12:52.470","270.31172999507197"
"04/15/2008 10:12:53.470","331.97212462159757"
"04/15/2008 10:12:54.470","333.84213658967417"
"04/15/2008 10:12:55.470","435.57278766584108"
"04/15/2008 10:12:56.470","442.31283080211716"
"04/15/2008 10:12:57.470","685.92438991609549"
"04/15/2008 10:12:58.470","295.1618890360898"
"04/15/2008 10:12:59.470","61.470393410517829"
"04/15/2008 10:13:00.470","56.360360706308519"
"04/15/2008 10:13:01.470","45.260289665853861"
"04/15/2008 10:13:02.470","224.58143732119885"
"04/15/2008 10:13:03.470","1022.0365410338626"
"04/15/2008 10:13:04.470","1114.6271336136551"
"04/15/2008 10:13:05.470","241.52154573789269"
"04/15/2008 10:13:06.470","506.79324347675828"
"04/15/2008 10:13:07.470","374.88239924735518"
"04/15/2008 10:13:08.470","359.5723012627281"
"04/15/2008 10:13:09.485","427.62119831413077"
"04/15/2008 10:13:10.485","400.15256097639025"
"04/15/2008 10:13:11.485","1076.2868882360847"
"04/15/2008 10:13:12.485","325.94208602935055"
"04/15/2008 10:13:13.485","449.36287592240592"
"04/15/2008 10:13:14.485","344.28220340610181"
"04/15/2008 10:13:15.485","569.57364527132972"
"04/15/2008 10:13:16.485","775.02496015974498"
"04/15/2008 10:13:17.485","729.54466908588222"
"04/15/2008 10:13:18.485","778.70498371189581"
"04/15/2008 10:13:19.485","1080.1269128122422"
"04/15/2008 10:13:20.485","754.56482921490704"
"04/15/2008 10:13:21.485","999.11639434492372"
"04/15/2008 10:13:22.485","701.36448873272786"
"04/15/2008 10:13:23.485","276.26176807531567"
"04/15/2008 10:13:24.485","252.13161364232732"
"04/15/2008 10:13:25.485","533.27341294984296"
"04/15/2008 10:13:26.485","759.54486108711092"
"04/15/2008 10:13:27.485","817.11522953746908"
"04/15/2008 10:13:28.485","129.41082822930068"
"04/15/2008 10:13:29.485","158.94101722251023"
"04/15/2008 10:13:30.485","160.55102752657618"
"04/15/2008 10:13:31.485","655.87419759486454"
"04/15/2008 10:13:32.485","444.49284475420637"
"04/15/2008 10:13:33.485","272.47174381916045"
"04/15/2008 10:13:34.485","94.730606275880163"
"04/15/2008 10:13:35.485","355.28227380655238"
"04/15/2008 10:13:36.485","898.29574909279427"
"04/15/2008 10:13:37.485","1309.3783800216322"
"04/15/2008 10:13:38.485","88.200564483612695"
"04/15/2008 10:13:39.485","1.2300078720503811"
"04/15/2008 10:13:40.485","4.9400316162023437"
"04/15/2008 10:13:41.485","217.90139456892524"
"04/15/2008 10:13:42.485","373.26238887928884"
"04/15/2008 10:13:43.485","73.760472067021226"
"04/15/2008 10:13:44.485","1.4100090240577541"
"04/15/2008 10:13:45.485","34.960223745431975"
"04/15/2008 10:13:46.485","15.990102336654955"
"04/15/2008 10:13:47.485","63.580406914604247"
"04/15/2008 10:13:48.485","26.560169985087906"
"04/15/2008 10:13:49.485","11.150071360456707"
"04/15/2008 10:13:50.485","15.250097600624644"
"04/15/2008 10:13:51.485","17.010108864696736"
"04/15/2008 10:13:52.485","12.210078144500125"
"04/15/2008 10:13:53.485","95.650612163917842"
"04/15/2008 10:13:54.485","38.740247937586801"
"04/15/2008 10:13:55.485","5.6000358402293777"
"04/15/2008 10:13:56.485","1.6600106240679942"
"04/15/2008 10:13:57.485","19.590125376802412"
"04/15/2008 10:13:58.485","2.8100179841150981"
"04/15/2008 10:13:59.485","631.48404149786563"
"04/15/2008 10:14:00.485","579.14370651972172"
"04/15/2008 10:14:01.485","1.0600067840434177"
"04/15/2008 10:14:02.485","15.870101568650039"
"04/15/2008 10:14:03.485","112.28071859659903"
"04/15/2008 10:14:04.485","15.660100224641438"
"04/15/2008 10:14:05.485","4.9800318722039822"
"04/15/2008 10:14:06.485","25.44016281704203"
"04/15/2008 10:14:07.485","16.130103232660691"
"04/15/2008 10:14:08.485","5.9300379522428939"
"04/15/2008 10:14:09.485","4.8400309761982481"
"04/15/2008 10:14:10.485","2.6400168961081349"
"04/15/2008 10:14:11.485","19.430124352795858"
"04/15/2008 10:14:12.485","42.55027232174286"
"04/15/2008 10:14:13.485","37.550240321538055"
"04/15/2008 10:14:14.485","1.210007744049562"
"04/15/2008 10:14:15.485","15.930101952652498"
"04/15/2008 10:14:16.485","20.550131520841735"
"04/15/2008 10:14:17.485","4.0900261761675267"
"04/15/2008 10:14:18.485","8.0100512643280908"
"04/15/2008 10:14:19.485","1.6000102400655365"
"04/15/2008 10:14:20.485","2.3300149120954372"
"04/15/2008 10:14:21.485","1.6200103680663558"
"04/15/2008 10:14:22.485","10.730068672439504"
"04/15/2008 10:14:23.485","4.4600285441826832"
"04/15/2008 10:14:24.485","9.0300577923698704"
"04/15/2008 10:14:25.485","0"
"04/15/2008 10:14:26.485","15.550099520636932"
"04/15/2008 10:14:27.485","2.970019008121652"
"04/15/2008 10:14:28.485","64.580413314645213"
"04/15/2008 10:14:29.485","71.850459842942996"
"04/15/2008 10:14:30.485","53.220340610179903"
"04/15/2008 10:14:31.485","32.620208769336124"
"04/15/2008 10:14:32.485","1.0000064000409603"
"04/15/2008 10:14:33.485","4.8400309761982481"
"04/15/2008 10:14:34.485","8.2700529283387425"
"04/15/2008 10:14:35.485","11.160071424457115"
View 1 Replies
View Related
Sep 29, 2000
Hi,
In my SQL server 7.0, I have got 250 store procedures in each database.
Before using them for my application, I want to ecyption all.
I must add "WITH ENCRYPTION" string in each SP in all database and it'll take me a long time. Is there fastest way to encryption all SPs in all DBs? Have anyone got an utility SP ( or anyway else) to do this?
Thanks in advance.
View 1 Replies
View Related
Mar 10, 2008
What is the fast way a stored procedure can copy a table from a linked server?
I would like to tune this statement, possibly with hints or other logging options. Assume that table_A and table_B have the exact table structure and that I want to preserve table_A and all its indexes and contraints. The table will be truncated before this load, if that helps in any way.
insert into table_A select * from OpenQuery(Server,'select * from Table_B')
TIA, Mike
View 4 Replies
View Related
Jul 20, 2005
In relation to my last post, I have a question for the SQL-gurus.I need to update 70k records, and mark all those updated in a specialcolumn for further processing by another system.So, if the record wasKey1, foo, foo, ""it needs to becomeKey1, fap, fap, "U"iff and only iff the datavalues are actually different (as above, foobecomes fap),otherwise it must becomeKey1, foo,foo, ""Is it quicker to :1) get the row of the destination table, inspect all valuesprogramatically, and determine IF an update query is neededOR2) just do a update on all rows, but addingand (field1 <> value1 or field2<>value2) to the update querythat isupdate myTablesetfield1 = "foo"markField="u"where key="mykey" and (field1 <> foo)The first one will not generate new update queries if the record hasnot changed, on account of doing a select, whereas the second versionalways runs an update, but some of them will not affect any lines.Will I need a full index on the second version?Thanks in advance,Asger Henriksen
View 2 Replies
View Related
Jun 10, 2008
Hey All, I'm trying to decide what's the 'best' to use. I've been designing and creating database for a while and have pretty much always used a surrogate key and not a normal one. I've finally had some free time to start studying more so in my spare time and read up and come accross a lot of guides, articles and stories that tout that normal keys should be used whenever possible as they're a better identifier and that surrogate keys should only be used when there is not a readily available normal key. Now perhaps I'd be open to accepting that but absolutely every database I come across tends to only use surrogate keys. For example I'm doing an authentication system from scratch and am looking at the User table. Now of course the user name has to be unique, should that be the primary key or should I have a seperate column with a guid or an incrementing int or the like as the primary key? I can certainly see that username could be used. I can also see how it may be easier when looking through the data tables to identify who/what a table is refering to with a surrogate key. However it still seems sort of sloppy, for lack of a better word, to me. Where now I could have somebody's username (or any other piece of data used for this purpose) spread accross a lot of other tables. And while writting this I just thought of the scenario that perhaps somebody needs their username changed, with this method now the ids need to be changed on all the related rows of all the other tables whereas with a surrogate key it wouldn't matter. Anyways I'm mostly looking for opinions on which way to go (not just with the user sample, but more in general).Thanks.
View 2 Replies
View Related
May 9, 2001
Hi all,
I've been running a long query which takes almost 39 seconds in Query Analyzer. After creating a Stored Procedure (with the same query) I expected to run it faster bcoz I heared that SP has a cache, and its a faster technique. But I didnt gain any performance improvments.
Can somebody clear my confusion, what I'm doing wrong.
Thanks!
View 1 Replies
View Related
Jan 12, 2000
We have a payroll database that needs to be backed up just before completing the payroll for that period. I need to create a batch file that a normal user can run that will tell the database to back up and then tell the user when it is done so they can continue working. Is there an easy way to do this without giving the users special permissions? I don't want to give them backup op status. Any help would be appreciated.
View 4 Replies
View Related
Jun 19, 2000
I will be taking over a database that has almost no pk's or relations(this is not my choice, but a vendors)
Management is looking at stored procs to improve performance, but I am wondering
if the db is in this state will there really be a gain. I am pushing for normalization
first, but if anybody has any ideas or opinions I would appreciate
View 2 Replies
View Related
Jul 20, 2007
hi friends the below query is actually what type of join whether inner join or normal query..?????
if not exists(select 'x'
from cobi_invoice_hdr h(nolock),
fin_quick_code_met q(nolock) ,
ci_adjustment_drdoc_vw z (nolock)
where h.tran_ou = @ctxt_ouinstance
and h.invoce_cat = @category_tmp
and d.so_no between @sonumberfrom and @sonumberto
and isnull(h.tran_amount,0) between @totalinvoiceamountfrom and @totalinvoiceamountto
and h.tran_date between convert(varchar(10),@invoicedatefrom,120)and convert(varchar(10),@fininvoicedateto,120)
and h.tran_no between @invoicenumberfrom and @invoicenumberto
and h.bill_to_cust between @billtocodefrom and @customerto
and h.fb_id = isnull(@fb,h.fb_id)
and h.tran_currency = isnull(@currency,h.tran_currency)
and h.createdby = isnull(@useridentity,h.createdby)
and EXISTS (select '*' from cobi_cust_custinfo_vw c(nolock)
where h.bill_to_cust = c.custcode
andc.ouid = @ou_tmp )
and z.status = q.parameter_text
and q.parameter_type = 'STATUS'
and q.parameter_category = 'STATUS'
and q.component_id = 'COBI'
and q.parameter_code = @status_tmp
and h.tran_no = z.documentno
and q.language_id = @ctxt_language
and z.language_id = @ctxt_language)
begin
'No matching invoices found.'
select @m_errorid = 514 -- Porselvi.J - COBIDMS412AT_000255
return
end
End
View 2 Replies
View Related
Sep 19, 2006
What is the 'sixth normal form' in the normalisation of tables in database management system.
View 3 Replies
View Related
Dec 26, 2000
Hi,
1)I need to transfer 500 gb of data from one server to other, which is faster, DTS/BCP/Restore.
2)Which are the best methods for checking blocking, dead locks & Indexes!
Thanks you all in advance
Richard..
View 5 Replies
View Related
Nov 5, 2004
I have a master table which has demographic data such as name, dob, location along with a primary key id. It will have about 10-12000 records. We get a refresh file every hour which may or may not have corrections for these records hourly with about 3,000 records. I put this data into a table. This data should be considered always to be correct. To handle the update to the master table I need to create an update process. I can take one of two approaches, just update all the records in the master table regardless if they are correct or not, or do some type of left join on those that do not match (in other words, only update the ones where thae names or dob don't match) There is an underlying update trigger on the patient master which will also fire if these values are changed. An opinions on a best approach?
View 1 Replies
View Related
Feb 2, 2006
Hi,
I have a production server that has an 8Gb db. It is dual Xeon with 5x HDD - 2 mirrored and 3 striped. db on stripe, log and OS on mirror. 2x Gb network cards.
The application goes slow (ie users notice) when a backup is running so i have placed a crossover cable from one NIC to a test server so that it can back up to a HDD on that server, and then to tape. The test server has 2xGb NIC and the link between the two servers is on a seperate subnet to However, in the first trial of this the back up and verify takes 3 minutes longer.
Is this because the target server doesnt have a disk stripe?
What is the best config for the production server (ie will a slower backup but to another server be less load to contend with the application)?
thanks
Fatherjack
View 2 Replies
View Related
May 22, 2007
I've got a view that is driven from a 80 million record table in a data warehouse. I am trying to populate an aggregate table in a datamart, but am running into preformance problems. The datamart table needs to be updated daily. I understand there are many factors that effect performance, but in general would the fastest approach be:
1) Truncate the datamart table
2) Perform a bcp of the view to a text file
3) Bulk Insert to the datamart table
If you need more information to answer this please let me know.
Thanks,
Matt
View 7 Replies
View Related
Jul 23, 2005
Hi!We have Sql Server 2000 in our server (NT 4). Our database have now about+350.000 rows with information of images. Table have lot of columnsincluding information about image name, keywords, location, price, colormode etc. So our database don?t include the images itself, just a path tothe location of every image. Keywords -field have data for example likethis:cat,animal,pet,home,child with pet,child. Now our search use Full-TextSearch which sounded like good idea in the beginning but now it have hadproblems that really reduce our search engine?s performance. Also searchresults are not exact enough. Some of our images have also photographer?sname in keywords -column and if photographer?s name is, for example, PeterMoss, his pictures appears in web-page when customer want to search "moss"(nature-like) -pictures.Another problem is that Full-Text Search started to be very slow when queryresult contains thousands of rows. When search term gives maximum 3000rows, search is fast but larger searches take from 6 to 20 seconds tofinish which is not good. I have noticed also that first search is alwaysvery slow, but next ones are faster. It seems that engine is just"starting" when first query started to run.Is there better and faster way to handle the queries? Is it better torebuild the database somehow and use another method to search than Full-Text Search? I don?t know how to handle the database other way when everyimage have about 10 to even 50 different keywords to search.We have made web interface and search code with Coldfusion. ColdfusionServer then take care of sending all queries to Sql Server.I hope that somebody have some idea how to speed up our picture search.--Message posted via http://www.sqlmonster.com
View 2 Replies
View Related