Gererating Dynamic Queries During Run Time
Jun 26, 2007How to Gererating Dynamic Queries During Run Time and execute the results
Thanks in advance
Suresh
ADVERTISEMENT
Dynamic Queries
Dec 8, 2006Hi,
This question has been asked probably million times, but it sems that I cannod find right answer on search engines.
I need to send parameters to my stored procedure, but not only parameters. For example in where clause I have something like:
where myID = 12
I need to be able to filter results on myID, but one time I need it to be eqal to 12 and other time I need it to be different (<>) from 12. Some time I even need to add another condition like myID2 = 1. Can I solve this without additional procedures?
I believe that I saw solution in 3-Tier ArchitectureTutorial Series few weeks ago but it seems that something changed, and I cannot find it anymore. Can anyone help?
SUM Of Dynamic Queries
Mar 29, 2005I am trying to get the sum of two dynamic queriesThat is:EXEC('SELECT COUNT(id) as subtotal1...) + EXEC('SELECT COUNT(id) as subtotal2...)If I assign to a variable, say @total, I get errors or NULLs. Is there a workaround?Thanks in advance for your comments
View 1 Replies View RelatedDynamic Queries
Mar 19, 2007Hi,
I have a parameter in the url to the report. According to that parameter, I need to change the query. For example, url = http://localhost/reportserver?param1=A
if Param1=A, then I need to use query1 for the report
if param1 = B, then I need to use query2 for the report.
I would like to know if I can do this. If not, is there any other way to build query dynamically in the report.
thanks
Running Dynamic Queries
Jan 1, 2006I'm creating a search function right now and I'm running into a problem where my search function doesn't know what fields the user will search for, and therefore doesn't know whether or not to put quote marks around specific values or not.
For example, somebody might search for somebody else with year of birth in which case I might have a query:
SELECT userid FROM users WHERE yob = 1970
but somebody else might search for a name, in which case I need
SELECT userid FROM users WHERE first_name = 'Andrew'
or somebody else might search for both and need
SELECT userid FROM users WHERE yob = 1970 AND first_name = 'Andrew'
I'm accomplishing this by having the function (this is in PHP) take an array as an argument, where the key is the MySQL column and the value is the required value for that column. So in the 3rd example, it would be a 2-item array:
yob => 1970
first_name => 'Andrew'
And then cycling through that and dynamically creating a MySQL query based on the received array.
So... my problem is finding a way to specify when quote marks are required and when they're not. Is there any way to just have MySQL default to the format of the column? Also, if anybody thinks this isn't the right way to create a search function, let me know because I'm new at this .
Thanks!
PS: Right now what I'm doing is I'm creating arrays that include names of columns that do and don't need quote marks. Then in construcing the MySQL statement I'm checking to see which array a column is in, and making the quote decision based on that.
Best Way To Construct Dynamic Queries
Apr 17, 2008I am working with a form that I wish to construct a dynamic query from the results of. The forum has a date range and two radio buttons. Each radio button enables a list of items that can be clicked. So for example, if we assume the question,
"What is your favorite food, and what toppings do you like on it?" where the radio buttons are foods, the list boxes are toppings. Assuming the user can choose a Hamburger or a Salad with generic toppings, their choices are as such:
They can choose a Hamburger, with every topping
They can choose a Hamburger with a single topping.
They can choose a Hamburger with multiple toppings.
They can choose a Salad with the same combinations as above.
They cannot choose both a Hamburger and a Salad - mutually exclusive items.
Then, I wish to construct a query that, based on the conditions above, retrieves information relavent to their criteria, such a the number of food items to choose from, their price, etc. - basic information. What is the most efficient way to do this? Should I write a stored procedure with numerous conditionals and all available parameters, constructing the sproc as such:
Code Snippet
CREATE PROCEDURE GetFoodInfo
@from datetime,
@to datetime,
@FoodType varchar(20),
@toppings varchar(20)
AS
BEGIN
DECLARE @query varchar(300)
SET @query = 'SELECT COUNT(DISTINCT ' + @FoodType + ') '
IF @FoodType = 'Hamburger'
SET @query = @query + 'FROM Hamburgers '
ELSE
SET @query = @query + 'FROM Salads '
IF @toppings <> 'ALL'
SET @query = @query ' WHERE Toppings = ' + @toppings
EXEC (@query)
Apologies of this syntax is incorrect, but you get the general idea. Of course, this is a small example - in reality, I would have 5-10 conditional requirements Or, should I generate a stored procedure (or simple query) for each operation? For example, assuming each is a stored procedure:
GetHamburgers <-- would get Hamburgers with all toppings
GetHamburgersWithToppings
GetSalads
GetSaladsWithToppings
What is the best method for what I wish to achieve? What is fastest? Is there a better way than I have listed? Thank you.
Again, this is a small example, but I hope someone can help.
Select Into Using Dynamic Queries
May 5, 2008I need to create a temporary table using dynamic queries and then i have to use the temporary table for data manipulatuion.
Can someone help me out on this.
EG
sp_executesql N'Select top 1 * into #tmp from table1'
select * from #tmp
Help Using Dynamic Queries In Temp Table
Nov 20, 2006The dynamic sql is used for link server. Can someone help. Im getting an error
CREATE PROCEDURE GSCLink
( @LinkCompany nvarchar(50), @Page int, @RecsPerPage int )
AS
SET NOCOUNT ON
--Create temp table
CREATE TABLE #TempTable
( ID int IDENTITY, Company nvarchar(50), AcctID int, IsActive bit )
INSERT INTO #TempTable (Name, AccountID, Active)
--dynamic sql
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT a.Name, a.AccountID, a.Active
FROM CRMSBALINK.' + @LinkCompany + '.dbo.AccountTable a
LEFT OUTER JOIN CRM2OA.dbo.GSCCustomer b
ON a.AccountID = b.oaAccountID
WHERE oaAccountID IS NULL
ORDER BY Name ASC'
EXEC sp_executesql @sql
--Find out the first and last record
DECLARE @FirstRec int
DECLARE @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
--Return the set of paged records, plus an indication of more records or not
SELECT *, (SELECT COUNT(*) FROM #TempTable TI WHERE TI.ID >= @LastRec) AS MoreRecords
FROM #TempTable
WHERE ID > @FirstRec AND ID < @LastRec
Error:
Msg 156, Level 15, State 1, Procedure GSCLink, Line 22
Incorrect syntax near the keyword 'DECLARE'.
Help In Writing Queries For The Dynamic Values
Mar 18, 2008Hello, I really have a problem writing queries for the dynamic values. i follow the below mentioned method to write the queries but its really confusing. ex: str = "SELECT SO.Description,SO.DiscountPct,SO.Type,SO.Category,SO.StartDate,SO.EndDate,SO.MinQty,SO.MaxQty," + "S.Name AS ProductSubCategory,P.Name AS ProductName, C.Name AS ProductCategory FROM Production.Product P " + "INNER JOIN Production.ProductSubcategory S ON P.ProductSubcategoryID = S.ProductSubcategoryID " + "INNER JOIN Production.ProductCategory C ON S.ProductCategoryID = C.ProductCategoryID " + "INNER JOIN Sales.SpecialOfferProduct SOP ON P.ProductID = SOP.ProductID " + "INNER JOIN Sales.SpecialOffer SO ON SOP.SpecialOfferID = SO.SpecialOfferID " + "WHERE '" + txtStartDate.Text + "' between SO.StartDate AND SO.EndDate AND '" + txtEndDate.Text + "' BETWEEN SO.StartDate AND SO.EndDate " + "AND SO.Description Like '" + txtSpecialDesc.Text + "%'"; can anybody help me in writing the queries for dynamic values in an easy way. Thank you Sandeep Chavva
View 3 Replies View RelatedHelp With Stored Procedures / Dynamic Queries
Jun 12, 2006Hello, I'm trying to create a Stored Procedure who receives the table name as a parameter, then uses a cursor to obtain every column name and then builds a string like SELECT col1, col2, ... from TABLE
In fact that would be the same as SELECT * FROM table; but I can't do this, because I'll be using this stored procedure to loop through many tables that has different quantity of columns with a DTS, and if a specify the *, then the DTS wouldn't let me do the select with tables with different quantity of fields.
Could you help me please, because my code isn't working:
CREATE PROCEDURE dbo.stp_Test
(
@tablename AS VARCHAR(50)
)
AS
DECLARE @columnname varchar(50)
DECLARE @strsql Nvarchar(500)
DECLARE @query varchar(4000)
SET NOCOUNT ON
DECLARE c1 CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE table_name = @tablename
OPEN c1
FETCH NEXT FROM c1 INTO @columnname
WHILE @@fetch_status = 0
BEGIN
IF (@strsql is null)
BEGIN
SET @strsql=@columnname
END
ELSE
BEGIN
SET @strsql = @strsql + ',' + @columnname
END
FETCH NEXT FROM c1 INTO @columnname
END
CLOSE c1
DEALLOCATE c1
SELECT @query = 'SELECT ' + @strsql + ' FROM ' + @tablename
EXEC @query
SET NOCOUNT OFF
GO
Problem With Dynamic Queries In SQL Tasks
Mar 19, 2008Hello, i have dynamic queries for example:
DECLARE @query nvarchar(250);
DECLARE @table nvarchar(100);
SET @table = 'table_name';
SET @query=
N'update '+@table+'
set column = 0
where column is null';
EXECUTE sp_executesql @query
SET @query=
N'update '+@table+'
set column2 = 0
where column2 is null';
EXECUTE sp_executesql @query
It is very simple example. My question is: it is possible somehow put these queries in SQL Tasks?
Because if i do like this i get error "The Declare cursor SQL construct or statement is not supported."
Maybe there is other way to solve my problem? Or maybe syntax should be changed?
Thanks for advice
Using Stored Procedures Are You Safe From Sql Injection If Your Not Using Dynamic Queries ?
Mar 12, 2008Im reviewing my stored procedures for a new application and got to thinking about protecting against sql injection. I think im pretty safe since im using stored procedures and none of them use any 'exec' commands within them, but im not sure.
I was reading this article, and again all the examples that list a stored procedure, have an 'exec' command somewhere that is the culprit. So, in my case lets say I was doing something like this:
Im generally using regularexpression validation controls on the client side of the application and limiting the max length of the input there as well.
Am I safe, or do I need further input checking within the procedure ?
Code Snippet
CREATE PROCEDURE [dbo].[get_Uploads]
@app varchar(50)
--Init variables
SET @error_number = 0
BEGIN TRY
SELECT [Logid],[Filename],[Label],[UploadDate],[App]
FROM UploadLog au
WHERE [App]=@app
END TRY
BEGIN CATCH
SET @error_number = -2
END CATCH
Dynamic Queries With Data Reader Source Adapter
Apr 16, 2008I am a business user trying to build an incremental ETL package with SSIS. I have a working prototype on SQL Server 2005 where I select the max(ID) from the last successful run and pass that value into a variable. Then, in my Data Flow step, I select an OLE DB source adapter and use this variable in a custom select statement.
Here's my challenge....the live data is actually in a Postgres DB so I have to use a Data Reader Source adapter. When I try to pass my variable to this adapter the job bombs out. Does anyone know how to dynamically update the query text inside a Data Reader source adapter using variables or otherwise?
SQL Queries Time Out During Backup
Aug 2, 2006Hello, I am new to the forum and hopefully someone can help me with the
problem I have. Lately, I noticed that my sql statements are timing out
during the time SQL server is running backup job. The errors I am
seeing in the log are:
Process 59:0 (724) UMS Context 0x06403BC0 appears to be non-yielding on Scheduler 1.
Error: 17883, Severity: 1, State: 0
I am running SQL Server 2000, Enterprise (SP4)
I believe this problem should have been fixed in SP3a. I even tried
rolling back to SP3a, but still experience the same problem. Does
anyone else have experienced this issue?
Thanks.
Run-time Changeable Queries
Jun 30, 2006I need to extract and store a value from a table (or from a MS Access file with OpenDataSource) which is not always the same and it is therefore stored in the @openfile variable. Something like this:
...
declare @standardselect nvarchar(4000)
declare @value int
select @standardSelect='select top 1 @value=val from ' + @openfile
exec (@standardSelect)
...
It obviously doesn't work because the variable @value is not declared within the sql string.
However, since @openfile is always different, I need to pass it through a string and the only way I know is within a variable. If I declare @value inside the @standardselect it is not accessible to the rest of the procedure, which is not acceptable for me.
Any suggestions?
DMX Queries With MS Time Series
May 23, 2006Hi!
I have a table Month_Sales(Month, product_1, .., product_n). The value of column product_i is the sale in this month.
so when i build MS Time Series for this domain, i want to query to find top m product is seld most in next month??
How do i buid that query???
Most Time Consuming SQL Queries
Oct 19, 2006What are the types of queries that are most time consuming...which may easily lead to expired time out etc...thanks
Regards
Alu
Queries Against Linked Server Time Out?
May 15, 2008Ok, this is a little convoluted so bear with me.
I have a SQL 2000 SP4 box on Windows 2000 Server with the Oracle 11g Client installed.
I've created a linked server to an Oracle database that lives offsite (sites connected with a fairly high speed VPN link).
There's a view on the Oracle database I'm trying to access through the linked server using the four-part naming convention*.
If I execute a fairly simple query against that view, the query runs until it eventually times out two hours later. And by simple, I mean something like this:
select * from CGN_LMS..CGNLMS.V_PRODUCTION_SUM where FISCAL_PERIOD = 200803
That happens if I use the OLE DB provider from Oracle or the one from MS.
Now, if I run the same type of query against that Oracle DB from Excel or Access (using the ODBC driver), the query will complete in seconds, returning about 800 rows of data.
For fun, I created a blank Access database with one linked table that points to that V_PRODUCTION_SUM view on the Oracle database, and one Query within that Access database that SELECTs * FROM the linked table WHERE FISCAL_PERIOD=200803.
I then created a linked server on my SQL 2000 server that points to the Access database. Now, if I hit that saved Access query through the SQL 2000 linked server, it also returns the results in about 9 seconds. If I query the linked table in the Access database and specify WHERE FISCAL_PERIOD = 200803, it completes in about 6-8 minutes.
So I guess my question is... WTF?
How is it that this:
SQL 2000 ->OLE DB provider-> Oracle
Times out after 2 hours, while this:
SQL 2000 -> Jet driver-> Access -> ODBC driver -> Oracle
returns all my results in seconds/minutes?
Couple other side notes...
A lot of the queries that I test with using this linked server work just fine. In fact, with this particular query, if I were to just pull SELECT TOP 800 * with no WHERE clause, the query would run rather quickly (like in 4 seconds).
Also, if I use OpenQuery instead of the 4-part linked server name, the queries return results as quickly as I'd hope. That might work as a last resort, but we have existing applications that use the 4-part names already and we'd rather not have to suss out and change all those if we don't have to.
Any help or insight would definitely be appreciated.
Justin
Queries Take A Long Time After Sp2 Installation
Jun 5, 2007Has anyone had thsi issue, if so what was the fix?
We installed service pack2, the day after 2 of our production jobs started taking a long time to complete and causing a ton of blocking.
it went from running in 2 minutes to now taking 3 hours and 29 minutes to run. Can someone help?
Dynamic Report Name At Run Time?
Sep 19, 2007Hi Friends,
Is it possible to give a name at run time to a report when we try to download it in any format.
Do we have any control over the report name for e.g. the report name can be passed at parameter value.
Thanks,
Novin
Dynamic Time On Columns With Reporting Services
May 17, 2005Hi all!
If I have some mdx I'm using in reporting services like this:
select
{ [TimeByMinute].[All TimeByMinute].[2005].[May].[1] : [TimeByMinute].[All TimeByMinute].[2005].[May].[6] } on columns,
{A_list_of_measures } on rows
from ACD_Calls
The column names are unique to the day of month- which means when I use a table to display this in reporting services, the field names change dynamically when the date parameters change which means the table stops working.
I'll post this in reporting services too but I thought maybe I could alias the column names in mdx shielding the reporting services table from changes in dates.
What do you think? Would a matrix be more flexible in this case?
Richard
Dynamic T-SQL Query Creates Table At Run Time.
Aug 1, 2007The following are the output lines from my code which is constructing some T-SQL queries on the fly. The query highlighted in yellow is the problem query. The code upto the problem query is working correctly and I am able to see the output tables in the Query Analyzer
STEP 1 : Create 3 Tables In Dynamic T-SQL : Showing 3 Strings from Exec statement
Create Table ##Test_word28July2007185548990201 ( t float, e float, s float, word varchar(80) )
Create Table ##OUT_Test_word28July2007185548990201 ( t float, e float, s float, word varchar(80), KeywordID int, rank float )
Create Table ##STVR_FLOAT_Test_word28July2007185548990201 ( var_val float)
Step 2: Retrieving A Value Into Another Global Table (table has only 1 row and 1 column)
Insert Into ##STVR_FLOAT_Test_word28July2007185548990201 ( var_val ) Select IsNull( t , 0 ) from ##Test_word28July2007185548990201
(1 row(s) affected)
Step 3: I need the value (var_val) in ##STVR_FLOAT_Test_word28July2007185548990201
Update ##Out_Test_word28July2007194827580759 Set t = Select var_val from ##STVR_FLOAT_Test_word28July2007194827580759 where t > Select var_val from ##STVR_FLOAT_Test_word28July2007194827580759
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Select'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Select'.
(1 row(s) affected)
Problem Definition
Part 1
The update query is trying to retrieve a value in a dynamically constructed table. ##STVR_FLOAT_Test_word28July2007185548990201 (1 column var_val , 1 row)
Update <Table_Name> set t = @var_val where t > @var_val
The update query is simple, except I need to "SELECT" @var_val from the dynamic table
The @var_val is a float value.
I have outputted the contents of the table that is holding the @temp_val variable
It has the correct value and the table has only 1 row and 1 column.
WHAT IS THE SYNTAX OF A QUERY TO SELECT A SINGLE VALUE FROM A TEMP TABLE WITH ONLY 1 ROW OR COLUMN AND USE THE SELECTED VALUE AS A VARIABLE IN AN UPDATE STATEMENT
Dynamic Data Set Parameters During Report Run Time
May 25, 2007I need to pass the out put from a stored procedure as an input parameter to another stored procedure. I created a data set for each stored procedure. Fron the second data set parameter tab, I added a parameter to refer to the field from the first data set.
I get the error Fiels can not be used in query parameter expressions.
I tried two simple queries instead of stored procedures with first query feeding the second query. I get the same error. Is there any other other way to accomplish this?
Thank You.
ODBC Bcp_init Run Time Dynamic Link Library
Aug 9, 2007Hi,
I'm having problems successfully executing bcp_init through a function pointer retrieved at run time from sqlncli.dll.
The call works when the library is statically linked.
Can someone please point out the problem? The commented out code in green executes successfully when the library is statically linked. Code marked in red fails.
SQLGetDiagRecW does not provide any useful information.
Any help is appreciated.
Regards,
Venkat
typedef SQLRETURN (__stdcall *SQLALLOCHANDLEPROC)
(
SQLSMALLINT HandleType,
SQLHANDLE InputHandle,
SQLHANDLE OutputHandle
);
/// typedef native function SQLSetEnvAttr
typedef SQLRETURN (__stdcall *SQLSETENVATTRPROC)
(
SQLHANDLE EnvironmentHandle,
SQLINTEGER Attribute,
SQLPOINTER Value,
SQLINTEGER StringLength
);
/// typedef native function SQLSetConnectAttrW
typedef SQLRETURN ( __stdcall * SQLSETCONNECTATTRPROCW )
(
SQLHANDLE ConnectionHandle,
SQLINTEGER Attribute,
SQLPOINTER ValuePtr,
SQLINTEGER StringLength
);
typedef SQLRETURN ( __stdcall *SQLCONNECTW )
(
SQLHANDLE ConnectionHandle,
SQLWCHAR * ServerName,
SQLSMALLINT NameLength1,
SQLWCHAR * UserName,
SQLSMALLINT NameLength2,
SQLWCHAR * Authentication,
SQLSMALLINT NameLength3
);
typedef RETCODE ( __stdcall *BCPINIT )
(
SQLHANDLE ConnectionHandle,
const WCHAR* TableName,
const WCHAR* DataFileNae,
const WCHAR* ErrorFileName,
SQLINTEGER Direction
);
main()
{
// Load library and initialize function pointers.
HMODULE hModule = ::LoadLibrary(_T("SQLNCLI.dll"));
SQLALLOCHANDLEPROC fnAlloc = (SQLALLOCHANDLEPROC)::GetProcAddress( hModule, _T("SQLAllocHandle") );
SQLSETENVATTRPROC fnEnvAttSet = (SQLSETENVATTRPROC)::GetProcAddress( hModule, _T("SQLSetEnvAttr") );
SQLSETCONNECTATTRPROCW fnConnectAttSet = (SQLSETCONNECTATTRPROCW)::GetProcAddress( hModule, _T("SQLSetConnectAttrW") );;
SQLCONNECTW fnConnectProc = (SQLCONNECTW)::GetProcAddress( hModule, _T("SQLConnectW") );
BCPINIT fnBCPInit = (BCPINIT)::GetProcAddress( hModule, _T("bcp_initW") );
// Allocate ODBC handles.
SQLHENV m_SQLEnvironment;
SQLHDBC m_SQLConnection;
SQLHSTMT m_SQLStatement;
SQLRETURN iReturn;
iReturn = fnAlloc( SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment);
//iReturn = SQLAllocHandle( SQL_HANDLE_ENV,SQL_NULL_HANDLE,&m_SQLEnvironment);
iReturn = fnEnvAttSet(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);
//iReturn = SQLSetEnvAttr(m_SQLEnvironment,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0);
iReturn = fnAlloc(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection);
//iReturn = SQLAllocHandle(SQL_HANDLE_DBC,m_SQLEnvironment,&m_SQLConnection);
// Set up bulk copy.
int nType = SQL_COPT_SS_BCP;
SQLPOINTER pvAttribute = (SQLPOINTER)SQL_BCP_ON;
SQLINTEGER nValue = SQL_IS_INTEGER;
iReturn = fnConnectAttSet(m_SQLConnection, nType, pvAttribute, nValue );
//iReturn = SQLSetConnectAttrW(m_SQLConnection, nType, pvAttribute, nValue );
// Connect.
BSTR ODBCSource = :: SysAllocString(L"SQL2005");
BSTR UserName = :: SysAllocString(L"sa");
BSTR Password = :: SysAllocString(L"xxx");
iReturn = fnConnectProc(m_SQLConnection, ODBCSource,7,UserName,2,Password,3);
//iReturn = SQLConnectW(m_SQLConnection, ODBCSource,7,UserName,2,Password,3);
// Init bulk copy.
BSTR sTableName = :: SysAllocString(L"test");
BSTR sErrorFile = :: SysAllocString(L"c:\test4.txt");
int nDirection = DB_IN;
RETCODE nRet = fnBCPInit( m_SQLConnection, sTableName, NULL, sErrorFile, nDirection ); // nRet = 0
//RETCODE nRet = bcp_initW( m_SQLConnection, sTableName, NULL, sErrorFile, nDirection ); // nRet = 1
}
Best Practice To Pull Data From Sql Server 2000 To Sql Server 2005 With Dynamic Queries
May 3, 2007Hi There,
I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?
Thanks a lot!!
SQL 2012 :: Delete Particular Trace File And At Same Time Keeping Directory Dynamic
Mar 13, 2014I am struggling figuring out the token from a CMDEXEC job (as opposed to TSQL Job). It is not an option to execute the command by enabling the executing CMDs via TSQL, which is why I am using the agent. I have seen the Microsoft Site on tokens but all examples seem to be oriented to TSQL Job Type.
I am trying to delete a particular trace file and at same time keeping the SQL Directory dynamic.Taking it a step further is adding in "deleting if file exist".
del $(ESCAPE_SQUOTE(SQLDIR)) + "LogTestTrace.trc"
Integration Services :: Dynamic SIS Package Changing Source Connection At Run Time
Jul 30, 2015I am in between of creating a dynamic SSSIS package which will run for multiple zones having different source connection.My source is in Oracle.I am having 3 DFT with the 3 different source tables.I want to create a package with above DFT dynamically so that my single package can run for the entire zone with dynamically source connection change.I have created a Master table which stores the zone source connection string and zone name. I have 2 different connection.so if in future any new zones come so only newly zone details need to be add in master table without opening the package.
View 3 Replies View Related'Dynamic Time(year,month,date)' Report Template (rdl) Using Report Designer VS2005
Jan 19, 2007I have three types of specific reports that i have to create with the input parameters (range) either
1: By date (rdl 1)
2.By Month (rdl 2)
3.By Year (rdl 3)
Is it possible ( or how do I ) to create just one report template ( one rdl) with the three sets of parameters ( hiding/invisible which ever two sets base on user selection) and the output of the report will display the desired type( either by year, month or date).
I ask this because its possible to create a drill down report from year down to date etc in report designer (vs 2005). Not sure if I can create one instead of three rdls and with the 'logic' built within that template.
Thanks
Regards
Alu
How To Convert UTC Time (retrieved From SQL) To Local Time In Reporting Services Based On Time Zone
Aug 7, 2007
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns
Aug 25, 2007Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule
Nov 23, 2007Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
Merge Replication W/ Dynamic Row Filter - Not 'dynamic' After First Initial Sync?
May 2, 2007If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
T-SQL (SS2K8) :: How To Add Inline TVF With Dynamic Columns From CRL Dynamic Pivot
Mar 9, 2015I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....