Is there a way to write a SP for all database.
e.g. I see that I can use sp_databases SP in all databases.
How do I write my own SP which will be available in all databases?
The SP that I have is in a particular database, I cannot use it in other databases unless I manually write it in that too. Can I avoid this?
Thanks
Hi All,I tried to get a global variable in my task scritp by using "Dts.Variables("myVar").Value", every time I've got an errorThe element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there. I've seen some examples online to get global varaibles in task script and all of them display the same code Any idea Franck
View 1 Replies View RelatedI would like to create a global DB connection. I am going to be using this string throughout my program and want to be able to call the string that references my connection.
Thanks in advance.
Hello,
In the properties for a DTS package, I have a string variable. I need to execute an update statement based on this variable. I was going to use Execute SQL, but I didn't know now to get a value from the variables section.
I store the value there because I will be using dtsrun or the DTS library to execute the package, which I can change this value upon execution. How do I execute an update based on the value?
Brian
How to create my own global variable and set its value. In another word, i want to set the value of a variable in one sp and want another sp to see its value.
thanks
Michael
Have anyone encountered a problem with DTS Global variables. What I am trying to do is keep a counter in a global variable. It works fine when I run the job manually but if I schedule the job it doesn't increment the counter. Any ideas?
Thanks in advance!
Nancy
Is there a way to declare a global variable that can be used in seperate stored procedures?
I have a statistics table that tracks the inserts and updates for several stored procedures.
I need to have the procedure_A run and insert a count of inserted and updated rows.
Procedure_B needs to identify the row inserted by procedure_A and update the columns with a count of how many it inserted and updated.
Procedure_C needs to do the same as procedure_b.
I want to pass a global variable with the id of the row inserted by procedure_A to the rest of the procedures so that they will know which row to update. How do I do this? Can I?
Thanks.
cani define a global time in sql server2000, which all tables in DIFFERENT databases can use. I need this in order to INNER JOIN all tables in SAME/DIFFERENT DBs on basis of this datetime field.
View 7 Replies View RelatedHi all,
I have a user using this tool, which creates a temporrary storeproc in tempdb.
when I don't give him permission on temp_db, it errors out and says you should create a system variable to point to a different database to create this sp.
Can anyone kindly explain to me how could this be done?
I appreciate any comment on this thread.
Thanks in advance.
Jay
Dear All!
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.
Hi,
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.
If following code, it is ok if I execute part1 and q1 together.
but if I try to execute Q2, I got error
Server: Msg 137, Level 15, State 2, Line 4
Must declare the variable '@str'.
I guess I have to execute part1 and Q2 at same time.
Is there a way to avoid that. I mean after I execute part1 @str will be kept in memory, and I can execut q2 without a problem? (like in SAS)
Thank
/* Part1*/
/* how to make @str global*/
declare @str nvarchar(20);
set @str='%subway%';
/*Q1*/
select *, case regionname when 'telesales' then 't' else 'o' end as rn
from dbo.RPT_ContractDetails
where businessname like @str
/* Q2*/
select contracttypename,regionname, count(*)as cou, sum(fundingamount)as Dollar
from dbo.RPT_ContractDetails
where businessname like @str
group by contracttypename,regionname
Using SQL2000, I have a DTS that takes data from MySQL to sqlserver, thecatch is I want to specify a specific range of dates.How to use a global variable? At the moment I manually changes the dates andjobs run on a daily basis.sample sql statement from Mysql connection:select *from Table1where date between '1/1/2004' and '6/30/2004'CHANGE TO:select *from Table1where date between @fromdate and @todateTIABob
View 1 Replies View RelatedHi all, I need to update some data in a table, based on some criteria.In this case we are talking about the stamping of a price against a job.The update table holds the jobs, and the update_details table holds theactivities performed on each job and the cost for each activity. If ipull back this information using the following codeselect t1.reference,t1.update_id, t2.*from update t1, update_details t2where left(t1.reference,2) in ('EA','ND','SD','ST')and t1.update_id = t2.update_idI get something likeEA 1883 Act1 4.20EA 1883 Act2 3.00EA 1883 Act3 7.50EA 2444 Act1 4.20SD 5433 Act1 5.60I need to update the cost for everything pulled back using the abovesql, to a price determined in another table (activities)the activities table would look something likeActivity_Code Cost_London Cost_RocAct1 5.60 4.20Act2 4.00 3.00Act3 6.20 5.60in a nutshell i need to update the cost in the update details fromCost_roc to Cost_london for all activities for all jobs in the updatetable that have a referance starting with specific letters. The newprices need to be obtained from the activities table.Would be very gratefull for any help on this matterRegards,Ian Selby*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI have several stored procedures and UDFs that have code that convert between GMT timezone and other U.S. timezones.
for example,
to convert from GMT to CT I use this:
@dtmCentral = dateadd(hour, -5,@GMTdtm)
However, come Nov 4th after 2AM, when DST ends, I need to change this to
@dtmCentral = dateadd(hour, -6,@GMTdtm)
Assuming that I can store this -5 or -6 value in a global variable, I want to have a stored procedure that updates it when DST starts and ends.
Is there a better way of doing this other than the option of going through the entire calculations of figuring out whether it's DST or not each time ?
Thanks.
hey there
Reporting services - page footer
I have got this off the ssw.com.au
Execution Time
="Execution Time: " +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds",
(
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") +
IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", ""))
)
and it is supposed to show like this
Execution time: 1 minute, 10 seconds
but all I get is
Execution Time: 0 Seconds on all reports
Can someone tell me what is wrong please?
jewel
Hello guys! Is it possible to declare global sql commands and call it in a rowcommand_function?
Here's what I did...
Dim p_s_syounin2 As New SqlCommand
Dim cnn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)
Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
If (Session("syozokubu_id") = 20) And (Session("syozokuka_id") = 21) And ((Session("kaikyuu_id") = 23)) Then
p_s_syounin2.CommandText= ("UPDATE TE_shounin_zangyou SET p_s_syounin2=syain_hnm FROM TR_syainID WHERE syozokubu_id=20 AND syozokuka_id=21 AND kaikyuu_id=23")
'''' connection string is not placed here acc. to my research
End If
End Sub
Protected Sub my_gridview_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles my_gridview.RowCommand
If e.CommandName = "Approve" Then
cnn.Open()
p_s_syounin2.ExecuteNonQuery()
cnn.Close()
End If
End Sub
I get an error message that says " ExecuteNonQuery: Connection property has not been initialized. "
Please help me.
Thanks guys.
Audrey
Hi,
Can we create our own system global variable using @@ in Sql server 2000?
If yes, can you please post in the sql code with it?
Alos, is the system global variables are kwywords in Sql or they can be updated?
Thanks in Advance!!!
Shobana
Is there a way in SQL Server to setup Global Server Variables or Constants
We are working with an Off the Shelf Constituent Management application based on SQL Server. We can only read from the App's DB. So we setup another DB to run SP to access the data in the main DB. One problem we have is that there codes that the app DB uses that we need to reference as criteria in our SP. Example: Code for a phone type of email is 731. So if we want to pull email addresses we need to Select where PhoneType = 731. We found out that each time the main db is rebuilt those codes change. That means finding everytime we used that code and changing it.
It would be great to be able to set a global variable and use it anytime that code is needed.
Any ideas.
A sample connection string is something like this....
"Server=sql.company.com;Database=myDatabase;Trusted_Connection=True;"
My question is, since the actual .com must go into the string, if you change host companies,
you will have to go through all pages and also change the connection string.
I know there must be a way to declare the string once in like the global.asax and then
refer each page to that string.
I've been hunting around for a sample on how to do this.
Anyone know a good link to refer to?
Thanks,
Zath
How can I set a global var in another DTS package from inside the one I'm excuting. An example would be that I have in package #1 an ID that gets assigned, and it executes package #2 which needs the ID from the previous package.
Thanks,
Todd
A short explanation: I have 4 locations that will import from text files.
These text files will need to be 'stamped' with a location_id. I'd like to pass the location_id to the dts package via a VB program. The reason for the VB program is I'd like to keep users from the Enterprise Manager. Is this possible to accomplish? Thanks for your help.
I am executing:
--------------------------------
DECLARE @x AS int
SET @lcQuery = 'UPDATE ... WHERE .... ' + ' SET @x = @@ROWCOUNT'
EXEC (@lcQuery)
PRINT @x
--------------------------------
@x does NOT return a value, because it is "local" to the lcQuery execution. As a matter of fact, to execute it, I have to write:
SET @lcQuery = 'DECLARE @x AS int ' +
'UPDATE ... WHERE .... ' +
'SET @x = @@ROWCOUNT'
EXEC (@lcQuery)
How can I pass the variable @x to the progam from within EXEC (@lcQuery)?
How does EXEC (@lcQuery) execute? In a different space?
Thank you.
How to set and reference Global Variable in a SQL Task (MS SQL 2000)
:)
Hi:
A regular permanent table is not an option:
Need to exec a procA which at the end also exec procB.
procA will insert to table A and also generate a intermidate result set to a ##A global table, it works fine at this point. However, when it exec the procB at the end of exec procA, the ##A global table is empty when entering procB.
The key is I want to pass a records set to the procB without using a tableTempB. Does a ## global table should be still alive until procB execution is done?
I also tried use table variable, but it does not look like to accept @tableA
as part of the procB's parameters.
Also tried function, but it could not support a #tempTable within it which will do a dynamic query insertion.
thanks
David
Hi everyone:
I am creating an sp, in which I check for the existence of a global temp table (using the exists)
statement. If the Exists returns a false, I move on to processing without the temp table. If it
returns a true, I utilize the temp table to do some inserts. I create the temp table when my
application first starts up. The problem that I am facing is that the check for the temp table`s
existence seems to be failing. Is there any other way to check for the existence of a global
temp table??
Any info really appreciated
Thanks
Nisha
As a newcomer to SQL2000, I need to create a DTS Global Variable that will import a file daily automatically. I've got the DTS working great, but I have to manually execute it every day. The log file name Accounting 02-22-2002.csv changes by the date every day. So the next record set would be Accounting 02-23-2002.csv and so on. Any ideas on how to create this? I think some VBScript is necessary from what I understand. I'm looking at the function DATEADD(d, 1, date).
View 1 Replies View RelatedHi all,
Where can I find information/write up on importing data from Oracle, Lotus Notes, Lotus
Approach / MS Access / Lotus Spreadsheets and text files in to SQL Server 6.5? I've
scanned through books on-line but it doesn't contain any useful information about this.
Is it so that if I want to import data from any of the above mentioned databases then I first
will have to convert data into text(ascii) fromat from that database and then import from
text files??? Sounds weird, isn't it?
Cheers
Nimesh
========
Hey all,
I've recently been attempting a transform data task with a custom query for the source. Using the query, i've attempted to use global params, but it only ever seems to work if there is only one item in the global var. If I return an entire resultset, I get a "EXCEPTION_ACCESS_VIOLATION" instead. I'm trying to use it like "SELECT * FROM whatever WHERE ID IN(?)"
I've pondered this problem for quite some time now and I am wondering if there is a workaround for it. I know it would take much too long to do the same thing in activeX with a transform, so I would rather do it this way if I could.
Thanks in advance,
-Kilka
Can I define a Global variable and use it as a Connection to SQL Server .
What I want to do is Create a DTS which pumps out files to an app server. However this might be done in different environments and I want to keep the SQL Connection Dynamic and change it in global parameter value.
Please help ,,,
Thanks
Hi!
I want to know the scope of a Global Variable in case of multi users.
Means i have declared a global variable in a function. And a new value is assigned to this global variable into this function, each time it is called.
So if, 3 users call this function at same time, then will the get different gloabl variables or same?
Regards,
Shabber.
We're using SQL Server 2000. I have a table with a couple fields I'd like to automatically updatre with a atrigger. One of the fields needs to log the current time the record has been modified. The other field needs to log the current user ID.Storing the current time is easy with UPDATE and GETDATE(), but obtaining the current user from my ASP.NET code isnt as pretty as I would have to pass in the user ID from my backend code into the server, perhaps as a stored procedure variable that will manually update the table with the new ID. I was hoping to perhaps set some global variable of the user ID for the current connection to the dbase so that once the trigger is made, it feeds off this connection-level global variable to plug in the needed User ID for the record.So for instance:SET @@MyCurrentUserID = 4; UPDATE MyTable SET myStuff='games';The MyTable trigger will be initiated and the LastModified field of MyTable will be auto update with the current datetime, but also the LastModifiedUser field of MyTable will be update with MyCurrentUserID.Is this possible with SQL Server 2000 for Windows 2K3?Tx
View 7 Replies View RelatedIs there any way to set a SQL instance to a default lock timeout value? From my understanding, this can be set per session but not globally. I think that any thread waiting longer than say 5 minutes for a lock should fail and would like this make the default in some of our servers.
View 3 Replies View Related