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
I have a table thats updated daily with monthly data totals:
Month, Total orders1, Total orders2, etc 12/01/2012, 5, 8, etc 11/01/2012, 6, 5, etc
How do I pull data from this table in SQL Server for ONLY the current month? I was thinking using the getdate() function to get the current month, but it doesn't match exactly so I get no results
I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT RG.Id AS Id, RG.Name AS Name, ISNULL(SUM(AC.Amount), 0) AS Amount, RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
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))
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?
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 ?
Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up ArtNo March <=March 1210 20,500 50,900 1220 21,200 64,000 1230 15,400 40,300 ... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
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
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.
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);
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
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!
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"] + " ) ";
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.
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.
I run a query from Excel to pull the data from SQL server and I have created another worksheet to add my columns for different analysis and costs. When someone add another line in SQL against an order or a customer then manually added columns data goes out of sync.Is there anyway when I refresh the RAWDATA or anything changes on SQL table then it should sync my formatted worksheet.
I am trying to create a query that will pull a variety of data from three different tables. I've had to join four tables because one set of data is in a completely different table.
I am expecting one row of data but instead I get 4 rows. I suspect this is because I am joining a table indirectly. Here is the code:
There is only one applicable row in SDH, but four rows appear when I run the query. All four rows are identical except for the GL.GLCode column, which lists the three GL codes associated with the DeductionItems table. The fourth row is a duplicate of the third.
but when i form my statement with an inner join, i miss records, with the outer, i get a ton of records (more than both tables combined). i have been working with sql for about week now and can do simple queries but this is beyond me at this point. any help what so ever would be greatly appreciated.
I am working on an application to retrieve data to the windows ce 3.0 handheld from the Sql server database. I am able to retrieve the data from the server using RDA pull method and able to see the data on the local handheld database. To display the retrieved data in the dialog to the user, it requires to query the local databse and get the information and then display the information, which is taking some time.
My question is, to reduce the time and improve the performance , instead of pulling the data to the local table, is there any way to pull the data and have it in memory and display the details to the user?
To develop the above application, I used some of the code to pull the data from the server from the sample application C:Program FilesMicrosoft SQL Server CE 2.0SampleseVCeVCReplRdaHPC. I am developing the application for windows ce 3.0 device using eVC 3.0
I am trying to use RDA.pull to pull data from SQL server 2000 to sql mobile, but it fails when some columns are included in the SQLSelectString, though the datatypes involved are only char, float, smallint and smalldatetime, and nothing is longer than 30. Since there is a very large number of columns, it is difficult to isolate the guilty one by trial and error! Coud someone please help?
I am building RDA process between SQL server and SQL server CE. Since when we use the method 'SqlCeRemoteDataAccess.pull()', the data will be pulled down from SQL server to CE server and a new table is created with some addtional columns. As I only intend to pull the data and never push it back to SQL server, if I pull the data down for the second time, there will be an error saying that table is already existing. I wonder whether I can pull the data to an existing table everytime I do the RDA?
When I execute the package in Debug mode, it works.
I then import the package into SQL Server 2005 and it is showing under Stored Packages --> MSDB - AS400Package
I can right click and Run Package from there and it works fine.
When I set the package up to run as a job, it fails. What I have noticed is that the Sign On that I use in the package shows that there was in "invalid login" after the job fails.
I hope I have this post in the right place and hope I have left enough information behind to help. If not let know what else I need to post. I have only been using Business Intelligence Development for a couple days and SQL Server 2005 for about a week or so. All of the SQL DTS packages that I have created seem to be working fine with their jobs as well as my stored procedures.
I have to pull data for a report from 11/1/2013 to date. But once we get to 11/1/2014 then the report should pull data from 11/1/2014 to date and so on. Is there a way to do this? I guess I could go in and change the year manually but was hoping that there was a way to do this automatically.
so today would be tadate >= '11/1/2013'
but at any point between 11/1/2014 to 10/31/2015 it should be tadate >= '11/1/2014'
I'm currently working with a 10 million plus row database with the dataresiding on a Unix box with Cache 5.0. The problems is that it can take fivedays to pull one table from Cache to SQL 2000 using the ODBC connectionprovided by Cache in a SQL 2000 DTS package. I think the real problem isconverting the data from the post relational format (Cache) to a relationalformat (SQL 2000)???Does anyone have any ideas / suggestions on how to speed this transfer ofdata? I'm very new to Cache and any help would be greatly appreciated.Thanks,-p
Anyone have any experience in connecting to a Basis database? I want to build an SSIS package to ETL data out of a Basis database and into a sql server 2005 db. I have downloaded the ODBC driver, but am not usre how to plug it in to SSIS to do teh extraction.
Also I am totally new to Basis...it is not an RDBMS at all but the literature says it supports SQL queries, so I am not sure about where to start.
Could someone give me the general syntax to pull xml data from a web server that I have access to? I do know that I can not use an ip address, I have to use the hostname, etc.
I was using something like
FROM OPENROWSET(BULK '\SERVERNAMESHAREPATHDATAFILE.XML', SINGLE_BLOB) as x
instead of using SERVERNAME I am user http:\www.mycompanyname.commycompanynamestoredata.xml
I do not understand why this does not work. I hope I gave enough information for this.
Hello All, I have an application pulling data from SQL Server 2005 which is running on windows mobile 5 devices, and RDA is setup and working perfectly over http and this is in the production for long time, but we have decided to implement SSL for transportation recently and we have a certificate installed to the server and i am trying to pull data using https and getting "the request to send data to the computer running IIS has failed" message, only RDA stopped working over SSL, i can access scripts and other resources from the device using https (I made code to trust any certificate for any web requests) the link to the dll is accessible from outside, i have installed the root certicate on the server, the virtual directory has been changed to communicate via https on web synchronization wizard, then problem occurs, otherwise it will work ok, i am totally stuck at this point, is this a problem with port setup port 443 is opened, the issue is clearly a SSL issue. any help would be appreciated..what are the things to be done to implement SSL for RDA, i don't see anything more in msdn. thank you so much! sush
Hey guys, i finally connected to the db fine however for some reason i cannot pull the data. I was hoping somebody here might be able to help me. The code is below
Function MyQueryMethod() As System.Data.DataSet Dim connectionString As String = "server='xxxx'; user id='xxxx'; password='xxxxxx'; database='wel"& _ "sh_indiv'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT [test12].[name], [test12].[grade] FROM [test12]" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet) Return dataSet End Function im kinda new to programming with vb for asp.net pages so please bare with me.
I have my database: "RequestTrack" My table (with its columns): "Request"RequestKey (automatically generated)..and the Primary KeyEntryDate (datetime)Summary (nvarchar)RequestStatusCodeKey (bigint)EntryUserID (nvarchar)EntryUserEmail (nvarchar)I am wanting to create a basic web form where my user interface has 3 text boxes and a Submit button: txtUserID.TexttxtEmailAddress.TexttxtRequestSummary.Text **After I hit the submit button the information will then be inserted into the database. Also the RequestStatusCodeKey will be MANUALLY typed in so that will not require the user to add that. Please please please help ! I've been searching online for days and looking at various websites and still havent found anything. I've found somethings but they went into too much depth with too much information. I am just wanting to stay basic but w/o using SQLDataSource Controls. I would like to be able to store a lot of data. Thanks for your help!!!
'17686568 - Bill Statement - 11/16/2006 - Stm. Date - 10/27/2006'
Above is the data string that I am trying to pull the information from. Here is the function I'm currently using:
cast(substring(c.itemname,charindex('Bill Statement - ',c.itemname)+18,10) as varchar)
...which gives me what i want most of the time, but beacuse the date is not equally formatted through out the database and the date can look like 5/4/2012 and using the above formula it will show up as 5/4/2012 -
So my question is how can i trim off the dash part when the data shows up like 5/4/2012 - ?
