SQL
I was building a quick test web site and I was using the aspnet_regsql tool to add membership to a SQLEXPRESS database. At first, I tried: aspnet_regsql -A all -C "Data Source=.\SQLEXPRESS;Integrated Security=True;User Instance=True" -d "C:\code\Test\APP_DATA\aspnetdb.mdf" For some reason, the SqlConnection insisted that it try to create the database and disregarded the full path to the database (note the path in the exception).SQL Exception:
System.Data.SqlClient.SqlException: Directory lookup for the file "C:\Documents and Settings\user\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS\C:\code\Asp.net\ServerControlTest\App_Data\Database.mdf" failed with the operating system error 123(The filename, directory name, or volume label syntax is incorrect.).
CREATE DATABASE failed. Some file names listed...
In my SharePoint experiments with form-based authentication (FBA), I have been installing self-signed SSL certificates since I am developing in a virtual machine without a certificate authority. Last night, I shut down my virtual machine instead of the usual Suspend operation. This morning, I started the virtual machine but SharePoint wasn't working and said: Cannot connect to the configuration database. I then tried to open SQL Server Management Console and tried to connect, only to receive the following message: I didn't freak out, but I did check the SQL Server Service which was running, and then the SQL...
In SQL, aggregate functions will return the group by values or the aggregate function results, but it is difficult (or at least harder than it should be) to return the primary key or ROWID. In contrast, most programming languages will return the instance (or a pointer/reference to the instance) when searching for items. Background A project manager allocated me several weeks ago on emergency basis to help out with another project that was having difficulties with a SQL Historian system. I ended up developing a nice set of SQL tables, functions, and stored procedures to transfer data from a remote...
I was setting up a linked server from my web database to my local database to ease the backup procedure. Currently I have a script which I run to copy all of the database from the web database to my local database. I thought I could simply use sp_addlinkedserver to conect the SQL Servers. My initial attempts to connect the servers was rebuffed:-- Try 1 (failed)
EXEC sp_addlinkedserver
@server = 'subtext_web'
,@srvproduct = 'SQL Server'
,@datasrc ...
I had a little fun with SQL Server last week trying to normalize a gas composition function that was returning a molar percentage of the gas. During testing we found several cases where the sum of the components did not add up to 100.0%. Of course due to floating point inaccuracies it is quite possible that rounding errors could also contribute to the issue. The values we were seeing were a couple of percentage points different and so we decided to normalize the data to ensure that it added up to 100.0%. At the same time, I rounded the components...
I must be getting tired... Once again I almost damaged my blog trying to get a pristine local copy. This time I decided that it would be easiest to export the blog as BlogML and reload it into the local web application. However I didn't notice that when I first exported the blog, it generated an error (I think due to the "Embed Attachments" option) in the export so I regenerated the export without embeded attachments. When I went to import the BlogML, somehow I didn't notice that it was connecte
Aside from the fact Houston has been getting a significant amount of rain this year, it seems that work and personal projects are also experiencing their share of challenges. I wanted to adjust the skin of my blog the day before yesterday and I wanted to try it out offline on a local installation. I also wanted to make a full backup of the database which served multiple purposes including being able to test changes locally. I had made a copy of the data previously using the SQL Server 2005 Export Data DTS mechanism, but it did not contain the...
The past several days I have been wrapped in relatively complex calculations in SQL Server. The process involves gas composition data from a time-series historian that is weighted by flow-rate and averaged. Then the composition is used for several lookup tables for volume correction based on the gas mixture and temperature. The algorithm tries to find the closest x and y values to the supplied parameters. If any of the parameters match the lookup table, then standard linear interpolation can be
I recently installed some tables via SQL Query Analyzer under a user id that was not 'dbo'. I needed to change the owner on the tables and wrote this simple query to generate the execute statements to change the owner. Run the query to generate the next query -- so you can review the list prior to running it.SELECT 'exec sp_changeobjectowner ''' + name + ''', ''dbo'''
FROM sysobjects
WHERE type = 'U'
AND uid != 1
ORDER BY name
The result looks something like:exec sp_changeobjectowner 'asset', 'dbo'
exec sp_changeobjectowner 'project', 'dbo'
exec sp_changeobjectowner 'salesorder', 'dbo'
I was working on a project last week where I needed to update the columns and data types in tables in SQL Server. It is pretty quick to do this manually such as:ALTER TABLE orders
ALTER COLUMN orderid varchar(36) NOT NULL
I prefer this compared to the code SQL Server generates in the change script which typically renames the existing table, creates a new table, copies the data from the previous table to the new table, and then restores indices and keys. While that is appropriate for dramatic changes, it is overkill for something of this nature.
Since I love...