Issue Getting Notified
I have a WCF service that is hosted in a Windows service. It is polling the Service Broker queue and waiting to retrieve messages that are getting posted to the queue. All of the messages get posted to a queue from a trigger that fires on a table. I now added the code that writes the message to the queue to a stored procedure that i have. I run the sproc and the queue gets the message, but the WCF service never seems to get the notification, although the message is no longer in the queue. Is there something different with the way that works in a trigger compared to a sproc?
FYI - The messages that are sent to the queue from the triggers are getting picked up by the application.
Thanks
-paul
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
How To Be Notified About New Data ???
Dear all, I have a windows service which store data to a database table named "HistBuffer" (under an SQL server Express 2005) . Then I have a library which is in charge to collect those stored information in HistBuffer and proceed them. The tricky thing here ( may be not so tricky) is that as long as a new row is inserted to this HistBuffer, my library need to be notified in a way and then can request the data. how can I do this notification from new rows to my library ? thnaks for help regards serge
View Replies !
View Related
SSIS Issue Data Type Issue While Loading From Oracle To SQL
Hi, I have SSIS package which is extracting data from oracle. I am using always use defualt code page =true and Defualt code page value=1252. While transferring values in to target table the package is success but when I check the decimal values For example I have 488.9602 or 56.908 values in oracle table but when they come to SQL server they are rounded as 488.0000 or 56.000. How can I populate the correct values from source to target The target table has data type decimal(26,4). Also when I check in the preview of source table I am able to see correct values. This is happening during the transfer only.
View Replies !
View Related
Cpu Issue
i have a 8 cpu server and two application ok, when i run the firts application from my computer and i run sp_who2 in the sql server, i get just 1 spid but when i run the second application in my computer again i get 8 cpu spid. why is happening this?
View Replies !
View Related
DTS Issue
Our ERP system generates this report every hour and spits out an xls file.I built a DTS package to run behind it and drop the information into a SQL table. It works fine but for one small detail...There is a date in the xls file, and in the raw file, it looks fine. But when it gets to the database, the field is null. At first I was just copying it as it was... then I had to add a query and tried to convert the field to SMALLDATETIME, to no avail.Does anyone have any suggestions as to WHY DTS won't copy or even SEE this date?(in the DTS, when I preview, the fields show blank.. but again, the date is in the xls file!)
View Replies !
View Related
SQL Issue
I am getting the following error on some SQL that I am runningOnly one expression can be specified in the select list when the subquery is not introduced with EXISTS.I have tested the SQL in a validator and all seems OK.I a basically doing a left join on two tables with the right half of the join being a subquery select statement, which also has a parameter in it.SELECT View_all_Included.Product_Name, View_all_Included.Description, View_Selected_gifts_on_list.Who_For, View_Selected_gifts_on_list.List_ID, View_all_Included.AP_Product_ID,FROM View_all_IncludedLEFT OUTER JOIN View_Selected_gifts_on_list ON View_all_Included.AP_Product_ID = (SELECT View_Selected_gifts_on_list.List_ID, View_Selected_gifts_on_list.Who_For FROM View_Selected_gifts_on_list WHERE (View_Selected_gifts_on_list.List_ID = @GGLLID))Have you any idea how to correct this query so it doesn't fall over on this statement.
View Replies !
View Related
UDF Issue
Hey guys and gals. I have a function I created on sqlserver that takes a date I want to pass to it and calculate 2 other dates. I want to then take those dates and plop them into two text boxes on my webform. I have searched high and low and found a few suggestions on how to code this. However, I get an error saying I haven't passed enough arguments to the function. Here's my code for the function on SqlServer and the code for calling the function on the page: <code> CREATE FUNCTION DueDate (@dtRequestDate datetime, @days int) RETURNS datetimeASBEGINdeclare @counter int set @counter = 1 --declare a variable to hold the ending datedeclare @enddate datetime --set the end date to the start date. we'll be -- incrementing it for each passing business dayset @enddate = @dtRequestDate /*Start your loop.While your counter (which was set to 1), is less than or equal to the number of business days increment your end date*/WHILE @counter <= @days BEGIN --for each day, we'll add one to the end dateset @enddate = DATEADD(dd, -1, @enddate) --If the day is between 2 and 6 (meaning it's a week --day and the day is not in the holiday table, we'll --increment the counter IF (DATEPART(dw, @enddate) between 2 and 6) AND (@enddate not in ( select HolidayDate from Holidaytbl where [HolidayYear] = datepart(yyyy,@enddate) ) ) BEGIN set @counter = @counter + 1 END --end the while loopEND --return the end dateRETURN @enddate --end the functionEND </code> Here is what I have on the page: <code> Sub Button4_Click(sender As Object, e As EventArgs) Dim cmd As SqlCommandDim sdr As SqlDataReaderDim strSQL As StringDim fname As String Dim conn as SQLConnection= NEw SQLConnection("server=servername;trusted_connection=yes;database=mydb")strSQL = "SELECT dbo.DueDate("+ Request("dtRequestDate") +")"conn.Open()cmd = New SqlCommand(strSQL, conn)fname = cmd.ExecuteScalar()iwcontentdate.Text = fnameconn.Close() End Sub</code> When I run the page and click the button to fire this function I get "An insufficient number of arguments were supplied for the procedure or function dbo.DueDate" I have two textboxes: dtRequestDate and IWcontentdate. I plug a value into dtRequestdate and then click the button to call the proc in .net. What I would hope is that the value for iwcontentdate would return and be populated. I have another box as well but if I can get one to show, I'll be able to get the other.: <asp:TextBox id="dtRequestDate" tabIndex="15" width="100px" Runat="server"></asp:TextBox> <asp:textbox id="iwcontentdate" tabIndex="16" runat="server" width="100px"></asp:textbox> <asp:Button id="Button4" onclick="Button4_Click" runat="server" Text="Button" ></asp:Button> Can anyone walk me through fixing this? Or at least show me a good way to call this function? I read some places where you can call it through a stored proc. I tried to create but it bombed out (go figure!) Thanks guys. Cordell
View Replies !
View Related
Yet Another DTS Issue
All, I have searched for almost two weeks and have not found a solution yet. I have a DTS pkg on our sqlserver box that works fine in EM. I have it coded to my asp.net page. Here's the code to call it: default.aspx page <code> <%@ Page Language="VB" Debug="true" codefile="~/DTS.aspx.vb" Inherits="FileSend" %><%@ import Namespace="System.Data" %><%@ import Namespace="System.Data.SQLClient" %><%@ import Namespace="System.IO" %><%@ import Namespace="System.Web.UI.WebControls" %><%@ import Namespace="System.Web.UI.HtmlControls" %><%@ import Namespace="System.Web.UI.HtmlControls.HtmlInputHidden" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html><head></head><body> <form id="Form1" method="post" runat="server"> <asp:Button id="Button1" onclick="Button1_Click" runat="server" Text="Button"></asp:Button> <asp:Label id="Label1" runat="server" text="Label"></asp:Label> </form></body></html> </code> DTS.aspx.vb codebehind: <code> Imports DTSPartial Class FileSend Inherits System.Web.UI.Page Public Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Dim pkg As New DTS.Package pkg.LoadFromSQLServer("myservername", _ "sa", "", _ DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, _ "sa", _ "{Pkgid1}", _ "{Pkgid2}", _ "Pkgname", "") pkg.Execute() 'Label1.Text = "Package Executed" End SubEnd Class </code> When I run this locally (http://localhost:8080/Default.aspx) it works fine. The package fires succesfully. However, when I move the files to the server and try to run it, the code fires, but the DTS never runs. I know this because part of my DTS has an email generate and saves a file on a shared folder. Also, I have a success message that shows. Each time I run from the server, I get the success message as if it ran. Like I said, i have searched the web for a long time looking for answer and all of the sites I found showed HOW to run a DTS from .net but not WHAT to do if they just don't work. Desperate for help guys and gals. :) I hope I was detailed in my problem and listed everything you need to help me. TIA Cordell
View Replies !
View Related
IIS Issue
I just installed IIS on my windows xp pro machine and when I try to access awebsite on the local IIS server, I get the following error message:"You are not authorized to view this page"I have enabled "Anonymous access" inside Directory Security option of IISand I also granted full NTFS permissions to all users so that the IIS rootdirectory is readily accessible. However, I am still not able to access anywebsite that resides on the localhost.Please help.Thanks.Jess
View Replies !
View Related
XML Issue.
using ms sql 2000EXEC sp_xml_preparedocument @iTree OUTPUT, @docI get an error unless I remove the encoding attribute(<?xmlversion="1.0" encoding="utf-8" ?>) from my XML document prior torunning my stored procedure. So I need a way to strip this out via myC# code that prepares the output.Thanks.
View Replies !
View Related
Yet Another BCP Issue
Hello all,Background:I've posted a couple questions recently regarding using BCP to extractmany tables to text files. I've written an extract application thatbcp's about 100 tables from a database to text files. I've run into acouple limitations using BCP, but I finally landed on (what I thought)was a solid solution. I now call bcp passing a sproc name and aparameter or two and having that sproc execute the query for me,thereby doing the extracts to the proper files.Background-Detail:The sproc that executes the query seems to be working just fine forall but a couple tables. I have a need for extracting files in aparticular format (quoted and pipe-delimited, with quoted and pipedcolumn headers as the first row in the resulting file), and that'swhat my sproc does. For a given table name, I select all the columnsand interrogate their column type (systype) and wrap the properformatting around the particular field value (i.e. "|" + field_name +"|"). After building the proper SQL query, I execute it and theresults are bcp'd to the specified extract file.Issue:For some reason, any table that contains a bit field as the last fieldin the table design, BCP cannot execute the query, claiming there's asyntax error near the end of my query (unclosed quotation mark). Forbit field types in SQL Server, the select query that I build for theformat of the extract involves a SELECT..CASE statement to convert a 1to TRUE and 0 to FALSE. The odd thing is that the query that is builtin the sproc will work just fine in SQL Query Analyzer, as well asjust putting the desired query in a varchar variable in a sproc(without building it by looking up field types) and executing it viaBCP. BCP only croaks when I run the sproc as designed and the finalfield selected in the generated query has a type of 'bit'. There areother tables that contain bit fields and BCP seems to run the extractqueries just fine for those instances. The only difference being thatthose table designs don't contain a bit field as the last field.It's a tough thing to accurately describe, so please reply withfurther questions, and I'll try to help you better understand. But theroot issue is that the SAME EXACT query will work in query analyzer aswell as a stored procedure, but not when executing the storedprocedure via BCP.Anyone seen this type of inconsistency using BCP?Thanks for any help on this one./bc
View Replies !
View Related
DTS Issue
Hi, This is wiered problem I am facing when I execute a DTS package to fetch the information of oracle database to sql server database. The job was working fine earlier. this is what the error looks like: DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_3 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147008507 (80074005) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error Detail Records: Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 1100 Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: Timeout expired Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun OnError: D... Process Exit Code 2. The step failed. One more thing, I created the same type to package to transfer the contents to a text file for testing, the package executes in one system and does not executes in the current system.
View Replies !
View Related
DTS Issue
In Microsoft SQL DTS I'm trying to run a package that convert output to a excel spreadsheet. Following error appears: Error source: Microsoft Jet Database Engine Error Description: Failure Creating File. What has added to my frustrations is, once I go to the server and open up the DTS package and click on destination it allows me to create new table, but that is only temporary. If I run a schedule job at a later time it fails again with the above message.
View Replies !
View Related
BCP In Issue
First off, my database, data files, format files, etc. are all in good working order. I can BCP in a set of 500,000 records successfully. On a subsequent BCP where I am incrementally adding to the table, the BCP appears to upload the data to the target table, but when I do a count only the original 500,000 records are there. What could be causing this? I do get a handful of errors due to binary data in the file... Bob
View Replies !
View Related
Bcp -F Issue
I'm trying to figure out the why my bpc command to insert headers isn't working properly. Here's what I have. bcp "SELECT (setname + CONVERT(NVARCHAR(MAX), number)) as mergeData, sellprice, sellprice FROM MtgoData.dealer.CardsView c WHERE isfoil=0" queryout m: egularprices.txt -U beta -P gettowork -S tcp:192.168.86.100SQLEXPRESS,14430 -c -C ACP -t , -F "Product code, Price, List price" The error I get is: The -F flag must be followed by a positive integer. 'Product code, Price, List price' is not a legal positive integer. What's the proper syntax to getting the header to read Product code, Price, List price? Thanks for the help!
View Replies !
View Related
SUM Issue
Hey, all. I have this table: Code: Name---Visits---Date Joe --- 28 --- 2/1/2006 Mark --- 40 --- 2/1/2006 Joe --- 87 --- 2/3/2006 Mark --- 31 --- 2/4/2006 Diana --- 10 --- 2/2/2006 I want to display the results as: Code: Joe --- 115 Mark --- 71 Diana --- 10 I'm not sure how to go about it.
View Replies !
View Related
Clr Issue
Hi, am experiencing some problems when using CLR stored procedures. The CLR workflow is the following: 1- Fetchs data from certain view. 2- Creates email message using this data. 3- Send message using MS SQL 2005 procedure. The problems I experienced: A- Procedure exists if there is invalid SQL, and I can not catch generated exceptions. B- The procedure is working when I execute it from MS SQL studio, but it is failing when I call from Web application. After debugging it, I found that it is failing when called from web application, at the last phase when the MS SQL 2005 send mail stored procedure is to be executed, and specifically it fails when I open context connection, to execute procedure. What are the possibilities for such behavior, and how can I catch such exceptions at that point? Regards Rabeeh Abla
View Replies !
View Related
UNC Issue
I am having an issue running a .bat file using the execute proccess task in ssis. When I run it to path to a locally mapped drive it runs fine. When I try using a UNC path, the step turns green and executes, but nothing happens. What am I doing wrong and how do run I file on a UNC path from the execute proccess task editor?
View Replies !
View Related
WMI Issue
Hi everyone, Primary platform is XP SP2. We€™ve got a workstation which is not possible to install Sql25k client and BOL due to requirements complaining about WMI. Since WMI is not a separate piece we€™re totally stuck with this (to install again OS is not weight up by the moment) This script has been launched in order to solve it but results has been unsucessfully at all: @echo on cd /d c: emp if not exist %windir%system32wbem goto TryInstall cd /d %windir%system32wbem net stop winmgmt winmgmt /kill if exist Rep_bak rd Rep_bak /s /q rename Repository Rep_bak for %%i in (*.dll) do RegSvr32 -s %%i for %%i in (*.exe) do call :FixSrv %%i for %%i in (*.mof,*.mfl) do Mofcomp %%i net start winmgmt goto End :FixSrv if /I (%1) == (wbemcntl.exe) goto SkipSrv if /I (%1) == (wbemtest.exe) goto SkipSrv if /I (%1) == (mofcomp.exe) goto SkipSrv %1 /RegServer kipSrv goto End :TryInstall if not exist wmicore.exe goto End wmicore /s net start winmgmt :End Let me know how to fix this problem. TIA
View Replies !
View Related
64-bit Issue
I am running into a strange problem when trying to run my package against 64-bit version of SQL Server 2005. The package was initially developed with a CTP 2 version of SSIS. It has been running fine against a 32-bit bersion of SQL Server 2000. I created a new SSIS project, with the release version, and added the package mentioned above. Things still work fine against a 32-bit version of SQL Server 2000. However when I run the same exact package against a 64-bit version it doesn't work correctly. I am using a flat file connector - to pull data from a text file - and loading the data into a database table. Each time I run against the 64-bit version of SQL Server 2005 the number of nulls - for a particular colum - changes. In other words, the following query returns different counts each time the package is run - even when the underlying flat file data hasn't changed: select count(*) from some_table where email_address is null Any help would be much appreciated! Scott
View Replies !
View Related
Issue
i have a data source which is a weblogs in which data is stored in ncsa format using ssis i need to get that data to one of my database tables can u guys plz help me out in this. i.e to to build the ssis scenario thanks praneeth sample weblog 10.196.2.236 - wromero@kpmg.com [26/Jun/2007:12:04:44 -0400] "POST /krisk3/krisk3/(ea12r4f4nin0ta55h0suj445)/Evaluation/Evaluation.aspx HTTP/1.0" 200 42925
View Replies !
View Related
GAC Issue
I have .NET 1.1/Visual Studio 2003 and am trying to make the .NET C# code visible in SSIS -- I am a newcomer to GAC and did the following: Set up a strong-name key pair Put in the reference to it inside assemblyinfo Put in PreBuild event to uninstall in GAC and PostBuild to install in GAC All appeared fine Set up project in SSIS Put up Add Reference dialog and looked for project dll -- not there I see from above something about putting the assembly into c:windowsframework... I do not have a directory "framework" under "windows" Is there another place I should put the assembly>
View Replies !
View Related
ASP.net With SQL Issue
I have to develop asp.net application, which reads data from SQL database and insert data into SQL database. Basically I have to create asp.net form . Please can anyone tell me how do I start from scratch. Thank you.
View Replies !
View Related
CPU Issue - Between 80% To 100%
Hi, i've SQL 2005 SP2 64BIT on Windows Server 2003 SP2 64BIT. i've performace issue with my CPU,i've 2 CPU INTEL XEON 3.0GHZ and my CPU is always between 80% to 100%. i start the profiler to catch long TSQL that eats my CPU and i've noticed those 2 TSQL that runs,i don't know what they are. 1. SPtmtCompleted -- Encrypted text .Net SqlClient Data Provider sa 48219 214960 0 63470 63 2008-01-07 09:47:35.980 2008-01-07 09:48:39.450 1 master 29132155 HEVERPRI 0X01 0 0 heverpripri sa 37113059 0 0 -1 2 912616191 20801 - AQ 2 1 2. RPC:Completed exec sp_lckchainrn @DB=N'master' .Net SqlClient Data Provider sa 48219 214962 0 63471 63 2008-01-07 09:47:35.980 2008-01-07 09:48:39.450 0X00000000020000001A00730070005F006C0063006B0063006800610069006E0072006E003A00000082001800E7306E00760061007200630068006100720028 1 master 0 - OK 29132158 HEVERPRI 0X01 sp_lckchainrn 0 3 heverpripri sa 0 if you look at those 2 TSQL the CPU usage is 48219 and the READ is 214962. maybe anyone know what are those 2 TSQL? Thx
View Replies !
View Related
Another Row To Col Issue
Hi, I've got a Problem to craete a view which works like this: I've got this Table: USR_ID ATNAM ATVAL -------------------- --------------------- ------------- 2 USR_DEPARTMENT BS1 2 USR_MARITAL_STATE married 3 USR_DEPARTMENT BS1 3 USR_MARITAL_STATE single and I have to display it in a view like this USR_ID USR_DEPARTMENT USR_MARITAL_STATE ---------- ------------------- ------------------- 2 BS1 married 3 BS1 single maybe there will be additional "ATNAMS" in future. Has anybode an idea how i can achieve this? thanks in advance Raimund
View Replies !
View Related
Get The Min Value Issue
i have a table like this: PO V1 V2 V3 1 2007-01-01 Aa a 2 2007-02-01 Bb b 2 2007-03-01 Cc c 1 2007-01-01 Dd d 1 2007-05-01 Ee e i want get the Value of column V2,V3 , according minimal cloumn V1 ,in each group column PO. how can i write the SQL statement ? tahnks.
View Replies !
View Related
Same Issue
Hi, I'm facing the same problem with VSS Writer on a machine i test the installation. I had a similar error on another test machine but was with the documentation component or something like that. I end out reducing the number of components to install to avoid this errors as i really only needed the engine. My command is: -q /norebootchk /qn reboot=ReallySuppress addlocal=SQL_Engine,SQL_Data_Files instancename=ABC SQLAUTOSTART=1 SECURITYMODE=SQL SAPWD=€?abc€? And on the logs (C:Program FilesMicrosoft SQL Server90Setup BootstrapLOG) i get this: Component update returned a fatal error : 1714 Error Code: 0x800706b2 (1714)Windows Error Text: No protocol sequences have been registered. Source File Name: sqlncli.msiCompiler Timestamp: Thu Oct 5 12:22:33 2006 Function Name: RemoveExistingProductsSource Line Number: 1583Error: Failed to add file :"C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0002_LABA_.NET Framework 2.0.log" to cab file : "C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSqlSetup0002.cab" Error Code : 2Delay load of action "UploadDrWatsonLogAction" returned nothing. No action will occur as a result.Message pump returning: 1714 On the summary: Machine : LABAProduct : SQL VSS WriterError : The older version of Microsoft SQL Server VSS Writer cannot be removed. Contact your technical support group. --------------------------------------------------------------------------------Machine : LABA Product : Microsoft SQL Server VSS Writer Product Version : 9.00.3042.00 Install : Failed Log File : C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0003_LABA_SqlWriter_1.log Last Action : RemoveExistingProducts Error String : The older version of Microsoft SQL Server VSS Writer cannot be removed. Contact your technical support group. System Error 1612. Error Number : 1714 I only want to create a new instance with my definitions and only need the engine, why does it have to be messing arround with the vss writer? Any suggestions are welcome. Thanks.
View Replies !
View Related
Same Issue
I have uninstalled and reinstalled in a new location on the Computer as well as using the default instance. Still no Full-Text Language! Is there a manual way of installing a language? Nothing seems to be working and I still have yet to find any information from either MSDN or other forums. Thanks, Frank
View Replies !
View Related
Sql Sum Issue
Hi i have query which i need to add a sum to but not sure how as i keep getting a ambiguous error!! i need a sum(cost)as newcost from this query SELECT tmp.ID, tmp.Cost, tmp.DateofSM, tmp.MessageText, tmp.GroupMessageID, tmp.ClubID, tmp.RCODE FROM ( SELECT t1.ID, t1.Cost, t1.DateofSM, t1.ClubID, t1.MessageText, t1.GroupMessageID, t1.RCODE, ( SELECT COUNT(*) + 1 FROM SMS_Records WHERE GroupMessageID=t1.GroupMessageID AND ID <t1.ID ) AS 'RowNo' FROM SMS_Records t1 )tmp WHERE tmp.RowNo=1 AND DATENAME(month, DateOfSM)= @Date AND ClubID = @ClubID
View Replies !
View Related
DTS Issue - Please Help!
I'm kind of a rookie and having an issue with DTS in SQL v8 I'm trying to move data from a sql table to a text file (OLE DB for SQL Server -> text file destination) Transform Data Task Properties: SQL Query - want to select about 50 columns from the table defined in OLE DB source but if I select more than 30-35columns in the query it won't populate the destination - only have source on the Transformations tab. Also, when this happens if I click "define columns" it completely shuts down the console window. It works fine if I select 10-15 or so colums every thing is there and works as expected. The text file is setup as "fixed field", "ANSI", "{CR}{LF}, "First row has column names" is unchecked The text file is currently blank Any tips or insight would be greatly appreciated! Let me know if you need any more detail. Thanks in adavnce!!
View Replies !
View Related
Not Really A Sql Issue But !!!
I have a sql select script which runs at nine every morningnow at the moment it emails the results in a csv file format and this is running all under a dts job. What i would like to do is run the sql script script and have the results in the body of the email instead of the csv file. It sonly because few of the big boys in the company can't open csv files on there blackberrys and it would be easier to view the results with in the email. Any help would be great.
View Replies !
View Related
And Another Issue
So I now have my incproducts db with the correct categories. How do I copy all the information to the the products db? Or in this case 1 columns contents in 1 db to the another db column! I have tried: INSERT INTO products.pID select * from incproducts.impartno and get Server: Msg 208, Level 16, State 1, Line 3 Invalid object name 'pID'. Server: Msg 208, Level 16, State 1, Line 3 Invalid object name 'impartno'.
View Replies !
View Related
Procedure Issue
I can provide maxInt(mean howmany record) but how can I insert multiple values which is in table in .net server side code. means how can I pick up all those records in procedures in sql server 2005. what varaiable i can use like-array in sql server 2005 so that i can insert all records using loop. thanks
View Replies !
View Related
SQL Server Issue With ASP.NET
I'm a little new at this so bear with me... I understand that in order to use a SQL database for membership I need to define the connection string and run the aspnet_regsql application. Unfortunately I use a hosted SQL server and the only way it allows access is from code run on the web server. Is there a way I can upload the contents of an MDF file created in a separate but similar project to "trick" the app into thinking that this tool had been run? I use 1and1 for my webhost, do you have reccommenations as to a better choice? Chris
View Replies !
View Related
Issue With Raiseerror
I have an issue with the error message that is being raised in a stored procedure: RAISERROR('Error inserting Value %d into Order_Header_Reference_Code_XREF for Order_Header_ID %d and Reference_Code_ID %d', 10, 1, @Value, @OrderHeaderID, @ReferenceCodeID) Here is the result after executing my procedure with "@Value = 'test'" Error inserting Value 114994016 into Order_Header_Reference_Code_XREF for Order_Header_ID 10801 and Reference_Code_ID 1 114994016 should say 'test' right? Does anyone see an issue with my raiseerror statement?
View Replies !
View Related
Issue With UPDATE
This works when I do an INSERT, can anyone tell me why it does not work in this UPDATE? I get this error: Incorrect syntax near the keyword 'CASE'.UPDATE tblReceipts SET PaymentDate=@PaymentDate, TenderTypeID=@TenderTypeID, AmountPaid=@PaymentAmount, CreditCardType=@CreditCardType, ExpirationDate=@ExpirationDate, CASE WHEN @CreditCardType > 0 THEN ccNumber=EncryptByPassPhrase('Abrdarba!1234$',@ccNumber) ELSE ccNumber=@ccNumber END, LastUpdate=@LastUpdate, LastUpdateBy=@LastUpdateBy END
View Replies !
View Related
SQL Datatype Issue
Hi,Sorry if this isn't the right forum...I'm getting a "System.ArgumentException: Column requires a valid DataType" on the following line of code: dcUserAnswer.DataType = System.Type.GetType("SqlTypes.SqlInt32") I'm using SQL Server 2003 & ASP.NET 1.1 Thanks,Jens
View Replies !
View Related
Strange SQL Issue
Hi there I have this statement I have written as follows:strCommand = "SELECT * FROM tblstock WHERE Type='"&Statement &"' AND Description like'"&criteria &"%' OR Tag like'"&criteria &"%'OR Location like'"&criteria &"%' OR LAN like'"&criteria &"%' OR RAM like'"&criteria &"%' OR CD like'"&criteria &"%' OR OS like '" &criteria &"%' OR SN like'" &criteria &"%' OR DeviceStatus like'" &criteria &"%' ORDER BY " &sSortStr The variable "Statement" is passed into the sub as "PC" which means only records of type "PC" should be displayed along with any other criteria. The issue I'm having is that when I specify criteria I'm also recieving other types eg "Cameras" if they contain any of my criteria.. I can't understand how because in the statement I tell it only to display records of type "PC".. Anybody know what I'm doing wrong? Thanks
View Replies !
View Related
DateTime Issue With Some SQL
Hi, I'm having some terrible troubles with inserting DateTime into an MSSQL database and being able to search for data by date. It's all confusing me because of the damn US-style date formatting (no offence). I have a form that users fill out, and one piece of data happens to be the date (dd/mm/yyyy). I'm using a regex to make sure it can only be entered in the correct format. Now, I know that SQL doesn't have a 'Date only' datatype so I'm using the 'datetime' data type. When the user clicks Process, I use DateTime date = Format.ToDateTime(txtDate.Text) and then insert 'date' into the DB.When the date (say 11/10/2007) is inserted into the database, it shows up (in MS SQL Server Management Studio Express) as '11/10/2007 12:00:00 AM' which appears to be correct. I think it's correct because if I enter 31/01/2007 it shows up in SQL as '31/01/2007 12:00:00 AM', otherwise wouldn't it crash because there is no 01/31/2007? Anyway, the insertion *seems* to be working correctly, but it's the search function that's driving me crazy. If a user wants to search for data by date, they enter the date into the textbox and again I format it by doing: DateTime searchDate = Format.ToDateTime(txtSearch.Text)I then query the database with "SELECT * FROM tablename WHERE Date = '" + searchDate + "' but it doesn't return the correct results!If I want to see the results for data on 11/10/2007, I have to search for 10/11/2007 otherwise there will be no results shown. Also, if I search for 31/01/2007, it crashes with an error saying something about converting a char to the datetime datatype out of range.I have edited my Web.Config file so that it contains the globalisation tabs with my culture etc set to en-AU etc... I'd post my code here, but I'm at home now and the code is at work. I'll post it here soon as I'm sure you'll need it. Any help is *greatly* appreciated. This is really annoying...DateTime in .NET and SQL just don't mix for me.
View Replies !
View Related
Security Issue
Hi, I'm trying to create a stored procedure that checks if a user has entered a certain page before, and if so, deletes the role that enables that user to access the page after 24 hours (access is provided through web.config in the folder) . Using a table (userAccessControl) that simply contains the userName, roleName and the entranceDateTime, I want the procedure to check if the entranceDateTime for the user is less then 24 hours before the current time, and if NOT, delete the record for that user from the table, and executing the aspnet_UsersInRoles_RemoveUsersFromRoles stored procedure. Also, if the user has never entered the page before so the record does not exist in the table, it must be created by the stored procedure. So there is a stored procedure that is called through the webpage with the parameters: userName = User.Identity.NameroleName = "some_role"time = Now.Date() So the executing scheme looks something like this. Checks if user exists True > Check if user does not exceed 24 hour limit True > Delete user from UserAccessControl and execute stored procedure to delete user from the role False > Do Nothing False > Create user in UserAccessControl My question... how would this stored procedure look and what is the best way to use it within the page? Thanks in advance!!
View Replies !
View Related
JOIN Issue
Hello All... would love some help with the following SQL: I am joining 3 Tables on Accession Number, to retrieve data. The Tables are: ClinicalPatientClinicalSpecimenClinicalTestsRequested There is (1) record in the ClinicalPatient Table for Accession 6281. There are (3) records in the ClinicalSpecimen Table for Accession 6281. Each Accession has a SpecimenID (6281-01, 6281-02, 6281-03). When a Test is Requested, a record is written to the ClinicalTestsRequested table and a "1" is posted to the record in the "AddTest" Field.At this point there are (2) records in the ClinicalTestsRequested table that have the Value of "1" in the "AddTest" field.When I run the following query, all (3) records display, but the third one ALSO has a "1" displayed in the AddTest Field and it should be blank?? What am I missing here?? ClinicalTestsRequested table is the only one with the AddTest field. THANKS !! SELECT DISTINCT ClinicalPatient.PatientID, ClinicalPatient.MedRecord, ClinicalPatient.LastName, ClinicalPatient.FirstName, ClinicalPatient.Address1, ClinicalPatient.Accession, ClinicalSpecimen.SpecimenID AS CSSpecimenID, ClinicalSpecimen.Accession AS CSAccession, ClinicalTestsRequested.AddTest FROM ClinicalPatient INNER JOIN ClinicalSpecimen ON ClinicalPatient.Accession = ClinicalSpecimen.Accession INNER JOIN ClinicalTestsRequested ON ClinicalTestsRequested.Accession = ClinicalSpecimen.Accession WHERE (ClinicalPatient.Accession = 6281)
View Replies !
View Related
Group By Issue
Alright, so I'm trying to bind a list of pictures to a repeater but the sql I'm trying to write is killing me. The goal is simple: Write a query that returns all the pictures where the selected people are tagged but only return those pictures where all selected people are in them. So far I have the following SQL statement. SELECT picture.id, picture.name, picture.album_id, tag.id , tag.picture_id, tag.people_id FROM picture LEFT OUTER JOIN tag ON picture.id = tag.picture_idWHERE (tag.people_id = '1') OR (tag.people_id = '3')GROUP BY picture.idHAVING (COUNT(picture.id) > 1) Now the problem is that you have to include all the columns that appear in the SELECT statement in the GROUP BY clause otherwise it errors out (this query will do that) but if I include them all then I get zero results when I know I should have 1 result given this particular query's desired result. Now when I was working with mySQL you could leave out columns in the GROUP BY and mySQL would handle it but seems SQL Server is not so forgiving. Any ideas?
View Replies !
View Related
SQL Performance Issue
I have a table which is similar to this VehicleRef, Manufacturer, Model, Derivitive, Term, MilesPA, CH 1000, Audi, A3, 1.6 SE, 12, 10000, 104.991000, Audi, A3, 1.6 SE, 12, 20000, 102.991000, Audi, A3, 1.6 SE, 12, 30000, 102.991000, Audi, A3, 1.6 SE, 24, 10000, 102.991000, Audi, A3, 1.6 SE, 24, 20000, 102.991000, Audi, A3, 1.6 SE, 24, 30000, 102.991000, Audi, A3, 1.6 SE, 36, 10000, 102.991000, Audi, A3, 1.6 SE, 36, 20000, 102.991000, Audi, A3, 1.6 SE, 36, 30000, 102.99 The above all relates to 1 vehicle and there are about 500 vehicles, the primary key is vehicle ref. For my page of results I want to display each unique vehicle and the lowest CH price for that vehicle (and also the term and milespa relating to the lowest price) First I fill a datatable with the following query (lowerprice and upperprice are variables) SELECT DISTINCT vehicleref,manufacturer,model,derivative FROM tblFigures WHERE ch > lowerprice AND ch < upperprice This works fine and the query executes in under a second. The problem comes when I try and loop through the datatable and add the additional info using a different query. SELECT TOP 1 ch,term,milespa FROM tblFigures WHERE vehicleID = 1000 and ch > lowerprice and ch < upperprice ORDER BY ch ASC When I add the second part it takes over a minute to get the results. Is there anyway I can optimise this, either using indexes or more concise select statements perhaps, any help appreciated. Thanks
View Replies !
View Related
|
TRACKER
