sql

Tag: sql

SQL Tips: Insert Value using Identity from Previous Insert

Ever find yourself needing to write SQL code to run on multiple tables where one of the values in a secondary INSERT statement is dependent upon the identity value from previous insert? Well, if so, this should work for you…

[code language=”sql”]DECLARE @NewApiId int;
INSERT INTO APIs (Name, Description) VALUES (‘Social Links’,’The Social Links API, available only to corporate clients, will output a report all of the clients for the client”s ClientTypeID that includes all of the recorded social media properties’);
SELECT @NewApiId = CAST(scope_identity() AS int);
INSERT INTO ApiSubscriptions (ApiId, ClientId) VALUES (@NewApiId, 666);
select * from APIs;
Select * from APISubscriptions WHERE ClientId=666;[/code]

Pretty simple, no?

Insert From Table on Remote SQL Server

Ever have the need to populate a table from data that you have in a table on a different SQL server? This is a very handy thing to do when you are trying to add data to your dev or staging SQL databases from the live server.
Previously I would have exported the data from the one server and then imported that data into the new one.
Well brother (and sister) I’m her to tell you there is a better way!
Through the magic of Linked Servers (SQL Server 2005+ I believe) it can be done. And through the use of Synonyms, you can even make it less cumbersome if it is something that you do regularly.
How does this work you ask? Well here is how I did it.

  1. Ensure that in SQL Mangler (I mean SSMS) you are connected to both servers with a user that has access to both databases.
  2. Open a query window for your target database
  3. Run the following command replacing server.domain.com with the FQDN of the source server
    exec sp_addlinkedserver [server.domain.com];
  4. Run your INSERT INTO statement as follows:
    INSERT INTO targetTableName ([Col1], [Col2], [Col3], [etc])
    SELECT [Col1], [Col2], [Col3], [etc]
    FROM [server.domain.com].databaseName.dbo.tableName
  5. If this is something you are going to be doing a lot of, you can make a Synonym of to save you some typing:
    CREATE SYNONYM nickname FOR [server.domain.com].databaseName
    Which would give you the ability to shorten your FROM selector.

Of course, your results may vary, always test on a backup database, blah, blah blah. Enjoy coders!

SQL Files Grown Out of Control?

For merchants running AspDotNetStorefront who aren’t regularly running the Monthly Maintenance routines, you might find yourself in a situation similar to one of our clients where the size of the transaction log for their SQL database had grown large enough that their site was shut down because it couldn’t grow any more.

In this case, since we couldn’t access the admin section of the site to run the monthly maintenance, I was able to run the following commands in order to clear the transaction log to the point of the last backup AND then shrink the physical size of the SQL database files. Please note that before running these commands GET A FULL BACKUP OF THE DATABASE. If you can’t do this directly from SQL Management Studio (or similar tool) you should be able to initiate an ad-hoc backup from your hosting company’s control panel.

Read more

Change Starting Order Number in ASPDNSF

From within your storefront’s “Run SQL” option under “Configuration” | “Advanced” (or from within SQL Mangler, I mean Manager, enter the following command:

dbcc checkident(OrderNumbers, reseed, XXXXXX)

Where XXXXXX is the new number you want to use as your seed.

This can come in very handy if you moving from one storefront to another to ensure a continuity of orders.

from: http://manual.aspdotnetstorefront.com/p-1335-changing-the-starting-order-number.aspx

Quickly Map Manufacturers to Store in ASPDNSF Multi-Store solution

image I’m sure you’ve been there before. You (or your customer) have gone in and added all your products and defined which products go with which stores but then decided after the fact that you want to have a manufacturer’s page (or menu) that shows all the manufacturers of products that  exist in a give store on your Multi-Store solution.

Read more

How To Extend ASPDNSF Product Name Field

On of the nice features of AspDotNetStorefront is that it supports multiple languages right out of the box. It does this by storing the multiple locale phrases as XML fragments inside the particular field. So, in the case of a product name, this means stuffing each of the multi-lingual product names into the Name field in the Product table. It also needs to be properly defined in this field with the XML code so a sample, multiple language name field could contain something similar to the following:

<ml>
    <locale name=”de-DE”>CMS-6R4</locale>
    <locale name=”en-US”>CMS-6R4</locale>
    <locale name=”fr-FR”>CMS-6R4</locale>
    <locale name=”ja-JP”>CMS-6R4</locale>
</ml>

Read more

Search and Replace in SQL TEXT or NTEXT column

In the past, when doing global search and replaces across text fields in SQL server, I’d been taking the path of least resistance and just writing ASP.NET pages to loop through and performing .Replace() on the SQL column in question.

Of course a devoted programmer, or at least one with more time on their hands, would have instead spent the time trying to find a better way to skin that cat. Well, courtesy of Google, I think I’ve found one. This article from SQLTeam.com all but handed me the answer. Since you can’t use the TSQL REPLACE function against a TEXT (or NTEXT) column the code below will work using UPDATETEXT instead:

Read more