Now It's Our Turn To Face This Issue.. When It Will Be Finally Resolved..
Sep 28, 2007
Hi all,
We have 2003 EE 64 bit & SQL 2005 EE 64 bit as well(service packs for windows & SQL pack 2 (9.030.42), now I have discovered this things with conversion of DTS to SSIS... We have set Oracle linked servers - they are working, at least we can see stuff, haven't tried to write on Oracle yet, sqlplus is also able to connect to oracle servers.. Our Oracle guy have installed 10.2.0.3 patch 10, but still no luck with SSIS...I'm not going to rename folders on production, like someone suggested it here...I checked path also, there was seems to be required folders included..I even tried to create SSIS on my PC and deployed it on 64 bit, using files method, without saving on SQL server - doesn't work, when it hit part with Oracle connection..And in connection string it looks like it use OLEDB..
I'm not excited about using OPENQuery etc, instead of SSIS.. It's just driving me nuts with this things...How Microsoft testing this stuff if bunch of people facing this issues and there's still no officiall fix or something like that..
View 9 Replies
ADVERTISEMENT
Sep 16, 2007
RESOLVED RESOLVED RESOLVED
DOH! When I rebuilt the OS, I changed the data partition ID's, and didn't remember to change them in the connetion strings
Not great, but better than continuing to bang head against wall.
Would like to say to MS appreciators that the VS2005 reinstall actually went quite well, considering.
===========================================================================
I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.
Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)
I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:
An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).
The Connection strings are all of the form
connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=(specific file string).MDF;Integrated Security=True;User Instance=True"
I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.
The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.
The only thing that changed is that there was this fresh install of SQL Server Express.
I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.
So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.
Any suggestions would be appreciated.
Thanks!
EDIT----TRIED SOME THINGS:
Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...
Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.
So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.
EDIT --- TRIED SOME MORE THINGS:
I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.
I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.
Any pointers on this last piece would be helpful.
EDIT - MORE INFO
The error details
[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
View 1 Replies
View Related
Jul 25, 2006
Within my script component I was having some difficulty getting nested Exceptions to bubble out of the script component logic. The symptom appears when you get an ex.message of "Object reference not set to an instance of an object." As soon as I removed the Finally section my message (or the actual Exception object) was properly passed to the caller.
I've only done minimal work with Try/Catch/Finally structures. Is this normal?
Thanks,
Jeff Tolman
E&M Electric
View 6 Replies
View Related
Nov 7, 2006
So where do I go from here/
SSMS doesn't seem to have anything like QA?
I spent the better part of today installing everything...read somewhere about using VS to write code for sprocs.
What does everyone else do?
View 6 Replies
View Related
May 13, 2008
Good morning (or afternoon, depending on your local).I've finally crossed over from Access to SQL Server. An interesting step fraught with surprises, syntax quirks and occasionally some exclamations of joy (just kidding).Anyway, here's my first question. I'm working on moving a currently functioning VB app from an Access backend to a SQL Server back end. In the process, I have been converting my app driven SQL calls to Server based stored procedures. Aside from a few issues (like SQL not liking concatenating string with the &), everything has worked out until this gem: SET NOCOUNT ONTRANSFORM Max(tE.EntryKey) AS Expr1 SELECT tE.ProjectKey FROM TS_tblWeeks tWINNER JOIN (TS_tblProjects tPINNER JOIN (TS_qryGroup tGINNER JOIN TS_tblEntry tE ON tG.GroupKey = tE.GroupKey) ON tP.ProjectKey = tE.ProjectKey) ON tW.WeekKey = tE.WeekKey WHERE (tE.WeekKey) = 1 And (tE.PersonKey) = 1GROUP BY tW.WeekKey, tE.ProjectKey, tP.ProjectSortORDER BY tW.WeekKey, tP.ProjectSort, tG.GroupKeyPIVOT tG.GroupKey;SQL server reports:Msg 102, Level 15, State 1, Line 1Incorrect syntax near 'tE'.Msg 156, Level 15, State 1, Line 13Incorrect syntax near the keyword 'PIVOT'.The highlighted lines are the TRANSFORM and PIVOT lines.The query works fine in Access, and works fine through ADO.NET as a supplied query. My intention is to make the two integers in the WHERE clause to be parameters supplied to the procedure.It's something simple, isn't it?Thanks!
View 14 Replies
View Related
Mar 30, 2008
Apress pro t-sql 2005. so far it seems pretty good in clearing up a lot of things Ive been doing but wasnt really sure I understood the same way I understand for example, C#. It was the little things about the language that Ive never been realy clear on such as basic control flow statements. Anyone else read this book yet ?
View 2 Replies
View Related
Oct 4, 2006
Please help. I'm completely new to SSIS, and I was hoping someone could point me in the right direction(s).
I am developing a winforms client app, and I need to be able to provide the user with the ability to import data in CSV format into our application's database.
I'd like to use SSIS if possible, as long as what I am trying to do isn't near impossible.
I'm thinking of a UI where the user can specify a flat file (CSV) to upload and be processed by an SSIS package on a remote database server.
This package will be responsible for validating the CSV file and inserting data into the database as appropriate.
Is there a way I can:
launch an SSIS package remotely, from a winforms app on the client machine that is not running SQL server (preferably asynchronously) -- and pass it some parameters, including the import file itself
(optionally) provide progress feedback to the client pc to let them know it is being processed
display a nice SSRS report on the client upon completion that tells them exactly what the success/failure of the import was (how many rows processed, which ones failed and why, etc)
Any helpful examples, links, etc would be most appreciated.
Thanks in advance!
View 1 Replies
View Related
Oct 4, 2005
I am using a function to fill a listbox in asp.net If I run it using the sql statement in the code, it works. if I use the sp I get an error saying the sp expects @strBookingNo which wasn't supplied. I have stepped through the function and there is a value in txNewBookingNo.text. Here's my function - the part commented out is the part that works,but I'd rather do it through an sp. I've included my sp too
Function GetFacilitiesBookings()
Dim MySQL As String = "spRB_GetFacilitiesBookings"
Dim MyConn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New SqlParameter("@strBookingNo", CInt(Me.txtNewBookingNo.Text)))
Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(MySQL, strConn)
Dim ddlDataSet As New DataSet
myDataAdapter.Fill(ddlDataSet, "tblRB_FacilitiesBookings")
Return ddlDataSet
'Dim strSQL As String
'strSQL = "Select * from tblRB_FacilitiesBookings where FB_BookingNo = " & CInt(Me.txtNewBookingNo.Text) & " and FB_Cancelled =0"
'Dim myDataAdapter As SqlDataAdapter = New SqlDataAdapter(strSQL, strConn)
'Dim ddlDataset As New DataSet
'ddlDataset.Clear()
'myDataAdapter.Fill(ddlDataset, "tblRB_FacilitiesBookings")
'Return ddlDataset
End Function
CREATE Procedure [spRB_GetFacilitiesBookings]
@intBookingNo integer
as
Select * from tblRB_FacilitiesBookings where
FB_BookingNo = @strBookingNo and
FB_Cancelled = 0
GO
View 14 Replies
View Related
Dec 13, 2005
Can anyone point me to a site which explains SQL syntax. ie I want to put brackets around a variable which holds a string of values for an IN clause.
Dim mySQLstr As String = "SELECT * from vweAvailableRooms where BD_RoomRef IN '"(" & Me.Session("RoomRefLogin") & ")"'"
View 2 Replies
View Related
Jan 5, 2006
I have an sp which I should return the latest date in my sql table. Howver I am getting this error 'Implicit conversion from data type datetime to bigint is not allowed. Use the CONVERT function to run this query. ' Here's my sp and my code which calls the sp.
CREATE PROCEDURE spRB_MaxDate (
@MaxDate datetime OUTPUT
)
AS
SELECT @MaxDate =MAX(BD_DateRequired)
FROM tblRB_BookingDates
RETURN
GO
Here's how I call it
Dim MySQL1 As String = "spRB_MaxDate"
Dim myConn1 As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim Cmd1 As New SqlCommand(MySQL1, myConn1)
Cmd1.CommandType = CommandType.StoredProcedure
'Add the parameters
Dim outParm As SqlParameter = New SqlParameter("@MaxDate", 0)
outParm.Direction = ParameterDirection.Output
Cmd1.Parameters.Add(outParm)
myConn1.Open()
Cmd1.ExecuteNonQuery()
myConn1.Close()
'
' get value of new id
Dim MaxDate As DateTime = Cmd1.Parameters("@MaxDate").Value
View 2 Replies
View Related
Jul 12, 2007
Hi,
I have installed SSRS and created a .net application... now when I try to open the report viewer, I get the error " the permission granted to user 'domainuser' are insufficient for performing this operation. (rsAccessDenied)" I have searched the net hi and low and cant seem to find an adequate answer... something to do with roles or permissions? I have assigned permissions to this user on the ssrs database, yet I still cant seem to view the report. It works fine in my test environment (as it usually does ) any help on this would be greatly appreciated as to be honest, I dont really know what im doing here.
"Impossible is Nothing"
View 8 Replies
View Related
Dec 10, 2007
I am selecting from two tables, with a one-to-many relationship.
Select t2.*
From Table1 t1
inner join Table2 on t1.id = t2.id
where t2.Field2 in (1,3)
There are times when there are 2 or more entries in table2 that relate to table1. I want to only display 1 of these entries, (either will do), I just dont want to display all.
View 3 Replies
View Related
Apr 10, 2007
Hi we recently depoyed SSRS 2005 on two servers along with our web application.
the extranet setup
IIS Web server (app server) in DMZ linked to database server sat behind our corporate firewall. SSL installed on IIS server. SSRS app is on the IIS, with its db on the main database server behind corproate firewall.
we have a simple link going from our app to the reporting services link
however when we enter a login (incl admin) it fails with the message 'The remote name could not be resolved: "ourdomain.com"
windows authentication at the moment.
any help much appreciated.
thanks
View 3 Replies
View Related
Feb 1, 2008
Hi,
I am pretty new to harcore ASP.NET and .NET in general but I know the basics of the language and stuff like that. Basically I used to be a hardcore object pascal Delphi developer doing Windows Applications but have now moved to .NET world. The reason I posted this question here is I couldn't find any other specific place to ask so here it goes.
To get me started with good programming source and practices I downloaded the ASP.NET TimeTracker Starter Kit and after some modificaion in connection string (using SQL 2005 Developer edition not the Express one) I have managed to make it work. Things done in there are pretty interesting way by using a delegate to retrieve data and present it using databound controls.
As much simple as it sounds I am more of a fan of not using too many databound controls except in dropdown box, list box and stuff like that. So basically I need to develop DataAccessLayer (DAL) in such a way that it's useful for both Windows and Web application. So with my research I found that I should be using DataSets in the DAL because they are serializable and thatz what Web Applications & Services love over using SqlDataReader. Other advantage of using DataSet is to use ForEach syntex over While Loop in SqlDataReaders. So below is some code I extracted from the TimeTracker Starter Kit to get me started.
I also noticed that the return result is a list array of certain object, in this case Category. So does it mean that it can be used in frontend using ForEach syntex? How can I convert the code below to use DataSets over SqlDataReader? Private Delegate Sub TGenerateListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef tempList As List(Of T))
Public Overrides Function GetAllCategories() As List(Of Category)
Dim sqlCmd As SqlCommand = New SqlCommand()
SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETALLCATEGORIES)
Dim categoryList As New List(Of Category)()
TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)
Return categoryList
End Function
Public Overrides Function GetCategoryByCategoryId(ByVal Id As Integer) As Category
If Id <= DefaultValues.GetCategoryIdMinValue() Then
Throw New ArgumentOutOfRangeException("Id")
End If
Dim sqlCmd As SqlCommand = New SqlCommand()
AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, Id)
SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYID)
Dim categoryList As New List(Of Category)()
TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)
If categoryList.Count > 0 Then
Return categoryList(0)
Else
Return Nothing
End If
End Function
Private Sub TGenerateCategoryListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef categoryList As List(Of Category))
Do While returnData.Read()
Dim actualDuration As Decimal = 0
If Not returnData("CategoryActualDuration") Is DBNull.Value Then
actualDuration = Convert.ToDecimal(returnData("CategoryActualDuration"))
End If
Dim category As Category = New Category(CStr(returnData("CategoryAbbreviation")), actualDuration, _
CInt(returnData("CategoryId")), CDec(returnData("CategoryEstimateDuration")), CStr(returnData("CategoryName")), _
CInt(returnData("ProjectId")))
categoryList.Add(category)
Loop
End Sub
Hope this makes sense. Sorry if I have posted this in a wrong section..!
Cheers,Nirav
View 6 Replies
View Related
Apr 14, 2008
Here is a simple grid view page I made.
http://visualboxscore.com/boxscores/cfb_box_scores.aspx
Ultimately I want to be able to filter every field that is listed. However, for now I am just learning this stuff and would like to just filter the by the name in the "offense" field.
However, it is not binding the selection thus not filtering it out. Do you see any problems in my code?
<%@ Page Language="VB" %>
<html>
<head id="Head1" runat="server">
<title>GridView DetailsView Master-Details (2 Page)</title>
</head>
<body>
<form id="form1" runat="server">
<b>Choose a team:</b>
<asp:DropDownList ID="DropDownList1" DataSourceID="SqlDataSource2" AutoPostBack="true"
DataTextField="offense" Runat="server" />
<asp:SqlDataSource ID="SqlDataSource2" Runat="server" SelectCommand="SELECT DISTINCT [offense] FROM [cfb_boxscores]"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>" />
<br />
<br />
<asp:GridView ID="GridView1" AllowSorting="True" AllowPaging="false" Runat="server"
DataSourceID="SqlDataSource1" DataKeyNames="offense"
AutoGenerateColumns="False">
<Columns>
<asp:BoundField HeaderText="Date" DataField="date" DataFormatString="{0:MM/dd/yy}" ItemStyle-HorizontalAlign="Center" SortExpression="date" />
<asp:BoundField HeaderText="Offense" DataField="offense" ItemStyle-HorizontalAlign="Center" SortExpression="offense" />
<asp:BoundField HeaderText="Defense" DataField="defense" ItemStyle-HorizontalAlign="Center" SortExpression="defense" />
<asp:BoundField HeaderText="Rush Yards" DataField="rush_net" ItemStyle-HorizontalAlign="Center" SortExpression="rush_net" />
<asp:BoundField HeaderText="YPC" DataField="yards_per_carry" DataFormatString="{0:#.##}" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_carry" />
<asp:BoundField HeaderText="Pass Yards" DataField="pass_yards" ItemStyle-HorizontalAlign="Center" SortExpression="pass_yards" />
<asp:BoundField HeaderText="YPA" DataField="yards_per_att" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_att" />
<asp:BoundField HeaderText="Total Yards" DataField="total_yards" ItemStyle-HorizontalAlign="Center" SortExpression="total_yards" />
<asp:BoundField HeaderText="YPP" DataField="yards_per_play" ItemStyle-HorizontalAlign="Center" SortExpression="yards_per_play" />
<asp:BoundField HeaderText="Points" DataField="points" ItemStyle-HorizontalAlign="Center" SortExpression="points" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server" SelectCommand="SELECT [date], [offense], [defense], [rush_net], [yards_per_carry], [pass_yards], [yards_per_att], [total_yards], [yards_per_play], [points] FROM [cfb_boxscores]"
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>">
<SelectParameters>
<asp:ControlParameter Name="Offense" ControlID="DropDownList1" />
</SelectParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
overall I want to format the table look and and background but for now I just want to see how this stuff functions.
Thanks!!
View 3 Replies
View Related
Jun 18, 2008
I have a working sp:
SELECT dbo.Job.CompanyJobId, dbo.Item.UnitOfMeasure, dbo.Job.Name, dbo.Job.ChangeDate,
dbo.Region.CompanyRegionID, dbo.Job.Active,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'MA' then dbo.ProductionEvent.AlternateQuantity ELSE 0 END) AS material,
sum(case dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN dbo.ProductionEvent.Quantity ELSE 0 END) AS production
FROM dbo.job
inner join dbo.Event ON dbo.Job.JobGuid = dbo.Event.JobGuid
inner join dbo.ProductionEvent on dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid
left outer join dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
left outer JOIN dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid
inner JOIN dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid
inner JOIN dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid
left OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.Item.UnitOfMeasure = 'TN'
and(dbo.SourceType.CompanySourceTypeId = 'PR' or dbo.SourceType.CompanySourceTypeId = 'MA')
GROUP BY dbo.Job.CompanyJobId, fMeasure, dbo.Region.CompanyRegionID,
dbo.Job.Name, dbo.Job.ChangeDate, dbo.Job.Active
Now I need to inser another join like this:
dbo.Event ON dbo.EmployeeLaborEvent.EventGuid = dbo.Event.EventGuid INNER JOIN
and I get error:
Server: Msg 1013, Level 15, State 1, Line 15
Tables or functions 'dbo.Event' and 'dbo.Event' have the same exposed names. Use correlation names to distinguish them.
Not sure what this means. Thank you.
View 7 Replies
View Related
Jun 19, 2008
I am reading over a job description and I am unsure of that the employer is refering to when they say "Instance Parameters".
Here is the context that it is used in.
* Tune and optimize SQL statements, Instance Parameters, and Data Storage including the identification of performance related issues.
View 3 Replies
View Related
Aug 16, 2005
You can't have but one default contraint in a DB named
df_revisiondate Default getdate()
I thought that sense they were being created on different Tables that it would not matter. It was a way to maintain a type of consistency by giving them the same name. However, it SEEMS to have caused all my tables to crash.
thanks!
View 5 Replies
View Related
Oct 14, 2005
Hi all.. i have a problem.
Let say i have 3 tables. Let's name it Main, table2, table3. in table Main, there are 4 colums(M_id,M_name M_tbl2, M_tbl3). In table2 and table3, each has 3 colums. For table2 the colums are id, tbl2Name, tbl2Desc and for table3(id, tbl3Name, tbl3Desc).
M_tbl2 will refer to id in table2 and M_tbl3 will refer to id in table3. How am i going to get M_id, M_name, tbl2Name, tbl3Name in one query?
View 4 Replies
View Related
May 3, 2006
I am creating a view in SQL using the View tab in the database and adding the table and the fields I need. For criteria I need to have 28 'or' s . How do I insert more columns on the end of my view ?
View 2 Replies
View Related
Nov 29, 2006
I have test tables with key fields that increment by one each time a record is added. If I delete all the records in a table (using a delete query) is there a way I can make the key start at 1 again.
The reason I ask is that by using test data in these tables (and there are a lot of records),when I clear them out and append the live data the key field starts at something like 764525
View 1 Replies
View Related
Jun 6, 2007
Sorry if this sounds dumb but I can't find the answer . I have created a scheduled job on my test database using the wizard. How do I edit it or delete it ? Step by step instructions please
I have never done this before so am just experimenting at the moment.
View 1 Replies
View Related
Aug 13, 2007
I have a table NUMBERS in which I have Column1 and Column2. Column1 will always be a unique number, yet Column2 may have duplicates.
What I would like to do is return all the data in Columns 1 and 2, yet have Column1 return as a comma seperated value.
Column1 Column2
1 15
2 16
3 16
4 17
Return:
Column1 Column2
1 15
2,3 16
4 17
Any ideas will be great!
Thanks
View 14 Replies
View Related
Jan 25, 2008
Hello All,
I have about 10 different insert into statements which I would like to include into a loop of some sort. Here is what I am trying to accomplish.
SET @aryTableNames = 'thisTable','thatTable','anotherTable'
DO
SELECT * INTO [Server].[dbo].[@aryTableNames]
FROM OPENQUERY(mysqlDB, 'SELECT * FROM `@aryTableNames`')
LOOP
View 5 Replies
View Related
Jan 25, 2008
I have a stored procedure where I would like to use substring in the where clause, if possible.
CREATE PROCEDURE GetJobList
@PlantId char(3)
Select ......
From ......
WHERE (dbo.Source.CompanySourceId = 'PROD' and dbo.Batch.ReportDate >= @DateFrom and dbo.Batch.ReportDate <= @DateTo and (if @PlantId <> ' ' then SUBSTRING(dbo.Job.CompanyJobId, 1,3) = @PlantId))
I get syntax error "Incorrect syntax near 'if, incorrect sysntax near 'then'....
View 4 Replies
View Related
Feb 1, 2008
I have a sp that selects from a table. The table has a MA record (received qty) type and a PR record (used qty) type.
Below is code that produces an extract and gives 2 records, one for MA (received qty) and one for PR (used qty).
SELECT dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, dbo.Product.CompanyProductId as product_id, SUBSTRING(dbo.Job.CompanyJobId, 1,3) as plant_id, dbo.SourceType.CompanySourceTypeId,
CASE dbo.SourceType.CompanySourceTypeId
WHEN 'PR' then SUM(dbo.ProductionEvent.Quantity)
ELSE SUM(dbo.ProductionEvent.AlternateQuantity)
END
AS
tot_qty
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
WHERE dbo.SourceType.CompanySourceTypeId = 'PR'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'
or dbo.SourceType.CompanySourceTypeId = 'MA'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'
GROUP BY dbo.Batch.ReportDate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.SourceType.CompanySourceTypeId, dbo.Product.CompanyProductId
Tot Qty
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 MA372.25
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 PR357.25
Now I want to extract the same data into a temp table (that I have created) and when reading the same records I would like to update either a received qty field or a used qty field depending upon record type MA or PR). The query works but both qty fields are empty. Can anyone see what I am doing wrong?
SELECT dbo.Batch.ReportDate AS job_date, dbo.Job.CompanyJobId AS job_number, dbo.Item.CompanyItemId AS cost_code, dbo.Product.CompanyProductId as product_id, SUBSTRING(dbo.Job.CompanyJobId, 1,3) as plant_id, dbo.SourceType.CompanySourceTypeId,
qty_received =
CASE dbo.SourceType.CompanySourceTypeId WHEN 'MA' then SUM(dbo.ProductionEvent.Quantity) ELSE 0 END,
qty_used =
CASE dbo.SourceType.CompanySourceTypeId WHEN 'PR' THEN SUM(dbo.ProductionEvent.AlternateQuantity) ELSE 0 END,
qty_wasted =
CASE dbo.SourceType.CompanySourceTypeId
WHEN 'MA' then SUM(dbo.ProductionEvent.Quantity)
ELSE 0
END
-
CASE dbo.SourceType.CompanySourceTypeId
WHEN 'PR' THEN SUM(dbo.ProductionEvent.AlternateQuantity)
ELSE 0
END
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid
WHERE dbo.SourceType.CompanySourceTypeId = 'PR'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'
or dbo.SourceType.CompanySourceTypeId = 'MA'
and substring(dbo.Product.CompanyProductId, 1, 4) = 'ASPH'
and dbo.Item.UnitOfMeasure = 'TN'
GROUP BY dbo.Batch.ReportDate, dbo.Job.CompanyJobId, dbo.Item.CompanyItemId, dbo.SourceType.CompanySourceTypeId, dbo.Product.CompanyProductId
QTY QTY QTY
REC USED WASTE
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 MA.00 .00 .00
2007-12-17 3006204 05001 ASPH - SP 9.5 C 300 PR.00 .00 .00
View 9 Replies
View Related
Nov 23, 2005
ARRRRRRRGGGGGHHHHH!!Please can you help, I'm going round the bend with this.I have a simple and small table called STOCKCATS, which I need to query toget back a dataset in a particular order, but although it looks simple Ican't get it to work. My table schema plus sample data to see the problemis as follows:DROP TABLE IF EXISTS `STOCKCATS`;CREATE TABLE `STOCKCATS` (`CATID` varchar(30) NOT NULL default '',`LEVEL` varchar(30) default NULL,PRIMARY KEY (`CATID`),KEY `indxCATEGORYID` (`CATID`));INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('A001',''),('A002','A001'),('A003','A001'),('A004','A001'),('A005','A001'),('PCHW01',''),('MHW01',''),('FD01',''),('ELEC01',''),('MHW02','MHW01');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('MHW03','MHW01'),('MHW04','MHW01'),('MHW05','MHW01'),('PCHW02','PCHW01'),('PCHW03','PCHW01'),('PCHW04','PCHW01'),('PCHW05','PCHW01'),('PCSW01',''),('MSW01',''),('C001',''),('C002','C001'),('C003','C001'),('MV',''),('SUZ','MV'),('ALF','MV'),('PLASMA','ELEC01'),('T01','ELEC01'),('HEATING',''),('RAD','HEATING'),('P01',''),('B01','P01'),('BB','HEATING'),('FS','HEATING'),('WM','HEATING'),('AEROSOL',''),('SOLVENTS','AEROSOL'),('DGC','');INSERT INTO `STOCKCATS` (`CATID`,`LEVEL`) VALUES('DGXWINDOWS','DGC'),('DGXEXTRA','DGC'),('DGXCON','DGC');As you can see from the table structure, this table consists of 2 fieldvalues. The 1st is the category code and the 2nd is the level is at. If acatid has a level of nothing, eg '', then it means that it is a root levelcategory. If a catid has a another cat's catid in it's level, eg B01 hasP01, then it is a sub-category of this category, eg B01 is a sub-cat of P01.All I want to do is query this table and bring back the data so thatalphabetically it goes root level cat A1, then all the sub-cats for thisroot level, then root level A2, then all sub-cats for this root level and soon. An example using the above would be as follows:^ ^ A to G of root level cats plus their sub-cats....HEATING << root levelBB << sub-cat of heatingFS << sub-cat of heatingWM << sub-cat of heating/ / I to Z of root level cats plus their sub-cats....A few posters kindly gave me a solution of ORDER BY COALESCE(CATID,LEVEL),CATID and I thought this had done it, but I was looking at the ('A001',''),('A002','A001'), ('A003','A001'), ('A004','A001'), ('A005','A001')entries as these naturally fell into place. If you use this order commandon the above you will see that ('P01','') and it's associated ('B01','P01')sub-cat just don't come together.Does any body have any ideas?ThanksLaphan
View 6 Replies
View Related
Apr 6, 2008
This may sound silly, but I'm calculating how much someone owes over time verses how much they have paid for that period. So if they owe more than they have paid the result is an under payment amount like -$100. Then I need to add the -$100 to the new payment due, let's say $100. So the total Due is $200. But if I add -100 to 100 that equals 0.
Any suggestions?
View 3 Replies
View Related
Aug 3, 2001
How do you turn LOGGING OFF for a DTS package????
DB2 is a simple "LOAD DATA REPLACE NO LOG"... (can SQL Server make it any more difficult to find this option to turn on/off??!!!)
frustrated!!!!!!!!!!!!!!!!
thanks-
View 1 Replies
View Related
Nov 7, 2006
Hello,In SQL Server 2005 Enterprise, I can change the CPU affinity viaManagement Studio. Is there a way that I can change these settings viat-sql?I wish to use all my cpu's at night when my data warehouse builds andthen durning the day reduce the number of cpus for SQL so that theapplication can get more time.TIARob
View 2 Replies
View Related
Dec 27, 2006
Ever since installing SQL Server 2005 Express Edition the XP Task Manager shows €œsqlservr.exe€? running normally, as a €œNETWORK SERVICE€? consuming 26MB of memory. This is true even after a cold reboot. I€™d like to turn SQL off since I am not currently planning on using it. I tried detaching a couple of projects I had started, as part of tutorials, but that did not make a difference.
So how do I turn SQL Server 2005 Express Edition off without removing it from the system, which I do not want to do?
Thanks.
Thomas
View 3 Replies
View Related
Aug 16, 2006
I have a select statement like this. Notice that I am doing the same calculation on different fields. Is it possible I can make this, in coding terms, a fucntion; so I can call it when I like (this query) and just provide the field.
Example: select field1, test_field = secs_to_hhmmss(TALK_TIME) from ...
SELECT dbo.date_table.real_date, dbo.time_table.hh,
COUNT(dbo.CALLDETAIL.id) as NumOfCalls,
TalkTime =
CASE WHEN CAST(SUM(TALK_TIME) AS INTEGER)/3600<10 THEN '0' ELSE '' END
+ RTRIM(CAST(SUM(TALK_TIME) AS INTEGER)/3600)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(TALK_TIME) AS INTEGER) % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(TALK_TIME) AS INTEGER) % 3600) % 60),2),
HoldTime =
CASE WHEN CAST(SUM(HOLD_TIME) AS INTEGER)/3600<10 THEN '0' ELSE '' END
+ RTRIM(CAST(SUM(HOLD_TIME) AS INTEGER)/3600)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(HOLD_TIME) AS INTEGER) % 3600) / 60),2)
+ ':' + RIGHT('0'+RTRIM((CAST(SUM(HOLD_TIME) AS INTEGER) % 3600) % 60),2),
View 3 Replies
View Related
Oct 10, 2006
I am trying to combine 2 queries, each with their own 'order by' and I am having trouble.
This is just an example, not what I'm trying to do, my query is more elaberate but looks simular to this.
SELECT TOP 10 *, 'FieldA' AS SortedBy
FROM TableA
Order By FieldA Desc
UNION ALL
SELECT TOP 10 Precent *, 'FieldB' AS SortedBy
FROM TableA
Order By FieldB Asc
Anyway not to get the following error?
Server: Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'UNION'.
View 2 Replies
View Related
