SqlServerKudos

Tips and Tricks

1
kudos

Kudos

Copying large files when running SQL Server

published 5 days, 3 hours, 5 minutes ago posted by

sa 14 days, 5 hours, 44 minutes ago

Copying large files when running SQL Server

Copying large files has always been a pain on a SQL Server as it can result in the OS taking memory from SQL. This is something you don’t want to happen. In the past, and probably still, it can cause What i noticed recently on my windows 7 machine was...(read more) (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: Tips and Tricks

1
kudos

Kudos

Creating a unique constraint on a column that allows nulls

published 30 days, 4 hours, 47 minutes ago posted by

sa 39 days, 7 hours, 30 minutes ago

Creating a unique constraint on a column that allows nulls

Primary keys, unique indexes and unique constraints all enforce uniqueness. The difference between the primary key and the unique index/constraint is that the primary key does not allow any NULL values. However whilst the unique index/constraints do allow...(read more) (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: SQL Server 2005, SQL Server 2008, Tips and Tricks

1
kudos

Kudos

Report Builder 3.0

published 37 days, 5 hours, 20 minutes ago posted by

sa 46 days, 6 hours, 46 minutes ago

Report Builder 3.0 has some minor additions that make your life a better world. On that i’ve just found is the ability to know what renderer is being used. Why is that useful? Well in all the training I do on Reporting Services I always highlight the...(read more) (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: Report Builder 3.0, reporting, Tips and Tricks

1
kudos

Kudos

Controlling what appears on your tabs

published 41 days, 5 hours, 36 minutes ago posted by

sa 50 days, 8 hours, 16 minutes ago

You can control what appears on your tabs from SQL 2008 onwards. Its still not ideal, IMHO you need to have multi line tabs to show server, database, filename etc. Anyway to control what appears go to Tools | Options and then select the Tec Editor node...(read more) (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: Management Studio, Tips and Tricks

1
kudos

Kudos

What line does the error line number refer to?

published 46 days, 6 hours, 1 minute ago posted by

sa 53 days, 7 hours, 26 minutes ago

What line does the error line number refer to?

You’ve got an error and it gives you a line number Msg 50000, Level 16, State 1, Procedure TestToSeeWhatTheErrorLineNumberRefersTo, Line 14 Some Error What line does that actually refer to? Well its the line in the original batch that compiled the procedure...(read more) (more)

category: News | clicked: 1 | comment | | source: sqlblogcasts.com

tags: Tips and Tricks, TSQL

1
kudos

Kudos

Scream if you want to go faster

published 103 days, 10 hours, 15 minutes ago posted by

sa 110 days, 12 hours, 43 minutes ago

My session for 24hrs of pass on High Performance functions will be starting at 11:00 GMT thats migdnight for folks in the UK. To attend follow this link https://www.livemeeting.com/cc/8000181573/join?id=N5Q8S7&role=attend&pw=d2%28_KmN3r The rest...(read more) (more)

category: News | clicked: 1 | comment | | source: sqlblogcasts.com

tags: PASS, Performance, Query Tuning, SQL Server 2005, SQL Server 2008, Tips and Tricks

1
kudos

Kudos

Avoid SQL Injection with Parameters

published 144 days, 13 hours, 19 minutes ago posted by

sa 154 days, 12 hours, 53 minutes ago

The best way to avoid SQL Injection is with parameters. With parameters you can’t get SQL Injection. You only get SQL Injection where you are building a SQL statement by concatenating your parameter values in with your SQL statement. Annoyingly many TSQL...(read more) (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: best practice, Security, Tips and Tricks, TSQL

1
kudos

Kudos

Enabling super single user mode with SQL Server

published 155 days, 14 hours, 8 minutes ago posted by

sa 164 days, 14 hours, 38 minutes ago

Enabling super single user mode with SQL Server

I recently got an email from a fellow MVP about single user mode. It made me think about some features I had just been looking at and so I started playing. The annoyance about single user mode for SQL Server is that its not really single user, but more...(read more) (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: SQL Server 2005, SQL Server 2008, Tips and Tricks

1
kudos

Kudos

SSIS - XML Source Script

published 170 days, 15 hours, 13 minutes ago posted by

sa 179 days, 15 hours, 51 minutes ago

The XML Source in SSIS is great if you have a 1 to 1 mapping between entity and table. You can do more complex mapping but it becomes very messy and won't perform. What other options do you have? The challenge with XML processing is to not need a huge amount of memory. I remember using the early versions of Biztalk with loaded the whole document into memory to map from one document type to another. This was fine for small documents but was an absolute killer for large documents. You therefore need a... (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: c#, SQL Server 2005, SQL Server 2008, SSIS - Integration Services, Tips and Tricks, XML

1
kudos

Kudos

SSIS - The expression cannot be parsed because it contains invalid elements at the location specified

published 176 days, 15 hours, 38 minutes ago posted by

sa 185 days, 17 hours, 22 minutes ago

SSIS - The expression cannot be parsed because it contains invalid elements at the location specified

If you get the following error when trying to write an expression there is an easy solution Attempt to parse the expression "@[User::FilePath] + "\" + @[User::FileName] + ".raw"" failed. The token "." at line number "1", character number " (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: SSIS - Integration Services, Tips and Tricks

1
kudos

Kudos

SSIS - character number "81" was not recognized

published 177 days, 14 hours, 43 minutes ago posted by

sa 186 days, 17 hours, 23 minutes ago

SSIS - character number "81" was not recognized

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: SSIS - Integration Services, Tips and Tricks

1
kudos

Kudos

SSIS - Access Denied with UNC paths - The file name is a device or contains invalid characters

published 179 days, 14 hours, 51 minutes ago posted by

sa 187 days, 16 hours, 23 minutes ago

SSIS - Access Denied with UNC paths - The file name is a device or contains invalid characters

I spent another day tearing my hair out yesterday trying to resolve an issue with SSIS packages runnning in SQLAgent (not got much left at the moment, maybe I should contact the SSIS team for a wig). My situation was that I am deploying packages to a development server, and to provide isolation I was running jobs with a proxy account that only had access to the development servers. Proxies are an awesome feature and mean that you should never have to "just run the job as sysadmin". The issue I was f... (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: SSIS - Integration Services, Tips and Tricks

1
kudos

Kudos

SQL Server service accounts and SPNs

published 180 days, 14 hours, 52 minutes ago posted by

sa 187 days, 17 hours, 28 minutes ago

Service Principal Names (SPNs) are a must for kerberos authentication which is a must when using sharepoint, reporting services and sql server where you access one server that then needs to access another resource, this is called the double hop. The reason this is a complex problem is that the second hop has to be done with impersonation/delegation. For this to work there needs to be a way for the security system to make sure that the service in the middle is allowed to impersonate you, after all y... (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: kerberos, SPN, Tips and Tricks

1
kudos

Kudos

Execute Package Task Packages fail for no apparent reason

published 180 days, 14 hours, 52 minutes ago posted by

sa 188 days, 15 hours, 28 minutes ago

Execute Package Task Packages fail for no apparent reason

Much has been said about indirect configurations being the correct way to configure packages. This is definitely true with SQL 2008 as they fixed a number of issues with configuring the configuration, i.e. being able to pass int at runtime the location for the configuration settings. Unfortunately I'm not using 2008, I'm stuck with SQL 2005 and I hit a rather annoying feature. You know, one of those features, that is really small but takes ages to debug. Fortunately I have logging enabled and so it ... (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: SSIS - Integration Services, Tips and Tricks

1
kudos

Kudos

Generating surrogate keys with xquery

published 186 days, 15 hours, 18 minutes ago posted by

sa 193 days, 17 hours, 57 minutes ago

Saving a hierarchy of data from an XML document into a set of tables is a problem when the only relationship between nodes in the hierarchy is the position of the node. SQL doesn't like implied things like this. It likes things to be explicit. This means that when you save the child nodes you are stuck as to how you can get the appropriate primary key value of the parent node. With OPENXML you have metaproperties but with xquery you don't. This means you have to do some tricky SQL using row_number. ... (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: Tips and Tricks, XML

1
kudos

Kudos

Accessing system views in global stored procedures

published 187 days, 15 hours, 23 minutes ago posted by

sa 194 days, 16 hours, 53 minutes ago

Accessing system views in global stored procedures

Having utility functions that do helpful stuff is great, just look at sp_who2, sp_help, sp_helpindex as examples. What if you want to write your own. Well you can you just stick the procedure in master and prefix it with sp. You can now call the procedure from any database without calling master..procedure. The real worth of these comes when you access system views like sys.objects and sys.tables. However unfortunately these are scoped to the database of the procedure and NOT the context database. ... (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: Tips and Tricks, TSQL

1
kudos

Kudos

Time is no use for recording Time

published 216 days, 20 hours, 43 minutes ago posted by

sa 226 days, 8 hours, 8 minutes ago

I was asked by a client about the new Time datatype in SQL 2008. They wanted to store the duration of some work so they could use the nice date time functions to get the hours, minutes, seconds etc. However you can't use the Time date type for that, because it can only hold time up to midnight after that it roles around to 0. Not much use if something takes longer than 24 hrs to run. If you would like to have such a feature then vote for the reccomendation for an interval data type. https://connect.... (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: SQL Server 2008, Suggestions, Tips and Tricks

1
kudos

Kudos

XML Validation in SQL Server Intergration Services

published 219 days, 20 hours, 58 minutes ago posted by

sa 229 days, 4 hours, 47 minutes ago

XML Validation in SQL Server Intergration Services

I've been workig with XML in SQL Server Integration services this week and to say the XML task in SQL Server Integration services sucks is an understatement. Its buggy, the documentation is woefully inadequate in some cases wrong. I believe this was one of the first components written back for SQL 2005 so you can imagine it was written along time ago, XML wasn't as standardised. So what if you want to validate an XML document against a XSD Schema. Well in true SQL Server Integration services mode th... (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: SQL Server 2005, SQL Server 2008, SSIS - Integration Services, Tips and Tricks, XML

1
kudos

Kudos

Error: The type of the value being assigned to variable ?? differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object

published 220 days, 21 hours, 3 minutes ago posted by

sa 230 days, 4 hours, 52 minutes ago

Error: The type of the value being assigned to variable ?? differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object

If you get the above error and you have no idea why (i.e you haven't changed the data type of a variable) then it is likely due to a data type inference issue with SSIS. You might think that SQL is returning a value of one type when in fact it isn't its returning something else. This occurs with a number of functions in SQL, the key is that you think your SQL is returning an integer when in fact its returning a decimal or a data type that SQL Server Integration services maps to a string. Two fiuncti... (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: Bugs, SSIS - Integration Services, Tips and Tricks

1
kudos

Kudos

Deploying a database solution in Visual Studio Database Professional - Tip

published 221 days, 21 hours, 4 minutes ago posted by

sa 231 days, 52 minutes ago

Deploying a database solution in Visual Studio Database Professional - Tip

I really like the database projects in visual studio however there are a few bug bears that really annoy me. This post is about deploying a solution. When working in development i have the project set to build the delpoy script and to deploy it. Whats annoying is that I prefer to work in the schema view of the project and not the solution view and in schema view there is no context menu(that I know of) to deploy the project. So each time you want to deploy you have to go to the the solution explorer... (more)

category: News | clicked: 0 | comment | | source: sqlblogcasts.com

tags: Data Dude, Tips and Tricks, Tools

Welcome!

Tips and Tricks

Copying large files when running SQL Server

Creating a unique constraint on a column that allows nulls

Report Builder 3.0

Controlling what appears on your tabs

What line does the error line number refer to?

Scream if you want to go faster

Avoid SQL Injection with Parameters

Enabling super single user mode with SQL Server

SSIS - XML Source Script

SSIS - The expression cannot be parsed because it contains invalid elements at the location specified

SSIS - character number "81" was not recognized

SSIS - Access Denied with UNC paths - The file name is a device or contains invalid characters

SQL Server service accounts and SPNs

Execute Package Task Packages fail for no apparent reason

Generating surrogate keys with xquery

Accessing system views in global stored procedures

Time is no use for recording Time

XML Validation in SQL Server Intergration Services

Error: The type of the value being assigned to variable ?? differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object

Deploying a database solution in Visual Studio Database Professional - Tip

Open ID Login

Standard Login

Forgot Password?

Sign up