SQL 7.0 Admin Tests
May 19, 1999Has anyone who has taken and passed the SQL 7.0 test for SQL Admin
have any recommendations on the best sources to help pass?
ADVERTISEMENT
Speed Tests (in Vs. Specified)
Jul 30, 2007Hi Everyone -
We were discussing select speeds the other day,
the question comes in at...
what is faster, a standard query with specifies the search critera
i.e. select * from xyz where a='A' or a='B'
or is the IN a better way to go....
select * from xyz where a in ('A', 'B')
we ran the tests in performance analyzer,
but they were the same results....
maybe i'm smoking the stuff - but i thought the
useage of the IN causes a full table scan for each
of the items in the in clause
please advise
take care
tony
Pre-certification Tests
Apr 23, 2007Anyone know of a site or company that provide good pre-tests for certification?
I don't mind paying for downloadable test either.
Peter Larsson
Helsingborg, Sweden
IT Tests. SQL, XML, ASP.NET, C, C++, C#, VB.NET, HTML...
Feb 17, 2006http://www.testyourabilities.com/it/IT tests. SQL, XML, ASP.NET, C, C++, C#, VB.NET, HTML...
View 2 Replies View RelatedTransact SQL :: Last X No Of Tests
Jun 3, 2015I have one requirement where i have multiple tests conducted on different dates where i have to calculate last 'x' no of tests
Test Date
Test Seq
Subject
1/12/2014
1
English
[code]....
If i enter @testcount=2 then it should pull the data for only data related to test date 1/12/2014 & 10/06/2014...Above data is only sample data but logic should be dynamic..
SQL Sever Performance Tests
Jul 28, 1998I am currently investigating tools that are available that would enable us to performance test SQL tables/databases- can anyone recommend any?
Thanks
Web Tests Of Reports Working For Anyone?
Feb 11, 2007Has anyone been successfull in creating a Web Test with Team System to test a Reporting Services report? I can record the test just fine but I can't run the test without errors being reported. I suspect that the session state has to be parameterized but I have no clue how to do this.
Why is this so hard? Why aren't there samples for this kind of thing? I've been all over the SSRS and Team Test forums and the MSDN site and can't find anything about Reporting Services reports being tested with Team System Web Tests.
How To Modify This Code So That It Also Tests For NULL
Aug 2, 2004Hi,
I'm trying to extend the usage of the code below. This method returns a variable of type SQLParameter and what I want is to accomodate even a check for a null in the parameter. This is a code which I got from one of my co-workers who isn't anymore working here. Any help would be appreciated. I do not want to throw an exception here when a field's type doesn't fall into one of the categories below. The method where this methos is called is the first part of my below code. Any help would be appreciated.
Part 1
private void AddCommandParameters(SqlCommand Comm, string sProcName, ArrayList aParams)
{
string sSQL = "sp_helptext @objname = '" + sProcName + "'";
SqlDataReader dr = this.GetDataReader(sSQL);
int i = 0;
if(dr.Read())
{
while(dr.Read())
{
//CLOSING PARENTHESIS
//When at the closing paren of the stored procedure's variable
//declaration, quit adding parameters.
if(dr[0].ToString().Trim() == ")")
{
break;
}
//ENDING PARENTHESIS
//When at the closing paren of the stored procedure's variable
//declaration, do nothing and skip to next record.
else if(dr[0].ToString().Trim() == "(")
{
//Nada
}
//PARAMETERS
//Add all the parameters listed in the stored procedure's variable
//declaration.
else
{
string [] sParamDef = dr[0].ToString().Trim().Split(Convert.ToChar(" "));
int cs = sParamDef.Length;
string st = sParamDef[0].ToString().Trim();
SQLParameter s = GetSQLParameter(sParamDef[0].ToString().Trim());
Comm.Parameters.Add(sParamDef[0], s.Type, s.Size);
Comm.Parameters[sParamDef[0]].Value = aParams[i];
i++;
}
}
}
else
{
throw(new Exception("Stored Procedure: " + sProcName + " NOT FOUND."));
}
dr.Close();
}
Method being called
private SQLParameter GetSQLParameter(string sVarTypeDec)
{
SQLParameter oParameter;
//VARCHAR
if(sVarTypeDec.IndexOf("varchar") >= 0)
{
int iOpen = sVarTypeDec.IndexOf("(");
int iClose = sVarTypeDec.IndexOf(")");
int iSize = Convert.ToInt16(sVarTypeDec.Substring(iOpen + 1, iClose - iOpen - 1));
oParameter = new SQLParameter(SqlDbType.VarChar, iSize);
}
//CHAR
else if(sVarTypeDec.IndexOf("char") >= 0)
{
int iOpen = sVarTypeDec.IndexOf("(");
int iClose = sVarTypeDec.IndexOf(")");
int iSize = Convert.ToInt16(sVarTypeDec.Substring(iOpen + 1, iClose - iOpen - 1));
oParameter = new SQLParameter(SqlDbType.Char, iSize);
}
//SMALLDATETIME
else if(sVarTypeDec.IndexOf("smalldatetime") >= 0)
{
int iSize = 4;
oParameter = new SQLParameter(SqlDbType.SmallDateTime, iSize);
}
//DATETIME
else if(sVarTypeDec.IndexOf("datetime") >= 0)
{
int iSize = 8;
oParameter = new SQLParameter(SqlDbType.DateTime, iSize);
}
//FLOAT
else if(sVarTypeDec.IndexOf("float") >= 0)
{
int iSize = 8;
oParameter = new SQLParameter(SqlDbType.Float, iSize);
}
//REAL
else if(sVarTypeDec.IndexOf("real") >= 0)
{
int iSize = 4;
oParameter = new SQLParameter(SqlDbType.Real, iSize);
}
//SMALLMONEY
else if(sVarTypeDec.IndexOf("smallmoney") >= 0)
{
int iSize = 4;
oParameter = new SQLParameter(SqlDbType.SmallMoney, iSize);
}
//MONEY
else if(sVarTypeDec.IndexOf("money") >= 0)
{
int iSize = 8;
oParameter = new SQLParameter(SqlDbType.Money, iSize);
}
//BIT
else if(sVarTypeDec.IndexOf("bit") >= 0)
{
int iSize = 1;
oParameter = new SQLParameter(SqlDbType.Bit, iSize);
}
//TINYINT
else if(sVarTypeDec.IndexOf("tinyint") >= 0)
{
int iSize = 1;
oParameter = new SQLParameter(SqlDbType.TinyInt, iSize);
}
//SMALLINT
else if(sVarTypeDec.IndexOf("smallint") >= 0)
{
int iSize = 2;
oParameter = new SQLParameter(SqlDbType.SmallInt, iSize);
}
//BIGINT
else if(sVarTypeDec.IndexOf("bigint") >= 0)
{
int iSize = 8;
oParameter = new SQLParameter(SqlDbType.BigInt, iSize);
}
//INT
else if(sVarTypeDec.IndexOf("int") >= 0)
{
int iSize = 4;
oParameter = new SQLParameter(SqlDbType.Int, iSize);
}
else
{
throw(new Exception("Parameter type not supported."));
}
return oParameter;
}
Thanks,
Remote Connection Tests Fine, But Nothing Works On The Page Itself.
Mar 26, 2008If this post belongs somewhere else I appologize. I have spent several days trying to solve this problem with no luck. My site is online. Hosted at NeikoHosting. I can connect to the database remotely when adding a datacontrol. It tests fine. But when running the page it won't connect. Even if I go in and change the Web.Config connection string to a local Data Source provided to me by Neiko, it still won't work. It just won't connect. Here are the two connection strings in the Web.Config, minus my login info: Only the remote string will pass testing. Neither works on the site. <add name="yourchurchmychurchDBConnectionString" connectionString="Data Source=MSSQL2K-A;Initial Catalog=yourchurchmychurchDB;Persist Security Info=True;User ID=me;Password=pwd" providerName="System.Data.SqlClient" />
<add name="yourchurchmychurchDBConnectionString2" connectionString="Data Source=66.103.238.206;Initial Catalog=yourchurchmychurchDB;Persist Security Info=True;User ID=me;Password=pwd" providerName="System.Data.SqlClient" />
Here is the stack trace, if that helps.
[DBNETLIB][ConnectionOpen (Connect()).]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.OleDb.OleDbException: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[OleDbException (0x80004005): [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.]
System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1131233
System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +53
System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.OleDb.OleDbConnection.Open() +37
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.FormView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.FormView.EnsureDataBound() +163
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +50
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Control.PreRenderRecursiveInternal() +170
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041
So....... HELP!!!!
Thank you!
SQL 2012 :: No Disks Were Found On Which To Perform Cluster Validation Tests
Sep 9, 2014I am getting following errors in my Cluster Validation report when trying to create a Windows Cluster.
I have 2 nodes DB01 and DB02 . Each has 1 public ip, 1 private ip (for heartbeat), 2 private ips for SAN1 and SAN2. The private ip's to SAN are directly connected via Network Adaptor in DB01 and DB02.
Validate Microsoft MPIO-based disks
Description: Validate that disks that use Microsoft Multipath I/O (MPIO) have been configured correctly.
Start: 9/9/2014 1:57:52 PM.
No disks were found on which to perform cluster validation tests.
Stop: 9/9/2014 1:57:53 PM.
[Code] ...
SqlDataSource Set To Stored Procedure, Tests OK In Wizard, Does Not Return Data When Executed
Apr 27, 2007With a Gridview Control, I set the SqlDataSource to be a stored procedure in a Sql Sever database.
Using the wizzard to configure the datasource, the test returns lots of rows. After completing the wizzard, the gridview control does not show the column names in the VS2005 designer. For the gridview column headers the values are Databound Col0, Databound Col1, Databound Col2, ....) This tells me I have a problem.
I tried the same thing with a simpler stored procedure. This test stored procedure does not call anything else, takes several input parameters, returns rows. The column names show in the gridview control as expected.
So I am trying to figure out why the first case of gridview with sqldatasource is not working and the second case works . The stored procedure that is not working with my gridview calls multiple inner stored procedures and has #TEMP tables. My complex stored procedure ends with Select * from #Temp.
Could the calling of other inner stored procedures and use of #temp tables contribute to the problem? If yes then what is the strategy for using a gridview and loading it with the data that the complex stored procedure returns?
SQL Server Admin 2014 :: Does Security-admin Role Plus Deny Alter Any Login Cancel Each Other Out
Aug 27, 2015I want to set up a database role so that users can use sp_readerrorlog through SSMS. It does a check on membership in the securityadmin role.
I have tested it and can see you can grant execute on xp_readerrorlog but the SSMS GUI uses sp_readerrorlog.
I thought I could create a user/certificate and add the signature to sp_readerrorlog but it's not permitted (likely because it's not a normal database object).
So the other solution is to add the users to the securityadmin role but then explicitly deny alter any login (best done with a custom server role in 2012+ but otherwise just manually in 2008). I tested this out and it works, I'm not able to alter any logins or increase my own permissions, I also did a check of what's reported from fn_my_permissions(null, null) and it shows minimal permissions like I'd expect.
SQL Server 2008 :: Linked Server Tests Fine But Query Does Not Work
Apr 16, 2015Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.
Here is my linked server:
EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null
Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.
SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')
OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType".
Msg 7321, Level 16, State 2, Line 2
An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".
Admin Access To SQL Without Server/domain Admin Access
Sep 6, 2007We are using Win2k3 R2 with SQL 2000 in a domain environment.
Is it possible to create a domain group to grant admin level and user level access to SQL2000/2005 without giving users server admin or domain admin access?
It has always been my impression that to have admin access to SQL that you had to at least had admin level access on the server.
Any clarification would be greatly appreciated.
Thanks!
Web Based Admin
Jun 25, 2006Does anyone know of a web based front end to Microsoft SQL Server 2005 Express edition? I tried using the SQL Server 2000 server web admin tool from microsoft, but it will not allow me to install it because it says it need the 1.1... framework installed. I have 1.0,1.1,and 2.0 installed, is there anyway to circumvent this? Or has anyone else figured another way to get the webadmin tool to work with 2005 express edition.
Thanks!
SQL Web Admin Tool
Mar 4, 2004Hi,
I've been having trouble getting this tool to work with my server/database. I'm using XP Pro, MSDE, .NET 1.1.4322 and IIS 5.1. IIS has been installed and tested with the help of others. .asp pages are being served locally and to another PC on the network. No obvious problem here. When I installed MSDE I used the SAPWD='strongpassword" command to create a password. I also built a new database. I had positive feedback on both of these actions.
Now to the tool - when I activate the administrator it pops with the little window saying Web Server Configuration. It lists the following options:
Desired Port:
IIS
Cassini Personal Web Server
Restore Faults
and Start and Stop buttons.
If I leave the setting on IIS and click Start it opens a webpage entitled Web Data Administrator. Moving further down I have a welcome message and then the line -
Please enter a SQL Server name:
Username: [Desktopob shadowed entry]
Password
Server (local)
Authentication Method:
Windows Intergrated
SQl Login
and then a Login button
If I select SQL Login and enter "sa" as the username and then my password leaving (local) as the server I get the following error:
Invalid username and/or password, you are using a windows login that is not your own, or server does not exist.
I have tried all combination of \desktopsa, sa, you name it. The password I selected is correct because I made a point of writing it down so I wouldn't forget it. I have also tried all the combos using Cassini as well.
This is where I'm stuck at this point. All suggestions are appreciated.
Thanks,
Bob
Web Based Admin MS SQL
Dec 1, 2005Is there a web based admin to administer MS SQL? If there is a free one, that would be better.
View 2 Replies View RelatedHelp: MSCS Admin
Apr 28, 2001I am new to MS Cluster server. Could anyone tell me the correct procedure to shutdown and restart MS SQL Server 2000 in a Clustered environment? Thanks
View 1 Replies View RelatedRemote Admin
Nov 2, 2001Are there any tools for administering SQL2000 remotely over the TCP/IP? And if this is possible what are the main hurdles on setting it up. Emulation is an option.
View 2 Replies View RelatedCluster Admin. And SQL
May 3, 2000Anybody know if you can manage the Cluster Administrator tool from a remote workstation? I've found nothing(except PcAnywhere and the like) documented on this..
Thanks
Pete
SQL Admin Wages
May 4, 2000Hi,
I am fairly new to SQL server admin.
What sort of wages are paid for this type of work.
Is there a shortage skilled administators in this area?
Does the MSCE DBA increase your wages?
Thanks
Admin Tools
Jul 5, 2000Hi Guys
I have just been trying to install the Admin tools from the SQL 6.5 setup CD on a Win NT Workstation 4 SP6 system. Everything seems to go fine until the icons are addded into the program group, these come up as system default icons and don't link to any programs. If I go into the installed directory and execute the programs then I get the following message:
The SQLOLE OLE object could not be registered. Class not registered (80040154)
After clicking OK the program terminates. I have tried diferent install directories, even diferent partitions. I have also deleted the directory and removed the Registry entries manual - this is because it cannot be uninstalled.
Still no luck.
Any ideas?
Many thanks
Stuza
SQL 7 Admin Exam
Jan 4, 2000Does anyone know of a good book that will prepare you for this exam. I know that in some early discussions that Mike Hoetch warned against some of the new books on the market.
Thanks
Ricky Kelley
Domain Admin And Sa
Dec 13, 1999A couple of newbie questions:
1) Do Domain Admins have SA rights by default in SQL7? If so, is there a way to keep domain admins out of particular databases.
2) Is it possible to create a database or table that even SA can't get into?
Thanks
JD
A SQL 7.0 Admin Book?
Jun 5, 1999Any recommendations on a good SQL 7.0 Systems Admin book for future DBAs? Too many books at the store, so i'm wondering how i can narrow it down. I'm looking more for the Admin side rather than developer side type book. Thanks.
View 2 Replies View RelatedDBA Admin Plan
Jan 11, 2000Hi there,
Could anyone please help me, I have been given the responsibility of planning and implementing the admnistration of 25 existing SQL Servers (6.5 and 7.0) countrywide and I am unsure as to where I should start. I am relatively new to SQL Server and if someone could just point me in the right direction or possibly recommend any good publications which I could refer to, I would be extremely grateful.
Ms Sql Admin .....exam..help
Mar 1, 2001Hi
Here is a mcdba admin question
Consider the following time line:
8:00 You accomplish the full database backup.
8:10 User1 enters the order 53 for customer 10, whose name in the database is
XXXXX (I
can't remember, but not Bill Smith)
8:12 User2 at remote server changes the name XXXXX to YYYYY
8:13 User3 changes the name YYYYY to ZZZZZ
8:14 User3 commit the transaction.
8:20 Media fails for database.
8:30 You restore the database from 8:00 backup. What is the status of the order
53 in the
database?
a) The order 53 is not in the database.
b) The order 53 is in the database and name is XXXXX.
c) The order 53 is in the database and name is YYYYY.
d) The order 53 is in the database and name is ZZZZZ.
Ans: B The name was already there.
The answer is B but I think th answer is A
becouse He or Her restroe the database from 8:00
I think the 53 order maked after 8:00 so
there is not order 53 in the backup database
How about you think and why !!!
Thanks kjs
Database Admin
Dec 30, 1999long, bad story here is the brief:
sql: 6.0 w/ fixes
platform: intel NT 4 sp3+
our "dba", i use that term loosly as the person who filled that position had little expertiese in managing sql, found a new job and has left; i worked with him on periphery matters so i'm less effective than he. unfortunatly ..."he with the most experience..." becomes "the man" so now i'm working to become a good dba and require some assistance.
we had a contractor come in and work out a method for archiving data using bcp, some batch files, and sql scripts; prior to this he assisted us in porting from one platform to another, digital alpha running NT to intel platform running NT.
in brief the procedure is as follows:
using options of EM create scripts to create all objects except indexes, again use EM to create sql scripts to create all objectes including indexes.
obtain sizing information and create a device and database, including log etc.
use the create objects script to create objects in the newly created database
use a custom script to create a batch file to bcp data from the primary database into flat files.
use a custom script/batch file to create a batch file to bcp data into the newly created database.
use the create indexes sql script to re-create indexes. we have to modify the sql create all objects including indexes script. i've worked up a batch file using for /f and if == to do this.
do some row counts and spot checks of tables stored procedures to verify conversion.
so now my questions:
using the information supplied causes problems, the tables end up being created but are owned by the wrong user. is there a way to change table, stored procedure, or object ownership? i create the database and objects as sa, then populate with bcp and re-create the indexes as the desired owner. my understanding is that sa has the special permissions needed to create objectes and the create objects script should make the appropriate users, logins, and changes to the db objects... where am i going wrong?
Admin Task
Apr 7, 2004Can anyone tell me whether there is any way to automatically genereate SQL script for all SQL jobs on a server?
I need to create the script for about 30 jobs and dont want to spend hours getting RSI clicking on each one and exporting the script if i can avoid it!!!!
TIA
Sql Admin Training
Feb 23, 2006has anyone tried the cd/learning from sql usa
http://www.sqlusa.com/order2005/.
Interested in any input
Sql Admin Via The Internet
Mar 29, 1999Hello,
I will soon be managing a sqlserver/iis4.0/nt box located remotely in another state. It has a permanen connection to the net. I have a dialup connection.
I have a remote control package, but it is slow. Is there a way to add that boxes sql server to my enterprise manager on my local nt server so that I can
adminsiter it from my machine? I am not over familair with sql server, but quickly learning!
Will I need to set up a vpn to do that?
I searched microsoft knowledge base, but could not find anything.
Thanks for your support.
Johanna
SQL 2000 Admin
Aug 29, 2001I am trying to find some resources for SQL 2000 admin. I have a book and will be taking a class, but am still looking for a forum and a bit of Technical expertise to discuss issues with.
Thanks
Dbforums Admin??!!
Jan 9, 2006Hi
Does any one know what the email contact is the dbforums admin? I can't seem to locate it, neither an online mailer?
Thanks