Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

End With

Catch ex As Exception

MsgBox(ex.ToString)

End Try

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating Access Database Problem

Hi

I am trying to create an access database (vb 2005). The code sample below works fine if I create the database without specifying a username and password (values left blank). However if these are specified an exception is thrown. Any help/suggestions would be appreciated.

Try

Dim cat As Catalog = New Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;" & _

"Jet OLEDB:Engine Type=5;" & _

"User Id=user;" & _

"Password=pass;")

Console.WriteLine("Database Created Successfully")

cat = Nothing

Catch ex As Exception

Console.WriteLine("Failed to create database")

End Try

Hi,

which exception is thrown ? You should use the error information of the exception to to see what is failing during the creation. There should be a deatiled information in the properties of the exception like ex.Message. Although this is not a Access forum, feel free to come back with that, we will move the thread afterwards to the appropiate forum. For the next time, pick the forum which is more related to Access.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

The exception that is thrown is:

'Cannot start your application. The workgroup information file is missing or opened exclusively by another user'.

Originally I had posted this in the Visual Studio Express Edition (which I am using) forum. A forum Moderator moved the post to this forum. I would appreciate it if you moved the post to the appropriate forum. Alternatively let me know what the forum is and I'll close this post and add a new one in the appropriate place.

Thanks for your help.

Sean

|||Hi,

I don′t think that there is a direct way to do this in the connection string. The connection string is used for passing credentials during connections time, thus checking if the passed user exists in the database (as it does not upon creation time). You should better use the following link to figure out how to create the user after creating the database via code.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscgroupsusersappendchangepasswordmethodsexamplex.asp

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi

Thanks for your answer. I now undertand I was trying to do too much when creating the database.

I have tried to create a new group, as the example on the link you provided, does. This however is throwing an exception.

The code I am using is:

Dim cat As ADOX.Catalog

Dim cn As ADODB.Connection

cn = New ADODB.Connection

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

cat = New ADOX.Catalog

cat.ActiveConnection = cn

Try

With cat

'Create and append new group with a string.

.Groups.Append("Accounting")

End With

Catch ex As Exception

MsgBox(ex.ToString)

End Try

The exception being thrown is 'Object or provider is not capable of performing operation'. I have looked this up and followed the instructions at the following:

http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b824261

I have created a new Workgroup Information file. This made no difference and the same exception is thrown. Additionally if I try and reference the workgroup file within the connection properties I get an exception within visual studio. e.g.

With cn

.Provider = "Microsoft.Jet.OLEDB.4.0"

.Properties("Jet OLEDB:System database") = "c:\test\AppGen.mdw"

.Open("Data Source=C:\Seans VB\AppGeneratorSystem\AppGen.mdb;")

End With

It says the properties 'Item' is read only. According to the documentation this should not cause a problem if it is before when the connection is open.

I hope you can help.

Regards, Sean

For information I have the following references added:

ADODB (Microsoft ActiveX Data Objects 2.5 Library)

ADOX (Microsoft ADO Ext. 2.8 for DLL and Security)

Creating a view from a linked server

Hi
i have created a new database and a new linked server that points to an
AccessDB using an ODBC DSN.
Now inside that new sql db i have create i need to created a new view so
i open EM went to views and paste the following
select * from openquery (AccessLinkedServer,'select * from mytable')
i press run and i see the data ok .but when i try to save the view i get
the following
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: [DataDirect][ODBC dBase driver]Optional feature not
implemented.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
0x8004d00a].
*** Sent via Developersdex http://www.examnotes.net ***try creating the view via query analyzer. there are all kinds of funky
issues with em.
-oj
"tolisss" <nospam@.devdex.com> wrote in message
news:e7H8X6ZRFHA.1500@.TK2MSFTNGP09.phx.gbl...
>
> Hi
> i have created a new database and a new linked server that points to an
> AccessDB using an ODBC DSN.
> Now inside that new sql db i have create i need to created a new view so
> i open EM went to views and paste the following
> select * from openquery (AccessLinkedServer,'select * from mytable')
> i press run and i see the data ok .but when i try to save the view i get
> the following
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'MSDASQL' was unable
> to begin a distributed transaction.
> [Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
> message: [DataDirect][ODBC dBase driver]Optional feature not
> implemented.]
> [Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
> [OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
> 0x8004d00a].
>
>
>
> *** Sent via Developersdex http://www.examnotes.net ***

Creating a view from a linked server

Hi

i have created a new database and a new linked server that points to an
AccessDB using an ODBC DSN.
Now inside that new sql db i have create i need to created a new view so
i open EM went to views and paste the following

select * from openquery (AccessLinkedServer,'select * from mytable')

i press run and i see the data ok .but when i try to save the view i get
the following

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
could not be performed because the OLE DB provider 'MSDASQL' was unable
to begin a distributed transaction.
[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned
message: [DataDirect][ODBC dBase driver]Optional feature not
implemented.]
[Microsoft][ODBC SQL Server Driver][SQL Server]OLE DB error trace
[OLE/DB Provider 'MSDASQL' ITransactionJoi JoinTransaction returned
0x8004d00a].

*** Sent via Developersdex http://www.developersdex.com ***tolisss (nospam@.devdex.com) writes:
> i have created a new database and a new linked server that points to an
> AccessDB using an ODBC DSN.
> Now inside that new sql db i have create i need to created a new view so
> i open EM went to views and paste the following
> select * from openquery (AccessLinkedServer,'select * from mytable')
> i press run and i see the data ok .but when i try to save the view i get
> the following
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The operation
> could not be performed because the OLE DB provider 'MSDASQL' was unable
> to begin a distributed transaction.

Try creating the view from Query Analyzer. I seem to recall
that when you create a view from Enterprise Manager, it starts a
transaction. Later when you come to create the view itself, the OLE DB
provider cannot upgrade the transaction to a distributed one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating a Unique Index

Hi

I tried the following from the help file...

When you create or modify a unique index, you can set an option to
ignore duplicate keys. If this option is set and you attempt to create
duplicate keys by adding or updating data that affects multiple rows
(with the INSERT or UPDATE statement), the row that causes the
duplicates is not added or, in the case of an update, discarded.

For example, if you try to update "Smith" to "Jones" in a table where
"Jones" already exists, you end up with one "Jones" and no "Smith" in
the resulting table. The original "Smith" row is lost because an
UPDATE statement is actually a DELETE followed by an INSERT. "Smith"
was deleted and the attempt to insert an additional "Jones" failed.
The whole transaction cannot be rolled back because the purpose of
this option is to allow a transaction in spite of the presence of
duplicates.

But when I did it the original "Smith" row was not lost.

I am doing something wrong or is the help file incorrect.

DanThose paragraphs are referring to the IGNORE_DUP_KEYS option which is not
the default when creating an index. Did you specify the IGNORE_DUP_KEYS
option on your CREATE INDEX statement?

Why do you want to ignore duplicate keys in this way? Typically, it would be
better to put the code to ignore duplicates in your INSERT or UPDATE
statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
IGNORE_DUP_KEYS option is a little strange and very non-standard and
non-relational as this article explains.

--
David Portas
----
Please reply only to the newsgroup
--|||Hi

"Did you specify the IGNORE_DUP_KEYS"

Yes I did.

The issue I have is using a update statement with a table that has
IGNORE_DUP_KEYS index as the help file says --

"if you try to update "Smith" to "Jones" in a table where "Jones"
already exists, you end up with one "Jones" and no "Smith" in the
resulting table. The original "Smith" row is lost because an UPDATE
statement is actually a DELETE followed by an INSERT"

But when I try this, the original "Smith" row is not lost...

So am I doing something wrong or is the help file wrong.

Could you give it a try?

Thanks

Dan

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<lI-dnYK39q4-Y1yi4p2dnA@.giganews.com>...
> Those paragraphs are referring to the IGNORE_DUP_KEYS option which is not
> the default when creating an index. Did you specify the IGNORE_DUP_KEYS
> option on your CREATE INDEX statement?
> Why do you want to ignore duplicate keys in this way? Typically, it would be
> better to put the code to ignore duplicates in your INSERT or UPDATE
> statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
> IGNORE_DUP_KEYS option is a little strange and very non-standard and
> non-relational as this article explains.|||You're right. The UPDATE statement described should produce an error
("Cannot insert duplicate key"). The RTM version of Books Online is wrong
and that page has been changed in the latest version:

http://msdn.microsoft.com/library/e...uniqueindex.asp

--
David Portas
----
Please reply only to the newsgroup
--

Creating a System.Management assembly in order for my own Assembly to work?

Hi

I am a bit paranoid about what I just did to my SQL Server 2005 with this CLR experiment.

I created a Class Lib in C# called inLineLib that has a class Queue which represents an object with an ID field.

in another separate namespace called inLineCLRsql, I created a class called test which will hold the function to be accessed from DB, I referenced and created an instances of the Queue class, and retrieve it's ID in a function called PrintMessage.

namespace inlineCLRsql{

public static class test{

public static void PrintMessage(){

inLineLib.Queue q = new inLineLib.Queue();

int i = q.queueId ;

Microsoft.SqlServer.Server.SqlContext.Pipe.Send(i.ToString());

}

}

}

to access this from the db, I attempted to create an assembley referencing inLineCLRsql.dll. This didn't work as it complained about inLineLib assembly not existing in the db. I then attempted to create an assembley for inLineLib but it barfed saying System.Management assembly not created.

so what I did is (and this is where I need to know if I just ruined sql server or not):

1- ALTER DATABASE myDB SET TRUSTWORTHY ON;.

2- CREATE ASSEMBLY SystemManagement

FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'

WITH PERMISSION_SET = UNSAFE

3- CREATE ASSEMBLY inLineLibMaster

FROM 'D:\inLine\Server\inLineLib\bin\Debug\inLineLib.dll'

WITH PERMISSION_SET = unsafe

4- and finally

CREATE ASSEMBLY inLineLib

FROM 'D:\inLine\Server\CLRSQL\inlineCLRsql\bin\Debug\inlineCLRsql.dll'

WITH PERMISSION_SET = SAFE

Everything works after those steps (which took some trial and error). I can create a sproc like:

CREATE PROC sp_test AS

EXTERNAL NAME inLineLib.[inlineCLRsql.test].PrintMessage

and it returns the Queue ID

Is there anything unadvisable about the steps above?

Thanks for your help

M

System.Management is not supported by CLR integration in SQL Server 2005. What this means is that it has not been tested for reliability and security to work perfectly under a hosted environment. Some (or all) parts of this assembly might work just fine and some (or none) might not work reliably under conditions like memory pressure, stack overflow, stress etc. You need to test your scenario well (for reliablilty and security) and ensure that it works for you. The list of supported .NET framework assemblies is following:

CustomMarshalers

Microsoft.VisualBasic

Microsoft.VisualC

mscorlib

System

System.Configuration

System.Data

System.Data.OracleClient

System.Data.SqlXml

System.Deployment

System.Security

System.Transactions

System.Web.Services

System.Xml

Creating a publication

hi
I was following the walkthrough "Creating a Mobile Application with SQL Server Mobile" and when I got to the point where you create a "local publication" I couldn't find the link "Local Publication" in my Object Explorer.

I read all the help in books online however id did not tell me how to bring that link there.

I did install the replication component using the CD installation. I have SQL Server 2005 Standard Edition and Visual Studio 2005

I also found the help "Using the Publication Wizard to Create a Publication" but did not know where to locate or start the wizard.

any help will be appreciated.

1. Run sqlwb.exe to launch SQL Management Studio.

2. Make a connection to the SQL server you have installed. So it is shown as the root node of the tree view in the Object Explorer.

3. Look for Replication\Local Publications alone the tree view hierarchy.

4. Right click on the "Local Publications" to show the context menu - you can select "New Publication ...".

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights.

|||hi
thanks for your reply.
the problem is there is no "Local Publication" folder. The only thing is "Local Subscription"

how can I add that folder?
|||

I suspect you can add that folder. In general SQL 2005 Standard SKU should be able to do publication. Please check one more time that you do have Standard SKU not SQL Express SKU. You can get this information as "select @.@.version".

Thanks.

Creating a Package of dtsx packages

Hi

I am trying to build a package that is comprised of 100+ dtsx packages but cannot seem to get it to work. I have created a new connection where the connectionmanagertype = file and the file path is equal to the folder in which my dtsx files are located. I (location = fileSystem). No matter what I do I get an access denied error that shows the folder location but no package. I manually typed the name of the package in the PackageName property and have pasted in the PackageID in the appropriate property as well but I don't see anything in the PackageNameReadOnly. I have read the MSDN information but I don't see a step by step way to build a package of packages against which I can compare. Can anyone set me straight?

Thanks.

Robin

You can't point a connection to a folder; it has to point to a package (.dtsx file).|||

Hi Cheese,

You can use Execute Package Tasks to do the trick. Here's how I build these:

1. Create a new SSIS package.

2. On the Control Flow, drag an Execute Package Task from the toolbox.

3. Double-click it to open the editor.

4. On the General page, give it a descriptive name.

5. On the Package page, click the COnnection dropdown and select <New Connection...>

6. Select Existing File for Usage Type and navigate to the file.

7. If the package is password protected, click the ellipsis (sp?) in the Password textbox and enter it (and confirm).

8. Click OK to exit the editor.

That should be enough to run a package.

Hope this helps,

Andy

|||Yep, I assumed Robin was trying to use the Execute Package tasks... If not, then Andy is spot on.|||

Hi

What I was actually trying to do was execute 115 packages (children) from a single package (parent) where I only used one connection manager. I thought that if I use the existing folder option I shouldn't have to create a connection manager for each one. I can do this writing code in an execute process task and the .net framework but that was more effort (troubleshooting properties) than it was worth. To me it would seem that I should be able to define a directory and recursively execute each *.dtsx package in a looping fashion in a simpler fashion. So far the only way I have gotten this to work is to 115 excecute package tasks with 115 connection managers (1 to 1 relationship of course) and then execute each one upon completion and out of process. This while successful seems to be a very poor way of doing things.

Thanks,

Robin

|||

Do you need to execute those packages serially or in parallel? If you need them to run in a serial fashion; you may use a ForEach loop container and 1 execute package task/Connection manager.

But if you need them to run in parallel or if you need to create precedence constraint between packages; I don't see a better way than having 115 execute package tasks and connection managers.

Notice that is you use Execute system task; the task will return succesfull right after sending the command; so the master package will not know whether the called package fail or not.

|||

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible. My solution right now is to have a connection manager for each source file (all 115 of them). This seems to be a really stupid thing to do.

Thanks,

Robin

|||

Cheese Bread wrote:

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible.

This sounds like the best approach for your scenario and is what I would have done. Why didn't it work?

-Jamie

|||

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

|||

Cheese Bread wrote:

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

Use a foreach loop to spin through your specific folder looking for *.dtsx files. Then, inside that foreach loop, you have one execute package task. Using the expressions feature of that task, you can set the Connection property to the variable populated by the foreach loop.

Creating a Package of dtsx packages

Hi

I am trying to build a package that is comprised of 100+ dtsx packages but cannot seem to get it to work. I have created a new connection where the connectionmanagertype = file and the file path is equal to the folder in which my dtsx files are located. I (location = fileSystem). No matter what I do I get an access denied error that shows the folder location but no package. I manually typed the name of the package in the PackageName property and have pasted in the PackageID in the appropriate property as well but I don't see anything in the PackageNameReadOnly. I have read the MSDN information but I don't see a step by step way to build a package of packages against which I can compare. Can anyone set me straight?

Thanks.

Robin

You can't point a connection to a folder; it has to point to a package (.dtsx file).|||

Hi Cheese,

You can use Execute Package Tasks to do the trick. Here's how I build these:

1. Create a new SSIS package.

2. On the Control Flow, drag an Execute Package Task from the toolbox.

3. Double-click it to open the editor.

4. On the General page, give it a descriptive name.

5. On the Package page, click the COnnection dropdown and select <New Connection...>

6. Select Existing File for Usage Type and navigate to the file.

7. If the package is password protected, click the ellipsis (sp?) in the Password textbox and enter it (and confirm).

8. Click OK to exit the editor.

That should be enough to run a package.

Hope this helps,

Andy

|||Yep, I assumed Robin was trying to use the Execute Package tasks... If not, then Andy is spot on.|||

Hi

What I was actually trying to do was execute 115 packages (children) from a single package (parent) where I only used one connection manager. I thought that if I use the existing folder option I shouldn't have to create a connection manager for each one. I can do this writing code in an execute process task and the .net framework but that was more effort (troubleshooting properties) than it was worth. To me it would seem that I should be able to define a directory and recursively execute each *.dtsx package in a looping fashion in a simpler fashion. So far the only way I have gotten this to work is to 115 excecute package tasks with 115 connection managers (1 to 1 relationship of course) and then execute each one upon completion and out of process. This while successful seems to be a very poor way of doing things.

Thanks,

Robin

|||

Do you need to execute those packages serially or in parallel? If you need them to run in a serial fashion; you may use a ForEach loop container and 1 execute package task/Connection manager.

But if you need them to run in parallel or if you need to create precedence constraint between packages; I don't see a better way than having 115 execute package tasks and connection managers.

Notice that is you use Execute system task; the task will return succesfull right after sending the command; so the master package will not know whether the called package fail or not.

|||

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible. My solution right now is to have a connection manager for each source file (all 115 of them). This seems to be a really stupid thing to do.

Thanks,

Robin

|||

Cheese Bread wrote:

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible.

This sounds like the best approach for your scenario and is what I would have done. Why didn't it work?

-Jamie

|||

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

|||

Cheese Bread wrote:

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

Use a foreach loop to spin through your specific folder looking for *.dtsx files. Then, inside that foreach loop, you have one execute package task. Using the expressions feature of that task, you can set the Connection property to the variable populated by the foreach loop.

Creating a Package of dtsx packages

Hi

I am trying to build a package that is comprised of 100+ dtsx packages but cannot seem to get it to work. I have created a new connection where the connectionmanagertype = file and the file path is equal to the folder in which my dtsx files are located. I (location = fileSystem). No matter what I do I get an access denied error that shows the folder location but no package. I manually typed the name of the package in the PackageName property and have pasted in the PackageID in the appropriate property as well but I don't see anything in the PackageNameReadOnly. I have read the MSDN information but I don't see a step by step way to build a package of packages against which I can compare. Can anyone set me straight?

Thanks.

Robin

You can't point a connection to a folder; it has to point to a package (.dtsx file).|||

Hi Cheese,

You can use Execute Package Tasks to do the trick. Here's how I build these:

1. Create a new SSIS package.

2. On the Control Flow, drag an Execute Package Task from the toolbox.

3. Double-click it to open the editor.

4. On the General page, give it a descriptive name.

5. On the Package page, click the COnnection dropdown and select <New Connection...>

6. Select Existing File for Usage Type and navigate to the file.

7. If the package is password protected, click the ellipsis (sp?) in the Password textbox and enter it (and confirm).

8. Click OK to exit the editor.

That should be enough to run a package.

Hope this helps,

Andy

|||Yep, I assumed Robin was trying to use the Execute Package tasks... If not, then Andy is spot on.|||

Hi

What I was actually trying to do was execute 115 packages (children) from a single package (parent) where I only used one connection manager. I thought that if I use the existing folder option I shouldn't have to create a connection manager for each one. I can do this writing code in an execute process task and the .net framework but that was more effort (troubleshooting properties) than it was worth. To me it would seem that I should be able to define a directory and recursively execute each *.dtsx package in a looping fashion in a simpler fashion. So far the only way I have gotten this to work is to 115 excecute package tasks with 115 connection managers (1 to 1 relationship of course) and then execute each one upon completion and out of process. This while successful seems to be a very poor way of doing things.

Thanks,

Robin

|||

Do you need to execute those packages serially or in parallel? If you need them to run in a serial fashion; you may use a ForEach loop container and 1 execute package task/Connection manager.

But if you need them to run in parallel or if you need to create precedence constraint between packages; I don't see a better way than having 115 execute package tasks and connection managers.

Notice that is you use Execute system task; the task will return succesfull right after sending the command; so the master package will not know whether the called package fail or not.

|||

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible. My solution right now is to have a connection manager for each source file (all 115 of them). This seems to be a really stupid thing to do.

Thanks,

Robin

|||

Cheese Bread wrote:

My goal is/was to execute them sequentially and independent of the status of the previous step. Each dtsx I believe shouldn't require its own connection (at least in my mind) since they all reside in the same directory folder. I just wanted to iterate through a folder running all dtsx packages in the simplest manner possible.

This sounds like the best approach for your scenario and is what I would have done. Why didn't it work?

-Jamie

|||

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

|||

Cheese Bread wrote:

My theory always failed because if I configured the connection to the folder level, it never found the individual dtsx files. I always got an error basically telling me it couldn't find the package. If you create a connection at the file level for each dtsx using the interface (and not creating a loop in .Net code) you get a direct connection to the file itself and the settings are created for you. This will run fine. If you just define the folder through the interface there is no defined dtsx file (which is good) but it always errors. It is as if the folder connection is for output only. To recreate the problem on your machine create two simple dtsx packages and store them in the file system. Then create a new package and try to run the two previously created ones. If you create two connections you are fine but if you create one connection set to the folder it will fail.

Thanks,

Robin

Use a foreach loop to spin through your specific folder looking for *.dtsx files. Then, inside that foreach loop, you have one execute package task. Using the expressions feature of that task, you can set the Connection property to the variable populated by the foreach loop.

Creating a new variable with SQL Server Analysis Services

Hi!

I have just started working with SQL Server Analysis Services and I have already expierenced some problems:
I am working with the Adventure Works data and I want to create a new variable (Customer Value) out of the following data:

"Customer ID" and "Order Number"

I.e. a customer with the "customer ID" 00001 has ordered two products (so two order numbers are linked to this customer id) --> the new variable should identify the customer as a "C" customer.

therefore -->

"D" customer value: 0 orders
"C" customer value: 1-2 orders
"B" customer value: 3-4 orders
"A" customer value: >5 orders

Do you have any ideas to solve this problem?

Thanks

Cemens

Hello Cemens,

Do you want to create another attribute for the dimension Customers? Then I would suggest to create a named calculation in the DataSourceView (DSV) of your project with SQL

Creating a loop

Hi

I need to create an SQL table and automatically populate it with 100,000 records (just one column).

How can I achieve this? The create table part is straight forward enough but how can I get all those rows in there using a single script?

I imagine the statement will require Loop and While.

This is for testing purposes.declare @.i int
set @.i=100000
while @.i>0
begin
insert into table_name values (@.i)
set @.i=@.i-1
end|||Much appreciated!
I'm running this against a table I've already created and I'm getting..
"Server: Msg 213, Level 16, State 4, Line 5
Insert Error: Column name or number of supplied values does not match table definition." :confused:|||can u paste the ddl for the table? how many columns does the table have?
replace the insert with:
insert into table_name (column_name) values (@.i)|||No worries - I got it to work in the end - I created a new table with just one column and everything is fine.

Thanks for your help amigo/amiga..|||hi Harshal
I'm hoping you can help with a problem leading on from this. The purpose of this exercise was to measure how long two scenarios take to create the tables and insert records.

Scenario A : Stand alone desktop
Scenario B : Server with dual processor Xeon

The database on each is identical - yet the Desktop took 01:07 to insert compared to the server's 06:48 !! Nearly 7 minutes!

Any ideas what could be causing this??

Cheers

Samsara

Creating a DB by restore - problems

Hi:

I have a file that I have been told is a SQL Server backup from a
server somewhere. The file is about 200MB in size

I am trying to create the database on my local server using RESTORE. I
created the backup device, associated it with a backup name etc.,
copied the file into the backup dir.

When I run the RESTORE command, Query Analyzer tells me the database
needs 31 GB of space and the RESTORE aborts. I've tried this several
times, get the same result every time.

Anybody ever seen anything like this? Is there another way to create a
DB in a server using a backup file?

I am running SqlServer 2000, Developer edition on a machine running
Windows Server 2003 OS.

I would appreciate any help/suggestions.

WazA restored database is the same size as the original database so you'll need
that amount of free space for the restore. You can determine the amount of
space required with RESTORE FILELISTONLY. For example:

RESTORE FILELISTONLY
FROM DISK='C:\MyBackupFile.bak'

Note that the backup file may be considerably smaller since unused data
pages are not backed up.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<EggsAckley@.Last.com> wrote in message
news:mbt9915ei2t78eueqcmudmfdaomv68oujv@.4ax.com...
> Hi:
> I have a file that I have been told is a SQL Server backup from a
> server somewhere. The file is about 200MB in size
> I am trying to create the database on my local server using RESTORE. I
> created the backup device, associated it with a backup name etc.,
> copied the file into the backup dir.
> When I run the RESTORE command, Query Analyzer tells me the database
> needs 31 GB of space and the RESTORE aborts. I've tried this several
> times, get the same result every time.
> Anybody ever seen anything like this? Is there another way to create a
> DB in a server using a backup file?
> I am running SqlServer 2000, Developer edition on a machine running
> Windows Server 2003 OS.
> I would appreciate any help/suggestions.
> Waz|||On Wed, 25 May 2005 23:55:55 GMT, "Dan Guzman"
<guzmanda@.nospam-online.sbcglobal.net> wrote:

>A restored database is the same size as the original database so you'll need
>that amount of free space for the restore. You can determine the amount of
>space required with RESTORE FILELISTONLY. For example:
>RESTORE FILELISTONLY
>FROM DISK='C:\MyBackupFile.bak'
>Note that the backup file may be considerably smaller since unused data
>pages are not backed up.

Dan:

Thanks for your response. RESTORE FILELISTONLY says the data file
needs 1.2 gig but the log file needs 31 gig. How can I restore the
data without the log, or can I?

Thanks, Waz|||<EggsAckley@.Last.com> wrote in message
news:mbt9915ei2t78eueqcmudmfdaomv68oujv@.4ax.com...
> Hi:
> I have a file that I have been told is a SQL Server backup from a
> server somewhere. The file is about 200MB in size
> I am trying to create the database on my local server using RESTORE. I
> created the backup device, associated it with a backup name etc.,
> copied the file into the backup dir.
> When I run the RESTORE command, Query Analyzer tells me the database
> needs 31 GB of space and the RESTORE aborts. I've tried this several
> times, get the same result every time.
> Anybody ever seen anything like this? Is there another way to create a
> DB in a server using a backup file?

Sounds like they may have had a huge database that only contained 200 MB of
data.

Other than havnig 31 GB free, not sure what you can do.

Can you post the results of a RESTORE FILEHEADERSONLY command?

> I am running SqlServer 2000, Developer edition on a machine running
> Windows Server 2003 OS.
> I would appreciate any help/suggestions.
> Waz|||On Thu, 26 May 2005 01:21:58 GMT, "Greg D. Moore \(Strider\)"
<mooregr_deleteth1s@.greenms.com> wrote:

>Sounds like they may have had a huge database that only contained 200 MB of
>data.
>Other than havnig 31 GB free, not sure what you can do.
>Can you post the results of a RESTORE FILEHEADERSONLY command?
BackupName
BackupDescription
BackupType ExpirationDate
Compressed Position DeviceType UserName
ServerName
DatabaseName
DatabaseVersion DatabaseCreationDate
BackupSize FirstLsn LastLsn
CheckpointLsn DifferentialBaseLsn
BackupStartDate
BackupFinishDate SortOrder
CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel
SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor
SoftwareVersionBuild MachineName
Flags BindingId RecoveryForkId
Collation
---------------------------------------
----------------------------------------------------------------------------
---- ----------------
---- --- ----
---------------------------------------
---------------------------------------
---------------------------------------
----- ----------------
------- --------
-------- --------
--------
----------------
---------------- ---
--- ----- ------- ------
------ ------- -------
-------
---------------------------------------
---- ------------
------------
---------------------------------------
Scheduled xx Test backup
Scheduled xx Test Backup
1 NULL 0
1 102 TESTSERVER\Administrator
TESTSERVER
xx Test
539 2003-08-06 17:38:53.000
206685184 3597000023310200001 3597000023311100001
3597000023310200003 3596000007489300003 2004-01-12
18:55:39.000 2004-01-12 18:56:14.000
52 228 1033 196609 80
4608 8 0 194
TESTSERVER
0 {007ED5AD-104E-452D-xxxx-512A2B3C700A}
{43356D09-0597-4AD4-xxxx-6D38CAD81F5D} SQL_Latin1_General_CP1_CI_AS

(1 row(s) affected)

>> Waz|||<EggsAckley@.Last.com> wrote in message
news:tjba91d1qsh4anh0otm6772h27l4f1ju48@.4ax.com...
> On Thu, 26 May 2005 01:21:58 GMT, "Greg D. Moore \(Strider\)"
> <mooregr_deleteth1s@.greenms.com> wrote:
> >Sounds like they may have had a huge database that only contained 200 MB
of
> >data.
> >Other than havnig 31 GB free, not sure what you can do.
> >Can you post the results of a RESTORE FILEHEADERSONLY command?

Thanks. unfortunately I meant FILELISTONLY as Dan had correctly said.

And no, unfortunately I don't know of anyway to restore a database w/o the
log file.|||Hi:

I still need help with this. I tried the following command in Query
Analyzer:

RESTORE DATABASE x
from y
with NORECOVERY,
MOVE 'x_dat' to "C:/***/mssql/data/x_dat1.mdf;

I make no reference to the log file 'x_log' which is also present in
the backup file, as demonstrated by the results of RESTORE
FILELISTONLY.

It seems content with the data, but then it complains about the
logfile. Of course if I specify a location for the log, it complains
that there is insufficient space on the disk (which is true, it wants
31 GB).

Is there a way to restore only the data portion? Or is there another
way to create a DB from a backup file, when the DB doesn't already
exist on my server?

I very much appreciate anyone's help with this.

Eggs

On Wed, 25 May 2005 18:07:55 -0400, EggsAckley@.Last.com wrote:

>Hi:
>I have a file that I have been told is a SQL Server backup from a
>server somewhere. The file is about 200MB in size
>I am trying to create the database on my local server using RESTORE. I
>created the backup device, associated it with a backup name etc.,
>copied the file into the backup dir.
>When I run the RESTORE command, Query Analyzer tells me the database
>needs 31 GB of space and the RESTORE aborts. I've tried this several
>times, get the same result every time.
>Anybody ever seen anything like this? Is there another way to create a
>DB in a server using a backup file?
>I am running SqlServer 2000, Developer edition on a machine running
>Windows Server 2003 OS.
>I would appreciate any help/suggestions.
>Waz|||(EggsAckley@.Last.com) writes:
> I still need help with this. I tried the following command in Query
> Analyzer:
> RESTORE DATABASE x
> from y
> with NORECOVERY,
> MOVE 'x_dat' to "C:/***/mssql/data/x_dat1.mdf;
> I make no reference to the log file 'x_log' which is also present in
> the backup file, as demonstrated by the results of RESTORE
> FILELISTONLY.
> It seems content with the data, but then it complains about the
> logfile. Of course if I specify a location for the log, it complains
> that there is insufficient space on the disk (which is true, it wants
> 31 GB).
> Is there a way to restore only the data portion? Or is there another
> way to create a DB from a backup file, when the DB doesn't already
> exist on my server?
> I very much appreciate anyone's help with this.

The by far easiest solution in this case, is to add a new disk to the
machine. Look at the price for a 40 GB disk and compare with what you
cost your employer/client per hour. You don't have much time to look
for shortcut solution, before your work is more expensive than the disk.

If this happens on a machine which require special expensive disks
on some sort, just find another computer where you can restore,
shrink the log, and then move the database to where you want it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 26 May 2005 22:24:20 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>The by far easiest solution in this case, is to add a new disk to the
>machine. Look at the price for a 40 GB disk and compare with what you
>cost your employer/client per hour. You don't have much time to look
>for shortcut solution, before your work is more expensive than the disk.
>If this happens on a machine which require special expensive disks
>on some sort, just find another computer where you can restore,
>shrink the log, and then move the database to where you want it.

Thanks very much for the reality check. I used my USB external drive
and was able to do the restore in a straightforward manner. The
initial stumbling point was that I couldn't see the USB drive when I
installed it under Windows 2003 Server, then I realized W2003Svr
requires you to manually assign drive letters, so I did.

Can you (or anyone) suggest general guidelines for improving the
performance and/or efficiency of stored procedures under SqlServer
2000? Again, I very much appreciate any guidance.

Eggs|||(EggsAckley@.Last.com) writes:
> Can you (or anyone) suggest general guidelines for improving the
> performance and/or efficiency of stored procedures under SqlServer
> 2000? Again, I very much appreciate any guidance.

The question is a bit open-ended. But here are some general points
from the top of my head:

o Try as much as possible to avoid iterative solution, and use set-
based solutions.

o When using temp tables, create them as the first executable statement
in the procedure. (DECLARE @.local is not an executable statement.) If
you create temp tables in the middle of it all, you will get a
recompile.

o Share your graces between temp tables and table variables. Sometimes
ons is right, and sometimes the other. My general suggestion is that
you start with a temp table, but if you find that you get performance
problems because of recompiles, switch to temp tables. (Keep in mind
that those recompiles can just as well be life-savers!)

o And while it's sometimes it's a good idea to keep a temp table/table
variable for storage of intermediate results, it can also sometimes
be more effecient with one big query from hell that does it all in
one statement.

o Don't do this:
CREATE PROCEDURE some_sp ... @.startdate = NULL, ... AS
IF @.startdate IS NULL
SELECT @.startdate = convert(char(8), getdate(), 112)
since SQL Server sniffs the parameter value, it will build query
plans assuming that @.startdate is NULL. It's better to copy to
a local variable, of which SQL Server makes no assumption at all
about the value. Even more effecient is to move processing to an
inner procedure once all defaults have been filled in.

o Microsoft recommends that you always use two-part notation, for
instance "SELECT ... FROM dbl.tbl" and not "SELECT ... FROM tbl",
and claims this is more effecient. I claim that if the procedure
is owned by dbo, there should be no difference, and if there is,
that's a bug. One of these days, I will have to benchmark it.
Anyway, since MS recommends it, I thought I should mention it.

If you have particular issues you want to dicsuss, you are welcome.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Fri, 27 May 2005 20:46:09 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

> (EggsAckley@.Last.com) writes:
>> Can you (or anyone) suggest general guidelines for improving the
>> performance and/or efficiency of stored procedures under SqlServer
>> 2000? Again, I very much appreciate any guidance.
>The question is a bit open-ended. But here are some general points
>from the top of my head:
>o Try as much as possible to avoid iterative solution, and use set-
> based solutions.

>>Please elaborate a little on what exactly you mean by
set-based solutions vs iterative.

>>Also what do you think about cursors?
>o When using temp tables, create them as the first executable statement
> in the procedure. (DECLARE @.local is not an executable statement.) If
> you create temp tables in the middle of it all, you will get a
> recompile.
>o Share your graces between temp tables and table variables. Sometimes
> ons is right, and sometimes the other. My general suggestion is that
> you start with a temp table, but if you find that you get performance
> problems because of recompiles, switch to temp tables. (Keep in mind
> that those recompiles can just as well be life-savers!)
>o And while it's sometimes it's a good idea to keep a temp table/table
> variable for storage of intermediate results, it can also sometimes
> be more effecient with one big query from hell that does it all in
> one statement.
>o Don't do this:
> CREATE PROCEDURE some_sp ... @.startdate = NULL, ... AS
> IF @.startdate IS NULL
> SELECT @.startdate = convert(char(8), getdate(), 112)
> since SQL Server sniffs the parameter value, it will build query
> plans assuming that @.startdate is NULL. It's better to copy to
> a local variable, of which SQL Server makes no assumption at all
> about the value. Even more effecient is to move processing to an
> inner procedure once all defaults have been filled in.
>o Microsoft recommends that you always use two-part notation, for
> instance "SELECT ... FROM dbl.tbl" and not "SELECT ... FROM tbl",
> and claims this is more effecient. I claim that if the procedure
> is owned by dbo, there should be no difference, and if there is,
> that's a bug. One of these days, I will have to benchmark it.
> Anyway, since MS recommends it, I thought I should mention it.
>If you have particular issues you want to dicsuss, you are welcome.

Thanks very much.

EA|||(EggsAckley@.Last.com) writes:
>>o Try as much as possible to avoid iterative solution, and use set-
>> based solutions.
> >>Please elaborate a little on what exactly you mean by
> set-based solutions vs iterative.
> >>Also what do you think about cursors?

A cursor is an iterative solution. Some people hear that cursors are
evil, so they go home and replace the cursor with a WHILE loop where
they do SELECT MIN from a table or somesuch. That's typiclally even
worse.

In a set-based solution you work on all data in one statement. I steal an
example from another thread, where a poster had a trigger like this:

CREATE TRIGGER [AI_CATEGORY] ON [dbo].[CATEGORY]
FOR INSERT
AS
DECLARE @.TEMP_ID AS INT,
@.COUNTER AS INT,
@.P_ID AS INT

SELECT @.TEMP_ID = CATEGORY_ID,
@.COUNTER = 1,
@.P_ID = PARENT_CATEGORY_ID
FROM INSERTED
IF @.P_ID IS NOT NULL
BEGIN
WHILE@.TEMP_ID IS NOT NULL
BEGIN
SELECT @.TEMP_ID = @.P_ID,
@.COUNTER = @.COUNTER + 1
FROM INSERTED
END
END
UPDATE CATEGORY
SET DEPTH = @.COUNTER

This is an example of an iterative solution. Here is my rewrite of this
into a set-based solution:

CREATE TRIGGER category_tri ON CATEGORY FOR INSERT AS
UPDATE c
SET DEPTH = coalesce(p.DEPTH, 0) + 1
FROM CATEGORY c
JOIN inserted i ON c.CATEGORY_ID = i.CATEGORY_ID
LEFT JOIN CATEGORY p ON i.PARENT_CATEGORY_ID = p.CATEGORY_ID

In this case, we cannot compare performance, as the iterative trigger
was incorrect, but it illustrates the two different approaches.

There are situations where iterative solutions are required, or at least
can be justified. But in many situations, there are magnitudes of
performance to gain by using a set-based solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> Can you (or anyone) suggest general guidelines for improving the performance and/or efficiency of stored procedures under SqlServer 2000? <<

I have some general guidelines in SQL PROGRAMMING STYLE (chapters 8, 9
and 10). Chapter 8 is a review of the principles of software
engineering and point out that they sitll apply to T-SQL, PL/SQL,
SQL/PSM, Informix/4GL, and all the other proprietary 4GLs. Frankly, I
think that nobody is teaching SE any more from the code I see. Chapter
9 is "Heurtistics" and 10 is "Thinking in SQL"

In the case of T-SQL in particular:

1) One old and still good heuristic was not to write over 50 lines in a
proc. T-SQL is a simple one-pass compiler and was not designed to be
an application development language. Do not stress it.

2) The lower the McCabe number of the proc, the better it will run.
That means try to write a chain of pure SQL statements without whiles
or If-then flow control. Again, STYLE has example of this. You can do
a lot of if-thn logic in a CASE expression

3) Think in sets and not sequences of process steps. This is vague and
hard to teach. Remember learning recursion? You just have to bang
your head against it until you understand it.

4) Avoid materializing physical storage like the proprietary temp
tables and table variables. Use derived tables and CTEs instead, so the
optimizer can see everything. Most of the time, materializing physical
storage is the result of violating (3); they hold the results of a step
and pass it along to the next step in sequence, like we use to do with
mag tapes in the 1950's.

5) Test code for exceptions. Hey, sometimes the heurisitics are
wrong. Parameter sniff and re-compiling are particular to T-SQL and
can help or hurt in production environments.

6) If your procedure has to clean up data, then the DDL is probably
missing constraints. Think of the schema as a whole, not as procedures
here and data there. That is how we designed file systems.|||--CELKO-- (jcelko212@.earthlink.net) writes:
> 1) One old and still good heuristic was not to write over 50 lines in a
> proc. T-SQL is a simple one-pass compiler and was not designed to be
> an application development language. Do not stress it.

Ehum, a single SQL statement can easily exceed 50 lines - particulary if
one is to go by the advice and use not temp tables.

For the record, the longest stored procedure we have is some 3000 lines
of code. This procedure had a predecessor, which was shorter for the
simple reason that it called plenty of subprocedures. Those subprocedures
are now incorporated in the big one. Why?

Because the original procedure accepted scalar input in terms of
variables. Variables are easy to pass around as parameters. The new
version instead reads its input from a table, and make an extensive
use of table variables - there's 43 of them. Tables are difficult to
pass as parameters.

> 4) Avoid materializing physical storage like the proprietary temp
> tables and table variables. Use derived tables and CTEs instead, so the
> optimizer can see everything.

Sometimes this is a good idea. Sometimes it's better to store
intermediate data in a temp table/table variable. This is particularly
true if you need to repeat the same derived table in the query. The
optimizer computes it for each occurrence. The same applies to CTEs
in SQL 2005. But it can also be good to use a temp table for intermediate
storage, since a temp table has statistics, and this can help the
optimizer.

Speaking of proprietary issues, here's another thing. Avoid the ANSI
way:

UPDATE tbl
SET col = (SELECT SUM(col2) FROM tbl2 WHERE tbl2.keycol1 = tbl.keycol

Instead use the proprietary MSSQL way:

UPDATE tbl
SET col = d.sum2
FROM tbl t
JOIN (SELECT keycol, sum2 = SUM(col2)
FROM tbl2
GROUP BY keycol) d ON t.keycol = d.keycol

My experience is that this gives better performance.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating a check constraint on a renamed column

Hi

I am having a check constraint on a table column Col1.
Now i am renaming the table column from Col1 to Col2. I dropped the check constraint before renaming the column . Then I renamed the column and then i am recreating the check constraint on Col2. I have written all these commands in a single script. Now when i am trying to run this script it gives me error saying
"Invalid column name 'Col2'"
How can i resolve this issue since i need to run these commands in a single file only.

Commands:-

CREATE TABLE TRY(
ID INTEGER,
COL1 TINYINT,
CONSTRAINT CHK_COL CHECK (COL1 IN(1,2))
)

ALTER TABLE TRY DROP CONSTRAINT CHK_COL
EXEC SP_RENAME 'TRY.COL1', 'COL2'
ALTER TABLE TRY ADD CONSTRAINT CHK_COL CHECK(COL2 IN (1,2))

SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to SQL Server.

use the following script...

Code Snippet

CREATE TABLE TRY(

ID INTEGER,

COL1 TINYINT,

CONSTRAINT CHK_COL CHECK (COL1 IN(1,2))

)

Go

ALTER TABLE TRY DROP CONSTRAINT CHK_COL

Go

EXEC SP_RENAME 'TRY.COL1', 'COL2'

Go

ALTER TABLE TRY ADD CONSTRAINT CHK_COL CHECK(COL2 IN (1,2))

|||

As Mani indicated, SQL Server operates on a 'batch' of statements at a time. Without a GO between your two statements, the first statement has not completed, and the second statement is attempting to change a table that doesn't yet exists.

Adding the GO between the statements is required after CREATE object type operations if you wish to then work with those objects.

|||ok
now i need to give the above code in a BEGIN END block
can i execute a BEGIN END block within a BEGIN END block with a GO statement
|||

No.. You should not allowed to enclose GO with in BEGIN .. END.

It is logically incorrect.

Why you need to have in BEGIN .. END?