T-SQL (SS2K8) :: Dynamically Set Database
Jun 26, 2014
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.
DECLARE @DB_Name varchar(100)
DECLARE @DatabaseName varchar(100)
DECLARE @Command nvarchar(200)
DECLARE @Command2 nvarchar(200)
DECLARE database_cursor CURSOR FOR
[Code] ....
View 9 Replies
ADVERTISEMENT
Sep 5, 2014
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.
View 2 Replies
View Related
Aug 28, 2014
I have an SSIS package that puts together a series of queries to check all text fields in all tables of a database for a set of "invalid" characters. I know the root query works as we have been running it manually for quite a while. However, now that I have changed it to build dynamically, I cannot figure out why it does not work.
Here is the code. The EXEC (@sSQL) returns 3 records. It just so happens that these 3 records are the only ones in the database with a '?' in the field. The very bottom statement returns 0 records. I cannot figure out what the difference is. When I do PRINT (@sSQL), it looks fine to me, except that @Pattern is fully printed.
DECLARE @Pattern NVARCHAR(MAX)
DECLARE @sSQL NVARCHAR(MAX)
DECLARE @1 nvarchar(max)
DECLARE @2 nvarchar(max)
DECLARE @loop int
[code].....
View 9 Replies
View Related
Sep 2, 2014
I have table of standardized data that I'm merging into from an Import table.
This import table may have the [ContactName]. I one row in the import table has the contact name then every row would have the contact name.
What I'd like to be able to do is something like this in the UPDATE portion of the Merge (I don't care about the insert phase, since I can insert a null [ContactName] since I'm not overwritting an existing [ContactName]
MERGE INTO [dbo].[Standardized] AS [target]
USING [dbo].[ImportDestination]
AS [Source]
ON [Source].[Key] = [Target].[Key]
WHEN MATCHED
THEN UPDATE
SET[ContactName] = CASE WHEN source.[ContactName] IS NOT NULL THEN source.[ContactName] else target.[ContactName];
Is anything like that possible and if the code above works it would only work on a row by row bases. Is it possible to figure out if any [ContactName] has data and if so possibly overwrite an old [ContactName] with a null?
View 1 Replies
View Related
Nov 9, 2014
I have created dynamic sql to declare variables based on columns from the table and i set values to those variable now here is the issue . i want to check the variable values how do i do that dynamically
drop table test
create table test
(
id varchar(10) not null,
col1 varchar(10) ,
col2 varchar(10)
[Code] .....
Now my next step is verify if the variable is blank or not how do i do that ?
How do i verify all of the columns one after the other .
I am after the statement like this dynamically
-- IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' '
--IF NOT (@col2 = '') THEN set @SQL = @SQL + '[col2] = ' + @col2 + ' '
I need to check if the columns are blank or not dynamically as i do not want to hard code the column names there.
View 4 Replies
View Related
May 21, 2015
I have two tables:
tblServer
tblInstance
tblServer represents a server, tblInstance represents any SQL Server instances present on the server. They're related thru srvID.
I'd like to write a query that gives me servers with all instances, on one row. This dataset will populate a List in an SSRS report.
I have this but it's clunky and does not provide for more than 2 instances on the server. I'd like this to account for any number of instances all on one row without having to hard code multiple joins:
with serverInfo as
(
select
srv.srvType as Type
,srv.srvName as ServerName
,srv.srvIP as ServerIP
,ins.instNetName as InstanceNetName
[Code] .....
View 3 Replies
View Related
May 27, 2015
UPDATE Report
SET MSYear= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012'
when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013'
when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014'
when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015'
when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016'
when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017'
End
Actually our business year starts from 1st july and for this code I need function which is dynamically updates the every year for example 2014-07-01 to 2015-06-30 this is called as a 2015 year like this I need function which will dynamically update a year.
View 4 Replies
View Related
May 19, 2015
DELETE FROM Report_temp2
WHERE MSSalesID in
( Select Report_temp.MSSalesID FROM Report_temp)
DELETE FROM Report_temp
WHEREMSDate < '2015-07-01'
Actually the year stating form july.
Q1 is july,aug,sep.
Q2 is oct nov,dec.
Q3 is jan,feb,mar.
Q4 is april, may,june.
So what I need is dynamically I want to delete the data every year prior to current year.
View 4 Replies
View Related
May 27, 2015
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @CIR AS tmp On t.RowId = tmp.Max_RowId
[Code] ....
But CASE statement is not working...
View 7 Replies
View Related
Feb 27, 2014
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
View 6 Replies
View Related
Aug 13, 2007
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=????????????????)
View 6 Replies
View Related
Aug 13, 2007
I need an sql statement that dynamically matches a database name in a stored proc. Here is my attempt
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%'
and (DATABASENAME=????????????????)
Can someone help me out?
Thanks.
View 4 Replies
View Related
Jun 21, 2004
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]
Any help appreciated
View 2 Replies
View Related
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
Jun 2, 2006
Hey all.
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 ?
View 1 Replies
View Related
Nov 2, 2006
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
View 3 Replies
View Related
Oct 3, 2007
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.
View 6 Replies
View Related
Oct 1, 2007
Hi,
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.
Thanks in advance.
Regards,
Rajeev
View 5 Replies
View Related
Oct 4, 2007
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'
Can someone give me the correct solution?
View 4 Replies
View Related
Jun 8, 2006
To be clear:
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?
The same with the Database Name.
Thank you
View 6 Replies
View Related
Feb 28, 2007
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')????)
Thanks for any help
View 1 Replies
View Related
Sep 22, 2006
Hello,
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?
something like
Declare @MyDatabaseName varchar(30)
set @MyDatabaseName = "MyDB"
SELECT * from MyDatabaseName.dbo.MyTable
Any suggestion? Please.
Thanks in advance
View 8 Replies
View Related
Sep 24, 2006
Is there a way I can write a query to dynamically select a database table. That is, instead of having a variable for a certain column like customerId which would be €œcustomerID = @customerID€? I want to use the variable to select the table. I assume I may have to use a stored procedure but I am unclear as to how this would be coded.
Thanks
View 1 Replies
View Related
Nov 16, 2006
Hello Friends,
I have a problem with ASP.net with dynamic data transfer from asp page to microsoft sql server 2000. For example , I have asp
web page with one text field and a buttion.
When I click the buttion, the value entered in the text field should be transfered from the text field to database.
Kindly See the following section c# code .....
SqlConnection objconnect = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = objconnect;
cmd.CommandText = " select * from Table_Age where Age = @Age";
cmd.CommandType = CommandType.Text;
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@Age";
parameter.SqlDbType = SqlDbType.VarChar;
parameter.Direction = ParameterDirection.Output;
parameter.Value = TextBox1.Text;
objconnect.Open(); // Add the parameter to the Parameters collection.
cmd.Parameters.Add(parameter);
SqlDataReader reader = cmd.ExecuteReader();
********************this section c# code is entered under the button control************************************
connection string is mentioned in the web.config file.
In the above c# code , I have a text field , where I entered the age , the value entered in the text field should be sent to
database. I have used SqlParameter for selecting the type of data should be sent from ASP.NET 2.0 to the microsoft sql server 2000. I am facing a problem
where I am unable to sent the random datas from a text field to the database server.
I have a created a database file in the microsoft server 2000.after the excution of the fIeld value is assinged to NULL in the main database i.e microsoft the sql server 2000.Can anyone help with this issue.My mail id phijop@hotmail.com- PHIJO MATHEW PHILIP.
View 1 Replies
View Related
Aug 6, 2015
I am trying to dynamically create the connection to a database within an SSIS package.
the requirement is to allow the user to pass through the database as a variable and that variable will dynamically create the connection string in the connection manager.
Is this possible, if so how?
View 0 Replies
View Related
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
Jul 31, 2015
I need to create a DB dynamically in the Server from the SSIS only....
How to implement this....
View 4 Replies
View Related
Jul 24, 2015
I have three tables in data base:
customer
product
sales
And i want to use SSIS package dynamically load data from database into three separate flat file, each table into each file.
I know i got to use for each loop task ADO.Net schema row set enumerator, with OLEDB connection manager, select table name or view name variable from access mode list, but the problem comes, as table name is dynamic then flat file connection is also dynamic, i am using visual studio 2013...
View 5 Replies
View Related
Jan 4, 2012
Is there a Microsoft published document for SQL database coding standards and guidelines/T-SQL coding standards available??
View 7 Replies
View Related
Apr 9, 2013
How to get the initial size of the database file using T-SQL.
sys.master_files, sys.database_files, sysfiles, sysaltfiles --> gives only the current size and not the initial size.
View 4 Replies
View Related
May 16, 2014
I am trying to modify a script that to back databsae in t-sql.I put it in a stored procedure with one paramenter dbname.I would like if a dbname is passed when calling the sproc, it will only backup this database, if no paramenter is entered, the dbname is null, then backup all user databases- no system databases.But I have difficulty to define this two situations in code.
Below is the sproc:
CREATE PROCEDURE [dbo].[BackupDB]
@dbname varchar(50)
AS
BEGIN
[code]...
View 1 Replies
View Related
May 19, 2014
How to merge the data from one database to another if we have identity column on both the database. If we are merging two companies,we need employee table of 2 database and insert them into first database and corresponding fkey tables say some 7 tables.how to merge if the table is having identity column.
DatabaseA has 7 Tables
Namely T1,T2,....T7
DatabaseB has 7 Tables
Namely T1,T2,....T7
T1 is master
T2 is Child
DatabaseA
T1
|
----------------
| |
T2 T3
| |
----------------- ---------------
|| | |
T4T5 T6 T7
DatabaseB
T1
|
----------------
| |
T2 T3
| |
----------------- ---------------
|| | |
T4T5 T6 T7
All the tables have interrelationship as shown pkey and Fkey
All the T1...T7 have Pkey with identity column starting from 1 and corresponding Fkey column in their child tables
Database A Table information Rows
T1-10000
T2-5000
T3-5000
T4-5000
T5-5000
T6-5000
T7-5000
Database B Table information Rows
T1-20000
T2-10000
T3-10000
T4-10000
T5-10000
T6-10000
T7-10000
Now i want to merge all the data from Database B to DatabaseA
How can i merge since in DatabaseA id for T1 starts from 1,2,3,4...and so on...
DatabaseB id for T1 also starts from 1,2,3,4...and so on...
and the fkey tables also have same 1,2,3,4...and so on... with reference of parent table
View 3 Replies
View Related
Sep 24, 2014
I need to create a job that import an access database.
Does exist a command that I can use to do that?
View 6 Replies
View Related
