Hey, SQL Server gurus and experts, please offer your precious advices -
(1) Is it worth going for MCDBA now, when Microsoft has stopped supporting SQL Server 2000 and many companies are upgrading to version 2005 ?
(2) Is MCTS* - SQL Server 2005 useful for SQL Server programmers as well ?
Thank you in advance.
* MS Technical Specialist
_________
Lepanto
Is there any DBA's here that have Database Certifications under them??
View 14 Replies View RelatedHi,
My MSQSQL 2000 application inone company has a backup file of more than 6 GB. The total number of tables are more than 280 while the number of fields are more than 8000.
The last months I noticed that the backup database file is increasing rapidly. Is there a particular reason or anyone fas an idea why?
My largest table consists of more than 160 fields and has amore than 1,2 million records and 32 indexes. I know this is not very good, and I'm thinking methods to fix it since I have noticed performance problems. Can you have some advices?
I read that with MSSQL 2005 I can partition my table horizontally and vertically. However I think that this option is available with the enterprise edition which costs more than 10,000 Euros. Is this correct?
Is there another way to increase performance? My server is Windows 2003 with 4 GB memory. I think that Windows 2003 doesn't support more than 4 GB memory. Is this true?
Are there some advices for my case from the experienced users of this forum??
Regards,
Manolis
I'm a newbe in the realm of database reporting. At my current position, I'm reporting off of CRM databases using Crystal V-11. Previously I'd experience working with HR databases using the same reporting tool. I am interested in progressing to work with database design and scripting. Any suggestion from anyone on which certifications to pursue?
Thanks.
I've almost completed a small project with MS SQL 2000 server in the BE andAccess 2003 in the FE. 5 concurrent users, 30 tables, 35 forms and subforms,13 reports.Not so happy in the beginning as soon as Access Event Model was shown(Crappy at minimum, even compared with not-anymore-existant tools), veryunhappy when the project is almost completed.Main concerns:- User interface VERY slow (UI redraw on not so complex forms, don't talkabout conditional formatting, unstable and...bah!!!!)- Too many bugs IMHO for a 4th generation product- Lack of basic features (record hilight, formatting......)- Crashes- Slow even when running queries on server side (Stored procedures).- Advanced techniques requires you being an acrobat.That is ,I'm seriuosly thinking of REWRITING the whole project, using theexisting DB structure, looking for a seriuos development tool andenvironment. I remember of Borland Paradox 7 for Windows. Ten years ago ithad a far superior event model, no bugs, speed and many limits yes, butwe're talking about a ten years old product. If only Borland continuedimproving and enahnce that product.....Anyhow back to present..... any advice?
View 4 Replies View Relatedfirst set up data source from solution explorer,then new connection from data source from connection managers
directly new ole db connection or ado.net connection etc by right clicking from connection managers
Any help would be greatly appreciated.My problem is that I need to set up a backup SQL Server 2000 machinewhich can be used in case of a failure to my primary. All databases(30 as of now) must be an up to the minute exact copy of productionand include most recent changes in data as well as any structurechanges (Tables, Views, SP's, Triggers, Users . . etc).When I tried this using Transactional Replication, the replicationprocess gets fouled up once I introduce any kind of structure changesto the DB. I've considered the idea of doing periodic backups andrestoring it to my backup SQL server, but this does not give me theconcurrency needed with 0 latency.I've seen articles that recommend using Transaction Replication with'Scheduled Table Refresh', and also doing database dumps to restore onthe backup machine, but I have not been able to find any documentationregarding this to try out. How can I implement this type of backupstrategy in SQL 2000?
View 2 Replies View RelatedBACKGROUND
I just recently set up my SQL 2005 server. Now I wanted to test to make sure Visual Studio can connect to the DB and query the tables.
So for testing purposes I installed the test database that comes with SQL Server called 'Adventure Works'. It installed just find.
I went back to my laptop, fired up VS, when to 'Server Explore' and created the connection, which works perfectly. I'm able to expand the db.AdventureWorks.dbo and see the 'Tables' folder. I can view the table data and everything.
Wonderful!!! However...
PROBLEM
I went back to my default.aspx page, dragged a SqlDataSource on the page, configured it to use the AdventureWorks data and store the connection string. Now when I do a simple query like 'SELECT * FROM Employee' it get the following message:
There was an error executing the query. Please check the syntax of the command and if present, the types and values of the parameters and ensure they are correct.
Invalid object name 'Employee'.
Now I KNOW 'Employee' isn't an invalid object because I can see it and open it in 'Server Explorer'. Can someone please tell me what I'm missing.
Thanks!
Hi,
Whenever i try to connect to my localhost i get an error message telling me that it doesn't exist or access is denied ... This is strange because i had no problems with it up until yesterday ... I can't connect to my Localhost through SQL Server Service Manager ... When i go into Enteprise Manager i can't open localhost or view anything in it ... It just keeps telling me that it doesn't exist or access is denied ...
Thanks,
Jack.
I have a project to convert an application developed in VB 6.0 which has backend as MS-ACCESS and make it compatible with SQL-SERVER 2000. many fuctions like cdate(), IsNull() and UCase() which work in access do not work in SQL. what are their SQL Equivalent?
View 6 Replies View RelatedHi i want to know how much bytes will sql server take to store asingle alphabet like "a". i need to know similarly for all data types(including images).here i am doing a application where i need topredict the amount of space required in sql server to store the userfed dynamic data.Give me a handy solution.Regardsvisu
View 5 Replies View RelatedHi,
I'm hoping somebody can help me here as i'm struggling to find any information elsewhere on the net. We have recently purchased a new server, the rough specs are:
2 X Quad-Core Xeon E7320 2.13GHz 4Mb Cache
32Gb PC2-5300 DDR II RAM
We are planning to install the 64 Bit version of SQL Server 2005. We want to use the server for a number of purposes.
Building and weekly processing of 2 complex data marts (approx size is 1Tb each)
Processing and querying of 2 Analysis Services databases that will be built from these data marts. These will be queried by no more than about 15 users (no more than about 5 simultaneously).
Relational querying of the data marts themselves (same users as above)
My problem is that I am not sure of the best way to configure SQL Server. Should I use 2 separate instances? How should the processors/memory be shared between SQL Server/Analysis Services? My main priority is the performance of the OLAP querying. However, I also want the weekly processing and any ad-hoc SQL querys run against the marts to be efficient.
Any advice would be very much appreciated.
Kind Regards,
David.
I'm wanting to start to understand SQL Server. I've got the Beta Version of MS SQL Server 2000 and was wondering whether I could install this on my Windows Me box and start developing? Is this wise, or should i not use the Beta version? Is there a time when the Beta version "runs out"?
Are there any good basic SQL web sites out there? I know all about Access developement and i know some SQL, but i know nothing about SQL server. Which is the best site for me?
Thanks.
Hi Gurus
Currently we are using SQL Server Reporting Services on top of our client data warehouse. Almost all the reports deployed on the report production server are executing Stored Procedures (behind the scene).
Can anyone tell me how can I query Report Server database to get a list of reports and the stored procedures being executed by these reports?
Early Thanks,
Salman Shehbaz.
Database Administrator
Xavor Corp.
92-321-440-0797
Hi ALL
Currently we are using SQL Server Reporting Services on top of our client data warehouse. Almost all the reports deployed on the report production server are executing Stored Procedures (behind the scene).
Can anyone tell me how can I query Report Server database to get a list of reports and the stored procedures being executed by these reports?
Early Thanks,
Salman Shehbaz.
Database Administrator
Xavor Corp.
92-321-440-0797
Hi,
I have two webservers and another server where the database resides.. and in my webservers i have reporting serverices set up.. in my Report server configuration manager for Webserver 1, in the database setup tab i am specifying the remote the database server which i want to connect and i am providing the correct username and passwords to connect to the remote databse and when i specify the remote database username and password i can see all the databases listed in that server. So in server name i have specified the name of the sql server.. and in the Database name i have specified ReportServer and given credentails type as sql server credentials and given the correct username and password..
But when i give apply i am getting an warning or error saying that The database version (c.0.8.54) does not match the Report service installation. This version cannot be upgraded. You must use a different database.
and in internet explorer if i give http:\localhost
eports I get an error saying
The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'C.0.8.54'. The expected version is 'C.0.8.40'. To continue update the version of the report server database and verify access rights.
any help will be appreciated.
Regards,
Karen
Hi,I have 2 stored procedure 1st insert the data in parent tables and return the Id. and second insert child table data using that parent table id as paramenter. I have foreign key relationship between these two tables also.my data layer methods somewhat looks likepublic void Save(order value){using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required)) { int orderId = SaveOrderMaster(value); value.OrderId = orderid; int childId = SaveOrderDetails(value); //complete the transaction transactionScope.Complete(); }}here 1. SaveOrderMaster() calls an stored procedure InserOrderData which insert a new record in order table and return the orderId which is identity column in Order table.2. SaveOrderDetails() call another sotored procedure which insert order details in to table "orderdetail" using the foreign key "orderid".My Problem:Some time the above method works correctly but when i call it repeatledly (in a loop) with data, some time it gives me foreign key error which state that orderid is not existsin table Order. This will happen only randomly. I am not able to figureout the reason. does some one face the same problem. if yes, what could be the reason and/or solution.
View 5 Replies View RelatedHi,
I want to develop a monthly salary report for our employees. The salary is calculated on daily basis for individiaul employees. Thus the report is supposed to display Employee Name, ID etc + 31 columns (representing maximum days in a month). The report will display the employee basic information along with their salary calculation for each day. So its like 35 columns report. I already have used a cursor in my stored procedure with SQL Server 2005 to get this report (displayed in .NET Page) but the performance is more than worst and takes even 3-4 minutes to compile (which ofcourse is unacceptable). There are around 300 employees and they belong to different departments. When we select a given department, all the respective employees report has to be displayed. If i select All Departments, then the entire 300 employees report has to be displayed. I dont want to cache the data as it will have a big performance issue for the server.
I want some good suggestion on how to develop a monthly report in SQL Server 2005 which can display the data with optimized performance. Kindly help of any guru will be highly appreciated.
Regards...
Hello! I'm trying to get caching work with ASP.NET 2.0 and SQL Server 2005, but I have a problem and I can't figure out what I'm doing
wrong.I have tried to do everything according to the instructions. I
have enabled the database for cache notification by running the
aspnet_regsql tool on the command line like this:aspnet_regsql -S server
-E -d database -etThen I have run the aspnet_regsql tool to enable each
table in the database for cache notification:aspnet_regsql -S server -E
-d database -et -t tablenameI have added the necessary parts into
web.config file, so that it is like
this:<connectionStrings><add name="ConnectionString1"
connectionString="DataSource=servername;Database=databasename;Integrated
Security=True"providerName="System.Data.SqlClient"/> </connectionStrings> <system.web>
<caching> <sqlCacheDependency enabled="true"
pollTime="2000"> <databases> <add
connectionStringName="ConnectionString1"
name="KJ"/> </databases>
</sqlCacheDependency> </caching>I have added this
into global.asax file for the application start event:
System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())To
test caching I wrote this piece of code where I get data from the table
MAAKUNNAT in
the database.----------SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString(),"MAAKUNNAT")Dim
maakunnatDS As New DataSetIf Cache.Get("maakunnat") Is Nothing
Then Dim myConnection As
NewSqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString())
myConnection.Open() Dim cmd As New SqlCommand("haemaakunnat",
myConnection) cmd.CommandType =
CommandType.StoredProcedure Dim riippuvuus As New
SqlCacheDependency("KJ", "MAAKUNNAT") Dim aggDep As New
AggregateCacheDependency
aggDep.Add(riippuvuus) Dim maakunnatDA As SqlDataAdapter =
New SqlDataAdapter maakunnatDA.SelectCommand =
cmd maakunnatDA.Fill(maakunnatDS, "MAAKUNTA")
myConnection.Close() Cache.Insert("maakunnat", maakunnatDS,
aggDep) Else Response.Write("The data is in the
cache") maakunnatDS = Cache.Get("maakunnat")End
If-----------------But when I run the page I get this
error.------------------------------------------The 'MAAKUNNAT'
table in the database 'KJ' is not enabled for SQL
cachenotification.Please make sure the table exists, and the table
name used for cachedependency matches exactly the table name used in cache
notificationregistration.To enable a table for SQL cache
notification, please useSqlCacheDependencyAdmin.EnableTableForNotifications
method, or the commandline tool aspnet_regsql. To use the tool, please run
'aspnet_regsql.exe -?'for more information.To get a list of enabled
tables in the database, please
useSqlCacheDependencyManager.GetTablesEnabledForNotifications method, or
thecommand line tool
aspnet_regsql.exe.----------------------------------------------When I
run the aspnet_regsql tool with the -lt parameter to get a list of enabled
tables in the database they are all enabled. I have searched many pages in
the internet and I cannot find out what I'm still doing wrong. I'd
appreciate it very much if someone could help me with this
problem.Toni S.
Follow up for previouse message
I had only 40 mb of disk space in the c drive. could that be the reason. if so, how can I turn on the server and get into win NT and make the server up and running.
I hope someone can reply as soon as possible
Ahmed
Hi every one
I am new meber of this community with an urgent new problem with SQL server DTS process:) pls HELP!
PROCESS DETAILS:
My data base(db) is on main frame(flat file with hierarchical db format) I used vb script to get that db and split it in to three files so processing is easy after this I use dts PROCESS WITH EXECUTE SQL TASK in combination of some bulk insert scripts to pick these files from server directory and import it in to sql server tables. I know I could have use bulk insert task in dts but my formate file was giving me a problem while I try to develop it through that process so I ended up using tsql bulk insert statment with formate file in the EXECUTE SQL TASK .
PROBLEM :
My problem is I have to manually activate VB script where as I want this to be part of my DTS packet so it can be automated.
I know there is Active x scripting task in DTS but have not used it can some one please tell me if this script can be put in to Active X scripting task with or with out any modifications and also what are the modifications I need to do so it can run before other DTS task (which picks the file and insert it in three different tables after format file application.
this is the VB SCRIPT:Const
ForReading = 1,ForWriting = 2
Dim fso, SourceFile, TargetFile, retstring,Level1No, Level2No, Level3No, Level1Str, Level2Str, Level3Str
Level1No = 0
Level2No = 0
Level3No = 0
Set fso = WScript.CreateObject("Scripting.FileSystemObject")
'Set SourceFile = fso.OpenTextFile("C:SourceWGEE.NONX.GMM220D.UNLOAD.G00178888V00", ForReading)
Set SourceFile = fso.OpenTextFile("C:SourceDataNotepad.txt", ForReading)
set l1 = fso.OpenTextFile("C:SourceLevel1.txt", ForWriting,True)
set l2 = fso.OpenTextFile("C:SourceLevel2.txt", ForWriting,True)
set l3 = fso.OpenTextFile("C:SourceLevel3.txt", ForWriting,True)
Do While SourceFile.AtEndOfStream <> True
retstring = SourceFile.ReadLine
if Instr(1,retstring,"PACM",0) > 0 then
Level1No = Level1No + 1
Level1Str = Rpad(Level1No," ",10)
l1.WriteLine(Level1Str & retstring)
End if
if Instr(1,retstring,"PACPRS",0) > 0 then
Level2No = Level2No + 1
Level2Str = Rpad(Level2No," ",10)
l2.WriteLine(Level1Str & Level2Str & retstring)
End if
if Instr(1,retstring,"PACACC",0) > 0 then
Level3No = Level3No + 1
Level3Str = Rpad(Level3No," ",10)
l3.WriteLine(Level1Str & Level2Str & Level3Str & retstring)
End if
Loop
Function Rpad (MyValue, MyPadChar, MyPaddedLength)
Rpad = MyValue & string(MyPaddedLength - Len(MyValue), MyPadChar)
End Function
THANKS ALL
Hi,
Is it true that I need Analysis Server to simply log the queries being sent to my databases?
I am not familiar with SQL server. In MySQL it is a simple checkbox setting; when I check it, all queries reaching the database(s) are written to a file. But accomplishing this in SQL Server is not so easy, so it seems. I read that I need Analysis Server to do this. I only bought the database, so I will have to purchase it. But I am not sure that this is, in fact, what I am looking for. I need a file that contains something like this:
01/08/2008 14:19:21 UserNameDbName: SELECT name, telno FROM Customer WHERE country = 'Netherlands' AND name LIKE '%bicycle%'
I need the queries exactly as they are sent to the database, from any (type of) client. Can Analysis Server provide this?
Thx,
/sohan
When is RTRIM needed in a Select ... where clause. I noticed that if I have a column named TEXT varchar(17) which is varchar and in the where clause I state where
TEXT = 'This is the text'
or I state
TEXT = 'This is the text ' followed by 4 spaces
The equate still works - so when do I need RTRIM?
Do I need RTRIM for a host variable:
...where TEXT = RTRIM(:VAR_001)
if the host variable is the same length as the TEXT column field in the SQL Server 2005 database?
Hello,
I hope this is in the right forum, I'm new to MSDN. I'm also not an expert in SQL, so please bear with me as I was unable to find an answer by searching the archives.
We have a single SQL 2005 Standard installation with 30 user CALs on a Server2003R2 box. We have another Server 2003 box in a remote but connected location that we previously used for backup replication. What we'd like to do is use some of the replication features in SQL 2005 to replicate the data over every night to the other box, onto which we would install another copy of SQL 2005. This would not be a server accessed by clients, but simply a "live backup", that in the event of a catastrophic failure, could be manually set up to operate as a SQL server in place of the failed primary server. I don't need any kind of automatic "failover", just the ability to replicate the data over to the other system so I have two SQL Servers that synchronize from one to the other overnight.
I was told that I wouldn't need to buy the user CALs again, that the 30 user cals would translate over if the primary server fails and has to be replaced by the cloned server. But what do I buy for the SQL server itself? A boxed version is a bit pricy and comes with 5 CALs which apparently I wouldn't need. Then I noticed that on softwaremedia.com the open license version, which can be bought a la carte, lists a requirement of buying at least 5 cals or a processor license. What exactly do I need to do this, and how can I get it for the least amount of further spending?
Thanks!
Hi All,
I am a new comer to the internet e-commerce and ebusiness service. I have limited budget for my startup. I would like to know whether the SQL Server 2005 standard x32 edition (5 client), as this verion is still relative affordable, could be good enough to support an ebusiness intertnet site. When an internet broswer access to my site, would that access be counted as one client?
Also, I would like to know whether anyone there have experience of using SQL express edition to support an internet site, that potentially need roburst data transaction.
Thanks a lot.
SPA
Hi
We want to upgrade the Clustered SQL Server 2000 in our production environment to SQL Server 2005 (Clustered).
Are there any complexities we need to take care of?
Do we need a downtime during the period when the SQL server is being upgraded?
Thanks
Priyanka
I get the following error when I try to access the database from my web application
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
The application works when I run it on my local machine, but when I move the application to a remote server, I get the above error.
Below is the connection string section in my web.config file.
<connectionStrings> <remove name="LocalSqlServer"/> <add name="LocalSqlServer" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|CiaaASPNETDB.MDF;user instance=true;Integrated Security=True;Initial Catalog=CiaaASPNETDB;Integrated Security=True;user instance=true;Connection Timeout = 0" providerName="System.Data.SqlClient"/> <!--Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|CiaaASPNETDB.MDF;user instance=true;Integrated Security=True;Initial Catalog=CiaaASPNETDB --> </connectionStrings>
Can anyone help me with this.
I have 2 SQL 2000 servers. One has been added as a linked server.
They both have a db called claims. If I am on server 1 and want to write a sql statement I am stuck with the write syntax
I tried select patient.* from testedi..claims.
The server is linked using the sa user. I know this is simple but in a hurry and stuck. THe servers are on same domain.
Thanks
Hi
if i try to delete data via MFC from Microsofts SQL Server 2000
i get following message: Data can be read only.. Weird i can write and read already. do i have to setup anything in Micrsofts SQL Server 2000 ??
please help its urgent.
Greets
Indian
I am getting different results with LEFT outer join operator and *= operator. With *= I am getting the expected results. Can anyone look at SQL and tell what I am doing wrong?
SQL with Left Outer join operator:
select CurrentWeekFinMetrics.[Hub+],
WeeklyMetricsFormat.line#,
WeeklyMetricsFormat.MetricsType,
WeeklyMetricsFormat.Metrics,
WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then
case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol )
else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end
else
convert (varchar, CurrentWeekFinMetrics.displayCol )
end
from WeeklyMetricsFormat LEFT JOIN CurrentWeekFinMetrics on (WeeklyMetricsFormat.Line# = CurrentWeekFinMetrics.Line#)
where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen'
order by CurrentWeekFinMetrics.[Hub+], WeeklyMetricsFormat.Line#
SQL with *= operator
select CurrentWeekFinMetrics.[Hub+],
WeeklyMetricsFormat.line#,
WeeklyMetricsFormat.MetricsType,
WeeklyMetricsFormat.Metrics,
WeeklyMetricsFormat.Measure, WeeklyMetricsFormat.jobs, case when dataformatchar is not null then
case when IsPrefix = 'Y' then dataformatchar + convert (varchar, CurrentWeekFinMetrics.displayCol )
else convert (varchar, CurrentWeekFinMetrics.displayCol ) + dataformatchar end
else
convert (varchar, CurrentWeekFinMetrics.displayCol )
end
from WeeklyMetricsFormat , CurrentWeekFinMetrics
where CurrentWeekFinMetrics.WeekEndingDate = '10/09/04' and CurrentWeekFinMetrics.[Hub+] = 'Amstelveen'
AND (WeeklyMetricsFormat.Line# *= CurrentWeekFinMetrics.Line#)
For Left outer join operator, I am getting 54 rows, *= I am getting 69 rows.
Thanks and Regards
mb
Hi!I have a large project that is due to complete this week. In order tocomplete it I need SQL Server 2000 installed on a remote server. Mydisk is corrupt and to order another media disk would damage mydeadline. I have the licence and serial key, but now need good installfiles. I am even ready to buy another retail box, if I can find asupplier that would give me a download site for the media, while I waitfor the shipment!Please PLEASE help!Regards,Barry
View 6 Replies View RelatedUsing SQL Server 2000 std. edition, I was bitten by the bug described in KBs 818671 and 289149. Query optimizer using Hash Match Team operators would sometimes fail. I added -T8679 at SQL Server startup.
Now that I'm upgrading to SQL Server 2005, is this trace flag still required?
I see that "this was fixed in SQL 2000, SP1." However, I would like a more precise confirmation that this flag is no longer needed in SQL 2005. Sometimes, no news is not necessarily good news.
The error is intermittent, and at least partially dependent on data conditions not available to me for exhaustive regression testing (or else of course I would do that).
Any info, comments, etc. ... would be welcome.
Thanks ...
Tracy Slack
Someone please help me, Im trying to set up SQLServer on my website(IIS v4.0) and I cant seem to get any web apps that use SQL to intall correctly, I get errors like, SQL does not recieve remote connections by default, or just 'Server Not Found'.
This is what I need to know
- How to set SQL to accept incomming remote connections
- Where to find the Name of the SQL instance
- Is it a good idea to have a password on the db, and how do I add one
- A walkthrough on how to set up SQL Express 2005(what to do after the istall is complete)
As you can see Im very new to SQL. So any information you could give to help would be greatly appreciated.
Thanks
Admin @ Something-to-do.com