- 8 Week Game
- Quick Ref
Archive for category SQL Server
Without linking the servers you can do one off queries against another server using the OPENDATASOURCE or OPENROWSET functions. You will need to first enable “Ad Hoc Distributed Queries” as this is disabled by default- this can be achieved using sp_configure (if you’re logged in as sa)
sp_configure 'Ad Hoc Distributed Queries', 1 reconfigure
Once active you can use the function like this;
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=<servername>;User ID=<user>;Password=<password>').<dbname>.<dbo>.<tablename>
There is also an OPENROWSET function;
SELECT source.* FROM OPENROWSET('SQLOLEDB', 'Data Source=<servername>;User ID=<user>;Password=<password>', 'SELECT * FROM <dbname>.<dbo>.<tablename>') as source
Thanks to Richard who dug this little nugget up from the interwebs.
So I did a post for SQL 2000 aaages ago showing how to do this- it’s a lot easier in SQL 2005 but i always forget the syntax as i rarly have to use it.
Open a new query editor on your selected database server then run this;
use <database_name>; /* CREATE A NEW ROLE */ CREATE ROLE db_executor /* GRANT EXECUTE TO THE ROLE */ GRANT EXECUTE TO db_executor
Then right click your database user (under security for the given database) and tick the new db_executor role. You’re done!
I always seem to remember the access syntax for this rather than the sql server version–
update hachette_digitalideas_import set harrietId = u.Id from hachette_digitalideas_import h left outer join user_group u ON (h.email = u.email or h.[full name] = u.name) AND u.btype = 'u'
As opposed to the following in access;
update hachette_digitalideas_import left outer join user_group u ON (h.email = u.email or h.[full name] = u.name) AND u.btype = 'u' set harrietId = u.Id
In Summary: I basically wanted to take the data in a table, then linked data from another table concatenated into a comma separated string, and attached as a varchar column on the end of the first table!
We have an internal app to allow staff to order with a table structure like so;
OrderHeader -> OrderDetail -> Publisher -> Warehouse
On the order header view page we need to know which warehouses are involved, which means a query going right down the chain from product on the order line, to the publisher, down to which warehouse that publisher is based at- because we can only tie to a single table or view I had a problem unless i wanted loads of repeated rows- after some googling I found CROSS to be what I was after;
Based on the above post I was able to build SQL like so;
SELECT OrderHeader.id, OrderHeader.user_id, OrderHeader.OrderDate, Users.name, WareHouses = LEFT(o.list, LEN(o.list)-1) FROM OrderHeader INNER JOIN User ON OrderHeader.UserId = User.UserId LEFT OUTER JOIN CROSS APPLY ( SELECT distinct CONVERT(VARCHAR(50), WareHouse.Name) + ',' AS [text()] FROM WareHouse INNER JOIN Publishers ON WareHouse.id = Publishers.WarehouseId INNER JOIN OrderDetailLine ON Publishers.PublisherID = OrderDetailLine.PublisherID WHERE OrderDetailLine.OrderHeaderId = OrderHeader.OrderHeaderId ORDER BY CONVERT(VARCHAR(50), WareHouse.Name) + ',' FOR XML PATH('') ) o (list)
This bought me back each row, with a comma separated list of the warehouse names in the end column which i could then filter on!
We have a SQL Server 2005 box, on which we have a database which has a full text index. The database gets a massive refresh of its data each night, meaning the full text index needs rebuilding. I added a schedule to my full text index (Databases > [DBName] > Storage > Full Text Catalogs > Right click your index – click properties > Population Schedule) and this created me a new job (SQL Server Agent > Jobs) but when i opened it up and went to steps, nothing was there! If i tried running the job it would fail because there are no steps!
I found this as a documented bug in the initial release candidate of sql 2005 (since fixed in the service packs) on the microsoft connect site, and added a workaround – basically just add your own job, using the following sql (which i found on msdn) to rebuild your index;
ALTER FULLTEXT CATALOG [catalogue_name] REBUILD;
Of course, this is only a workaround until we can get the latest service pack installed.
I’m building a project using entity framework and wanted to ensure it wasn’t going mental and spamming the database with more queries than it should- this requires SQL Profiler, but i didn’t know how to connect it to my local instance of sql (I had just added an MDB file to the app’s app_data folder, so it was running via local sql express)
Turns out it’s easy- you just connect to .\SQLEXPRESS database and then execute this query against the master database;
SELECT owning_principal_name, instance_pipe_name FROM sys.dm_os_child_instances
From the results you will be able fish out the “named pipe” that you can just put into the server name box when connecting with profiler.
I’ve written lots of app’s which store email addresses, and usually go for a nvarchar(100) to store them, but thought I would find out once and for all, what the actual maximum size was- just out of curiousity.
Having googled around, I came across a couple of lengthy posts on the matter (here and here), quoting figures from various standard (which seemed to point at 320 character), then corrections to those standards by members of ICANN based on the SMTP spec and so on and so forth…
After much trawling, it seems the official answer is : 254 characters (256 including the standard <> brackets around the address)
In SQL Server 2005 onwards you can have conditional Order By clauses in your stored proc’s- for example
SELECT EmployeeID, SalariedFlag FROM HumanResources.Employee ORDER BY CASE SalariedFlag WHEN 1 THEN EmployeeID END DESC ,CASE WHEN SalariedFlag = 0 THEN EmployeeID END; GO
Ok well, it does, but I’ve had a very stressful afternoon at it’s merciless hands! Just trying to get the ASP.Net roles & membership working on asp.net 3.5, using IIS7. I created the project and clicked “ASP.Net Confguration” from the project menu, but no working providers existed.
I defined some custom providers and nothing would get picked up.
It turns out, under IIS you need to define a connection string with a specific name to make this work–
<connectionStrings> <clear/> <add name="LocalSqlServer" connectionString="your real connection string goes here!" /> </connectionStrings>
So we’re clearing out the default inherited connection strings then overriding the default LocalSqlServer connection to have the correct connection to our real database, as this appears to be the connection string it defaults to.
Save the web.config file, the fire up the “ASP.net configuration” site again and you should be away!
Thanks to Mr Ledger for channelling the awesome power of google through himself to yield that nugget of joy and relief!
An error occurred during the execution of the SQL file ‘InstallRoles.sql’. The SQL error number is 446 and the SqlException message is: Cannot resolve collation conflict for equal to operation.
Cannot resolve collation conflict for equal to operation.
I recently had this problem while trying to setup the roles & memberships gubbins on a database using the aspnet_regsql tool on the v2 .net framework.
This turned out to be because this was a SQL 2005 database which we had imported from a third party, produced on SQL2005. The SQL2005 database had a collaction of SQL_Latin1_General_CP1_CI_AS while our sql 2000 box’s default collection was Latin1_General_CI_AS.
The fix was simple; having backed up our database (!) simply run this command;
Alter Database testdatabase Collate Latin1_General_CI_AS
This converted the database to the same Latin1_General_CI_AS collation as the server’s default, and allowed the aspnet_regsql to do it’s job unhindered!
NOTE: If you’ve ran aspnet_regsql once already before correcting your collation (which i would imagine you probably did!), make sure you run it again in remove mode before running a final time in install mode, as the first time it runs it will bomb out after creating some of the tables already leaving you with tables in your database still using the old collation!