Load data from text file into some table implemented in stored procedure.

Hello, I want to load data from text file to MS SQL DB table.

In MySQL, it is the "LOAD DATA INFILE..." query statement.

What is sutable query if I want to migration from Mysql to MS SQL Server 2005 Express?

I think you need some third party tool, which will do the needed convertions. Executing query form a file you need to read the SQLCMD form BOL, this is command line utility.

|||hi remedios,

you can use the bcp(Bulk Copy Program) to basically do the same as in MySql. or if not why not use DTS in Enterprise Manager and select the text file as the Source

hth

Load assembly from Stored Procedure

Hi,
Is it possible to load an assembly either from a byte array or from the disk at runtime from a CLR stored procedure.

I have created a stored procedure that needs to load different assemblies depending on a query and then invokes a method (called execute() passing a string query as a parameter).
From debugging the CLR stored procedure in .NET I have found that it is this line that creates an error:

Assembly myAssembly= Assembly.Load()

It doesnt matter if I attempt to load the assembly from it's byte array or from a file location, it always gives the following error:

Msg 6522, Level 16, State 1, Procedure runQuery, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'runQuery':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[] rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly)
at ClientInterface.RunInterface(String queryName, String parameters, String& returnValue)
It works perfectly if I run it using a mock up front end app it just seems to be sql server that doesnt like it.

Anybody have any ideas?
Assembly.Load() should work, BUT the assembly you are loading has to be catalogued in the database.

You are not allowed to load assemblies from anything but the database, that's why you are getting the error you see above: A .NET Framework error occurred... have been disabled by the host."

Niels
|||Thanks for replying Niel.

I will try loading the assemblies into the database and use Assembly.Load(assemblyName) to get it working.

N
sql

Load assembly from Stored Procedure

Hi,

Is it possible to load an assembly either from a byte array or from the disk at runtime from a CLR stored procedure.

I

have created a stored procedure that needs to load different assemblies

depending on a query and then invokes a method (called execute() passing a string query as a parameter).

From debugging the CLR stored procedure in .NET I have found that it is this line that creates an error:

Assembly myAssembly= Assembly.Load()

It

doesnt matter if I attempt to load the assembly from it's byte array or

from a file location, it always gives the following error:

Msg 6522, Level 16, State 1, Procedure runQuery, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'runQuery':
System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host.
System.IO.FileLoadException:

at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[]

rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark,

Boolean fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly)
at ClientInterface.RunInterface(String queryName, String parameters, String& returnValue)

It works perfectly if I run it using a mock up front end app it just seems to be sql server that doesnt like it.

Anybody have any ideas?

Assembly.Load() should work, BUT the assembly you are loading has to be catalogued in the database.

You are not allowed to load assemblies from anything but the database, that's why you are getting the error you see above: A .NET Framework error occurred... have been disabled by the host."

Niels|||Thanks for replying Niel.

I will try loading the assemblies into the database and use Assembly.Load(assemblyName) to get it working.

N

Little Lock Symbols

WE are new to SQL Server 2005. My team memebers and I have the same login-in
as a group. One member developed some stored procedure code that I am trying
to review , but there are thse little lock symbols on the file icon. how can
I see the file?
Do you mean when you open the file in Management Studio, the tab at the top
has a lock icon? This means the file is read-only, possibly because it is
checked into source control. If this is not what you are talking about,
then you can explain (a) where you see the lock, and (b) what it means that
you can't "see" the file?
A
"Candyman" <Candyman@.discussions.microsoft.com> wrote in message
news:F8A96B55-7ED2-4913-94EF-FB215804AE17@.microsoft.com...
> WE are new to SQL Server 2005. My team memebers and I have the same
> login-in
> as a group. One member developed some stored procedure code that I am
> trying
> to review , but there are thse little lock symbols on the file icon. how
> can
> I see the file?
|||WE are in Management Studio. On the left hand side is the Obkect Explorer,
We have many listings under databases. The tree structure looks like
Databases\db_ReportSource\Programmability\Stored Procedures. . . Then there
is a file dbo.SQL_myquery listed and the adjacent icon looks like a light
blue piece of paper with a darker blue stripe at the top. Then there is a
little yelow lock on the right lower corner of the icon. My team mate can
right click on the file and see a Modify option to show me the code. That
Modify option is greyed out to me. ( and four other procs he created.) How do
I see the code? Is there another way to get to it?
"Aaron Bertrand [SQL Server MVP]" wrote:

> Do you mean when you open the file in Management Studio, the tab at the top
> has a lock icon? This means the file is read-only, possibly because it is
> checked into source control. If this is not what you are talking about,
> then you can explain (a) where you see the lock, and (b) what it means that
> you can't "see" the file?
> A
> "Candyman" <Candyman@.discussions.microsoft.com> wrote in message
> news:F8A96B55-7ED2-4913-94EF-FB215804AE17@.microsoft.com...
>
>
|||If you just want to see it and not modify it, then:
(1) Right-click the database name and select new query
(2) Make sure you are in Results to Text (Ctrl+T)
(3) Type:
EXEC sp_helptext 'SQL_myquery';
(4) Hit F5 (or the Execute button on the toolbar)
"Candyman" <Candyman@.discussions.microsoft.com> wrote in message
news:119BA461-C65C-4755-83CA-E7EF1C4CAE3B@.microsoft.com...[vbcol=seagreen]
> WE are in Management Studio. On the left hand side is the Obkect
> Explorer,
> We have many listings under databases. The tree structure looks like
> Databases\db_ReportSource\Programmability\Stored Procedures. . . Then
> there
> is a file dbo.SQL_myquery listed and the adjacent icon looks like a light
> blue piece of paper with a darker blue stripe at the top. Then there is a
> little yelow lock on the right lower corner of the icon. My team mate can
> right click on the file and see a Modify option to show me the code. That
> Modify option is greyed out to me. ( and four other procs he created.) How
> do
> I see the code? Is there another way to get to it?
> "Aaron Bertrand [SQL Server MVP]" wrote:
|||if I 'check (green check)' it, the programs returns: "query excecuted
successfully".
Then when I try to run as suggested the command errors out saying "There is
no text for object 'SQL_myquery'"
Could the file actuallly be residing on the other users machine and not in
SQL Server? or network?
"Aaron Bertrand [SQL Server MVP]" wrote:

> If you just want to see it and not modify it, then:
> (1) Right-click the database name and select new query
> (2) Make sure you are in Results to Text (Ctrl+T)
> (3) Type:
> EXEC sp_helptext 'SQL_myquery';
> (4) Hit F5 (or the Execute button on the toolbar)
>
>
> "Candyman" <Candyman@.discussions.microsoft.com> wrote in message
> news:119BA461-C65C-4755-83CA-E7EF1C4CAE3B@.microsoft.com...
>
>
|||> if I 'check (green check)' it, the programs returns: "query excecuted
> successfully".
Yes, if you hold your mouse over the green check you decided to click, you
will see that means "parse" (syntax check). The success message means that
the command didn't have any syntax errors.

> Then when I try to run as suggested the command errors out saying "There
> is
> no text for object 'SQL_myquery'"
Are you sure your query is running in the correct database? When you
right-clicked "the database" was it db_ReportSource, or some other database?
Are you sure you were in an Object Explorer context of the correct server?
What happens when you execute the following in a properly created New Query
window:
USE db_ReportSource;
GO
EXEC SQL_myquery;
GO
EXEC dbo.SQL_myquery;
GO
?

> Could the file actuallly be residing on the other users machine and not in
> SQL Server? or network?
No, from your earlier description, this is a stored procedure in a database.
There are many differences between a stored procedure in the database and a
file in the file system. They are certainly not the same, and a file
somewhere on your network is certainly not going to magically appear under
the stored procedures node in object explorer.
A
|||Candyman (Candyman@.discussions.microsoft.com) writes:
> if I 'check (green check)' it, the programs returns: "query excecuted
> successfully".
> Then when I try to run as suggested the command errors out saying "There
> is no text for object 'SQL_myquery'"
> Could the file actuallly be residing on the other users machine and not in
> SQL Server? or network?
First of all, it is not a file. It's an object in a database, and there
should indeed be a file for it in the file system, or even better in
the version-control system. But if you co-worker just created the procedure
in a query window, and then closed the window without saving it, there is
no file at all. Just an object in a database.
Normally, though, stored procedures can be disassembled from the database
so you can edit them, which is popular in teams that don't believe in
files or version-control systems.
But this small lock indicates that you can't. There are two possible reasons
for this:
o The procedure was created with the clause WITH ENCRYPTION.
o It is a stored procedure created in a CLR language such as VB .Net or C#.
If you right-click the procedure and selecr Properties, you might be
able to deduce which of the cases it is. If there things like "Property
AnsiNullsStatus is not available..." it is a CLR stored procedure.
In either cases, you will have to ask your co-worker where he has the
source code.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||The application asked me for some date parameters, which I enterd and
recieved results back so there is something there, but I still can't see the
original code.
Erland might have hit it on found the solution as the file properties do
seem to be encrypted. This is something we will have to discuss withthe
group. Thanks so much for your input and patience.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, if you hold your mouse over the green check you decided to click, you
> will see that means "parse" (syntax check). The success message means that
> the command didn't have any syntax errors.
>
> Are you sure your query is running in the correct database? When you
> right-clicked "the database" was it db_ReportSource, or some other database?
> Are you sure you were in an Object Explorer context of the correct server?
> What happens when you execute the following in a properly created New Query
> window:
> USE db_ReportSource;
> GO
> EXEC SQL_myquery;
> GO
> EXEC dbo.SQL_myquery;
> GO
> ?
>
> No, from your earlier description, this is a stored procedure in a database.
> There are many differences between a stored procedure in the database and a
> file in the file system. They are certainly not the same, and a file
> somewhere on your network is certainly not going to magically appear under
> the stored procedures node in object explorer.
> A
>
>
|||BINGO!
Under properties in the Options section the file has Encrypted set to TRUE
and Replication set to FALSE also ( I guess which means I could not copy the
file which I tried to do also.)
We are baby new to this and I have to bring this up to the author of the
code and to our new “Weekly user group” . Both of which are out today, but
thank you for the info! This is helpful.
I will also suggest we start using Source Safe for version control.
Thanks!
"Erland Sommarskog" wrote:

> Candyman (Candyman@.discussions.microsoft.com) writes:
> First of all, it is not a file. It's an object in a database, and there
> should indeed be a file for it in the file system, or even better in
> the version-control system. But if you co-worker just created the procedure
> in a query window, and then closed the window without saving it, there is
> no file at all. Just an object in a database.
> Normally, though, stored procedures can be disassembled from the database
> so you can edit them, which is popular in teams that don't believe in
> files or version-control systems.
> But this small lock indicates that you can't. There are two possible reasons
> for this:
> o The procedure was created with the clause WITH ENCRYPTION.
> o It is a stored procedure created in a CLR language such as VB .Net or C#.
> If you right-click the procedure and selecr Properties, you might be
> able to deduce which of the cases it is. If there things like "Property
> AnsiNullsStatus is not available..." it is a CLR stored procedure.
> In either cases, you will have to ask your co-worker where he has the
> source code.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
|||> Under properties in the Options section the file has Encrypted set to TRUE
> and Replication set to FALSE also ( I guess which means I could not copy
> the
> file which I tried to do also.)
No, replication and copying a file are not the same thing.
A

little help on delete stored procedure

I created a insert stored procedure but It was not working correctly
Could you correct the code?
I am trying to insert contract information on contract table but before that I want to check the studentID in student table and contactId in contact table if they exist I want to insert into the contract table

Please help!

************************************************** ***
My contrat DDL is follows

create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);

************************************************** ***
My insert stored procedure is follows

create proc sp_insert_new_contract
( @.contractDate [smalldatetime],
@.tuition [money],
@.studentId [char],
@.contactId [int])
as
if exists (select s.studentId, c.contactId
from student s, contact c
where @.contactId = contactId
and @.studentId = studentId)


begin
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@.contractDate,
@.tuition,
@.studentId,
@.contactId)

end
else
print 'studentId and contactId are not valid, please try another studnetId and contactId'
goSyntactically, it should be:

create proc sp_insert_new_contract
( @.contractDate [smalldatetime],
@.tuition [money],
@.studentId [char] (4),
@.contactId [int])
as
if exists (select s.studentId, c.contactId
from student s, contact c
where @.contactId = contactId
and @.studentId = s.studentId)
...

But logically, you're also missing a validity of JUST a student id:

declare @.student_check char(4)
select @.student_check = max(studentid) from student where studentid = @.studentid
if @.student_check is null begin
print 'Invalid studentid specified'
return (1)
end

if exists (select 1 from contact
where @.contactId = contactId
and @.studentId = @.studentId) begin
print 'Specified studentid/contractid already exist in the table!'
return (1)
end
...
After then you can go on with the rest of your procedure.

lists through sp_helpsrvrolemember return unexpected results

When running the stored procedure sp_helpsrvrolemember to return a list of
users with 'sysadmin' permissions, my result set was inconsistent. Users tha
t
had the permissions removed weeks ago still showed up, however, when running
the query hours later, the results set changed and did not reflect the old
permissions.
Is this a known bug, a cache issue that needs restarts?
(This is a SQL 2000 sp4 instance)
DougCan you recreate that with a test database? I'm not able to do that here on
my system.
"Doug Olson" wrote:

> When running the stored procedure sp_helpsrvrolemember to return a list of
> users with 'sysadmin' permissions, my result set was inconsistent. Users t
hat
> had the permissions removed weeks ago still showed up, however, when runni
ng
> the query hours later, the results set changed and did not reflect the old
> permissions.
> Is this a known bug, a cache issue that needs restarts?
> (This is a SQL 2000 sp4 instance)
> Doug|||I haven't been able to exactly re-create it, however, I have received some
communication/feedback that this is related to a ghosting issue/problem.
Doug
"Buck Woody - Microsoft SQL Server Team" wrote:
[vbcol=seagreen]
> Can you recreate that with a test database? I'm not able to do that here o
n
> my system.
> "Doug Olson" wrote:
>|||Good stuff - so do you have this solved?
"Doug Olson" wrote:
[vbcol=seagreen]
> I haven't been able to exactly re-create it, however, I have received some
> communication/feedback that this is related to a ghosting issue/problem.
> Doug
> "Buck Woody - Microsoft SQL Server Team" wrote:
>

Listing permissions on Stored Procedures via query.

How do I list the permissions on a stored procedure with a query?
I'm trying to remove Public from some xp_* procedures but I'd like to see
what Public is assigned to first.
Thanks much.Look up the system procedure sp_helprotect in SQL Server Books Online.
Anith|||"Horst" <Horst@.discussions.microsoft.com> wrote in message
news:6E119626-412D-494C-8F42-FA999C485625@.microsoft.com...
> How do I list the permissions on a stored procedure with a query?
> I'm trying to remove Public from some xp_* procedures but I'd like to see
> what Public is assigned to first.
>
> Thanks much.
You could reverse engineer the sp_helprotect sproc, but this should work
for you as well.
--
USE master
GO
CREATE TABLE #Foo (
Owner sysname,
Object sysname,
Grantee sysname,
Grantor sysname,
ProtectType varchar(50),
Action varchar(50),
[Column] sysname NULL
)
INSERT #Foo
EXEC sp_helprotect
SELECT *
FROM #Foo
WHERE OBJECT LIKE 'xp%'
AND Grantee = 'public'
DROP TABLE #Foo
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks for the help.
"Rick Sawtell" wrote:

> "Horst" <Horst@.discussions.microsoft.com> wrote in message
> news:6E119626-412D-494C-8F42-FA999C485625@.microsoft.com...
> You could reverse engineer the sp_helprotect sproc, but this should work
> for you as well.
> --
> USE master
> GO
>
> CREATE TABLE #Foo (
> Owner sysname,
> Object sysname,
> Grantee sysname,
> Grantor sysname,
> ProtectType varchar(50),
> Action varchar(50),
> [Column] sysname NULL
> )
> INSERT #Foo
> EXEC sp_helprotect
> SELECT *
> FROM #Foo
> WHERE OBJECT LIKE 'xp%'
> AND Grantee = 'public'
> DROP TABLE #Foo
> --
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||Thanks for the help.
"Anith Sen" wrote:

> Look up the system procedure sp_helprotect in SQL Server Books Online.
> --
> Anith
>
>

Listing of all Stored Procedures

Where and how can I list all stored procedures listed in a specific database?
Thank you.
Hello,
The below system procedure can be used to list all procedures in a database
sp_stored_procedures
Thanks
Hari
"Terry" <Terry@.discussions.microsoft.com> wrote in message
news:5D259ABB-FC93-4467-BFE1-082039AD26BE@.microsoft.com...
> Where and how can I list all stored procedures listed in a specific
> database?
> Thank you.
|||Thank you very much!
"Hugo Kornelis" wrote:

> On Wed, 3 Jan 2007 08:16:00 -0800, Terry wrote:
>
> Hi Terry,
> For SQL Server 2005:
> SELECT name
> FROM sys.procedures;
> For SQL Server 2000:
> SELECT name
> FROM sysobjects
> WHERE type = 'P'
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>

listing columns in view and stored procedures

Using SS2000 SP4. If I create a view and list the columns in the select
statement, if I then create a sp using the view is it better to list the
columns again or just use '*'?
Thanks,
--
Dan D.Do not use *. You will not know what you are selecting if your view
definition is changed.
"Dan D." wrote:

> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||You will get varying opinions here, especially since we don't know what your
definition of "better" is.
My opinion:
*ALWAYS* list your columns in production code, and never use SELECT *.
Too many things can go wrong throughout the pipeline.
A
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||Good point. Thanks.
--
Dan D.
"Omnibuzz" wrote:
> Do not use *. You will not know what you are selecting if your view
> definition is changed.
> --
>
>
> "Dan D." wrote:
>|||That's how I feel. I saw a piece of code that was written using '*' and
wondered what other people thought.
Thanks,
--
Dan D.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You will get varying opinions here, especially since we don't know what yo
ur
> definition of "better" is.
> My opinion:
> *ALWAYS* list your columns in production code, and never use SELECT *.
> Too many things can go wrong throughout the pipeline.
> A
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
>
>|||The person that wrote this should be shackled and whipped.
Since this is probably illegal in most states, provinces and countries...
He should at least be forced to write 100 times on a blackboard:
I shall never use "Select *" in production code again.
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:AA233A05-4BEE-4A90-B97C-DFAFFAF37006@.microsoft.com...
> That's how I feel. I saw a piece of code that was written using '*' and
> wondered what other people thought.
> Thanks,
> --
> Dan D.
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>|||I'll see if I can track him/her down.:)
--
Dan D.
"Raymond D'Anjou" wrote:

> The person that wrote this should be shackled and whipped.
> Since this is probably illegal in most states, provinces and countries...
> He should at least be forced to write 100 times on a blackboard:
> I shall never use "Select *" in production code again.
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:AA233A05-4BEE-4A90-B97C-DFAFFAF37006@.microsoft.com...
>
>|||To add to the topic.
We now have a new company standard.. that you list ALL FIELDS in your INSERT
statements.
For Example:
at one point , an Emp table has EmpID, LastName, FirstName columns.
We had code like this
INSERT INTO Emp Values (101, 'Smith', 'John')
...
Why is this bad'
Someone adds a new column
Emp.Age.
Now every INSERT fails. Because the table has 4 columns, and the INSERT
supplies 3.
..
I can't tell you how many bugs I've tracked down with that (stupid) issue.
ALWAYS use a list. If it wasn't for quick debugging , Select * should be
outlawed! (Maybe a little extreme, but it can cause alot of issues)
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
> Using SS2000 SP4. If I create a view and list the columns in the select
> statement, if I then create a sp using the view is it better to list the
> columns again or just use '*'?
> Thanks,
> --
> Dan D.|||Thanks for your 2cents Sloan.
--
Dan D.
"sloan" wrote:

> To add to the topic.
> We now have a new company standard.. that you list ALL FIELDS in your INSE
RT
> statements.
> For Example:
> at one point , an Emp table has EmpID, LastName, FirstName columns.
> We had code like this
> INSERT INTO Emp Values (101, 'Smith', 'John')
> ...
> Why is this bad'
> Someone adds a new column
> Emp.Age.
> Now every INSERT fails. Because the table has 4 columns, and the INSERT
> supplies 3.
> ...
> I can't tell you how many bugs I've tracked down with that (stupid) issue.
> ALWAYS use a list. If it wasn't for quick debugging , Select * should be
> outlawed! (Maybe a little extreme, but it can cause alot of issues)
>
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:E10EEF85-B534-4887-81D8-159967A83E9F@.microsoft.com...
>
>|||On Mon, 1 May 2006 10:23:01 -0700, Dan D.
<DanD@.discussions.microsoft.com> wrote:

>Using SS2000 SP4. If I create a view and list the columns in the select
>statement, if I then create a sp using the view is it better to list the
>columns again or just use '*'?
>Thanks,
Aaron promised you varying opinions, but everyone was taking the same
side, so I figure it was time to add my two cents.
I consider * to be a very valuable tool in the SELECT list, and
prefer it in many situations. In general I prefer * when the query
MUST include EVERY column from a table. It avoids the possible error
of leaving a column out, and it enforces a uniform sequence to the
columns that can't hurt.
Example: A view that has to include every column from a table, plus
other columns:
SELECT X.*, Y.SomeCol
If table X changes, all the is needed is to ALTER the view (with no
changes) to force a recompile.
When there are two tables with identical layouts and rows are inserted
from one into the other:
INSERT X
SELECT * FROM Y
It is very hard to get that wrong. Again, if the tables change all
that is required is a recompile, removing one more chance to make an
error. If only one of the tables change the recompile will fail,
which is a Good Thing as the issue of how to deal with the change was
not addressed, and needs to be. I prefer such a failure to having the
difference remain unadressed.
Roy Harvey
Beacon Falls, CT

Listing AS stored procedures

Is there a way to list the UDFs/stored procedures in a deployed assembly?

I donno, if I really understood your question...

TO list the stroedprocedures and Functions, you can use as follows

for StoreProcedures

select name

from sys.objects

where type in (N'P', N'PC')

for Functions

select name

from sys.objects

where type in (N'FN', N'IF', N'TF', N'FS', N'FT')

|||

This works nicely in SQL Server database. How would you do the same thing in Analysis Services?

In AS you can design CLR UDFs and stored procedures and deploy them in assemblies at database or at server level. Once you have more than a handful of them deployed, you would want to be able to enumerate them without having to open the source code. How else would you make them available to everyone writing MDX against the cubes in the corresponding database?

I have looked at XMLA and schema rowsets and I do not see a way to do it.

|||There is no schema row-set way to enumerate those, but if you have the assemblies on your drive you could run ildasm utility and browse the assembly for public classes and methods. Those can be used as stored procedures.

If you do not have the copies of the assemblies on your drive, there is a way to get them from the server. I forgot how to do it but i will post another reply soon (unless somebody will answer). Of course, it will work if you have permissions to get the binary back from the server.

Listing all scheduled tasks on a server

I am trying to use

sp_help_jobstep

to detail the scheduled tasks on my server.

However I get an error saying the stored proc does not exist. Is it not installed by default or is it possibler it was deleted somehow?

Is there another way I can extract all my scheduled tasks so that I can produce a SQL script to automate their re-creation if they get deleted?

I am using SQL 7 and got this SP from bol.

ThanksOriginally posted by FunkyD
I am trying to use

sp_help_jobstep

to detail the scheduled tasks on my server.

However I get an error saying the stored proc does not exist. Is it not installed by default or is it possibler it was deleted somehow?

Is there another way I can extract all my scheduled tasks so that I can produce a SQL script to automate their re-creation if they get deleted?

I am using SQL 7 and got this SP from bol.

Thanks

Fixed it - forgot that you have to run against MSDB...lol

Listening for report events

I have a series of reports and want to fire off a stored procedure to update
a "Printed date" column in my database. I don't want to do this from the
dataset that populates my datset as I only want this to occur if the report
is successfully generated. Is there any way I can do this with the scheduler?
If not, are the any report events that I can listen for? And or any other
solution to this problem.Sorry. I don't think I made my intentions very clear. I meant to say that I
have a series of report, and want to fire off a stored procedure everytime a
report is successfully generated.
"Graham Hope" wrote:
> I have a series of reports and want to fire off a stored procedure to update
> a "Printed date" column in my database. I don't want to do this from the
> dataset that populates my datset as I only want this to occur if the report
> is successfully generated. Is there any way I can do this with the scheduler?
> If not, are the any report events that I can listen for? And or any other
> solution to this problem.

Liste Tables

bonjour,
Existe t il une stored proc qui permette de récupérer la liste des tables
d'une base de donnée Sql 2K ?
Et si oui peut on ensuite récupérer la structure d'une table ; liste de
champs, d'index, de déclencheurs, contraints...etc
Merci
Christophe.There are many ways, pl. search the achives of this newsgroup. Here is an
easy one:
EXEC sp_tables
For keys, you can do:
EXEC sp_pkey 'tbl'
--
- Anith
( Please reply to newsgroups only )|||Sorry for this 'french' post, and Thanks for help !
Regards
Christophe
"meynet" <meynet@.csprogramme.com> a écrit dans le message de
news:bjq00r$670$1@.news-reader4.wanadoo.fr...
> bonjour,
> Existe t il une stored proc qui permette de récupérer la liste des tables
> d'une base de donnée Sql 2K ?
> Et si oui peut on ensuite récupérer la structure d'une table ; liste de
> champs, d'index, de déclencheurs, contraints...etc
> Merci
> Christophe.
>|||merci beaucoup, Vishal !
--
- Anith
( Please reply to newsgroups only )|||This is a multi-part message in MIME format.
--=_NextPart_000_03B7_01C37881.C6A60830
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You should have left this one to Aaron then ;-)
-- Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uI4R99GeDHA.2236@.TK2MSFTNGP12.phx.gbl...
My French is weak but I think you need sp_help.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"meynet" <meynet@.csprogramme.com> wrote in message =news:bjq00r$670$1@.news-reader4.wanadoo.fr...
bonjour,
Existe t il une stored proc qui permette de r=E9cup=E9rer la liste des =tables
d'une base de donn=E9e Sql 2K ?
Et si oui peut on ensuite r=E9cup=E9rer la structure d'une table ; =liste de
champs, d'index, de d=E9clencheurs, contraints...etc
Merci
Christophe.
--=_NextPart_000_03B7_01C37881.C6A60830
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV><FONT face=3DArial size=3D2>You should have left this one to Aaron =then ;-)</FONT></DIV>
<DIV><BR>-- <BR>Jacco Schalkwijk MCDBA, MCSD, MCSE<BR>Database Administrator<BR>Eurostop Ltd.</DIV>
<DIV> </DIV>
<DIV> </DIV>
<BLOCKQUOTE style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Tom Moreau" <<A =href=3D"mailto:tom@.dont.spam.me.cips.ca">tom@.dont.spam.me.cips.ca</A>>= wrote in message <A =href=3D"news:uI4R99GeDHA.2236@.TK2MSFTNGP12.phx.gbl">news:uI4R99GeDHA.2236=@.TK2MSFTNGP12.phx.gbl</A>...</DIV>
<DIV><FONT face=3DTahoma size=3D2>My French is weak but I think you =need sp_help.</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
=<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A =href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"meynet" <<A href=3D"mailto:meynet@.csprogramme.com">meynet@.csprogramme.com</A>> =wrote in message <A =href=3D"news:bjq00r$670$1@.news-reader4.wanadoo.fr">news:bjq00r$670$1@.news=-reader4.wanadoo.fr</A>...</DIV>bonjour,<BR><BR>Existe t il une stored proc qui permette de r=E9cup=E9rer la liste des =tables<BR>d'une base de donn=E9e Sql 2K ?<BR>Et si oui peut on ensuite r=E9cup=E9rer =la structure d'une table ; liste de<BR>champs, d'index, de d=E9clencheurs, =contraints...etc<BR><BR>Merci<BR>Christophe.<BR><BR></BLOCKQUOTE></BODY><=/HTML>
--=_NextPart_000_03B7_01C37881.C6A60830--|||This is a multi-part message in MIME format.
--=_NextPart_000_02A5_01C37857.EFA1EBC0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Y'know, just 'cuz we're Canadian, does mean we speak French, eh? ;-)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message =news:OTFK4jHeDHA.3024@.tk2msftngp13.phx.gbl...
You should have left this one to Aaron then ;-)
-- Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uI4R99GeDHA.2236@.TK2MSFTNGP12.phx.gbl...
My French is weak but I think you need sp_help.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"meynet" <meynet@.csprogramme.com> wrote in message =news:bjq00r$670$1@.news-reader4.wanadoo.fr...
bonjour,
Existe t il une stored proc qui permette de r=E9cup=E9rer la liste des =tables
d'une base de donn=E9e Sql 2K ?
Et si oui peut on ensuite r=E9cup=E9rer la structure d'une table ; =liste de
champs, d'index, de d=E9clencheurs, contraints...etc
Merci
Christophe.
--=_NextPart_000_02A5_01C37857.EFA1EBC0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =charset=3Dwindows-1252">
<META content=3D"MSHTML 6.00.2800.1226" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#c0c0c0>
<DIV><FONT face=3DTahoma size=3D2>Y'know, just 'cuz we're Canadian, does =mean we speak French, eh? ;-)</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"Jacco Schalkwijk" <<A href=3D"mailto:NOSPAMjaccos@.eurostop.co.uk">NOSPAMjaccos@.eurostop.co.uk</=A>> wrote in message <A href=3D"news:OTFK4jHeDHA.3024@.tk2msftngp13.phx.gbl">news:OTFK4jHeDHA.3024=@.tk2msftngp13.phx.gbl</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>You should have left this one to Aaron =then ;-)</FONT></DIV>
<DIV><BR>-- <BR>Jacco Schalkwijk MCDBA, MCSD, MCSE<BR>Database Administrator<BR>Eurostop Ltd.</DIV>
<DIV> </DIV>
<DIV> </DIV>
<BLOCKQUOTE style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Tom Moreau" <<A =href=3D"mailto:tom@.dont.spam.me.cips.ca">tom@.dont.spam.me.cips.ca</A>>= wrote in message <A =href=3D"news:uI4R99GeDHA.2236@.TK2MSFTNGP12.phx.gbl">news:uI4R99GeDHA.2236=@.TK2MSFTNGP12.phx.gbl</A>...</DIV>
<DIV><FONT face=3DTahoma size=3D2>My French is weak but I think you =need sp_help.</FONT></DIV>
<DIV><BR>-- <BR>Tom</DIV>
<DIV> </DIV>
=<DIV>---<BR>T=homas A. Moreau, BSc, PhD, MCSE, MCDBA<BR>SQL Server MVP<BR>Columnist, SQL =Server Professional<BR>Toronto, ON Canada<BR><A =href=3D"www.pinnaclepublishing.com=">http://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=
/sql</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV>"meynet" <<A href=3D"mailto:meynet@.csprogramme.com">meynet@.csprogramme.com</A>> =wrote in message <A =href=3D"news:bjq00r$670$1@.news-reader4.wanadoo.fr">news:bjq00r$670$1@.news=-reader4.wanadoo.fr</A>...</DIV>bonjour,<BR><BR>Existe t il une stored proc qui permette de r=E9cup=E9rer la liste des =tables<BR>d'une base de donn=E9e Sql 2K ?<BR>Et si oui peut on ensuite r=E9cup=E9rer =la structure d'une table ; liste de<BR>champs, d'index, de d=E9clencheurs, =contraints...etc<BR><BR>Merci<BR>Christophe.<BR><BR></BLOCKQUOTE></BODY><=/HTML>
--=_NextPart_000_02A5_01C37857.EFA1EBC0--|||de rien, je vous en prie
uhh, i've to take help this :-)
http://www.canuckabroad.com/language/french.shtml
Cheers,
-Vishal
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uhKjgiHeDHA.3024@.tk2msftngp13.phx.gbl...
> merci beaucoup, Vishal !
> --
> - Anith
> ( Please reply to newsgroups only )
>|||This is a multi-part message in MIME format.
--=_NextPart_000_02FD_01C37860.F8CD8D40
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
He's from North Bay, Ontario.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message =news:#oIPWpHeDHA.2268@.TK2MSFTNGP10.phx.gbl...
I thought Aaron is a French Canadian? (or should that be Quebecois?)
-- Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:e7GBqlHeDHA.2248@.TK2MSFTNGP09.phx.gbl...
Y'know, just 'cuz we're Canadian, does mean we speak French, eh? ;-)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message =news:OTFK4jHeDHA.3024@.tk2msftngp13.phx.gbl...
You should have left this one to Aaron then ;-)
-- Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:uI4R99GeDHA.2236@.TK2MSFTNGP12.phx.gbl...
My French is weak but I think you need sp_help.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"meynet" <meynet@.csprogramme.com> wrote in message =news:bjq00r$670$1@.news-reader4.wanadoo.fr...
bonjour,
Existe t il une stored proc qui permette de r=E9cup=E9rer la liste =des tables
d'une base de donn=E9e Sql 2K ?
Et si oui peut on ensuite r=E9cup=E9rer la structure d'une table ; =liste de
champs, d'index, de d=E9clencheurs, contraints...etc
Merci
Christophe.
--=_NextPart_000_02FD_01C37860.F8CD8D40
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

He's from North Bay, =Ontario.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jacco Schalkwijk" wrote in message news:#oIPWpHeDHA.2268=@.TK2MSFTNGP10.phx.gbl...
I thought Aaron is a French Canadian? =(or should that be Quebecois?)
-- Jacco Schalkwijk MCDBA, MCSD, MCSEDatabase AdministratorEurostop Ltd.
"Tom Moreau" = wrote in message news:e7GBqlHeDHA.2248=@.TK2MSFTNGP09.phx.gbl...
Y'know, just 'cuz we're Canadian, =does mean we speak French, eh? ;-)
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Jacco Schalkwijk" wrote in message news:OTFK4jHeDHA.3024=@.tk2msftngp13.phx.gbl...
You should have left this one to =Aaron then ;-)
-- Jacco Schalkwijk MCDBA, MCSD, MCSEDatabase AdministratorEurostop Ltd.


"Tom Moreau" = wrote in message news:uI4R99GeDHA.2236=@.TK2MSFTNGP12.phx.gbl...
My French is weak but I think you =need sp_help.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"meynet" =wrote in message news:bjq00r$670$1@.news=-reader4.wanadoo.fr...bonjour,Existe t il une stored proc qui permette de r=E9cup=E9rer la liste des =tablesd'une base de donn=E9e Sql 2K ?Et si oui peut on ensuite r=E9cup=E9rer =la structure d'une table ; liste dechamps, d'index, de d=E9clencheurs, =contraints...etcMerciChristophe.

---

--=_NextPart_000_02FD_01C37860.F8CD8D40--sql

listbox contents to sql stored procedure command parameter?

Hello,

Stuck in a spot and hoping someone will nudge me in the right direction...

I'm trying to write to a sql db via a storedprocedure using a parameter. i'm pretty certain the below statement is causing the problem. but i'm not sure how to properly refer to it...

Dim connString As String
connString = "integrated security=false;user id=sa;server=HCENT1;database=LicenseRenewal;persist security info=False"
Dim myConnection As New SqlConnection(connString)
Dim myCommand As New SqlCommand("InsertPage1", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
' ......other (working) parameter statements.......
Dim parameterDates As New SqlParameter("@.Dates", SqlDbType.VarChar, 4000)
parameterDates.Value = Session("lstDates")
myCommand.Parameters.Add(parameterDates)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

the session("lstDates") is the contents of lstDates.Items (listbox) from a previous page.
i'm guessing its not valid to refer to it as a varchar, can someone point me to the proper way to handle this?

tia
andyI would try Session("lstDates").ToString(), though I personally would try and debug it to see what exactly is in that session variable.

Are you getting an error message? If so, please give the EXACT message.|||thank you for the suggestion.

I've tried what you're saying and the table gets populated with the string:

"System.Web.UI.WebControls.ListItemCollection"

i took a stab in the dark and tried varbinary (sans .tostring) as the sqldatatype for the parameter/storedprocedure/columntype and i received:

Server Error in '/NET/LicenseRenewal' Application.
------------------------
Object must implement IConvertible.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidCastException: Object must implement IConvertible.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidCastException: Object must implement IConvertible.]
 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +196
 LicenseRenewal.W4.WriteDBPage1() in C:\Documents and Settings\andrzej\VSWebCache\www.aea13.org\NET\LicenseRenewal\W4.aspx.vb:453
 LicenseRenewal.W4.btnDONE_Click(Object sender, EventArgs e) in C:\Documents and Settings\andrzej\VSWebCache\www.aea13.org\NET\LicenseRenewal\W4.aspx.vb:158
 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
 System.Web.UI.Page.ProcessRequestMain() +1277

Seems it can't automatically convert a collection of listbox items into a sql column list, a string list (not one long string however) in the column would be sufficient.

i welcome/appreciate any further input.

thank you
andy|||forgot to say, the contents of the session("lstDates") are various quantities (less then 25 typically) of strings following the 'syntax' of "02/02/04 From: 08:00 AM To 09:00 AM". The string gets compiled from a text box and 2 other listboxes and added to the lstDates. I know the session("lstDates") holds valid data because i've used the session info to reload the listbox without problems

http://www.aea13.org/net/licenserenewal/w1p2.aspx is the page that has the listbox, the page following that is supposed to write to the database when someone clicks on the bottom button.

I took copies of these 2 pages out of the full application, spliced them together and have butchered the code to get rid of non (error) relevant functions. only the relevant parts work for this testing purpose, also the button is only writing the single parameter to a lone table with a lone column.

thanks again.|||ok, got tired of fighting with it so went around it...

Dim connString As String
connString = "............."
Dim myConnection As New SqlConnection(connString)
Dim myCommand As New SqlCommand("test1", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim strTempString As String
Dim lstTempListbox As New ListBox
Dim arrTemparray As New ArrayList
Dim x As Integer
lstTempListbox.DataSource = Session("lstDates")
lstTempListbox.DataBind()
For x = 0 To lstTempListbox.Items.Count - 1
If strTempString = "" Then
strTempString = lstTempListbox.Items(x).ToString
Else
strTempString = strTempString + lstTempListbox.Items(x).ToString
End If
Next
Dim parameterDates As New SqlParameter("@.Dates", SqlDbType.VarChar, 4000)
parameterDates.Value = strTempString
myCommand.Parameters.Add(parameterDates)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()

the table gets the data nicely formatted into a single cell and i can spit it back out in the RTF without formatting issues.

hope it helps someone else :)
andy

listbox and sql stored procedure

Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help me
Some examples here:
http://vyaskn.tripod.com/passing_arr...procedures.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help me
|||This link is a good starting point. You will find some excellent
information here:
http://www.sommarskog.se/
Read the "Arrays and Lists in SQL Server" link
Keith
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
> Hi,
> First of all sorry for my not perfect english.
> I've got listbox in my .aspx page where the users can make multiple
> selection.
> So, Users can select 7 items in listbox, I have to take value from
> items and pass it to stored procedure to delete 7 rolls in my table.
Thats
> simple, but what if user select 3 or 30 items in listbox? The problem is
> that I dont know the number of the parameters, and how to pass them. can
I
> use array or is there some different solution?
> Of course I can take the collection of items and for every item, I
can
> call stored procedure, but this is no good in performance reason.
> Please help me
>

listbox and sql stored procedure

Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help meSome examples here:
http://vyaskn.tripod.com/passing_ar..._procedures.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help me|||This link is a good starting point. You will find some excellent
information here:
http://www.sommarskog.se/
Read the "Arrays and Lists in SQL Server" link
Keith
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
> Hi,
> First of all sorry for my not perfect english.
> I've got listbox in my .aspx page where the users can make multiple
> selection.
> So, Users can select 7 items in listbox, I have to take value from
> items and pass it to stored procedure to delete 7 rolls in my table.
Thats
> simple, but what if user select 3 or 30 items in listbox? The problem is
> that I dont know the number of the parameters, and how to pass them. can
I
> use array or is there some different solution?
> Of course I can take the collection of items and for every item, I
can
> call stored procedure, but this is no good in performance reason.
> Please help me
>

listbox and sql stored procedure

Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help meSome examples here:
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
Hi,
First of all sorry for my not perfect english.
I've got listbox in my .aspx page where the users can make multiple
selection.
So, Users can select 7 items in listbox, I have to take value from
items and pass it to stored procedure to delete 7 rolls in my table. Thats
simple, but what if user select 3 or 30 items in listbox? The problem is
that I dont know the number of the parameters, and how to pass them. can I
use array or is there some different solution?
Of course I can take the collection of items and for every item, I can
call stored procedure, but this is no good in performance reason.
Please help me|||This link is a good starting point. You will find some excellent
information here:
http://www.sommarskog.se/
Read the "Arrays and Lists in SQL Server" link
--
Keith
"John" <stomss2003@.yahoo.com> wrote in message
news:%23HUM$drXEHA.3944@.tk2msftngp13.phx.gbl...
> Hi,
> First of all sorry for my not perfect english.
> I've got listbox in my .aspx page where the users can make multiple
> selection.
> So, Users can select 7 items in listbox, I have to take value from
> items and pass it to stored procedure to delete 7 rolls in my table.
Thats
> simple, but what if user select 3 or 30 items in listbox? The problem is
> that I dont know the number of the parameters, and how to pass them. can
I
> use array or is there some different solution?
> Of course I can take the collection of items and for every item, I
can
> call stored procedure, but this is no good in performance reason.
> Please help me
>

Listbox

I'm new to MS Reporting Services and I need help ....
I have a Stored Procedure and I'm passing 3 parameters. One of the
parameters is a listbox with several options. I want to automatically
populate those options from a database query. Problem is I am not sure
how to do this.
The report data is currently coming from a Stored Procedure that
returns a single dataset. Do I need to add another dataset or a
subreport? Need helpYou can handle this by creating a new dataset, which can either use a
stored proc that brings back the options you want in your listbox, or
manually typing in the query (dataset type of "Text").
Next, go to Report --> Parameters
Choose the parameter that you want a listbox for.
Under "Available Values," choose "From Query"
In the dataset field, select the new dataset you just created
In the "value field," choose the field from your dataset that holds the
"value" you want passed to the query that drives your report
In the "label field," choose the field from your dataset that you want
to display in the listbox.
John
melishbd wrote:
> I'm new to MS Reporting Services and I need help ....
> I have a Stored Procedure and I'm passing 3 parameters. One of the
> parameters is a listbox with several options. I want to automatically
> populate those options from a database query. Problem is I am not sure
> how to do this.
>
> The report data is currently coming from a Stored Procedure that
> returns a single dataset. Do I need to add another dataset or a
> subreport? Need help|||Sorry I had misunderstood...these guys seem to have you pointed in the
right direction now, anyway.
John
melishbd wrote:
> I'm new to MS Reporting Services and I need help ....
> I have a Stored Procedure and I'm passing 3 parameters. One of the
> parameters is a listbox with several options. I want to automatically
> populate those options from a database query. Problem is I am not sure
> how to do this.
>
> The report data is currently coming from a Stored Procedure that
> returns a single dataset. Do I need to add another dataset or a
> subreport? Need help

List user-defined objects

Hi, all. How does one list all the user-defined objects (tables, udf's,
udt's, stored procedures, and views) for a SQL Server 2000 db -- the ones
owned by dbo? Thanks.Check information schema views in BOL.
use yourDB
go
declare @.s sysname
set @.s = N'dbo'
select
table_name,
table_type
from
information_schema.tables
where
(table_type = 'base table' or table_type = 'view')
and table_schema = @.s
and objectproperty(object_id(table_schema + '.' + quotename(table_name)),
'IsMSShipped') = 0
select
*
from
information_schema.column_domain_usage
where
domain_schema = @.s
select
routine_name,
routine_type
from
information_schema.routines
where
(routine_type = 'procedure' or routine_type = 'function')
and routine_schema = @.s
go
AMB
"dw" wrote:

> Hi, all. How does one list all the user-defined objects (tables, udf's,
> udt's, stored procedures, and views) for a SQL Server 2000 db -- the ones
> owned by dbo? Thanks.
>
>|||Thank you, Alejandro. I didn't know what to look under -- now I know to
research information schema views. Thanks :)
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:8AC22FB8-4D41-471E-9762-1E669A1D3BBB@.microsoft.com...
> Check information schema views in BOL.
> use yourDB
> go
> declare @.s sysname
> set @.s = N'dbo'
> select
> table_name,
> table_type
> from
> information_schema.tables
> where
> (table_type = 'base table' or table_type = 'view')
> and table_schema = @.s
> and objectproperty(object_id(table_schema + '.' + quotename(table_name)),
> 'IsMSShipped') = 0
> select
> *
> from
> information_schema.column_domain_usage
> where
> domain_schema = @.s
> select
> routine_name,
> routine_type
> from
> information_schema.routines
> where
> (routine_type = 'procedure' or routine_type = 'function')
> and routine_schema = @.s
> go
>
> AMB
>
> "dw" wrote:
>|||http://www.aspfaq.com/search.asp?q=schema%3A
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"dw" <cougarmana_NOSPAM@.uncw.edu> wrote in message
news:em7WicTNFHA.1884@.TK2MSFTNGP15.phx.gbl...
> Thank you, Alejandro. I didn't know what to look under -- now I know to
> research information schema views. Thanks :)
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message
> news:8AC22FB8-4D41-471E-9762-1E669A1D3BBB@.microsoft.com...
quotename(table_name)),
ones
>

List of stored procedures with permission for executing for user

I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?

Thanks for tips

If you want to get the list of stored procedures , on which specific database user ('XY') has EXECUTE permission explicitly granted , consider the following query:

Code Snippet

SELECT [name]

FROM sys.objects obj

INNER JOIN sys.database_permissions dp ON dp.major_id = obj.object_id

WHERE obj.[type] = 'P' -- stored procedure

AND dp.permission_name = 'EXECUTE'

AND dp.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT

AND dp.grantee_principal_id =

(SELECT principal_id FROM sys.database_principals WHERE [name] = 'XY')

But you should be aware that here you deal only with explicitly granted permissions, not effective permissions of the user.|||

thanks a lot|||

is there any way to do this with sql server 2000?