Greetings all,
I want to be able to generate a table on the fly. I know I can use dynamic sql to do this but my brains have forzen this afternoon so any help would be much appreciated.
I have the first query that returns one column with three rows. The contents of the these three rows will form the new columns in the dynamically generated table. How can I do this?
Thanks.
Hi,
Is there a way to dynamically create a new table in sql server express using the code behind with vb on a Page_Load event?
Thanks Matt
Hello,I am interested in dynamically creating temp tables using avariable in MS SQL Server 2000.For example:DECLARE @l_personsUID intselect @l_personsUID = 9842create table ##Test1table /*then the @l_personsUID */(resultset1 int)The key to the problem is that I want to use the variable@l_personsUID to name then temp table. The name of the temp tableshould be ##Test1table9842 not ##Test1table.Thanks for you help.Billy
View 5 Replies View RelatedHello all:
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.
create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )
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..
create table emp (
empid int, empname varchar(100), empage int....)
The table should look like as:
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....
Waiting for replies........
saby
I am getting error when I am trying to create table on runtime
Declare @FileName varchar(100)
Declare @File varchar(100)
set @FileName='brkrte_121227102828'
SET @File = SUBSTRING(@FileName,1,CHARINDEX('_',@FileName)-1)
--=select @File
[Code] ....
Error massage:-
Msg 203, Level 16, State 2, Line 16
The name 'CREATE TABLE DataStaging.dbo.Staging_brkrte ( [COL001] VARCHAR (4000) NOT NULL, [Id] Int Identity(1,1), [LoadDate] datetime default getdate() )' is not a valid identifier.
How to resolve above error....
Hi - hope someone can help. We're creating a ASP.NET C# site that allows a user to connect to any of our 100+ SQL Servers and query a database that sits on every server. I'm trying to work out (and failing) how we can set the 'Data Source' in a connection string dynamically using the server name selected by the user in a drop down box. Any help appreciated, thanks in advance,
View 2 Replies View RelatedHi guys
I'm investigating whether if its possible to Dynamically create a cube. Then Process this cube before exporting it to a .cub file.
I know that DTS in sqlserver is able to process a cube given at a scheduled time interval. But I'm not sure how I can export a cube to a offline .cub file dynamically. The only way that i know to create a .cub file is via PivotTable in Excel.
Any help in pointing me to the right direction is appreciated.
Thankyou :)
Tom
Hi everybody,
I have a problem. When I try to create a database with this code:
strConnection = "server=(local);Integrated Security=SSPI"
objConnection = New SqlConnection(strConnection)
objConnection.Open()
Dim strSQL = "CREATE DATABASE xxxxxx ON PRIMARY"
strSQL += "(Name=test_data, filename = 'xxxxx.mdf', size=3,"
strSQL += "maxsize=5, filegrowth=10%)log on"
strSQL += "(name=mydbb_log, filename='xxxxx.ldf',size=3,"
strSQL += "maxsize=20,filegrowth=1)"
objCommand = New SqlCommand(strSQL, objConnection)
objCommand.ExecuteNonQuery()
it usually works, except when the name of the database (xxxxxx) contains a number in the beginning of the name or if it is all made up completely by numbers. I also converted the database name into a string, but it doesn't work either.
What is the problem?
How can I solve it?
Thanks in advance
I was given what at first seemed a simple task (and maybe it is, but with everything else on my plate, I can't seem to get my head wrapped around this)...create a grid or table something similar to below:
Plan A Plan B PlanC PlanD
Item1 yes yes no no
Item2 yes yes no no
Item3 yes no yes no
etc.....
The "plans" are stored in one db table, the "items" in another. What I can't seem to get straight in my mind is how to relate the yess and nos and then generate a tabular layout similar to the above.
I don't need anyone to do it for me, but a push in the right direction would sure be nice (using SQL 2005) Thanks!
Hi
Some one Please guide me... How to create a Excel File Dynamically in DTS.
Once i run my DTS Package the result should be moved to a new Excel sheet( This should be created dynamically).
I tries in this way but it says....Syntax error at the DTS GlobalVariables.....
Function Main()
Dim appExcel
Dim newBook
Dim oSheet
dim oPackage
dim oConn
Set appExcel = CreateObject("Excel.Application")
Set newBook = appExcel.Workbooks.Add
Set oSheet = newBook.Worksheets(1)
oSheet.Range("A1").Value = "au_lname"
oSheet.Range("B1").Value = "au_fname"
oSheet.Range("C1").Value = "phone"
oSheet.Range("D1").Value = "address"
oSheet.Range("E1").Value = "city"
DTSGlobalVariables("fileName").Value = "C:\" & Month(Now) & "-" &
Day(Now) & "-" & Year(Now) & "-" & Hour(Time) & "-" &Minute(Time) & "-" &
Second(Time) & ".xls"
With newBook
.SaveAs DTSGlobalVariables("fileName").Value
.save
End With
appExcel.quit
set oPackage = DTSGlobalVariables.parent
set oConn = oPackage.connections(2)
oConn.datasource = DTSGlobalVariables("fileName").Value
set oPackage = nothing
set oConn = nothing
Main = DTSTaskExecResult_Success
End Function
Folks,
Is there any way I can dynamically alter the structure of a table? In other words if my Transact SQL
statement returns 5 rows I want to alter an existing table and put in 5 columns. Strange?!
Something like
alter table add
column+@i
The alter table statement is within a Cursor and ideally @i should increment each time and alter
the table to put in a new column.
If I went 3 times thru the loop, I should have
column1
column2
column3
added to the new table.
Any pointers would be greatly appreciated. Thanks much in advance.
Has anyone found out how to dynamically create report width.
If I have 20 columns on a report all of 2cm, then report will be 40cm width.
If I display the columns based on specific criteria using the iif function and then setting the an expression for the Visibility Hidden property, the report could potentially reduce in width.
I cannot see how to build an expression for the report width or table width, to accommodate the potential change in the width of the report when columns are not shown, thus it pages incorrectly, leaving huge white space on the right of the report when columns are hidden... has anyone come across this requirement, and hopefully a solution for it?
Many Thanks people
Hi
My SSIS package automatically creates Excel files everyday.
i want the excel files to get todays date when they are created .
Eg:06152008 for today and 06162008 for tomorrow
please help
Hi everybody, i'm a newbie to SSIS and I'm having a problem dynamically creating a new excel spreadsheet in SSIS.
What I need to do is be able to dynamically create a brand new Excel spreadsheet after a data flow task completes.
Any help would be most appreciated. Thank you.
i have a ssrs report in which i have creating rows with heading of months name from jan to dec i.e. jan|feb|mar|apr|....i have a parameter start date.i want when i enter start date from march supoose i enter date 03/22/2015 then display months from mar to dec.
View 5 Replies View Relatedthanks
View 4 Replies View RelatedTrying to figure out the best method of reading in a number of flat files, all with different number of columns and data types and outputting them to a database.
Here's the problem: They are EBCDIC encoded and some of the columns are packed decimal. I've set up one package that takes the flat file, unpacks the decimal (Using UnpackDecimal component) and then sending the rest through a second component to go from EBCDIC -> ASCII.
What I need is a way to do this for every flat file based on the schema for that flat file. One current solution is to write a script/app to create the .dtsx XML file and then execute that for each flat file. It appears like this may be possible, but I haven't gotten far enough to know for sure. So my questions are this:
1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.
2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).
Thanks,
Travis
Hi All,
I have an exiting excel workbook say master.xls. Now I need to dynamically create and append a new worksheet to the above master.xls every month end using the Reporting services.
Could you please guide me how dynamically creating the worksheets task can be achieved using the reporting services?
Your any guidance or help in this matter will be highly appreciated.
Thanks in advance
Regards
Raman Kohli
I have three databases which have the identical data structure. When the user runs a report, he has to select a database.
For example, my report query would look like:
Select * from <theDatabase>.dbo.MyTable
How could I assign the value of <theDatabase> at run time depending on what the user selects from the list?
In my datasource, I have only specified the server name and no value for initial catalog.
If I run the following command in a Query window it works:
RESTORE DATABASE CIS_Source_Data_Test FROM DISK = 'y:CIS_Source_Data_backup_2015_06_17_085557_7782407.bak' WITH RECOVERY, REPLACE
If I dynamically put together the command and store it in variable @cmd and then execute it using
exec sp_executesql @cmd or exec (@cmd) it does not work. I get the following:
Msg 2745, Level 16, State 2, Procedure CIS_Source_Data_Refresh, Line 92
Process ID 62 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Procedure CIS_Source_Data_Refresh, Line 92
RESTORE DATABASE is terminating abnormally.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Why it won't work when I try to create and run it dynamically?
Is there anyway to dynamically create Excel Worksheets based on a group identity results from a query?
Thanks!
SkySeek.
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely,
Bryan Ax
we have a table in our ERP database and we copy data from this table into another "stage"Â table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
View 4 Replies View RelatedI have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
[code]....
Hi,I have 30 Table with same structure but it will accept different value at RunTime according to AirCraftType. I wand to create a stored prosedure that will accept TableName, FlightDate, FlightNo. whenever i wnat to execute this code in the database I received error :Must declare the variable '@TableName'create proc InsertDataForAirBus330 @FlightNo varchar(5),@TableName varchr(5),@FlightDate datetime
as
insert into @TableName values(@JourneyDate,@FlightNo,18,42,280,3,7,35)plz suggest me how to solve this pboblem...Jasim...
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
How do I dynamically pivot a table? On my example below, the STORE changes.
View 2 Replies View RelatedHi,
I have N1 table where columns name(id,Field). Base on the fields of this table I want to create N2 table from SP where data from N1 will be columns in N2.
id Field
-- ------
1 ID
2 First
3 Last
Create table N2(ID,First,Last)
regards,
Mark
Hi,
I want to load data from one database (from all tables) to another database. For this I am using €˜Foreach loop€˜(with SMO enumerator), I can get table name for specific database into variable successfully but now I want to load data from that table, for this I am using data flow task. In data flow task I have added €˜OLE DB Source€™ with data access mode €˜Table name or view name variable€™ and set variable but it gives me an error when loading data from 2nd table due to change in schema.
How to set table name dynamically in €˜OLE DB source€™?
Thanks in advance J
Omkar.
Hello,
I have to design a DTS package (not SSIS ) in which i want to select the destination table dynamically. Can any one help me out.
Thanks
MV
Hi
i'm a newbie reporting services developer (first experience) and i have this problem that i'm trying to explain you by steps:
1 i link the report to a stored procedure to know if a user is enabled to watch or not the results table
2 if the user not enable to watch i'll write on the report "user not enable..." and so i hide the result table
...is it possible to do this?
Thanks in advance for any information
I'm trying to create a procedure that can access a column in a table dynamically.<br>
Lets say I have the following table<br>
create table table_items<br>
(rec int identity not null,<br>
item1 int,<br>
item2 int,<br>
item3 int)<br>
and I have a procedure as such
create procedure access_item<br>
@rec int,<br>
@itemindex int,<br>
@item int as<br>
select @item = (select item1 from table_items where rec = @rec)<br>
However, the column I want to access is @itemindex.<br>
If @itemindex = 1, then set @item to item1<br>
if @itemindex = 2, then set @item to item2<br>
if @itemindex = 3, then set @item to item3<br>
I can't use a simple 'if/then' selection, because the number of columns in the table can grow, and I don't want to have to rewrite the procedure everytime a column is added.<br>
How do I do this?<br>
I tried to use an execute command, as such<br>
create procedure access_item_2<br>
@rec int,<br>
@itemindex int,<br>
@item int as<br>
declare @sql varchar(255)<br>
select @sql = "select @item = (select item" + convert(varchar(2), itemindex) + " from table_items where rec = @rec)"<br>
execute (@sql)<br>
but when the procedure runs, I get the following error:<br>
'@item is not a valid variable.'<br>
What else can I do?<br>
Please e-mail me at sam@microcsl
I am having SP which gives, two result sets. The columns which are coming from result sets are also dynamic.
i.e. some time 5 columns and some time 10 columns.
Now I want to load this output into 2 different tables on daily basis. This would be truncate/delete table and load again.
Now my problem is that as I am not sure about columns, Is it possible to create table(Physical Table) depends on output of SP, and after load data into it.
During each load we can drop table, No issue and we can handle this through SSIS Package.