Pull Data From Cache 5.0 To SQL 2000 (fast?)
Jul 20, 2005
I'm currently working with a 10 million plus row database with the data
residing on a Unix box with Cache 5.0. The problems is that it can take five
days to pull one table from Cache to SQL 2000 using the ODBC connection
provided by Cache in a SQL 2000 DTS package. I think the real problem is
converting the data from the post relational format (Cache) to a relational
format (SQL 2000)???
Does anyone have any ideas / suggestions on how to speed this transfer of
data? I'm very new to Cache and any help would be greatly appreciated.
Thanks,
-p
View 3 Replies
ADVERTISEMENT
Sep 22, 2015
I have a dashboard page that I would like to load fast. But I would also like the user to be able to run the report On-Demand. The report has start date and end date parameters which default to the first and last day of last month. If I use a Cache plan that loads at 6AM the report runs with the default parameters set in the report, the user has to wait until it completes before they can change the parameters.
If I uncheck "use default" parameters in the report I lose the ability to set a cache refresh plan with the default parameters because the option is greyed out. So is there anyway outside of creating a second copy of the report to allow a user to open a report, change the parameters and run it, but also have a way to schedule some sort of Cached report for the default parameters so I can put that on a WebPart page that would load instantly?
View 4 Replies
View Related
May 3, 2007
Hi 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!!
View 4 Replies
View Related
Sep 28, 2005
The trick is to use a pivot tableCheck out the code herehttp://sqlservercode.blogspot.com/2...ops-in-sql.html
View 3 Replies
View Related
Jul 27, 2006
Can I do this with a sql 2000 database
http://www.cryer.co.uk/brian/sqlserver/howto_movedatabasefile.htm
then attach sql express to this mdf ?
View 8 Replies
View Related
Dec 27, 2007
Im getting this error when trying to set up a cache dependency...are there any special permissions etc?From CS:SqlCacheDependency dep = new SqlCacheDependency("MySite-Cache", "Products");Cache.Insert("Products", de.GetAllProductsList(), dep); From connectionStrings.config:<add name="SiteDB" connectionString="Data Source=localhost,[port]SQLEXPRESS;Integrated Security=true;User Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF" providerName="System.Data.SqlClient" />Also tried this using my machinename<add name="SiteDB" connectionString="Data
Source=<machinename>,[port]SQLEXPRESS;Integrated Security=true;User
Instance=true; AttachDBFileName=|DataDirectory|ASPNETDB.MDF"
providerName="System.Data.SqlClient" /> From web.config: <caching> <sqlCacheDependency enabled="true" pollTime="10000"> <databases> <add name="MySite-Cache" connectionStringName="SiteDB" pollTime="2000"/> </databases> </sqlCacheDependency> </caching> EDIT: So making progress I can't seem to get the table registered for cache dependency:The sample i have says"aspnet_regsql.exe -E -S .SqlExpress -d aspnetdb -t Customers -et"and the command line response is "Enabling the table for SQL cache dependency..An error has happened. Details of the exception:The table 'Customers' cannot be found in the database."Where does this "Customers" table come from? There is obviously not an application specific "Customers" table in aspnetdb I'm confused probably more by the example than anything....
View 3 Replies
View Related
Jan 23, 2004
Hello everybody
We need to move table T1 from database A to T1 database B on same server
size of table T1 15 GB and 40000000 rows
database B just created and will act as warehouse
could it be done simply by
1.creating table T1 on db B and then
2.set db to simple recovery
3.
insert into B.dbo.T1
select * from A.dbo.T1
4. create all the indexes on table T1 in db B
free disk space is 35GB
Any idea how to optimze import
Thank you
View 5 Replies
View Related
Jun 10, 2015
why bcp out (exporting data to a text file from a sql table using bcp utility ) is faster ?
View 6 Replies
View Related
May 31, 2007
Is there a way to drop clean buffers at the database level instead of the server/instance level like the undocumented DBCC FLUSHPROCINDB (@dbid)??
Is there a workaround for dbo? to be able to flush procedure and data cache without being elevated to sysadmin? server role?
PS: I am aware of the sp_recompile option that can be used to invalidate cached execution plans.
Thx.
View 1 Replies
View Related
Apr 8, 2006
I am searching for a way to fast load relation data. I know how to load data fast but how can i store relation data fast.
For example :
Table1 ( tabel1Id int identity , name varchar(255) )
Table2 ( tabel2Id int identity , table2Id , name varchar(255))
When i insert 50 records into Table1 i can't get the 50 identity fields back, to insert the related data into Table2.
I think one of the solutions could be returning a selection of
Table1 joined with syslockinfo, but i have no idea how to do it.
Does anyone have an idea?
View 3 Replies
View Related
Jul 10, 2007
Hi Every one,
How can I load or copy say millions of rows to a table in the database faster?
Thanks,
Mejo George
View 6 Replies
View Related
Apr 25, 2007
Hi all,
I have following problem:
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
{
engine.CreateDatabase();
}
serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";
using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(
Configuration.Default.SyncServerAddress, "", "", connStr))
{
rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");
}
Everythink works fine, when I use 'sa' user account in serverConnStr.
But, when I change conn string to:
"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
Please help.
Thank you.
Fipil.
View 10 Replies
View Related
Jul 5, 2007
Hi,
For this scenario, what is the best method of exporting data to sql 2005.
I want to export data from desktop app across internet to sql which can do on a row by row basis, but this is very slow and if the connection goes down halfway then pretty much buggered.
What is the best, reliable and fastest way to copy data across internet (several thousand rows), I have read about Bulk Insert etc... but also how would get around an upload and crashes half way, is there a way of uploading and until the whole upload goes through then the data is inserted into the database.
Would appreciate any guidance.
Richard
View 3 Replies
View Related
Dec 15, 2005
I've successfully carried out pull subscription using Wizard in SQL server 2000.
But NOW its the time to deploy the project & I've to do it programatically........
i tried to use the default stored procedures like "sp_addmergepullsubscription ", "sp_addmergepullsubscription_agent ", "sp_reinitmergepullsubscription ", with respective parameters.
Then using replmerge.exe i tried to execute the batch file But could not succeed......
Can anyone guide me from the Basics ??? (i knw the Replication Architecture its heirarchy....but have NO idea of performing it Programatically......!!!!!!!!)
by BASICS, i mean......how should i execute the resp. stored procedures with what parametes & the order i should follow in order to successfully pull a merge subscription in SQL server 2000.
if a Sample code is mentioned as example, would be better for me to understand....
Thanking all of you in anticipation...........
Mandar
View 1 Replies
View Related
Feb 15, 2008
Hi guys,
I am looking at the plan caches/cached pages from the perspective of
sys.dm_os_memory_cache_counters and sql serverlan Cache - Cache Pages
For the first one I am using
select (sum(single_pages_kb) + sum(multi_pages_kb) )
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
a slight change from a query in
http://blogs.msdn.com/sqlprogrammability/
For the second just perfmon.
The first one gives me a count of about 670,000 pages only for the object and query cache and the second one gives me a total of about 100,000 pages for five type of caches including object and query.
If I am using the query from http://blogs.msdn.com/sqlprogrammability/ to determin the plan cache size
select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB
from sys.dm_os_memory_cache_counters
where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'
it gives me about 5 GB when in fact my SQL Server it can access only max 2GB with Total and Target Server Memory at about 1.5 GB.
Does anyone have any idea what is going on?
View 2 Replies
View Related
Jul 15, 2014
I have a cluster hosting multiple GP databases and a second for my data-warehouse I am playing around with (personal project).
I have scripts that pull data from all the DB's to input into the DW's tables(Customers,Reps,Hub....)
An example of my branch script :
select interidas BranchID,
cmpnynamas BranchDesc,
address1as BranchAddressLine1,
address2as BranchAddressLine2,
address3as BranchAddressLine3,
zipcodeas PostalCode
from dynamics..SY01500
Where interid in ('comp1', 'comp2', 'comp4', 'comp5')
what would be the best way to using these scripts pull the data to my testDW and not have duplicate data issues?
I was thinking of using a staging DB on the GP cluster and then building an import data package to run nightly. the issue i had was how do i avoid duplicate data ?
View 0 Replies
View Related
Mar 4, 2005
Does anyone know how to upload (bulk) data from a client (written in Excel VBA) to a remote SQL2000 database? Of coarse I tried "INSERT INTO" and rst.addnew but I noticed this is much, much slower as downloading from the same remote database.
Thanks.
View 3 Replies
View Related
May 28, 2015
I have below DB structure in MSSQL for a small application which follow relational approach. Data retrieval (for Hostels) will need several Join, may be Key-Value approach where data retrieval will be fast.
Hostels
------------
HostelId,
Name,
Address,
CategotyId,
SubCategoryId,
FoodCategoryId,
LandLordId
Data:
1 H1 Address1 1 1 2 20
2 H2 Address2 1 2 2 21
3 H3 Address3 2 2 1 17
Category
----------
CategoryId,
CategoryName
[code]...
View 10 Replies
View Related
Jun 29, 1999
I have a server where the cache hit ratio averages 99.5%, however the cache flushes figure is high at 13068 over half a day, the avg free page scan is 122, both above what microsoft reccomend.
The server has 320Mb memory of which 272 is allocated to SQL Server.
Of the 272, 229.9 is allocated to the data cache.
I would be grateful of any suggestions to improve the situtaion
Thanks in advance
Tom Willetts
View 1 Replies
View Related
Jul 28, 2015
I’m looking for clearity on partition switching. The idea is to use many BULK INSERT statements into table dbo.X_n in parallel and when BULK INSERT for table dbo.X_n is completed, switch dbo.X_n into dbo.bigdaddy. I think this is the fastest way to upload a couple hundred GB of data.
In learning about partition switching (in part) from The Data Loading Performance Guide under Partition SWITCH, I hear the instructions to say copy the main table exactly to become a target. But in that same step (#1), I read that we need to change the default file group of the target (dbo.X_n) from the default file group. Then it says I need to match indexes and lists the filegroup as something we need to match with the main table.
As an overview of the partition switching strategy, I think the whole point of BULK INSERT with partitioning is to have seperate files (in same group) to enable concurrent uploading where each table has its own file. Once the upload is completed to a table (dbo.X_n) then we do the partition switch into the main table (dbo.bigdaddy). The data we just uploaded doesn’t actually move, just the metadata for it.
When I read the instructions linked above, I hear “Don’t have the same filegroup on your target as the main table. You must have the same filegroup on your target as the main table.”
Where am I disconnected?
View 5 Replies
View Related
Jul 28, 2015
I’m looking for clearity on partition switching. The idea is to use many BULK INSERT statements into table dbo.X_n in parallel and when BULK INSERT for table dbo.X_n is completed, switch dbo.X_n into dbo.bigdaddy. I think this is the fastest way to upload a couple hundred GB of data.
In learning about partition switching (in part) from The Data Loading Performance Guide under Partition SWITCH, I hear the instructions to say copy the main table exactly to become a target. But in that same step (#1), I read that we need to change the default file group of the target (dbo.X_n) from the default file group. Then it says I need to match indexes and lists the filegroup as something we need to match with the main table.
As an overview of the partition switching strategy, I think the whole point of BULK INSERT with partitioning is to have seperate files (in same group) to enable concurrent uploading where each table has its own file. Once the upload is completed to a table (dbo.X_n) then we do the partition switch into the main table (dbo.bigdaddy). The data we just uploaded doesn’t actually move, just the metadata for it.
“Don’t have the same filegroup on your target as the main table. You must have the same filegroup on your target as the main table.”
View 1 Replies
View Related
Feb 1, 2008
Hi,
I created a report model using reporting services, deployed the model locally and then built a report based on that model. The report works fine, but any data updates don't show up, seems like data is cached. I bounced reporting services, cleared cached tables in ReportServerTempDB but of no use. Has anybody faced this problem? How can I get updated data for reports built through a report model. Any help is appreciated.
Thanks,
Sri
View 4 Replies
View Related
May 6, 2008
Hi,
Writing a report that displays total hours, etc for each day in a given month and I'm having trouble comking up w/ an elegant solution. Tables are set up like this:
table1 (id, start_date,end_date,submitter)
table2( id,table1_id,start_time,end_time, etc)
Each record in table2 is a child to a parent record in table1 and there may be multiple children for each parent. The "start_time" and "end_time" fields are datatype "datetime" and are stored with actual times (not 12:00:00 AM"). I need the report to display the values for each day in a gridview, etc so I can act upon them from the "RowDataBound" sub (more math, etc). The problem that I am running into is that for records that have multiple children for a given day, I need to sum those results and display the summed data as a single row within the GV - can't seem to work that out - I keep getting every child record displaying as its own row in the GV.
Any thoughts? I'm sure it's probably fairly simple but I am still a beginner...thanks as always
View 8 Replies
View Related
Aug 22, 2005
I am new to SQL, as old as it is. I am not new to programming Inormally just use Access.I have two tables for a little project manager I made. After updates Isent an email to the user. I need to populate the user based on the"Assigned To" field I use, but I only log the username and not theemail address. Is there a way to associate the "Assigned To" to theUser's account in the "User" table so that I can pull thier emailaddress through code?ThanksChuck
View 5 Replies
View Related
Mar 6, 2008
I have the report sever running on windows2003 with sql standard server 2005. However, I' m trying to create a report that pull data from a different server with sql express. Could I do this? If so...any links, examples, suggestion I can take a look at.
thanks
View 1 Replies
View Related
Nov 2, 2006
Hello -
I am trying to pull data from SQL Server 2000 database onto my Pocket PC and the simple query works on Northwind sample database but does not on another custom build database. I did test the statement in the SQL Query Analyzer and it works.
The error I get is: "Failure setting up a non parameterized query, possible incorrect SQL query."
I know the code is right - thanks to Rory B's screenscasts. Any guidance will be greatly appreciated!
Thanks!
Moldau
Here is the code:
private void RdaPull()
{
try
{
//Create the database
if (File.Exists("\My Documents\Test.sdf"))
File.Delete(\My Documents\Test.sdf);
SqlCeEngine engine = new SqlCeEngine();
engine.LocalConnectionString = localConnection;
engine.CreateDatabase();
engine.Dispose();
//Initialize RDA Object
SqlCeRemoteDataAccess rda = null;
rda = new SqlCeRemoteDataAccess(rdaUrl, localConnection);
rda.Pull ("Customers", "select * from Customers", rdaOleDbConnectionString);
rda.Dispose();
MessageBox.Show("Done");
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.Message);
}
View 7 Replies
View Related
Dec 6, 2005
Dear Anyone,
View 6 Replies
View Related
Oct 16, 2007
Hi,
In my application i am storing the data in a database(server A). And i have a scenario where in a middle server(server B) is there in which i need to create a table and pull the data from one of the tables from my original database server(server A) into second database server(server B). Where can i get the code to write a job script which can connect from server B to server A and pull the data from the table and delete that data from the table in server A. Can any give me the code or link where i can find the code. Please help.
Thanks
View 5 Replies
View Related
Dec 19, 2007
Good Afternoon,
I have recently been tasked to come up with a means by which to create SQL data pulls based on user entries. What I mean by this is, I will have a pre defined SQL data pull set up and the user can then select the criteria upon which to pull said data.
So, say I want to display a table where one of the colums has is the "Year". I dont however want all years of data pulled, only 2005 - 2006. So, in form field 1, I could select start year as 2005 and in form field 2, I could select end year as 2006. This would then give me all data from this table based on that year range, or whatever year range I select and submit.
Is there a way to do this in ASP? I have been reading up on UDF with SQL, but I cant find anything about linking it through the use of web forms or through ASP. Or if there is another way to accomplish this? Any assistance is very much appreciated!
View 5 Replies
View Related
Feb 19, 2008
Hello, I am trying to pull run this sql statement but it's bombing out at the comm.ExecuteNonQuery();. ..
Could someone help me figure this out..
Ex.System.Int32 bum = System.Convert.ToInt32(Request.QueryString["dum"]);
SqlConnection conn = new SqlConnection("Data Source=**********************");SqlCommand comm = new SqlCommand();
comm.Connection = conn;SqlDataAdapter myadapter = new SqlDataAdapter(comm);DataSet myset = new DataSet();
conn.Open();
comm.CommandText = "Select * from Order_Forms where (Order_Num = " + Session["dum1"] + " ) ";
comm.ExecuteNonQuery();myadapter.Fill(myset, "Order_Forms");
myset.AcceptChanges();
Can yo usee the problem???
View 7 Replies
View Related
Apr 23, 2006
Hi, I am trying to pull e-mail addresses of certain users from a list of all the users in our company. I have two tables. One contains a single column of only the users I need. The other table contains every user and their e-mail address. Could someone tell me how to pull this data from these two tables. I am new to SQL Query and have been trying to figure out these JOIN statements, but nothing I am doing seems to work.
Thank you,
Lisa
View 2 Replies
View Related
Jul 28, 2004
I have an OLAP server and would like to use my Chart FX software without having to purchase the OLAP extensions on the server due to budget restraints (ouch).
I've heard that it is possible (although limited) to attach toan OLAP cube using SQL select statements (not MDX).
Basically, I would like to pull the OLAP data in the relational sense.
Is this possible? If so, are any good articles on this subject?
I'm new to OLAP and would like to transition slowly.
Thanks in advance :)
View 1 Replies
View Related
