Query Error Help. (Could Not Allocate Ancillary Table For View Or Function Resolution)
Aug 8, 2007
i created a query and when i run it like this i get data but when i add a value in the 2ed case for '2%' i get error.
Select a.email, case when a.reportnumber like '1%' then
(select b.Reportnumber
from ijasSummaryNo b
where a.Reportnumber = b.Reportnumber) end as Reportnumber,
case when a.Reportnumber like '1%' then
(select b.stonebreakdown
from ijasSummaryNo b
where a.Reportnumber = b.Reportnumber) end as Measurement,
case when a.Reportnumber like '1%' then
(select b.reportcarddate
from ijasSummaryNo b
where a.Reportnumber = b.Reportnumber) end as ijasDate,
case when a.reportnumber like '2%' then
(select c.Reportnumber
from appraisalsummaryblue c
where a.reportnumber = c.reportnumber) end as imacsRepNo
from t_RegisterInfoTemp a
Query works fine like this but when i add this (the one marked bold i get error)
case when a.reportnumber like '2%' then
(select c.Reportnumber
from appraisalsummaryblue c
where a.reportnumber = c.reportnumber) end as imacsRepNo,
case when a.reportnumber like '2%' then
(select c.Measurement
from appraisalsummaryblue c
where a.reportnumber = c.reportnumber) end as Measurement2
This is the error.
Server: Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
View 4 Replies
ADVERTISEMENT
Jun 11, 2015
I'm just wondering what is the resolution or execution order for a query like this (simplified version):
CREATE VIEW ClosedSales AS
SELECT *
from NatSales
where CurrentFlag = 1
and ValidTo <> '9999-99-99'
Select * from ClosedSales
where CustomerId = 10
The thing I want to know is:
- If first the complete dataset from the view is calculated and the the CustomerId=10 filter is applied
- Or the CustomerId=10 condition is added to the where clause in the view definition
My real case is much more complex but that is an interesting point in my performance analysis.
View 2 Replies
View Related
Mar 21, 2015
I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below
Amount Month Year
25,000 July 2014
25,000 August 2014
25,000 September 2014
25,000 October 2014
25,000 November 2014
25,000 December 2014
25,000 January 2015
25,000 February 2015
.
.
.
.
25,000 June 2015
View 7 Replies
View Related
Sep 5, 2007
what is more efficient between a table-value function or a View
View 3 Replies
View Related
Jul 23, 2005
I have a problem (who not?) with a function which i'm using in a view.This function is a function which calculates a integer value of adate. For example: '12/31/2004 00:00:00" becomes 20041231. This isvery handy in a datawarehouse and performes superfast. But here is myproblem.My calendar table is limited by a couple of years. What happens isthat sometimes a value is loaded which is not in the range of theCalendardate. What we want to do is when a date is loaded is that thisfunction insert a minimum date when date < minimum date and a maximumdate when date > maximum date.Yes i know you're thinking : This is datamanipulation and yes this istrue. But now we loose information in our cubes and reports by innerjoining. So if we can use a minimum and a maximum than a user wouldsay: "This is strange, a lot of values on 1980/1/1!" instead of "Ithink that i have not all the data!"GreetzHennie
View 2 Replies
View Related
Sep 21, 2006
Hey,First, sorry if this post appear twice, because, I can not find my postrecently send, trying to post it once again.I'm out of ideas, so, I thought, will search help here again :(I'm trying to prepare a view for ext. app. This is in normal cases veryeasy, but what if the view structure should be dynamic?!Here is my point (I will siplify the examples).I have a table:create table t_data (id bigint identity (1,1) not null,valvarchar(10) not null,data varchar(100) not nullconstraint [PK_t_data] primary key clustered(id) with fillfactor = 90 on [PRIMARY] )goinsert into t_data (val, data) values('1111111111','1234567890abcdefghijklmnoprstuvwxyz 1234567890abcdefghijklmnoprstuvwxyz67890abcdefghij klmnoprstuvwxyz')insert into t_data (val, data) values('2222222222','1234567890abcdefghijklmnoprstuvwxyz 1234567890abcdefghijklmnoprstuvwxyz12345abcdefghij klmnoprstuvwxyz')insert into t_data (val, data) values('3333333333','12345abcdefghijklmnoprstuvwxyz12345 67890abcdefghijklmnoprstuvwxyz1234567890abcdefghij klmnoprstuvwxyz')insert into t_data (val, data) values('4444444444','67890abcdefghijklmnoprstuvwxyz12345 67890abcdefg12345hijklmnoprstuvwxyz67890abcdefghij klmnoprstuvwxyz')insert into t_data (val, data) values('5555555555','1230abcdefghijklmnoprst12345uvwxyz1 234567890abcdefghijklmnoprstuvwxyz67890abcdefghijk lmnoprstuvwxyz')gocreate table t_dataVal (id bigint identity (1,1) not null,valvarchar(10) not null,fill varchar(4) not nullconstraint [PK_t_dataVal] primary key clustered(id) with fillfactor = 90 on [PRIMARY] )goinsert into t_dataVal (val, fill) values ('1111111111','AAAA')insert into t_dataVal (val, fill) values ('2222222222','KKKK')insert into t_dataVal (val, fill) values ('3333333333','DDDD')insert into t_dataVal (val, fill) values ('4444444444','ZZZZ')insert into t_dataVal (val, fill) values ('5555555555','CCCC')gocreate table t_conf (id bigint identity (1,1) not null,start int not null,length int not null,description varchar(20) not null,constraint [PK_t_conf] primary key clustered(id) with fillfactor = 90 on [PRIMARY] )goinsert into t_conf (start, length, description) values (1,10,'value_1')insert into t_conf (start, length, description) values (11,3,'value_2')insert into t_conf (start, length, description) values(55,15,'value_3')insert into t_conf (start, length, description) values (33,2,'value_4')insert into t_conf (start, length, description) values (88,1,'value_5')insert into t_conf (start, length, description) values (56,7,'value_6')goNow here is the issue:table t_conf contain data, which can be modified by user. The user isseting the appropriate values.Now, there should be a view, which returns:- as headers (collumn names) this what is defined in description columnof t_conf (for example: value_1, value_2 ... value_6)- as values, substrings of all data from t_data, cutted with start andlength values for appropriate decription from t_conf.- first two columns of view, should be column val and fill of t_dataValtableSo the effect should be like this:valfillvalue_1value_2value_3value_4value_5value61111111111AAAA1234567890abc....2222222222KKKK1234567890abc....3333333333DDDD12345abcdefgh....4444444444ZZZZ67890abcdefgh....5555555555CCCC1230abcdefghi....of course, for all other value_x should be the appropriate substringsshown.Sounds simple, hm?Well, I'm trying to do this, since yesterday evening, and can not :(In real life, the call of view/function might happend a lot.The table t_data might have around 4000 records, but the data string islonger (around 3000 characters).Application, might acess a udf, which returns table, and I was focusingin that.Was trying, to create local temp table in function, to insert values,using cursor over t_conf.Unfortunately, everything what I get, is just a vertical representationof the data, and I need it horizontal :(The other problem in function is, that I can not use exec() (wll known)so I can not even create a table,dynamicly, using as column names description value from table t_conf,and as size of field length from this table.Sorry, that the description is maybe not exactly for my problem, butthis is because I'm not even sure, which way to use :(any help will be appreciated!Thank You - Matik
View 1 Replies
View Related
Jul 6, 2015
I am using Allwayson to replicate a FCI to a remote site. (windows 2012R2) Although all is implemented, iam getting a strange error with the listener resources after ive made a failover test.
It keeps saying "Name resolution not yet available"from my understanding this is trying to update an entry that doesn't exist (since the user has permissions to do it).
View 4 Replies
View Related
Jul 23, 2005
Because of an error in google or underlying site i can reply on my ownissue. Therefore i copied the former entered message in this message.-------------------------------------REPY----------------------------------Hi Maybe i wasn't clear. I want to dynamically check whether what thelowest date and the highest date is in the calendar table. Thepresented solutions has fixed dates and i don't want that.If i could store a global variable in SQL server (dynamic properties?)then it would be great. Fill this once and call it multiple times inmy intensively used function. Is this possible?GreetzHennie----------------------------Previously enteredissue-----------------------I have a problem (who not?) with a function which i'm using in a view.This function is a function which calculates a integer value of adate. For example: '12/31/2004 00:00:00" becomes 20041231. This isvery handy in a datawarehouse and performes superfast. But here is myproblem.My calendar table is limited by a couple of years. What happens isthat sometimes a value is loaded which is not in the range of theCalendardate. What we want to do is when a date is loaded is that thisfunction insert a minimum date when date < minimum date and a maximumdate when date > maximum date.Yes i know you're thinking : This is datamanipulation and yes this istrue. But now we loose information in our cubes and reports by innerjoining. So if we can use a minimum and a maximum than a user wouldsay: "This is strange, a lot of values on 1980/1/1!" instead of "Ithink that i have not all the data!"GreetzHenniePlaats een reactie op dit berichtBericht 2 van deze discussieVan:John Bell (jbellnewsposts@hotmail.com)Onderwerp:Re: Intensively used function in view needs a minimum andmaximum from a tableView this article onlyDiscussies:comp.databases.ms-sqlserverDatum:2004-12-30 03:56:25 PSTHiIf you LEFT or RIGHT JOIN to the calendar table you will get a NULLvaluefor the column, you can then is CASE to determine the valueCREATE FUNCTION ConvertDate (@datevalue datetime)RETURNS INTASBEGINDECLARE @dateint INTSELECT @dateint = CAST( CASE WHEN A.Date < '20030101' THEN '19800101'WHEN A.Date > '20051231' THEN '99991231'ELSE CONVERT(CHAR(4),C.[Year]) + RIGHT('0'+CONVERT(VARCHAR(2),C.[Month]),2) + RIGHT('0'+CONVERT(VARCHAR(2),C.[Day]),2)END AS INT )FROM ( SELECT @datevalue AS [Date] ) ALEFT JOIN CALENDAR C ON C.[Date] = A.[Date]RETURN @dateintENDGOJohn"Hennie de Nooijer" <hdenooijer@hotmail.com> wrote in messagenews:191115aa.0412300238.7dee0f85@posting.google.c om...[color=blue]>I have a problem (who not?) with a function which i'm using in a[/color]view.[color=blue]> This function is a function which calculates a integer value of a> date. For example: '12/31/2004 00:00:00" becomes 20041231. This is> very handy in a datawarehouse and performes superfast. But here is my> problem.>> My calendar table is limited by a couple of years. What happens is> that sometimes a value is loaded which is not in the range of the> Calendardate. What we want to do is when a date is loaded is that this> function insert a minimum date when date < minimum date and a maximum> date when date > maximum date.>> Yes i know you're thinking : This is datamanipulation and yes this is> true. But now we loose information in our cubes and reports by inner> joining. So if we can use a minimum and a maximum than a user would> say: "This is strange, a lot of values on 1980/1/1!" instead of "I> think that i have not all the data!">> Greetz>> Hennie[/color]Plaats een reactie op dit berichtBericht 3 van deze discussieVan:Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo)Onderwerp:Re: Intensively used function in view needs a minimum andmaximum from a tableView this article onlyDiscussies:comp.databases.ms-sqlserverDatum:2004-12-30 15:32:06 PSTOn 30 Dec 2004 02:38:51 -0800, Hennie de Nooijer wrote:[color=blue]>I have a problem (who not?) with a function which i'm using in a[/color]view.[color=blue]>This function is a function which calculates a integer value of a>date. For example: '12/31/2004 00:00:00" becomes 20041231. This is>very handy in a datawarehouse and performes superfast. But here is my>problem.[/color](snip)Hi Hennie,Is this conversion all that your function does? If so, you might wanttotry the following alternative (using CURRENT_TIMESTAMP as example;replaceit with your date column / parameter):SELECT CAST(CONVERT(varchar, CURRENT_TIMESTAMP, 112) AS int)You could put this in the UDF (probably at least as fast as yourcurrentCalenmdar-table based function), or use it inline as a replacement tothefunction call (probably even faster).It should work for all dates from Jan 1st 1753 through Dec 31st 9999.Best, Hugo--(Remove _NO_ and _SPAM_ to get my e-mail address)
View 2 Replies
View Related
Aug 13, 2007
How Do I fix View(below) or Multi-Table select(below) to use this Function to return distinct rows via qcParent_ID?
Following Function populates a field (with concat list of related titles) with other required fields:
Create Function [dbo].openItemsIntoList(@Delimeter varchar(15),@qcparent_ID varchar(1000))
Returns Varchar(8000) as
Begin
Declare @Lists as varchar(8000);
Select @Lists = '';
Select @Lists = @Lists + itemTitle + @Delimeter From z_QClocate_openAll_Qualifier
Where @qcParent_ID = qcParent_ID;
Return Substring(@Lists,1,len(@Lists)-len(@Delimeter));
End
works perfect against single table select (returning 54 distinct rows by qcParent_ID):
Select a.qcParent_ID, a.Facility, a.Modality, openItemListToFix
From dbo.a2_qcEntryForm a
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
But data is needed from 3 tables...
- Created a VIEW that returns all (82) rows (negating distinct of the function on qcParent_ID)
- Failed Miserably Integrating Function call into a multi-table select (inexperienced with complex joins)
This VIEW returns ALL (82) rows in table:
CREATE VIEW z_QClocate_openAll AS
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
Calling like this returns ALL 82 rows (negating the functions distinct):
Select a.qcParent_ID, a.qcStatus, a.qcAlert, a.itemComplete, a.Facility, a.Modality, openItemListToFix
From z_QClocate_openAll a
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
AND THEN THERES...
Failing miserably on Integrating the Function call into This SELECT ON MULTI-TABLES:
How to integrate the Function call:
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
into the multi-table Select relationships (while maintaining Where & Order By):
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
View 3 Replies
View Related
Oct 28, 2007
I posted a question a few days ago, to get some general information what this command does:
command.cmd
osql -S (local)SQLEXPRESS -E -i DropDB.sql
DropDB.sql
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Caching')
DROP DATABASE [Caching]
I have gotten some great answers. Now I like to ask a deeper question. This command was created for SQL Server Express, for an internal software package at my company, by an outside vendor. However, sometimes this command doesn't work 100% on all user computers. So I have two questions.
1. Is there any way to either enhance the command, or the database command, to provide error diagnostics, to find out the issue for an individual user computer, why it doesn't always work?
2. Can anyone think of any scenarios that would prevent this from working?
The key element here is that this caching database is supposed to allow individuals to use a particular piece of software, when they aren't connected to the network at work. But it doesn't always work on each computer - 100% of the time - even when the computer has SQL Server Express installed, and is running Windows XP.
Randy
View 2 Replies
View Related
Oct 29, 2007
An outside vendor created this command and SQL files for internal
software, using SQL Server Express, which is installed on every user
machine
osql -S (local)SQLEXPRESS -E -i DropDB.sql
The SQL file has this:
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Caching')
DROP DATABASE [Caching]
This command was created for SQL Server Express, for an internal software package at my company, by an outside vendor. However, sometimes this command doesn't work 100% on all user computers. So I have two questions.
1. Is there any way to either enhance the command, or the database command, to provide error diagnostics, to find out the issue for an individual user computer, why it doesn't always work?
2. Can anyone think of any scenarios that would prevent this from working?
The key element here is that this caching database is supposed to
allow individuals to use a particular piece of software, when they aren't connected to the network at work. But it doesn't always work on each computer - 100% of the time - even when the computer has SQL Server Express installed, and is running Windows XP.
Randy
View 2 Replies
View Related
Jul 20, 2005
I am getting a "Could not allocate space for object 'temp_trc' in database'Test' because the 'PRIMARY' filegroup is full"The database test has unrestricted growth (All the defaults). It resides ondrive c which has 4Gigs free. I added new data and log files on drive dwhich is about 30G free. I know that my insert doesn't take even 1G diskspace.Why is the database complaining about a full filegroup when I just expandedit?J.
View 1 Replies
View Related
Nov 20, 2000
Hi all,
I need to load a table with 820,000 records from a Sybase db via DTS. It always fail with the error:
"Error at destination for row number 820000. Could not allocate space for object in tablespace tempdb
. The default filegroup is full.".
There is only the primary filegroup defined in the db. I've increased the size from 1.5GB to 2GB,
and specify that it shd grow automatically by 10% and there's no limit to the size.
There is still some 28GB in the server, so it should be fine.
It still fail so I added another file to the primary filegroup with size 100MB. Again, it failed with the same error msg.
Where or how else can I increase the tempdb size?
Any help is appreciated.
Thanx,
Jes
View 1 Replies
View Related
Jan 31, 2008
I have an error on the phpbb message board I am using. I have a MS SQL database and cannot figure out what I get this error. How could I fix this?
The full error is:
Could not allocate space for object 'phpbb_topics' in database 'adultsiblinggrief' because the 'PRIMARY' filegroup is full.
View 1 Replies
View Related
Jul 20, 2005
is there a setting that will ebnable uniform extent allocation uponcreation of index/table by default ?if there isn't any default setting can you code it in?thanks,Doron
View 1 Replies
View Related
Feb 18, 2008
Hello, I want to get data from datatable as below. I am getting error:
BC30516: Overload resolution failed because no accessible 'New' accepts this number of arguments. I did not understand what is wrong. Because everything is same as msdn library.
my codebehind is:
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClientPartial Class Default2 Inherits System.Web.UI.Page
Private Shared Function GetConnectionString() As String
' To avoid storing the connection string in your code,
' you can retrieve it from a configuration file.
Return "Data Source=Database;Initial Catalog=otel;Integrated Security=True;Pooling=False"
End FunctionProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadDim connectionString As String = _
GetConnectionString()
' Create a SqlConnection to the database.Using connection As SqlConnection = New SqlConnection(connectionString)
' Create a SqlDataAdapter for the Suppliers table.Dim mailsAdapter As SqlDataAdapter = _ New SqlDataAdapter()
' A table mapping names the DataTable.mailsAdapter.TableMappings.Add("Table", "Pages")
connection.Open()Dim PagesCommand As SqlCommand = New SqlCommand( _"SELECT * FROM Pages", _
connection)
PagesCommand.CommandType = CommandType.Text
' Set the SqlDataAdapter's SelectCommand.
mailsAdapter.SelectCommand = PagesCommand
' Fill the DataSet.
Dim dataSet1 As Dataset = New Dataset("Pages") 'ERROR MESSAGE HERE...........................................mailsAdapter.Fill(dataSet1)
connection.Close()LblPageName.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(1))
TxtPageTitle.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(2))TxtPageSummary.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(3))
Rte1.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(4))TxtPageimgUrl.Text = CStr(dataSet1.Tables("Pages").Rows(0).Item(5))
End Using
End Sub
End Class
View 1 Replies
View Related
Feb 26, 2008
Hi SQL Server Team,
In order to add a data source by using ODBC Administrator, programmatically i am using SQLConfigDataSource. Thus calling SQLConfigDataSource with the fOption set to either ODBC_ADD_DSN for creating a new DSN.
Following is the stattement specified in the source code. where type_of_driver = "Microsoft Access Driver (*.mdb)"; and parameters contains the data source source credentials information such as DSN, UID, PSW, FIL, Description, DataDirectory,DEFAULTDIR and DBQ all separated by " ".
rc = SQLConfigDataSource(NULL, ODBC_ADD_DSN, type_of_driver, parameters);
after execution of below mentioned command an error is encountered i.e -1 is returned if the DataDirectory path exceeds certain nested directories link. for eg. the file path specified below.
C:My DocumentsClients CurrentAHW Netcare BenefitsDeliverables1-5 Results Chain - system LevelAlberta NetcareBenefits Logic System Level V01.rcm
Creating a datasource with the file name i.e Benefits Logic System Level V01 every time.
rc = :: SQLConnect(hDbc, (unsigned char *)szSource, SQL_NTS, (unsigned char *)szUserName, SQL_NTS, (unsigned char *)szPassword, SQL_NTS);
Also following error message was encountered during execution of SQLConnect command.
/*szErrorMsg = 0x0012e834 "[Microsoft][ODBC Microsoft Access Driver]General error Not enough information to connect to this DSN with SQLConnect. Use SQLDriverConnect."*/
Thus followed the instruction smentioned in the error message an dtried using SQL DriverConnect() in the following manner.
rc = :: SQLDriverConnect(hDbc, NULL,(SQLCHAR *)"DSN=RCM",SQL_NTS,outstr, sizeof(outstr),
&outstrlen,SQL_DRIVER_NOPROMPT);
Still an error is encountered but this time it returns a RETCODE of 100 whrease earlier it had returned -1.
Inform the procedure to be followed using SQLConnect when DataDirectory path is too long.
An early reply will be appreciated.
Cheers & Regards,
Saresh
View 1 Replies
View Related
Apr 22, 2008
Hey everybody!
I create a function to call stored procedure, and query a view, but I am having troubles with quering table-value function.
I looked for an example on the net on how to do it, but I couldn't find any (I find only .net examples :-) )
Can anyone direct me to an example, or write a small sample?
Thanks a lot in advance!!!
View 15 Replies
View Related
Nov 30, 2006
Hi there,I struggle to get this going i would like to insert data into 2 tmptables in a view.If i run the code on it's own it works perfectly until i want to createa view it complains about the INSERTthis is my codeCreate view dbo.vew_SwitchesAsINSERT INTO tmpInsSelectDistinctBIV.DATE,BIV.ID,CA.NAME,BIV.IND,BIV.AMOUNT,BIV.UNITS,BIV.INAME,MB.NOfrom Cars BIVLEFT JOIN MountainBikes MBON MB.ID = BIV.IDAND MB.CLASS = BIV.CLASSAND MB.NUMBER = BIV.NUMBERAND MB.DATE = BIV.DATELEFT JOIN Caterpillars CAON CA.ID = MB.NOwhere BIV.CLASS = 'SWCH'and BIV.IND = 'IN'AND BIV.UNITS = 0AND BIV.AMOUNT <0ORDER BY BIV.DATE ASC------ Step 2 -------Into tmpOutsInsert Into tmpOuts ---- All Switches In ----SelectDistinctBIV.DATE,BIV.ID,CA.NAME,BIV.IND,BIV.AMOUNT,BIV.UNITS,BIV.NAME,MB.NOfrom Cars BIVLEFT JOIN Mountainbikes MBON MB.ID = BIV._IDAND MB.CLASS = BIV.CLASSAND MB.NUMBER = BIV.NUMBERAND MB.DATE = BIV.DATELEFT JOIN Caterpillars CAON CA.ID = MB.NOwhere BIV.CLASS = 'SWCH'and BIV.IND = 'OUT'AND BIV.UNITS = 0AND BIV.AMOUNT <0ORDER BY BIV.DATE ASC----------------------Step 3 ----------------SelectDistinctins.DATE,ins.ID,ins.NAME ,insIND,ins.AMOUNT/100 as AmountIn,outs.IND,outs.AMOUNT/100 as AmountOut,outs.NAME
Quote:
View 4 Replies
View Related
Apr 5, 2006
I am having the following situation - there is a view that aggregates and computes some values and a table that I need the details from so I join them filtering on the primary key of the table. The execution plan shows that the view is executed without any filtering so it returns 140 000 rows which are later filtered by the join operation a hash table match. This hash table match takes 47% of the query cost. I tried selecting the same view but directly giving a where clause without the join €“ it gave a completely different execution plan. Using the second method is in at least 4 folds faster and is going only through Index Seeks and nested loops.
So I tried modifying the query with third version. It gave almost the same execution plan as the version 1 with the join operation.
It seams that by giving the where clause directly the execution plan chosen by the query optimizer is completely different €“ it filters the view and the results from it and returns it at the same time, in contrast to the first version where the view is executed and return and later filtered. Is it possible to change the query some how so that it filters the view before been joined to the table.
Any suggestions will be appreciated greatly
Stoil Pankov
"vHCItemLimitUsed" - this is the view
"tHCContractInsured" - this is the table
"ixHCContractInsuredID" - is the primary key of the table
Here is a simple representation of the effect:
Version 1:
select *
from dbo.vHCItemLimitUsed
inner join tHCContractInsured on
vHCItemLimitUsed.ixHCContractInsuredID = tHCContractInsured.ixHCContractInsuredID
where tHCContractInsured.ixHCContractInsuredID in (9012,9013,9014,9015)
Version 2:
select *
from vHCItemLimitUsed
where ixHCContractInsuredID in (9012,9013,9014,9015)
Version 3:
select *
from dbo.vHCItemLimitUsed
where ixHCContractInsuredID in
(select ixHCContractInsuredID
from tHCContractInsured
where ixHCContractInsuredID in (9012,9013,9014,9015))
View 1 Replies
View Related
Jul 27, 2005
I am in the middle of creating an editable DatGrid:
Sub AccessoryGrid_EditCommand(source As Object, e As MxDataGridCommandEventArgs)
AccessoryGrid.EditItemIndex = e.Item.ItemIndex
End Sub
Sub AccessoryGrid_BeforeUpdate(source As Object, e As MxDataGridUpdateEventArgs)
e.NewValues.Add("@AccessoryID",
AccessoryGrid.DataSource.DataSource.Tables(0).Rows(e.Item.DataSetIndex)
("AccessoryID"))
e.NewValues.Add("@AccessoryName", CType(e.Item.Cells(1).Controls(0),TextBox).Text)
e.NewValues.Add("@AccessoryPrice", CType(e.Item.Cells(2).Controls(0),TextBox).Text)
e.NewValues.Add("@AccessorySold", CType(e.Item.Cells(3).Controls(0),TextBox).Text)
e.NewValues.Add("@AccessoryDesc", CType(e.Item.Cells(4).Controls(0),TextBox).Text)
e.NewValues.Add("@AccessoryImage", CType(e.Item.Cells(5).Controls(0),TextBox).Text)
End Sub
For some reason, I get an error message like this:
Server Error in '/' Application.
Disallowed
implicit conversion from data type nvarchar to data type smallmoney,
table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT
function to run this query.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException:
Disallowed implicit conversion from data type nvarchar to data type
smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use
the CONVERT function to run this query.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException: Disallowed implicit conversion from data type nvarchar to data type smallmoney, table 'cardb.dbo.accessories', column 'AccessoryPrice'. Use the CONVERT function to run this query.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.PerformSqlCommand(SqlCommand command) +82 Microsoft.Saturn.Framework.Web.UI.SqlDataSourceControl.Update(String listName, IDictionary selectionFilters, IDictionary newValues) +114 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnUpdateCommand(MxDataGridUpdateEventArgs e) +869 Microsoft.Saturn.Framework.Web.UI.MxDataGrid.OnBubbleEvent(Object source, EventArgs e) +546 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 Microsoft.Saturn.Framework.Web.UI.MxDataGridItem.OnBubbleEvent(Object source, EventArgs e) +86 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +95 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277
My main question is, how can I convert my column 'AccessoryPrice' to smallmoney?
I have been trying to get rid of this error by trying to change the
field type within my database with no success, I keep on getting the
same error either way.
I would be very greatful if anybody can help me.
View 2 Replies
View Related
Aug 23, 2007
Hi everybody.
I created an application role in a database (DB1) and gave it all the rights on a view in DB1 which refers to a table located in another db (DB2). I also gave the rights to the app role on a table of DB1
I tried to use this app. role through the sp_setapprole launched by a user (server principal?) which is SQL Server administrator (and local administrator (Win 2003 Server)).
With the following query
SELECT USER_NAME()
I see that the approle is being used.
Than, if I query the table on DB1 everything works, but if I query the view, referring a table in db2 I get following error:
The server principal "NameOfServerPrincipal" is not able to access the database "DB2" under the current security context.
What should I do to make it work?
The table in DB2 has the same schema of the view in DB1 which refers to it.
I put the DB1 TrustWorthy and both the database have the db_chaining option activated.
Any idea on how to solve the problem would be widely appreciated.
Thank you very much.
Vania
View 5 Replies
View Related
Jul 20, 2005
I have a database which stores information about organisms collectedduring sediment toxicology research. For each sample, organisms insediment are collected and identified taxonomically (Order, Family,Genus, Species).Taxonomy lookup information in the database is stored in a recursivetable in the form:TSN (taxa serial number)Rank (Order, Family, Genus, Species)NameParent_TSN (related Taxa at higher taxonomic level)When the number of a particlar organism collected is entered into thedatabase, the count is stored along with the lowest level TSN theorganisms were identified to.Okay - now the problem. Depending on the type of analysis being done,a user may want organism counts at the lowest level, or rolled up to ahigher taxonomic level (usually Family). Can I write a recursivefunction which will cycle through the Taxonomy database, and providethe name of the organism at the appropriate taxonomic level? Is this areasonable approach with regard to speed and efficiency?Something Like:SELECT sample_id, 'Get Name Function(Rank, TSN)', Sum([count]) ASNoTaxa FROM dbo.tblbenthicResults could then be grouped and summed on the Name, to summarisedata for each sample/taxa.Is this a reasonable approach? Or is there a better one? Did I explainthe problem well enough?Thanks in advance,Tim
View 6 Replies
View Related
Aug 29, 2007
I am trying to access data from a database to print a report. The code I am using says I have a syntax error regarding the object person_info. Person_Info is a table value function on the SQL Server (2005). Does anybody see a problem with this line of code? Thanks for your help! Regards, Steve
"INNER JOIN (SELECT * Person_Info FROM (" & Session("current_project") & ")) ON pc.personID_fk=pe.personID_pk " & _
View 4 Replies
View Related
Apr 8, 2014
I want to convert the data from Original Table to Reporting View like below, I have tried but not get success yet.
Original Table:
================================================================
Id || Id1 || Id2 || MasterId || Obs ||Dec || Act || Status || InstanceId
================================================================
1 || 138 || 60 || 1 || Obs1 ||Dec1 || Act1 || 0|| 14
2 || 138 || 60 || 2 || Obs2 ||Dec2 || Act2 || 1|| 14
3 || 138 || 60 || 3 || Obs3 ||Dec3 || Act3 || 1|| 14
4 || 138 || 60 || 4 || Obs4 ||Dec4 || Act4 || 0|| 14
5 || 138 || 60 || 5 || Obs5 ||Dec5 || Act5 || 1|| 14
View For Reporting:
Row Header:
Id1 || Id2 || MasterId1 || Obs1 ||Desc1 ||Act1 ||StatusId1||MasterId ||Obs2 ||Desc2 ||Act2 ||StatusId2 ||MasterId3||Obs3 ||Desc3 ||Act3 ||StatusId3||MasterId4||Obs4||Desc4 ||Act4 ||StatusId4 ||MasterId5||Obs5 ||Desc5 ||Act5 ||StatusId5||InstanceId
Row Values:
138 || 60 || 1 || Obs1 ||Desc1 ||Act1 ||0 ||2 ||Obs2 ||Desc2||Act2 ||1 ||3 ||Obs3||Desc3 ||Act3 ||2 ||4||Obs4||Desc4 ||Act4 ||0 ||5 ||Obs5 ||Desc5 ||Act5 ||1 ||14
View 6 Replies
View Related
Nov 28, 2005
Hi
i got errro mess "Invalid length parameter passed to the substring function" from this below. Anyone how can give me a hint what cause this, and how i can solve it? if i remove whats whitin thoose [] it works, i dont use [] in the code :)
colums:
VLF_InfectionDestination is nvarchar 254
SELECT TOP 10 tb_AVVirusLog.VLF_VirusName, COUNT(tb_AVVirusLog.VLF_VirusName) AS number
FROM tb_AVVirusLog INNER JOIN
__CustomerMachines002 ON tb_AVVirusLog.CLF_ComputerName = __CustomerMachines002.FalseName
WHERE (CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
(__CustomerMachines002.folder_id = @folderId) [OR
(CONVERT(varchar, tb_AVVirusLog.CLF_LogGenerationTime, 120) BETWEEN @fyear + @fmonth + @fday AND @tyear + @tmonth + @tday) AND
(tb_AVVirusLog.VLF_InfectionDestination LIKE N'%@%')]
GROUP BY tb_AVVirusLog.VLF_VirusName
HAVING (NOT (tb_AVVirusLog.VLF_VirusName LIKE N'cookie'))
ORDER BY COUNT(tb_AVVirusLog.VLF_VirusName) DESC
View 7 Replies
View Related
Jan 21, 2015
I'm attempting to convert some INSERT-EXEC structures into table-valued functions because the procedures are deeply nested and INSERT-EXEC doesn't like nesting (Error 3915: Cannot use the ROLLBACK statement within an INSERT-EXEC statement)
The procedure has a single select statement, so I created an inline table-valued function. When I ran it with sample data, I received this error (yes, twice):
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
After ruling out obvious mistakes, I started to deconstruct the select statement with its CTE and TVP. The result is the following, built in my local sandbox database:
CREATE TYPE test_list AS TABLE(a int);
GO
CREATE FUNCTION test_function (@p int, @theTable test_list READONLY)
RETURNS TABLE
AS
RETURN (
WITH cte
AS (SELECT a FROM @theTable)
SELECT cte.a
FROM cte);
GO
DECLARE @t test_list;
INSERT @t VALUES(1);
SELECT * FROM test_function(1, @t);
When I run this, I get the same error as noted above. I'm running on version 10.50.4000.0, Developer Edition. (2008 R2 SP2)
The function above does just about nothing and has redundancies because I stripped the actual function down to the essential elements to cause the error. The essential elements are:
- One of the parameters is a table-valued parameter (the UDTT definition does not seem to matter)
- The SELECT statement has a CTE
- The TVP is accessed within the CTE
- The outer FROM clause references the CTE
- There is also a scalar parameter on the function (scalar type does not seem to matter).
- The scalar parameter precedes the TVP in the parameter list.
So I have an easy work-around: put the TVP first in the parameter list.
View 5 Replies
View Related
Mar 14, 2006
I have a problem with inserting records into table when an indexed viewis based on it.Table has text field (without it there is no problem, but I need it).Here is a sample code:USE testGOCREATE TABLE dbo.aTable ([id] INT NOT NULL, [text] TEXT NOT NULL)GOCREATE VIEW dbo.aViewWITH SCHEMABINDING ASSELECT [id], CAST([text] AS VARCHAR(8000)) [text]FROM dbo.aTableGOCREATE TRIGGER dbo.aTrigger ON dbo.aView INSTEAD OF INSERTASBEGININSERT INTO aTableSELECT [id], [text]FROM insertedENDGODo the insert into aTable (also through aView).INSERT INTO dbo.aTable VALUES (1, 'a')INSERT INTO dbo.aView VALUES (2, 'b')Still do not have any problem. But when I need index on viewCREATE UNIQUE CLUSTERED INDEX [id] ON dbo.aView ([id])GOI get following error while inserting record into aTable:-- Server: Msg 8626, Level 16, State 1, Procedure aTrigger, Line 4-- Only text pointers are allowed in work tables, never text, ntext, orimage columns. The query processor produced a query plan that requireda text, ntext, or image column in a work table.Does anyone know what causes the error?
View 1 Replies
View Related
Mar 9, 2006
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies
View Related
Nov 2, 2006
Hi all,
I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got:
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9
System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled.
I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K?
Thanks, Jos
Here's a test script:
/*
----------------------------------------------------------------------------------------------------
T-SQL code to test creation of three types of function where the function name begins with "sys_".
Jos Potts, 02-Nov-2006
----------------------------------------------------------------------------------------------------
*/
PRINT @@VERSION
go
PRINT 'Scalar function with name "sys_" creates ok...'
go
CREATE FUNCTION sys_test
()
RETURNS INT
AS
BEGIN
RETURN 1
END
go
DROP FUNCTION sys_test
go
PRINT ''
go
PRINT 'In-line table-valued function with name "sys_" creates ok...'
go
CREATE FUNCTION sys_test
()
RETURNS TABLE
AS
RETURN SELECT 1 c
go
DROP FUNCTION sys_test
go
PRINT ''
go
PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...'
go
CREATE FUNCTION sys_tmp
()
RETURNS @t TABLE
(c INT)
AS
BEGIN
INSERT INTO @t VALUES (1)
RETURN
END
go
DROP FUNCTION sys_test
go
PRINT ''
go
/*
----------------------------------------------------------------------------------------------------
*/
And here€™s the output from running the test script in Query Analyser on our server:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Scalar function with name "sys_" creates ok...
In-line table-valued function with name "sys_" creates ok...
Multi-statement table-valued function with name "sys_" generates error 1706...
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11
System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the function 'sys_test', because it does not exist in the system catalog.
View 3 Replies
View Related
Jul 21, 2004
Hiya folks,
Having a problem with a view.
I'm using the syntax
Right('000' & Myfield,3) to 'pad' out the results.
ie if MyField contained 45 the answer should be : 045
but i'm getting the results of : 0
Stuck!! Any offers kind people??
View 4 Replies
View Related
Sep 18, 2007
SELECT *
FROM dbo.Objects
WHERE (UserRights & dbo.GetUserRights()) > 0)
The purpose of this view is to implement row level security. The Field, UserRights is a BigInt and holds up to 64 bits corresponding to the user or group that has a right to view this record.
The Function GetUserRights() is a function which returns an Int corresponding to the user rights of the person who is currently logged in. In a bitwise fashion, it then "ands" them together to see if the value is greater than 0. If so, the user has rights to the record.
If I hardcode a value to replace the function, the Select statement is very fast. However, if I use the function it is extremely slow. I believe this is due to the function getting run once for each row of the table, instead of just once.
Is there a way to rewrite this so that it only runs once? I could do this in a stored procedure, but I need it in a view so that I can use replace "Objects" (the table) with "vwObjects" the view.
Thanks,
Greg
View 6 Replies
View Related
Dec 5, 2007
Hi, Is there anyway to create a view within a Function? The code is as below. I execute the code between "BEGIN" and "END". SQL Analyzer report error that said
'CREATE VIEW' must be the first statement in a query batch.
I could make the variable constant in SELECT statement, but I'm wondering if there is a way to make CREATE VIEW as part of code piece.
CREATE Function GetCommonFailurs()
AS
BEGIN
IF OBJECT_ID(N'CommonFailures') IS NOT NULL
DROP VIEW CommonFailures
DECLARE @Run1Result as char(4), @Run2Result as char(4);
SET @Run1Result='Fail';
SET @Run2Result='Fail';
CREATE VIEW CommonFailures
AS
SELECT Run1Failures.RunID as Run1ID,
Run2Failures.RunID as Run2ID,
@Run1Result as 'Run1Result',
@Run2Result as 'Run2Result',
Run1Failures.SmartyDOTXMLFilePath as Run1SmartyFilePath,
Run2Failures.SmartyDOTXMLFilePath as Run2SmartyFilePath,
Run1Failures.SDET as SDET,
Run1Failures.CommandLine as CommandLine,
Run1Failures.OutputFilePath as OutputFilePath
FROM Run1Failures
INNER JOIN Run2Failures
ON Run1Failures.TestID = Run2Failures.TestID
END
View 4 Replies
View Related
