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
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 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 just want to store the value of a parameter in a global variable that all my reports in the same project can use.
My goal is to create a dynamic query. For example:
Company Name: Widgets inc.
Divisions: Sales, Service, Tech, Accounting
I have a matrix and when I click on the more information button it goes to another report. I want the next report to know what division is currently selected in the dropdown parameter. So, being a VB programmer, I thought I could store parameter1.division.value into a global variable and update the variable whenever the parameter changes.
This way, on the next report, my query's where statement is the global variable.
@GlobalVariable = parameter1.division.value
Select name, address, phone FROM Employee WHERE division = @GlobalVariable
I am using Visual Studio to design this project although I would prefer to use VB or ASP. But this is my only stumbling block right now. Everything else is complete.
Hello, We have a need to create a variable within a View. We tried declaring our variable like we would in a stored procedure, but that doesn't seem to work. What is the proper way to do this? Thank you. Kay
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...
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.
Is it possible to have part of a table name used in a CREATE statementcontained in a variable? Here's what I'd like to do, althoughobviously the syntax of this isn't quite right or I wouldn't be hereasking:DECLARE @TblPrefix char (3)SET @TblPrefix = 'tst'CREATE TABLE @TblPrefix + TestTable (col1 int)The point there is to have a table named tstTestTableThe reason I need to do this is that my ISP will only give me onedatabase to work with and I'd like to have two copies of theapplication I'm developing running at the same time. So I'd like torun the sql script that creates the tables with TblPrefix set to "dev"and then run it again with TblPrefix set to "liv"thankseric
Everyday they are sending to our servers a file named like CC_20080501 . it is CC_YYYYMMDD So I have to check it and move into the database table. I am thinking to create standard database connection and name like CC_std And When the file arrives to create something like CC_std (actually copying my daily file CC_YYYYMMDD to CC_std ? BUT how can I do it? any other way to do it?
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)
__________________________________________________ _ CREATE TRIGGER dbo.Fochini_Insert ON dbo.FochiniTable AFTER INSERT AS BEGIN DECLARE @v_object_key VARCHAR(80) DECLARE @v_object_name VARCHAR(40) DECLARE @v_object_verb VARCHAR(40) DECLARE @v_datetime DATETIME
SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins <--- my problem area!! SET @v_object_name = 'FochiniTable' SET @v_object_verb = 'Create' SET @v_datetime = GETDATE()
IF ( USER <> 'webuser' ) INSERT INTO dbo.xworlds_events (connector_id, object_key, object_name, object_verb, event_priority, event_time, event_status, event_comment) VALUES ('Fochini', @v_object_key, @v_object_name, @v_object_verb, '1', @v_datetime,'0', 'Triggered by Customer CREATE')
END ________________________________________________
i'm trying to get the INSERTED variable from table FochiniTable on colomn Cust_Id
and the statement: SELECT ins.Cust_Id INTO @v_object_key FROM inserted ins - is failing [still a newbie on mssql server 2000]
I have a booking system which stores an event along with a start date and a finish date. I want to create a table which has a row for each day the event is on. I can find the number of days easily enough using a datediff function but I don't know how to translate this into that number of rows. My current thinking is to cross join the original table out to another temporary table with a large number of rows and use SQL2005 to only select the top 'x' rows, then use the rownumber of this temp table to add the required number of days to the StartDate to get each eventdate. Currently I use a loop to build the required table but there must be a way to get it all done in a single statement :) Thanks
ORIGINAL DATA EventID StartDate EndDate 1 1/1/08 1/1/08 2 20/1/08 22/1/08
Need to create same login on 100s of servers using local admin login create on the OS. (for xp_cmdshell proxy setup)
Normal command works fine but I meed to make <svr_name> a variable.
CREATE LOGIN [<svr_name>DBALocalUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english] GO
When I try various efforts to pass in the <svr_name> portion it fails every time.
declare @cmd varchar(200), declare @username varchar(50) set @username = 'DBALocalUser' set @cmd = ' CREATE LOGIN ['+@@servername+''+@username'] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]'
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!
I'm trying to create a variable for use in a DTS job for yesterday's date in the string format YYYYMMDD for use in pulling yesterday's material transactions from my ERP system. I've got the Query statement created and working fine, but I have to manually go into the DTS job and insert the proper date manually. I'm looking into an Active-X script inserted into the workflow for the job to create a global variable, but I can't seem to come up with anything that works. Any help or direction would be appreciated. Thanx in advance!! Glenn
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.
I want to create a SP that creates a new database, so I script-ed out the db and paste the script into new SP gui (SQL 2000). I want to pass it a variable for the data/log file location that is not the default location. The original script looks like this: CREATE DATABASE [PWRR_DDS] ON (NAME = N'PWRR_DDS_Data', FILENAME = N'F:SQL SERVER FILESDatabasesdbName.mdf' , SIZE = 3118, FILEGROWTH = 10%) LOG ON (NAME = N'PWRR_DDS_Log', FILENAME = N'F:SQL SERVER FILESDatabasesdbName_Log.LDF' , SIZE = 5000, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS. I replaced the path of the FILENAME variable like this: CREATE DATABASE [PWRR_DDS] ON (NAME = N'PWRR_DDS_Data', FILENAME = @DBPath, SIZE = 5000, FILEGROWTH = 10%) LOG ON (NAME = N'PWRR_DDS_Log', FILENAME = @LogPath , SIZE = 5000, FILEGROWTH = 10%) COLLATE SQL_Latin1_General_CP1_CI_AS, declaring the variables as char(500). The error I get is "Incorrect sybtax near '@DBPath'. Any ideas for workaround? Thanks, EJM
now i am currently using SSIS Package using BUI, The Source and Destination File we Given Manullay Connect the Server name ,And Table . Instead of given Manual . How to create Global Variable Connection Manager.
Suppose Today i am Working Developement Server. Latter i will be changed Production Server Database. At That time we have to Going to Modify all the Connection .Instead of This How to Create the Connection Manager Gloabe Variable . and How to Use .Please Any one give Sample For Connection Manager variable for Different Server.
Is it possible to use a variable to specify the filegroup in the ALTER/CREATE PARTITION SCHEME command?
I want the partition scheme to use the default filegroup for ALTER and CREATE PARTITION SCHEME. At the time the script is created, I don't know the default filegroup in the database.
My code:
declare @fileGroupName VARCHAR(50) = (select top 1 name from sys.filegroups where is_default = 1) ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName
Is failing:
Incorrect syntax near '@fileGroupName'.
Q: Is it possible to use a variable for the filegroup in the ALTER/CREATE commands? Is so, what is the correct syntax?
Q: If using a variable is not possible, is there another way to specify the default filegroup?
As part of my package, i require a date (Only date, not DateTime) which is 10 months previous to get date.Eg: for today if the package executes, then i want 12/1/2014 , which i will use in my package as a filter like 'where date='?' where ? is a paramter which is is derived from the above logic
So, I have a project parameter @ppdate with value as  -10. I create a variable with DateTime (because there is NO date type for SSIS) and gives the expression as below
dateadd("Month",@ppdate, DATEADD("D",-(DAY(GETDATE()))+1,GETDATE())) , I am getting '7/1/2011 11:33:38 AM' which i don't want - i want only '12/1/2014'. How can i get it?
To get '12/01/2014', Â If i change the variable from DateTime to string, then i think i cant use the value in the filter condition like ''where date='?' because this does not accept string. Is this correct?
I am writing a stored procedure and have a query where I create a variable from other table
Declare @Sem varchar (12) Null @Decision varchar(1) Null Select emplid,name, Semester Decision1=(select * from tbldecision where reader=1) Decision2=(select * from tbldecision where reader=2) Where Semester=@Sem And Decision1=@Decision
But I am getting error for Decision1 , Decision2. How can I do that.
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...
