How do I attach database when I just have express edition

I'm trying to attach a database to sql server using the sp_attach_db
stored procedure. However, I don't see management studio on my
computer. I do have Visual Basic 2005, which is supposed to install
the express version of SQL Server. How do I run the command to attach
the database given that I don't have a paid-for version of sql server?
Thanks,
MarvinTry using the utility SQLCMD.EXE
How to: Connect to the Database Engine Using sqlcmd.exe
http://msdn2.microsoft.com/en-us/library/ms188247.aspx
AMB
"COHENMARVIN@.lycos.com" wrote:
> I'm trying to attach a database to sql server using the sp_attach_db
> stored procedure. However, I don't see management studio on my
> computer. I do have Visual Basic 2005, which is supposed to install
> the express version of SQL Server. How do I run the command to attach
> the database given that I don't have a paid-for version of sql server?
> Thanks,
> Marvin
>|||Hi
For SQL Server 2005 Express Edition you can download a management studio
and you will not pay for that , pease search on interent
<COHENMARVIN@.lycos.com> wrote in message
news:2e4245c5-ad3a-444d-882d-61eacf58aa4d@.i29g2000prf.googlegroups.com...
> I'm trying to attach a database to sql server using the sp_attach_db
> stored procedure. However, I don't see management studio on my
> computer. I do have Visual Basic 2005, which is supposed to install
> the express version of SQL Server. How do I run the command to attach
> the database given that I don't have a paid-for version of sql server?
> Thanks,
> Marvin|||> For SQL Server 2005 Express Edition you can download a Management Studio
> and you will not pay for that, please search on internet
Microsoft SQL Server Management Studio Express
http://www.microsoft.com/downloads/details.aspx?familyid=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796
Brief Description
Microsoft SQL Server Management Studio Express (SSMSE) is a free,
easy-to-use
graphical management tool for managing SQL Server 2005 Express Edition and
SQL Server 2005 Express Edition with Advanced Services.
Tom
http://kbupdate.info/

How do I adjust the tab settings in SQL Server Enterprise Manager.

I found how to adjust the tab settings when editing a stored procedure using
Query Analyzer, but I don't see any way to adjust the tab settings when
editing a stored procedure using SQL Server Enterprise Manager.
Does anyone know how to do this?
Thanks,
VernThere isn't. That dialog is only really appropriate for viewing the code or
copying it. You should use Query Analyzer to edit and will find the task is
much easier and cleaner.
Andrew J. Kelly SQL MVP
"Vern" <Vern@.discussions.microsoft.com> wrote in message
news:DF5D3DF0-B5EE-49C9-9816-F124D350F4F8@.microsoft.com...
>I found how to adjust the tab settings when editing a stored procedure
>using
> Query Analyzer, but I don't see any way to adjust the tab settings when
> editing a stored procedure using SQL Server Enterprise Manager.
> Does anyone know how to do this?
> Thanks,
> Vernsql

How Do I Add 1 to a Field?

I have a Table: Thread

There are 2 relevant fields: ThreadID and Counter (integer).

I want a Stored Procedure that will get a record based on ThreadID and will add 1 to the counter.

I know how to do this as 2 stored procedures (one to get the current value of the counter and a second procedure to write the new value).

There must be a better way to do it as one procedure though.

Any suggestions?

Thanks,

Chris

You can do this in a number of ways. get the number and add the 1 at the application layer. or even as simple as

SELECT counter+1

FROM Thread

WHERE Threadid = @.Threadid

|||

The example you give will return a number 1 larger, but I also need to have that new number written into the data.

Can I do that with 1 stored procedure?

Chris

|||

Chris Messineo:

I want a Stored Procedure that will get a record based on ThreadID and will add 1 to the counter.

Thats what you wanted right?

|||

I'm sorry if my question is confusing.

I need to get the new value, but I also need to set that value in the db. I believe your solution will just get me the new value.

Does that make sense?

Chris

|||

do an UPDATE then. You can write a proc that will do the update and return the value. you can either return using an OUTPUT parameter or a SELECT statement.

How do I access the value of a stored proc return param in c# using executeNonQuery?

I've got a stored proc to insert a record and return the id of the record inserted in an output param.
How do I access this value in my code after the proc is executed?

param = comm.CreateParameter();
param.ParameterName ="@.MemberID";
param.Direction =ParameterDirection.Output;
param.DbType =DbType.Int32;
comm.Parameters.Add(param);

try
{
rowsAffected =GenericDataAccess.ExecuteNonQuery(comm);
}
catch
{
rowsAffected = -1;
}

you should simply be able to access the parameters Value property.

param.Value

How do I access sql system stored procs from code

Hello:

Okay not really understanding how to do this, but how do I access system stored procedures from code? Basically I would like to determine information about each table in my database, the primary key, number of columns, the names of the columns and the datatypes of the columns in each table. Not too much to ask. How do I go about accessing this information. I have a fairly good idea on how to do it using T-SQL but how do I do it using an assembly? Has anyone else done this before? Any help would be greatly appreciated!

ThanksOkay i am stupid again and did not do a google search. So after do this I found an excellent resource for this particular problem. I found it at:

http://www.ftponline.com/vsm/2003_01/magazine/columns/databasedesign/default.aspx

Shows how to access Table Metadata programmatically using C# and VB.Net AWESOME! Working on my own code generator and now I am finally getting to a point where I can start to finish it. All I needed was a way to generate stored procs from table metadata. Now I have the table metadata and a way to generate stored procs I am all set. AWESOME.

Thanks for those that read this post hope it helps you out in the future. Remeber a real programmer does not write code but writes code that generates code for him/her.|||Just the same as any other stored procedure...you need permissions to run the proc and the path to the proc, e.g master

How do I access a SQL view in VB.Net?

I'm able to access my stored procedures just fine, but I can't query/show a view that I'd built in SQL Server. Any suggestions on how would be welcome.

Sample Stored Procedure reference, and I just want to reference a view now:

 ' Connect to the Database
Dim MyConn As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("myConnectionString"))
' Establish the set of data commands and a database connection that are used to fill the dataset
Dim MyCommand As New SqlCommand("vw_Mast_PW_Accounts", MyConn)
MyCommand.CommandType = CommandType.StoredProcedure
MyConn.Open()
dgMyGrid.DataSource = MyCommand.ExecuteReader
dgMyGrid.DataBind()
MyConn.Close()

I might be mistaken here, but a view is treated pretty much like a table. So if you want to return data from a view, query against it as if it were a table: SELECT * FROM vw_myView WHERE ...

You'll need to make sure your web app has permissions to query the view and, I think, any underlying tables (though I might be wrong on that last point).|||That's great, but do is there a way to do it without using SQL but simply referencing it as I would a stored procedure (i.e., just the name)?

Thank you,
Brent|||I don't believe so, for that same reason that you couldn't do that with a table.

Youcould create a stored procedure that returned data from your view, if you really needed that functionality...|||I gave up and built the stored procedure to access that view. Thank you for the suggestion!

Brent

How do a pass a 'Where' string clause to a stored procedure

I need to be able to construct a SELECT statement in a stored procedure and
concatenate a CSV list of values passed as an input parm to the stored
procedure. How can I do this without declaring a variable for the Select,
then executing it. I need to use this with Reporting Services so unless the
Select fields are available in the query, RS won't work.See
http://www.sommarskog.se/dyn-search.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Kitty" <Kitty@.discussions.microsoft.com> wrote in message
news:B0D87B12-BDCB-488E-89C2-F5D713DFE80E@.microsoft.com...
>I need to be able to construct a SELECT statement in a stored procedure and
> concatenate a CSV list of values passed as an input parm to the stored
> procedure. How can I do this without declaring a variable for the Select,
> then executing it. I need to use this with Reporting Services so unless
> the
> Select fields are available in the query, RS won't work.|||See
http://www.sommarskog.se/arrays-in-sql.html
http://www.users.drew.edu/skass/SQL...unction.sql.txt
The outline is like this:
select stuff
from T
join ListToTableFunction(@.myList) as Items
on Items.Item = T.Item
or with the logic of ListToTableFunction directly
in the non-dynamic query. <>
Steve Kass
Drew University
Kitty wrote:

>I need to be able to construct a SELECT statement in a stored procedure and
>concatenate a CSV list of values passed as an input parm to the stored
>procedure. How can I do this without declaring a variable for the Select,
>then executing it. I need to use this with Reporting Services so unless th
e
>Select fields are available in the query, RS won't work.
>|||>> I need to be able to construct a SELECT statement in a stored
procedure and concatenate a CSV list of values passed as an input parm
to the stored procedure. <<
No, you need to learn about coupling, cohesion and the basics of
software engineering. You are supposed to know what you are doing
before you do it. The idea of doing code on the fly is a violation of
everything you should have been taught in a freshman CS course.

How can you use VSS to manage stored procedures

Can anyone explain how to use the VSS integration in SSMS to mange stored
procedures? We use VSS to manage source code in Visual Studio but I can't
figure out how this is done with stored procedures in SMSS
What I do is:
1) Create a Solution for stored procedures.
2) Add the solution to Source Control
3) Add a new query.
4) Select a stored procedure
5) Script the SP as CREATE to clipboard
6) Paste into the new query
7) Add extra code at the bottom to grant permissions
8) Rename NewQuery.sql to my_SP.sql in the solution explorer
9) Check in pending check ins
10) repeat steps 3 to 9 until you have all your SP's under Source Control
I always script an existing sp as create so that I can't accidentally hit F5
You can do things like add a separate project to the solution for each
database or a separate solution for each database or any other way that
suits your way of organising things.
Regards,
Nigel Ainscoe MBCS MCP
"D. Haber" <DHaber@.discussions.microsoft.com> wrote in message
news:7BFAB085-3D2F-4FB0-9822-BE9BDEC2DB52@.microsoft.com...
> Can anyone explain how to use the VSS integration in SSMS to mange stored
> procedures? We use VSS to manage source code in Visual Studio but I can't
> figure out how this is done with stored procedures in SMSS
>
>

How can you mass-validate stored procedures, views, etc?

I am looking for a way to validate all objects in the database that is is
possible for in some automated fashion. For instance, if a column is droppe
d
from a table stored procedures that rely on it will fail on the next
execution, but I want to be able to run a process that will reveal that prio
r
to execution. I would want to do that for views and user defined functions
as well. Other than executing every procedure and function and selecting
from every view does anyone know of a way to do this?
I thought I'd just script the objects out for ALTER and run that script
against the database but it appears you can only create an ALTER script one
object at a time through EM and with thousands of objects I really don't wan
t
to go through that.
I created a script that ran sp_recompile against every stored procedure,
then another block that executed every stored procedure without supplying an
y
parameters and that seemed to catch stored procedures that relied on missing
columns. The problem with that is it makes the assumption that the stored
procedure would either fail when no parameters were supplied, or would be
harmless when the stored procedure executed either because it required no
parameters or had defaults for all parameters. Of course this also only
worked for stored procedures and not the functions and views.
I made the mistake of changing a view without realizing that it was used in
a stored procedure and when that stored procedure executed it failed. You
can't depend on the dependencies that SQL maintains since they are not alway
s
correct, so you can't even make note of the dependent objects and selectivel
y
check only those objects without risking missing a dependency.
Any suggestions would be greatly appreciated.Byron (Byron@.discussions.microsoft.com) writes:
> I am looking for a way to validate all objects in the database that is
> is possible for in some automated fashion. For instance, if a column is
> dropped from a table stored procedures that rely on it will fail on the
> next execution, but I want to be able to run a process that will reveal
> that prior to execution. I would want to do that for views and user
> defined functions as well. Other than executing every procedure and
> function and selecting from every view does anyone know of a way to do
> this?
> I thought I'd just script the objects out for ALTER and run that script
> against the database but it appears you can only create an ALTER script
> one object at a time through EM and with thousands of objects I really
> don't want to go through that.
>...
This is indeed a difficult situation, and I don't really think there
is a single good way around it.
The method in the above paragraph is fairly easy to apply: use Generate
Scripts from Enterprise Manager to script the entire database. Then
open the generated script in a an editor and change CREATE PROCEDURE
to ALTER PROCEDURE.
However, you may not catch all errors this way. Say that you have:
INSERT #temp (...)
SELECT ..., missing_column
FROM ...
Because of deferred name resolution, SQL Server will not alert you of the
missing column. It sees that #temp is missing, and will defer compilation
of that statement until #temp has been created, and thus the error will
not occur until run-time.
The approach I use myself is based on the fact that we have all code
under version control. We also have a build tool that goes head over
heels to nullify the effect of deferred name resolution, by creating
all temp tables in an SP before loading the procedure. Thus a missing
column is noticed directly. Furthermore, it's easy for me to build an
empty database from SourceSafe, which will give me a complete sysdepends
that I can rely on.

> I created a script that ran sp_recompile against every stored procedure,
> then another block that executed every stored procedure without
> supplying any parameters and that seemed to catch stored procedures that
> relied on missing columns. The problem with that is it makes the
> assumption that the stored procedure would either fail when no
> parameters were supplied, or would be harmless when the stored procedure
> executed either because it required no parameters or had defaults for
> all parameters. Of course this also only worked for stored procedures
> and not the functions and views.
Our build-and-load tool is available for free download on
http://www.abaris.se/abaperls/. To get started with it, may be overmuch.
However, the toolset in includes a free-standing tool SPTRITEST which
performs essentially the above, but also provides dummy value for all
parameters, so it permits you test far more procedures. It goes without
saying that you should run this on a *copy* of the database, or else
you get a load of junk in it.
And it may still not find all cases of missing columns, since if there
are IF-ELSE branches, you are likely to pass over only one of them.
I also need to add the disclaimer that I wrote it in 6.5 days, and I
have not used it myself for ages.
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

How can you get the current database name via T-SQL?

I have stored some commonly used sprocs and user-defined functions I have created in a separate db.

Some of these functions work with the current db sysobjects table, for instance, and need to "know" the database that I am calling the sproc/function from.

Is there anyway to return the current database name via T-SQL to assign to a local variable which I can then include in the procedure/function execute statement?

Bill

Use the system function: db_name().

SELECT db_name()

|||

Code Snippet

select db_name()

|||

Thanks so much. I knew is was probably simple, just couldn;t find it in the T-SQL help.

How can we understand ?

How can we understand
- which stored procedures executes slowly ,
- which stored procedures needs more memory ,
- which indexes has much more mb
- which indexes are not used
- which indexes stay in memory
thanks for your answersUse Profiler
Madhivanan

How can take Back-Up and restore database from a web form wsing asp.net(vb.net)

I want to give a facility to backup database restore database to the customer in my project.
Database is stored in sql server 2000.
From the website the browser can take backup by clicking BACKUP button and
restore database by clicking RESTORE button.
is it possiable in asp.net programatically ?

In SQL Server you backup a database using the "backup database" command. You restore a database using the "restore database" commands. You can lookup the complete syntax to these commands.

You issue these SQL commands from asp.net just like you would any other SQL command. The backup command will work even if others are using the database at the moment you do the backup. The biggest problem would be the restore. If you are going to restore a database over an existing database (overwrite it) then that database must not be in use or you will get an error.

Brian

|||i do not really recommend this approach whether you can or not !!!!
this is an admin job and it is better not to have a wide access to do this kind of operation..... i am not sure about if you can do it or not ... !!|||Its definitely possible, take a look at this page on MSDNhttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp This details the TSQL commands that are necessary to perform the backup/restore. Once you create the commands just execute them with SqlClient.
Hope This Helps

How can Internal Activation use more resources?

Hi,

I'm using service broker queue with internal activation to run a stored procedure.
The DB server is windows 2003 R2, 4 cpu, with SQL server 2005 SP2.
When I'm runing the stored procedure directly from the sql management studio it takes about 75% of the cpu and running for about a minute, but when the stored procedure is activated by the queue internal activation (as a background process) it uses only 25% of the machine cpu (my guess it uses only 1 cpu insted of all 4 cpu) and running for much longer time (sometimes even more than one hour).
How can I change this behavior? I want it to run as fast as possible.

The queue decleration is:

CREATE QUEUE [TaskQueue]
WITH ACTIVATION (
STATUS = ON,
PROCEDURE_NAME = ProcessTasksProc,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF);

Thanks in advance,
Shai Brumer

If you have 4 CPU, why are you restricting the MAX_QUEUE_READERS to 1?

How did you measure this? Did you preload the queue with messages and run the procedure? Or are the messages continuosly incomming from some local or remote sender?

Note that activated procedures are looped for you by the activation mechanism, that is if the procedure exists and there are still message in the queue, it is invoked again. You will not see a separate Activation:End and Activationtart events for this, so it may look like is running for hours but in fact is contiuosly re-invoked because there are still message in the queue.

With only 1 activated proc, it is normal to consume only 25% of the 4 CPUs. The only way a procedure could use more than one CPUs (and push CPU utilization above 25%) would be to contain many queries that can be parallelized. Do you have anything in the procedure that could be heavilly paralelized and thus explain the 75% when launched from SSMS?

|||

My intention is to run the stored procedure asynchronously from another stored procedure, and for this I used service broker queues as async mechanism- when I want to run the procedure asynchronously I enter a message into the queue that is configured to active the procedure. The procedure is heavy and using all the cpu’s of the server (I can see parallelization in the execution plan and the server cpu is up to 75% when I’m running the procedure directly from the management studio).

I set up the MAX_QUEUE_READERS to 1 because I want a single procedure to run at any given time- but I want the procedure to take as much resource she need in order to complete the run as fast as it can.

Is this the normal behavior of internal activation? (Useing only 1 CPU).

Is there any way to change this?

|||Activation procedure should behave just as if the procedure was launched from SSMS. If you ahve plans that are heavily parallel, they should continue to be parallel and use 70% CPU. Can you compare the plans when run under activation vs. when run from SSMS?|||

How can I get the execution plan from the SSMS ?

|||

I ment:

How can I get the execution plan from the activation ?

|||Attach the Profiler and monitor the server and monitor the Performance/Showplan XML for Query Compile. The Profiler will show the plans just like SSMS does and each event/plan can be saved as an individual .sqlplan file (right click on the event and select 'Extract Data').|||

It seems the problem was the statistics of the table.

Thanks for the help.

Shai.

How can I use System.DirectoryServices in SQL 2005

I'm working on migrating webservices from VS 2003 to SQL Server 2005 by using VS 2005 DB project to create some Stored Procedure. When I want to add a component reference of "System.DirectoryServices " in to my project, I could not find it in the list. Is there a way to add this component in to my DB project or how can I access the Active Directory from the SQL 2005.

Thank you in advance,

Worachart C.

Worachart wrote:

I'm working on migrating webservices from VS 2003 to SQL Server 2005 by using VS 2005 DB project to create some Stored Procedure. When I want to add a component reference of "System.DirectoryServices " in to my project, I could not find it in the list. Is there a way to add this component in to my DB project or how can I access the Active Directory from the SQL 2005.

The reason you can not reference the dll from a Sql project in VS, is because the dll is not in the "approved list". In other words, certain system assemblies are considered to not be used from inside SQL Server, i.e. they can cause problems etc, or they have not been fully tested from inside SQL Server. Your directory services assembly is one of those.
However, you can register the assembly manually in SQL Server by running CREATE ASSEMBLY from Sql Server Management Studio, and after having done that, the assembly will be available to reference from your Sql Server project. Notice that you probably have to register the assembly as UNSAFE, which should make you stop and think what you are doing - so you do not call methods that can harm SQL Server.
Niels|||Thank you Niels to make me understand a lot better. I will learn more about whether I should use that class in my project or should I try with some other way.

POP

How can I use stored procedure's recordset output into simple SELECT statement?

Hi All
I have one stored procedure called MY_SP that returns a recordset. I would
like to use this recordset output into my SELECT statement like this:
SELECT * FROM (EXEC MY_SP '1', '2') RS
The above blurs up with syntax error at EXEC. Is there a way to achieve
this? (Note that MY_SP takes few parameters)
Thanks in advance.
NayanI don't understand why don't you just call EXEC MY_SP '1', '2'?
Perayu
"Nayan Mansinha" <nmansinha@.icode.com> wrote in message
news:%23ddMQp5RGHA.3972@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I have one stored procedure called MY_SP that returns a recordset. I
> would like to use this recordset output into my SELECT statement like
> this:
> SELECT * FROM (EXEC MY_SP '1', '2') RS
> The above blurs up with syntax error at EXEC. Is there a way to achieve
> this? (Note that MY_SP takes few parameters)
> Thanks in advance.
> Nayan
>|||thanks for asking
The example I have included is for the purpose of getting my problem across
to the audience. I agree with you that if I need to do something as simple
as quoted, I would rather do it your way. I'm actually trying to further
create a complex query using SELECT that will require data from this SP.
I hope I have made my question little more clearer.
TIA
Nayan
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:eJMwRI6RGHA.5900@.tk2msftngp13.phx.gbl...
>I don't understand why don't you just call EXEC MY_SP '1', '2'?
> Perayu
> "Nayan Mansinha" <nmansinha@.icode.com> wrote in message
> news:%23ddMQp5RGHA.3972@.TK2MSFTNGP10.phx.gbl...
>|||Use Temp table to store the resultset from MY_SP is one option.
Perayu
"Nayan Mansinha" <nmansinha@.icode.com> wrote in message
news:eC%235CQ6RGHA.4976@.TK2MSFTNGP11.phx.gbl...
> thanks for asking
> The example I have included is for the purpose of getting my problem
> across to the audience. I agree with you that if I need to do something
> as simple as quoted, I would rather do it your way. I'm actually trying
> to further create a complex query using SELECT that will require data from
> this SP.
> I hope I have made my question little more clearer.
> TIA
> Nayan
> "Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
> news:eJMwRI6RGHA.5900@.tk2msftngp13.phx.gbl...
>|||Thanks Perayu for your quick response.
The temp table option will work fine but what if I dont have permission to
modify the said SP? In that case, what would be my options?
thanks again for your ideas.
Nayan
"Perayu" <yu.he@.state.mn.us.Remove4Replay> wrote in message
news:OfZwUV6RGHA.5656@.TK2MSFTNGP11.phx.gbl...
> Use Temp table to store the resultset from MY_SP is one option.
> Perayu
> "Nayan Mansinha" <nmansinha@.icode.com> wrote in message
> news:eC%235CQ6RGHA.4976@.TK2MSFTNGP11.phx.gbl...
>|||On Tue, 14 Mar 2006 15:59:52 -0500, Nayan Mansinha wrote:

>Thanks Perayu for your quick response.
>The temp table option will work fine but what if I dont have permission to
>modify the said SP? In that case, what would be my options?
Hi Nayan,
You don't have to modify the SP. You can insert the results of the SP in
a tem table, then use that in your later queries:
CREATE TABLE #Reults
(Col1 some_datetype NOT NULL,
..)
INSERT INTO #Results (Col1, ...)
EXEC EXEC MY_SP '1', '2'
Hugo Kornelis, SQL Server MVP|||In light of your other posts, rewrite the stored procedure as a table
returning function and then you can easily use it in the FROM part of a
SELECT statement.|||Hi Hugo.
Thanks for replying.
I already did this earlier but wanted know if there is a simpler way that
can allow me to pipe-in the resultset from an SP without first creating the
table.
thanks again.
Nayan
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:r7le129b97pr04pcafqo0boti13qba0945@.
4ax.com...
> On Tue, 14 Mar 2006 15:59:52 -0500, Nayan Mansinha wrote:
>
> Hi Nayan,
> You don't have to modify the SP. You can insert the results of the SP in
> a tem table, then use that in your later queries:
> CREATE TABLE #Reults
> (Col1 some_datetype NOT NULL,
> ...)
> INSERT INTO #Results (Col1, ...)
> EXEC EXEC MY_SP '1', '2'
> --
> Hugo Kornelis, SQL Server MVP|||Hi JeffB
I understand your solution. What I'm looking at is how can I directly call
the stored procedure in the FROM part of the SELECT statement. I hope the
answer to this is not "NO" - cant use a stored procedure in the FROM part!
Thanks for the reply
Nayan
"JeffB" <jeff.bolton@.citigatehudson.com> wrote in message
news:1142374607.563351.82890@.i40g2000cwc.googlegroups.com...
> In light of your other posts, rewrite the stored procedure as a table
> returning function and then you can easily use it in the FROM part of a
> SELECT statement.
>|||I don't believe that you can. This is what a function that returns a
table is for.

How can I use SMO within a CLR stored procedure

Hi,

I have a CLR stored procedure that I wish to use SMO.

However this caused a number of problems :

1. Add reference didnt show SMO. So I tracked down a redistributable i.e. SQL Server 2005 Feature pack to install - still didnt show up. However, in a normal .net project they do show up so I erhm hacked the SQL server database project file to manually add the references.

Great have the reference, added the include statements - intellisense working code away - even builds :)

using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Smo;

2. Tried to deploy and came up with the error :

Error 1 Assembly 'microsoft.sqlserver.connectioninfo, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91.' was not found in the SQL catalog.

:(

Shot down in flames..

Would very much appreciate if someone could point me in the right direction to utilising SMO or point out where I am going wrong - other than dont hack the project file :)

Note the reason why I wish to use SMO is so I can read sql files from disk and execute them in the database i.e. create stored procedures etc.

The standard mechanism I have found i.e. a server connection and using ExectueNonQuery fails as the script files use batch seperators in the text i.e. 'GO'.

I have also tried using xp_cmdshell but any error reporting is virtually non existant other than parsing the output which will be truly horrific.

Open to suggestions :)SMO is not supported inside SQL Server. Normally what you can do in a scenario when a system dll (like SMO) isn't supported, is to manually catalog the dll in the database (that way it would show up in th VS SQL Server project when you try to reference it) and be able to use it.

In the case of SMO however it won't work period.

Niels|||Ah, thanks Niels.|||

Hi,

(warning: CLR newbie)

I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."

So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Any help is greatly appreciated.

Rocco

|||

Rocco Mastrangelo wrote:

Hi,

(warning: CLR newbie)

I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."

So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Yes, you have to run CREATE ASSEMBLY. Be aware that you probably have to create the assembly with a permission set of UNSAFE. This in turn requires certain permissions/settings in the database. See this thread for more info.

Niels

How can I use SMO within a CLR stored procedure

Hi,

I have a CLR stored procedure that I wish to use SMO.

However this caused a number of problems :

1. Add reference didnt show SMO. So I tracked down a redistributable i.e. SQL Server 2005 Feature pack to install - still didnt show up. However, in a normal .net project they do show up so I erhm hacked the SQL server database project file to manually add the references.

Great have the reference, added the include statements - intellisense working code away - even builds :)

using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Smo;

2. Tried to deploy and came up with the error :

Error 1 Assembly 'microsoft.sqlserver.connectioninfo, version=9.0.242.0, culture=neutral, publickeytoken=89845dcd8080cc91.' was not found in the SQL catalog.

:(

Shot down in flames..

Would very much appreciate if someone could point me in the right direction to utilising SMO or point out where I am going wrong - other than dont hack the project file :)

Note the reason why I wish to use SMO is so I can read sql files from disk and execute them in the database i.e. create stored procedures etc.

The standard mechanism I have found i.e. a server connection and using ExectueNonQuery fails as the script files use batch seperators in the text i.e. 'GO'.

I have also tried using xp_cmdshell but any error reporting is virtually non existant other than parsing the output which will be truly horrific.

Open to suggestions :)SMO is not supported inside SQL Server. Normally what you can do in a scenario when a system dll (like SMO) isn't supported, is to manually catalog the dll in the database (that way it would show up in th VS SQL Server project when you try to reference it) and be able to use it.

In the case of SMO however it won't work period.

Niels|||Ah, thanks Niels.|||

Hi,

(warning: CLR newbie)

I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."

So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Any help is greatly appreciated.

Rocco

|||

Rocco Mastrangelo wrote:

Hi,

(warning: CLR newbie)

I am trying to use Microsoft.Office.Interop.Excel within my CLR stored procedure. I added <Reference Include="Microsoft.Office.Interop.Excel" /> as an <ItemGroup> in the project file. That allowed me to have access to the namespace and build the stored procedure successfully. However, when I try to deploy the stored procedure, I get the same error as the initial post "Assembly 'microsoft.office.interop.excel, version=12.0.0.0, culture=neutral, publickeytoken=71e9bce111e9429c.' was not found in the SQL catalog."

So how do you "manually catalog the dll in the database" so that you can successfully deploy? Are you refering to the "CREATE ASSESSMBLY" command?

Yes, you have to run CREATE ASSEMBLY. Be aware that you probably have to create the assembly with a permission set of UNSAFE. This in turn requires certain permissions/settings in the database. See this thread for more info.

Niels

How can I use several resultsets from stored procedure?

I have create stored procedure that returns 2 resultsets. When I configure OLE DB Source to use this procedure I can not add second output for he source. I get following error:

TITLE: Microsoft Visual Studio

Error at Data Flow Task [OLE DB Source [1]]: An output cannot be added to the outputs collection.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC020800F (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

How to add second output?

The stock OLEDB source component supports one non-error output.

To populate multiple outputs from a source component (perhaps to avoid a double extract), you can write a script source transform with as many outputs as necessary. Building multi output transforms (script and custom) is not much more difficult than a single output source component.

Furthermore, you can write a custom source tranform if you wish to re-use the code (aka toolbox resident) more easily than cut/paste re-use of script components.

If a little coding is not up your alley, then you're stuck with a single output.

Of course there are probably workarounds to simulate two outputs, such as interleaving/marking the columns of the two result sets such that they form one result set you can later conditionally split, but that is likely beside the point.

how can i use COUNT in my stored procedure?

hello,

i have a list of Categories and each one contains a list of SubCategories, and i use a nested Repeater to show each Category with their SubCategories, but because are to many to show verticaly, i want to make 2 nested repeaters and in the first one to show only the first "n" Categories with their SubCategories and in the second Repeater should be the last n Categories, and like so they will be in 2 colums

How can i use the COUNT function in my stored procedure to take only first n Categories?

I used SELECT * FROM Categories WHERE CategoryID <= 5 but i don't want to order by the primary key i want to order by a COUNT or something like this...

here is my stored procedure (i use it for two nested Repeaters)

ALTER PROCEDURESubCategoriiInCategorii

CategoryIDint)

AS

SELECTCategoryID,Name, DescriptionFROMCategoriesWHERECategoryID = @.CategoryID

ORDER BYName

SELECTp.SubCategoryID, p.Name,p.CategoryIDFROMSubCategorii p

ORDER BYp.Name

i hope you understand what i mean, thank you

Hi,

Have a look at this threadhttp://forums.asp.net/t/1138145.aspx it will tell you how to get N first rows or in otherwords N top rows.

Hope it will help you out.

Thanks and best regards,

|||

if i use the stored procedures from there i get error from the nested repeater...

i tryied this, but i get an error: invalid column name RowNumber ...why the alias don't work?

SELECTROW_NUMBER()OVER(ORDER BYCategoryID)ASRowNumber, CategoryID,Name, DescriptionFROMCategorii

WHEREDepartamentID = @.DepartamentIDANDRowNumber = 3

|||

Hi,

Actually I don't exactly get what are you trying to do in the above stored procedure.

Thanks and best regards,

|||

if i execute the stored procedure without the "AND" part, in the RowNumber i have values from 1 to 9 (counts how many CategoriyID's i have)

i think it work, try to execute the procedure

thank you

i just resolved it, here is the stored procedure

SELECT*FROM(

SELECTROW_NUMBER()OVER(ORDER BYCategoryID)ASRowNumber, CategoryID,Name, DescriptionFROMCategoriiWHEREDepartamentID = @.DepartamentID

)ASMyTable

WHERERowNumber <=5

i think is good for what i need, thank you again for help

How can i use a for loop in a query ?

hi all i have the following stored procedure.

declare @.AreaID int

select @.AreaID = 1

select DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT

from
table1 INNER JOIN
table2 ON table1.JobId = table2.JobEvent_JobId
where

(table1.Job_AreaId = @.AREAID)

the there are about 300 AREAID's so my question is do i do a "LOOP" or is that no efficient and chews up resources on the server ?
what would be a nice clean way to do this ?

thanks
robby

Are you asking how to iterate over the results returned from your stored procedure?

depending on how you retrieve you results either from a data reader or you used a data adapter and filled it to a dataset

//if filled to a dataset you can do thisif (MyDataSet.Tables.Count != 0){foreach (DataRow rowin MyDataSet.Tables[0].Rows) {//now you can access each row returned }}//if using a data readerwhile (DataReader.Read()){//hear you can access each row }

|||

Hi,

if i lets say hardcode the areaid to lets say 1 i get a set of results. i want to next get the results for areaid 2 so as if i want to runt the stored procedure again. But can i lets say have some sort of loop which will pass the parameter (areaid ) into the stored procedure and get the results in c# ?

that would be i think a bit too long winded ... is that right ?

|||

Sorry, but I do not understand what you want.

|||

What I understood from the problem is that you want to execute the same query for around 300 AREAID.
If I am correct following query will solve your problem:

SELECT table1.Job_AreaId, DATEPART(hh, dbo.JobEvent.JobEventDateTime) AS hourbracket, count(ID) as NUMCOUNT
from table1 INNER JOIN table2 ON table1.JobId = table2.JobEvent_JobId
where table1.Job_AreaId in
(
SELECT the list of distinct AreaId for ex: select AreaId from MasterTable
)
Group by table1.Job_AreaId

Hope this helps you!!!

|||

Hi ,

Thanks for the response . It sure did help much appreciated

robby