SCRIPT TO ALTER for functions in a SQL 2000

I installed SQL Server Management Express and try to access to a SQL Server 2000 remotly.

I connected correctly but when I try to alter an existing function, I get the following error message:

TITLE: Microsoft SQL Server Management Studio Express

Property AnsiNullsStatus is not available for UserDefinedFunction '[dbo].[functionName]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Express.Smo)

Note that functionName contain the correct function name.

Can someone confirm me that SQL Server Managmt Express can not alter function from previous SQL version or may I need to set some parameters on Server 2000.

Thanks

I'm moving this to the Tools forum.

Mike

sql

Script to Add WITH ENCRYPTION to all SPs, Vs and Fns

I'm about to roll out a new database to another site and would like to encrypt all stored procedures, views and functions. I know this can be done by simply adding WITH ENCRYPTION in the appropriate spot, but I don't want to edit every single stored procedure, view and function one by one.

Is there a way (and if so, how do I go about it) to first of all output all stored procedures, views and scripts to a file, (not using enterprise manager, but rather using a sql script) then encrypt them all?

Essentially, I need to output them all to a file first just in case I have to edit them again.

Any ideas on how I would go about this?

Thanks in advanceAnyone have any clues?|||you need to script out the sp/fn/etc, then use FileSystemObject to read the file in and look for " AS" and prefix it with "with encryption". There isn't any *automated* way or sql builtin tool.

Script SQL 2005 database to individual files?

I am trying to script the DROP(IF EXISTS) and CREATE for all of my
tables, views, stored procs, and functions to individual SQL text files
(one per object). This was trivially done in SQL 2000 with Enterprise
Manager, but when I try in SQL 2005 through Management Studio my only
"script mode" options are:

- Script to file (which is one huge file with everything)
- Script to Clipboard
- Script to New Query Window

FYI, I get to this screen through Management Studio by right clicking
on a database and selecting Tasks > Generate Scripts... > Next (doesn't
seem to matter what combo of objects I select to script or what other
options). I am using SQL Server 2005 Developer (which is Microsoft SQL
Server Management Studio 9.00.1399.00).

Any solution to this (i.e. via Management Studio, command line, etc.)
would be greatly appreciated.

Thanks.
TedMSDN/BOL indicates that this is possible here
http://msdn2.microsoft.com/en-us/library/ms191299.aspx. However, it
doesn't give any clue on how to accomplish it.

Quote from that page: "The schema for generated objects can be saved in
a single SQL Script file, or in several files with each file containing
the schema of just one object."|||Ted O'Connor (toconnor@.gmail.com) writes:
> I am trying to script the DROP(IF EXISTS) and CREATE for all of my
> tables, views, stored procs, and functions to individual SQL text files
> (one per object). This was trivially done in SQL 2000 with Enterprise
> Manager, but when I try in SQL 2005 through Management Studio my only
> "script mode" options are:
> - Script to file (which is one huge file with everything)
> - Script to Clipboard
> - Script to New Query Window
> FYI, I get to this screen through Management Studio by right clicking
> on a database and selecting Tasks > Generate Scripts... > Next (doesn't
> seem to matter what combo of objects I select to script or what other
> options). I am using SQL Server 2005 Developer (which is Microsoft SQL
> Server Management Studio 9.00.1399.00).
> Any solution to this (i.e. via Management Studio, command line, etc.)
> would be greatly appreciated.

Rather than hackiong SMO on your own, I don't think there is one.
Note that there are really two features from SQL 2000 you are missing:
1) One object per file.
2) A script that performs both DROP and CREATE.

There is this suggestion on MSDN Product Feedback Centre,
http://lab.msdn.microsoft.com/produ...px?feedbackid=9
eb6c773-2dbb-4a27-b9d8-225d6ed4385a
the by far most voted-on item for SQL Server.

I did actually only find one item that brings up one file per object,
http://lab.msdn.microsoft.com/produ...px?feedbackid=2
7695db7-cef6-42c3-9cb0-ac30583bbee9
there are no votes here, beside the submitted, but that is because the
bug has not been validated.

In any case, that would be better as a suggestion, as it is not a bug
that you can't script per object, just a poor design. So if you can't
find a suggestion on that theme - submit one.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Script objects

Is there anyway to automate the scripting of tables,views, functions, and
procedures for a database to run nightly?
Thanks,
Doug Stiershttp://www.karaszi.com/sqlserver/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Doug Stiers" <doug@.nospam.com> wrote in message news:epYawYoEEHA.3976@.TK2MSFTNGP12.phx.gbl
..
> Is there anyway to automate the scripting of tables,views, functions, and
> procedures for a database to run nightly?
> Thanks,
> Doug Stiers
>

Script for SQL 2005

Hi,

How to prepare a script that copies all the table structures, procedures, logins, functions from sql server 2000 database to sql server 2005 database. Sorry i am confused doing this. can anyone help me out. pls.

Thanks in advance

1st question, why do you need a script to do this? You can backup your SQL 2000 DB, restore it on SQL 2005 and upgrade it to SQL 2005 when you restore. Same holds true for detaching it from SQL 2000, copying it to SQL 2005 server, then attaching it to the SQL 2005 DB server as an upgraded DB.

If you need the scripts, you can script the database by Opening Enterprise Manager, right clicking on the database you want, then select script menu (I can't remember the exact name of the choice), but in the script wizard, select all objects and set your paramaters and file output. Run the wizard and you should get one or several (depending on how you set it up) files you can open and run on SQL 2005 (SQL 2005 supports SQL 2000 database compatability, so you can create the SQL 2000 DB on it and then upgrade it).

Script for SQL 2005

Hi,

How to prepare a script that copies all the table structures, procedures, logins, functions from sql server 2000 database to sql server 2005 database. Sorry i am confused doing this. can anyone help me out. pls.

Thanks in advance

1st question, why do you need a script to do this? You can backup your SQL 2000 DB, restore it on SQL 2005 and upgrade it to SQL 2005 when you restore. Same holds true for detaching it from SQL 2000, copying it to SQL 2005 server, then attaching it to the SQL 2005 DB server as an upgraded DB.

If you need the scripts, you can script the database by Opening Enterprise Manager, right clicking on the database you want, then select script menu (I can't remember the exact name of the choice), but in the script wizard, select all objects and set your paramaters and file output. Run the wizard and you should get one or several (depending on how you set it up) files you can open and run on SQL 2005 (SQL 2005 supports SQL 2000 database compatability, so you can create the SQL 2000 DB on it and then upgrade it).

Script Component - Overidable Methods/Functions.

I am looking for a list of either generally used or a full list of overridable methods/functions for a script transformation?

I tried looking in the Visual Studio behind the script component but it only gives one method namely, Input0_ProcessInputRow, or if you make it asynchronous then it gives you another method CreateNewOutputRows.

I am basically looking for all the available overridable methods behind a script component in various situations.

Thanks,

Andy.

Position the cursor outside of Input0_ProcessInputRow method but still within the class definition. Then type:

"public overrides " (you'll need the space at the end)

Intellisense will then give you a list of overridable methods.

-Jamie

scptxfr.exe - generated script fails

Generated script fails because fucntions are added to this script after
tables in which this functions can be used for calculated column.
How can I resolve this?
Is there any usefull tool that can generate script with proper order of
dependence objects?
|||Download DataStudio from http://www.agileinfollc.com, it has very strong
scripting capability.
"Sergi Adamchuk" <adamchuk@.gmail.com> wrote in message
news:1124378725.452117.32190@.g47g2000cwa.googlegro ups.com...
> Is there any usefull tool that can generate script with proper order of
> dependence objects?
>
|||Can it do exactly that I need. It is 7 MB very big for me. I would like
to know weather this soft can do my work before downloading. Does
avaluatin versiong it?
|||DataStudio has evaluation version (http://www.agileinfollc.com/product.asp),
you can install and try it before you buy.
John King
http://www.agileinfollc.com
"Sergi Adamchuk" <adamchuk@.gmail.com> wrote in message
news:1124459262.366497.216500@.g47g2000cwa.googlegr oups.com...
> Can it do exactly that I need. It is 7 MB very big for me. I would like
> to know weather this soft can do my work before downloading. Does
> avaluatin versiong it?
>
|||I downloaded evaluation version. This software DOES NOT make working
SQL script for database creation. ((
The problem is still opened.
|||Can I temprally disable error rising if I'am creating a table that has
in its default value nonexisting function?

SCOPE_IDENTITY() vs. @@IDENTITY

I have a basic C# application that is trying to INSERT a row and get the ID. Really simple; there are no triggers, no stored procs or functions were involved, the app is single threaded, there is currently only one user. I have a really basic table with a INTEGER IDENTITY PK column. All very standard.

If I do the INSERT followed by a "SELECT @.@.IDENTITY" on the same connection, it works correctly.
If I use SCOPE_IDENTITY() instead, it returns NULL. I use SCOPE_IDENTITY on a variety of other occassions and it works fine. Why would this be? I thought SCOPE_IDENTITY() was the preferred replacement to @.@.IDENTITY.

I guess what I have is satisfactory but this was frustrating and I want to know why.

This is on SQL Server 2000 Standard edition with version SP3a + hot fixeshai roger,

SCOPE_IDENTITY and @.@.IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope. @.@.IDENTITY is not limited to a specific scope.

This is the example given in BOL

Suppose if you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@.@.IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

Hope this relives u of ur frustration|||Yes, I read BOL and understand the documented theoretical and conceptual differences between the two. However, those differences don't apply to my situation.

There is only one thread, one process, one identity value, and one table involved. There are no triggers or functions or stored procs involved. It's a simple INSERT/get ID situation. And @.@.IDENTITY works and SCOPE_IDENTITY doesn't work which just doesn't make any sense according to what I've read.