T-SQL (SS2K8) :: Query Works Static But Does Not Dynamically
Aug 28, 2014
I have an SSIS package that puts together a series of queries to check all text fields in all tables of a database for a set of "invalid" characters. I know the root query works as we have been running it manually for quite a while. However, now that I have changed it to build dynamically, I cannot figure out why it does not work.
Here is the code. The EXEC (@sSQL) returns 3 records. It just so happens that these 3 records are the only ones in the database with a '?' in the field. The very bottom statement returns 0 records. I cannot figure out what the difference is. When I do PRINT (@sSQL), it looks fine to me, except that @Pattern is fully printed.
DECLARE @Pattern NVARCHAR(MAX)
DECLARE @sSQL NVARCHAR(MAX)
DECLARE @1 nvarchar(max)
DECLARE @2 nvarchar(max)
DECLARE @loop int
[code].....
View 9 Replies
ADVERTISEMENT
Feb 22, 2006
I'm using Delphi 2006 to create a DLL which will be integrated into SQL 2005. It's been a long road and I've made a lot of headway, however I am currently having the following problem creating the stored procedure:
My dll name is 'Crystal_Reports_Test_01'
In the DLL, my class is named 'Class01'.
In the DLL, my procedure is named 'TestMe'
I've managed to integrate the DLL into SQL using the following statement:
CREATE ASSEMBLY TEST_ERIC_01
AUTHORIZATION dbo
FROM 'c:mssqlassembliescrystalreports.dll'
WITH PERMISSION_SET = UNSAFE
I am attempting to create the stored procedure which points to the 'TestMe' method inside of the DLL. FYI: 'CrystalReports' is the namespace above my class that I had to add in order to get it to locate the class. The following code is used to create the stored procedure:
create procedure dbo.Crystal_Reports_Test_01(
@Parm1 nvarchar(255)
)
as external name TEST_ERIC_01.[CrystalReports.Class01].TestMe
But I get the following error:
Msg 6573, Level 16, State 1, Procedure Crystal_Reports_Test_01, Line 1Method, property or field 'TestMe' of class 'CrystalReports.Class01' in assembly 'CrystalReports' is not static.
I'm not sure what this means exactly. I think it means the method (the procedure) is not using Static method binding but should be. I have no idea what this really means or how to accomplish this in the DLL - or if I'm even going about this in the right way.
Any help would be appreciated ! I'll post the Delphi code (DLL) below.
Thanks,
Eric Gooden
library CrystalReports;uses System.Reflection, System.Runtime.InteropServices;...................type Class01 = class public procedure TestMe([MarshalAs(UnmanagedType.LPWStr)] var sVarString: wideString); export; end;procedure Class01.TestMe([MarshalAs(UnmanagedType.LPWStr)] var sVarString: wideString); export;begin sVarString:= 'Lets change the value and see if the stored proc. gets the change.';end;end.
View 4 Replies
View Related
Jun 26, 2014
A user is wanting to run a query against the current 3 active databases. These change every month on the 1st e.g. Test-06-14. Therefore I want to set up a dynamic query which will always use the current database. I believe I am almost there but I cannot set the USE @DatabaseName dynamically yet.
DECLARE @DB_Name varchar(100)
DECLARE @DatabaseName varchar(100)
DECLARE @Command nvarchar(200)
DECLARE @Command2 nvarchar(200)
DECLARE database_cursor CURSOR FOR
[Code] ....
View 9 Replies
View Related
Sep 5, 2014
I am having SP which gives, two result sets. The columns which are coming from result sets are also dynamic.
i.e. some time 5 columns and some time 10 columns.
Now I want to load this output into 2 different tables on daily basis. This would be truncate/delete table and load again.
Now my problem is that as I am not sure about columns, Is it possible to create table(Physical Table) depends on output of SP, and after load data into it.
During each load we can drop table, No issue and we can handle this through SSIS Package.
View 2 Replies
View Related
Sep 2, 2014
I have table of standardized data that I'm merging into from an Import table.
This import table may have the [ContactName]. I one row in the import table has the contact name then every row would have the contact name.
What I'd like to be able to do is something like this in the UPDATE portion of the Merge (I don't care about the insert phase, since I can insert a null [ContactName] since I'm not overwritting an existing [ContactName]
MERGE INTO [dbo].[Standardized] AS [target]
USING [dbo].[ImportDestination]
AS [Source]
ON [Source].[Key] = [Target].[Key]
WHEN MATCHED
THEN UPDATE
SET[ContactName] = CASE WHEN source.[ContactName] IS NOT NULL THEN source.[ContactName] else target.[ContactName];
Is anything like that possible and if the code above works it would only work on a row by row bases. Is it possible to figure out if any [ContactName] has data and if so possibly overwrite an old [ContactName] with a null?
View 1 Replies
View Related
Nov 9, 2014
I have created dynamic sql to declare variables based on columns from the table and i set values to those variable now here is the issue . i want to check the variable values how do i do that dynamically
drop table test
create table test
(
id varchar(10) not null,
col1 varchar(10) ,
col2 varchar(10)
[Code] .....
Now my next step is verify if the variable is blank or not how do i do that ?
How do i verify all of the columns one after the other .
I am after the statement like this dynamically
-- IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' '
--IF NOT (@col2 = '') THEN set @SQL = @SQL + '[col2] = ' + @col2 + ' '
I need to check if the columns are blank or not dynamically as i do not want to hard code the column names there.
View 4 Replies
View Related
May 21, 2015
I have two tables:
tblServer
tblInstance
tblServer represents a server, tblInstance represents any SQL Server instances present on the server. They're related thru srvID.
I'd like to write a query that gives me servers with all instances, on one row. This dataset will populate a List in an SSRS report.
I have this but it's clunky and does not provide for more than 2 instances on the server. I'd like this to account for any number of instances all on one row without having to hard code multiple joins:
with serverInfo as
(
select
srv.srvType as Type
,srv.srvName as ServerName
,srv.srvIP as ServerIP
,ins.instNetName as InstanceNetName
[Code] .....
View 3 Replies
View Related
May 27, 2015
UPDATE Report
SET MSYear= casewhen MSDate > '2011/06/30' and MSDate < '2012/07/01' THEN '2012'
when MSDate > '2012/06/30' and MSDate < '2013/07/01' THEN '2013'
when MSDate > '2013/06/30' and MSDate < '2014/07/01' THEN '2014'
when MSDate > '2014/06/30' and MSDate < '2015/07/01' THEN '2015'
when MSDate > '2015/06/30' and MSDate < '2016/07/01' THEN '2016'
when MSDate > '2016/06/30' and MSDate < '2017/07/01' THEN '2017'
End
Actually our business year starts from 1st july and for this code I need function which is dynamically updates the every year for example 2014-07-01 to 2015-06-30 this is called as a 2015 year like this I need function which will dynamically update a year.
View 4 Replies
View Related
May 19, 2015
DELETE FROM Report_temp2
WHERE MSSalesID in
( Select Report_temp.MSSalesID FROM Report_temp)
DELETE FROM Report_temp
WHEREMSDate < '2015-07-01'
Actually the year stating form july.
Q1 is july,aug,sep.
Q2 is oct nov,dec.
Q3 is jan,feb,mar.
Q4 is april, may,june.
So what I need is dynamically I want to delete the data every year prior to current year.
View 4 Replies
View Related
May 27, 2015
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @CIR AS tmp On t.RowId = tmp.Max_RowId
[Code] ....
But CASE statement is not working...
View 7 Replies
View Related
Mar 28, 2007
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
View 4 Replies
View Related
Apr 30, 2007
Hello everybody,
I'm developing a report using the following structure :
declare @sql as nvarchar(4000)
declare @where as nvarchar(2000)
set @sql = 'select ....'
If <conditional1>
begin
set @where = 'some where'
end
If <conditional2>
begin
set @where = 'some where'
end
set @sql = @sql + @where
exec(@sql)
I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.
I realize that when I cut off the if clauses, then it works at Reporting services.
Does anybody know what is happening?
Why the query works in query analyser and doesn't work in Reporting Service ?
Thanks,
Maurício
View 2 Replies
View Related
Feb 27, 2014
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
View 6 Replies
View Related
Jul 30, 2015
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
View 13 Replies
View Related
Sep 28, 2006
Hi,First post so apologies if this sounds a bit confusing!!I'm trying to run the following update. On a weekly basis i want toinsert all the active users ids from a users table into a timesheetstable along with the last day of the week and a submitted flag set to0. I plan then on creating a schduled job so the script runs weekly.The 3 queries i plan to use are below.Insert statement:INSERT INTO TBL_TIMESHEETS (TBL_TIMESHEETS.USER_ID,TBL_TIMESHEETS.WEEK_ENDING, TBL_TIMESHEETS.IS_SUBMITTED)VALUES ('user ids', 'week end date', '0')Get User Ids:SELECT TBL_USERS.USER_ID from TBL_USERS where TBL_USERS.IS_ACTIVE = '1'Get last date of the weekSELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 6)I'm having trouble combing them as i'm pretty new to this. Is the bestapproach to use a cursor?If you need anymore info let me know. Thanks in advance.
View 4 Replies
View Related
Aug 4, 2004
Hi I have a following SP that does not return results. Could somebody tell me what I am doing wrong here.
CREATE procedure dbo.wfiDocuments_Get
(
@ModuleIdint,
@PortalIdint,
@IsActivebit=NULL
)
AS
declare @SQL nvarchar(4000)
SET @SQL =
'select
DocumentId,
ModuleId,
PortalId,
CreatedByUserId,
''CreatedByUserName'' = Users.FirstName +'' '' + Users.LastName,
CreatedDate,
Title,
Category,
Syndicate,
Clicks,
Size,
Description,
FileName,
VPath,
IsActive
from wfiDocuments
left outer join Users on wfiDocuments.CreatedByUserId = Users.UserId
where PortalId=@Portal and ModuleId =@Module'
IF @IsActive IS NOT NULL
SET @SQL = @SQL + ' AND IsActive=@Active'
SET @SQL = @SQL + ' ORDER BY Title'
EXEC sp_executesql @SQL,
N'@Portal INT,@Module INT,@Active bit',
@PortalId,@ModuleId,@IsActive
GO
View 11 Replies
View Related
Aug 27, 2005
Hi
The query below with table join works on my dev server sql server MC
v2, but returns only NULL values for Questiona and Module on the live
server MC v1.2, is this error to do with the version or something else
like relationships?
SELECT
Induction_Module.Induction_Module, Induction_Questions.Question,
Induction_AnswerIncorrectStats.AnswerIncorrectNo,
Induction_Questions.AnswerCorrect,
CAST(Induction_AnswerIncorrectStats.DateAnswered AS VarChar(11)) AS
DateAnswered
FROM Induction_Questions LEFT OUTER JOIN
Induction_Module ON Induction_Questions.InductionModuleID =
Induction_Module.Induction_ModuleID RIGHT OUTER JOIN
Induction_AnswerIncorrectStats ON Induction_Questions.QuestionID =
Induction_AnswerIncorrectStats.QuestionID
Regards
Steve
View 4 Replies
View Related
Mar 4, 2007
I have a query that works fine but fails as a sproc.
QUERY:
SELECT UserName, ProfileId, FirstName, LastName
FROM dbo.CustomProfile JOIN dbo.aspnet_Users
ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId
WHERE UserName = 'Brown'
SPROC:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetProfileId]
@UserName nvarchar
AS
SELECT UserName, ProfileId, FirstName, LastName
FROM dbo.CustomProfile JOIN dbo.aspnet_Users
ON dbo.CustomProfile.UserId = dbo.aspnet_Users.UserId
WHERE UserName = @UserName
The query returns results. In SQL Server Management Studio when I execute the sproc and enter the value Brown the sproc returns no values; i.e. 0
View 2 Replies
View Related
Mar 15, 2006
The following query works in SQL Server 2000 but gives follwoing error in SQL Server 2005
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.
=======================
SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@%'
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
AND NOT EXISTS (SELECT VWCONTACT.ID
FROM (SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ) NESTEDQUERY1
WHERE VWCONTACT.ID = NESTEDQUERY1.ID)
when I modify the query like the following in SQL Server 2005 it works. Now the problem is since it is adynamically generated query from our application based on users selection of criteria, it means a lot to us to change the code.
PLEASE HELP....
=====================================================
SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL
AND VWCONTACT.EMAIL LIKE '%@%'
EXCEPT
((SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) >= 77002) TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) <= 77099) TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77336)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77338)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77345)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77365)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77396)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77489)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77504)
OR (CONTACTATTRIBUTEID = 22943
AND ISNUMERIC(ATTRIBUTEVALUE) = 1
AND CONVERT(float,ATTRIBUTEVALUE) = 77598)) ATTR
ON ATTR.CONTACTID = VWCONTACT.ID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL )
UNION
(SELECT DISTINCT VWCONTACT.[ID] ID
FROM VWCONTACT
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 23102
AND ATTRIBUTEVALUE = 'Houston') TMP0
ON VWCONTACT.ID = TMP0.CONTACTID
JOIN (SELECT CONTACTID
FROM VWCONTACTATTRIBUTEVALUE
WHERE CONTACTATTRIBUTEID = 22944
AND ATTRIBUTEVALUE = 'TX') TMP1
ON VWCONTACT.ID = TMP1.CONTACTID
WHERE VWCONTACT.ACCOUNTID = 2615
AND VWCONTACT.VIRTUALDELETIONDATE IS NULL ))
View 3 Replies
View Related
May 11, 2006
I have a simple ASP.NET application that allows the user to enter a Query in a TextArea and submit that to the database. Queries that contain Integer, String or other filters seem to work fine, but when the Query contains a Date or DateTime I have problems.
Here is an example that works fine in Query analyzer but fails when executed in ASP.NET.
"select top 10 * from Subscribers where StartDate < '09-03-2002'"
In Query Analyzer no problem, in ASP.NET I get this error...
Line 1: Incorrect syntax near ';'
However if the Query uses '=' instead of a '<' or '' then it works without error
"select top 10 * from Subscribers where StartDate ='09-03-2002'"
Any help would be appreciated
View 3 Replies
View Related
May 29, 2007
Hi,
I'm using IsNULL(<insert something SQL witty here>) to zero out a derived column if there's no return result. When I submit my query in Management Console the results come back perfectly, when I send it over as a stored procedure (as an alter or create) then execute, my last few columns come back 0 not populated as in the query run. Anyone know why this happens?
View 9 Replies
View Related
Jun 23, 2006
Getting a weird error while trying out a query from Access 2003 on aSQL Server 2005 table.Want to compute the amount of leave taken by an emp during the year.Since an emp might be off for half a day (forenoon or afternoon), havethe following computed field:SessionOff: ([ForenoonFlag] And [AfternoonFlag])The query works fine when there's no criterion on SessionOff.However, when I try to get the records where the SessionOff equals 0, Iget the following error:~~~~~ODBC--call failed. [Microsoft][SQL Native Client][SQL server]Incorrect syntax near the keyword 'NOT'. (#156)~~~~~I checked the SQL of the Access query, but there's no NOT anywhere init:~~~~~SELECT tblWorkDateAttendance.*FROM tblWorkDate INNER JOIN tblWorkDateAttendance ONtblWorkDate.WorkDate = tblWorkDateAttendance.WorkDateWHERE (((([ForenoonFlag] And [AfternoonFlag]))=0) AND((tblWorkDateAttendance.WorkDate)<Date()) AND((Year([tblWorkDate].[WorkDate]))=Year(Date())) AND((Weekday([tblWorkDate].[WorkDate])) Between 2 And 6) AND((tblWorkDate.HolidayFlag)=False));~~~~~What gives?
View 4 Replies
View Related
Jun 7, 2008
Hi....I am fresh to asp.net .... i want to get the Request.QueryString Value Dynamically in SQL Query ... I have attached the coding of two files please gothrough and give the solution..First File : -actyearwise.aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="actyearwise.aspx.cs" Inherits="acts_actyearwise" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:FormView ID="FormView1" AllowPaging="false" DataSourceID="AccessDataSource1" Width="80%" align="center" runat="server"> <ItemTemplate> <tr style="font-family: Arial; font-size: small;"> <td align="left" width="7%"> <a id="href1" href="actcheck.aspx?2007" target="_self">2007</a> </td> <td align="left" width="7%"> <a id="A1" href="actcheck.aspx?2006" target="_self">2006 </a> </td> <td align="left" width="7%"> <a id="A2" href="actcheck.aspx?2005" target="_self">2005 </a> </td> <td align="left" width="7%"> <a id="A3" href="actcheck.aspx?2004" target="_self">2004 </a> </td> </tr> </ItemTemplate> </asp:FormView> <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM actindex "></asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content> actyearwise.aspx.csusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class acts_actyearwise : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { }}// This page works Properly... In the actyearwise.aspx ..just some years with link... i have entered ...the href page is actcheck.aspx(below).. In this page i want the years dynamically.. please go throughSecond file : -actcheck. aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="actcheck.aspx.cs" Inherits="acts_actfiles_actcheck" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" DataKeyNames="actno" DataSourceID="AccessDataSource1" Width="100%" AllowSorting="true"> <Columns> <asp:BoundField HeaderText="actno" Visible="False"/> <asp:TemplateField SortExpression="actno"> <ItemTemplate> <table width="80%" align="center"> <tr> <td align="left" width="3%" colspan="3"> <%# Eval("actno") %> </td> <td align="left" width="75%"> <a id="href1" href="actshorttitle.aspx?<%# Eval("actid") %>" target="_self"> <%# Eval("actname") %> </a> </td> </tr> </table> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM actindex where actyear = 'check' "> </asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content> actcheck. aspx.csusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class acts_actfiles_actcheck : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { String check = Request.QueryString.ToString(); } protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { }} The text i had bold and italics in the actcheck.aspx... is the problem..... From the actyearwise.aspx .. i was passing the actyear value ..... In actcheck,aspx .. i want to get it dynamically in SQL Query...... Please give the solution .. I am using the SQL database.Table name actindexfields nameactnoactnameactidactyear In the actyear field i have different years .... Thanks in advance
View 5 Replies
View Related
Apr 23, 2002
I'm trying to dynamically set the TOP value in a query, but it doesn't work. Any other way it can be done?
Doesn't work:
declare @sometopvalue int
set @sometopvalue = 100
select top @sometopvalue
field1,
field2,
field3
from table1 ORDER BY BRDCST_DT_TM field1 desc
View 1 Replies
View Related
Mar 31, 2004
hi all
I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.
any one know why that might be the case??
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
i've included the union query here for completeness of the question
begin
declare @current_date datetime
set @current_date = GETDATE()
select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc
end
View 1 Replies
View Related
Jul 23, 2015
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,
[Code] ....
View 3 Replies
View Related
Mar 7, 2008
Hi i have a script works in sql query analyzer;
declare @id decimal
declare mycur CURSOR SCROLL for select myRowID from myTable order by myRowID
open mycur;
Fetch ABSOLUTE 30 from mycur into @id
close mycur;
deallocate mycur;
select @id
this script turns me a value.
i create a stored procedure from above script and its syntax is ok;
CREATE PROCEDURE SELECT_MyRow
AS
declare @cur cursor
declare @RowID decimal
set @cur = CURSOR SCROLL
for select myRowID from myTable order by myRowID
open @cur
Fetch ABSOLUTE 30 from @cur into @RowID
close @cur
deallocate @cur
select @RowID
GO
my c# code using stored procedure is below;
Code Snippet
try
{
OleDbCommand cmd = new OleDbCommand("SELECT_MyRow", myconnection);
cmd.CommandType = CommandType.StoredProcedure;
myconnection.Open();
OleDbDataReader reader = cmd.ExecuteReader();
MessageBox.Show(reader.GetName(0));//here fails
while (reader.Read())
{
MessageBox.Show(reader.GetDecimal(0).ToString());
}
reader.Close();
myconnection.Close();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
The code above fails because reader reads no values, error message is "No data exists for the row/column"
but i know exists. Can anyone help me, what is the difference between stored procedure and script ?
View 4 Replies
View Related
May 11, 2007
We are running MS RS and SQL Server 2000 SP3.
We have one LEDGER, where all the daily activities are stored. The LEDGER table has 4 indexes (1 clustered and 3 non-clustered). To get AR we use this table.
Well problem is some times in 1-2 months, any simple AR query takes a long time and every other client gets slow response (queries are very slow or sometimes block).
If we DROP any index on LEDGER table and again put it back (RECREATE), all our queries work fine and faster. This goes on till 1-2 months, till we see the same issue again.
This is a classic case happened today. Queries were running fine till morning 8 AM. We upload some 50 thousand records to Ledger table (Data Conversion). Well after 30 mins, all simple AR queries started taking a long time. We DROPPED an index in LEDGER table and everything was faster....Just to be same we added back the same index again.......everything is Faster.....
What is this. ....is it our QUERY, index or huge Transactions or no free space ???
We are scheduled to run SP4, next week. But is there any solution in the mean time on what is this?
Also is they any way to KILL all SQL server processes that take more than a mins. We just don't want ALL our client to Slow down because of one query????
Thanks,
View 3 Replies
View Related
Jul 17, 2006
I have a dts that works from the command prompt, but hangs in Query Analyzer. Here's the code inside Query Analyzer:exec master..xp_cmdshell 'dtsrun /S BFHSQL4 /N vhl_dts_14144b /E'From the command prompt, it's just:dtsrun /S BFHSQL4 /N vhl_dts_14144b /EAny ideas what's wrong? Thanks for your help!
View 3 Replies
View Related
Feb 21, 2008
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasource and a gridview, no data is displayed.anyone know what my problem is?
View 1 Replies
View Related
Nov 9, 2006
Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance
View 1 Replies
View Related
May 30, 2007
I can run the following query in Management Studio, but get the error listed below when I run it from the data tab in Reporting Services:
declare @starttime as datetime
declare @endtime as datetime
declare @timezone as integer
declare @date as datetime
set @timezone = 1
set @date = '5/1/2007'
set @starttime = dateadd(hh, @timezone, @date)
set @endtime = dateadd(d, 1, @starttime)
select @Starttime, @endtime from site
Error Message:
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
The variable name '@starttime' has already been declared. Variable names must be unique within a query batch or stored procedure.
------------------------------
ADDITIONAL INFORMATION:
The variable name '@starttime' has already been declared. Variable names must be unique within a query batch or stored procedure. (Microsoft SQL Server, Error: 134)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=134&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
What I am trying to accomplish is the ability for users to select which time zone they want the data in the report to display in. To do this, I created a timezone parameter that has the offset from Central Time (which is how all data is stored in our database).
Any help would be greatly appreciated!
View 4 Replies
View Related
Mar 28, 2008
I have a simple update statement that is running forever in SQL 2005 but works fine in SQL 2000. We have a new server we put SQL 2005, restored db. The table in question WEEKLYSALESHISTORY I even re-indexed all the indexes and rebuilt the stats as well. But still no luck, still running extremely long. 1 hour 20 minutes.
I'll try to give you some background on these table. Weeklysalehistory has approx 30 fields. I have 11 indesxes set up weekending date being one of them. And replication control has index on lasttrandatetime as well. So I think my indexes are fine.
/* Update WeekEnding Date for current weeks WeeklySales Records */
Update WeeklySalesHistory set
weekendingdate =
(SELECT LastTransDateTime from ReplicationControl
where TableName = 'WEEKHST')
where weekendingdate is null
Weekly sales has approx 100,000,000 rows
Replication control has 631,000 (Ithink I can delete some from here to bring it down to 100 or 200 records) Although I don't think this is issue since on 2000 has same thing and works fine.
I was trying to do this within SSIS and thought that was issue. I am new so SSIS but it runs long even if I just run it as a job with this simple Update statement so I think its something with tables, etc that is wrong.
One thing on noticed if I look at the statistics in SQL Server Management studio there is a ton of stats. some being statistics on indexes which makes sense then I have a ton of hind_113_9_6 and simiiar one like this. I must have 90 or so named like this. Not sure how to check on SQL 2000 all the stats to see if they moved over from there or what. I checked a few other tables and don't have all these extra stats. Could this be causing the issue do I need to delete all these extras? Any help would be greatly appreciated.
Stacy
View 5 Replies
View Related
