Error: The multi-part identifier could not be bound. when using a function and alias

20-Sep-1212 Leave a comment

You cannot pass parameters to functions across a JOIN.

http://stackoverflow.com/questions/4123413/the-multi-part-identifier-columnname-could-not-be-bound

Use APPLY:

SELECT ROW_NUMBER() OVER(ORDER BY x.Year DESC, x.Month) AS ID,
x.*,
fr.*
FROM (
SELECT Client,
Month(TimeStamp) As Month,
Year(TimeStamp) As Year,
Day(TimeStamp) As Day,
(dateadd(yy,(Year(TimeStamp)-1900),0)
+ dateadd(mm,Month(TimeStamp)-1,0)
+ Day(TimeStamp)-1) AS XTimeStamp,
Sum(KwTop) As KwTop,
Sum(KwHeap) as KwHeap,
Sum(KwLow) As KwLow
FROM Ori.vEnergyUnion
GROUP BY
Year(TimeStamp), Month(TimeStamp),Day(TimeStamp), Client
) x
OUTER APPLY
(
SELECT *
FROM Finance.fGetRates(x.XTimeStamp) Fr
WHERE fr.ValidFrom = x.XTimeStamp
) fr

Filed under Errors, Transact SQL Tagged with

SQL Profiler Templates

08-Aug-1212 Leave a comment

With a lot of “noise” from hits on the live data I want the ability to see what my development code is hitting for SQL. In this particular case I want to understand the SQL that Entity Framework is generating.

The default location of SQL Profiler templates is (possibly 90 or 100 or other):

C:\Users\<USERNAME>\AppData\Roaming\Microsoft\SQL Profiler\10.0\Templates\Microsoft SQL Server\100

Events to include:

RPC:Completed

Filters:

NTUserName = ‘<>’
ApplicationName Not Like ‘<>’

 

Filed under SQL Server

Get SQL table space – and store for KPI over time

06-Jul-1212 Leave a comment

See http://therightstuff.de/CommentView,guid,df930155-f60f-4f56-ab33-f1352ff091a1.aspx

For a single table

EXEC sp_spaceused ‘<schema>.<tablename>’

For database size

–See sql file for dbo.TableSpaceUsed
SET NOCOUNT ON DBCC UPDATEUSAGE(0) — DB size.
EXEC sp_spaceused– Table row counts and sizes.

For all tables

See http://codebetter.com/raymondlewallen/2005/03/25/using-sp_msforeachtable/
http://wp.me/p17IS4-ge

CREATE TABLE #t
(
[name] NVARCHAR(128)
,    [rows] CHAR(11)
,    reserved VARCHAR(18)
,     data VARCHAR(18)
,     index_size VARCHAR(18)
,    unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable ‘EXEC sp_spaceused ”?”’

–Optional, but I am now using a table I have created for storing this table size over time
DECLARE @SpoolID int SELECT @SpoolID =ISNULL((SELECTMAX(SpoolID)FROM dbo.TableSpaceUsed), 0)+ 1
–SELECT @SpoolID

INSERT INTO dbo.TableSpaceUsed (SpoolID, [name], [rows], reserved, reservedText, data, index_size, unused)
SELECT @SpoolID, name,rows,CAST(REPLACE(reserved,‘KB’,”)ASint)AS reserved, reserved AS reservedText, data, index_size, unused
FROM  #t

SELECT * FROM #t ORDER BY CAST(REPLACE(reserved,‘KB’,”)AS int) DESC— # of rows.

SELECT SUM(CAST([rows] ASint))AS [rows] FROM #t

DROP TABLE #t

Filed under SQL Server, Transact SQL

Using sp_MSforeachtable

06-Jul-1212 Leave a comment

http://codebetter.com/raymondlewallen/2005/03/25/using-sp_msforeachtable/

See usage in http://wp.me/p17IS4-gc

Filed under Transact SQL

Forms-Based Authentication with SQL Azure

29-Jun-1212 Leave a comment

 

Following is quite useful

http://msdn.microsoft.com/en-us/library/windowsazure/hh508981.aspx

I was able to get this to work without a Windows Azure Project

Worked on site. However the role manager did not wok

http://stackoverflow.com/questions/5541868/problem-with-your-selected-data-store-on-the-asp-net-website-administration-tool

Changed following and it worked
<roleManager enabled=”true”>

 

 

Filed under ASP.Net, SQL Azure

Deleting an ASP.Net azure hosted website using Entity Framework can potentially delete the linked database

27-Jun-1212 Leave a comment

I had a database on Azure that was being updated using Windows Forms. I then created an ASP.Net MVC application using EF to work against the same database. However I following a tutorial which used Code First

http://www.windowsazure.com/en-us/develop/net/tutorials/web-site-with-sql-database/

This would not publish until I used Nu-Get to:

enable-migrations

However I think I then used code first migrations on publish. However I published the website with high credentials and wanted to change the credentials to lower. Could not see how to do this in Azure, so deleted the website. At this point the database disappeared without warning.

This is why the last posts have been about SQL Azure backup. However recovered using on-site data and re-created database.

Microsoft support helped with the ASP.Net so:

enable-migrations

Check the file created, but do not check the box on publish.

Tested by deleting the Website – This then asked if you wanted to delete the linked database. Answer: No. Database stays ok.

Filed under ASP.Net, Azure, Deployment, Entity Framework, SQL Azure, Troubleshooting

How do I backup SQL Azure to a Blob Storage Account using bacpac

27-Jun-1212 Leave a comment

http://msdn.microsoft.com/en-us/library/windowsazure/hh335292

My word press blog entry

 

Filed under Azure, SQL Azure

How do I … backup by using Create Database … As Copy Of to an Azure database (to Azure)

27-Jun-1212 1 Comment

–Start logged into Azure and with master as current database
–Does work 27/06/2012
DROP DATABASE _20120627
CREATE DATABASE _20120627 AS COPY OF

–Must wait for state to be changed from Copying to ?Ready?
SELECT * FROM sys.dm_database_copies
SELECT * FROM sys.databases
SELECT name, state, state_desc from sys.databases where name = ‘_20120627’

–Test that data is there
SELECT * FROM .

Filed under Azure, SQL Azure

Azure Storage – a pre-requisite to export SQL to blob storage

27-Jun-1212 Leave a comment

A storage account needs a container before it can be used.
The container may be made in Visual Studio using a new Project > Cloud > Windows Azure Cloud Service
When this project is created with any Web Roles you create the relevant projects will also be created and linked.
When this is created make this Cloud Service Project the start-up project and run-it in Visual Studio as an administrator you may then get further, it will not start the cloud service project but the web project. This will then give relevant keys.

I found this not to run in local. Needed to publish it first.
Then in Azure Management > Hosted Services > Locate > Run from DNS

http://social.technet.microsoft.com/wiki/contents/articles/2153.windows-azure-and-sql-azure-tutorials-tutorial-3-using-windows-azure-blob-service-en-us.aspx#__Lesson1Proc1

 

To test the application in the staging environment

  1. From the portal, in the left pane, click Hosted Services.
  2. In the middle pane, expand GolferMessageBoard, and then click v1.2.0.0.
  3. In the Properties pane, click the URL in the DNS name box. The application is opened in a new browser tab or a new browser window depending on your browser configuration.
  4. Test the application by entering one or more entries.

After the application is working correctly in the staging environment, you are ready to promote it to the production environment.

To promote the application to production

  1. From the portal, in the left pane, click Hosted Services.
  2. In the middle pane, expand GolferMessageBoard, and then click v1.2.0.0.
  3. From the top menu, click Swap VIP.
  4. In the Swap VIPs dialog, click OK. Wait until the Status for the deployment changes to Ready.
  5. In the Properties pane, click the URL in the DNS name box. The application is opened in a new browser tab or a new browser window depending on your browser configuration.
  6. Test the application in the production environment by entering one or more entries.

 

Azure Export of SQL database to blob

The format of the file should be
http:// teststorage01.blob. core.windows.net/dbbackup/ebcgroup1.bacpac

I had following error:
“azure blob is not writeable … the specified container does not exist”

To create the container make sure the above has been published to Azure and run from there.

 

Filed under Azure, SQL Azure

LocalReport set-up and publish

23-Feb-1212 Leave a comment

How do I … keep reports in a sub-directory

May be set in code or in ReportViewer control

Dim report as New LocalReport()
report.ReportPath = “Reports\ReportName.rdlc”

How do I … fix Local reports that work in Visual Studio not when published

The errors you may get include:

  • An error occurred during local report processing copy
    The report definition for report ‘..\some_path_to_report\<reportname>.rdlc’ has not been specified
    Could not find file ‘C:\inetpub\wwwroot\some_path_to_report\tse.rdlc’.

When reports are added their default Build Action is Embedded Resource.
To fix this change to Content and choose a copy policy, probably Copy if Newer

How do I … use a report in a referenced project

Add the report as a “Linked” file ( Add existing item > Add as Link )(probably) at the same directory level as in the referenced project
Then on these linked files choose the necesary options as above

Filed under Errors, SQL SSRS, VB.Net, Visual Studio

← Older posts

Newer posts →