Monday, March 19, 2012
Got it!
ALTER procedure admin_ConvertUnix2Dos
as
declare @.dir varchar(256)
declare @.FileName varchar(256)
declare @.Convert varchar(512)
declare @.Exec varchar(512)
create table #tmp
(FileName varchar(256))
set @.dir = 'dir "C:\Documents and Settings\chris.rose\My Documents\FTP\" /B
'
insert into #tmp exec master..xp_cmdshell @.dir
declare MyCur cursor for
select FileName from #tmp
open MyCur
fetch next from MyCur into @.FileName
while @.@.fetch_status = 0
begin
set @.Convert = 'c:\Unix2Dos\Unix2Dos.exe '+replace(@.Dir,'" /B
',@.FileName+'"')
set @.Convert = replace(@.Convert,'dir','')
exec master..xp_cmdshell @.Convert
fetch next from MyCur into @.FileName
end
close MyCur
deallocate MyCur
"ChrisR" <noemail@.bla.com> wrote in message
news:e7b%23CC3oFHA.2080@.TK2MSFTNGP14.phx.gbl...
> I've been messing with the quotes and am getting nowhere quickly. Any
> ideas?
>
> "ChrisR" <noemail@.bla.com> wrote in message
> news:eYhBM12oFHA.3828@.TK2MSFTNGP12.phx.gbl...
>You removed the double quotes around the path of the EXE file? I think the p
roblem is that when you
have two sets of double quotes (as in the first version), you need to enclos
e the hole shebang in
double quotes:
""c:\Unix2Dos\Unix2Dos.exe" "C:\Documents and Settings\chris.rose\My
Documents\FTP\CABHLDRLSACTNCONSTANTS.TAB;1""
Not needed now as you don't have spaced etx in path to Unix2Dos.exe, but mig
ht be worth knowing for
next time...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ChrisR" <noemail@.bla.com> wrote in message news:uAmBsL3oFHA.3996@.TK2MSFTNGP12.phx.gbl...[c
olor=darkred]
> Thanks Tibor...
>
> ALTER procedure admin_ConvertUnix2Dos
> as
> declare @.dir varchar(256)
> declare @.FileName varchar(256)
> declare @.Convert varchar(512)
> declare @.Exec varchar(512)
> create table #tmp
> (FileName varchar(256))
> set @.dir = 'dir "C:\Documents and Settings\chris.rose\My Documents\FTP\" /
B '
> insert into #tmp exec master..xp_cmdshell @.dir
> declare MyCur cursor for
> select FileName from #tmp
> open MyCur
> fetch next from MyCur into @.FileName
> while @.@.fetch_status = 0
> begin
> set @.Convert = 'c:\Unix2Dos\Unix2Dos.exe '+replace(@.Dir,'" /B ',@.FileName+
'"')
> set @.Convert = replace(@.Convert,'dir','')
> exec master..xp_cmdshell @.Convert
> fetch next from MyCur into @.FileName
> end
> close MyCur
> deallocate MyCur
>
> "ChrisR" <noemail@.bla.com> wrote in message news:e7b%23CC3oFHA.2080@.TK2MSF
TNGP14.phx.gbl...
>[/color]
Sunday, February 26, 2012
GO statement in SQL scripts
Can anyone tell me exactly what the significance of the GO statement is in a SQL script, what it does and under what circumstances it is really necessary.
Thanks for your help.
itsmarkdavies@.hotmail.combasically you need a go any time you issue a:
CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, or CREATE VIEW statment. Also a table can not be altered and then referenced in the same batch.
Check out Books On Line, on the index tab type in the keyword "GO". BOL has a very goo explination of the use of "GO" and batches in general.|||So Paul it sounds like 'GO' is like 'commit' when you create new objects in your script ?|||Not quite, transactions are used to group operations into a logical work unit which you commit or rollback based on some test. A "go" is syntacticaly required to execute some TSQL statments. Consider the following:
Code:
----------------------------
begin transaction
create procedure test_getstuff as select db_name(), user_id()
exec test_getstuff
rollback transaction
if object_id('test_getstuff') is not null
print 'test_getstuff exists'
else
print 'test_getstuff does not exist'
----------------------------
will generate an error, however
Code:
----------------------------
begin transaction
go
create procedure test_getstuff as select db_name(), user_id()
exec test_getstuff
rollback transaction
if object_id('test_getstuff') is not null
print 'test_getstuff exists'
else
print 'test_getstuff does not exist'
go
sp_helptext test_getstuff
go
----------------------------
will work because TSQL requires a create procedure to be the first command in a batch. Notice, however, that the stored procedure does a little more than expected. Lastly:
Code:
----------------------------
begin transaction
go
create procedure test_getstuff as select db_name(), user_id()
go
exec test_getstuff
sp_helptext test_getstuff
rollback transaction
if object_id('test_getstuff') is not null
print 'test_getstuff exists'
else
print 'test_getstuff does not exist'
go
----------------------------
The 2nd go is used to terminate the batch. Now the sp looks correct.
Notice that in all of this I destroyed all my work by wrapping everything in a transaction. I could just ahve easily saved all my work by using a commit.|||Hi,
Use the "GO" statement if you want to end the SQL Statements and want SQL Server to execute till that.
Mr. Paul and Mr.Youngman has explained in a nice way.
You can add these below basic info also along with that.
Basically SQL Server must understand where Your SQL script ends.
So, Microsoft uses "GO" to terminate/end SQL Statements. Oracle uses ";" to terminate/end SQL Statements.
As soon as SQL Server sees "Go" statement it understands that SQL Statement is ended and it can start executing the SQL Statements till that point.
I can put in this way, like a man looking for pebbles. as soon as he finds a pebble, he picks and puts in a bag and looks nearby whether there are some more and keep going. Similarly SQL Server looks for "Go" and as soon as it sees "GO" it thinks, Ok I found a "go" and I have to end there and pick all the SQL Statements I come across and start executing.
Thats it.
Have Fun :)
Varad01
Globale Function
Hi !
Is it possible to declare a gloable function in reportServer that all reports could access? For each reports that use the same function i've declared it in the code property of the rapport but it will be easier if i could share the function for all reports.
Thanks !
You could move the functions into a custom assembly. Check the "custom assembly" section of this BOL page: http://msdn2.microsoft.com/en-us/library/ms155798.aspx
-- Robert
global vars in script files ?
workaround ?
[ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
In the following script, the local var @.ptId is lost once a "GO" is
executed.
USE [ContentDB]
GO
INSERT INTO [dbo].[PageTypes]
([ptName]
,[ptPath]
,[ptParamName])
VALUES
('unused'
,'/redirect.aspx'
,'url')
DECLARE @.ptId int
SET @.ptId = @.@.IDENTITY
.
.
.
.
<lots and lots of other SQL>
.
.
.
.
GO
.
.
.
.
<lots and lots of other SQL>
.
.
.There are no global variables in TSQL. You can use a temp table for this, or
check out SET
CONTEXT_INFO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:eYtWs8JRGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Is it possible to declare a global variable in a SQL script ... or some wo
rkaround ?
> [ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
> In the following script, the local var @.ptId is lost once a "GO" is execut
ed.
> USE [ContentDB]
> GO
> INSERT INTO [dbo].[PageTypes]
> ([ptName]
> ,[ptPath]
> ,[ptParamName])
> VALUES
> ('unused'
> ,'/redirect.aspx'
> ,'url')
> DECLARE @.ptId int
> SET @.ptId = @.@.IDENTITY
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
> .
> GO
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
>
global variables urgent,
i declare 3 global variables inside a formula.but can not refer those variables in another formula in the same report.
tell me how & where to declare global variables.
thanx.hi
try using this way :
formula @.x
numbervar a;
a:=10+20;
formula @.y
numbervar b;
numbervar a;
b:= 20+30;
b:=a+b;
as many variable as you want decalre in a formula, but the each formula should declare the variable.
This will give an idea about the global varialbles.
please try and let me know.|||Use shared variable also. Read it more in help file
Global Variables
Is there a way to declare a persistent global variable in SQL Server?
I'd like my stored procs to fetch data in a different source depending on a debug (or development) variable.
For example, I'd like to be able to set a variable to either 0 or 1 (true or false) and have a static SP defined as:
IF @.MYVARIABLE = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')
What do you think? Since these SPs should be called a lot, I don't want to store the info in a table, I want it as a global variableso it will be as fast as possible.
Any other suggestions are also welcomed.
Thanks,
Skip.Wouldn't you just put this into a table? Then add the statement:
DECLARE
@.Environment bit
SELECT @.Environment = Environment FROM tblFlags
IF @.Environment = 1
SELECT * FROM Openquery(Server1, 'SELECT * FROM Table1")
ELSE
SELECT * FROM Openquery(Server2, 'SELECT * FROM Table2')
But wouldn't it be better to have a dedicated test server with data refreshed as you require? Sorry, may not always be realistic, but just a thought.
Regards,
hmscott|||First of all, yes having a dev server makes way more sense. Actually, this is what we have here, the switch (the if...else) will be used to create the linked server to point to the correct server instance. The example above was only a reference and did not represent an actual situation.
Second, what I have now is a variable in a "commands" table. It works fine but the function is called so many times that it slows my system. I'd like to have a global variable in memory to speed up the process of reading its value.
Any other suggestions?
Thanks,
Skip.|||Nothing comes to mind. You can use the PINTABLE command to put that table into memory, but if it is called so frequently, it's probably already there.
You said you have a "commands" table, but that the function (what function?) is called so often that it slows the system. Just to check, is the table very large? Is it properly indexed? What do you consider to be heavy usage?
We have a particular sp that is called more or less each time a user hits a page on our site (to pull back configuration and web settings). We have about 300 users and about 2000 page hits per day (frequently more). I've never had any complaints about this particular sp running slowly.
Regards,
hmscott
Global variable scope - across package
I have a Main.dtsx file which is having executing some 10 .dtsx packages. Can I declare a variable in Main.dtsx and use it in all the other packages which it is executing?
Note: I am using Yukon April CTP
Thanks
HariniYes you can. This post explains how: http://blogs.conchango.com/jamiethomson/archive/2005/09/01/2096.aspx
-Jamie|||Thanks. This is what exactly I want.
Friday, February 24, 2012
Global Variable
I want to know that how can i declare a global variable in database, assign some value to it, then using it in multiple triggers and procedure then deallocating that.
Please provide a smal example.
Regards,
Shabber.Create a User-Defined Function that returns the value?|||And deallocate it?
What the heck are you doing?
UDF is the only way to simulate a global variable, but if you then wipe out your UDF it will break your sprocs.|||I'd assume Shabber has no big need for deallocating. It's probably just that if there would have been such a thing as a global variable, then it would have been a good habit to deallocate once it wasn't needed anymore.|||Thanks for replying.
But how function will provide the functionality of Global Variables. A bit confusing.
Regards,
Shabber.|||You call the function, which will return the value you're after.|||Thanks for replying.
But how function will provide the functionality of Global Variables. A bit confusing.
Regards,
Shabber.
CREATE TABLE MyParameter (
SiteID int IDENTITY (1,1) NOT NULL,
SiteName varchar(255) NOT NULL
)
CREATE FUNCTION udfMyFunction
(@.p1 int)
RETURNS varchar(255)
AS
BEGIN
DECLARE @.sTemp varchar(255)
SELECT @.sTemp = SiteName FROM MyParameter WHERE SiteID = @.p1
RETURN @.sTemp
END
Example Data and Usage:
insert into MyParameter(SiteName) Values ('Foo')
insert into MyParameter(SiteName) Values ('Bar')
SELECT dbo.udfMyFunction (2)
I don't know that this example is all that useful, but maybe it will give you some ideas.
Regards,
hmscott
Sunday, February 19, 2012
Giving Table name as a Variable
Hi guys,
I want to insert record to the table, the table name is a variable. I was tried like this,
declare @.Table_Name as varchar(255)
declare @.MM as varchar(2)
declare @.YY as varchar(4)
set @.MM = month(getdate())
set @.YY = year(getdate())
set @.Table_Name = 'tb_XXXX_' + @.MM + @.YY
INSERT INTO @.Table_Name (column1,....) VALUES (@.Column1,...)
If I print the @.Table_Name variable it shows the corret Table name. But while executing the Procedure it shows a error message
'Must declare the sclar variable @.Table_Name'
How do I implement this? Please anyone had a experience like this tell me a way. Thanks in advance.
Arun.
You have to use the dynamic SQL here. Here the sample to use the dynamic SQL – using sp_executesql or exec
Code Snippet
Create Table #TestDynSQL
(
Id int,
Name varchar(100),
DOB Datetime
);
Declare @.P_Id int, @.P_Name varchar(100), @.P_dob datetime
Declare @.SQL as Nvarchar(4000)
Declare @.ParamDecl as Nvarchar(4000)
Set @.SQL = N'Insert Into #TestDynSQL Values(@.id, @.name, @.dob)'
Set @.ParamDecl = N'@.id int, @.name varchar(100), @.DOB datetime'
Set @.P_id=1
Set @.P_Name='Mani'
Set @.P_Dob='1979-07-26'
--Highly recommanded to use the sp_executesql
Exec sp_executesql @.SQL, @.ParamDecl, @.P_id, @.P_name, @.P_dob
--or
--Beware of SQL injection while using the EXEC(STATEMENT)
Set @.SQL = 'Insert Into #TestDynSQL values(' + cast(@.P_Id as varchar) + ',''' + @.P_Name + ''',''' + Cast(@.P_Dob as varchar) + ''')'
Exec (@.SQL)
Select * from #TestDynSQL
|||
Mani gave you a good solution using dynamic SQL.
However, there are some issues about using dynamic SQL that you should be aware. There may be significant performance penalties and there may also be security issues. The following articles are good resources to learn and explore the use (and mis-use) of dynamic SQL.
Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx