- 8 Week Game
- About Me
- Quick Ref
Archive for category SQL Server
We have an older system which stores XML as a varchar in the database. Copying & pasting from this often truncates the content, and if it doesn;t you then have to paste it into visual studio or something to get the correct formatting etc etc…. it’s a pain.
A quick way round this- if you’re using SQL server 2005 or above- is to cast the output as xml- this makes the xml like a clickable hyperlink in the results panel, and clicking it opens up the full unabridged version of the xml, nicly formatted and indented as god intended.
SELECT TOP 100 [ID] ,cast(replace(request,'encoding="UTF-8"','encoding="UTF-16"') as xml) ,cast(replace(response,'encoding="UTF-8"','encoding="UTF-16"') as xml) ,[UserID] ,[RequestDate] FROM [MyXMLDataLogTable] order by id desc
Thanks go once more to my good colleague Andrew Carmichael for this top tip.
As noted in the article, you can run this batch file;
@echo ========= SQL Server Ports =================== @echo Enabling SQLServer default instance port 1433 netsh firewall set portopening TCP 1433 "SQLServer" @echo Enabling Dedicated Admin Connection port 1434 netsh firewall set portopening TCP 1434 "SQL Admin Connection" @echo Enabling conventional SQL Server Service Broker port 4022 netsh firewall set portopening TCP 4022 "SQL Service Broker" @echo Enabling Transact-SQL Debugger/RPC port 135 netsh firewall set portopening TCP 135 "SQL Debugger/RPC" @echo ========= Analysis Services Ports ============== @echo Enabling SSAS Default Instance port 2383 netsh firewall set portopening TCP 2383 "Analysis Services" @echo Enabling SQL Server Browser Service port 2382 netsh firewall set portopening TCP 2382 "SQL Browser" @echo ========= Misc Applications ============== @echo Enabling HTTP port 80 netsh firewall set portopening TCP 80 "HTTP" @echo Enabling SSL port 443 netsh firewall set portopening TCP 443 "SSL" @echo Enabling port for SQL Server Browser Service's 'Browse' Button netsh firewall set portopening UDP 1434 "SQL Browser" @echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK) netsh firewall set multicastbroadcastresponse ENABLE
One of the things I’ve always liked about MySql is its easy to use command line interface. Microsoft Enterprise Manager was always pretty good, and of course SQL Management Studio, so I rarly have to interface with MSSQL using the command line, but when I did, I always used osql. As of SQL 2008 this was replaced by sqlcmd which is very similar to the mysql command line implementation. Basic usage is;
sqlcmd -S server_name\instance_name -U sa -P your_password
You can then type directly into the console, executing a “GO” after each statement to run. I recently used this to enable remote connections on a new install of sql express, like so;
1> exec sys.sp_configure N'remote access', N'1' 2> GO Configuration option 'remote access' changed from 0 to 1. Run the RECONFIGURE statement to install. 1> RECONFIGURE WITH OVERRIDE 2> GO 1> exit
I’ve recently spent a day of so of my life doing something which I did in minutes using DTS (And I’m sure on SSIS in the past??). I selected export data from a database in sql 2005 and chose my destination database and a bunch of tables. At the end I select save as SSIS package; everything runs fine.
I can exit the sql management studio, run the package again– all ok.
I then log out, log back in, run the package, and I’m swamped with errors about missing tmp files.
It turns out that the ssis wizard, dumps critical parts of the ssis package (table schema and the like) out into temp files (Only when you tick optimize for multiple tables- whcih I had to in order to be able to use transactions), which on our system gets wiped on logout- so when you come back, the package is crippled.
I found a solution on BigResource.com from user jaegd;
The Import/Export wizard created files are stored in the TMP environment variable by default.
To store the Import Export Data wizard files with a checked “Optimize for many tables” setting in a particular directory, create a batch file with the following contents (change the dir name to one you want) and run it.
This approach just changes the TMP location temporarily to a place you’d prefer.
If you don’t want to create a batch file, just run the following from Start/Run…
cmd /c set TMP=c:\place_for_files && dtswizard.exe
This has fixed it! Such a relief- I set the env var tmp to c:\scripts\SSISFiles then generate the SSIS package, and save it to the file system in c:\scripts\ with no sensitive data embedded, then fire it off from the command line, supplying the connection strings.
dtexec.exe /FILE "C:\scripts\MySSISPackage.dtsx" /CONNECTION DestinationConnection;"\"Data Source=SiteDB\SQLEXPRESS;User ID=sync-user;Password=abc123;Initial Catalog=Chinook;Provider=SQLNCLI;Auto Translate=false;\"" /CONNECTION SourceConnection;"\"Data Source=CentralSQL;User ID=sync-user;Password=abc123;Initial Catalog=ProductDataStore;Provider=SQLNCLI;Auto Translate=false;\"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V /CONSOLELOG NCOSGXMT >Log.txt
This way everything is kept nicely grouped together. Thought I would blog it for future me, and to prevent anyone else shaving 3 years off their life with stress.
I recently had this problem with a site I’m working on. I’m using Entity framework to access a bunch of legacy stored procedures, originally in MySQL but ported over to MSSQL. Having added the sprocs to EF and let it auto-generate it’s magic, I was shocked to find one of my calls throwing the following exception;
System.Data.EntityCommandExecutionException: The data reader is incompatible with the specified 'ChinookModel.GetVehicleDescFromModelId_Result1'. A member of the type, 'ivaluationid1', does not have a corresponding column in the data reader with the same name. at System.Data.Query.InternalTrees.ColumnMapFactory.GetMemberOrdinalFromReader(DbDataReader storeDataReader, EdmMember member, EdmType currentType, Dictionary`2 renameList) at System.Data.Query.InternalTrees.ColumnMapFactory.GetColumnMapsForType(DbDataReader storeDataReader, EdmType edmType, Dictionary`2 renameList) at System.Data.Query.InternalTrees.ColumnMapFactory.CreateColumnMapFromReaderAndType(DbDataReader storeDataReader, EdmType edmType, EntitySet entitySet, Dictionary`2 renameList) at System.Data.Query.InternalTrees.ColumnMapFactory.CreateFunctionImportStructuralTypeColumnMap(DbDataReader storeDataReader, FunctionImportMapping mapping, EntitySet entitySet, StructuralType baseStructuralType) at System.Data.EntityClient.EntityCommandDefinition.FunctionColumnMapGenerator.System.Data.EntityClient.EntityCommandDefinition.IColumnMapGenerator.CreateColumnMap(DbDataReader reader) at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, EntitySet entitySet, EdmType edmType, MergeOption mergeOption) at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, MergeOption mergeOption, ObjectParameter parameters) at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ObjectParameter parameters) at Chinook.Model.ChinookEntities.GetVehicleDescFromModelId(Nullable`1 modelid) in C:\_Dev\Chinook\Source\Chinook.Model\Chinook.Designer.cs:line 160 at Chinook.Model.GlassGuideDataProvider.GetVehicleFromModelId(Int32 ModelId) in C:\_Dev\Chinook\Source\Chinook.Model\GlassGuideDataProvider.cs:line 104 at Chinook.Services.VehicleDataLookup.GetVehicleForRegistration(String reg) in C:\_Dev\Chinook\Source\Chinook\Services\VehicleDataLookup.asmx.cs:line 81
It seemed to be moaning that one of the columns in the complex type I set for it to use as the return type, doesn’t match to a column actually being returned, despite entity framework having auto generated that type itself! The cause was, as is generally the case, user error. Inspecting the sproc a bit closer I noticed one of the columns was being returned twice, using the same name. EF generated me a call with an iValuation and iValuation1 column, but failed to match iValuation1 because in reality both columns were simply called iValuation! So a simple, and kind of obvious fix, but worth noting to stop someone else loose a little more hair when they’re confonted with this error!
Just had an hour or so’s worth of ball-ache from this issue! Working on an MVC2 site, i wanted to move the default membership out of the aspnet mdf file and into the sites main MDF file. This should be a pretty simple web.config change, and a matter of running aspnet_regsql against the database. I read an article mentioning that you can do this straight from the command line to your MDF file, but I had no success with this method- it simply created another database for me with a name equal to the full path of the database i had requested. So I found I had to attach my database to my local sqlexpress instance, then run it against that; so in sql;
EXEC sp_attach_db 'mydb', N'E:\_Dev\5imply\5imply-MVC2\BackOffice\App_Data\5imply.mdf'
Then you can just run aspnet_regsql in the same way you would for any other database; In my example I just wanted Membership, roles and profiles
C:\Windows\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe -S .\SQLEXPRESS -d mydb -A mrp -E Start adding the following features: Membership Profile RoleManager .. Finished. C:\Windows\Microsoft.NET\Framework\v2.0.50727>
Then once you’re done, you can detach again;
exec sp_detach_db 'mydb'
Also- one other gotcha I found was that I had to do all this using SQL Management studio, having started it using “Run as Administrator”- I’m not sure if this is something specific to Windows 7, as I would have expected to already be an administrator? But I spent ages wondering why SQL refused to attach my database- Just kept getting an error “create file encountered operating system error 5″ I eventually ran procmon and found it just didn’t have permisisons to the file under the account it was running under.
We have a site finity site which was nocked together by a designer who has since left- unfortunatly one of the meta field we had on the news articles “ViewCount” was set to ShortText instead of Integer, meaning we couldn’t do anything “numbery” with it– like sort it correctly (being text it sorted like 999- 998- 90- 899- 898- 80 for example!), which was kind of crippling the “Most Popular” box on the homepage.
I read up that this is a simple matter of updating the entry for this meta field in the web config- easy enough (in the web.config under telerik > cmsEngine > metaFields);
<!--<add key="News.ViewCount" valueType="ShortText" visible="True" searchable="True" sortable="True" defaultValue=""/>--> <add key="News.ViewCount" valueType="Integer" visible="True" searchable="True" sortable="True" defaultValue="0"/>
But unfortunly, this only applies to any data inserted after that change has taken place, and i had 6000 or so records already in the database as text. Having dug around in the database I found the solution- meta data seems to be held in a table called sf_GCMetaData so having gradually built up a query which isolated my ViewCount fields i set about writing an update statement;
UPDATE sf_GCMetaData SET ValueType = 3, IntegerValue = CAST(ShortText AS Integer) WHERE (Application = '/News') AND (KeyValue = 'ViewCount') AND (ValueType = 0) GO UPDATE sf_GCMetaData SET ShortText = null WHERE (Application = '/News') AND (KeyValue = 'ViewCount') AND (ValueType = 3)
A few notes- ValueType 0 is Shorttext, 3 is Integer- i figured this out by looking at other known meta fields. So i move the data out of Shorttext and put it into IntegerValue field (using a cast to actually convert the data) then in a subsequent update i wipe out the shorttext data, incase this some how messes it up!
So conversion complete and nothing broke– try this at your own risk though and make sure you backup your databases first!!
SQL Server Export : “Text was truncated or one or more characters had no match in the target code page”
I recently did an export from a database table on a thirds party website we host, from the news letter table- Using the standard SQL Export dialogue in sql server management studio 2008, i selected to export to a flat file- i pasted in my query and adjusted the column mappings then hit finish only to be confronted with an error mid way through the export;
Copying to C:\Users\Shaw.Young\Desktop\bq-newsletters1.txt (Error)
Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column “surname” returned status value 4 and status text “Text was truncated or one or more characters had no match in the target code page.”.
(SQL Server Import and Export Wizard)
Error 0xc02020a0: Data Flow Task 1: Cannot copy or convert flat file data for column “surname”.
(SQL Server Import and Export Wizard)
Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component “Destination – bq-newsletters1_txt” (46) failed with error code 0xC02020A0 while processing input “Flat File Destination Input” (47). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
Error 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component “Source – Query” (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)
I checked the mapping for the surname field, and at 100 characters it was the same as the field length so this wasn’t truncating, which left me with the codepage issue. This is easily fixed by simply changing the encoding of the flat file all the way back on the Choose a Destination tab- Mine defaulted to “1252 (ANSI – Latin I)” so i changed it to “65001 (UTF – 8)” and this fixed the problem. Really simple, but I’d actually never come across this before when doing an export so figured I would blog it.
A bit of syntax that I can never remember off the top of my head! SQL 2005 ‘Transaction Log is Full on database xxx’
DBCC SHRINKFILE('dbname_log', 1) BACKUP LOG dbname WITH TRUNCATE_ONLY DBCC SHRINKFILE('dbname_log', 1)
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.