Okay, so here's my dilemma: I'm trying to figure out a way I can get a list of drives, users, groups, etc. from computers on the network. There are a couple of caveats:
1) This has to be done entirely using T-SQL. There can be no external components that need to be installed.
2) xp_cmdshell can NOT be used, both for security reasons and because some of the computers being polled do not have SQL Server installed.
I would like to use the sp_OA* stored procedures. So far I have been able to connect to a remote server and find the running state of SQL Server; however, when it comes to enumerating collections I'm kinda lost.
This has also been posted in the MS-SQL general forum.
Hello to all,Maybe first small introduction:- SQLServer 2000 SP3,- XP Pro EN,- ActiveX,- SP in databaseIt should working like this.There is a instanse of an object working, which recieves "telegramms"from all clients, including SQLServer.In SP I set the special properities (see code below) and it works.Also works the method, which I call by this object.The only thing which is not working is, that I cannnot read the objectproperty, which I try to read!I was trying already, to do something that the SQL server wantblocking the object, but it is not the case.Below the code:---------------------------------------declare @iRetValintdeclare @iObjectintdeclare @sPropertyvarchar(2560)declare @sSource varchar(1000)declare @sDescription varchar(1000)declare @sLog varchar(1000)declare @dDateEVT datetimedeclare @sText1varchar(10)declare @sText2varchar(10)declare @iProperty intdeclare @nMessageNrnumericdeclare @bstrDateTime varchar(100)declare @textFromA1varchar(100)declare @textFromA2varchar(100)declare @i intset @iObject = 0set @dDateEVT = getdate()set @iRetVal = 0set @sText1 = 'AA00000000'set @sText2 = 'BB00000000'set @iProperty = 7set @i = 0-- {034188F2-8DBC-4613-829A-76D5279C35A3}exec @iRetVal = sp_OACreate 'RAIDSSimComponents.pidMessenger',@iObject OUTPUT,1IF @iRetVal <> 0beginexec sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTset @sLog = 'LOG1: No object created. Source: ' + @sSource + 'Description: ' + @sDescriptionprint @sLogend-- Set the object propertyexec @iRetVal = sp_OASetProperty @iObject, 'FollowFromB', 1IF @iRetVal <> 0beginexec sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTset @sLog = 'LOG1: Error by setting property FollowFromA'print @sLogendexec @iRetVal = sp_OASetProperty @iObject, 'FollowFromB_EventID', 555IF @iRetVal <> 0beginexec sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTset @sLog = 'LOG1: Error by setting property FollowFromB'print @sLogend-- Call methodexec @iRetVal = sp_OAMethod @iObject,'SendNotification_FromA',@iProperty OUT, 555, @dDateEVT, @sText1, @sText2IF @iRetVal <> 0beginexec sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTset @sLog = 'LOG1: Error by setting property FollowFromA'print @sLogendset @sProperty = '?'set @i = 1-- Start the while loop, to give the time that the object set thepropertywhile @sProperty = '?'begin-- Do something to make the object not busy any moreselect * from ds_mds_tab-- Get the property from a objectexec @iRetVal = sp_OAGetProperty @iObject, 'ExtraInfo_FromB',@sProperty OUTIF @iRetVal <> 0beginexec sp_OAGetErrorInfo @iObject, @sSource OUT, @sDescription OUTset @sLog = 'LOG2: Source: ' + @sSource + ' Description: ' +@sDescriptionprint @sLogendprint @sProperty-- Write it to the logexec ds_sp_writetodslogger 'ple', @sPropertywaitfor delay '00:00:01.00'endexec sp_OADestroy @iObject
Hi ,I am looking for meta-information about the return recordset of astored-procedure. The procedure returns a resultset that contains columns ofmore tables joined together. In all tables, I use, there is aRecord-Creation-Timestamp-Attribute. When joining two or more tables theseattribute-names appear in ther resultset buti found no way to distinguish them.I there a way to retrieve meta-information about the result-recordset ofsuch a stored-procedure?here some details:the tables=======CREATE TABLE [dbo].[Table1] ([Table1ID] [int] IDENTITY (1, 1) NOT NULL ,[FK_Tab2ID] [int] NULL ,[CreatedAt] [datetime] NULL )CREATE TABLE [dbo].[Table2] ([Table2ID] [int] IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (35) NULL ,[CreatedAt] [datetime] NULL)the stored-procedure:===============CREATE PROCEDURE dbo.sp_Test_RetrieveData@ID intASSET NOCOUNT ONselect * from table1 inner join table2 on (FK_Tab2ID = Table2ID)where table1.ID = @IDGOthe resultset:==========Table1ID,FK_Tab2ID,CreatedAt,Table2ID,Description, CreatedAt(the attribute CreatedAt appears twice.)-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----http://www.newsfeeds.com - The #1 Newsgroup Service in the World!-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Hello, I'm building an ecommerce website which requires customers to create an account before they go ahead with a purchase. I have a createaccount.aspx page in Visual Web Developer 2005 with text boxes where users can enter their details (email, password, name and address). I'm trying to insert the information which users type into the text boxes into an SQL database table called Customers. I've dragged and dropped an SQL data source onto my page and have set it to operate on my AddCustomer stored procedure. I've confirgured my data source such that the parameter for each field in the database is set to the appropriate control on the webpage (for example the Email parameter source is "textboxEmail"). I've also placed a button onto my page so that the button click event can act as the trigger for sending the information in the text boxes to the database. I wasn't totally sure how to write code for the button click event such that when the button is clicked, the INSERT stored procedure runs. At the moment I'm using: Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click SqlDataSource1.Insert() End Sub When I try to run my application I'm getting an error which reads: Cannot insert the value NULL into column 'Email', table 'C:DOCUMENTS AND SETTINGSLUKE JACKSONMY DOCUMENTSVISUAL STUDIO 2005WEBSITESJACKSONSNURSERIESAPP_DATADATABASE.MDF.dbo.Customers'; column does not allow nulls. INSERT fails.The statement has been terminated. The error message implies that I haven't set the necessary parameters correctly but I really don't know where I'm going wrong! The code I'm using for my stored procedure is as follows: ALTER PROCEDURE AddCustomer ( @CustomerID int, @Email nvarchar(50), @Password nvarchar(MAX), @Name nvarchar(50), @Address1 nvarchar(50), @Address2 nvarchar(50), @Address3 nvarchar(50), @City nvarchar(50), @County nvarchar(50), @PostCode nvarchar(50) ) AS INSERT INTO Customers (Email, Password, Name, Address1, Address2, Address3, City, County, PostCode) VALUES (@Email, @Password, @Name, @Address1, @Address2, @Address3, @City, @County, @PostCode) I'd be really grateful if anyone could help me out with this. Thanks in advance, Luke p.s. just incase it helps, here's my createaccount.aspx page: <%@ Page Language="VB" MasterPageFile="~/Master.master" AutoEventWireup="false" CodeFile="createaccount.aspx.vb" Inherits="createaccount" title="Untitled Page" %> <%-- Add content controls here --%> <asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1"> <span style="text-decoration: underline"><strong>Create Account<br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="AddCustomer" SelectCommandType="StoredProcedure" InsertCommand="INSERT INTO Customers 	(Email, Password, Name, Address1, Address2, Address3, City, County, PostCode) 	VALUES 	(@Email, @Password, @Name, @Address1, @Address2, @Address3, @City, @County, @PostCode)"> <SelectParameters> <asp:Parameter Name="CustomerID" Type="Int32" /> <asp:ControlParameter ControlID="textboxEmail" Name="Email" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxPassword" Name="Password" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxName" Name="Name" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxAddress" Name="Address1" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxAddress2" Name="Address2" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxAddress3" Name="Address3" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxCity" Name="City" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxCounty" Name="County" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxPostCode" Name="PostCode" PropertyName="Text" Type="String" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="Email" Type="String" /> <asp:Parameter Name="Password" Type="String" /> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Address1" Type="String" /> <asp:Parameter Name="Address2" Type="String" /> <asp:Parameter Name="Address3" Type="String" /> <asp:Parameter Name="City" Type="String" /> <asp:Parameter Name="County" Type="String" /> <asp:Parameter Name="PostCode" Type="String" /> </InsertParameters> </asp:SqlDataSource> <br /> </strong></span> <table style="font-weight: bold; width: 394px; text-decoration: underline"> <tr> <td style="width: 111px; height: 21px; text-align: left"> Email:</td> <td style="height: 21px"> <asp:TextBox ID="textboxEmail" runat="server" Width="147px"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> Password:</td> <td style="height: 21px"> <asp:TextBox ID="textboxPassword" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> Name:</td> <td style="height: 21px"> <asp:TextBox ID="textboxName" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> Address 1:</td> <td> <asp:TextBox ID="textboxAddress" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> Address 2:</td> <td style="height: 21px"> <asp:TextBox ID="textboxAddress2" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> Address 3:</td> <td> <asp:TextBox ID="textboxAddress3" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> City:</td> <td> <asp:TextBox ID="textboxCity" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> County:</td> <td style="height: 21px"> <asp:TextBox ID="textboxCounty" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> Post Code:</td> <td> <asp:TextBox ID="textboxPostCode" runat="server"></asp:TextBox></td> </tr> </table> <asp:Button ID="btnSubmit" runat="server" Text="Submit" /> </asp:Content> Thanks again
I am trying to pull information about all the user created stored procedures in a database with a query. I have successfully pulled all the procs with parameters, but I want to result set to also contain the stored procedures without parameters. I can't quite get it. Any help would be appreciated:
SELECT procs.name as ProcName, params.name as ParameterName, types.name as ParamType, params.max_length, params.precision, params.scale, params.is_output FROM sys.procedures procs LEFT OUTER JOIN sys.all_parameters params ON procs.object_id = params.object_id LEFT OUTER JOIN sys.types types ON params.system_type_id = types.system_type_id
WHERE params.user_type_id = types.user_type_id AND procs.is_ms_shipped = 0 ORDER BY procname, params.parameter_id
Public FirstNumber As Variant Public SecondNumber As Variant
Function AdditionFunction(FirstNumber, SecondNumber) AdditionFunction = FirstNumber + SecondNumber Print AdditionFunction End Function
Step2
Registered the Dll regsvr32 d:mydlladditionfunction.dll
Step3
'Execute this script DECLARE @cInputValue1 int DECLARE @cInputValue2 int DECLARE @cOutputValue int DECLARE @objDLL int declare @Hresult int DECLARE @ErrorSource varchar (255) DECLARE @ErrorDesc varchar (255)
I'm faced with a situation where I will need to calculate a column fora resultset by calling a component written as a VB6 DLL, passingparameters from the resultset to the component and setting (orupdating) a column with the result. I thought that perhaps the bestway out would be to create a UDF that passes the parameters to the VBcomponent using the sp_oa* OLE stored procs.For a test, I created an ActiveX DLL in VB6 (TestDLL) with someproperties and methods. I then created a function that creates theobject, sets the required properties and returns a result. I usesp_oaDestroy at the end of the function to remove the objectreference. The function seems to work surprisingly well except for asmall problem; when I use the function to calculate a column for aresultset with more that one row, the DLL appears to stay locked up("the file is being used by another person or program"). This leavesme with the impression that the object reference is not beingdestroyed. I have to stop/restart the SQL Server in order to free theDLL.Question:Is the UDF approach the best way? I don't like the idea of creatingand destroying the object at every pass which is what the UDF does.As an alternative, I suppose that I could have a single SP where Icreate the OLE object once, loop through the result set with a cursorand do my processing/updating, then close the OLE object. I must saythat I'm not too fond of that approach either.Thanks for your help,Bill E.Hollywood, FL(code is below)___________________________--Test the functionCreate Table #TestTable(Field1 int)INSERT INTO #TestTable VALUES (1)INSERT INTO #TestTable VALUES (2)SELECT Field1, dbo.fnTest(Field1,4) AS CalcColFROM #TestTableDrop Table #TestTable___________________________CREATE FUNCTION dbo.fnTest/*This function calls a VB DLL*/(--input variables@intValue1 smallint,@intValue2 smallint)RETURNS integerASBEGIN--Define the return variable and the counterDeclare @intReturnValue smallintSet @intReturnValue=0--Define other variablesDeclare @intObject intDeclare @intResult intDeclare @intError intSet @intError=0If @intError = 0exec @intError=sp_oaCreate 'TestDLL.Convert', @intObject OUTPUTIf @intError = 0exec @intError = sp_OASetProperty @intObject,'Input1', @intValue1If @intError = 0exec @intError = sp_OASetProperty @intObject,'Input2', @intValue2If @intError = 0exec @intError = sp_oamethod @intObject, 'Multiply'If @intError = 0exec @intError = sp_oagetproperty @intObject,'Output',@intReturnValue OutputIf @intError = 0exec @intError = sp_oadestroy @intObjectRETURN @intReturnValueEND
i have sql2005 enterprise edition 64 bit edition and immediately after applying service pack 2 we are encountering few errors which are affecting our production very much
1. there is a user defined function which in turn calls sp_OA ....(ole automation procedures - this is enabled in the configuration manager) and it is working fine ....however over a period of time after 1-2 hours the functions returns unexpected values, and when you restart sql server, the function returns normal values as expected does service pack 2 upgrade in any way affect sp_OA extended system procedures..
2.i am getting this error contimously in my sql server log Message The activated proc [dbo].[sp_sysmail_activate] running on queue msdb.dbo.ExternalMailQueue output the following: 'The service queue "ExternalMailQueue" is currently disabled.' database mail stops workign after some time and when you restart sql server it starts working
can somebody throw some light on this as it is very badly affecting my production
I have some troubles with IBM WebSphere Application Server using MS SQL Server 2005 JDBC Driver. I always get the error e.g. java.lang.SecurityException: class "com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData"'s signer information does not match signer information of other classes in the same package
I found this Feedback but it seems to be closed.
A temporary solution for me was to delete the meta-inf directory of the JAR-File, but that can't be the solution.
I'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/
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?
hi guys, Can anyone advise me how how to read an excel doc i have stored locally? I need to be able to start the read from say row number 6 and finish the read once i get to a row that contains a pre-determined word signifying the end of processing. I intend to store the parsed data in an array that will be used as the data source for a gridview or repeater object on another asp page. I'm using ASP.net 2.0 and C# by the way. thanks in advance for any help!!!
Dim objCon2 As New SqlConnection() objCon2.ConnectionString = "a standard connection string" objCon2.Open()
Dim objCommand As SqlCommand objCommand = New SqlCommand(strSQL, objCon2) Dim objReader As SqlDataReader objReader = objCommand.ExecuteReader()
Label1.Text = objReader("email")
strSQL is a select command which I've checked (using SQL Query analyzer) does return data. I know the connection string is valid (and I presume if it wasn't that it'd fail on objCon2.open, which it doesn't).
So why oh why do I get this error on the last line (and yes, there is an "email" field in the contents of the reader)
System.InvalidOperationException: Invalid attempt to read when no data is present.
I have this code that I hacked together from someone else's example. I kind of understand how it works. I just don't know if it will and i am not in a location right now to check. I was wondering if I did this correctly first, second how can it improve and should i do something different. Basically i just want to check the password in a database. I am passing the username and password to this function from another functioprivate bool authUser(string UserName, string Password){ string connectionString = ConfigurationSettings.AppSettings["DBConnectionString"]; SqlConnection DBConnection = new SqlConnection(connectionString); bool result = false; DBConnection.open() SqlCommand checkCommand = new SqlCommand("SELECT password FROM Users WHERE userName='" + Password + "', DBConnection) SqlDataReader checkDataReader = checkCommand.ExecuteReader(); if(checkDataReader.GetString(0) == Password) { result = true; } else { result = false; } checkDataReader.Close(); DBConnection.Close(); return result;}Thank you Buddy Lindsey
Does anyone know how to read transaction log besides trace and profiler. The current situation is some one in our org. deleted an item and I'm trying to find out who and when.
I have some records that have been deleted. I need to find out who did it and to do that I need to read the logs. Are there any utilities that will allow me to read login 7.0? How about 6.5?
We are having continual problems with our transaction log filling up on one of our major applications. Does anyone know of a way or tool to read the transaction log? We want to determine what is causing this problem.
Is there a way, in SQL 7.0 to print out or view what's in the Transaction Log? In 6.5 you could view the table syslogs, but I don't see any documentation anywhere on how to do this in 7.0.
I received a SQL Server 6.5 database (.dat file) on a removeable drive. I'm currently running 7.0. Is there any way to read/import/link to this data without installing 6.5. And if I have to install 6.5 how do I get the system to see the database?
Anyone know a way to read DTS packages? I have inherited a DTS package, saved in SQL, and am trying to find a way to read the steps without having to view every single step through the GUI.
I am storing one text file on the server.This text file contains some mobile numbers.The file look like 009198XXXXXXXX 009198XXXXXXXX 009198XXXXXXXX 009198XXXXXXXX etc
Here I need to read this text file row by row mobile number and then insert these mobile numbers into a table by using sql procedure or sql trigger or any other method or coding. Is it is possible or not? If so then anybody can help me!
We have sql server 2000 and i need to read one perticular xml and from that i want ID field stored in one table...is it possible by query analyzer to read xml and stored ID in table...I have only path of that xml file...
-- drop proc SP_Trio_Popul_Stg_Tbls1 CREATE PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) AS -- alter PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) AS
declare @sql varchar (20)
set @sql = 'INSERT INTO dbo.Name_Pharse_Stg_Tbl1 (nm_last) SELECT nm_name FROM dbo.' + quotename(@tableName)
print @sql exec (@sql)
---------------------------------------------------------------------- exec SP_Trio_Popul_Stg_Tbls1 '00485' ---------------------------------------------------------------------- INSERT INTO dbo.Name Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'Name'.
im trying to run this SP but im getting an error. Can you help me to fix it?
Hi, I've been a developer for 20 some years, but never learned the design steps of a complex database. Can you give me a book or 2 to bring me up to speed with the latest database design and data modeling techniques?
