Tag Info

Hot answers tagged

15

NOT IN (SELECT ...) and NOT EXISTS (SELECT .. WHERE correlation..) are "Anti Semi Joins". That is, recognised set based operations WHERE NOT (MyColumn = 1) is a filter that requires all rows to be looked at For more info, see: Craig Freedman's "Introduction to Joins" Wikipedia "Relational algebra, Antijoins" Edit: for completeness LEFT JOINs often ...


8

Do I need this one at all ? No. The MCSA (and MCSE) are aimed more at administering networks, not SQL Server. These particular 2 are aimed at administering Windows 2003 networks. I think you would be wasting your money taking them as I think these 2 certs will be retiring soon. The Windows 2008 versions use the newer MCTS/MCITP naming convention. In ...


8

Similar Grant Fritchey had the issue where he had closed SSMS and lost the query he had been working on...blogged about here: Oh ****!


7

You might be able to retrieve info from cached query plans, check BOL for info on sys.dm_exec_query_stats, or run this from management studio connected to the same database: SELECT d.plan_handle , d.sql_handle , e.text FROM sys.dm_exec_query_stats d CROSS APPLY sys.dm_exec_sql_text(d.plan_handle) AS e Filter the output with ...


5

OK, there are several things that it could be but I'm not sure if we can determine which from here. These include the following: AD propagation issues AD caching "stickiness" on the target SQL Server Domain Trust issues (particularly if the target server is in a different domain) Windows Group membership/visibility issues UAC blocking users access to ...


5

Check for "CONNECT" rights SELECT SUSER_NAME(grantee_principal_id), * FROM sys.server_permissions WHERE type = 'COSQ' GO USE MYDB GO SELECT USER_NAME(grantee_principal_id), * FROM sys.database_permissions WHERE type = 'CO' To fix as needed USE master GO GRANT CONNECT SQL TO myLogin GO USE MYDB GO GRANT CONNECT TO MyUser GO


5

Yes, it is there correctly. You have run CREATE LOGIN correctly. The number of stars in the SSMS box is just a mask and is unrelated to your actual password. SSMS doesn't know or try to read your password from the server Note that SQL Server doesn't store your password so it can't relate to the **** in SSMS anyway. SQL Server hashes it into the ...


5

If you distribute your application with scripts to build the database (not a database backup or something like that), then in theory it should make no difference which version (of 32-bit and 64-bit) you develop against. Back when 64-bit was new and shiny (which was SQL Server 2000), there were some advanced features that 64-bit didn't support, but in ...


4

You just need to run two stored procedures to remove the setup of log shipping from the primary database How to: Remove Log Shipping (Transact-SQL). As the secondary doesn't exist you shouldn't need to perform step 2 & 4.


4

BI Development is a big subject to learn and will take a lot of time understand all the concepts, I would suggest this book as place to start your journey of discovery: The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset by Joy Mundy Permalink: http://www.amazon.co.uk/dp/0470640383


4

If database was in full recovery mode then there might be a chance to recover some data and get insights on what was done by reading transaction log. Unfortunately this is not supported by default but there are ways to do this. You can try using third party tools such as ApexSQL Log or SQL Log Rescue (free but SQL 2000 only). Another option is to try ...


4

2005+, default trace to the rescue. The default trace rolls over at 20mb but SQL retains the history of 5 traces. With access to the server you could retrieve the *.trc files from the MSSQL\Log directory. If you can't access the server, the following will give you the name of the current default trace file: SELECT * FROM ::fn_trace_getinfo(default) If ...


4

It is not a system level role, it is assigned in each database. Assign the login "public" and "db_backupoperator" roles in each database it needs to backup. http://www.sqlbackuprestore.com/backupandrestorerights.htm


4

This article on msdn indicates it's a new feature in SQL Server 2008. To me that means it's not available in 2005. There are a lot of resources available with step-by-step guides for other data encryption types in 2005, though. I think it's probably beyond the scope of a question on a Q&A site, however.


4

MS SQL server is a broad topic in itself and to be a master in it, you have to specialize in it. The MS SQL certification levels in order of advancement are MCTS>MCITP>MCM>MCA. There are two tracks of exams: Database Development and Database Administration. Passing the first MCTS exam will get you a MCP status. You can get a MCTS and MCITP in both tracks. ...


4

Just do SELECT ItemId FROM MyTable WHERE Value = 0xAAFF You are converting the varchar representation to varbinary(max) which is not correct. SELECT CONVERT(VARBINARY(MAX), '0xAAFF') returns 0x307841414646 for me for example. It will give you a result based on the character codes in that string representation in your default collation's code page.


3

See this brief article: Microsoft hasn't said much as to why English Query was [discontinued]. One could speculate that it may be due to lack of interest or an inability to provide accurate translations.


3

TDE is a new feature in SQL Server 2008. You can do column level encryption of the data within the database or in the application tier but that's about it. What's the goal of the encryption? Protecting data in the database? Protecting data in the data file? Protecting data in the backup files? Etc?


3

I think this achieves what you're looking for. Hope this helps. SELECT Authentication.Ticket , CAST(CASE WHEN Authentication.TicketExpires < GetDate() THEN 1 ELSE 0 END AS bit) AS 'IsExpired' FROM Authentication WHERE Authentication.accountID = @id


3

Some notes When you create a login, then the name must match AD exactly EXEC sp_grantlogin 'DOMAIN\Andy Soell When you map the login to a database user, it can be renamed EXEC sp_grantdbaccess 'DOMAIN\Andy Soell', 'Dilbert' The 2nd bit will fix your issue with implied schema. This happens if don't qualify schema (eg dbo) because SQL Server 2000 has ...


3

I am NOT an expert here but you can try something like below. String sConnectionstring; sConnectionstring = "Initial Catalog=Pubs;Integrated Security=true;Data Source=DCC2516"; SqlConnection Conn = new SqlConnection(sConnectionstring); SqlCommand blah = new SqlCommand("SET ARITHABORT ON", Conn); blah.ExecuteNonQuery(); SqlCommand cmd = new SqlCommand(); ...


3

The only option to think about is disk layout but you only have six of them. This isn't enough for "optimum performance" A RAID 1 volume with 2 disks will be around 135GB so if your database is 100GB+ you'd need RAID5/RAID 10 with 4 disks, but then you don't have enough for separate logs/OS/Tempdb volumes. As an aside, with 100GB+ DB why only 16GB RAM? ...


2

Having the date and time separate will allow you to do aggregates by time much easily. for eg: if you want to run a query to find what time period of the day is most busy. This is much easily performed using a separate time dimension. Also, you should just have one timekey. Decide on either GMT/ EST time - then use this in the fact table. If you need to run ...


2

Many years ago there was s certification called MCDBA. It was really strong certification, with server adinistration and networking adinistration exams inside. Current MCITP certification is easy (I have earned both), and I'm not sure that just finished the courses and just certified MCITP DBAs or DBDs are 100% ready to administer or develop the database. ...


2

Microsoft Access does not know how you want the files exported when you use the standard "export" text function. Here is a trivial piece of code that you can add to a Module, and run from a Command Button on a form, or directly from the Immediate Window: Public Sub ExportTables(ByVal DestinationPath As String) Dim a As Long For a = 0 To ...


2

Is the information on this page any use? It looks like you'll have to write something like the following: select cast(datediff(s,Authentication.TicketExpires,getdate()) as bit) from ... This will return a 1 if the ticket expiry date is in the future, and a 0 if it is exactly now or in the past.


2

You are creating a path to another location, be it SQL Server, Oracle, DB2 whatever. So the security risks are there but can be whatever you make of them. For example if you set up a linked server to a SQL Server and tell it to impersonate SA for every connection then the security risks are massive. If however you tell it to only use the users current ...


1

There's more than one way to do this with Access. (Newer versions of these articles are probably available.) You can make a data access page. If the database is accessible through a layer like ODBC, you can link the tables to Access. This would let employees use Access over the network; it wouldn't use the company's intranet. Use Access + Sharepoint. Use ...


1

You could look into APEX which is available even on the free Oracle Express Edition. Oracle Application Express (APEX) is a Web application development tool for Oracle Database. The tool uses built-in features such as user interface themes, navigational controls, form handlers, and flexible reports to accelerate application development. Oracle ...


1

The application I maintain does something similar that is database OS independent for around 100 users but in a much simpler fashion. Users are authenticated by the application and authorized from the database. Here are the details: a table for users including their email and enabled/disabled status a table of all AD groups that have a specific prefix to ...



Only top voted, non community-wiki answers of a minimum length are eligible