Dynamically Creating A SQL Connection

Mar 6, 2008

 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


ADVERTISEMENT

Dynamically Creating Cube

Jun 21, 2005

Hi 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

View 1 Replies View Related

Creating A Table Dynamically

Jun 10, 2008

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.

View 11 Replies View Related

Problem Creating Database Dynamically

Jul 29, 2004

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

View 1 Replies View Related

Dynamically Creating Features Grid

Mar 17, 2006

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!

View 2 Replies View Related

Dynamically Creating A Excel File In DTS

Feb 13, 2006

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

View 2 Replies View Related

Dynamically Altering/creating Tables

Jul 22, 1998

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.

View 1 Replies View Related

Dynamically Creating Report Width

Apr 5, 2007

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



View 1 Replies View Related

Creating A Table Dynamically In SQL Server Express With Asp.net And Vb

Jun 11, 2006

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

View 2 Replies View Related

Dynamically Creating File Name With Todays Date

Jun 15, 2008

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

View 3 Replies View Related

Dynamically Creating Temp Table Names

Jul 20, 2005

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 Related

Dynamically Creating A New Excel Spreadsheet In SSIS

Feb 25, 2008

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.

View 1 Replies View Related

Reporting Services :: Creating Cells Dynamically

Nov 2, 2015

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 Related

Any Progress On Creating Columns Dynamically On SQL 2005 Report?

Nov 6, 2007

thanks

View 4 Replies View Related

Dynamically Creating SSIS Package For Each Flat File

Apr 18, 2007

Trying 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

View 1 Replies View Related

Dynamically Creating New Worksheets To An Existing Excel Document

Aug 31, 2007

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

View 3 Replies View Related

SQL Server 2012 :: Syntax Error While Creating Table Dynamically

Apr 19, 2015

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....

View 4 Replies View Related

Dynamically Creating The Data Source Of A Report At Run Time Using Parameters

Feb 13, 2008



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.

View 1 Replies View Related

Transact SQL :: Dynamically Creating And Running A Database Restore Command

Jun 17, 2015

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?

View 8 Replies View Related

Dynamically Creating Excel Worksheets Based On Groupings From Query In SSIS

Apr 24, 2008

Is there anyway to dynamically create Excel Worksheets based on a group identity results from a query?

Thanks!

SkySeek.

View 7 Replies View Related

Remote Connection Refused When Creating ODBC Connection To SQL Server 2005

Aug 30, 2006

When I create a new odbc connection to a SQL server 2005 Db I get a failure telling me dat de SQL server does not allow remote connections.

How can I allow the server to allow this.



Any help appreciated



regards

View 1 Replies View Related

Dynamically Choosing Connection --please Help

Jul 26, 2007

 i have to store some data on a remote sever(MS SQL SERVER2000). The scenario is like 1. The web application runs on a local machine. User (who inputs) uses through LAN.2. The Input should  be stored in the remote server. if the remote connection is ok. otherwise it should be saved in local server's database(MS SQL 2000).3.  In the application's web.config  there is a connection string pointing to the remote server and another one (alternating one) points the local server's database. in scenario like this i first  to tested the remote connection. if it is not ok  then i initialize the local server's connection like thisprivate MyConnection()    {                        try            {                connectionSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnForRemote"].ToString());                connectionSql.Open();            }            catch (Exception ex)            {                connectionSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnForLocal"].ToString());            }            finally            {                connectionSql.Close();            }        connectionSql2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Temp"].ToString());                   }My problem is when the remote connection is lost it takes almost 1 minute to store in local database. how can i make it more time efficient. Thanks....

View 5 Replies View Related

How To Dynamically Change Connection?

Oct 26, 2007

Hi. I have this kind of problem since I am not very conversant with SSIS
and stripting in VB.NET.

The set-up is the following:
Flat File Source -> Script Component -> Flat File Destination

The flat file source looks like this:

NameOfFile
Headers
Data
Data
Data and many more rows of Data
NameOfAnotherFile
Headers
Data
Data
Data and many more rows
NameOfAnotherFile
Headers
Data
Data and so on in the same manner...

My stript looks like that (not very complicated):
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strRow As String = Row.Column0.Trim()
Dim strFolder As String = "Data"
Dim strConn As String
Dim charSep() As Char = {CChar(" ")}

If Mid(strRow.ToLower(), 1, 2) = "d0" Then


' This is the file name, so start a new connection to a new file
' which definitely does not exist yet
strConn = strFolder + strRow + ".csv"

' DestinationFile is the name of the
' Flat File Connection Manager
With Me.Connections.DestinationFile
.ConnectionString = strConn
End With

Else
Row.Column0 = String.Join(",", strRow.Split(charSep, StringSplitOptions.RemoveEmptyEntries))
End If
End Sub

I have several questions regarding this one.

1. Is it ok to change the ConnectionString property of the manager
to redirect the row to a different file? If not, what more to do?

2. If the new destination file does not yet exist, will it be created for me or will
I get an error? If I do, what to do not to?

3. The most important: What steps to take so that a row is discarded
from the pipeline in the script? I want some rows not to be directed
to the file destination. These are the lines that contain the name
of the file into which the data below belongs.

If you can optimize some steps in the script, then please do so by all means.
Thank you for any comments and the knowledge you kindly agree to share with me.
Darek

View 1 Replies View Related

Dynamically Assign Connection String

Feb 28, 2008

I have 3 web identical web apps whose only diff is that they access different SQl Server DB's. I use the SQLDataSource in a number of pages to retrieve data from the db. The apps all use Forms Auth. I would like to be able to see who is the logged on user user and assign the approp connection string to all the SQLDataSources in the app. For example when user UserA logs in they are retrieving data from on db but when UserB logs in they are retrieving data from another db.
I am sure this can be done but could use a little guidance. Thanks in advance.

View 1 Replies View Related

How To Change Dynamically The Connection String.

Nov 15, 2007

Hi,

I am looking to dynamically change the connection string in my SSIS package, to avoid changing the connection string each time I want to run in different environments.

Thanks in advance

View 3 Replies View Related

Dynamically Set The Connection Of SSIS Log Provider For XML

Mar 21, 2007

Sorry for my bad english.


I have an ssis package that contains a DateTime variable named RefDate
its value saved in the package is 8/31/2006. I setted up a Package
configuration for read the value of RefDate from the parent package.I
inserted a script task with message box to verify that the package
configuration works. When I launch the parent package the message box
shows the value of RefDate setted in the parent package. The problem
is that when I use RefDate to dynamically set the connection string of
the log connection of the SSIS log provider for XML files for the
child package, the connection string contains the child value of
RefDate.


Thanks in advance.
Renzo.

View 6 Replies View Related

How To Set Connection Of A SSIS Package Dynamically

Apr 27, 2008



Hi,
I am new to SSIS and i have to develop a ssis package which will run in a production machine through VB.Net(2003) exe.I am facing a problem while setting connection string of SSIS package dynamically.Can anybody help me on this?

View 1 Replies View Related

Configuring The Connection (DataSource) Dynamically

Feb 6, 2008



Hi All,
I need to write a report which will run based on the user's input for Server Group. Meaning, if the user wants the report to be run for one say, UAT group, the report (and therefore the stored procedure) should be executed against UAT server. Or, if the user wants it to be run on the Production machine, the stored procedure should be executed on the Production Server. Assume that both UAT and Production servers are in the same domain to rule out any authentication issue.

I think one way of doing it is to create a linked server on one server which we use to connect via the Report Manager. So, whatever Server Group is selected based on that the SP will be executed on that respective Server Group's Server, since in the query we will have given full names of the tables that we use.

But, I strongly suspect, that having linked servers will not be accepted since it requires that the server owners maintain that. So, I was thinking if there is any other way of achieving this. If anyone knows, please help me with this.

Thanks a lot and let me know if the question is not clearly stated.

Manoj.

View 1 Replies View Related

Dynamically Changing The Connection Properties

Apr 18, 2007

I want to transfer data from one server to another by using SSIS. i want the connection string to be dynamic and also according to the some other variable, the transforming data is changing.

Could you provide me the solution thet how i am able to change my connetction string dynamically and the other variable too.

i am using VS 2003 as front end and SQL server 2005 as a backhand.

Due to VS.NET 2003 i am able to create DTS packages but i have to migrate it and then anly i am able to use it in JOB in SQL server agent of SQL server 2005.

is that any code or any stored procedure from which i am able to migrate DTS packages to SSIS packages.

Thank you

View 4 Replies View Related

Dynamically Create Connection Managers @ Run Time

Jan 28, 2006

Is there a way to dynamically create a connection manager @ run time? I would like to do this from a data set of connection strings so I can link them into a union all component.

View 1 Replies View Related

Altering A Connection Manager Dynamically Via A Variable

Mar 29, 2006

Within an SSIS Package, we are trying to change the connection string of an output file connection manager at runtime (used for package logging).

To do this, we have defined variables with package-level scope and set the connection manager connection string to this variable. The first step of the package is to set these variables. The second step begins the rest of the package operations (moving data). The package executes successfully, but the log file is never created/appended to.

When the package is debugged, I can verify that the variables are being set correctly in the script task and that the variable values are being passed to the connection manager data sources.

Any ideas why this isn€™t working?

View 3 Replies View Related

Dynamically Setting Connection String Depending On Server_name?

Apr 18, 2008

I need to make my site aware of which server_name it is loading from so it uses a different connection string. (have dev + prod servers for web/sql)Currently my connection string is in web.config as follows:  <connectionStrings>      <!-- Development and Staging connection string -->          <add name="myconnection" connectionString="server=myserver; user id=mysuer; password=mypassword; database=mydatabase" />   </connectionStrings> I need to make sure the 'name' is the same for both connection strings since that is how the rest of my site looks for it.  However, I'm not sure how to get both in here with some sort of 'if/then' statement to determine which one to use.I've heard it could be done in global.asax with something similar to the code below, but I dont know how to assign a 'name' to a connection string for that type of setup. Sub
Session_OnStart  ServerName =
UCase(Request.ServerVariables("SERVER_NAME"))  IF ServerName = "prod.server.com" THEN   ...Set Prd string...  ELSE   ...Set Dev string...  END IF  End
Sub

View 8 Replies View Related

Can't I Dynamically Read From Any File? - Connection Manager Does Not Exist

Feb 23, 2007

I have a Parent package and a child package.

I have hard coded the value for the User::FilePath variable in the parent package.

I am mapping this variable to the value of the same variable in the Child Package.

I created a directory and sql file: "C: empsqlb.sql". I have verified that the path variable value is passed to the child package by using a Script task with a messgbox call.

How do I define an execute sql task to execute sql file: @[User::FilePath] & "sqlb.sql". I'm using this expression for the SqlStatementSource property. I have entered the OLEDB server information and specified the SQLSourceType = fileconnection.

However I get the error:

[Execute SQL Task] Error: Connection manager "D:
lewisdevZsqlb.sql" does not exist.

What am I doing wrong?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved