Create My Own @@Variables
Apr 16, 2008
Does anyone know if there is a way to create a server-wide variable... something like:
declare @@MyVar BIGINT
set @@MyVar = 22456
then refer to this var in any scope throughout the server?
ADVERTISEMENT
Using Variables To Create Views
May 8, 2007I am stuck on creating views using the variables. I keep on getting the 'CREATE VIEW' must be the first statement in a query batch error. I understand that views need to be the first statement, but I have a lot of views that need to reference specific variables - is there any way to do this?
The code I am using is as follows:
DECLARE @view varchar(MAX)
DECLARE @database varchar(30)
SET @database = 'KateTEST3'
--Insert views
SET @view = 'USE ['+@database+']
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON;
CREATE view [dbo].[userssupplier]
as
SELECT *
FROM dbo.Users
WHERE (User_Type = 4.0) OR
(User_Type = 5.2)'
EXEC(@view)
Thanks
Create A View Based On Variables?
Jul 28, 2004Hi All,
I would like to create a view based on a variable (a date).
CREATE VIEW testView (@myDate)
AS
select * from testTable
WHERE testVar = @myDate
--
This is far from what the final view would look like, but if anyone knows if something similiar to this can be done, I would greatly appreciate it.
Thanks.
Create Database Using Local Variables
Feb 4, 1999I have an installation script and want to create a database based on some data in a table (config). Testing the script though I get a message:
Msg 170, Level 15, State 1
Line 12: Incorrect syntax near '@DBData'.
The relevant sql is:
declare @DBName varchar (40)
declare @DBData varchar (40)
declare @DBLog varchar (40)
declare @DBSize int
select @DBName = obj_txt from config where obj_nm='DBName'
select @DBData = @DBName + '_Data'
select @DBLog = @DBName + '_Log'
select @DBSize = obj_int from config where obj_nm='DBSize'
raiserror('Creating Database %s ....',0,1,@DBName) with nowait
Create Database @DBName
on @DBData = @DBSize
Log on @DBLog = @DBSize
I don't think there's anything wrong, apart from the fact I am using the local variables. Is this allowed on a Create Database statement? I haven't found anything in Technet that may help.
Here's hoping....
Thanks
Dan
Concatenating Variables To Create A Column Name
May 5, 2008I am trying to build a Windows application using: Windows XP Pro ; VS Pro 2005, C# and SQL2005.
I have 2 database table3 as follows:
eg
1) myGameRecency which contains columns : GameId (identity specification column/primary key/not null), Date (not null), [1], [2], [3], [4] , WeeksSinceDr0, WeeksSinceDr1, WeeksSinceDr2
2) myGameFrequency which contains columns : AllBallsFrequency , WeeksSinceDrawnAllBalls
Using the myGameRecencyAllBalls table ---
I wish to insert a 0 into a column corresponding to a ball that has been drawn, eg if a 4 has been drawn, then a 0 will be inserted into that column. If a ball has not be drawn, then the value in that column will be a running total, signifying the number of draws since it was last drawn ( ie since a 0 was inserted into that column).
I place a 1 in the column corresponding to the number of weeks since a number has been drawn. The name of the column is therefore the concatenation of the string literal 'WeeksSinceDrawn' and the value held by the variable, @lastRowCount obtained by the lastrow_CURSOR.
I have declared a variable @colName to hold the concatenation / Set @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) and then tried to use it as follows: SET [@colName] = 1
however, I receive an error message advising me that I have an invalid column name. Is there any means of setting a column name by concatenating two variables or , a string literal and a variable ?
Thank you
lpbcorp
sqlCmd.CommandText = "DECLARE @colName nvarchar(max) " +
"DECLARE @lastRowCount int " +
"DECLARE lastrow_cursor CURSOR SCROLL FOR " +
"(SELECT [" + i.ToString() + "] FROM " + DBGameName.ToString() + "RecencyAllBalls) " +
"OPEN lastrow_cursor " +
"FETCH LAST FROM lastrow_cursor INTO @lastRowCount " +
"SET @colName = 'WeeksSinceDr' + CONVERT(nvarchar(max), @lastRowCount) " +
"IF @lastRowCount <= 175 " +
"BEGIN UPDATE " + DBGameName.ToString() + "RecencyAllBalls SET [@colName] = 1 WHERE Date = '" + Date + "' " +
"END " +
"ELSE " +
"UPDATE " + DBGameName.ToString() + "RecencyAllBalls SET WeeksSinceDrOver175 = 1 WHERE Date = '" + Date + "' " +
"CLOSE lastrow_cursor " +
"DEALLOCATE lastrow_cursor";
sqlCmd.ExecuteScalar();
Create Variables With Same Format In One Single Step?
Jul 23, 2005I would need to create multiple variables with the same format forfuture update, what I did is listing them separately, is there an easyway to combine them in one step? please see query below. Thanks a lot!--Current Query--SELECT cast(0.0 as money) as balance_1,cast(0.0 as money) as balance_2,cast(0.0 as money) as balance_3,cast(0.0 as money) as balance_4,cast(0.0 as money) as balance_5,cast(0.0 as money) as balance_6,cast(0.0 as money) as balance_7,cast(0.0 as money) as balance_8,account_no,XXXINTO table1FROM accountCan I do something like this? This one didn't work.SELECT balance_1 to balance_8 (cast 0.0 as money),account_no,XXXINTO table1FROM account
View 5 Replies View RelatedIs It Possible To Dynamically Create Multiple Variables In A Package?
May 21, 2008I'd like to be able to call different packages from a control flow. These packages will have different requirements for parameters therefore I'd like to create them dynamically.
Is this possible? Can I do it using a script task?
Thanks in advance.
Ben
Execute DTS 2000 Package Task Editor (Inner Variables Vs Outer Variables)
Sep 4, 2006 Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
How To Design A Package With Variables So That I Can Run It By Dos Command Assigning Values To Variables?
Jan 24, 2006Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
Problems On Create Proc Includes Granting Create Table Or View Perissinin SP
Aug 4, 2004Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
Boolean: {[If [table With This Name] Already Exists In [this Sql Database] Then [ Don't Create Another One] Else [create It And Populate It With These Values]}
May 20, 2008the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
Create Variable To Store Fetched Name To Use Within BEGIN / END Statements To Create A Login
Mar 3, 2014I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
Dynamic Create Table, Create Index Based Upon A Given Database
Jul 20, 2005Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies View RelatedCan CREATE DATABASE Or CREATE TABLE Be Wrapped In Transactions?
Jul 20, 2005I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
View 2 Replies View RelatedCreate Script To Create/Refresh Identical Database
Mar 26, 2008
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.
I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.
Any help would be appreciated. Thanks!
Grant CREATE VIEW, CREATE PROCEDURE ...
Apr 12, 2006Hi,
I have currently a problem with setting up the permissions for some developers. My configuration looks like this.
DB A is the productive database.
DB B is a kind of "development" database.
Now we have a couple of users call them BOB, DAVID, ...
who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.
Therefor I added them to the db role db_owner for db b.
For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message
'Msg 262, Level 14, State 1, Procedure Test, Line 3
CREATE VIEW permission denied in database 'b'.'
I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.
What's my mistake?
Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.
Regards,
Stefan
Create INDEX Within CREATE TABLE DDL
Jan 27, 2006Hi Minor and inconsequential but sometimes you just gotta know: Is it possible to define a non-primary key index within a Create Table statement? I can create a constraint and a PK. I can create the table and then add the index. I just wondered if you can do it in one statement. e.g. I have: CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE NONCLUSTERED ON [DEFAULT])which creates a table with a PK and unique constraint. I would like (pseudo SQL):CREATE TABLE MyT (MyT_ID INT Identity(1, 1) CONSTRAINT MyT_PK PRIMARY KEY Clustered, MyT_Desc Char(40) NOT NULL CONSTRAINT MyT_idx1 UNIQUE INDEX NONCLUSTERED ON [DEFAULT]) No big deal - just curious :D Once I know I can stop scouring BOL for clues. Tks in advance
View 2 Replies View Related@@ Variables
Jul 13, 2007What is @@variables means in sql server?
View 3 Replies View RelatedUsing LIKE With Variables
Jan 21, 2004Is there a way to use the LIKE keyword with variables like below?
DECLARE @Name CHAR(10)
SET @Name = 'MyName'
SELECT * FROM table
WHERE my_name LIKE @Name
This won't work, but you get the idea of what I want. Any thoughts?
Thanks,
DTS Variables..?
Jun 4, 2004Is there a way to pass variables off to DTS by ADO.NET?
Such as a FileName to export to and/or a parameter for the export query?
SQL HELP......IF's With Variables
Jun 8, 2005Anyone know how to write the portion in red in a stored procedure?LEFT OUTER JOIN TITLE AS T ON (POS.TITLE_ID = T.TITLE_ID)JOIN DISTRICT_LOCATIONS AS DL ON (POS.DISTRICT_LOCATION_ID = DL.DISTRICT_LOCATION_ID)WHERE POS.PRIMARY_IND = 1 IF @DISTRICT_LOCATION_ID != 'All' BEGIN and DL.DISTRICT_LOCATION_ID = @DISTRICT_LOCATION_ID ENDIF @ATTENDANCE_STATUS_ID!= 'All' BEGIN and AST.ATTENDANCE_STATUS_ID= @ATTENDANCE_STATUS_ID ENDUNION ALLSELECT DISTINCT 5 AS TAG ,3 AS PARENT ,convert(varchar,getdate(),101) as [ACTIVITY_REPORT!1!REPORT_DATE] ,AX.ACTIVITY_CLASS_ID AS [ACTIVITY!2!ACTIVITY_CLASS_ID] ,NULL AS [ACTIVITY!2!ACTIVITY_NAME]
View 2 Replies View RelatedT-SQL Variables
Feb 16, 2000Hi,
I am testing SQL Server 7.0. In Stored Proc I try to select a database which name is stored in the variable @databasename.
I get the error that it can't open a database @databasename.
Can I declare the database object in T-SQL?
Thanks
Using Variables In T-SQL
Nov 2, 2000I have this script:
.....
USE master
go
/* Get Name of Server & declare variables */
declare @sname varchar(30)
declare @db1 varchar(30)
declare @db2 varchar(30)
declare @db3 varchar(30)
declare @dbf1 varchar(30)
declare @dbf2 varchar(30)
declare @dbf3 varchar(30)
select @sname = rtrim(substring(srvname,5,30)) from sysservers
print 'The name of this server is: ' + @sname
Set @db1 = @sname + 'database1'
Set @db2 = @sname + 'database2'
Set @db3 = @sname + 'database3'
Set @dbf1 = @db1 + 'RL_log'
Set @dbf2 = @db2 + 'RL_log'
Set @dbf3 = @db3 + 'RL_log'
print @db1
print @dbf1
go
ALTER DATABASE @db1 MODIFY FILE (NAME = @dbf1, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db2 MODIFY FILE (NAME = @dbf2, MAXSIZE = UNLIMITED)
go
ALTER DATABASE @db3 MODIFY FILE (NAME = @dbf3, MAXSIZE = UNLIMITED)
go
.....
When I run it, I get the following errors:
.....
The name of this server is: KANSASCITY
KANSASCITYdatabase1
KANSASCITYdatabase1RL_log
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@db1'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db2'.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '@db3'.
.....
Don't let the line numbers fool you. They refer to the number of lines since the last 'go' in the script. As you can see, the @db1 and @dbf1 variables are evaluating correctly.
WHAT I AM TRYING TO ACCOMPLISH:
I am attempting to change the setting of the Transaction Log to grow to fill up the entire disk. I do not wish to limit the space at this time. I have approximately 200 servers to manage and this script would be most useful in managing them, if it only worked.
Should I be using a different function to change the settings on the Transaction Log? Something other than ALTER DATABASE?
Thank you (in advance)
Using Variables In DDL
Mar 28, 2003Hi
I think this is an easy question.
Is it possible to use a variable in a create database statement
i.e.
declare @db_name varchar(20)
select @db_name='new_db'
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = @db_name)
DROP DATABASE @db_name
CREATE DATABASE [@db_name] ON (NAME = @db_name, FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataew_DB_Data.MDF' , SIZE = 300, FILEGROWTH = 10%) LOG ON (NAME = N'new_DB_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdataImport_Utility_DB_Log.LDF' , SIZE = 30, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
Also, if the above is possible, how can i pass a database name to the script if I am calling it from a batch file (using osql).
Thanks,
Jim
Using Variables In T-SQL
Jul 26, 2000I want to change database in my SQL-script like this:
DECLARE @DB_NAME varchar(30)
SELECT * FROM @DB_NAME.dbo.TableName,
but it is syntactically incorrect.
I could use
USE (@DB_NAME)
but I write Stored Procedure, it is not allowed using
USE statement in any Stored Procedures.
Variables
Oct 31, 2005Hey folks,
i'm trying to make a variable inside of a loop do this.
@a = 1
@b = Variable + @a
and then inside of the loop @a incriments by 1, so i'd get
variable1
variable2
...etc
but I can't get the variable to work right...
Any help would be great!
Caden
Variables In MS SQL
Apr 14, 2006I have a MS SQL server DB which stores tables and queries and a MS Access DB which acts as an interface and stores Forms.
I am a MS Access Developre and this is my first MS Sql Server DB. I would like to know how can i pass a variable which is an object in a Form (In MS Access DB) to a query in MS SQL?
In MS Access( i mean mdb file which all tables, queries and forms are in one mdb file)it is very easy, for example if you would like to pass idfld from Form of frminfo to a query you can say: Where idname like " & Forms!frminfo!idfld , but it does not work on MS SQL.
Would you please tell me how can i use a variable in MS SQL query?
Thank
Ebi
DTS And Variables
Jan 29, 2007I hope this is the place for questions about DTS.
I'm working on a DTS package that runs against development databases. At some point I'll move it to qual for testing, and I'll have to change names of servers and credentials for some of the connections. Can I do that with global variables? BOL aren't very helpful about this.
To be more specific. I'll have one "master" package that will be kicking off other packages. I've found in help that I can use dtsrun to pass variables - so I'm assuming that DBAs moving packages from one environment to another will adjust parameters passed by dtsrun to the "master" package. But how does the "worker" package reference global variable from the "master" package? And how do I reference global variable inside the "worker" package so that it's value is used for any of the properties I see when clicking on "disconnected properties" (like server name or user name)?
Is there some sort of tutorial out there that explains use of global variables?
Thanks in advance for any and all leads
Variables In SQL?
Jun 5, 2007Basically I am creating a wizard(using forms) within a database to import client files and reformat them into the format that we need.
I have a form that list all 26 column headings that I need and tied to each column headings is a combo box that that lists the column headings from the imported client file.
I need to create a query that either appends it to an already formatted file or creates a new table with the column headings.
The problem is the field headings from the imported file change.
This is the sql that I used that doesn't work:
INSERT INTO Formatted_Data ( Field X )
SELECT Imported_Data.Forms![Import Data-3]![Combo33]
FROM Imported_Data;
Because Imported_Data.Forms![Import Data-3]![Combo33] is not recognized. I think if I could create a variable to replace the Forms! part then it would work but I do not know how to do this,
Thanks in advance to anyon that can help
Variables
Nov 14, 2007Hello,
I have a recordset that only has 1 record. I want to use this record to initialize the package variables. How do I do this? Or where do I do this?
Using Variables
Mar 14, 2007Hi
I have a simple task flow which takes data from an external table and puts it into a SQL server table. In the SQL server table I have a column which flags the data source. I have set up a variable in the variable window to hold a constant which was the appropriate value for the column. I cannot see how to map the variable I have created to the column in my output table.
Not sure if variables do what I think they do, can anyone give me a simple overview, and point me in the right direction to add my constant into the output table.
Thanks in advance
ADG
Variables
Apr 25, 2007Hi everyone,
Is the only way to get a SSIS variable to change values via a configuration file or table? I thought that at one point in developing some SSIS packages that if I changed the value of a variable in a script that value was then retained.
Thanks
Using Variables
Sep 7, 2007
Is there a way to manipulate the value of a variable with out using a skripttask?
I just want to var1 = var2.Substring(5)