Is it possible to have part of a table name used in a CREATE statement
contained in a variable? Here's what I'd like to do, although
obviously the syntax of this isn't quite right or I wouldn't be here
asking:
The point there is to have a table named tstTestTable
The reason I need to do this is that my ISP will only give me one
database to work with and I'd like to have two copies of the
application I'm developing running at the same time. So I'd like to
run the sql script that creates the tables with TblPrefix set to "dev"
and then run it again with TblPrefix set to "liv"
I have a table with Year , Account and Amount as fields. I want to
SELECT Year, Account, sum(Amount) AS Amt
FROM GLTable
WHERE Year <= varYear
varYear being a variable which is each year from a query
SELECT Distinct Year FROM GLTable
My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.
What I have done is to defined two variables as follows
Name: varYear (this will hold the year)
Scope: Package
Data type: String
Name:vSQL (This will hold a SQL statement using the varYear)
Scope: Package
Data type: String
Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]
I've created a SQL Task as follows
Result set: Full Result Set
Connection Type: OLE DB
SQL Statement: SELECT DISTINCT Year FROM GLTable
Result Name: 0
Variable Name: User::varYear
Next I created a For Each Loop container with the following parameters
Enumerator: Foreach ADO Enumerator
ADO Object source Variable: User::varYear
Enumeration Mode: Rows in First Table
I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows
Data Access Mode: SQL Command from Variable
Variable Name: User::varYear
However this returns a couple of errors "Statement(s) could not be prepared."
and "Incorrect syntax near '='.".
I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.
my stored procedure have one table variable (@t_Replenishment_Rpt).I want to create an Index on this table variable.please advise any of them in this loop... below is my table variable and I need to create 3 indexes on this...
I am passing a variable to a stored procedure using db.ExecuteNonQuery("dbo.CreateTable", @symbol); < C# CODE > the variable shows up fine but the stored procedure does not create the table... I have tried everything... here are two versions of code that do not work...
I want to create a table in a stored proc using a variable name instead of something hard coded. I was hoping to do something like....
CREATE PROCEDURE foo
-- Add the parameters for the stored procedure here
@TableName char = null
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE @TableName (
[HRMONTH] [int] NULL,
[HRYEAR] [int] NULL
) ON [PRIMARY]
But no combination of names '@'s, etc, allows me to use a variable name that I passed into the procedure. What am I missing? I will either receive a syntax error or the procedure will create a table called TableName rather than whatever TableName really stands for...
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
Can someone send me an example of creating a variable to use instead of a temp table? I cannot find an example on books on line, but know it is possible in SQL2000.
I€™ve got some tables with the year is part of the name, for example: TABLE2006, TABLE2007, etc.. . The year of the name of table I will read in the table INSERTED of my Trigger : I nead to create a trigger where I update those tables : SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE TRIGGER [TESTE] ON [dbo].[TABTESTE] FOR INSERT AS DECLARE @YearTable nvarchar(4), @IdClient INT, @MyTable TABLE ( IdClient INT, Situ NVARCHAR(50) ) BEGIN SET NOCOUNT ON; SELECT @YearTable = SITUACAO, @IdClient = IdClient FROM INSERTED SET @MyTable = 'TABLE' & @YearTable UPDATE @MyTable SET Situ = 'X' WHERE IdClient = @IdClient END GO Erros: Msg 156, Level 15, State 1, Procedure TESTE, Line 9 Incorrect syntax near the keyword 'TABLE'. Msg 137, Level 15, State 1, Procedure TESTE, Line 17 Must declare the scalar variable "@MyTable". Msg 1087, Level 15, State 2, Procedure TESTE, Line 18 Must declare the table variable "@MyTable".
I need to create a Table using the SQL Task and a Variable as the Table Name
I am getting an Error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The SQL Statement that I am using is Create Table ? (ColumnA char(5) Null)
Hi,I'm trying to dynamically assign the table name for a SELECT statement but can't get it to work. Given below is my code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROCEDURE GetLastProjectNumber (@DeptCode varchar(20)) AS BEGIN TRANSACTION SET NOCOUNT ON
DECLARE @ProjectNumber int SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','') SELECT MAX(@ProjectNumber) FROM 'tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.',''); END TRANSACTION Basically, I have a bunch of tables which were created dynamically using the code from this post and now I need to access the last row in a table that matches the supplied DeptCode. This is the error I get:Msg 102, Level 15, State 1, Procedure GetLastProjectNumber, Line 29Incorrect syntax near 'tbl_ProjectNumber'. Any help would be appreciated.Thanks.
Hello everyone, I'm still quite new ASP.net, Visual Web Developer 2008, and SQL. It has been a fun learning experience so far. Anyways, the site I am designing needs to allow its users to extensively search several different databases (MS SQL databases). I have followed many of the tutorials and have found it rather easy to add table adapters, gridviews and other data features that use basic SQL Select statements. One of the major database tables contains several columns which I would like to include as a search parameters from a drop down list. I was wondering if there is any way I can write a select which will pass a variable to be used as a column name to the statement? For example: SELECT DATE, GAME, EXACT, @COLNAMEFROM HistoryWHERE @COLNAME = @SOMEVARIABLE This obviously doesnt work, but thats the gist of what I want to do. Any suggestions? I need this to be simple as possible, Most everything I'm doing is done through the visual design mode. Im still slow to learn C# and apply it in the codebehind files unless I have very detailed step by step instuctions. Thanks Scott
I am running SQL Server 2000 and need to know how to write an IF else statement with a variable for a table name. I am constantly getting errors when I attempt this feat.
Code: Use [TestDatabase] Go CREATE PROCEDURE UserInputAsTable
Greetings!I am now doing one type of analysis every month, and wanted to creattable names in a more efficient way.Here is what happens now, everytime I do the analysis, I will create atable called something like customer_20050930, and then update thetable by using several update steps. Then next month I will create atable called customer_20051031. Does anyone know if there is a betterway to do it? like using a macro variable for the month-end date? Noweverytime I have to change the table name in every single update step,which would cause errors if I forget to change one of them. By using amacro, I would only need to change it once.Thanks!
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?
SET NOCOUNT ON
Declare @RandomRecordCount as int, @Counter as int Select @RandomRecordCount = 1000
Declare @Type table (Name nvarchar(200) NOT NULL) Declare @Source table (Name nvarchar(200) NOT NULL) Declare @Users table (Name nvarchar(200) NOT NULL) Declare @NumericBase table (Number int not null)
Set @Counter = 0
while @Counter < @RandomRecordCount begin Insert into @NumericBase(Number)Values(@Counter) set @Counter = @Counter + 1 end
Insert into @Type(Name) Select 'Type: Buick' UNION ALL Select 'Type: Cadillac' UNION ALL Select 'Type: Chevrolet' UNION ALL Select 'Type: GMC'
Insert into @Source(Name) Select 'Source: Japan' UNION ALL Select 'Source: China' UNION ALL Select 'Source: Spain' UNION ALL Select 'Source: India' UNION ALL Select 'Source: USA'
Insert into @Users(Name) Select 'keith' UNION ALL Select 'kevin' UNION ALL Select 'chris' UNION ALL Select 'chad' UNION ALL Select 'brian'
select 1 ProviderId, -- static value '' Identifier, '' ClassificationCode, (select TOP 1 Name from @Source order by newid()) Source, (select TOP 1 Name from @Type order by newid()) Type
In SQL Server you can do a SELECT INTO to create a new table, much like CREAT TABLE AS in Oracle. I'm putting together a dynamic script that will create a table with the number of columns being the dynamic part of my script. Got any suggestions that come to mind?
Example:
I need to count the number of weeks between two dates, my columns in the table need to be at least one for every week returned in my query.
I'm thinking of getting a count of the number of weeks then building my column string comma separated then do my CREATE TABLE statement rather then the SELECT INTO... But I'm not sure I'll be able to do that using a variable that holds the string of column names. I'm guess the only way I can do this is via either VBScript or VB rather then from within the database.
Hi,I want to create a temporary table and store the logdetails froma.logdetail column.select a.logdetail , b.shmacnocase when b.shmacno is null thenselectcast(substring(a.logdetail,1,charindex('·',a.logde tail)-1) aschar(2)) as ShmCoy,cast(substring(a.logdetail,charindex('·',a.logdeta il)+1,charindex('·',a.logdetail,charindex('·',a.lo gdetail)+1)-(charindex('·',a.logdetail)+1))as char(10)) as ShmAcnointo ##tblabcendfrom shractivitylog aleft outer joinshrsharemaster bon a.logkey = b.shmrecidThis statement giving me syntax error. Please help me..Server: Msg 156, Level 15, State 1, Line 2Incorrect syntax near the keyword 'case'.Server: Msg 156, Level 15, State 1, Line 7Incorrect syntax near the keyword 'end'.sample data in a.logdetailBR··Light Blue Duck··Toon Town Central·Silly Street···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00······0234578······· ····· ··········UB··Aqua Duck··Toon Town Central·Punchline Place···02 Sep2003·1·SGL·SGL··01 Jan 1900·0·0·0·0·0.00·····Regards.
I have a question regarding a locking scheme in MSSQL I hope you guys can help. In Sybase, I am able to specify datarow locking in DDL (ex. create table, alter table). Can I do the same in MSSQL or is there an equivalent option in CREATE TABLE statement in MSSQL? I came across a few articles in MSDN about datarow locking and it seems to me that MSSQL only allows locking through DML... Is that true? Thanks.
I need to expand resursion level for resursive CTE expression within CREATE FUNCTION statement for inline table function to a value greater than default. It turns out that OPTION clause for MAXRECURSION hint perfectly works if I use it outside CREATE FUNCTION (as well as CREATE VIEW for non-parametrized queries), but it does not within CREATE FUNCTION statement - I'm getting error:
Msg 156, Level 15, State 1, Procedure ExpandedCTE, Line 34
Incorrect syntax near the keyword 'option'.
Here is the function:
create FUNCTION [dbo].[ExpandedCTE]
(
@p_id int
)
RETURNS TABLE
AS
RETURN
(
with tbl_cte (id, tbl_id, lvl)
as
(
select
id, tbl_id, 0 lvl
from
tbl
where
id = @p_id
union all
select
t.id, t.tbl_id, lvl + 1 from
tbl_cte inner join tbl t
on rnr.tbl_id = tbl_cte.id
)
select
id, tbl_id, lvl
from
tbl_cte
option (maxrecursion 0)
)
Please help!
Alexander.
P.S. I'm really sorry if it is about syntax, but I could not find it in the documentation.
I 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.
I have a simple Control Flow setup that checks to see if a particular table exists. If the table does not exists, the table is created in an alternate path, if it does exist, the table is truncated before moving to a file import Data Flow that uses an OLE DB Destination to output the imported data.
My problem is, that I get OLE DB package errors if the table the OLE DB Destination Container references does not exist when I load the package.
How can I over come this issue? I need to be able to dynamically create the table in an earlier step, then use that table to import data into in a later step in the workflow.
Is there a switch I can use to turn off checking in the OLE DB Destination Container so that it will allow me to hook up the table creation step?
Seems like this would be a common task...
Steps:
1. Execute SQL Task to see if the required table exists 2. Use expresions to test a variable to check the results of step 1 3. If table exists, truncate the table and reload it from file in Data Flow using OLE DB Destination 4. If table does not exist, 1st create it, then follow the normal Data Flow
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
insert into #t(branchnumber) values (005) insert into #t(branchnumber) values (090) insert into #t(branchnumber) values (115) insert into #t(branchnumber) values (210) insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
I have a dts set up to take info from our as/400 and load it onto into SQL 7.0 server. I would like to within my select that I'm using to load up the data calulate a variable or create a temp table with one row that has the current fiscal year. This fiscal year would then be used in my select to determine what data to bring in. But it doesn't seem like I can use a table on the server within my select statement on the destination portion of my dts. This is what I would like to accomplish select * from iptsfil.ipwkhst ,swoolib.DSSCNTL where KYR#=select max(fiscyear) from fiscalyear(this is my temp table that I have already created) AND KWK# = STRNUM AND KCT#=1 AND TBLNME = 'WEEKHST'
the dts doesn't seem to like me using both file from the server and the 400 at the same time. Is there a way to do what I'm trying to accomplish
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!
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?
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.
