I'm wondering if there is any way for me to execute any type of command (delete, insert, create, alter, etc) on management studio without having to wait the server answer.
On Oracle, I use DBMS_JOB. On SQL Server, do I have to create a SQL SERVER Agent Job? What if I don't have permission to create that kind of jobs?
We have a two node cluster running W2K and SQL2000. The agent starts, but we cannot access any of the jobs. It gives us the 14258 Agent is starting, try again later... error message. We know that this is not the case, we have waited several hours without it "starting". The SQL Agent log indicates that the agent is waiting on SQL Server to recover databases, but all of our databases are up. It is almost as if during startup a flag did not get set to indicate that a database was recovered.
Has anyone run across this before? If so, did you ever figure out how to fix it?
Is anyone have already encountered such a problem :
When a table is locked by several locks (key, pag), either a SELECT request which does not need to lock any row cannot succeed.
In SQL Server Enterprise Manager, I see that my request is waiting for a lock "LCK_M_S" !!!
How I can avoid this kind of situation which seems to me completely abnormal.
I have never been faced with this kind of problem in Oracle.
Thanks for anyone who will help me to resolve this "bug".
I have to execute this command on my vs.net code:
Code Snippet
SET IDENTITY_INSERT table name ON
I use this to run it:
Code Snippet
.
.
.
Dim command As SqlCommand = New SqlCommand("SET IDENTITY_INSERT table name ON", msSqlConexion)
command.ExecuteNonQuery()
It doesn't throw any kind of errors, but it actually don't execute it, and If I run it on console it works ok.
any help?
While I was out of the office the Lan Team moved one of my SQL Server2000 servers to a new network domain. Since then the maintenance jobhas not ran.The error log for the SQL Agents has the message listed in the subjectline. I have not found any useful articles on the MS SQL Serversite. Anyone know what might be wrong and how to fix it.HTH -- Mark D Powell --
View 4 Replies View RelatedCan anybody help me with this command code that stops at the execute and eventually gives timeout.
Dim MM_Cmd, strSQL, strSQL2, strSQL3, strSQL4
Set MM_Cmd = Server.CreateObject("ADODB.Command")
MM_Cmd.ActiveConnection = MM_connAdmin_STRING
strSQL = "update Products_Categories set Depth=NULL, Lineage=''"
MM_Cmd.CommandText = strSQL
MM_Cmd.CommandType = 1
'MM_Cmd.CommandTimeout = 0
MM_Cmd.Prepared = True
MM_Cmd.Execute strSQL
Set MM_Cmd = Nothing
Regards
Amazing
I am getting below error while executing any SQL query on database server.
Time-out occurred while waiting for buffer latch type 2 for page (20:17937), database ID 13.
There is currently some space issue on the server, so by any chance this space issue is causing this error?
I have a little application that I have designed where I need to be able to execute create table and create function comands against the database.
It seems that it does not like my sql file. Does anyone know of a different method of doing this?
Error message
Line 2: Incorrect syntax near 'GO'.
Line 4: Incorrect syntax near 'GO'.
Line 8: Incorrect syntax near 'GO'.
'CREATE FUNCTION' must be the first statement in a query batch.
Must declare the variable '@usb'.
Must declare the variable '@usb'.
Must declare the variable '@i'.
A RETURN statement with a return value cannot be used in this context.
Line 89: Incorrect syntax near 'GO'.
Line 91: Incorrect syntax near 'GO'.
Line 94: Incorrect syntax near 'GO'.
Protected Sub Install()
Dim err As String = ""
While err.Length < 1
' Dim your StreamReader
Dim TextFileStream As System.IO.TextReader
'Load the textfile into the stream
TextFileStream = System.IO.File.OpenText(Request.PhysicalApplicationPath & "Scripts .sql")
'Read to the end of the file into a String variable.
executesql(TextFileStream.ReadToEnd, err)
err = "Susscessful"
End While
If err = "Susscessful" Then
Response.Redirect("Default.aspx")
Else
Me.lblError.Text = err
End If
End Sub
Private Function executesql(ByVal s As String, ByRef err As String) As Boolean
Try
Dim conn As New Data.SqlClient.SqlConnection(GenConString())
Dim cmd As New Data.SqlClient.SqlCommand(s, conn)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
Return True
Catch ex As Exception
err = ex.Message.ToString
Return False
End Try
End Function
Example sql file
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MyFunc]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[MyFunc]
GO
CREATE FUNCTION [dbo].[MyFunc]
(
-- Add the parameters for the function here
)
RETURNS varchar(1000)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result varchar(1000)
-- Add the T-SQL statements to compute the return value here
-- Do something here
-- Return the result of the function
RETURN @Result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
hello to all, and I hope you can help.
this is the code from BOL
dtsrun /Sserver_name /Uuser_nrame /Ppassword /Npackage_name /Mpackage_password /Rrepository_name
I have followed it and come up with ...
dtsrun /SPC-409 /Usa /Pmypassword /Nemployee_export /M? /R?
where can I know the repository_name, is it one of the following:
DTS package
Meta data service package
Meta data
all under Data Transformation Service folder in SQL Server
Thanks
Al
Is it possible to create a stored procedure to run a custom dos command (eg. c:ProgramName param1 param2)?
Thanks,
Ben
Hi all,
In SQL Server using xp_cmdshell we can excute any of the command or executable files which can be executed in command prompt. Here my problem is that .. I am trying to execute OSQL from the MSSQL(Query Analyser) using xp_cmdshell.. but its give error saying "'osql' is not recognized as an internal or external command,
operable program or batch file."
This error occours when it is not able to find the executable file... but same thing I am able to execute from the command prompt. So I feel this problem is some where related to the path setting of windows. If some one can solve this problem or sugesst the how to set the path for window it will be help full..
waiting for reply
Hi,
I wanted to know if there is a way to execute sql commands on the operating system's command line. If it is possible, then how do we do it ? For example to execute a SELECT * from Table statement what are we supposed to do ?
Thanks
S
Hi,
I need to execute some store procedures I have in the SQL editor but I seem to been having problems with the formatting for datetime variables needed for the execution of my code. can anyone please help?
thanks in Advance
exec [usp_CMSTemplateCreateNewTemplate] 0, 0, 'My First Page', '', 0, CAST(10/16/2007,DATETIME), CAST(10/25/2007, DATETIME), 0, @PageTemplateID=0
THE STORED PROCEDURE SAMPLE
ALTER PROCEDURE [dbo].[usp_CMSTemplateCreateNewTemplate]
@SiteID AS INT,
@PageID AS INT,
@Title AS NVARCHAR(50),
@EditHREF AS NVARCHAR(512),
@CreatorID AS INT,
@StartDate AS DATETIME,
@EndDate AS DATETIME,
@Child AS INT,@PageTemplateID AS INT OUTPUT
AS
Hi,
How do I execute all the .sql scripts in a folder with OSQL command?
Thanks in advance,
Hari Haran Arulmozhi
Hello, the following code works perfectly in SQL Server 2000 and SQL Server 2005 Express over WinXP but when run against an instance of SL Server2005 Express over Win2003Server, the first time Command.Execute is invoked returns no error (even though no action seems to be take by the server), subsequent calls return the error -2147217900 couldn't find prepared instruction with identifer -1 (message may vary, it is a translation from may locale)
Any ideas?
Thanks
Code Block
Public Sub Insert_Alarm(sIP As String, nAlarm As Long)
Static cmdInsert As ADODB.Command
Static Initialized As Boolean
On Error GoTo ErrorHndl
If Not Initialized Then
Set cmdInsert = New ADODB.Command
Set cmdInsert.ActiveConnection = db
cmdInsert.Parameters.Append cmdInsert.CreateParameter("IP", adVarChar, adParamInput, Len(sIP), sIP)
cmdInsert.Parameters.Append cmdInsert.CreateParameter("Alarm", adInteger, adParamInput, , nAlarm)
cmdInsert.CommandText = "insert into ALARMS(date_time,ip,alarm,status) values (getdate(),?,?,1)"
cmdInsert.CommandType = adCmdText
cmdInsert.Prepared = True
Initialized = True
End If
cmdInsert.Parameters(0).value = sIP
cmdInsert.Parameters(1).value = nAlarm
cmdInsert.Execute
Exit Sub
ErrorHndl:
...
End Sub
Hi!
I try to find out how to write an tsql program that sends dmx-ddl to SSAS.
this works:
select * from openquery(bdjOLAP, 'select <col> from <modelname>.content')
because it returns an resultset.
but how to something similar this (would fail, because no result set is returned):
select * from openquery(bdjOLAP, 'create mining structure...')
Best regards
Bjorn
Hello.
We have an ASP 3.0 application that currently works "correctly" on one server, Server A, and we€™re testing it on another server, Server B, which is 64 bit.
The connection string for Server A is:
DRIVER={SQL Server};SERVER=...;DATABASE=...;UID=...;PWD=...
The connection string for Server B is:
PROVIDER=sqloledb;SERVER=...;DATABASE=...;UID=...;PWD=...
(Note: Both servers point to the same database on the same server)
The unexpected behavior occurs after calling .Execute on a command. Here is some sample code:
Dim DBConn
Set DBConn = CreateObject("ADODB.Connection")
DBConn.Open strDBConnection '(the ones shown above)
Dim objCmd
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = DBConn
objCmd.CommandType = adCmdStoredProc
objCmd.CommandText = "sp_TestProcedure"
objCmd.Parameters.Append objCmd.CreateParameter("@Name", adVarChar, adParamInput, 50, "Test")
Dim rs
Set rs = objCmd.Execute
For this example, the stored procedure sp_TestProcedure is:
CREATE PROCEDURE sp_TestProcedure
@Name varchar(50)
AS
INSERT INTO tblTest ([Name], Date) VALUES (@Name, getDate())
SELECT COUNT(*) AS 'Count' FROM tblTest
The basic point is the stored procedure does an INSERT and then a SELECT.
Now... to the issue. On Server A, the variable rs above ends up with a single open Recordset which is the results of the SELECT statement.
However, on Server B, rs is set to a closed recordset, and rs.NextRecordset() gets a second recordset of the results of the SELECT statement.
I understand what's going on. Server B is first returning the number of rows affected by the INSERT which translates to a closed recordset. But Server A does not do this.
I would like to know why the default behavior of the command's .Execute is different on the different servers. Does it relate to the Provider/Driver settings in the connection string? Does it have anything to do with 64 bit VS. 32 bit servers?
I know that one way to address this issue to add SET NOCOUNT ON to the start of the stored procedure. But we have many stored procedures, and if the solution is a change in the connection string, that would be preferred. Also, whatever the possible solution is, I also looking to discover *why* it's happening.
Any help would be greatly appreciated.
Guys
Hunting high and low for the definitive answer to this query.
I need a system to enable the import of a csv file (trypically contacts exported from Outlook) into a SQL Server table unique to that user through a .aspx page.
At the first and most basic level I need to simply get the data into a table.
The ultimate goal though is to have a fully functioning system whereby the user can sync between the csv file and the database enabling them to upload the data for the first time then only update changes subsequently (new records/changes to existing records) etc.
1. Does anyone have code that does such a thing?
2. Does anyone know of a component/utility that can be purchased off the shelf.
3. Is there someone with the capacity to write such a module?
regards in anticipation
Andy.
Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()
With this sql command:
"exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this?
Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
Sorry for grammatical mistakes.
Hi, I need to send a table data into flat and then ftp into different location.
I was using xp_cmdshell via sql task but my network engineer is saying that this xp_cmdshell will break the security and recomond to use "Execute Process Task". If i'm using this task getting the below error.
Could you advice me regrding network engineer thought and any solution for avoiding this error.
---------------------------
Execute Process Task: C:WINDOWSsystem32ftp.exe
---------------------------
CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code 2, which does not match the specified SuccessReturnCode of 0.
---------------------------
Thanks,
Hi!
Thanks For your reply!
but this is very urgent please help!!!!!!!
I need one more help in this issue. what if i do not need any "
for e.g: i am trying to set conn string and varaible value
jobCommand = new SqlCommand("xp_cmdshell 'dtexec /f "" + path + "" /Conn "" + Packconn + "" "" + connect + "" '",cconn);
i am getting some value like this :
CommandText "xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx" /Conn "SE413695\AASQL2005.TestDB;" "Provider=SQLNCLI.1;Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;" '" string
I do not need the highlighted escape characters in it. what should i do??????
i need some thing like this :
xp_cmdshell 'dtexec /f "D:SSISProjectIntegration Services Project1ArchiveMainMultiTables.dtsx" /Conn SE413695AASQL2005.TestDB;"Provider=SQLNCLI.1;Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"'
Thanks,
Jas
Hi,
I'm looking into the idea of building an enhanced version of dtexec.exe that builds in some extra logging features. My utility will execute packages using the Package.Execute() method.
Thing is, I'd still want to support all of the command-line options that dtexec supports. For example, my utility should accept "/set package.variables[myvariable].Value;myvalue" and pass it through to the executing package but I can't find a way of doing it using Package.Execute().
Am I missing something or is this just not possible?
Thanks
Jamie
[Microsoft follow-up]
Hi all,
Criteria:
We connect to the remote database servers through the network from loca by using Query Analyzer.
Previously we were able to execute the xp_cmdshell command from local Query Analyzer to fetch the remote databases data.
But now we are unable to execute the xp_cmdshell command on remote databases from local Query Analyzer
We do not know what happened but i think due to network updates this command is not able to execute...
For ex:
Previously i was able to execute master..xp_cmdshell 'net start' from local Query Analyzer.But now not able to execute
Now my question is, is there any other way(Directly or indirectly) to execute the xp_cmdshell command on remote databases from local?
Note : we are able to execute this command on remote Query Analyzer but not from local QA
Any suggestions would be very very helpful to me?
Thanks in advance,
Good afternoon-
Can a batch file that resides on another server be executed from a different machine? I have a batch file that resides on a server that I would like to run using SQL 2005 Integration Services. Is there anything I can do that would allow me to remotely execute this batch file and have it run in that environment.
BATCH FILE:
cd C:Trandev
otrun -at OTRecogn.att -DINPUT_FILE=%1 -tl 1 -cs dv -lg mylog -I
C:Trandev represents the remote environment
I have tried mapping the remote machine to a network drive on my local machine and using that drive to execute the batch file in an Execute Process Task, but it does not work.
SSIS:
I have a FOR EACH loop grabbing files and writing fileName to a variable that is passed to the Process Task as an argument through an expression(%1 in the batch file above). The Working Directory is a mapped network drive. The Executable is also a network drive plus batch file name.
Any help would be appreciated.
My computer is a HP Compaq dc7100, 512mb RAM, WindowsXP
Hi,
I'm very new to SSIS and I€™m trying to do the following in a SQL task
RESTORE DATABASE @DatabaseName FROM DISK = @Backup WITH FILE = 1, MOVE @OldMDFName TO @NewMDFPath, MOVE @OldLDFName TO @NewLDFPath, NOUNLOAD, REPLACE, STATS = 10
I'm using an OLE DB connection and I have mapped user variables to the various parameter names. Unfortunately when i test the above command it fails on must declare the scalar variable "@DatabaseName". How can i get my values to be substituted into the command?
Many thanks
Martin
For several weeks I have been wrestling with this common problem! I could not connect to my SQL Server 2005 (express edition) server remotely.
I read all the documentation. I did days of research and found no solution! Many seem to have the problem, but none seem to have the answer! I followed every suggestion, including Microsoft's own instructions and NOTHING!
So, just on a whim, I tried something... and it worked.
In my connection string, I added the instance name... (DATA SOURCE=127.0.0.1SQLEXPRESS)
Crazy, but true.
I hope this helps anyone struggling with the same issue!
How to execute ssis package from command prompt and also pass configuration file to it and set logging to ssis log provider for sql server. Writing all those options with cmd.
View 3 Replies View RelatedI've a S.P. that need to execute in local PC, how can I execute ?
View 1 Replies View RelatedI have a ForEachLoop parent pkg that reads thru the file system of dtsx to be executed.
I would like to pass CLI parms to the command itself (dtexec). I'm not looking at passing values to the child pkgs but rather additional command line values such as:
/CONFIGFILE "C:myconfig.dtsConfig.
In my expression builder for the PackageName I added the additional string but during the execution, the reference to my dtsconfig is ignored without any errors and the child pkg runs using the default values provided at design time.
@[User::PackageToRun] + " /CONFIGFILE C:\myconfig.dtsConfig ".
How does one add additional CL values during the runtime for Execute Package Task?
Thanks,
Anatole
Hi all,
I apologize in advance for the lack of homework before posting, but I am just looking for a quick answer, or a quick point in the right direction to figure this out.
I have a developer from an adjacent team that wants to use Reporting Services in a SQL Server 2005 installation.
From what I found online during what I thought was a definitive search yesterday, I gave him the MSDN SQL Server 2005 DVD and told him that Reporting Services is now part of the standard installation. Which is what my brief search yesterday indicated to me.
He did the installation (full, according to him) and said he could not find Reporting Services.
So, the quick question is...
Where is Reporting Services in SQL Server 2005? I don't have a separate MSDN DVD that contains it as was the case on SQL Server 2000. This further supports my (wavering) position that it is part of the standard installation.
Thoughts? Direction? Collective kicks in the posterior?
Thanks!
I have created a job to execute a SSIS package located in the SSIS Package Store. When starting the job I receive an error. The history log reports:
View 12 Replies View RelatedI'm trying to execute a simple VBS file from the Executable command line in the Execute Process Task Editor.
My line is this : cscript.exe "c:convertcsvssisXlsToCsv.vbs"
SSIS keeps saying there are illegal characters here. Â I've Googled and looked about 20 articles and I can't resolve it.
I have a ForEach that loops through Excel files and changes them to CSV files using code i found. This script takes an original Excel file and transfers it to a new CSV file in a new directory.
So in DOS at the CMD line I would type : XlsTocsv.vbs originalfile.xls newfile.csv
I have the original file and new file in the Arguments line so I'm assuming that after the script executes it will look at the filepaths in the loop and loop through them so I want it do to this when it runs:
XlsTocsv.vbs [User::@ExcelFile] [User::@CSVFile]
I just can't get it to execute and I keep getting illegal characters. Â
I installed, uninstalled, deleted, editted my registry etc and I finally goto to this error while trying to install VS2005 (released) on a Windows 2000 PC (that had Beta2 and RC1 installed & uninstalled):
View 12 Replies View Related