I am using existing code, which I am trying to convert from using MS Access to SQL Server 2005...
The data set works fine with MS Access database, however when executing with SQL Server 2005 as data source, it generates the following error:
"..The data types ntext and nvarchar are incompatible in the equal to operator..."
in this line:
count = adapter.Update(dataset);
Not sure what should I look for since data sets are new to me.. Where should I check to fix this problem? I have noticed that the table has two columns with nvarchar...
A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?
I'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?
Hi, 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!
Hi, 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
I 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
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.
I 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.
VS.NET 2005 automatically deploys a CLR stored procedure when you start a project with debugging. However, if the CLR stored procedure attempts to access external resources you will get a message stating:
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.
I've isolated this to being because the CLR stored procedure does not have the EXTERNAL_ACCESS permission set.
Is there a security attribute that can be used to decorate the CLR stored procedure code so that VS.NET 2005 will register the CLR stored procedure with the correct permission set?
I 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
I'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...
Is 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 .
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?
I have two queries that generate two different datasets. One is a count of memebers, and the other is count of admits. I need to generate a calculated field from the two data sets called admits per 1000, which is essential the count of admits/counts of members *12000 I was able to calculte admits per 1000 easily in excel, however I need some insight on how to do is SQL.
Below are my queries from the two datasets.
MemberMonths dataset: Select factMembership.BusinessUnitCode, EffectiveCCYYMM, ISNULL(count(Distinct MemberId),0) As MemberCount From factMembership
[Code] ....
Admits dataset:
SELECT Factadmissions.BusinessUnitCode, factAdmissions.AdmitCCYYMM, ISNULL(Count(AdmitNum),0)As [Count of Admits] FROM factAdmissions
What I need to be able to do is somehow select based on a day, the total value of open orders. Â I have tried to do this in the database but it becomes fixed and quite cumbersome (this is a simplified example in reality i have line information and line component information).I am not hugely skilled with MDX and SSAS but know there are some semi-additive functions i want somebody to be able to pick a day and have the total value of only open orders.
I was wondering if anyone has ever written a chart with multiple datasets.
I need to be able to show sales dollars inflow by order date on one line and on the other needs to be sales dollars delivered by delivery date. So the all sections Values, Category groups, and Series Groups in the chart will be from 2 different datasets.
I have tried but it will not allow aggreates in the series groups.
we have a application written in straight ASP, we recently upgraded our SQL database connecting to the application from SQL 2000 to 2005 here is the issue
currently SQL 2005 is located in our test and UAT environments, SQL 2005 is not clustered in either location. Now let me make it clear THIS PROBLEM IS NOT HAPPENING IN OUR TEST ENVIRONMENT, I REPEAT THIS PROBLEM IS NOT HAPPENING IN OUR TEST ENVIROMENT, THIS PROBLEM IS ONLY HAPPENING IN OUR UAT ENVIROMENT
Each enviroment is outfitted with the following SQL 2005 environment, Enterprise Edition SP1 with Cumlative hotfix package 2153 just the SISS fix ONLY
now the problem is this, we have a vb program located directly on the server that has SQL 2005 on it that VB program inserts a timestamp into a table inside the database, now how this happens is the program connects to the database and opens a recordset with a forwardonly cursor and inserts the timestamp
NOW THE EXACT PROBLEM IS THAT THE VB PROGRAM CANNOT OPEN MULTIPLE RECORDSETS, THE ERROR RECEIVED IS LABLED LIKE THIS
NOW here is the interesting part, AGAIN THIS IS NOT HAPPENING IN OUR TEST ENVIRONMENT, THE VB PROGRAM AGAIN IS ON THE TEST SQL SERVER AND HAS A FORWARD ONLY CURSOR AND INSERTS JUST FINE
NOW IF I MOVE THE VB PROGRAM OUT OF OUR UAT ENVIRONMENT ONTO ANOTHER SERVER IT INSERTS THE RECORD ONTO SQL SERVER JUST FINE, IT OPENS THE RECORDSET AND OPERATES NORMALLY, BOTH SERVERS HAVE EXACT SAME PERMISSIONS
Can anyone help me understand whats happening here
One note, when we changed the cursortype from forward-only to keyset it worked fine but that doesnt explain things
why does it work in test so clearly what we did with changing the cursortype isnt the answer
I have a very strange problem using SQL Server 2005
I have several machines running an application, the problem is that on all machines except one of them the size of the result set that gets returned when I execute the following query is dfferent:
Select * from custoemr where EmployeeID = 3
on three out of the four machine the size of the result set is 1000, where on the other machine the size of the result set is 250, No errors are generated..
Can someome please teel me how to preceed in resolving this issue..
Hi there everyone. I have a stored procedure called “PagingTable� that I use for performing searches and specifying how many results to show per ‘page’ and which page I want to see. This allows me to do my paging on the server-side (the database tier) and only the results that actually get shown on the webpage fly across from my database server to my web server. The code might look something like this:
The problem is the stored procedure actually returns two result sets. The first result set contains information regarding the total number of results founds, the number of pages and the current page. The second result set contains the data to be shown (the columns specified). In ‘classic’ ASP I did this like this.
'Open the recordset rsItems.Open strSQL, conn, 0, 1
'Get the values required for drawing the paging table intCurrentPage = rsItems.Fields("CurrentPage").Value intTotalPages = rsItems.Fields("TotalPages").Value intTotalRows = rsItems.Fields("TotalRows").Value
'Advance to the next recordset Set rsItems = rsItems.NextRecordset
I am trying to do this now in ASP.NET 2.0 using the datasource control and the repeater control. Any idea how I can accomplish two things:
A) Bind the repeater control to the second resultset B) Build a “pager� of some sort using the values from the first resultset
Hello!My client has a need to be able to store Japanese characters in theirPeopleSoft database. So we need to change the character set from fromLatin1_General (1252) to Japanese character set (932) on SQL Server2000 Enterprise. I have 2 questions:1) I would like to know if in SQL Server, the character set is machinespecific or is it defined at the database instance level?2) Can multiple installs of SQL Server co-exist on a single server withdifferent default character sets?ThanksVishal
ColA ColB ----- ----- 21 A 22 A 23 A 24 B 25 B 26 D
What I want is to be able to identify a set sequence (1,2,3) based upon ColB such that I'd get the following result:
ColA ColB ColC ----- ----- ----- 21 A 1 22 A 1 23 A 1 24 B 2 25 B 2 26 D 3
I know that I should be able to get it using ROW_NUMBER() OVER (PARTITION BY ColB ORDER BY ColA), but instead of getting the sequence (1,1,1,2,2,3) I get (1,2,3,1,2,1). Using DENSE_RANK gave me the same results.
I have a Visual Studio 2005 solution which contains a Sql Server Integration Services (SSIS) project.
In this solution I have explicitly set a Web application project as startup project, but whenever I edit a DTS package within the SSIS project, VS automatically sets the SSIS project as startup project and the package I edit as startup object.
Needless to say, this may cause some really unwanted incidents when running the solution - thinking that you're about to run the Web application project (that was explicitly set as startup project), but instead, you run the edited package in the SSIS project.
Is there any way to avoid having the SSIS project automatically setting itself as startup project, any workaround here at all? :)
Hi,I come from the "dark side" php/mysql and there often problems withcharacter sets (utf-8, latin...) and storing data in datebase.Exists in the world of dot.net and ms-sql-server similiar problems?To precise: I have to store xml-data in database. Maybe its better toencode (like base64) the strings?Perhaps there are some links to read?Thanks.klaus.
so async cursor population is supposed to create the cursor and return the cursor id quickly, while the server works on async populating the results. For a keyset-driven cursor, SQL Server stores the key sets in tempdb, which it then uses to fetch data for cursor results. Anyway, this works fine for smaller tables, but I'm finding for large result sets, the async cursor population is very slow and indeed seems to approximate synchronous time. The wait stat I get while it is running (supposedly asynchronously) is TRANSACTION_MUTEX.
Example: --enable async cursor exec dbo.sp_configure 'cursor threshold', 0; reconfigure; declare @cursor int, @stmt nvarchar(max), @scrollopt int, @ccopt int, @rowcount int; --example of giant result set set @stmt = 'select * from sys.all_objects o1, sys.all_objects o1';
[code]...
Note that using the SQL "select * from sys.all_objects o1" is much faster than "select * from sys.all_objects o1, sys.all_objects o2". However, if cursor population is async, I'd expect the time to return a cursor id to be similar between the two.
I'm really new to the whole database deal (as well as VB.net) - specifically with the capabilities surrounding VB and SQL Server 2005. My question is open to any recommendations...
What I have is an application that a user uses to create 'new' products. They are presented a form to enter the information regarding the product they wish to create. They enter the details of the product and also locate an image that represents the product, too. Currently, this application saves the product information (including binary image data) into a SQL Server 2005 Express Edition database. This application and database reside on a client pc. What I need to do is to be able save updated and newly created product data into a file of some sort. That file will make its way to a memory stick (USB) and then be transported to a 'field' machine. Quite simply, what is the best way to do this? Are there walk-throughs on this sort of thing? The target database is also SQL Server 2005. I thought I'd post this question on here to get the best design ideas... Any help would be greatly appreciated.
