|
Hi All,
I have a very strange problem with xp_cmdshell. Here is a Stored Procedure that is meant to execute a VB script file.When i run the VB Script independently it works fine.
But when i execute the stored procedure that is pointing to this VB script file it runs with No error But Doesnt do the work.
Stored Procedure
USE [Database]
GO
/****** Object: StoredProcedure [dbo].[sp_SendSMS] Script Date: 06/27/2010 10:14:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_SendSMS]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Numbers NVARCHAR(200)
DECLARE @Body NVARCHAR(160)
DECLARE @Command NVARCHAR(500)
-- Insert statements for procedure here
SET @Numbers = '00442345672'
SET @Body = CAST(GETDATE() AS NVARCHAR(20)) + ' Test Message'
-- TODO: Get Revenue Statistics and append them to @Command
SET @Command = 'd:\test\SendSMS.vbs /Numbers:' + @Numbers + ' /Body: "' + @Body + '"'
EXEC xp_cmdshell @Command
END
The VB script file is under test folder on D drive which has Read write permission set on the folder.
Set WshShell = WScript.CreateObject("WScript.Shell")
sNumbers= WScript.Arguments.Named.Item("numbers")
sBody= WScript.Arguments.Named.Item("body")
URL = "http://sampleweb.com/samplepage.aspx?IFVERSION=210000&MESSAGETYPE=10&OADC=00447956053317&MESSAGEID=3333&RECEIVETIME=20070320151916&BODY=This%20is%20my%204th%20message%20on%20the%20LIVE%20TV&MCLASS=2&HEADER=0048001&DESTADDRESS=1234&CONNECTION=MIG01l1&DCS=240&RETRYCOUNT=3&PID=0&AVSTATUS=1&GUID=9BBA5E93-390C-450C-94C3-1122E5D4858B"
WshShell.Run(URL)
Set WshShell = Nothing
When i run the VBS file Independently it works fine.However when running the stored procedure above which contains the file i dont see the file being excuted.
Could You please advice what is going Worng?
Thanks for your time.
modified on Sunday, June 27, 2010 6:43 AM
|
|
|
|
|
So let me get this straight; you're using a stored proc, to execute a VBS file which makes an http request to send an SMS? Multiple points of failure doesnt even begin to describe this monster.
Anyway, in answer to your question, its almost certainly a permissions problem. When you execute the VBS file directly, its running in the context of the currently logged in user. When you run it using xp_cmdshell its running in the context of whichever user SQL Server is running as.
|
|
|
|
|
Thanks for your reply.Its Much appreciated.I have already checked and changed the user who loggs in as An is An administartor.
Is there any thing i have to do to force the Login name to remain as the one who had access to that particualr folder.
Many thanks for your Info.
|
|
|
|
|
There are so many things wrong with that response.
It_tech wrote: I have already checked and changed the user who loggs in as An is An administartor
That is why you can run the VBS manually. your logged in user is an admin, and as an admin has rights to run the script. SQL Server runs as a service, it knows nothing about your logged in user, and has a completely different set of permissions.
It_tech wrote: Is there any thing i have to do to force the Login name to remain as the one who had access to that particualr folder.
This problem has exactly nothing to do with folder permissions. You can give a user permission to read a file from a folder, but they may still be denied things like "Execute processes".
If you have acccess to sys admins, ask them for help. If you dont then start reading up on the permission system in operation for the OS you're using. It varies quite substantially between older generation servers like Win2K and new generation servers like Win2K8.
Finally, nobody here will be able to help you on this problem, as none of us have access to your servers, and all servers are set up differently.
|
|
|
|
|
|
Thanks for the Reply.
I have checked the login on Sql Server 2008 using Services.msc and changed
the login to the login i use for windows.
Its is still the same.
Is there any advice you can suggest?
Thanks.
modified on Monday, June 28, 2010 9:34 AM
|
|
|
|
|
i have one table SAMPLENAME with 500 record
in which some record SampleName in Sample i want to find out those SampleName
How can i do this plz help
|
|
|
|
|
|
Dear All,
Please help me to JOIN 2 table from different server.
Thank you.
|
|
|
|
|
1. Some "more" (more than JOIN) information would help.
2. Read the guidelines at the top of this page.
3. Where is the question?
Greetings
Covean
|
|
|
|
|
So idea of what databases you're working with would help.
|
|
|
|
|
naunt wrote: Please help me to JOIN 2 table from different server.
Infrastructure/Environment fail.
Now, to answer your question; execute sp_addlinkedserver[^] to link the two servers, then using the server name to fully qualify a table, write a join in the normal way.
This post assumes MS SQL Server.
|
|
|
|
|
Thank you for your reply.
I am really really sorry for my uncomplete question.
I am using SQL server 2000.
@J4amieC, Thank you. Will try with "sp_addlinkedserver[^]".
|
|
|
|
|
I have a table in MS Access 2007 database I want to make a chart with, column chart (or whichever one is vertical). The table holds information on various species of insect I recorded over the year, so I could have multiple records for one species. Anyway what I have wanted to do, but haven't been able to figure out what is that:
I wanted to make a chart that had Jan, feb, march (all the way to december) on the X axis and up the Y axis I wanted a number (not sure of the number needed on it, up to 500 possibly) and what I wanted to do with the chart is show how many of a particular species I found each month of the year.
The fields I wanted to get the information with from the table are the scientific name, date, stage and quantity. The scientific name will be displayed as the title of the table and this is the species I want to find out about. The date is the date that month I recorded the species and the quantity is how many I recorded on that date. I have another field which is called "stage" and when you open the chart it will ask you two things, the scientific name and the stage as the stage (adult, larvae etc...) is quite inomrtant to what I want and I want the stage to be displayed on the chart.
Just say I wanted to know about Pieris Napi, I want to type this in when I open the chart, next type in Adult as the stage and I would get a chart showing Jan-December and the total I found each month in a column chart with its scientific name dispayed on title and stage (wether its an adult or pupae etc..)
Anyone know how would I do this? I think I would need to make a query from the table to get the information on the scientific name and stage, but not sure what else to do, any help would be appreciated.
In the end we're all just the same
|
|
|
|
|
I think you probably want to use a parametrized query with species as input.
I'm slightly confused about the stage - should the chart only show the selected stage or all stages?
If you go this route then the query should group by month and stage. That should easily be done in designer.
An alternative might be to use a PivotTable as the source for the chart.
My Access is pretty rusty but I think this is fairly straightforward. I'm not too good on charts - never really used them in Access.
Would it be better to give the user a list of species and allow them to select from the list. Saves all sorts of bother if the user gets the spelling wrong.
Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis
The only valid measurement of code quality: WTFs/minute.
|
|
|
|
|
Hi Dave,
First, for stage, prefer a drop down list to choose the stage and the results change accordingly, just show one type of stage, say a table showing the Pieris Napi at adult stage only.
What you suggested on having a drop down list to chose the scientific name would be far better, how would I do this so you can choose what you want proir to creating the table, would an input form work best for this, choose the scientific name and stage on a small form then press a button to take you to a chart on that species. I know how to create the form, but not how to get the information onto a chart
Havn't used much with charts or inputting data except on tables latley, so I am a bit rusty.
In the end we're all just the same
|
|
|
|
|
Hii..
I m in a great confusion. Can u help me?I want to calculate total work hours of a day using sql function.I m having three tables.
SG_Emp_Master,SG_Daily_Register,SG_Emp_Department.Employee ID,From date,ToDate are the parameters passed..Here is my code..But i could'nt get correct output.pls help me.
CREATE FUNCTION[dbo].[FN_TIME_ATTNDNC_REPORTS] ( @EMPID varchar(50)
,@FROMDATE datetime ,@TODATE datetime )RETURNS TABLE
AS RETURN(
SELECT DR_EmployeeID as EMPID,EM_FirstName+EM_MiddleName+EM_LastName as EMPNAME,ED_Department as DEPARTMENT,
SUBSTRING(CONVERT(VARCHAR,ES_TimeIn,9),14,15)+'-'+SUBSTRING(CONVERT(VARCHAR,ES_TimeOut,9),14,15)as SHIFTTIME,
CASE WHEN ((CONVERT(VARCHAR,DR_TimeIn,101)=CONVERT(VARCHAR,DR_TimeOut,101))AND(SUBSTRING(CONVERT(VARCHAR,DR_TimeIn,9),14,15)>'11:59:59:000PM'))
THEN
ISNULL(CONVERT(VARCHAR,DR_TimeIn,107)+' 12:01:00:000AM','Not checked')
ELSE
ISNULL(CONVERT(VARCHAR,DR_TimeIn,109),'Not checked')
END
AS TIMEIN,
CASE WHEN(CONVERT(VARCHAR,DR_TimeIn,101)!=CONVERT(VARCHAR, DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR,DR_TimeIn,107)+' 11:59:59:000PM','Not checked')
WHEN (CONVERT(VARCHAR,DR_TimeIn,101)=CONVERT(VARCHAR,DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR,DR_TimeOut,109),'Not checked')
END
AS TIMEOUT,
CASE WHEN(CONVERT(VARCHAR, DR_TimeIn,101)=CONVERT(VARCHAR, DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut)/3600)
+':'+
CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut )%3600/60)
+':'+
CONVERT(VARCHAR(5),(DATEDIFF(SS,DR_TimeIn,DR_TimeOut)%60)),'0 ')
WHEN(CONVERT(VARCHAR, DR_TimeIn,101)!=CONVERT(VARCHAR, DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR,DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))/3600)
+':'+
CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR, DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))%3600/60)
+':'+
CONVERT(VARCHAR(5),(DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR, DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))%60)),'0 ')
ELSE 'NOT CHECKED'
END
AS HOURS
FROM SG_Daily_Register
INNER JOIN
SG_Emp_Shift on SG_Daily_Register.DR_ShiftID=SG_Emp_Shift.ES_ShiftID
INNER JOIN SG_Emp_Master on SG_Daily_Register.DR_EmployeeID=SG_Emp_Master.EM_EmployeeID
INNER JOIN SG_Emp_Department on SG_Emp_Master.EM_DeptID=SG_Emp_Department.ED_DeptID
WHERE DR_EmployeeID=@EMPID AND CONVERT(VARCHAR,DR_TimeIn,101)BETWEEN @FROMDATE AND @TODATE
UNION
SELECT DR_EmployeeID as EMPID,EM_FirstName+EM_MiddleName+EM_LastName as EMPNAME,ED_Department as DEPARTMENT,
SUBSTRING(CONVERT(VARCHAR,ES_TimeIn,9),14,15)+'-'+SUBSTRING(CONVERT(VARCHAR,ES_TimeOut,9),14,15)as SHIFTTIME,
CASE WHEN ((CONVERT(VARCHAR,DR_TimeIn,101)=CONVERT(VARCHAR,DR_TimeOut,101))AND(SUBSTRING(CONVERT(VARCHAR,DR_TimeIn,9),14,15)>'11:59:59:000PM'))
THEN
ISNULL(CONVERT(VARCHAR,DR_TimeIn,107)+' 12:01:00:000AM','Not checked')
ELSE
ISNULL(CONVERT(VARCHAR,DR_TimeIn,109),'Not checked')
END
AS TIMEIN,
CASE WHEN(CONVERT(VARCHAR,DR_TimeIn,101)!=CONVERT(VARCHAR, DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR,DR_TimeIn,107)+' 11:59:59:000PM','Not checked')
WHEN (CONVERT(VARCHAR,DR_TimeIn,101)=CONVERT(VARCHAR,DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR,DR_TimeOut,109),'Not checked')
END
AS TIMEOUT,
CASE WHEN(CONVERT(VARCHAR, DR_TimeIn,101)=CONVERT(VARCHAR, DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut)/3600)
+':'+
CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,DR_TimeOut )%3600/60)
+':'+
CONVERT(VARCHAR(5),(DATEDIFF(SS,DR_TimeIn,DR_TimeOut)%60)),'0 ')
WHEN(CONVERT(VARCHAR, DR_TimeIn,101)!=CONVERT(VARCHAR, DR_TimeOut,101))
THEN
ISNULL(CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR,DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))/3600)
+':'+
CONVERT(VARCHAR(5),DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR, DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))%3600/60)
+':'+
CONVERT(VARCHAR(5),(DATEDIFF(SS,DR_TimeIn,CAST(CONVERT(VARCHAR, DR_TimeIn,101)+' 11:59:59:000PM'AS DATETIME))%60)),'0 ')
ELSE 'NOT CHECKED'
END
AS HOURS
FROM SG_Daily_Register
INNER JOIN
SG_Emp_Shift on SG_Daily_Register.DR_ShiftID=SG_Emp_Shift.ES_ShiftID
INNER JOIN SG_Emp_Master on SG_Daily_Register.DR_EmployeeID=SG_Emp_Master.EM_EmployeeID
INNER JOIN SG_Emp_Department on SG_Emp_Master.EM_DeptID=SG_Emp_Department.ED_DeptID
WHERE DR_EmployeeID=@EMPID AND CONVERT(VARCHAR,DR_TimeIn,101)BETWEEN @FROMDATE AND @TODATE
)
|
|
|
|
|
Mate, please format the code because there are many smileys in your sql query.
|
|
|
|
|
Hi All,
I want to get all the values of a column as comma separated values in SQL Server. Can anybody help me writing the query for that.
Thanks in advance.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
Here is an example
DECLARE @String VARCHAR(1000)
SET @String = ''
SELECT @String = @String + ',' + Column1
FROM Table1
SELECT @String
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi guys,
I am having an inlined table valued function to search the users in my site.
The search is optional ie. sometimes user will search with first name, sometimes he searches with last name, and sometimes both.
As of now I am using if-else statements to check whether first name is ''(empty),or last name is ''(empty) or both are ''(empty).
I have two questions?
1. Is there any other way, so that I can avoid these if-else statements and make it a single query?
2. If I am using a function as shown below, I have to specify all the return parameters. Is there any way I can avoid this and return the complete table(I do not want to use stored procedures)?
I'm using a freetext search,because I want to get the search results according to relevence(ranked results).
ALTER FUNCTION [dbo].[Search]
(
@FirstName Varchar(400),@LastName Varchar(400)
)
RETURNS
@SearchResults TABLE
(
-- Add the column definitions for the TABLE variable here
UserName Varchar(400),
EmailAddress Varchar(400)
)
AS
BEGIN
IF (@FirstName<>'' AND @LastName<>'')
begin
insert into @SearchResults
Select Top 100 Percent UserName,EmailAddress
From UsersFirst t
JOIN FreetextTable(UsersFirst, FirstName, @FirstName) K On t.Id = K.[Key]
JOIN UsersSecond U on t.UserId=U.Id
JOIN FreetextTable(UsersSecond, LastName, @LastName) As L On U.Id = L.[Key]
ORDER BY K.[Rank] DESC;
end
ELSE IF (@FirstName<>'')
begin
insert into @SearchResults
Select Top 100 Percent UserName,EmailAddress
From UsersFirst t
JOIN FreetextTable(UsersFirst, FirstName, @FirstName) K On t.Id = K.[Key]
JOIN UsersSecond U on t.UserId=U.Id
ORDER BY K.[Rank] DESC;
end
return
END
Thanks In Advance
jophin
|
|
|
|
|
Dear Friend's,
My Issue scenario is as follow's
i have two servers Name
"Server_A"
"Server_B"
In "Server_A" i have one Database "DB1"
In "Server_B" i have one Database "DB2"
in Database "DB1" of Server "Server_A" i have one table "tbl_ListOfCountry"
now as per my business logic that table must be centralize where my client will update information and i need to use this table data in "Server_B".
For that i have create one procedure which pull all the data of table "tbl_ListOfCountry" from "Server_A".
for that i have created Synonym "tbl_ListOfCountry"
using following syntex
CREATE SYNONYM [dbo].[tbl_ListOfCountry] FOR [Server_A].[DB1].[dbo].[tbl_ListOfCountry]
now for security purpose i have created one user "user1" with "Server Role" as "Public" and "User Mapping" as "db_datareader", "db_datawriter" and "public" in "Server_B"
and Grant As Execute Permission of this user to my procedure "Proc1" which use to pull the record.
now the main issue is started
whenever i execute that procedure "Proc1" using "user1" login instead of "sa" it give me error
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'user1'.
Please, help me to overcome this issue...
Thanks in advanced..
Sasmi
|
|
|
|
|
Let me guess. The password for the 'sa' account on DB1(ServerA) is the same as on DB2(ServerB). 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
Thanks for the quick reply...
I am also think same but when i am using this Procedure in my application it is giving me same error can you explain me why this happening....
|
|
|
|
|
I suspect that it works when you are logged in to DB2 as 'sa', because it attempts to use those same credentials on DB1, it will work since 'sa' also exists in DB1 with the same password. When you login to DB2 with 'XYZ', the same login will be attempted on DB1 and since 'XYZ' doesn't exist on DB1, the login fails. The quick and dirty solution is to replicate the userid on DB1 and I suspect that will allow the link to work.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
