Can anyone tell me whether i can create/open a file inside a stored procedures??
I need to retrieve data from my database and then send these data to other users by
using xp_sendmail. I plan to arrange the data in a text file format.
Thanks for any reply.
I am studying VC++6 and am involved in a database learning project. I installed a SQL 7 server on this W2K Pro machine a long time ago and applied SP4 to the server. The instructions are to create a stored procedure by right-click on the Stored Procedures folder icon in the Data View and selecting New Stored Procedure. There is no New Stored Procedure option displayed when I do this (Docking View, Hide, Properties). What must I do to get to ability to create a stored procedure. The sample database being used is the Pubs database.
View 1 Replies View RelatedCan you create views within a stored procedure?
I need to create CLR Based Stored Procedures using VS2005 Standard Edition in Sql Server 2005 Standard Edition. Unfortunately, when I create a new project in VS2005. I can not find the template to create a Sql server project using C#. I re-install sql server and Vs2005, the problem still there. Does Vs2005 standard edition support create sql server project? Thanks a lot.
View 1 Replies View RelatedAnyone have any compelling arguements for using one over the other?
Thanks
Mercy
I surfed a lot of the internet piecing this together. I have a database that gets copied every night from a live database so users can run queries against it, so I needed to be able to recreate a stored procedure from within ASP.NET. Also, I needed to take information I was importing from a .CSV file and compare it using the stored procedure to find specific information and import it into another database. I was able to do this using the SQLDMO reference object.
Here are some clipits of what you need.
Imports SQLDMOPrivate myServer As New SQLServer
Dim serverName As String = System.Configuration.ConfigurationManager.AppSettings("Server").ToString()Dim userName As String = System.Configuration.ConfigurationManager.AppSettings("UserName").ToString()
Dim password As String = System.Configuration.ConfigurationManager.AppSettings("Password").ToString()
Dim storedProcedure As New StoredProcedure
Dim qresults As SQLDMO.QueryResults
Example Stored Procedure
Try
myServer.Connect(serverName, userName, password)
storedProcedure.Text = "IF EXISTS (SELECT * FROM sysobjects WHERE name='memb_proc' AND user_name(uid)='dbo') DROP PROCEDURE dbo.memb_proc"myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
storedProcedure.Text = "CREATE PROCEDURE dbo.memb_proc @lastname varchar(50) = '', @firstname varchar(50) = '', @zip varchar(5) = '', @membrowno varchar(50) OUTPUT AS Select @membrowno = rowno from memb where lastname LIKE @lastname and name LIKE @firstname and zip1 LIKE @zip "myServer.Databases.Item("<databasename>", "dbo").StoredProcedures.Add(storedProcedure)
Label1.Text = "Creation of stored procedure successful"Catch ex As Exception
Label1.Text = "Creation of stored procedure failed"
Finally
myServer.DisConnect()End Try
Run Stored Procedure and Obtain Results
myServer.Connect(serverName, userName, password)
qresults = myServer.ExecuteWithResults("USE [databasename] DECLARE @return_value int,@membrowno varchar(50) EXEC @return_value = [dbo].[memb_proc] @lastname = N'" & Last & "', @firstname = N'" & First & "',@zip = N'" & ZIP & "', @membrowno = @membrowno OUTPUT SELECT @membrowno as N'@membrowno'")For num = 1 To qresults.Rows
MemberRowNo = qresults.GetColumnString(num, 1)
Next
myServer.DisConnect()
You know this sample ODS dll project:
$80oolsdevtoolssamplesodsxp_hello
I need to find a template like this in Visual Basic.
On this site I read through the xp_Encrypt project which was developed in VB. I did not see source code or a downloadable project file.
My searching on the internet hasn't yielded any practicle results, all examples are either how to use an extended stored procedure or are a MSVC++ project.
Anyone find VB related resources, anywhere? Books, TV, magazines????
Hi all,
I had developed all my reports using CRXI and stored procedures of MSSQL 2000. Now I am migrating from CRXI to SSRS. But i have a problem because of my report stored procedures. These stored procedures are complex and nested. When i try to use procedures in a report, it gives a error. When i comment out my EXEC line in my procedures, it goes OK.
Please help me to do this ?
I am asked to create 100 procedures to a database. Any best way to create them in a database one by one by calling the files and saving the execution output files in a folder?
View 9 Replies View Related
i have sql server 2005 and sqlserver management studio;I want to get all stored procedures in my db as a filewhat can I do to get all stored procedures together as a single file
May i know whether i can write some text/string to
a file from inside a stored procedures?
Currently i can retrieve data from a query and use bcp
to write it to a file but i need to add some others text
to the same file.
Thanks for any reply!
hi,
I am new to Sql Server and I want export the stored procedures of my SQL Server 2000 database
to a flat file, so I can import it later to another SQL Server 2000 database (it is not anywhere in the network).
Can anyone explain me the easiest way to do that ?
Thanks !
I have a need to export all of the stored procedures in a database to files on the server dirve. I know that this can be done through the management interface but I need a way to do it programatically. I need to have a script or stored proc that dumps all of the procedures to a defined location on disk. Does anyone know how this can be done?
Thanks!
Hi
I wrote a clr storred procedure that use fileopen, fileput an fileclose to write a report from a sql 2005 database but I don't know how to give it permissions to do its job.
The Code is
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System.Security.Permissions
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure> Public Shared Sub PriceSum( ByVal intFecha as sqlInt32 )
dim strFecha as string
strFecha = cstr(intFecha)
Dim f As New FileIOPermission(PermissionState.none)
f.AllLocalFiles = FileIOPermissionAccess.write
f.Demand()
FileOpen(1, "G:AuVeJAPParchivo.txt", OpenMode.Random, OpenAccess.ReadWrite, OpenShare.LockReadWrite, 10)
Using connection As New SqlConnection("context connection=true")
Connection.Open()
dim strConsulta as string
strConsulta = "declare cur_unidades cursor for select id_tda_vdp from tda_venta_departamental_vdp where date_operacion_vdp = " & strFecha & " group by id_tda_vdp order by id_tda_vdp"
Dim command As New SqlCommand(strConsulta, connection)
Dim reader As SqlDataReader
reader = command.ExecuteReader()
using reader
Dim intKon as integer
intKon = 0
While reader.Read()
fileput(1, reader.GetSqlstring(0) & chr(0), intKon)
intKon = intKon + 1
End While
End Using
End Using
fileClose(1)
End Sub
End Class
As you could see, the report needs a chr(0) at the end of each line, thats why I spent a lot of time learning clr/sql, but when I finally success in crate the assembly an procedure in sql 2005 it gives me this frustrating error:
Msg 6522, Level 16, State 1, Procedure prueba1, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'prueba1':
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)
at StoredProcedures.PriceSum(SqlInt32 intFecha)
I use
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
I really appreciate your help, it could be a different way to do this or a simple step by step "how to grant permission"
thank you very much
Adolfo Ponce
Every day we are restoring prod DB in Development env. I need to save before restore users stored proc,
restore DB and after create SP from file.
Thanks.
The following 2 stored procedures are used to insert and select attachments for a web application I'm creating. The stored porcedures work in the basic sense but when I do the insert only one record is inserted. Also, when I do the select only one record is selected.
I'm wondering if I there is suppose to be some sort of record counting or looping involved to insert and return all records. If there is, I would appreciate any advice and/or examples on the proper way of doing this.
If the code is also needed I'd be glad to post it as well.
Thanks
CREATE PROCEDURE AddAttachments
(
@FILENAME varchar(200),
@FILE_PATH varchar(2000),
@T_PK int
)
AS
INSERT INTO ATTACHMENTS (FILENAME, FILE_PATH, T_PK) VALUES (@FILENAME, @FILE_PATH, @T_PK);
GO
CREATE PROCEDURE SelectAttachments
(
@PK int
)
AS
SELECT FILENAME, FILE_PATH
FROM TASKINGS RIGHT OUTER JOIN
ATTACHMENTS ON T.PK = ATTACHMENTS.T_PK
WHERE (T_PK = @PK);
GO
I wish to ultimately have the content of all stored procedures related to a database in a single ASCII file for review. Is that doable? If so, how?
Thanks,
Peter
Hi there,I have a data manipulation process written in a Nested Stored procedurethat have four levels deeper. When I run these individual proceduresindividually they all seems to be fine. Where as when I run them alltogether as Nested proces (calling one in another as sub-procedures) Logfile is growing pretty bad like 25 to 30GB.. and finally getting kickedafter running disk space. This process is running around 3hrs on a SQLserever Standard Box having dual processer and 2gb ram.This procedures have bunch of bulk updates and at least one cursor ineacch procedure that gets looped through.I was wondering if anybody experienced this situation or have any clueas to why is this happening and how to resolve this?I am in a pretty bad shape to deliver this product and in need of urgenthelp.Any ideas would be greatly appreciated..Thanks in advance*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View Relatedi am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it.
--no compilation error.
CREATE PROC testTemp
AS
BEGIN
INSERT INTO #tmp(dt)
SELECT GETDATE()
END
only on calling the proc does this give an execution error
Hi there,
Does anyone know any possible way of creating a dbf file from within a stored procedure?
Any guidence, articles, examples, topics to revice would be a great help.
Thanks guys
Butterfly82
In XSD file keep specification of each tables
So I think It would have some class or method to generate common stored procedure from xsd file.
Please help me.
Is there a way to use BCP or something else within a stored procedure to extract data from a select statement out to a text file?
View 4 Replies View RelatedHi!!!
Is it possible to create a stored procedure which will create a text file (containing information from some tables) and send it via e-mail to a list of user.
I know that I will have to configure the SQL Mail.
If it is possible, can someone give me sample code.
Thanks you.
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedI'm trying to write a procedure that having created a new database,will then create within that new database all the tables andprocedures that go with it.In doing this I'm hitting the problem that you can't issue a USEcommand within a procedure.So my question is either- how do I get around this?- if I can't, how can I create procedures etc in a *different*(i.e. the newly created) databaseor- is there a better way to do all this (*)I have SQL files that do this currently, but I need to edit in thename of the database each time before execution, so I thought aprocedure would be better. Also I'd like eventually to expose someof this functionality via a web interface.Although I'm a newbie, I feel I'm diving in the deep end. Any goodpointers to all the issues involved in this aspect of databasemanagement would be appreciated.(*) One thought that occurs to me is to have a "template" database,and to then somehow copy all procedures, tables, view etc from that.--HTML-to-text and markup removal with Detaggerhttp://www.jafsoft.com/detagger/
View 10 Replies View RelatedI created stored procedure to create trigger file in a particular directory using xp_cmdshell.
I am calling the procedure from windows batch script as follows
@@set osq200=osql /a 4096 /b /E /e /d %dbn% /m-1 /r 0 /S %dbs% /Q "exec SP_Create_TriggerFile %2,%1 "
@%osq200% >>%3rec.txt 2>%3rec_err.txt
@set dberr=%errorlevel%
@if %dberr% GTR 0 goto createDATriggerFileErr >>%3rec.txt
If the directory doesn't exist, its throwing error "The system cannot find the path specified" , but the %errorlevel% still showing as 0..
Does anybody know how to create/modify a file without using one of the system stored procedures?
Thanks
Can I create an excel file by stored procedure?
View 2 Replies View RelatedI have a Stored proc which on execution, will generate data in the view .
My requirement is using SSIS, how can I create a Flat file pointing to this view ?
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
I have a store procedure in MC400 which I can call from SSMS using the below command:
EXEC ('CALL GETENROLLMENT() ')At serverName
Now this command returns two data sets like:
HA HB HC HD HE
1112
112571ABC14
113574ABC16
114577ABC87
DADBDCDD
1115566VG02
1115566VG02
1115566VG02
I want to generate two different XML files from these two datasets.Is there any way this can be achieved in SSIS or t-sql ?
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'