Is it possible to modify column data type of view

The SQL Server Management Studio shows the data type of each column of views. I am wondering how SQL server determines the types since my SQL code of views does not specifiy data types for any columns.

I am much more interested in knowing whether the data types can be modified. Could anyone offer some hint?

Thanks,

hz

SQL Server uss the underlying schema information to do so unless you don′t specify a different data type than the source data type (liek within CONVERT). You can change the resulting data type in the view e.g. via CONNVERT(VARCHAR(10),GETDATE(),112), which was a datetime before and a varchar afterwards.

HTH, Jens Suessmeyer.|||

Jens, thanks a lot! That is exactly what I was looking for.

hz

Is it possible to manually run a subscription?

I'm wondering if there's a way through code to manually trigger a timed
subscription to run? The only thing I can see is to create an additional
schedule on the fly set for a few minutes in advance.
Any help would greatly be appriciated.
JoshYou can open Enterprise Manager and find the job with the same
scherduled time, right click and tell it to run.|||holy crap, no wonder sql server agent has to be running. I'm a bit
ashamed I never investigated this before.
I actually need to fire off the subscription through code. I'd imagine
this could be done then through SQLDMO.
Thanks for your response.
Lon wrote:
> You can open Enterprise Manager and find the job with the same
> scherduled time, right click and tell it to run.
>

Is it possible to ftp files using code in a SSIS Script Task?

Is it possible to ftp files using code in a Script Task? I need to read the contents of an xml file and if it has a a specific file name in there then I ftp the corresponding pdf file which is at the same location as the xml file. However I cannot do this using the provided FTP Task in SSIS, I would need to use code to do this as there are close to 50 xml files which I need to read and upload the corresponding pdf file file it meets a certain criteria.

I do not see a way of looping thru all the files in a folder unless I do this in a Script task. Any inputs or alternative comments on doing this will be appreciated.

Thanks,

MShah

In the Control Flow you should be able to use a ForEach loop to loop over all the files. Then you can use a script task (XML task might also work but not sure) to extract the information needed to create the path & filename to FTP.

You would store the create filename in a Variable and then do the FTP task in the loop to upload the files. Anyone know if the FTP task will connect to FTP once in this type of loop?

Fred

sql

is it possible to do this without cursors?

i have the following vb code that i want to turn into a stored procedure.
Can it be done without using cursors? thanks for any help!
what this code does is it says for each item, which other items reference it
in the column called source.
Set rst = CurrentDb.OpenRecordset("SELECT [Name], [Type], [ReferencedBy]
FROM [Catalog]")
If Not rst.EOF Then
rst.MoveFirst
Do While Not rst.EOF
objName = rst![Name]
objRefs = ""
Set findrst = CurrentDb.OpenRecordset("SELECT DISTINCT [Name],
[Type] FROM [Catalog] WHERE [Name] <> '" & objName & "' AND [Source] LIKE '*"
+ objName + "*';")
If Not findrst.EOF Then
findrst.MoveFirst
Do While Not findrst.EOF
objName = findrst![Name]
objType = findrst![Type]
objRefs = IIf(Len(objRefs) > 0, objRefs & ", " & objName
& " (" & objType & ")", objName & " (" & objType & ")")
findrst.MoveNext
Loop
End If
rst.Edit
rst![ReferencedBy] = objRefs
rst.Update
rst.MoveNext
Loop
End IfBen,
Please post the table DDL and sample data and desired results.
HTH
Jerry
"Ben" <ben_1_ AT hotmail DOT com> wrote in message
news:97650560-E7C1-40A4-B7FA-0443DC0FB20F@.microsoft.com...
>i have the following vb code that i want to turn into a stored procedure.
> Can it be done without using cursors? thanks for any help!
> what this code does is it says for each item, which other items reference
> it
> in the column called source.
>
> Set rst = CurrentDb.OpenRecordset("SELECT [Name], [Type],
> [ReferencedBy]
> FROM [Catalog]")
> If Not rst.EOF Then
> rst.MoveFirst
> Do While Not rst.EOF
> objName = rst![Name]
> objRefs = ""
> Set findrst = CurrentDb.OpenRecordset("SELECT DISTINCT [Name],
> [Type] FROM [Catalog] WHERE [Name] <> '" & objName & "' AND [Source] LIKE
> '*"
> + objName + "*';")
> If Not findrst.EOF Then
> findrst.MoveFirst
> Do While Not findrst.EOF
> objName = findrst![Name]
> objType = findrst![Type]
> objRefs = IIf(Len(objRefs) > 0, objRefs & ", " &
> objName
> & " (" & objType & ")", objName & " (" & objType & ")")
> findrst.MoveNext
> Loop
> End If
> rst.Edit
> rst![ReferencedBy] = objRefs
> rst.Update
> rst.MoveNext
> Loop
> End If|||create table catalog (name varchar(255), type varchar(50), source text,
referencedby text)
sample data before running the stored procedure
name type source referencedby
red hat mens red shirt
red shirt mens
after the stored procedure runs, i need the table to look like
name type source referencedby
red hat mens red shirt
red shirt mens red hat
the end result says that the red shirt is referenced in the source column by
the red hat.
thanks for any and all help!
"Jerry Spivey" wrote:

> Ben,
> Please post the table DDL and sample data and desired results.
> HTH
> Jerry
> "Ben" <ben_1_ AT hotmail DOT com> wrote in message
> news:97650560-E7C1-40A4-B7FA-0443DC0FB20F@.microsoft.com...
>
>|||SELECT c.[name], c.[type], c.[source], r.[name] AS ReferencedBy
FROM [catalog] c
LEFT JOIN [catalog] r ON c.[name] = r.[source]
HTH,
John Scragg
"Ben" wrote:
> create table catalog (name varchar(255), type varchar(50), source text,
> referencedby text)
> sample data before running the stored procedure
> name type source referencedby
> red hat mens red shirt
> red shirt mens
> after the stored procedure runs, i need the table to look like
> name type source referencedby
> red hat mens red shirt
> red shirt mens red hat
>
> the end result says that the red shirt is referenced in the source column
by
> the red hat.
> thanks for any and all help!
>
> "Jerry Spivey" wrote:
>|||A couple tips.
I assume you are not using the "text" data type for your source column. If
so, why? It is a FK column and should have the same data type as the related
column (in this case [name]). You can enforce referential integrity even
with self referenceing table relationships. I would suggest you do that.
Also, try not to use keywords for column or table names and try not to put
spaces in your column names.
Best of luck,
John
"Ben" wrote:
> create table catalog (name varchar(255), type varchar(50), source text,
> referencedby text)
> sample data before running the stored procedure
> name type source referencedby
> red hat mens red shirt
> red shirt mens
> after the stored procedure runs, i need the table to look like
> name type source referencedby
> red hat mens red shirt
> red shirt mens red hat
>
> the end result says that the red shirt is referenced in the source column
by
> the red hat.
> thanks for any and all help!
>
> "Jerry Spivey" wrote:
>|||Thank you for the reply but unfortuanately that doesnt work (i dont think)
because the column source can have any number of items in it that it
references. i guess my sample wasnt clear enough, let me try again
the column name is a database object name
the column type is the type of database object (user table, stored procedure
)
the column source is the source code for the object (eg, the code/text of a
stored procedure)
the column referencedby contains a list of objects where the value in this
records name field can be found in all other records source column
i hope that is a little clearer. i know the vb code i posted earlier works
for this exact task, but i was hoping to have a stored procedure version as
well that didnt use cursors.
thanks for any help again.
ben

is it possible to do a redirect in the custom code?

Is it possible to do a redirect to an arbitrary page in by using custom code?

eg.

Public sub myRedirect()

response.redirect(http://www.microsoft.com)

End sub

/Alex

Or automatically open a new webpage when viewing the report?

Is it possible to create a bar code in SQL reporting?

I need to be able to create a bar code in SQL reports. Do I need to get a three party tool or do you have anything that can help.

Thanks

SQL Server Report needs nothing. What you need it's a font. You have to look for a ttf with eg bar code 39 or other encoding. Unfortunattly goods ttf aren't free.
When you've got the font then replace the font of the field wich has the code and that's all.
Good luck in your search.|||thanks|||

I found a better choice instead of using fonts.

Barcode Professional .NET for Reporting Services

http://www.neodynamic.com/Products/BCRS/BarcodeRS.aspx?tabid=78&prodid=7

Cheers

|||

Or you could try this Windows Form Control that supports Reporting Services.

http://www.technoriversoft.com/developer.html

|||

Hi, I am trying to use a TTF font, and it prints out but the bar code reader is not reading the font.

In Crystal, I had to put an * before after the value.

In Excel, it works the same way.

But in SRS 2000, it will not work.

My expression is

="*"& Fields!MANUFACTUREORDER_I.Value.Trim() & "*"

I have also tried using the plus instead of *.

The font I am using is Free 3 of 9 Extended.

thanks for you help.

|||

IT is not good way

it is because when you compile the report into production server.

when user print it, it will show the text rather than bar code....

|||

As Inamori has stated. Everything works fine until the report is compiled into a productions server. At that point the barcode is shown as clear text.

So what exactly is the fix. Was one ever found?

Is it possible to create a bar code in SQL reporting?

I need to be able to create a bar code in SQL reports. Do I need to get a three party tool or do you have anything that can help.

Thanks

SQL Server Report needs nothing. What you need it's a font. You have to look for a ttf with eg bar code 39 or other encoding. Unfortunattly goods ttf aren't free.
When you've got the font then replace the font of the field wich has the code and that's all.
Good luck in your search.|||thanks|||

I found a better choice instead of using fonts.

Barcode Professional .NET for Reporting Services

http://www.neodynamic.com/Products/BCRS/BarcodeRS.aspx?tabid=78&prodid=7

Cheers

|||

Or you could try this Windows Form Control that supports Reporting Services.

http://www.technoriversoft.com/developer.html

|||

Hi, I am trying to use a TTF font, and it prints out but the bar code reader is not reading the font.

In Crystal, I had to put an * before after the value.

In Excel, it works the same way.

But in SRS 2000, it will not work.

My expression is

="*"& Fields!MANUFACTUREORDER_I.Value.Trim() & "*"

I have also tried using the plus instead of *.

The font I am using is Free 3 of 9 Extended.

thanks for you help.

|||

IT is not good way

it is because when you compile the report into production server.

when user print it, it will show the text rather than bar code....

|||

As Inamori has stated. Everything works fine until the report is compiled into a productions server. At that point the barcode is shown as clear text.

So what exactly is the fix. Was one ever found?

sql

Is it possible to create a bar code in SQL reporting?

I need to be able to create a bar code in SQL reports. Do I need to get a three party tool or do you have anything that can help.

Thanks

SQL Server Report needs nothing. What you need it's a font. You have to look for a ttf with eg bar code 39 or other encoding. Unfortunattly goods ttf aren't free.
When you've got the font then replace the font of the field wich has the code and that's all.
Good luck in your search.|||thanks|||

I found a better choice instead of using fonts.

Barcode Professional .NET for Reporting Services

http://www.neodynamic.com/Products/BCRS/BarcodeRS.aspx?tabid=78&prodid=7

Cheers

|||

Or you could try this Windows Form Control that supports Reporting Services.

http://www.technoriversoft.com/developer.html

|||

Hi, I am trying to use a TTF font, and it prints out but the bar code reader is not reading the font.

In Crystal, I had to put an * before after the value.

In Excel, it works the same way.

But in SRS 2000, it will not work.

My expression is

="*"& Fields!MANUFACTUREORDER_I.Value.Trim() & "*"

I have also tried using the plus instead of *.

The font I am using is Free 3 of 9 Extended.

thanks for you help.

|||

IT is not good way

it is because when you compile the report into production server.

when user print it, it will show the text rather than bar code....

|||

As Inamori has stated. Everything works fine until the report is compiled into a productions server. At that point the barcode is shown as clear text.

So what exactly is the fix. Was one ever found?

Is it possible to create a bar code in SQL reporting?

I need to be able to create a bar code in SQL reports. Do I need to get a three party tool or do you have anything that can help.

Thanks

SQL Server Report needs nothing. What you need it's a font. You have to look for a ttf with eg bar code 39 or other encoding. Unfortunattly goods ttf aren't free.
When you've got the font then replace the font of the field wich has the code and that's all.
Good luck in your search.|||thanks|||

I found a better choice instead of using fonts.

Barcode Professional .NET for Reporting Services

http://www.neodynamic.com/Products/BCRS/BarcodeRS.aspx?tabid=78&prodid=7

Cheers

|||

Or you could try this Windows Form Control that supports Reporting Services.

http://www.technoriversoft.com/developer.html

|||

Hi, I am trying to use a TTF font, and it prints out but the bar code reader is not reading the font.

In Crystal, I had to put an * before after the value.

In Excel, it works the same way.

But in SRS 2000, it will not work.

My expression is

="*"& Fields!MANUFACTUREORDER_I.Value.Trim() & "*"

I have also tried using the plus instead of *.

The font I am using is Free 3 of 9 Extended.

thanks for you help.

|||

IT is not good way

it is because when you compile the report into production server.

when user print it, it will show the text rather than bar code....

|||

As Inamori has stated. Everything works fine until the report is compiled into a productions server. At that point the barcode is shown as clear text.

So what exactly is the fix. Was one ever found?

is it a bug in SSCE OLEDB ?

i'm use this code ,in SQL2005 std and ACCESS database, it work

but if i use SSCE ,it's throw a OleDbException in ExecuteScalar()

Exception : OleDbException

0x80040E30L

DB_E_BADTYPENAME

Code Snippet

OleDbConnection od = new OleDbConnection("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=db.sdf;SSCE:Database Password=");

od.Open();

OleDbCommand og = new OleDbCommand("INSERT INTO [bills] ([billno],[checkouttime],[finalprice],[handle],[ischeckout],[memo],[paymode],[trick]) VALUES (@.billno,@.checkouttime,@.finalprice,@.handle,@.ischeckout,@.memo,@.paymode,@.trick)", od);

og.Parameters.Add("@.billno", OleDbType.VarWChar).Value = "2007051800000000";
og.Parameters.Add("@.checkouttime",OleDbType.DBTimeStamp).Value="2007-5-18 11:55:40";
og.Parameters.Add("@.finalprice", OleDbType.Single).Value = 0.0;
og.Parameters.Add("@.handle", OleDbType.VarWChar).Value = "admin";
og.Parameters.Add("@.ischeckout", OleDbType.SmallInt).Value = 0;
og.Parameters.Add("@.memo", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@.paymode", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@.trick", OleDbType.VarWChar).Value = "";

og.ExecuteScalar();

od.Close();

why the same code is not work? i'm find all MSDN ,but there is no answer

Who can help me,Thanks

SSCE is not registered as a standard oledb provider. We have a *limited* support for OLEDB. It doesn't comply with tier-1 or tier-2 requirements etc. Why don't you use our managed provider? Things are much more fun. If you have to use native provider only, you will have to use standard oledb interfaces like IDBInitialize, ICommand etc.

You can even search for oledb northwind sample on msdn. If your question is answered, please mark it as answered.

Thanks

Raja

|||Have you tried using ExecuteNonQuery in stead of ExecuteScalar

|||

Please try using double quotes instead of brackets to delimit identifiers. The SQL Compact Edition 3.0 query processor does not accept Access' brackets. Try this statement:

Code Snippet

INSERT INTO "bills" ("billno", "checkouttime", "finalprice", "handle", "ischeckout", "memo", "paymode", "trick") VALUES (...)

|||

Jo?o Paulo Figueira wrote:

Please try using double quotes instead of brackets to delimit identifiers. The SQL Compact Edition 3.0 query processor does not accept Access' brackets. Try this statement:

Code Snippet

INSERT INTO "bills" ("billno", "checkouttime", "finalprice", "handle", "ischeckout", "memo", "paymode", "trick") VALUES (...)

yes,it's works

but,i want to use the same code on SSEE SSCE ACCESS

so i use OLEDB parameter ,but it's error

|||

Rajagopal R V wrote:

SSCE is not registered as a standard oledb provider. We have a *limited* support for OLEDB. It doesn't comply with tier-1 or tier-2 requirements etc. Why don't you use our managed provider? Things are much more fun. If you have to use native provider only, you will have to use standard oledb interfaces like IDBInitialize, ICommand etc.

You can even search for oledb northwind sample on msdn. If your question is answered, please mark it as answered.

Thanks

Raja

the SSCE managed provider is sqlceconnection ,the SSEE managed provider is sqlconnection

if i use this provider ,i will write 3 different query on SSEE SSCE ACCESS

don't have an easy Method ?

thanks

|||

You could use database independent base classes in your code (e.g. DbConnection) and specific providers for each database:

DbConnection connection = new SqlConnection(...); // Or SqlCeConnection or whatever else.

connection.Open();

DbCommand command = connection.CreateCommand();

command.CommandText = "Select whatever from somewhere where some = ?";

command.Parameter.Add(...);

Usually it makes little sense because SQL dialects are slightly different and query for one database won’t work on another (unless it’s really basic).

is it a bug in SSCE OLEDB ?

i'm use this code ,in SQL2005 std and ACCESS database, it work

but if i use SSCE ,it's throw a OleDbException in ExecuteScalar()

Exception : OleDbException

0x80040E30L

DB_E_BADTYPENAME

Code Snippet

OleDbConnection od = new OleDbConnection("Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=db.sdf;SSCE:Database Password=");

od.Open();

OleDbCommand og = new OleDbCommand("INSERT INTO [bills] ([billno],[checkouttime],[finalprice],[handle],[ischeckout],[memo],[paymode],[trick]) VALUES (@.billno,@.checkouttime,@.finalprice,@.handle,@.ischeckout,@.memo,@.paymode,@.trick)", od);

og.Parameters.Add("@.billno", OleDbType.VarWChar).Value = "2007051800000000";
og.Parameters.Add("@.checkouttime",OleDbType.DBTimeStamp).Value="2007-5-18 11:55:40";
og.Parameters.Add("@.finalprice", OleDbType.Single).Value = 0.0;
og.Parameters.Add("@.handle", OleDbType.VarWChar).Value = "admin";
og.Parameters.Add("@.ischeckout", OleDbType.SmallInt).Value = 0;
og.Parameters.Add("@.memo", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@.paymode", OleDbType.VarWChar).Value = "";
og.Parameters.Add("@.trick", OleDbType.VarWChar).Value = "";

og.ExecuteScalar();

od.Close();

why the same code is not work? i'm find all MSDN ,but there is no answer

Who can help me,Thanks

SSCE is not registered as a standard oledb provider. We have a *limited* support for OLEDB. It doesn't comply with tier-1 or tier-2 requirements etc. Why don't you use our managed provider? Things are much more fun. If you have to use native provider only, you will have to use standard oledb interfaces like IDBInitialize, ICommand etc.

You can even search for oledb northwind sample on msdn. If your question is answered, please mark it as answered.

Thanks

Raja

|||Have you tried using ExecuteNonQuery in stead of ExecuteScalar

|||

Please try using double quotes instead of brackets to delimit identifiers. The SQL Compact Edition 3.0 query processor does not accept Access' brackets. Try this statement:

Code Snippet

INSERT INTO "bills" ("billno", "checkouttime", "finalprice", "handle", "ischeckout", "memo", "paymode", "trick") VALUES (...)

|||

Jo?o Paulo Figueira wrote:

Please try using double quotes instead of brackets to delimit identifiers. The SQL Compact Edition 3.0 query processor does not accept Access' brackets. Try this statement:

Code Snippet

INSERT INTO "bills" ("billno", "checkouttime", "finalprice", "handle", "ischeckout", "memo", "paymode", "trick") VALUES (...)

yes,it's works

but,i want to use the same code on SSEE SSCE ACCESS

so i use OLEDB parameter ,but it's error

|||

Rajagopal R V wrote:

SSCE is not registered as a standard oledb provider. We have a *limited* support for OLEDB. It doesn't comply with tier-1 or tier-2 requirements etc. Why don't you use our managed provider? Things are much more fun. If you have to use native provider only, you will have to use standard oledb interfaces like IDBInitialize, ICommand etc.

You can even search for oledb northwind sample on msdn. If your question is answered, please mark it as answered.

Thanks

Raja

the SSCE managed provider is sqlceconnection ,the SSEE managed provider is sqlconnection

if i use this provider ,i will write 3 different query on SSEE SSCE ACCESS

don't have an easy Method ?

thanks

|||

You could use database independent base classes in your code (e.g. DbConnection) and specific providers for each database:

DbConnection connection = new SqlConnection(...); // Or SqlCeConnection or whatever else.

connection.Open();

DbCommand command = connection.CreateCommand();

command.CommandText = "Select whatever from somewhere where some = ?";

command.Parameter.Add(...);

Usually it makes little sense because SQL dialects are slightly different and query for one database won’t work on another (unless it’s really basic).

Is having a trigger that inserts a row in Table A, when a row in same table is inserted by

I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.

Thanks

Hey,

I would prefer stored procedure; the reason is I have no control over triggers, so it makes it easier from a maintenance perspective on me, which is what I think you may also be under that restraint? I don't know how much slowing down it would do; I would think a lot of that depends on volume of data as well.

If you are inserting the main data using a stored procedure, then that stored procedure can do two inserts. You can also group them in a transaction so if one fails, both statements are rolled back for safety purposes.

Microsoft includes a lot of things: they include xp_cmdshell which most DBA's disable because of the power of it. So they will include things even though they may not be the best to implement...

|||

Actually, your DBA is correct. I always recommend, if possible, opting for stored procedures as opposed to triggers. The trigger will save you time if you are inserting rows into the table from many different sources in many different ways. But, if the only point of entry is a stored procedure, there's no reason not to place it in the sp. The farther back in SQL Server version you go, the less reliable triggers seem to get - and, yes, they can place locks on tables. Microsoft included them because they have their place - but it is a mistake to use them as substitutes for logical flow.

This shouldn't be a lot of extra work on you. If you aren't using a stored procedure already, they are much faster and far more secure than "on page" SQL. If you have insert statements in various parts of your application inserting to the same table, then you should be encapsulating them into a stored procedure anyway! Your DBA's job is to protect the efficiency and cleanliness of your database. Adding triggers unnecessarily affects both.

Is Excel ASOLEDB9 taking advantage of cube partitioning?

Hi,

I wonder if Excel ASOLEDB9 is benefiting from cube partitioning?
Some queries are very slow and the Excel generated code look not that good

I tried to pick some queries from SQL Server Profiler and run them in an mdx query window and I get syntax errors.

This leaves me perplex since I have the feeling that people try endless queries through their Excel pivot cube, then, after a while they cancel the Excel query because it takes forever, then the server remain stuck on a high level of CPU usage.

Is it because the syntax error or is it because they just ask for too much data?
Is canceling an Excel pivot data refresh enough to stop the server's query processing?

Yesterday night, it was so bad (100% CPU) that I had to restart the server.

Thanks,

Philippe

Cube partitioning is server-side, so all clients should benefit from it.

When you cancel a query in Excel, you dont cancel it server-side. Check out the following thread for more info on this.

|||Guys,
This is going to be a big problem.
Queries cancelled by the user keep running on the server.

This kills the server and there is no way that someone would spend time trying to manually trace these runaway queries and manually cancel them on the server.

It is also a big issue to have to restart the server everyday just because of these runaway queries.

I would like to see a fix for it in SP2 with a high Priority rating.

This is a server killer.

Probably the biggest bug ever in SSAS2005.

Philippe|||

If anything, this is probably a Excel bug.

If you are really struggeling with this you could try to write some custom code that identifies long running queries (look at the activityviewer sample application). Then you could cancel these queries with a xmla cancel command. Finally schedule your code to run every ten minutes or so with SQL Server Agent.

Is editing possible in QA 2000 debugger?

Hi,

Just started using the debugger in Query Analyser in SQL Server 2000.

Is there a way I can edit the code? It's nice to be able to step through the code,
but I don't seem to be able to edit it.

Cheers!

Eric.

use the alter statement

you can use it with Sps, table, view, udf etc

here some example

http://doc.ddart.net/mssql/sql70/aa-az_5.htm

you can also right click on the object in the object browser

and click on script object to new window as and then

clcik on alter

|||You can't edit in debug mode, as Joey says you need to issue an Alter proc command from the editor

Is assembly code replaceable?

I'm watching a webcast on CLR functions/procs and have a question:

If I create an assembly for my database using some .dll, is that .dll now locked by SQL Server?

I'm concerned of a situation where developer says the assembly code is bad. Developer presents new version of .dll .

- Will I get "Access denied, file is in use" when I try to replace the .dll ? Even if the assembly is used in a derived table column?

Thanks.

The file is not present on the file system. The assemblies is loaded into the database so it has to be placed here. The only thing that could possible cause problems is that you can′t drop an assembly which is used by depended procedures / functions etc.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

In addition, if you would like to deploy a newer version of a dll you previously registered with the server (using create assembly statement), you can have a look at alter assembly statement.

-Mat

|||

Good, I like that the file is no longer needed once uploaded into the database.

The ALTER ASSEMBLY statement looks promising, but is there anything that could hold an assembly-level lock because it's in use, preventing one from updating it?

Is there any sort of testing on dependents before being able to alter an assembly? For instance, I change an assembly method signature from one to two inputs, but database function expects to use one and only one. Another example, I remove a class that corresponded to a database type.

|||

Extensive work has been made in ALTER ASSEMBLY to make sure that it doesn't break any dependencies, including Function/Procedure/Triggers entry-point, UDTs, computed column definitions, persisted CLR expressions, or any schema-bound database code in general, so you should not have any issues there.

The ALTER ASSEMBLY statement will take an exclusive lock on your assembly, and so it will have to wait until the current users of this assembly are done. This is not very different than when you modify other database objects.

Hope this helps!