Hi
I need to run a stored procedure on each database in my SQL server. I want to have a loop to go through each db.
Is there a way I can run 'Use @dbname', I tried Execute and sp_executesql but it didn't work.
I want to execute the SP withing each db.
Thanks
I am trying to set a vaiable from a select statement
DECLARE @VALUE_KEEP NVARCHAR(120),
@COLUMN_NAME NVARCHAR(120)
SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')
SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)
PRINT @VALUE_KEEP
PRINT @COLUMN_NAME
RESULTS
-------------------------------------------------------------------------------------------
FirstName <-----------@VALUE_KEEP
FirstName <-----------@COLUMN_NAME
SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName
SELECT FirstName from Contacts returns: Brent
How do I make this select statement work using the @COLUMN_NAME variable?
Any help greatly appreciated!
Need to have 'Use Master' statement in one stored procedure from a Admin database to execute some task only in Master database could be performed.
error: Use database statement is not allowed in a procedure or trigger.
Is there a way to bypass this error?
I know my last result will be to put this stored procedure in Master, which I really don't want to...
thanks for help
-D
I am trying to get file space usage information for all of the databases in my server and store it in a table to collect a history of spce usage. I know that I can get this on a per database basis if I use DBCC(SHOWFILESTATS). So, I would issue a USE statement and then a DBCC statement inserting the rows into a temp table for later processing. Since databases can come and go, I would like to loop through the records in sysdatabases and for each one, issue the USE and then the DBCC via an sp_executesql.
This is all working fine except, the sp_executesql of the USE statement seems to be ignored.
Is there some way to get this statement to take effect?
Is there a better way to get this information - ie is this stored in a table or view that I can't seem to find?
Thanks in advance for your help.
Bob
Is there a way to 'extend' the use of 'Use DatabaseName' sql statement?
I want to build an dynamic sql string such as
'Use '+ @serverName+ '.Master', but it won't accept serverName. just for database within the same server...
thanks
David
Has anyone managed to get this to work?
dbcc rebuildlog (@dbname, 1, 0)
I have databse called RM-A1 and a log of RM-A1.log
When i run this statement it errors.
select ceiling((size * 8192.0)/(1024.0 * 1024.0)),
case when status & 0x100000 = 0 then
convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0)))
else convert (varchar, growth)
end + char(10)+char(13)
from RM-A1_log.dbo.sysfiles where fileid = '1'
--This works
select * from dbo.sysfiles
Im using a dynamic statement
set @cmd = N'select @ceil = ceiling((size * 8192.0)/(1024.0 * 1024.0)), @CNT = case when status & 0x100000 = 0 then
''MB '' + convert(varchar,ceiling((growth * 8192)/(1024.0*1024.0))) '
+ 'else ''PER '' + convert (varchar, growth) '
+ 'end' + char(10)+char(13)
+ 'from ' + @l_db_name + '.dbo.sysfiles where fileid = ''1'''
exec sp_executesql @cmd, @retType, @cnt OUTPUT, @ceil OUTPUT
I cannot determine why log name of '-' are erroring all other databases are working.
i have curputed mdf and ldf file for ms sql 2005
i have tried to put the db in standby mode and when i try to run dbcc checkdb or any other command i get this message
Database 'dbname' cannot be opened because its version (611) is later than the current server version (539).
but without standby mode i get this error
TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Could not find row in sysindexes for database ID 40, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
I am in a bind here..
I am getting the message (after do any sort of query) Database context changed to <dbname> I am using PHP as the p language.
Does anyone have any clue on why it is doing that..
Recently my host upgraded there PHP to version 4.3.10 and before the upgrade the queries were working fine.
Im on a windows server that runs PHP and im using a MSSQL database.
I've checked the bug reports on PHP and didn't come up with anything as well as googling the error..
IF you have any insite on this problem please let me know.. It may be a stupid error that I am overlooking on my part.
Thanks in advance
Greetings everyone,
I am trying to use a c# string with an SQL statement in a data adapter (.NET 03)
The code works fine and I have a variable called : string test = ..... that takes the needed values. I just need to implement this string in the sql statement. I tried adding this to my query but I only got an empty row:
WHERE (login = '" & test & "')
WHERE (login = '" + test + "')
any ideas?
PS: If I change to something like WHERE (login = 'abcdef') I get a result meaning there's something wrong with the way I am putting the variable in the sql query.
Again, I am not putting the string in a normal query in my .cs code. this is happening by right clicking the data adapter and configuring the sql statement in the designer window
THANKS!
Hi,
I've created an sql statement:
select *
from fin_installment
where key_construction = (select ser_construction from fin_construction where key_contract = ' " & variable & " ') order by int_serial
which is in an Dataset's TableAdapter.
This variable receives its value during the form init and it is an integer.
When I start the page the folowing error message is displayed:
" An error has occurred during report processing.
Exception has been thrown by the target of an invocation.
Conversion failed when converting the varchar value ' " & azonosito & " ' to data type int. "
So my question is that how can I use variables in sql statement in dataset?
Hi, I am trying to use a variable inside a LIKE statement, but it is not working as expected. It will not give a error, but it shows no results while it does show results if I replace the variable with the normal string within the LIKE statement. Here is my code:
Code:
-- this example returns results
SELECT whatever
FROM mytable
WHERE whatever LIKE 'blah%';
Code:
-- this example returns no results
DECLARE @test VARCHAR;
SET @test='blah%';
SELECT whatever
FROM mytable
WHERE whatever LIKE @test;
Any ideas why the version using the variable would not work?
Patrick
I'm having some trouble modifing a script to save me tons of work. The script if from Microsoft, and it is used as step 3 in a 6 step process to move MS Great Plains users from one server to another. Anyway, the script runs on only 1 company database at a time, and for most Great Plains environments there would only be 1 or 2 company DBs. But I am administering in an ASP environment and we have over 30 company DBs to move. So, I though I would adapt thier script to iterate over each company DB to do the work (rather than creating 30 separate scripts). So I wrapped their loop with my loop to do the iteration. The problem is that T-SQL will not let me use a variable in a USE statement. I've tried to remove the USE statements, but that added a lot of complexity in the internal loop. What is the best way to do this?
Here is the modified code:
/*
** Drop_Users_Company.sql
**
** This script will remove all users from the DYNGRP in the company database
** specified. It will then drop the DYNGRP and readd the DYNGRP to the company.
** It will then add all users back to the DYNGRP based on the SY60100 table.
** NOTE: You will need to replace %Companydb% with the company database
** name.
*/
/* Instead of replacing %Companydb% (in each USE statement) with the name of the
single company database that this script is supposed to work on, I've added
@cCompany to hold the company DB name through each iteration of the outside
cursor/while loop.
*/
declare @cCompany sysname/* ADDED BY ME FOR THE OUTSIDE LOOP */
declare @cStatement varchar(255)/* Misc exec string */
declare @DynDB varchar(15)/* DB Name exec string */
declare @DYNGRPgid int/* Id of DYNGRP group */
/*
** Loop through all company databases, emptying the DYNGRP group.
*/
SET QUOTED_IDENTIFIER OFF
use DYNAMICS
/* Select all of the Great Plains database names from the DB_Upgrade table, where the DB names are conviently stored */
declare C_cursor CURSOR for select db_name from DYNAMICS..DB_Upgrade where db_name not in ('DYNAMICS')
OPEN C_cursor
FETCH NEXT FROM C_cursor INTO @cCompany
WHILE (@@FETCH_STATUS <> -1)
begin
use @cCompany
select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP')
declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers
where gid = @DYNGRPgid and name <> 'DYNGRP'
set nocount on
OPEN G_cursor
FETCH NEXT FROM G_cursor INTO @cStatement
WHILE (@@FETCH_STATUS <> -1)
begin
EXEC (@cStatement)
FETCH NEXT FROM G_cursor INTO @cStatement
end
DEALLOCATE G_cursor
/*
** Do not delete the group to attempt to preserve the permissions already
** granted to it.
*/
use @cCompany
if exists (select gid from sysusers where name = 'DYNGRP')
begin
exec sp_dropgroup DYNGRP
end
/*
** Recreate the DYNGRP group in all company databases.
*/
use @cCompany
if not exists (select name from sysusers where name = 'DYNGRP')
begin
exec ("sp_addgroup DYNGRP")
end
end
DEALLOCATE C_cursor
______________________________________
Thanks for any help you have.
Declare @MyCode nvarchar(20);
Set @MyCode='ABC'
set @int_rowcount=(SELECT count(hoten) FROM @MyCode)
I run it but still errors !
How can i implement above statement ?
Thank you very much !
Hi,I am doing a really simple test with SQL Server 7.0:Using the Query AnalyzerLogged as saLocated in master database#1 USE Test#2 EXEC('USE Test')#1 => the database context is switched to Test#2 => the database is NOT switched???
View 2 Replies View RelatedHello Everyone,
I wanted to pass a SQL statement thru a variable, and use that variable in my source component.
SELECT CLINIC_SUK, CLINIC_CODE, CLINIC_DESC, CLINIC_ARABIC, Load_DT
FROM DIM_CLINIC
where load_dt > ?
I had created a variable with my SQL statement and mapped that variable in my source component.
Its giving me some error.
Parameter Information cannot be derived from SQL statement. Set parameter information before preparing command.
Please do inform me about the solution for having a parameter in my source SQL Statement.
Hi ,
I am testing a very simple query that use variable for sort direction and sort expression
DECLARE
@SortExp nvarchar(256),
@SortDir nvarchar(10)
Set @SortExp = 'curTime'
Set @SortDir = 'DESC'
Select * from table
where recID < 20
order by @SortExp @SortDir
and i got this error...
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
Is there anyway to do this task.
Thanks
Ddee
Can anyone help me with this issue? I restored a full backup with NORECOVERY because I need to restore the differentials as well but I have not been able to access the db. No matter what I seem to try i receive the following error:
System.Data.SqlClient.SqlError: This differential backup cannot be restored because the database has not been restored to the correct earlier state. (Microsoft.SqlServer.Smo)
Does anyone know what I need to do?
Thank you in advance.
Julio
How can I get
dbname
ip
domain name
Data path
using TSQL.
how can I stop services using TSQL
Emad
Hy, i have this problem in vb.net:
I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code:
Dim variable_name As StringDim variable As Integer
Dim sqlString As String = ("UPDATE table_name SET " variable_name " = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion)
cmdSqlCommand.ExecuteNonQuery()
When I don't use a variable after SET statement, everything work fine. This code works fine:
Dim variable As Integer
Dim sqlString As String = ("UPDATE table_name SET column_name = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion)
cmdSqlCommand.ExecuteNonQuery()
Please, if someone can help me in this...thanks..
could anyone please help me to resolve this issue?
here's my sql query which retrieve last 3 month data
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -3, getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
now instead of passing 3 in this query(datepart(mm,DATEADD(month, -3, getdate())) )
i need to pass a variable to retrieve data based on user requirements.
i tried this way,
dim mno as n
mno=4
t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -'"+mno+"', getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'")
its not working.
can i achieve this using stored procedure? or can i directly pass a variable to sql synatax?
thanks for any help
I'm curious if anyone knows the correct way to do this pseudo-statement correctly? I want to create a stored procedure in which I send it the table name of the table I want to query.declare @tableName varchar(500)set @tableName = 'PortfolioPreferenceOwnership' select * from @tableName
View 4 Replies View RelatedI am having difficulties with some sql syntax with sql server 2000.
I am trying to write code to update a column in which the name of it is unknown. At run time, I am able to set a variable equal to the correct column name but in doing so, treats the value as a String.
Ex.
Declare @varA varchar(12)
select @varA = (select top 1 Value from #temp)
Update TableX
set @varA = y.ColTest
from TableX x, TableY y
where x.Colid = y.Colid
It sets the variable = to the last value from TableX.ColTest
I want the Update statement to update the value for the Variable which
represents the correct column to update.
Any ideas?
Thanks,
Daniel
Hi,
I want to use a variable in a 'use' statement...
but, I cannot figure out the syntax, nor do I know if it is possible...
Here is an example SQL script:
/*-----------------------------------------------------*/
DECLARE @DataBase varchar(60)
--Declare cursor for all DBs except master, MSDB, Model, tempdb
DECLARE curdb CURSOR for select name from master..sysdatabases
where
name not in ('master', 'MSDB', 'Model','tempdb')for read only
--Open and perform initial fetch
open curdb
fetch curdb into @DataBase
--While there are databases to process, process each DB
While @@fetch_status = 0
PRINT @DataBase
use + ' ' + @database --or,
use @database
fetch curdb into @DataBase
end
/*------------------------------*/
Thanks,
Michael
Hi all,
I have been struggling with the below transact sql user defined function. I want to use a transact sql variable in an "in" statement. I don't get any results and I am not sure if I am receiving an error or not.
Code:
DECLARE @myval varchar(50),@username varchar(50)
DECLARE @rolelist varchar(2000)
SET @rolelist = ''
SET @myval = 'user a,user b'
select @myval = ''''+ replace(@myval,',',''',''') + ''''
print @myval
DECLARE User_Cursor CURSOR FOR
select distinct eusername
from euser
where eusername in (@myval)
OPEN User_Cursor
FETCH NEXT FROM User_Cursor INTO @username
SET @myval = @username
SET @rolelist = @username
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rolelist =+ @rolelist + ',' + @username
FETCH NEXT FROM User_Cursor INTO @username
END
CLOSE User_Cursor
DEALLOCATE User_Cursor
print @myval
print 'rolelist' + @rolelist
GO
I am at a loss any suggestions would be greatly appreciated.
Hi All,
What i am trying to do is concatenate variable "@Where" with CURSOR sql statement,inside a procedure . But, it doesn't seem to get the value for
the @Where. (I tried with DEBUGGING option of Query Analyzer also).
=============================================
SET @Where = ''
if IsNull(@Input1,NULL) <> NULL
Set @Where = @Where + " AND studentid='" + @input1 +"'"
if isnull(@Input2,NULL) <> NULL
Set @Where = @Where + " AND teacherid =' " + @Input2 +"'"
DECLARE curs1 CURSOR SCROLL
FOR SELECT
firstname
FROM
school
WHERE
school ='ABC' + @where
=============================================
Please check my SQL Above and Could somebody tell me how can I attach the VARIABLE with CURSOR sql statement ?
Thanks !
Is there anyway to use a variable to define a column in a select statement. I can put the variable in but I'm sure it will be read as a literal instead of the column.
select @column_name from table
Is it possible to set a value to a variable if a case statement is true?
like this:
CASE
WHEN Utable.type = 2 THEN U.Username2 + (@Uname= 2)
WHEN Utable.type = 3 THEN U.Username3 + (@Uname= 3)
Ive tried above and:
"+ (select @Uname = 2)"
"+ (SET @uname = 2)"
"; SET @uname = 2"
...
and several other things but non works.
variable's = INT
"Utable.type" = INT
"U.Username"2/3 = nvarchar(50)
Create table tbl(title nvarchar(40))
Create procedure df_bppr @de nvarchar(30)
As
Begin
Declare @sstr nvarchar(500)
Set @sstr = N'Alter Table tbl Add Constraint df_title Default '+ @de + ' For title'
Exec sp_executesql @sstr, @de
End
Execute df_bppr @de = 'NoTitle'
******************
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'NoTitle'.
Msg 128, Level 15, State 1, Line 1
The name "NoTitle" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
--------------------
I created the procedure without any error. But when i execute the procedure it shows the above error
I want to create a proc which dynamically change the default value for more than one column with same default value.
Vijai
Hello,
I would like to set a variable within my if exists statement, however SQL is throwing and error stating:
Incorrect syntax near '='.
If I remove the if exists, the query runs fine. Is there a reason why this is not working the way I have it and what suggestions can I use to accomplish what I am trying to do, which is store the ID into the permissionID variable
Here is my code block:
Code Snippet
declare @permissionID int;
if exists(select @permissionID = Id from Permission
where [Description] = 'SettlementReport')
Thanks,
Flea#
I've been coding a few years and SSIS makes me feel more stupid than any program I've ever used.
I've read BOL and bought a book. Can't say either one has really helped.
I'm still a complete idiot after one week of working with it. I apologize for asking so many stupid questions.
What I'm trying to do now is parameterize a SQL statement.
I have a variable that's a string. I have a DataFlowComponent as a data source. I find references all over the Internet and this forum to something called an "ExecuteSQLTask" but I sure can't figure out what that is. In my toolbox I have data flow sources for DataReader, Excel, Flat Files, Old DB, Raw file, and XML but no Execute Sql Task.
Anyway SELECT * FROM TABLE WHERE COLUMN='Value' in the SQL Command property is simple enough. Now I want 'Value' to be a variable. You know, like in T/SQL DECLARE @Foo VarChar(25). Creating the variable is easy as pie.
I have found at least 10 different examples of specifying variables on the web, all of which claim to be SSIS examples, Is it User::Variable? @[User::Variable]? @Variable?
I want to read rows from a table, with a a variable value in the WHERE clause, and pass them to the fuzzy lookup task.
Is my approach fundamentally flawed in some way?
Hi Experts, I would like to make a stored procedure in my db: I have sql express 2005.
I get Error 164 when creating this procedure:
CREATE PROCEDURE CrossTable
@Variable1 smallint,
@Variable2 smallint,
@Value smallint
AS
BEGIN
SELECT @Variable1, COUNT(@Variable1) AS 'Haufigkeiten'
FROM SurveyData
WHERE @Variable2 = @Value
GROUP BY @Variable1
END
GO
I would like to generate a frequency chart per userdefined-variable (@variable) with a where restriction.
The GROUP BY @Variable1 seems to be problem: is there some workaround in order to use variables in a GROUP BY clause? or how can I write an sql statement which do the same as this procedure (CrossTable) without using the GROUP BY clause?
Thanks a lot for your replies
Greets from Switzerland
Chris
In the funtcion below I am selecting a value from the page - Request.QueryString("ProjectID") and this is being pulled through correctly - if I debug then ProjectID = Request.QueryString ("ProjectID") does equal the correct value.
However this value is not then working in the following line:
strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"
This statement is not reading any value for ProjectID and so is selecting all from the table.....
How do I write this statement to pick up the variable above?????
Thanks in advance for your help!
Clare
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim ds As New Data.DataSet
Dim da As Data.SqlClient.SqlDataAdapter
Dim strSQL As String
Dim ProjectID As Int32
ProjectID = Request.QueryString("ProjectID")
strSQL = "SELECT [ProjectID] FROM [Projects] WHERE [ProjectID] = [ProjectID]"
Dim connString As String = "Data Source=xxx;Initial Catalog=xxx;User ID=xxx;Password=xxx"
da = New Data.SqlClient.SqlDataAdapter(strSQL, connString)
da.Fill(ds)
ds.Tables(0).Columns.Add("imgFile")
For Each tempRow As Data.DataRow In ds.Tables(0).Rows
tempRow.Item("imgFile") = ("imgGrab2.aspx?id=" & tempRow.Item("ProjectID"))
Next
ImgGrid3.DataSource = ds
ImgGrid3.DataBind()
End Sub