DB Engine :: WMI Token Replacement In Agent For TargetInstance?
Aug 4, 2015
I am trying to get details out of targetinstance from a WMI alert, using Token Replacement in the job step.
The alert fires properly.
The WQL query for the alert is:
select * from __InstanceModificationEvent within 300 where targetinstance ISA 'Win32_LogicalDisk' and TargetInstance.DriveType = 3 and TargetInstance.Freespace < 44832876928
However, I can't get the WMI info out from the alert - everything I've read says it's within TargetInstance, but I don't see how to use the WMI call for it. I'm specifically looking for DeviceID In the job step, I've tried multiple variations. Only the __CLASS has worked, and that's because it's not within TargetInstance.Â
set @mybody = '$(ESCAPE_SQUOTE(WMI(DeviceID)))'
set @mybody = '$(ESCAPE_SQUOTE(WMI(TargetInstanceDeviceID)))'
set @mybody = '$(ESCAPE_SQUOTE(WMI(__CLASS)))'
set @mybody = '$(ESCAPE_SQUOTE(WMI(TargetInstance.DeviceID)))'
Subject says it all, really. I want to start using Token Replacement,but do I break anything by enabling it? Do jobs that don't use tokensrequire any changes? I saw somewhere that it can't be turned off, soI'm paranoid about enabling it. Anything that I should be aware of?Many thanks.
Hello all Trying to delete some data from a SSCE (2005) DB produces the exception: SqlCeException There was an error parsing the query. [ Token line number = 1,Token line offset = 43,Token in error = C] Here is the code I am using
I'm trying to insert some values into an SQL Compact database on a WM6 device but there is something apparently wrong with my SQL statement...
The program is going to allow users to schedule an SMS message to be sent at a certain date and time. I'm using a database to keep track of the scheduled SMS messages. The database has 3 rows: phone number, message, and the date/time to be sent.
I'm guessing it doesn't like how I am trying to get the data from the different text boxes and the DateTimePicker to go inside the SQL command. Does anyone have any ideas on how to fix my SQL command or how to get data from a textbox and DateTimePicker to be inserted into a database a different way?
I am using SQL Server 2005 Reporting Services. I want to make it secure. I am also using WCF services and made them secure using Claim based System.Identity Model.
I want to apply same claim based model to Reporting Services.
On Saturday we moved a few databases from SQL Server 2005 to a SQL Server 2012 cluster; and as expected some jobs have been failing because of this. The Job in Question executes two stored procedures and then an SSIS package; however when I ran the Job it failed with the error
Msg 7411, Level 16, State 1, Line 1 Server 'servername' is not configured for RPC
I therefore configured the Server for RPC with the following script: -
exec sp_serveroption @server='servername', @optname='rpc', @optvalue='true' GO But the job failed again. So I therefore reran the script with the reconfigure option: - exec sp_serveroption @server='OCELOT7CLUST', @optname='rpc', @optvalue='true' GO reconfigure with override go
But again the job failed. I then closed SSMS and reopened it and attempt to run the job again and once again it failed.This is not an issue with the Linked Server as the linked Server is connecting as sa.
The SQL Server Agent (MSSQLSERVER) service on server started and then stopped. If i Manually start the services also SQL Server Agent services are stopping automatically.Though I selected Auto Restart SQL Server Agent if it stops unexpectedly, The services are stopping automatically.TCP/IP protocol was enabled for port 1433.Are there any setup I missed?
I have a client using SQL 2k, SP2 (due application requirements, SP3 is notan option - the application vendor will not specify why). We are receiving:[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL ServerConnection BrokenThere doesn't appear to be any rhyme or reason as to why this is happening.And...it shows it's ugly head at random places during execution. Has anyoneexperienced this also, or have any ideas on what to look for? I have seennumerous suggestions stating to upgrade from MDAC 2.6 to 2.7. At thispoint, I'm not sure if thats an option based on the vendors application(which by the way they no longer support!).Any ideas will be greatly appreciated.Greg
Now I have a string: "ab,cd,ef,", and I would like to use SQL to token this string to a string array like "ab" "cd" "ef". Then I want to do a loop to insert these values into a table like:
for( int i = 0; i<aStringArray.length i++) {
insert into aTable(aField) values(aStringArray); }
Here is the skinny. You can use this in a script from what I have done so far. If someone gives it a try in a stored procedure let me know. From what I have seen you can pass the token as uniqueidentifier. The problem is when you try to use it in a query you need to use Convert(char255, JOBID).
I searched all over the place and nowhere did I find something like the above. It seems simple but you can do a select in a table like sysjobhistory without converting the jobid first. Let me know if this helps anyone.
I have been struggling with this error for a while now. Not much when I put it in the search engines. I get the error as follows when I execute this CLR stored procedure:
Msg 10312, Level 16, State 49, Procedure SpPICK00, Line 0
.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.
The code for the stored procedure is as follows. If anybody could offer any advice on what this error means I would appreciate it. I know through debugging that it's erroring out on the ImpContext = ClientID.Impersonate() line.
Dim DbCon As New SqlConnection("context connection=true")
Dim DbSql As New SqlCommand("SELECT TOP 1 * FROM TblTransactions", DbCon)
Dim TransactionID As Int64
DbSql.Connection.Open()
Dim DbRs As SqlDataReader
DbRs = DbSql.ExecuteReader
While DbRs.Read
TransactionID = DbRs("TransactionID")
End While
DbRs.Close()
Dim MenuID As Int16
Dim MONumber As String
Dim LineNumber As String
Dim PTUse As String
Dim SEQN As String
Dim WorkCentre As String
Dim Stock As String
Dim Bin As String
Dim Qty As Double
DbSql = New SqlCommand("SELECT * FROM VwPickList WHERE TransactionID = @TransactionID", DbCon)
We have a test and production environment. After transfering some tables from test to prod and all stored procedures using those tables. We get an error when executing those stored procedures: " DB-library: Possible network error: Bad token from SQL Server: Datastream processing out of sync. Net-library error 0: DB-libray Process Dead - Connection Broken. " When we execute the stored procedure with 1 parameter less we get a parameter missing error. Then we execute the stored procedure again and everything is allright? Has anyone experienced this before? If so, please help. SQlServer 6.50.201
I am using SQL 2005 and I'm trying to find a T-SQL command that will allow me to retrieve a sub string based on the token. For instance, if I had a string like this:
abc^defgh^ij^klmnop^qrs
Let assume the token is the ^ and I want to get the string at the third token klmnop.
is there a simple one line command that I can use to retrieve this data?
Hallo All,I'm making in my DB some logs.I have a separate table containing:ID,Date,Source,Type,ErrorNo,DescriptionAcctualy the table struct is not immportand that's why I do not postthe script.I have one procedure, which I call in my other function procedures,triggers etc. with parameters.This procedure (WriteToLog) is called, if in other procedures, triggersis any validation, exception or only information which I would like tolog me separately, and it writes the record into this table.But sometimes, it is very hard, to follow much logs, and I figured out,if I could have something like transaction ID, task ID, it will be mucheasier.Firs idea was to, by going from procedures to other procedures, pass aself generated token (ie. date with any additional number). But then, Ineed to change everywhere where I call WriteToLog procedure the callsyntax.Let's say that we have following situation:Procedure1:Action1Select1CompareOfValues1exec WriteToLogAction2CompareOfValues2exec WriteToLogAction3exec Procedure2Procedure2:Action1exec WriteToLogSelect1Insert1Trigger1Started:Action1CompareOfValues1exec WriteToLogAction2Trigger1Exit:Procedure2Exit:Procedure1Exit:Now ... I could pass this my generated number, from SP to SP and so on.But I would like to now, does the MS SQL server has something whatidentifies transaction like descripted below.In this case, I do not need to pass any number, only I need to get thisnumber anyhow in SP WriteToLog, and insert it into my log table.Any sugestions?Thank's in advance.Mateusz
I've got a SQL Server hardware Replacement to do. This server has a merge replication setup on it which was setup by a contractor.
Do i have to do the configurations manually when moving the replication bits across two servers or is there a different process involved ? I am just not sure how to approach this.
Hello all, I get some dates entered manually in french format in a varchar(255) field and want to convert that field in datetime. Since, Enterprise manager is warning me about a risk to loose information, I'm thinking about a way to update the french text in the corresponding english text before doing the conversion. Example: 25-fév-08 (varchar(255)) has to become 25-feb-08 (datetime)
I have a job that we wrote in SQL 2000 that loops through and finds all the orders pending verification and distributes them via emal to 100+ sales people every night. Each email is sent to the individual sales rep. I did this using sp_makewebtask and attaching the html file to an email and using a WHILE statement to loop thorugh all pending entries in sql.
I recently learned that sp_makewebtask is not going to be around for the next release and that I should use RSS for all future development.
Question: How can I achieve the same result outlined above using RS? How can I dynamically create and distribute HTML files?
I know that RS is xml based, but some pointer on how/where to start would be much appreciated.
Dear all,I need some help from all my Transact SQL Guru friends out there..Here is the scenario in its most simplified form.. ..I have two tables.. A(Lookup table) and B(Transaction Table)TableA FieldsEmployeeLocationIDEmployeeLocation (This could have values say"B","BO","BOM","C","CA","CALC") etc...TableB FieldsEmployeeIDEmployeeName.......EmployeeLocationID (will have null initially when rows are populatedfirst time)EmployeeLocation (This could have values"BA123","BOMBAY","BOTS123","BRACK".... etc)I hope you get where I am leading this to, from my examples..Requirement is to populate the EmployeeLocationID in Table B withEmployeeLocationID from TableA by matching the field EmployeeLocationin both tables.Please note that table B's EmployeeLocation could be A'sEmployeeLocation + some additionalcodes like "123","RACK" etc in theabove example...Therefore, this is what I had wrote initially..update Bset B.EmployeeLocationID =A.EmployeeLocationID
Help, I have a vendor file with the vendors name. I need to add afield that has alternate captionseg. :In my vendor file I have records for AT&T with three differentnames/spellings for AT&T.AT&TLucent TechnologiesA T & TI want to add a new field that would have AT&T for the above records.I know I could hardcode a queryUpdate F_VENDORSet VENDOR_CAPTION_ALT = 'AT&T'where VENDOR_CAPTION like '%Lucent%' orVENDOR_CAPTION like '%A T & T%'however, how can I use another table that has the my search keywordsfor the "like" and the replacement captions so I dont need literalqueries to update all the diffent vendors?I have identified 96 instances of where I want to have an alternativename that I can use for a type of grouping. This gets really wildwith the military such as Army and Navy.TIARob
In the BOL, it states to replace DB_ID() with a valid database name when the compatibility level is 80 or below.
The original statement is:
SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
I have multiple sites trying to communicate with a SQL Server 2012 Express database at another remote site. At one site I am unable to connect to the remote server. If I try to use my program I get this message:
System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The token supplied to the function is invalid)
If I try to connect using SSMS I get the same error.I have been unable to find any reference to this message on the internet.
We are using SQL Server 2005 64 bit standard edition on both ends (subscriber distributor and publisher ) publisher is its own distributor. Now i want to investigate the replication performance and delayed in replication for this purpose i found that tracer tokens can be settedup but when i open replication monitor but unable to see tracer token tab.
Plz let me know something.why i am unable to see this tab, is there some problem of rights ? , permission, or installtion.
We have a sql server 2005 sp2 on w2k3 standard server. The servers started crashing every 4/5 days and have done this twice so far. checking the event viewer there are always events(2020) happening before the crash.
I have done some memory leak monitoring using performance monitor/poolmon/process explorer. and here is my findings:
sqlservr and lsass handles count keeps increasing linearly with time, i restarted the sql service yesterday and the count originally was at 900, this morning the count is at 29,000 for lsass and 28,000 for sqlservr.
using process explorer i can see that the majority of these handles are security context tokens.
we have a monitoring agent running on that server(altiris agent) which continousely logon/logoff to SQL to do some metric checks. so basically the access tokens created as a result of that never get released during the logoff.
also i noticed in poolmon that TOKE has the highest Bytes consumption of paged memory(50MBs) the token sizes are small (<1kb). so that kind correlates with the handles count problem mentioned above, since token are stored in kerenl paged memory.
Any ideas on how to troobleshoot this further is appreciated. right now i can only think of settings up a restart scheduel on sqlserver to aleviate the problem. but i need a long term solution which i am trying to work out.
In VS 2003 I used SQLDMO (Com Object) to list all available SQL Servers. Is in SQL Server 2005 a managed .net Component that can do that task?Thanks,Rainer.
Been meaning to post this for a while. It does a very limited job of only allowing [0-9], but could be extended to allow negative numbers, or numeric values that are suitable for numeric types other than INT, but avoiding the pitfalls of IsNumeric() which might allow through data not suitable for some of the numeric datatypes
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_IsINT]') AND xtype IN (N'FN', N'IF', N'TF')) DROP FUNCTION dbo.kk_fn_UTIL_IsINT GO CREATE FUNCTION dbo.kk_fn_UTIL_IsINT ( -- String to be tested - Must only contain [0-9], any spaces are trimmed @strINTvarchar(8000) ) RETURNS int-- NULL = Bad INT encountered, else cleanedup INT returned /* WITH ENCRYPTION */ AS /* * kk_fn_UTIL_IsINTCheck that a String is a valid INT *SELECT dbo.kk_fn_UTIL_IsINT(MyINTColumn) *IF dbo.kk_fn_UTIL_IsINT(MyINTColumn) IS NULL ... Bad INT * * Returns: * *int valueValid integer *NULLBad parameter passed * * HISTORY: * * 30-Sep-2005 Started */ BEGIN
DECLARE@intValueint
SELECT@strINT = LTRIM(RTRIM(@strINT)), @intValue = CASE WHEN @strINT NOT LIKE '%[^0-9]%' THEN CONVERT(int, @strINT) ELSE NULL END RETURN @intValue
shailendra writes "Hi,everbody lets come to my problem.I have one variable of type "ntext" which contain character "". i want to remove it or replace it with a blank character.
