dear experts,
i heard in a session, that the tables which are modifying continuously are known as dynamic tables.and which are not are known as static tables.
my question is how to find the statics to judge static and dynamic tables?
Hi All,
We're currently preparing for a project for a bank client of ours where we would be using SQL Server 2008's data mining capabilities.
In the context of the out-of-the-box algorithms of SQL08 does the algorithms Logistics, Clustering and Logistic Regression includes Dynamic Logistics, Dynamic Clustering and Dynamic Logistic Regression?
Regards,
Joseph
I have gotten mixed comments on this topic. I have a 64 bit machine running 64 windows 2003 standard and 64 SQL 2005 standard with 8 GB of RAM. We want to upgrade it to 32 GB. What is the best approach to do this? Dynamic or Stattic giving min and max server memory a value ? and if static what value should I use for 32 GB knowing that this box is only being used for SQL.
View 3 Replies View RelatedI have gotten mixed comments on this topic. I have a 64 bit machine running 64 windows 2003 standard and 64 SQL 2005 standard with 8 GB of RAM. We want to upgrade it to 32 GB. What is the best approach to do this? Dynamic or Stattic giving min and max server memory a value ? and if static what value should I use for 32 GB knowing that this box is only being used for SQL.
View 3 Replies View RelatedI want to distinguish between static SQL, dynamic SQL, and embeddedSQL, but couldn't find too much useful resources in the web.For example, if we put SQL statements (SELECT, INSERT, UPDATE, etc...)inside an application (e.g. Java application, VB application, etc...),do we consider those SQL statements as static SQL? or embedded SQL?How about dynamic SQL? any practical examples?Please advise. thanks!!
View 4 Replies View RelatedIn one step of an SSIS package, i create an outgoing XLS filename based on the current datetime setting, a la:
"myFileName_" + (DT_WSTR,4)YEAR( getdate()) + RIGHT("0" + (DT_WSTR,2)MONTH( getdate()),2)+
RIGHT("0" + (DT_WSTR,2)DAY( getdate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("hh", getdate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("mi", getdate()),2) +
RIGHT("0" + (DT_WSTR,2)DATEPART("ss", getdate()),2) +".xls"
which provides the format as myFileName_yyyymmddhhmmss.xls.
This value is then assigned to a variable, user::myFilenameDateTime.
This variable is referred to in various steps which need the full pathname or filename.
I found, though, that in subsequent steps, the value for user::myFilenameDateTime is re-calculated whenever the variable is invoked.
So in one Task i created the physical output XLS file and named it "correctly", eg, myFileName_20070428090204.xls; in the next Task, i call a Stored Procedure in SQL Server 2005 to email the file with a corresponding message (that pulls in more data from the database).
The single Parameter to the Stored Procedure (that does the emailing) is the supposed/expected full Pathname of the outgoing file just produced but the parameter no longer represents the original filename -- and has changed slightly (a few seconds have been added) and the filename is now, myFileName_20070428090210.xls; consequently, my Email Distribution program cannot find the file with that specific name, although File = myFilename_20070428090204.xls does certainly exist.
so it appears that these variables are calculated realtime whenever encountered.
1) Am I misunderstanding something or misusing the variable assignment?
2) How can i keep << myFileName_yyyymmddhhmmss.xls >>, "static" throughout the duration of the overal SSIS Process.
3) I would think that even if I assign the derived myFileName value initially to ANOTHER "static" variable, this won't achieve anything because the new variable will be re-calculated again, as well, when it is invoked.
thx/spirits,
seth j hersh
Hi every one,
Could you please anyone tell me how to have static and dynamic columns in a matrix?
We can add static columns by right clicking detail text box and add column. But it will repeat for each column group. I need static columns in the dynamic columns level itself.
Thanks in advance!
I need to put a dynamic table (the amount of rows I control) into a static container (with a fixed size).
View 2 Replies View RelatedI'm trying to setup a dynamic ole db connection using the SA user ID, it has to be dynamic because the server name will change and it has to be SA because we're pulling information from system databases that some users don't have access to.
If I setup a regular static connection using SA credentials it works like a charm of course. When I create an expression to use the User:erver variable it doesn't work, it throws an error message saying that "The login failed for user sa" among other things, I'm thiking that the sa's password is not being saved.
Where exactly do I place a password for dynamic connections using sql server users? On the connection string? On the password property of the source? Any ideas?
I'm using Delphi 2006 to create a DLL which will be integrated into SQL 2005. It's been a long road and I've made a lot of headway, however I am currently having the following problem creating the stored procedure:
My dll name is 'Crystal_Reports_Test_01'
In the DLL, my class is named 'Class01'.
In the DLL, my procedure is named 'TestMe'
I've managed to integrate the DLL into SQL using the following statement:
CREATE ASSEMBLY TEST_ERIC_01
AUTHORIZATION dbo
FROM 'c:mssqlassembliescrystalreports.dll'
WITH PERMISSION_SET = UNSAFE
I am attempting to create the stored procedure which points to the 'TestMe' method inside of the DLL. FYI: 'CrystalReports' is the namespace above my class that I had to add in order to get it to locate the class. The following code is used to create the stored procedure:
create procedure dbo.Crystal_Reports_Test_01(
@Parm1 nvarchar(255)
)
as external name TEST_ERIC_01.[CrystalReports.Class01].TestMe
But I get the following error:
Msg 6573, Level 16, State 1, Procedure Crystal_Reports_Test_01, Line 1Method, property or field 'TestMe' of class 'CrystalReports.Class01' in assembly 'CrystalReports' is not static.
I'm not sure what this means exactly. I think it means the method (the procedure) is not using Static method binding but should be. I have no idea what this really means or how to accomplish this in the DLL - or if I'm even going about this in the right way.
Any help would be appreciated ! I'll post the Delphi code (DLL) below.
Thanks,
Eric Gooden
library CrystalReports;uses System.Reflection, System.Runtime.InteropServices;...................type Class01 = class public procedure TestMe([MarshalAs(UnmanagedType.LPWStr)] var sVarString: wideString); export; end;procedure Class01.TestMe([MarshalAs(UnmanagedType.LPWStr)] var sVarString: wideString); export;begin sVarString:= 'Lets change the value and see if the stored proc. gets the change.';end;end.
I have some data in Excel - sheet1 which would be static.
Now I need to import data from SQL Server (using a query) and Union above static data with this SQL data and later I will have to create PowerPivot table in Sheet2.
Which is suitable option for me to import data from SQL to excel as I see "From SQL Server" option under "Data" and "Power Query" tab as well.
How to merge above SQL data with existing static data?
(SQL Server 2012)
Firstly I consider myself quite an experienced SQL Server user, andamnow using SQL Server 2005 Express for the main backend of mysoftware.My problem is thus: The boss needs to run reports; I have designedthese reports as SQL procedures, to be executed through an ASPapplication. Basic, and even medium sized (10,000+ records) reportingrun at an acceptable speed, but for anything larger, IIS timeouts andquery timeouts often cause problems.I subsequently came up with the idea that I could reduce processingtimes by up to two-thirds by writing information from eachcalculationstage to a number of tables as the reporting procedure runs..ie. stage 1, write to table xxx1,stage 2 reads table xxx1 and writes to table xxx2,stage 3 reads table xxx2 and writes to table xxx3,etc, etc, etcprocedure read final table, and outputs information.This works wonderfully, EXCEPT that two people can't run the samereport at the same time, because as one procedure creates and writesto table xxx2, the other procedure tries to drop the table, or read atable that has already been dropped....Does anyone have any suggestions about how to get around thisproblem?I have thought about generating the table names dynamically using'sp_execute', but the statement I need to run is far too long(apparently there is a maximum length you can pass to it), and evenbreaking it down into sub-procedures is soooooooooooooooo timeconsuming and inefficient having to format statements as strings(replacing quotes and so on)How can I use multiple tables, or indeed process HUGE procedures,withdynamic table names, or temporary tables?All answers/suggestions/questions gratefully received.Thanks
View 2 Replies View Relatedhello everyone,
Thank you Darrell for your help on my "Firehose" problem.
Well, I have another question for everyone. I have three tables with the same columns: Products, Products_Sold, and Products_StandBy. I have a Stored Procedures that gets the products: Products_Get, Products_Sold_Get, and Products_StandBy_Get. I want to be able to only have one stored procedure that gets from all tables depending on what @Table variable I give it.
I tried the following, but it didn't work:
CREATE Procedure CMRC_Products_Get
@Table
AS
CREATE TABLE #TempTable
(
ProductID int,
Chest nvarchar (50),
ItemSize nvarchar (50)
)
INSERT INTO #TempTable
(
ProductID,
Chest,
ItemSize
)
SELECT
ProductID,
@Table.Chest,
CMRC_WomensSizes.ItemSize
FROM
@Table INNER JOIN
CMRC_WomensSizes ON @Table.Chest = CMRC_WomensSizes.Chest
I get an error message on the line: "@Table.Chest,"
So, my question would now be, how do I go about doing what I'm attempting here? Anybody? Thanks ahead for any help.
Sincerely,
Alec
hi all.
i just want to know if i can create a dynamic table. i mean a table which expands itself based on the requirement.
or a table can only be static???
Cheers!!!
Bel.
Hi
I am quite new to the complexities of MS SQL and have a problem, I would like to resolve. I have 2 tables with a unique identifier in both and want to populate a new table with information from both, but the second table I would like to populate just some fields that have a DOB eg
Table 1:
uniqueId
Name
Address
Table2:
uniqueId
Type
Setting
example of content for Table 2:
uniqueId Type Setting
123 DOB 03/04/74
234 TFN 12345678
567 POA Mr Smith
So the new table needs to be populated with a ll of info in table 1 and has a new field called DOB so only the clients with a DOB should populate this field, if the client in Table 1 has a TFN reference, this record should be added to the new table but no value needs to be entered eg
123 Chris Smith 1 high street 03/04/74
234 Jon brown 2 high terrace <Null>
Cheers
pommoz
I have a procedure that takes database name and queries some tables in the database specified. I am trying to access tables like this:
SELECT COUNT(id) FROM @dbname..sysobjects WHERE type='U'
How can I access the tables while the database name is in a variable.
Thanx.
Hi:
I'm trying to create an ad-hoc query on a Asp.net page for user. Besides the usual Boolean operators, Field Names, Comparison operators & Field Values, the ad-hoc query also involves multiple tables, eg [Customers] , [Members] & [Orders].
Now I have difficulties on writing a TSQL sp on how to take the dynamic query with different tables under consideration. User might simply query each individual tables (eg, Customers with age > 25) or combination of tables (eg, Membered Customers with Orders Amt > 1000 between 1/1/2005 - 1/31/2005)
I have look up a lot dynamic query on the net but all are with only 1 single table to hit. Could anyone give me a direction on how to write a dynamic query script with multiple tables under consideration? Much appreciated.
ps: The ad-hoc query only contains these defined tables, no other table will be involved.
I would like to do a dynamic audit for the tables ( columns which needs to audited will also change dynamically). For example,
I am having 3 tables, table1 table2 table3
User can select Table 1 ( col 1, col3, col4) and Table2 ( col 3 and col 5).
I want to achieve this functionality without any triggers or output clause, so only one option which is left is "ChangeDataCapture" ( to my knowledge). So i did a small POC. The problem here is, system captures all the field data even though value doesn't change, for example,
TableA
CountryId CountryCode CountryName
1 IND INDIA
Update TableA
Set CountryCode = 'INDI', CountryName = 'INDIA'
Where CountryId = 1
On running the above query, CDC stores the value for both the columns, but i don't want this. I only wanted to store the CountryCode value alone.
CREATE TABLE [dbo].tCountry(
CountryId bigint IDENTITY(1,1) NOT NULL Primary Key,
CountryName [varchar](50) NULL,
CountryCode [varchar](50) NULL,
CreateBy bigint
)
EXEC sys.sp_cdc_enable_db
[code]....
--See here, i want to display CountryName as NULL, because i didnt do any changes in this column
--Why am i asking this bcoz, Frm my front end application, i will send the complete list of columns and the values to the update query.
Select * from cdc.dbo_tCountry_CT Where __$operation in (3,4)
Hi allIn the SP below im (trying to) do some dynamic sql. As you can see the tableto use is set as a variable and the 'exec' method used to run thesqlstatements.My problem is that the 'if exists' method is not doing what i was hoping itcould do.The @presql command returns somewhere between 0 or 50 rows (give and take) -i just want the 'if exists' part to determine if the select statementreturns something or not since i then will have to update a current row - orinsert a new one.Even if there is no rows returned, the 'if exists' command will return true:-/Any suggestions to a different way of approach...?Thanks in advance :-)######## Stored procedure start ########[various @ variables]....declare @presql varchar(200)select @presql = 'SELECT * FROM '+@CurrentDB+' where btsiteID='+cast(@SiteID as varchar(6))+''IF exists((@presql))BEGINdeclare @UpdateSQL varchar(400)set @UpdateSQL = 'UPDATE '+@CurrentDB+' SET btDate='''+cast(@FileDate asvarchar(12))+''''exec(@UpdateSQL)ENDELSEBEGINdeclare @InsertSQL varchar(2000)select @InsertSQL = 'INSERT INTO ' + @CurrentDB + '(btDate,btTime)VALUES('''+ cast(@FileDate as varchar(12)) + ''','+ cast(@ImportTime as varchar(6)) + ')'EXEC(@InsertSQL)END######## Stored procedure end ########
View 2 Replies View RelatedHi,
I have a database with serveral tables, for example 'customer', I want to update this table with a SSIS package. However, to ensure we don't have issues if the update fails then I've put in an intermediate stage
Using an Execute SQL Task I create temporary tables, for example 'customer_tmp'. Data is then imported into these tables. When all the data is imported successfully the original tables are dropped and the temporary tables are renamed, removing the '_tmp'
This works fine and I'm happy with it. However, if someone adds a column to one of the tables in SQL server it is lost on the next upload.
Similarly I have to hard code creating the indexes into the package as well.
Does anyone know how I could copy the original table definitions and create the temporary tables dynamically. So that any new columns would be picked up?
And indeed is it possible to copy the indexes from one table to another before the drop and rename trick?
Thanks in advance.
Iain
Hi, I'm having a problem with my code,I am doing the following - retrieving a field from a table, which is linked to another table, i'm only updating one of the fields in one of the two tables...
Code: ( text )
it seems like i am able to create it. But when i try to access that
temporary table, i get an error "Invalid object".
this is happening only when i try to create local temporary table.
Global temporary table works fine inside the dynamic SQL.
Any Help appreciated.
I need to report on data from several databases on several servers. They are all SQL Server 2005 databases. I am trying to created an Integration Services task to consolidate and transform this data for easy reporting. The problem I am having is one database in particular. It has tables like this:
tblLookupData_Customer1
tblLookupParseData_Customer1
tblLookupData_Customer2
tblLookupParseData_Customer2
tblLookupData_Customer3
tblLookupParseData_Customer3
I want to use only the tables of the form "tblLookupParseData*" for this list. I can do this in Stored Procedures by dynmacally building up the sql query. I cannot find out how to do this in Integration Services. When I make Datasource Views, they seem to expect me to pick from a list of known tables. This list of tables grows as Customers are added to the system.
NOTE: The way the tables are structured was NOT my idea. I hate storing "data" in the structure of the database. Many people also do this when they create "period" tables such as "CustomerData_05_2005". It speeds up writing the data, and querying a specific table, but it is a nightmare for reporting. I cannot change this as it is not in my responsibility.
Hi
I have developed an application in ASP/SQL server 7.
Ths system is single user. One of the tables is updated by usr actions( say Table A).
To make it multi user. I want to create table such as A_Username.
How can query be written for this. Also there are many stored procedures which will access this table. In these stored procedures i will send username as an input. Then the query in the stored procedure shd access the table as A_Username .
Such dynamic table name refrencing , how can it be done.. Is creating a string for the query and then executing it using sp_exec the only option?
pls suggest
How to pass dynamic values in xml path query?
WITH TEST AS (
SELECT TL.TERMINAL_ID,T.IP_ADDRESS, T.LOGICAL_CONNECT_STATUS, SI.SCHEDULER_ID,
SI.INSTRUCTION, SI.GROUP_ID, SI.MAX_READ_RETRIES, SI.DATA_CHAR, SI.SCHEDULE_TYPE,SI.FILEPATH_FLAG,
T.STATION_NAME,T.BANK_ID FROM SCHEDULERINFO SI
INNER JOIN TERMINALGROUP TG ON SI.GROUP_ID = TG.GROUP_ID INNER JOIN TERMINALGROUPLINK TL ON TG.GROUP_ID = TL.GROUP_ID
[Code] ....
I need to pass dynamic values in FOR SCHEDULER_ID COLUMN. Because I have huge data.
I have two dynamic pivot tables that I need to join. The problem I'm running into is that at execution, one is ~7500 characters and the other is ~7000 characters.
I can't set them both up as CTEs and query, the statement gets truncated.
I can't use a temp table because those get dropped when the query finishes.
I can't use a real table because the insert statement gets truncated.
Do I have any other good options, or am I in Spacklesville?
Need to change a Excel report to SSRS.
Excel report has around 15 tables all with different columns.
Is there a way , I can show all data in SSRS by avoiding creation of 15 datasets and 15 tables.
Note -- All 15 tables have differnt columns list.
I have this pivot table (I only post the static version as the problem only regards the single quotes)
SELECT * from(
select DATEPART(year,DeliverydatePackingSlip) as Year,
CASE WHEN DiffPromiseDateFirst < 0 Then '1 - too early'
WHEN DiffPromiseDateFirst = 0 Then '2 - on time'
ELSE '3 - too late' END as Delivery
from iq4bisprocess.FactOTDCustomer
WHERE OTD_Exclusion = 0)a
PIVOT ( COUNT(Year)
For Year
in ([2012],[2013],[2014],[2015])) as pvtNow, packing everything in a string parameter I always stumble over the single quotes. I tried to replace them with CHAR(39), I tried to define a parameter for each occurrence, but always get a syntax error. What am I doing wrong?declare @sql nvarchar(max)
declare @title1 nvarchar(20)
declare @title2 nvarchar(20)
declare @title3 nvarchar(20)
set @title1 = '1 - too early'
set @title2 = '2 - on time'
set @title3 = '3 - too late'
[Code] .....
exec sp_executesql @sqlThis would throw:Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'early'.
As im diving into my new DBA role and reading as much as I can, I am a little confused on DMV's and system tables.
I've been reading through the book "SQL Server 2005 Bible", which has been very helpful. I tend to use it in conjunction with BOL whenever I come across something I want to learn more.
Last Friday, I tinkered around with DMV's, which was really cool, but I ran into something today that confused me.
Basically, it was finding out the recovery model for all the DB's on the server. The code in the book was:
SELECT [name], recovery_model_desc
FROM sys.databases;
Which turned exactly what it says it will.
however, I am confused.
I initially thought I needed to specify something in the "[name]" section, but realized, that is not the case.
My question is, why is that?
How do I know when to use [] around something?
I found this article:
http://www.databasejournal.com/features/mssql/article.php/3587906
I have been reading it.
I guess I am just really young and raw to T-SQL to know when to use the language corectly.
Is it due to the fact that the rules or syntax is a little different when using system tables?
Hope that makes sense.
Thanks.
TCG
In one query, I would like to query every user table in a specified database for
SELECT TOP (3) COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
GROUP BY [Target IP]
ORDER BY EventNum DESC
How is this possible?
Please give examples, I am a beginner.
Assume every table has the same structure for columns event count, target ip, and time.
Hi,
I have a requirement to create #Temp table in database and insert values to it.
I use following code:
DbCommand dbCreateTable;
dbCreateTable = provider.CreateCommand();
dbCreateTable.Connection = conn;
dbCreateTable.CommandText ="Create table #MyTemp (Id varchar(10))";
dbCreateTable.ExecuteNonQuery();
string[] insertValues = {"Insert into #Mytemp values ('TestString1')",
"Insert into #Mytemp values ('TestString2')"};
DbCommand dbInsertData = provider.CreateCommand();
dbInsertData.Connection = conn;
foreach (String insertStr in insertValues)
{
dbInsertData.CommandText = insertStr;
dbInsertData.ExecuteNonQuery();
}
Code creates the Temp table but when it comes to insert statement, it throws error saying "Temp table not found".
Reason can be Create and Insert statement gets executed as 2 different sessions.
How to get the above requirement work fine?
Thank you.
HV
I have a scenario, need to create SQL server Tables dynamically.
I Have multiple xml data file on a particular location, and want to load those XML data into sql server tables, but he metadata of each xml data files are not same.
Hence the approach is that,
1. Pick first file from that location
2. Create a table according to that xml data file metada
3. load data on newly created table.
4. Pickup the next xml data files.
5. loop through, till the XML data files are exists on that location.
I have the following stored procedure...
CREATE Procedure UserGetInfo2 (@UserID int, @SystemTimePeriodID int )As set nocount onSELECT Users.UserId as UserID, Users.UserName as UserName, Users.RealName as RealName, UserTimePeriod.BudgetCode as BudgetCode, UserTimePeriod.SystemTimePeriodID as SystemTimePeriodID, Users.Password as Password, Users.SSN as SSN, Users.Location as Location, Users.ScheduleType as ScheduleType, Users.EmployeeType as EmployeeType, Users.TimeAccounted as TimeAccountedFROM Users INNER JOIN UserTimePeriod ON Users.UserId = UserTimePeriod.UserIDWHERE (users.userID= @UserID) AND (UserTimePeriod.SystemTimePeriodID = @SystemTimePeriodID)returnGO
The problem lies in that when a person has a SystemTimePeriodID over a certain value, there is no UserTimePeriod record since it has not been created yet.
Obviously, I need to wrap this in an IF...EXISTS
IF EXISTS (SELECT UserTimePeriodID FROM UserTimePeriod WHERE (SystemTimePeriodID = @SystemTimePeriodID) AND (UserID = @UserID))
(the SELECT above, since that's what needs to come back if the data exists)
ELSE
Do the same select but put in a static value for BudgetCode, like '0000'
GO
How could I do the part where the IF...EXISTS fails?
I'm... not sure I can use RETURNS, since it feeds into this recordset:
rstUserInfo2.Open "UserGetInfo2 " & Request("UserID") & ", " & Request("SYSTIMEPERIODID")
and later uses values from that RecordSet, such as <td><%=rstUserInfo("BudgetCode") & ""%></td>