I have the following dataset using dynamic SQL which works when i execute it, when i try to create a table using this dataset i cant see any fields. Does anyone know why dynamic SQL doesnt work ?
Declare @TopRange int
Declare @BottomRange int
Declare @SQL Varchar(1000)
IF @Param_leadage = '91+'
SET @TopRange = 91
ELSE
Set @TopRange = RTRIM(LEFT(REPLACE(@Param_leadage,'-',''),2))
IF @Param_leadage = '91+'
SET @BottomRange = 4000
ELSE
Set @BottomRange = LTRIM(RIGHT(REPLACE(@Param_leadage,'-',''),2))
SET @SQL = 'SELECT dbo.tblCustomer.idStatus, dbo.tblCustomer.idCustomer, dbo.tblCustomer.DateSigned' +
' FROM dbo.tblCustomer' +
' WHERE DateDiff(day, dbo.tblCustomer.DateSigned, GetDate()) >= ' + convert(varchar,@TopRange) + ' AND DateDiff(day,dbo.tblCustomer.DateSigned, GetDate()) <= ' + convert(varchar,@BottomRange)
IF @Param_status = 'Online Churn'
SET @SQL = @SQL + ' AND dbo.tblCustomer.idStatus = 4 or dbo.tblCustomer.idStatus = 5 or dbo.tblCustomer.idStatus = 11'
ELSE
SET @SQL = @SQL + ' AND dbo.tblCustomer.idStatus = ' + @Param_idstatus
I have the following situation. Our customer would like to generate a raw data export report from specific customer databases.
I have an administrative database with a customer table. In this customer table each customer has it's on specific customerId guid. Each customer also has it's own customer database that is named: Customer_ + guid of the customer in the admin database (eg: Customer_982240e79f424fb0a1d4bed16267245d). This customer database has 1 main table (dbo.Data), but the design can be different depending on the customer. Our customer would like to have the possibility to select specific fields from the table to export to an excel file. So i never know in advance how much fields there will be in the table.
What i want to achieve is to generate a report where i can select a specific customer from a list (from the admin database). Then be able to view all the available fields inside the table (like a dataset or something) and then let the customer select which fields he wants in his data export report.
I am having trouble figuring out why the following code throws an error:
declare @cols nvarchar(50), @stmt nvarchar(max) select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W select @stmt = ' select *
[Code] ...
The issue that I am having is:
Msg 245, Level 16, State 1, Line 4 Conversion failed when converting the varchar value '[' to data type int.
I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not).
I have a question regarding the dynamic sql. I have a script which which deletes all the foreign keys and re- creates it. So, I have created a table instead of a temp table. Now I need to create that foreign keys table in such a way that in which ever database I run the script, this foreign keys table should be created in that particular database.
For example:
IF OBJECT_ID('tempdb..keys','U') IS NOT NULL DROP TABLE keys CREATE TABLE keys.[@sourceserver],[@database]. (RowId INT PRIMARY KEY IDENTITY(1, 1), ForeignKeyConstraintName NVARCHAR(200), ForeignKeyConstraintTableSchema NVARCHAR(200), ForeignKeyConstraintTableName NVARCHAR(200), ForeignKeyConstraintColumnName NVARCHAR(200)
hi,I wish to create a temporary table who's name is dynamic based on theargument.ALTER PROCEDURE [dbo].[generateTicketTable]@PID1 VARCHAR(50),@PID2 VARCHAR(50),@TICKET VARCHAR(20)ASBEGINSET NOCOUNT ON;DECLARE @DATA XMLSET @DATA = (SELECT dbo.getHistoryLocationXMLF (@PID1, @PID2) as data)CREATE TABLE ##@TICKET (DATA XML)INSERT INTO ##@TICKET VALUES(@DATA)ENDis what i have so far - although it just creates a table with a name of##@TICKET - which isn't what i want. I want it to evaluate the name.any ideas?
I have written a script to pivot a table into multiple columns.
The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.
Here is a copy of the script below
-- Dynamic PIVOT IF OBJECT_ID('#External_Referrals') IS NULL DROP TABLE #External_Referrals; GO DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
I can create static report with Report Server, but wanted if anyone can help me create a report that is dynamic and regenerates with new data as it comes in.
I have a procedure that creates a large dynamic view of several tables. The view is a union view of up to 15 tables. The table names are all <name>_DDMM where name is the standard table name and ddmm is the day and month of the tables data. The tables are created by a software supplied by another company, so I can not ensure that the tables will always have exactly the same fields or number of fields. Sometimes the company will add more fields to the tables in thier updates. So, I have to include the field names in the SQL exec command to create the query. This makes for a very long exec command and depending on the number of tables it needs to include, it can require upwards of a 16,000 character string. Obviously, this can't work, so I had to break up the variable in order to create the procedure. However, I'm wondering if there isn't a better method than creating three different 8000 varchar variables and having overflow write to the next variable in line. Especially if the number of tables needs to be expanded, it could be a problem. Is there a better way to run a create view exec command on a large number of characters?
EDIT: Changed the title to read Procedurally generating a large view.
I am trying to use a first_name , Last_name and append this as username something like mike test and create username like 'Mtest@123'. and password as well.
The first_name, last_name comes from a table called "person".
Right now the user name and password is hard coded and I need to make this dynamically generated, is that possible within SQL?
How can I do this within SP? Below is my sp:
Alter PROCEDURE PatientEnroll (@person_id AS uniqueidentifier) AS BEGIN SET NOCOUNT ON INSERT INTO dbo.ngweb_bulk_enrollments(row_id, person_id,practice_id,
Hi I have developed an application in ASP/SQL server 7. Ths system is single user. One of the tables is updated by usr actions( say Table A). To make it multi user. I want to create table such as A_Username. How can query be written for this. Also there are many stored procedures which will access this table. In these stored procedures i will send username as an input. Then the query in the stored procedure shd access the table as A_Username . Such dynamic table name refrencing , how can it be done.. Is creating a string for the query and then executing it using sp_exec the only option? pls suggest
I have one table which holds all the information for the forms and i have another table which holds list of all the form fields. Now these two tables are mapped and that table give me list of all the fields in any forms.
To get the information about the users who filled different forms first i need to query the tblFormsToFormFields and get the list of all the fields for that form and then use that fields list in the select query to get the result.
In my report i need to provide the drop down with the list of all the forms and then display the information for all the customers for that particular form with the list of the form fields.
Is there in SSRS 2005 i can use C# or any programming language to create Columns in the report dynamically as there will be different fields for each form.
I am somewhat new to DTS packages - please bear with me...
:: Overview: From a web page, I need to create a text file on a User's mapped drive drive (note: the mapped drive is always the same and it is a drive that the IIS cannot see, and FTP is not an option here). One of the parameters I need to pass from the web page (to the DTS) is the text file name.
Currently, I have a stored procedure that creates my dataset for my text file, and I can create the text file from the web page onto the IIS server, but this does not solve my problem.
My dilemma is how do I create the dynamic text onto the mapped drive? The SQL server CAN see the mapped drive.
Can I bring together what the Stored Procedure creates AND pass thru the dataset and the dynamic name to a DTS so it can create and save the file?
Can the Stored Procedure be eliminated and it all be done via DTS?
What about passing in parameters directly to a DTS from the web page? I'm not finding much help on this portion
Any help and/or direction would be appreciated as I am on a tight deadline!
I have a scenario, need to create SQL server Tables dynamically.
I Have multiple xml data file on a particular location, and want to load those XML data into sql server tables, but he metadata of each xml data files are not same.
Hence the approach is that,
1. Pick first file from that location 2. Create a table according to that xml data file metada 3. load data on newly created table. 4. Pickup the next xml data files. 5. loop through, till the XML data files are exists on that location.
Banti writes "IF i create temporary table by using #table and ##table then what is the difference. i found no difference. pls reply. first: create table ##temp ( name varchar(25), roll int ) insert into ##temp values('banti',1) select * from ##temp second: create table #temp ( name varchar(25), roll int ) insert into #temp values('banti',1) select * from #temp
both works fine , then what is the difference waiting for ur reply Banti"
Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
I am trying to create a table that holds info about a user; with the usual columns for firstName, lastName, etc.... no problem creating the table or it's columns, but how can I "restrict" the values of my State column in the 'users' table so that it only accepts values from the 'states' table?
Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.
The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.
Table: Dummy ================================================== == field1 field2 field3.......... Empid Empname empaage1 sam 23........... 2 rai 22............ . . . n raj 45............. ================================================== == Now I want to create another table as "EMP" , with proper data type fields too..
Table: EMP ================================================== == Empid Empname empaage............ 1 sam 23............... 2 rai 22................ . . . n raj 45................. ================================================== ==
I want to do this dynamically..... Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]
A small rough snippet of the code will be appreciated....
I am using a Stored procedure in which I am creating a table and dropping that table at the end of execution. This SP is calling every 10 second (but no concurrent access) from my application. Is there any issue using the drop table command in the SP? will it create any memory fragmentation issue in SQL server?
I am new to T-SQL i got this code from some website but its not working can anyone let me know why @cur_tab_name varchar(30) is not decleared while i have decleared I want to use this for dynamic name of the table.
I want to know how to create a DTS for dynamic tables.., Details: 1)I have some tables which created every month(Like [Name][2 char of month][2 char of year] eg: name0206 this table is for feburary 2006),i want to create dts for those tables
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
I need to get the field values of a table (name will be dynamic).Then assign those values to properties in a class.Let's say I will get the table name dynamically.dim tblName as string = "tablea"The 2 tables can each have 25 fields or so.I need a way to select the amt and email field values from tblName. Without saying "select job_amt, job_email from ...Is there someway to get the values based on the column name. So if the column name has amt and email, then give me those values.Maybe loop through the datatable - then for each column -- if col.ColumnName.IndexOf("Amt") = 0 or col.ColumnName.IndexOf("email") = 0 thenthen drop that column from the datatable.ex of table structure<u>tablea</u>job_idjob_amtjob_email<u>tableb</u>dance_idamt_dancedance_email
Hi,I'm trying to create some tables dynamically based on the content of another table in the same database. I found a post that does what I want to do, but I can't get my code (that is similar to the post) to work.Given below is my code: 1 DECLARE @deptCode varchar(50), @numberOfDept int, @tableName varchar(MAX), @columnName varchar(MAX) 2 DECLARE @lengthDeptCode int, @lengthTableName int, @lengthColumnName int 3 4 SELECT @numberOfDept = COUNT(DISTINCT DeptCode) 5 FROM tbl_Department; 6 7 WHILE (@numberOfDept >=0) 8 BEGIN 9 SELECT @deptCode = DeptCode, @lengthDeptCode = LEN(DeptCode) 10 FROM tbl_Department; 11 12 SET @tableName = 'tbl_ProjectNumber'+@deptCode 13 SET @lengthTableName = LEN(@tableName) 14 SET @columnName = 'ProjectNumber'+@deptCode 15 SET @lengthColumnName = LEN(@columnName) 16 17 CREATE TABLE CAST(@tableName as char(@lengthTableName)) 18 ( 19 CAST(@columnName as char(@lengthColumnName)) int IDENTITY(1,1) NOT NULL 20 ) 21 22 SET @numberOfDept = @numberOfDept - 1 23 END This is actually my first time using SQL programatically so I'm guessing there are alot of problems with it. I just don't know what exactly. The error I get is:Msg 102, Level 15, State 1, Line 18Incorrect syntax near '@tableName'. Thanks.
Hi there,I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called@TABLENAME VARCHAR(16)and when i say SELECT * FROM @TABLENAMEit is giving me an error and I think I cannot declare @TABLENAME as a table variable because I do not want to create a temp table of sorts.I hope I am clear.thanks,Murthy here
I'm trying to work on an Integration Services project, and want to have a table copied into another table, but this destination table needs a datetimestamp in the name, like tbl061708547pm. I can get this to work, but if I try to use it with an into statement and a "+", I get an error telling me something is wrong around the "+": DECLARE @Date char(23)SET @Date = 'myTBL_' + CONVERT(char(23), GETDATE(), 14)SELECT colA, @Date FROM myTBL *****Error with:DECLARE @Date char(23)SET @Date = 'myTBL_' + CONVERT(char(23), GETDATE(), 14)SELECT * into @Date FROM myTBL ...and error with:DECLARE @Date char(23)SET @Date = CONVERT(char(23), GETDATE(), 14)SELECT * into 'myTBL_ + @Date FROM myTBL;
Is it possible to create a temp table using exec. e.g
exec('create table #temp1 (xyz int, abc int)')
when I run the above statement, I dont get an error but nothing happens.
The reason I need to create it dynamically is that in my stored proc, I am passed a number. This number determines how many columns my temp table should have. thanks Zoey
