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.
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?
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,
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.
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?
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!
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?
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
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
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.
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.
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).
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.
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.
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.
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....
How to create a sql statement that dynamically matches a database name in a stored proc. Somthing like this: Select Table_name From Information_schema.Tables Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0 and table_name like 'Item%Master' and (DATABASENAME=????????????????)
I have to write a large data migration script to move data from one SQL Server database to another. Is there any way to dynamically specify the server and database name? I would like to do something like the following, (but this does not seem to work):
Delete From [@ServerName].[@ImportToDatabase].[MyTable]
A user is wanting to run a query against the current 3 active databases. These change every month on the 1st e.g. Test-06-14. Therefore I want to set up a dynamic query which will always use the current database. I believe I am almost there but I cannot set the USE @DatabaseName dynamically yet.
I need to call another database in the same server dynamically; creating a varible off it or somehow. Example:
-- In database DBaseA declare @dbname as varchar (30) set @dbname='[DBaseB].[dbo]' select * from @dbname.[table2]
I know this block of code doesnt work. And i need a way of making the database DBaseB dynamic. DBaseA and DBaseB are in the same server. I am using Sql Server 2000. How do i solve this? Solutions ?
I have a need to create a database, and then populate it. However, thecode below doesn't work as I hoped it might (it creates the table inthe "master" database, which is Not A Good Thing). I know already(thanks Tony!) that if you use Dynamic SQL for the USE command, thenthe subsequent operations need to be Dynamic SQL as well, which is apity since there are over 11,000 lines of it and I don't really fancydebugging it!Does anyone have a cunning plan? In a nutshell, I would like to beable to:1. Create a new database with a derived name - as in the case below aname based on the year, though it might be month as well.2. Create and populate tables in this new database.These operations would ideally be running from a scheduled job.Any thoughts?TIAEdward====================================USE MASTERDECLARE @DBName VARCHAR(123)SET @DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)if not exists(select dbid from master.dbo.sysdatabases where name =@DBName)exec('CREATE DATABASE ' + @DBName)elseraiserror('Database already exists.',3, 1)EXEC ('USE ' + @DBName)if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[TestTable]') and OBJECTPROPERTY(id, N'IsUserTable')= 1)drop table [dbo].[TestTable]GOCREATE TABLE [dbo].[TestTable] ([TestID] [int] NOT NULL ,[Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL) ON [PRIMARY]GO
I have the folowing databases DB1,DB2,DB3,D4,DB5........ I have to loop through each of the databases and find out if the database has a tablename with the word 'Documents'( like 'tbdocuments' or 'tbemployeedocuments' and so on......) If the tablename having the word 'Documents' is found in that database i have to add a column named 'IsValid varchar(100)' against that table in that database and there can be more than 1 'Documents' table in a database. can someone show me the script to do it? Thanks.
I need to create a MSRS 2005 report which needs to connect to differnt databases at runtime.
1. Can we create a report that connects to different databases at runtime and generate a report. 2. Is there any other way to implement the same using .NET 2.0.
I am using Sql Server 2000. I have about 25+ databases . I want to run a series of commands on each database... how can I change the database context - the current database - dynamically in a loop... something like this below:
declare @SQLString Nvarchar(1000)
declare @DBName Nvarchar(100)
declare @SQL2 NVARCHAR(1000)
declare @TABLE_NAME NVarchar(100)
While @@FETCH_STATUS=0
Begin
SET @SQL2 = 'USE ' + @DB_NAME + ';GO;ALTER TABLE ' + @TABLE_NAME + ' MODIFY Name varchar(100)'
PRINT @SQL2
EXECUTE SP_EXECUTESQL @SQL2
End
I am getting an error when i run the above commands: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'GO'
You have a store procedure and inside you make Updates or Inserts against another Server. But that name can change and I dont want to change it manually in everyplace.
Per example:
SELECT * FROM Himalaya.DBName.dbo.tblUserGroup WHERE fldUserID = 7300
I have several Selects, Updates and Inserts pointing to that server.
What I can do if I want to change from Himalaya server to another server?
I am writing Stored Procedures on our SQL 2005 server that will link with data from an external SQL 2000 server. I have the linked server set up properly, and I have the Stored Procedures working properly. My problem is that to get this to work I am hardcoding the server.database names. I need to know how to dynamically specify the server.database so that when I go live I don't have to recompile all of my stored procedures with the production server and database name. Does anyone have any idea how to do this?
EXAMPLE:
SELECT field1, field2 FROM mytable LEFT OUTER JOIN otherserver.otherdatabase.dbo.othertable
OBJECTIVE:
Replace 'otherserver.otherdatabase.dbo.othertable' with some other process (dbo.fnGetTable('dbo.othertable')????)
I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them?
