http://support.microsoft.com/kb/946737 – Method D
Run the following commands in order from a command prompt. I just stick them in a batch file called fixprint.bat under the %windir% windows folder.
net stop spooler
del %systemroot%\system32\spool\printers\*.shd
del %systemroot%\system32\spool\printers\*.spl
net start spooler
Found a good step by step article here on SQL server central
http://www.sqlservercentral.com/articles/Service+Broker/67513/
At a high level these are the points to remember
To configure the service broker to send and receive messages between databases (either in local/remote instances), it needs few components in place to start the service. These are listed below.
If Visual Studio returns the message "No Files Were found to look in" from the find in files results window, then just do a Ctrl+Alt+PauseBreak key press withing the find results window, and search again.
I came across this issue myself this weekend,
http://en.community.dell.com/support-forums/peripherals/f/3528/p/19298625/20231966.aspx#20231966
"When trying to scan from printer direct or via all-in-one centre the printer starts the scan and then shuts down/re-starts? Any ideas/seen this before? Thanks Steve"
Spent ages searching the web for a solution for this one, but in the end the old "2 - inch drop" was all it really needed...
Just took the two screws out of the left of the flat bed scanner glass section. Then removed the left hand grey panel that snaps in place to the left of these screws on the side of the printer. Then managed to get enough leeway out of the flat bed scanner glass holder, to reach in and give the scanner bar inside a bit of a shake.
Switched off and on again, and the scanner bar reset itself to the correct location, which is, when working correctly, out of site underneath the A4 indicator bar.
/// <summary>
/// Serializes this class to a json string.
/// </summary>
/// <returns>A JSON string reprsenting a SelectListItem.</returns>
public string ToJson()
{
using (var memoryStream = new MemoryStream())
{
var dataContractJsonSerializer = new DataContractJsonSerializer(typeof(JsonSerializableSelectListItem));
dataContractJsonSerializer.WriteObject(memoryStream, this);
memoryStream.Position = 0;
using (var streamReader = new StreamReader(memoryStream))
{
return streamReader.ReadToEnd();
}
}
}
If your VPC seems slow try the following steps to rectify.
1. Have separate drives for your Page File, Host operating system, and VHD file
2.Try defragging again using O&O defrag (http://www.oo-software.com/home/en/products/oodefrag/) instead of the standard windows defrag tool, it works better. The steps below may take some time but should dramatically improve the responsiveness of your VPC. Make sure you have at least as much free space on your drive where the .vhd file is located as the current size of the .vhd. For example, if your .vhd file is 8GB, you need 8GB+ of free space. This much free space is not necessary but will speed up the defrag process. At the very least, try to have a couple gigs of free space before running defrag.
Warning: If you run these steps on a differencing or undo disk, compacting will require you to merge the differencing or undo disk with the original. If you cannot do this, your differencing or undo disk will be twice the size. However, it will most likely still perform better.
Note: Running defrag and precompacting are done inside the VPC. Compacting the disk is done using the Virtual PC Console with the VPC turned off.
Running Defrag (about 30 to 90 minutes depending on how fragmented the drive is)
1. Download the defrag utility and extract the defrag.exe and defragps.dll files to a location on the VPC root drive.
2. Inside the VPC, click Start, and then Run. Type cmd and press ENTER.
3. Change the path to the location where you extracted the defrag.exe and defragps.dll files , i.e. cd c:\tools\defrag and press ENTER.
4. Type defrag -d c: and press ENTER.
5. Let this process go for awhile. At the end your .vhd file will be about twice the size.
Next, you must precompact inside the VPC before compacting the .vhd.
Running Precompact (about 10 minutes)
1. On the VPC menu click CD, then Capture ISO Image.
2. Browse to C:\Program Files\Microsoft Virtual PC\Virtual Machine Additions\
3. Double-click Virtual Machine Precompactor.iso
4. If it doesn't launch, open the D drive in Explorer and double-click precompact.exe.
5. Let this process run. When it's done, shut down the VPC.
Now you can compact the .vhd file using the Virtual PC Console
Compacting the .vhd file (about 30 minutes)
1. Go to the Virtual PC Console, select your VPC, and then click Settings.
2. Select Hard Disk 1.
3. Click Virtual Disk Wizard.
4. Click Next on the first screen of the wizard.
5. Click Edit an existing virtual disk, and then click Next.
6. Click Browse on the Virtual Disk to Edit screen of the wizard and browse to your .vhd file. Select it and click Open.
7. Click Next.
8. Click Compact it (this should be the default), and then click Next.
9. Click Replacing the original file, and then click Next.
10. Click Finish. The disk compacting will start.
Other Performance Tips
1. Right-click My Computer, and then click Properties.
2. On the Advanced tab click the Settings button in the Performance section.
3. On the Visual Effects tab, click Custom then uncheck everything in the list below.
4. Click OK.
1. Right-click the desktop, and then click Properties.
2. On the Settings tab click Advanced.
3. On the Troubleshoot tab, move the Hardware acceleration slider to Full.
4. Click OK
If you get the above error when trying to return large numbers of items in a List<T> from a WCF service, then ensure you have set the following behaviour for the dataContractSerializer in both your client and service configuration files..
<dataContractSerializer maxItemsInObjectGraph="2147483646"/>
As in this blog, but with larger number (i.e. max of int)http://processmentor.com/community/blogs/scott_middleton/archive/2007/06/08/169.aspx
Need the settings in service layer and client, set to this.
Service has this
<behavior name="MyService.ServiceImplementation.MyService_Behavior">
<serviceDebug includeExceptionDetailInFaults="true" />
<serviceMetadata httpGetEnabled="true" />
<dataContractSerializer maxItemsInObjectGraph="2147483646"/>
</behavior>
</serviceBehaviors>
<service behaviorConfiguration="MyService.ServiceImplementation.MyService_Behavior"
name="MyService.ServiceImplementation.MyService">
<endpoint binding="basicHttpBinding"bindingConfiguration="BasicHttpBinding_Leads"
bindingNamespace="http://MyService.ServiceContracts/2007/04"
contract="MyService.ServiceContracts.IMyService" />
<endpoint address="mex" binding="mexHttpBinding"contract="IMetadataExchange" />
</service>
</services>
Client has this …
<behaviors>
<endpointBehaviors>
<behavior name="debuggingBehaviour">
<dataContractSerializer maxItemsInObjectGraph="2147483646" />
</behavior>
</endpointBehaviors>
</behaviors>
<endpoint address="http://localhost/Host/Internal/myservice.svc" binding="basicHttpBinding"
bindingConfiguration="BasicHttpBinding_IMyService"contract="LeadsServiceInternal.IMyService" name="BasicHttpBinding_IMyService"
behaviorConfiguration="debuggingBehaviour"
/>
Note you must also set the following two settings on the client maxReceivedMessageSize and maxBufferSize
<binding name="BasicHttpBinding_IMyService" closeTimeout="00:01:00" openTimeout="00:01:00"receiveTimeout="00:10:00" sendTimeout="00:10:00" allowCookies="false" bypassProxyOnLocal="false"
hostNameComparisonMode="StrongWildcard"maxBufferSize="500000000" maxBufferPoolSize="524288" maxReceivedMessageSize="500000000"messageEncoding="Text" textEncoding="utf-8" transferMode="Buffered" useDefaultWebProxy="true">
<readerQuotas maxDepth="32" maxStringContentLength="8192"maxArrayLength="16384" maxBytesPerRead="4096" maxNameTableCharCount="16384"/>
<security mode="None">
<transport clientCredentialType="None"proxyCredentialType="None" realm=""/>
<message clientCredentialType="UserName"algorithmSuite="Default"/>
</security>
</binding>
SELECT 'To' 'Direction',
OBJECT_NAME(sfk.constraint_object_id) 'ConstraintName',
OBJECT_NAME(parent_object_id) 'ConstrainedTable',
psc.name 'ConstrainedColumn',
OBJECT_NAME(referenced_object_id) 'ConstrainingTable',
csc.name 'ConstraingColumn',
'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(sfk.constraint_object_id)) + ' FOREIGN KEY(['+psc.name + ']) REFERENCES ' + OBJECT_NAME(referenced_object_id) +' ([' + csc.name +'])' AS [CreateScript] ,
'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id)) + ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(sfk.constraint_object_id)) AS [DropScript]
FROM sys.foreign_key_columns sfk
INNER JOIN sys.columns psc ON psc.column_id = sfk.parent_column_id
AND sfk.parent_object_id = psc.object_id
INNER JOIN sys.columns csc ON csc.column_id = sfk.referenced_column_id
AND sfk.referenced_object_id = csc.object_id
WHERE OBJECT_NAME(sfk.constraint_object_id) IN
(
SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
)
These two links are definitely worth a read I think...
http://oreilly.com/javascript/excerpts/javascript-good-parts/bad-parts.html
and especially…
http://oreilly.com/javascript/excerpts/javascript-good-parts/awful-parts.html
e.g.
Semicolon Insertion
JavaScript has a mechanism that tries to correct faulty programs by automatically inserting semicolons. Do not depend on this. It can mask more serious errors.
It sometimes inserts semicolons in places where they are not welcome. Consider the consequences of semicolon insertion on thereturn statement. If a return statement returns a value, that value expression must begin on the same line as the return:
{
status: true
};
This appears to return an object containing a status member. Unfortunately, semicolon insertion turns it into a statement that returns undefined. There is no warning that semicolon insertion caused the misinterpretation of the program. The problem can be avoided if the { is placed at the end of the previous line and not at the beginning of the next line:
status: true
};
Below is an example of the TSQL you can use to investigate your stored procedure's performance. Best to put this in a snippet if you're using VS 2012 or greater versions of SQL Management Studio I find.
SET NOCOUNT ON
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
CHECKPOINT
GO
PRINT ' '
PRINT '-- START: ProcName --------------------------------------------------------------------------------------------------------------------------------'
PRINT ' '
SET STATISTICS IO ON
SET STATISTICS TIME ON
EXEC dbo.MyProcNameHere
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
PRINT ' '
PRINT '-- END: ProcName --------------------------------------------------------------------------------------------------------------------------------'
PRINT ' '
GO
Those of you that know me, will know I'm pretty much obsessed with keyboard shortcuts. So imagine my delight when I came across this new list today whilst reading this review of Windows 8:
Windows logo key + start typing: Search your PC
Ctrl+plus (+) or Ctrl+minus (-): Zoom in or out of many items, like apps pinned to the Start screen or in the Store
Ctrl+scroll wheel: Zoom in or out of many items, like apps pinned to the Start screen or in the Store
Windows logo key + C: Open the charms
Windows logo key + F: Open the Search charm
Windows logo key +H: Open the Share charm
Windows logo key +I: Open the Settings charm
Windows logo key + K: Open the Devices charm
Windows logo key + O: Lock the screen orientation (portrait or landscape)
Windows logo key + Z: Open commands for the app
Windows logo key + PgUp: Move the Start screen and apps to the monitor on the right (apps in the desktop won’t change monitors)
Windows logo key + PgDn: Move the Start screen and apps to the monitor on the left (apps in the desktop won’t change monitors)
Windows logo key + Shift+period (.): Snap an app to the left
Windows logo key + period (.): Snap an app to the right
Just installed the YSlow browser extension for Chrome (although versions are available for other browsers too!).
http://developer.yahoo.com/yslow/
Not bad for finding where things on your site can be improved…
Other than the usual gang of four articles, http://www.dofactory.com/Patterns/Patterns.aspx, today I chanced across this page whilst reading the asp.net mvc best practices wiki, which had some more sample code for design-patterns....
Think I've just come up with a fool proof method for creating datacontracts and associated translator logic from Entity Framework generated objects
Creating datacontracts
1. Grab the class definition from the xxxModel.cs file in xxxx.DataContracts.csproj project
2. Paste into a new file in the xxx.DataContracts namespace
3. Use Resharper to clean up the code (Ctrl +E , Ctrl + C), choose full format option
4. Suspend off Resharper (Tools, options ,Resharper)
5. Grab the primitives section and change privates to publics
6. Replace ; with { get; set; } and then replace _ with empty string
7. That's it, you now have your matching datacontract with no typos and property names matching the auto generated ones in the xxxModel.cs file.
Creating translators (little bit more involved) method
1. Use the following regex replace expression on the pasted properties from the class you created above.
Remember to paste them from the datatype definition onwards, and put them at the start of the line in the mapping class first! e.g.
String Author { get; set; }
String DMlibraryName { get; set; }
DateTime? DateCreated { get; set; }
String DocNAME { get; set; }
int? DocNumber { get; set; }
String DocPath { get; set; }
String DocTypeCode { get; set; }
String DocTypeDescription { get; set; }
Int32 IsDMType { get; set; }
Int32 IsRRAKMANType { get; set; }
int? PersonID { get; set; }
int? ProjectID { get; set; }
String ProjectLabel { get; set; }
String htmlLocation { get; set; }
Find: ^([a-zA-Z_$][a-zA-Z0-9_$?]*):b{(.*)}\{(.*)
Replace with: source.\1 = dest.\1;
Result is as follows...
source.Author = document.Author;
source.DMlibraryName = document.DMlibraryName;
source.DateCreated = document.DateCreated;
source.DocNAME = document.DocNAME;
source.DocNumber = document.DocNumber;
source.DocPath = document.DocPath;
source.DocTypeCode = document.DocTypeCode;
source.DocTypeDescription = document.DocTypeDescription;
source.IsDMType = document.IsDMType;
source.IsRRAKMANType = document.IsRRAKMANType;
source.PersonID = document.PersonID;
source.ProjectID = document.ProjectID;
source.ProjectLabel = document.ProjectLabel;
source.htmlLocation = document.htmlLocation;
(Old Creating translators (little bit more involved) method)
1. Turn resharper back on
2. Use the File Structure window
3. Stick this in Excel and do some manipulation (ask me if you're not sure how to do this)
4. Paste this back into your C# file, and do a regex replace of \t with nothing
5. That's it, translator code written
Trick is to use tasklist.exe and pipe the output through a find then check the errorlevel as follows...
tasklist /FI "IMAGENAME eq WebDev.WebServer40.exe" 2>NUL | find /I /N "WebDev.WebServer40.exe">NUL
if "%ERRORLEVEL%"=="0" GOTO START
echo. Starting web app dev server
call sw.bat
:START
It appears that JS treats dates as zero indexed on the month part when you construct them in the format new Date("yyyy", "MM", "dd")
A little known fact that has caught out a few of our project team members this week, including myself. If you annotate a class with the DataContract attribute, then you must also ensure that the members you want to have serialized are also annotated with the DataMember / EnumMember attribute as well. The ones that aren't annotated will NOT get de-serialized when the data arrives at the consumer of your service method.
Note: If you apply no DataContract annotation at all to the class, then this has the same affect of attributing every member in your class with the DataMember / EnumMember attribute.
To be fair, this is somewhat eluded to in the following Microsoft article, although the part about it being implicit if you don't apply the attribute is not entirely obvious at first...
Silverlight 5 also supports a simplified opt-out model for serialization, where these attributes can be omitted from the type and the members of the type to be serialized. The data contract is implicit in the sense that the visibility modifiers (public, private) used by the type determines whether it and its members are included in the data contract. In this model, the name of a member is used to identify it in the serialized representation. A new IgnoreDataMemberAttribute attribute can be used to opt-out a member that is public when required.
Stick this in a batch file in your %systemroot%, then you can run it from anywhere
tskill devenv
REM 3 second wait for the macro question to come up
ping -n 3 localhost >nul
tskill vsaenv10
tskill vsmsvr10
In case any of you use the breakpoint window for setting bookmarks in code as well, then this will be very useful to you in VS2010, if you were used to VS2008, just putting the latest break point at the end of the list like I was...
There are many ways to do this, using http://yousendit.com for example, but the best that I have come across to date is definitely https://www.wetransfer.com/
This requires no registration / login process, and you can send up to 2gb at a time, with no limit on the amount of times you can do this.
Follow these steps again to get windows live messenger working again when one of your contacts has switched to the new outlook.com or hotmail.co.uk domain.
Start, Run..., tsKill msnmsgr
Then log on via Hotmail.com on the web and sign into web messenger, then delete all references to your friend from your Hotmail messenger contacts, then enter this in your hosts file
Start, run… , notepad %systemroot%\system32\drivers\etc\hosts
Enter this…
127.0.0.1 messenger.hotmail.com
Then delete this folder here
C:\Users\MyUserName\AppData\Local\Microsoft\Windows Live\Contacts
Then restart msnmgr.exe - C:\Program Files (x86)\Windows Live\Messenger\msnmsgr.exe
Now add back your friend that you removed from your contacts using their new sign in address
Excellent article just pointed out to me by one of my colleagues at work today...
I have copied the article here below, just in case it ever gets removed from that site...
By Justin James
April 20, 2012, 4:01 PM PDT
Takeaway: When you combine project management pitfalls with software development challenges, you have a recipe for some big (but often preventable) problems.
Project management is never an exact science, but when you combine it with the vagaries of software development, you have a recipe for disaster. I have seen a fair number of common mistakes that project managers make when working with software development projects. Some of these mistakes are not exclusive to software development, but they are especially prevalent and damaging in that context.
Fred Brooks illustrated a common project management mistake with his famous statement that just because one woman can have a baby in nine months does not mean that nine women can have a baby in one month. And we still see this come up time and time again — the idea that throwing more people at a problem can make it be fixed quicker. Sadly, this is just not true.
Every person you add to a project adds friction to the project as well – things like the time needed to bring them up to speed or coordinate their work with other people. In fact, my experience has been that there is a tipping point where adding people actually slows the work down more than it speeds things up, especially for the first few months. And there are many tasks that just can’t be split up to be done by many people or teams at once. They simply have to be done “one foot in front of the other.”
Managers need metrics for variety of reasons: measuring “success” or status, performance reviews and analysis, and so on. The mistake I see too often is that the easier it is to collect a metric, the more likely that it’s not measuring anything useful. Of course, the easiest metrics to collect or understand are also the most likely to be used. Let’s take “bug tickets” as an example.
It is easy to count how many tickets get entered. But that is not a good measure of quality, because how many of those tickets are user error or truly “features”? So managers often look to the next level of metric: ticket resolution rate (tickets closed per day or week or iteration or whatever). If you have ever dealt with a help desk that constantly closes tickets for things that aren’t actually fixed, causing a proliferation of tickets, you know what it’s like dealing with an organization driven by this metric!
Instead of actually getting work done or helping the user (for example, leaving tickets open until the user accepts the resolution), the organization exists solely to open as many tickets as possible and then close them as quickly as possible, so it can get its resolution rate up. A better number would be the hardest to measure: ratio of true “bug tickets” created in relationship to features deployed, changes made, or something similar. Needless to say, that is not an easy number to understand or to collect and report on. The result is that organizations choose to make decisions based on the wrong metrics rather than the right ones, due to convenience.
A common problem I see with certain project management methodologies is that they like to play “just so stories” with timelines and time estimates. Project manager who honestly think they know what pieces of functionality any given developer will be working on more than a month or two out (unless it is a very large, broad piece of functionality) are likely to be disappointed and mistaken. Software development is just too unpredictable. Even if you can prevent or account for all the usual things that alter timelines and priorities, there is still little guarantee that things will take the time you think they will.
Another typical issue with time estimates involves not breaking tasks down into small enough pieces. If I’m told that a piece of work will take one week, I’ll ask where exactly that number is coming from. Unless someone has analysed all the minor pieces of work in the whole, a “one-week” time estimate is nothing but pure conjecture and should be disregarded.
How many times have you seen a deadline blown because it was established without accounting for a critical task like testing? That is another reason why you cannot and should not ever accept a task on a timeline that is not broken down into its component tasks. There is a chance that the estimate omits something important.
It is important to keep everyone in the loop on project status, but it is easy to forget to do it. This is where a lot of the mistrust between IT and the business team comes from: The business does not feel like it has a good handle on what’s happening with its projects. And the more it feels left in the dark, the more likely it is to start trying to micromanage or force things to happen the way it feels it should be done. You can mitigate this problem by letting people know where things stand, both on a regular basis and when milestones are accomplished or the status changes.
There is often a wide gap between the priorities of projects within the development organization, the priority of the project in the view of the overall business, and the priority of the project in the eyes of the requester. A common issue is that a “high priority” project for one department is not viewed as important by the business because it does not generate revenues, and so the developers also downgrade it. Everyone needs to be on the same page with priorities, and a large part of that is to ensure that business units are not evaluated on the basis of projects that the overall business considers lower priority.
When the development team feels overwhelmed, one of the natural reactions is to establish a lot of process to slow things down. I have worked at places where even the most simple of changes required a change request form to be filled out (on paper, of course), in triplicate, physically disseminated, agreed upon, cross-signed by managers, and after all of that, there was still a 45-day minimum time until the work was to be done! Needless to say, this organization was not seen as a “partner” or an “important lever for getting work done” in the business, they were seen as a cost center and treated as such. The wall of process is typically a stopgap measure to deal with deeper issues in the process or company’s culture, and while it is easier to put up the wall than to deal with those issues (and in some companies, the issues are irreconcilable), the wall of process is counterproductive and leads to a hostile environment.
When adding people to a project, it is tempting to assume that they can hit the ground running. No one hits the ground running in the world of software development, and those who say they do are mistaken. Every project has an acclimation period, and the farther along the project is, the longer that acclimation period is — there is more and more code to understand and get a handle on. Failing to take this into account will get you into hot water. It may take only a few days or weeks for a developer to come into the project at the beginning, but it could take months for a developer to be fully productive when added to the project long after it has started
This is another “skill” (like “hitting the ground running”) that people think they have, but they really do not. The more you ask people to multi-task, the worse their work will be and the longer it will take. This applies to multi-tasking at the minute-to-minute level (juggling emails, phone calls, actual work, etc.) as well as the hour-to-hour or day-to-day level (handling multiple projects). The more you demand from people, the more the wheels fall off. To make it even worse, multi-tasking not only is likely to mangle the work, but it grinds people up and sends them looking for another job eventually… forcing you to bring in new people in the middle of a project and causing even more issues.
Had completely forgotten about these handy little tools that helps you figure out what your team members are up to in source control…
http://msdn.microsoft.com/en-us/vstudio/bb980963.aspx
This one, http://visualstudiogallery.msdn.microsoft.com/c255a1e4-04ba-4f68-8f4e-cd473d6b971f, for example allows you to see your team members in team explorer, and run queries against them. Didn't even realise there was a status search built into source control until I found this option this morning.
Just found this article on stack overflow about why our WPF icons appear blurry.
http://stackoverflow.com/questions/5645274/image-in-wpf-getting-blurry
Turns out you can fix this by adding the following attached properties to your images
<Image Source="/LoginPanel;component/Icons/icoLogin.ico"
RenderOptions.BitmapScalingMode="NearestNeighbor"
RenderOptions.EdgeMode="Aliased"/>
Use this handy utility next time you have to install a machine with base software. Aggregates all installers into one handy little setup...
These are the client side tools that have just been mentioned for developing responsive web applications for mobile platform today on http://live.aspconf.net/
If you're thinking of building single page applications in future, then you will need to learn the following frameworks for sure..
SET NOCOUNT ON
GO
PRINT 'Checking for the existence of this procedure'
IF (SELECT OBJECT_ID('sp_generate_inserts','P')) IS NOT NULL --means, the procedure already exists
BEGIN
PRINT 'Procedure already exists. So, dropping it'
DROP PROC sp_generate_inserts
END
GO
CREATE PROC sp_generate_inserts
(
@table_name varchar(776), -- The table/view for which the INSERT statements will be generated using the existing data
@target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted
@include_column_list bit = 1, -- Use this parameter to include/ommit column list in the generated INSERT statement
@from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE)
@include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement
@debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
@owner varchar(64) = NULL, -- Use this parameter if you are not the owner of the table
@ommit_images bit = 0, -- Use this parameter to generate INSERT statements by omitting the 'image' columns
@ommit_identity bit = 0, -- Use this parameter to ommit the identity columns
@top int = NULL, -- Use this parameter to generate INSERT statements only for the TOP n rows
@cols_to_include varchar(8000) = NULL, -- List of columns to be included in the INSERT statement
@cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the INSERT statement
@disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the INSERT statements
@ommit_computed_cols bit = 0 -- When 1, computed columns will not be included in the INSERT statement
)
AS
BEGIN
/***********************************************************************************************************
Procedure: sp_generate_inserts (Build 22)
(Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.)
Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some other location.
This procedure is also useful to create a database setup, where in you can
script your data along with your table definitions.
Written by: Narayana Vyas Kondreddi
Acknowledgements:
Divya Kalra -- For beta testing
Mark Charsley -- For reporting a problem with scripting uniqueidentifier columns with NULL values
Artur Zeygman -- For helping me simplify a bit of code for handling non-dbo owned tables
Joris Laperre -- For reporting a regression bug in handling text/ntext columns
Tested on: SQL Server 7.0 and SQL Server 2000 and SQL Server 2005
Date created: January 17th 2001 21:52 GMT
Date modified: May 1st 2002 19:50 GMT
Email: vyaskn@hotmail.com
NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL Server 2000's sql_variant data types
Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results
IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed
you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts
like nchar and nvarchar
ALSO NOTE THAT THIS PROCEDURE IS NOT UPDATED TO WORK WITH NEW DATA TYPES INTRODUCED IN SQL SERVER 2005 / YUKON
Example 1: To generate INSERT statements for table 'titles':
EXEC sp_generate_inserts 'titles'
Example 2: To ommit the column list in the INSERT statement: (Column list is included by default)
IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below,
to avoid erroneous results
EXEC sp_generate_inserts 'titles', @include_column_list = 0
Example 3: To generate INSERT statements for 'titlesCopy' table from 'titles' table:
EXEC sp_generate_inserts 'titles', 'titlesCopy'
Example 4: To generate INSERT statements for 'titles' table for only those titles
which contain the word 'Computer' in them:
NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter
EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"
Example 5: To specify that you want to include TIMESTAMP column's data as well in the INSERT statement:
(By default TIMESTAMP column's data is not scripted)
EXEC sp_generate_inserts 'titles', @include_timestamp = 1
Example 6: To print the debug information:
EXEC sp_generate_inserts 'titles', @debug_mode = 1
Example 7: If you are not the owner of the table, use @owner parameter to specify the owner name
To use this option, you must have SELECT permissions on that table
EXEC sp_generate_inserts Nickstable, @owner = 'Nick'
Example 8: To generate INSERT statements for the rest of the columns excluding images
When using this otion, DO NOT set @include_column_list parameter to 0.
EXEC sp_generate_inserts imgtable, @ommit_images = 1
Example 9: To generate INSERT statements excluding (ommiting) IDENTITY columns:
(By default IDENTITY columns are included in the INSERT statement)
EXEC sp_generate_inserts mytable, @ommit_identity = 1
Example 10: To generate INSERT statements for the TOP 10 rows in the table:
EXEC sp_generate_inserts mytable, @top = 10
Example 11: To generate INSERT statements with only those columns you want:
EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"
Example 12: To generate INSERT statements by omitting certain columns:
EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"
Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements:
EXEC sp_generate_inserts titles, @disable_constraints = 1
Example 14: To exclude computed columns from the INSERT statement:
EXEC sp_generate_inserts MyTable, @ommit_computed_cols = 1
***********************************************************************************************************/
SET NOCOUNT ON
--Making sure user only uses either @cols_to_include or @cols_to_exclude
IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))
BEGIN
RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)
RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified
END
--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format
IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))
BEGIN
RAISERROR('Invalid use of @cols_to_include property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'
RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property
END
IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))
BEGIN
RAISERROR('Invalid use of @cols_to_exclude property',16,1)
PRINT 'Specify column names surrounded by single quotes and separated by commas'
PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'
RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property
END
--Checking to see if the database name is specified along wih the table name
--Your database context should be local to the table for which you want to generate INSERT statements
--specifying the database name is not allowed
IF (PARSENAME(@table_name,3)) IS NOT NULL
BEGIN
RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)
RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed
END
--Checking for the existence of 'user table' or 'view'
--This procedure is not written to work on system tables
--To script the data in system tables, just create a view on the system tables and script the view instead
IF @owner IS NULL
BEGIN
IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL))
BEGIN
RAISERROR('User table or view not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table or view.'
RETURN -1 --Failure. Reason: There is no user table or view with this name
END
END
ELSE
BEGIN
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @owner)
BEGIN
RAISERROR('User table or view not found.',16,1)
PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.'
PRINT 'Make sure you have SELECT permission on that table or view.'
RETURN -1 --Failure. Reason: There is no user table or view with this name
END
END
--Variable declarations
DECLARE @Column_ID int,
@Column_List varchar(8000),
@Column_Name varchar(128),
@Start_Insert varchar(786),
@Data_Type varchar(128),
@Actual_Values varchar(8000), --This is the string that will be finally executed to generate INSERT statements
@IDN varchar(128) --Will contain the IDENTITY column's name in the table
--Variable Initialization
SET @IDN = ''
SET @Column_ID = 0
SET @Column_Name = ''
SET @Column_List = ''
SET @Actual_Values = ''
IF @owner IS NULL
BEGIN
SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
END
ELSE
BEGIN
SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'
END
--To get the first column's ID
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)
--Loop through all the columns of the table, to get the column names and their data types
WHILE @Column_ID IS NOT NULL
BEGIN
SELECT @Column_Name = QUOTENAME(COLUMN_NAME),
@Data_Type = DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE ORDINAL_POSITION = @Column_ID AND
TABLE_NAME = @table_name AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)
IF @cols_to_include IS NOT NULL --Selecting only user specified columns
BEGIN
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0
BEGIN
GOTO SKIP_LOOP
END
END
IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns
BEGIN
IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0
BEGIN
GOTO SKIP_LOOP
END
END
--Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column
IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1
BEGIN
IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column
SET @IDN = @Column_Name
ELSE
GOTO SKIP_LOOP
END
--Making sure whether to output computed columns or not
IF @ommit_computed_cols = 1
BEGIN
IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1
BEGIN
GOTO SKIP_LOOP
END
END
--Tables with columns of IMAGE data type are not supported for obvious reasons
IF(@Data_Type in ('image'))
BEGIN
IF (@ommit_images = 0)
BEGIN
RAISERROR('Tables with image columns are not supported.',16,1)
PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'
PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'
RETURN -1 --Failure. Reason: There is a column with image data type
END
ELSE
BEGIN
GOTO SKIP_LOOP
END
END
--Determining the data type of the column and depending on the data type, the VALUES part of
--the INSERT statement is generated. Care is taken to handle columns with NULL values. Also
--making sure, not to lose any data from flot, real, money, smallmomey, datetime columns
SET @Actual_Values = @Actual_Values +
CASE
WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')
THEN
'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ('datetime','smalldatetime')
THEN
'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'
WHEN @Data_Type IN ('uniqueidentifier')
THEN
'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ('text','ntext')
THEN
'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'
WHEN @Data_Type IN ('binary','varbinary')
THEN
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
WHEN @Data_Type IN ('timestamp','rowversion')
THEN
CASE
WHEN @include_timestamp = 0
THEN
'''DEFAULT'''
ELSE
'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'
END
WHEN @Data_Type IN ('float','real','money','smallmoney')
THEN
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ',2)' + ')),''NULL'')'
ELSE
'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + ')),''NULL'')'
END + '+' + ''',''' + ' + '
--Generating the column list for the INSERT statement
SET @Column_List = @Column_List + @Column_Name + ','
SKIP_LOOP: --The label used in GOTO
SELECT @Column_ID = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
ORDINAL_POSITION > @Column_ID AND
(@owner IS NULL OR TABLE_SCHEMA = @owner)
--Loop ends here!
END
--To get rid of the extra characters that got concatenated during the last run through the loop
SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)
SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)
IF LTRIM(@Column_List) = ''
BEGIN
RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)
RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter
END
--Forming the final string that will be executed, to output the INSERT statements
IF (@include_column_list <> 0)
BEGIN
SET @Actual_Values =
'SELECT ' +
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
'''' + RTRIM(@Start_Insert) +
' ''+' + '''(' + RTRIM(@Column_List) + '''+' + ''')''' +
' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' +
COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
END
ELSE IF (@include_column_list = 0)
BEGIN
SET @Actual_Values =
'SELECT ' +
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
'''' + RTRIM(@Start_Insert) +
' '' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' +
COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')
END
--Determining whether to ouput any debug information
IF @debug_mode =1
BEGIN
PRINT '/*****START OF DEBUG INFORMATION*****'
PRINT 'Beginning of the INSERT statement:'
PRINT @Start_Insert
PRINT ''
PRINT 'The column list:'
PRINT @Column_List
PRINT ''
PRINT 'The SELECT statement executed to generate the INSERTs'
PRINT @Actual_Values
PRINT ''
PRINT '*****END OF DEBUG INFORMATION*****/'
PRINT ''
END
PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas'
PRINT '--Build number: 22'
PRINT '--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com'
PRINT '--http://vyaskn.tripod.com'
PRINT ''
PRINT 'SET NOCOUNT ON'
PRINT ''
--Determining whether to print IDENTITY_INSERT or not
IF (@IDN <> '')
BEGIN
PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'
PRINT 'GO'
PRINT ''
END
IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
BEGIN
IF @owner IS NULL
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
END
ELSE
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'
END
PRINT 'GO'
END
PRINT ''
PRINT 'PRINT ''Inserting values into ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ''''
--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!
EXEC (@Actual_Values)
PRINT 'PRINT ''Done'''
PRINT ''
IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)
BEGIN
IF @owner IS NULL
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
END
ELSE
BEGIN
SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'
END
PRINT 'GO'
END
PRINT ''
IF (@IDN <> '')
BEGIN
PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF'
PRINT 'GO'
END
PRINT 'SET NOCOUNT OFF'
SET NOCOUNT OFF
RETURN 0 --Success. We are done!
END
GO
PRINT 'Created the procedure'
GO
--Mark procedure as system object
EXEC sys.sp_MS_marksystemobject sp_generate_inserts
GO
PRINT 'Granting EXECUTE permission on sp_generate_inserts to all users'
GRANT EXEC ON sp_generate_inserts TO public
SET NOCOUNT OFF
GO
PRINT 'Done'
Use the following powershell script to find the last users to login to a box since a given date, in this case the 21st April 2022 at 12pm un...
