Hot answers tagged microsoft
13
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 ...
7
Similar Grant Fritchey had the issue where he had closed SSMS and lost the query he had been working on...blogged about here:
Oh ****!
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 ...
5
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
...
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
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
3
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
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
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
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 ...
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
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
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();
...
2
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
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.
1
Short answer... don't drop any logins/users, use the stored procedure sp_change_users_login to fix your issue.
Here are step-by-step instructions:
You do not need to drop and re-create users.
Follow the steps below
script logins from source server -- use sp_help_revlogin which you can download from microsoft at http://support.microsoft.com/kb/918992
...
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 ...
1
Step 1: Download SSMS LINK
Step 2: Download Red Gate SQL Search LINK
Step 3: Install both
Step 4: Connect to your database server
Step 5: Search!
Only top voted, non community-wiki answers of a minimum length are eligible
