SQL Tools :: System Data Collection Sets
Jun 19, 2015I created Data Collection in wrong DB, how can I change the DB or return to default(as it came with clean version of SS) ?
View 3 Replies
ADVERTISEMENT
SQL Tools :: Management Studio Not Showing All Backup Sets When Restoring A Database?
Oct 16, 2015I am backing up a database as follows:
backup database PPLicense to disk = 'D:License.bak'
I have run this 5 times. If I know run this command:
restore headeronly from disk = 'D:License.bak'
I can see 5 entries returned this all looks good. In SQL Management studio I try and restore this database by selecting the file. The Restore Database dialog only shows the last backup set position i.e. position 5 and now the 4 before it. Why is this not showing the 4 previous backups?
I am using SQL Server 2012 SP 2 (Version 11.0.3460).
System.Security.SecurityException: Request For The Permission Of Type 'System.Data.SqlClient.SqlClientPermission, System.Data
Aug 21, 2006I have created a windows library control that accesses a local sql database
I tried the following strings for connecting
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true"
Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI"
I am not running the webpage in a virtual directory but in
C:Inetpubwwwrootusercontrol
and I have a simple index.html that tries to read from an sql db but throws
the error
System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
etc etc
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Trusted
I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security
I think that a windows form connecting to a sql database running in a webpage should be simple
to configure what am I missing?
Request For The Permission Of Type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, Pu
Mar 12, 2008
I created a .net console application within which I connect to a sql server (not local) as follows,
string conn_str = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=QuantEquitySql;Data Source=server name";
SqlConnection conn = new SqlConnection(conn_str);
conn.Open();
.
Everything works fine when I run it from my computer. When I try to run it from a network share I get the following error,
Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
I am using Visual Studio 2005 and .Net framwork v2.0.50727.
Does anybody know a fix to this problem?
Thanks
'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' Fail
Jun 20, 2007I have a report that uses some embedded custom code. The embedded custom code is a function that execute some sql query on a sql server database.Everything works fine in Visual studio. The report gets deployed on the server successfully, however when running the report from report manager i get the following error message :
The Hidden expression for the table €˜table1€™ contains an error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
Here is the code :
************************************************************************
Public function get_field() as string
Dim myConnection As System.Data.SqlClient.SqlConnection
Dim myCommand As System.Data.SqlClient.SqlCommand
Dim data_reader As System.Data.SqlClient.SqlDataReader
Dim field(100) as string
Dim i as integer
Dim j as integer
Dim sql_field as string
Dim nbr_field as integer
Dim rtn_string as string
i = 0
sql_field ="Select field from mytable"
myConnection = New System.Data.SqlClient.SqlConnection("Datasource=xxx.xxx.xxx.xxmydatabase;Initial Catalog=mydatabase;User Id=user1;Password=password1;")
myConnection.Open()
myCommand = New System.Data.SqlClient.SqlCommand(sql_field, myConnection)
data_reader = myCommand.ExecuteReader()
While data_reader.Read()
if data_reader.HasRows then
field(i)= data_reader(0).ToString()
end if
nbr_field = nbr_field + 1
i= i+1
End While
data_reader.Close()
myConnection.Close()
for j = 0 to nbr_field -1
rtn_string = rtn_string + field(j) + ","
Next j
rtn_string = left(rtn_string,rtn_string.length-1)
return rtn_string
'return sql_cmd
'return yes_no
'return lkupfield
end function
******************************************************************
Why do i get the error message ?, is this related to Code Access Security issues with .net framework. if yes
how do i set the Security so the report server or report manager allows embedded custom code to be executed. Any advice ?
Chi
Dynamic Data Elements For A Data Collection Application
Sep 20, 2005What is the better table design for a data collection application.1. Vertical model (pk, attributeName, AttributeValue)2. Custom columns (pk, custom1, custom2, custom3...custom50)Since the data elements collected may change year over year, whichmodel better takes of this column dynamicness
View 7 Replies View RelatedA First Chance Exception Of Type 'System.Data.SqlClient.SqlException' Occurred In System.data.dll
Jan 18, 2008Hi,
I've written this code multiple times now. But for the first time i get an error at the line underlined. My procedure runs perfectly when i execute it through Sql Query analyzer.
plzz help.. Its urgent and am unable to find the reason for this error "A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll"
Thanks !SqlConnection conn = new SqlConnection(DbConnectionString);
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "dbo.rqryTradesPRR";
cmd.Parameters.Add("@COBDate",SqlDbType.DateTime).Value = "2002-10-31 00:00:00.000" ;
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
// have written something here
}
Thanks in advance !
SQL Tools :: Error - Unable To Access User Principal Information From The System
Aug 25, 2014I am not able to launch the Kerberos Configuration Manager due to the above error.
View 4 Replies View RelatedData Collection From Many To One Servers
Mar 7, 2008I need to feed head office sql server with the data from regional servers. Servers are spread through all continents
Data input done locally on Head office server as well and plus need to ship data from other servers.
So clarify this - Head office server is not standby one. Mirroring is out of the picture, I think..
Initially, I thought ship a log every 15 min and restore on Head office server but is this going to create an issue for the local data processing?
Any bright ideas welcome!
SQL 2012 :: Data Collection Does Not Show Data
May 30, 2014i am trying to configure data collector on my server. so i configured data collector on server A and setup on server B. but the "Query statistics collection set" do not show me any data.
i right click and select "collect and upload now " item and get success result for this. but in the report i cant see any data...
also in the log page of data collection i see so many errors with messages like this:
"Failed to create kernel event for collection set: {2DC02BD6-E230-4C05-8516-4E8C0EF21F95}. Inner Error ------------------> Cannot create a file when that file already exists."
i tried some solution like disabling and enabling again, re-configuring, removing and configuring again .... but none of them work right.
SQL 2012 :: Data Collection (MDW) - TempDB
Mar 30, 2014It is possible that Data Collection can cause massive increasing MB/sec to tempdb ? I cannot find connection with tempdb and I set cash file, but on same disk.
Or it can be something different? Last two weeks what I checked was Read/Write MB/s to tempdb increasing progressively.
One time it was about 20MB/sec
After it was reseting and again 1MB/sec..
What I checked , External company which install SQL Server made one file for tempdb, next week or during breaktime(it will be possible), I would like make 8files next weekend work.
Now I saw that TempDB mdf was still increased, but using was just 8-10%
SSIS- The Element Cannot Be Found In A Collection. This Error Happens When You Try To Retrieve An Element From A Collection On A
May 19, 2008hi,
this is sanjeev,
i have SSIS package, using my c# program i want to add one execute package task to this package's sequence container.
it is creating the new package with out any probelm. but when i opened the package and try to move the newly created exeute package task it is giving the following error.
the element cannot be found in a collection. this error happens when you try to retrieve an element from a collection on a container during the execution of the package
this is my code
{
Package pkg = new Package();
string str = (string)entry.Key;
pkg.Name = str;
alEntity = (ArrayList)entry.Value;
ConnectionManager conMgr;
Executable chPackage;
TaskHost executePackageTask;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
//string PackagePath = @"c:Genesis.dtsx";
//p = app.LoadPackage(PackagePath, null);
p = new Package();
p.LoadFromXML(parentPackageBody, null);
p.Name = str;
//Sequence seqContainer;
IDTSSequence seqContainer;
//seqContainer = (Sequence)p.Executables["Extract Genesis Data"];
seqContainer = ((Sequence)p.Executables[0]);
string packageLocation = @"Geneva Packages";
conMgr = p.Connections["SQLChildPackagesConnectionString"];
foreach (string val in alEntity)
{
if (seqContainer.Executables.Contains("Load_" + val) == false)
{
chPackage = seqContainer.Executables.Add("STOCK:ExecutePackageTask");
executePackageTask = (TaskHost)chPackage;
executePackageTask.Name = "Load_" + val;
executePackageTask.Description = "Execute Package Task";
executePackageTask.Properties["Connection"].SetValue(executePackageTask, conMgr.Name);
executePackageTask.Properties["PackageName"].SetValue(executePackageTask, packageLocation + ddlApplication.SelectedItem.Text + @"" + executePackageTask.Name);
}
}
app.SaveToXml(Server.MapPath("../SynchronizeScript/Packages/" + ddlApplication.SelectedItem.Text + @"") + str + ".dtsx", p, null);
}
please let me know what is the wrong in my code.
thanks in advance.
regards
sanjeev bolllina
sanjay.bollina@gmail.com
Power Pivot :: External Access To Data Sets In The Data Catalog?
Apr 23, 2015I'm currently working on a BI architecture for a customer, and consider to propose the Power BI data catalog as a data distribution layer. The customer will use Power BI, but also has other BI tools.
Are data sets in the data catalog available to other clients than Power Query alone? E.g. are there OData feed endpoints available? If not, what would be the best way to give other tools access to the data?
How To Split The Data Into Training And Validation Sets When Doing Data Mining?
Jun 15, 2007Could I ask how to spit the data into training and validation sets when doing data mining?
Thanks
Realtime Data Collection - How Do I Put SQL7 In Charge?
Feb 6, 2000I have a product where we feed an SQL 7 DB data collected from Manufacturing. Presently, the Data transport Program is in charge of getting prepared data from machines and inserting into the DB. This design assumes SQL7 is always ready and able - which is not true due to customer queries or backups or etc. consuming resources. There is a low level buffer in system at manufacturing level if transport dies, but transport is ignorant of SQL distress, so keeps hammering DB's frontdoor. I'm looking for help in putting SQL server in charge of allowing data in - when resources are adequate. Seems I need a function that can determine server stress QUICKLY to forestall transport program and a buffer for records at the transport layer. Anyone know / done a system where SQL server CHECKS for waiting records or OK's an external program to send until told to stop? What indicates (reliably) low server resources? Anyone ever used MSMQ?
"Black Holes are proof SOMEBODY, SOMEWHERE really did have a particularly bad Y2K problem!"
SQL 2012 :: Data Collection Centralized Database - MDW
Apr 17, 2014I Enabled Data Collection on one of the server and planned to make it as Centralised Management Data Warehouse I configured data collection on it and can view reports. Next, I went to other server and configured "Set up data collection" to use my first instance as the centralised Database. But the issue is I can only see reports of first server. Am I missing something here.
I did exactly as explained in this video [URL] .....
How To Manage A Paging Mecanism In Data Collection ?
May 6, 2008Dear all,
I have an aplpication whih collect different types of history data from an SQL database. On type of those data are hitory log information where amount of records can be really big.
What i would like to implement is a kind of paging mecanism for colelcting those data.
fro example the first call will retrun the first 100 rows, then the next call would get rows 101 to 200 . etc....
How to perform thsi behaviour from SQL side ?
regrds
serge
Join Three Data Sets From Different Data Flows Into One Txt File
Mar 9, 2008Hi, I was wondering how it is posible to join three data sets from different data flows into one txt file.
Let's explain a little more:
I have 3 dataflows. Each of them connect to sql server and and by a SQL command, they bring data into SSIS.
Each SQL command differ between them. So each data set have different columns (they dont have the same format). Also the amount of columns differ between each one.
What I need is to join the three data sets into one txt file. How can I do this? It is posible to join them with different data set formats into a txt file?
Is this the best way to join different data? It is better to use as many OLE DB Sources are needed instead of different data flows?
Thanks for your help!
SQL 2012 :: Data Collection Fails For 1 Of 4 AlwaysOn AG Groups
Jun 5, 2014I have a two node SQL 2012 AlwaysOn HADR cluster (v11.0.3412) with 4 availability groups configured. The AG groups are set to synchronous mode and the secondary is not readable (we do not want the synchronous replica readable so we do not risk any reads causing contention so we maintain fast performance).
On the secondary we are getting a persistent failure with the Data Collector job called Collection_Set_3_Upload. The failure occurs within the second job step. That job step is executing the following command:
dcexec -u -s 3 -i "$(ESCAPE_DQUOTE(MACH))$(ESCAPE_DQUOTE(INST))"
The error message is as follows:
Log Job History (collection_set_3_upload) Step ID 2 Server CLUSTERNODE2
Job Name collection_set_3_upload
Step Name collection_set_3_upload_upload
Duration 00:00:07
[Code] ....
I know I can prevent this error message by enabling readable secondaries, but we do not want this.
I have tried stopping the data collection jobs and purging the cache directory but to no avail. It will succeed the first time then persistently fail again with the same message every time after that.
In addition, if I set the one failing AG group to readable secondary the job succeeds. So that means that 3/4 work fine, only this one is having an issue.
SQL 2012 :: Data Collection And Daylight Saving Time
Oct 26, 2015Last weekend many of our severs had a failed job "collection_set_3_upload". The error that occured is: "Violation of PRIMARY KEY constraint 'PK_ active_ sessions_ and_requests'. Cannot insert duplicate key in object 'snapshots.active_sessions_and_requests'. The duplicate key value is (2824333, 2015-10-25 02:54:49.7630000 +02:00, 1)."Last weekend we happened to go from summer time to winter time. i.e. the clock passed 02:00 - 3:00 two times during this night.
I.e. there is a bug in the Data Collector component that collects data for the Management Data Warehouse: it uses local time instead of UTC. I've created a Connect item to report it to Microsoft.URL...how do you get your process running again? the job will no longer run because it will every 5 minutes keep on trying to upload the conflicting data for the 2nd 2:00 - 3:00 period. I've only found one solution: get rid of all data collected but not yet uploaded.
You do this by stopping the Collection set (in SSMS go to Object Explorer -> <the server you want to fix> -> Management -> Data Collection -> System Data Collection Sets. Right click "Query Statistics" and select "Stop Data Collection Set").Then you delete the cached results from the sql server machine's harddisk. These cached results are in files located in a Temp folder on the sql machine itself, inside the AppData folder for the service account SQL Server Agent is running under. Usually it will be something like: c:Users<sql agent service account>AppDataLocalTemp.
Inside this folder delete all files that have 'QueryActivity' in their name. You'll loose all data collected since the start of wintertime, but at least your data collection process will work again.After this you can start the Collection set again by right clicking it and select "Start Data Collection Set". Every 5 minutes the data will be summarised and uploaded into your management data warehouse.
Posting Data Etiquette - Jeff Moden
Posting Performance Based Questions - Gail Shaw
Hidden RBAR - Jeff Moden
Cross Tabs and Pivots - Jeff Moden
Catch-all queries - Gail Shaw
Data Collection On Production Server To Capture Growth Rates
Mar 18, 2015I setup data collection on a production server to capture growth rates.
When I run the dis usage report, it shows a daily growth rate of over 500 megs. This seems excessive to me.
As a troubleshooting step I then ran sp_space used and got these results:
database_namedatabase_sizeunallocated space
rgc_prod 273442.63 MB3648.48 MB
reserved data index_size unused
265345488 KB164385384 KB99826072 KB1134032 KB
What should my next steps be to try and determine why there is so much growth? And isn't the index size rather large?
Large Data Sets
Mar 20, 2008Hi,
I'm currently trying to retrieve results from a large dataset, there are over 45000 records and I need to use them all to peform counts etc. I have set up views, but my page is still being returned slowly, is there anything I can do to speed this up?
Thanks
Gemma
Query With 2 Sets Of Data
May 7, 2008I am trying to query one table and get two different timeperiods of data, I am summing monthly totals to provide a running year total, but I also need last month's total in a seperate column. This is what I have so far but the subquery makes me group it which provides duplicate grouping.DECLARE @LASTPD AS INT
SET @LASTPD = (SELECT MAX(LASTPERIOD) FROM TABLE)
SELECT NAME,
POST_PD AS [MONTH],SUM(CHARGE_AMOUNT) AS MONTHLY_$,
LASTMONTH.LAST_MONTH,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLE INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME
WHERE POST_PD = @LASTPD
AND TABLE2.NUM= 539
GROUP BY NAME) AS LASTMONTH
INTO #TEMP_SAFROM TABLE
INNER JOIN TABLE2
ON TABLE2.NAME = TABLE.NAME,(SELECT SUM(CHARGE_AMOUNT) AS LAST_MONTH
FROM TABLEWHERE TABLE2.NUM = 539
GROUP BY NAME, POST_PDORDER BY NAME, POST_PD
SELECT NAME,
LAST_MONTH,
CAST(SUM(MONTHLY_$)AS DECIMAL(20,2)) AS YEARLY_$
FROM #TEMP_SA
GROUP BY NAME
ORDER BY NAME
Matching Two Sets Of Data
Mar 12, 2008Hi All,
I would like to match two sets of data. I have setup a view of data that contains a group of customers and their details. I want to view this data, but also find these customers in another table based on matching their surname and date of birth, then retreive the information stored on these customers from the second table.
Does anyone have any suggestions how i would go about doing this?
Thanks in advance
Humate
quote:Originally posted by Michael Valentine Jones
It takes real skill to produce something good out of a giant mess.
Comparing Two Sets Of Data
Jul 23, 2005I have the following situation. One set of data has 274 rows (set2)and anther has 264 (set1). Both data sets are similar in structure aswell as values for both of them were extracts from the same parenttable. Hope the info would substitute DDL. I need to find the "gap"rows between these two sets.Attempted to run a query likeselect count(*)from set2where not exists(select *from set1)did not yield what I desired. What else to try?TIA.
View 12 Replies View RelatedTwo Data Sets In A Matrix
Mar 13, 2007Hi
I have a matrix whos colunm group is filed by Dataset1,
now i want to add naother colunm group,but using the Dataset2
can I use two different dataset for a matrix,
for differnt colunm group
please help me in this regards
thanks
Error: The External Metadata Column Collection Is Out Of Synchronization With The Data Source Columns
Apr 17, 2007Hello,
I have a SSIS package with a Data Flow task. This task transfers the data from SQL Server 2000 to a table in SQL Server 2005.
I deployed and tested this package on the Test Server. Then put this package in a job and executed it - Works fine.
On the production server- If I execute the package through DTEXECUI, it works fine. But when I try executing it through a job- the job fails and it gives me following error:
Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "T_FieldName" (82)" needs to be removed from the external metadata column collection....
What I don't understand is, why are there no errors displayed when I execute the package through DTEXECUI.
Can anyone help me to resolve this issue.
Thanks.
T-SQL (SS2K8) :: Matching Two Data Sets
Apr 28, 2015I have two tables - one with sales and another with payments against those. The payment may not match the exact amount of sales and I have to use FIFO method to apply payments. The payment month must >= sales month.
How can i write a query to do this? Examples are as below.
Table 1
Sales Sale DateSale Amt
1Jun-141200
2Oct-142400
3Dec-14600
4Feb-1512000
Table 2
Pay Month Pay YearPay Amount
5 2014 300
6 2014 1000
10 2014 500
11 2014 2000
12 2014 300
1 2015 900
create table tbl1
(
saleNo int
,saleDate date
,saleAmt float
)
insert into tbl1 (saleNo, saleDate, saleAmt)
[Code] ....
Train And Test Data Sets
Feb 8, 2007I've seen that sometimes is better to split the table into a test dataset and a training dataset, and I'll appreciate if anyone can explain why is this...
thanks
Santiago Aceñolaza
Argentina
Multiple Data Sets In One List
Mar 24, 2008Is there a way to put more then one data set in a list.
I have a report that has three data sets with three tables. Now i want to show each report by Region, per page. So you can view the same stuff for each region seperately, instead of all together. Is there a way to do this. Where i dont have to go back in my code, and find a way to link everything together, so its in one data set .
Dynamic Data Sets In Reports
Sep 3, 2007
Hi,
I'm using a matrix report where in i want to use two datasets in the same report. How can i make the dataset dynamic for a single report.
Regards
Calculated Field Using Two Data Sets
May 12, 2008Hi,
I'm trying to created a report.
Final report looks like this.
Total Loans/Lines (#)
13,283
Total Commitments ($ MM)
$1,703
Total Outstandings ($ MM)
$1,175
A
B
C
D
F
Bankruptcy
0
$0
$0
0.00%
0.00%
Charge Off
0
$0
$0
0.00%
0.00%
Source table looks like this;
Bankruptcy
0
Charge Off
0
CLTV
131
DSR
102
Exc Total
265
FICO
7
Foreclosure/Repossession
Grand Total
13283
Loan Amount
32
Column D = A Bankrupcy(0) / Total Loans/Lines #(13283)
But it does not let me to use report expression as its not in the same scope.
Can anyone tell me how to do this calculation ?I was trying to use a report expression but it seems like not working.
Thanks
Are Data Sets Memory Efficient?
Feb 8, 2008
Hi,
I have designed a contact manager with Data Grid Control bound to a Data Set.
When the application closes, data from Data Set is written to XML file and when application opens, data from XML file is loded into Data Set and is show in Data Grid control.
Contacts in my application can exceed over 1,000
So, Is Data Set capable of handling lot of data very efficiently in memory?
Please advise