So I have a person who is adamant in tell me that SQL Server does not run on windows XP.
Now, I have already done all the research on this (i.e. sql server 2000 product page / requirements) and know the answer, but they insist on asking the question, so here it is .....
'Will SQL Server run on Windows XP'
A simple YES or NO will suffice; however, if you want to explain the answer (if it requires one ;) ), please feel free.
Does anyone out there know if you can take a user like this domain1Username and without dropping it and recreating change it to domain2username. I am using Sql 7.
Thanks in advance
Troy
So, I kinda taught myself sql on the net in a couple of weeks... I've been practicing commands and what not with a database with a company I work with. Here's what I need some help with:
I'm creating a batch file that will connect to the sql server, and input a .sql in the batch file which launches the sql script to check to see if a store's data has come in or not, and if it hasn't, to launch the job to process that store. Now, after it launches the job, the only step for the job is to process what it can before it fails or completes, and most of the time it fails after 1-10 minutes. A big a variance. So my question is, how do I make it so that the script being called, waits for the job to finish before proceeding to the next command which checks a different store and launches another script?
If you want the script, I'll mod out some of the actual names for security purposes and post it for you guys. Any help is appreciated, and I've searched and hacked Google to get an answer, but nothing, so I decided to post this. Thanks!
Hi everybody
I want to know in the real world, what RDBMS is used for keeping people's accounts in official Banks( institution for saving and borrowing money) ? do they use SQL-Server , ORACLE or some thing else?
Thanks in advance.
Regards.
Whilst digging about in the systables I noticed 2 curious things:
When creating an application that returned all tables, columns and fields I had to use sys.systables, and when returning the information from this view, I received two identical columns 1. myColumn of type sysname and 2. myColumn of type varchar. The point being, what is this column for, and why is it there, as it was quite annoying to note it when returning the dataset in my app.
My other point is, while checking out what I could do with sysname, I noted the xml type has a capacity of -1, whereas all other data types have a capacity between 2-8000. Could someone clarify this for me, as it seems a single xml document could be inserted upto 2gb.
Regards.
How can I write a query to return non distinct results?
View 3 Replies View RelatedCan someone try and explain to me how a sql query can run fine and return data, yet when I try to run it in dtswizard, while the "preview" view shows the data, actually running it returns zero rows?
This is on SQL2005 express and since I can't get dtsrun or dtexec to work, I'm using auto-it to simulate my actually stepping through the process. Very kludgy, but "when all you've got is a hammer...."
Dear GroupSomething that I ever found quite difficult was finding a bug in ascript e.g. in a stp#ored procedure as often it would indicate theerror in a completely different line in which the error is actuallylocated. Take the following scriptALTER PROCEDURE [dbo].[fra_UpdateCompany]@CompanyID int, @CompanyName varchar(50), @Status int, @TelNovarchar(50), @FaxNo varchar(50), @Email varchar(50), @Web varchar(50),@OfficeType int, @Comment varchar(512)ASDECLARE@CommentOrg varchar(512)IF (LEN(@CompanyName) < 1) BEGIN SET @CompanyName = NULL ENDIF (LEN(@TelNo) < 1) BEGIN SET @TelNo = NULL ENDIF (LEN(@FaxNo) < 1) BEGIN SET @FaxNo = NULL ENDIF (LEN(@Email) < 1) BEGIN SET @Email = NULL ENDIF (LEN(@Web) < 1) BEGIN SET @Web = NULL ENDIF (LEN(@Comment) < 1) BEGIN SET @Comment = NULL ENDSET @CommentOrg = (SELECT Comment from fra_company WHERE CompanyID =@CompanyID)SET @Comment = (@Comment + '' + @CommentOrg)-- UPDATE COMPANYUPDATE fra_company SET CompanyName = @CompanyName, Status = @Status,TelNo = @TelNo, FaxNo = @FaxNo, Email = @Email, Web=@Web, OfficeType =@OfficeType, Comment = LTRIM(@Comment) WHERE CompanyID = @CompanyIDIt throws an error in Line 17 IF (LEN(@TelNo) < 1) BEGIN SET @TelNo =NULL END when the error is actually in line 23: SET @CommentOrg =(SELECT Comment from fra_company WHERE CompanyID = @CompanyID)ErrorMsg:Msg 137, Level 15, State 2, Procedure fra_UpdateCompany, Line 17Must declare the scalar variable "@ContactID".Why is that? I had hoped that it had improved from SQL 7.0 to 2005 butit's still the same vague thing.Thanks for sharing your expertise and wisdom on this,Martin
View 2 Replies View RelatedHi all
I have a Float Field in my table with following values:
1.63
7.42
35.71
0.58
every thing is ok BUT when i Select Data from this table in
QUERY ANALIZER environment with (Select * from mytable) , Query Analizer shows me the following values:
1.6299999999999999
7.4199999999999999
35.710000000000001
0.57999999999999996
Why? while i don't have this problem in Enterprise manager !!!
Thanks in advance.
Kind Regards.
Dear group,if someone could give me an idea what is going on in one of ourdatabases, this would really really be helpful.We have two tables with around 2 / 3 million rows. These tables have nokey and no ID. (This major design flaw will be overcome in some laterversion of the application-software working on this DB but right now ihave to live with this).Now for the funny bit1) I open one window in the Query-Analyzer and write some code likeBegin transaction INSERT INTO TABLE COMMIT2) in another window i write "SELECT COUNT(*) from TABLE"If I perform the insert then afterwards select count(*) the row-countis incremented by two whereas the Insert-Statement said "1 row(s)modified.DBCC gives no errors.DBCC gives amount of rows 2 million rowsSelect count(*) on the same table gives 3 million rowsExporting the data, truncating the table re-importing data gives noresult, right now the DTS-status is 203 and the machine is "thinking".Is there any possibility to check the "integrity" of the table?This problem is on the production machine, but right now i am workingon a copy so it was propagated with backup / restore-mechanism.Any hint would be very helpfulThanks and GreetingsUli
View 5 Replies View RelatedI found a code snippet that use the curious following sintax on the creation of View:
CREATE VIEW [dbo].[vw_EvenValues]
AS
SELECT [TestColumn]
FROM [dbo].[TestTable]
WHERE [TestColumn] % 2 = 1
[code]....
IF [TestColumn] on the Select is varchar, then error occurs and say:"Conversion failed when converting for the varchar value 'A001' to data type int"
Hi,I have been running some queries against a table in a my database andhave noted an odd (at least it seems odd to me) performance issue.The table has approximately 5 million rows and includes the followingcolumns:DocID (INTEGER, PRIMARY KEY, CLUSTERED)IsRecord (INTEGER, NONCLUSTERED)Title (VARCHAR(255), NONCLUSTERED)If I issue the following query:SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'it takes about 23 seconds to return the 481 hits.The execution plan shows a non-clustered index scan being performed onthe Title index (returning 481 rows) and a non-clustered index scan onthe IsRecord index (returning 4.9 million rows). These are then mergedin a hash match/inner join operation.The Title index scan has an estimated row size of 41 and an I/O costof 9.82 (cost is 27%). The IsRecord index scan has an estimated rowsize of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Matchaccounts for a further 52% of the cose with the SELECT at the head ofthe plan listed as 0% cost.If I issue the following query:SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'it takes about 12 seconds to return the 481 hits and consists solelyof a non-clustered index scan of the Title Index.Again the Title index scan has an estimated row size of 41 and an I/Ocost of 9.82 ans it's cost is listed as 78%. The SELECT at the head ofthe plan is attributed the other 22% of the cost.All this is fine, however when I issue the following query:SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE'%process%'it takes 1 minute 50 seconds to run the query. The execution plansshows that a clustered index scan is occurring and this accounts for96% of the cost. The estimated row size is 463 and the I/O cost is111.What on earth is going on here. I can understand the need to scan theTitle index because of the wildcards, but why on earth would the queryperform a scan of the clustered (primary key) index? And what is goingon with the row size and I/O cost?All the indexes and statistics are up to date, so I am at a completeloss to explain what is going on here. Can anyone explain why the 3rdquery is so much slower (and possibly suggest a way to improve theperformance)/ThanksPaul MateerMeridio LimtedI am at a complete loss to explain what is happening here,
View 3 Replies View RelatedHi all,
Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.
However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.
Here is the code that works:SET NOCOUNT ON
CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl
SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl
The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.
Any insight anyone? Or is BOL just full of...well..."stuff"?
Using MSDE and OSQL
I begin with:
C:OSQL -D VID -i C:accepted.sql -o C:Resultsaccepted.txt -n -w500 -Usa
That gives me data such as this:
363 Cynthia KY 36
542 Charlene NC 3
594 Amanda NJ 9
592 Robert NJ 54
Then this command to create a table
CREATE TABLE accepted
(
Customer_idnvarchar(50)NULL,
Cust_Namenvarchar(50)NULL,
Cust_Statenvarchar(50)NULL,
Cust_Countnvarchar(50)NULL
)
GO
I've created this BCP format file:
8.0
4
1 SQLCHAR 0 50 "/t" 1 Customer_id SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 50 "/t" 2 Cust_Name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 50 "/t" 3 Cust_State SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 50 "/r/n"4 Cust_Count SQL_Latin1_General_CP1_CI_AS
Table is created. I can SELECT * FROM accepted and see my column names.
Then I try to BCP into the table using:
C:>BCP sales..east in C:Resultsaccepted.txt -t -f C:cpformataccepted.fmt -Usa -Ppwd
I get this error:
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
and so on......
In the .fmt file I've tried "", " ", " " and everthing I could think of as a delimiter. Still no luck. I've tried almost every switch available to both OSQL and BCP.
The data I am trying to BCP is a SQL result so I don't think any special delimiters are placed. I've tried not using the .fmt file and using the prompts but still no luck. Data is CAST in the query and doesn't excede 45 characters.
Hope I've explained my problem well enough.
-Deana
Hi,I need to input Chinese character into the table of the database. I did try to install/run both Chinese/English version of Visual Studio into Chinese/English version of Server 2003 but it still didn't work.Please help !stephen
View 3 Replies View RelatedI use txtbox.Text = cmd.ExecuteReader() but it doesn't work. How can I fix it ?
View 4 Replies View RelatedI have two textboxes on the page...shipdate and duedate. When the page loads, shipdate has today's date loaded and duedate has a date that's 28 days later than today. When I change the dates and submit it's not updating in the database instead I'm getting the two dates in pageload. What am I doing wrong?Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
ShipDateTxt.Text = Today()
DueDateTxt.Text = DateAdd(DateInterval.Day, 28, Today())
End SubProtected Sub LoanRequest_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LoanRequest.Click
Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString)Dim cmd As New Data.SqlClient.SqlCommandWith cmd
.Connection = conn
.CommandType = Data.CommandType.StoredProcedure
.CommandText = "UpdateloanerInfo".Parameters.AddWithValue("@requestorid", Integer.Parse(Request.QueryString("requestorid")))
.Parameters.AddWithValue("@shipdate", ShipDateTxt.Text).Parameters.AddWithValue("@duedate", DueDateTxt.Text)
End With
Here's updateloanerinfo stored procedure:
@requestorid int,@shipdate datetime,@duedate datetime
AS update LibraryRequest
set [shipdate] = @shipdate,[duedate] = @duedate
Where requestorid=@requestorid
Can I pass a parameter to a DTS package?
Here is what I am trying to do: Every month we need to import a fixed format text file into one of our tables. The format and location of the file is same every month except for the name. I want to create a DTS package to import it and call this DTS package first thing in a stored procedure(after which I do some processing with this imported data). I want to create the filename in my stored procedure and then call this DTS package to import it.
I am usig DTS as the interface is so much easier and want to avoid bcp :-)
Is it possible?
Thanks in advance,
Nishi
I am just learning SQL Server and I am stuck with a few things. I was wondering if you can define input masks for columns in the database in SQL Server. I have used this feature many times in Access and on the front end in Oracle Forms. Is there such a thing in SQL Server?? Is it just called something different.....? I will be trying to format telephone numbers and dates. Thank you for you time.
Nadine Sinclair
Hi all,
Can anyone tell me about how to prompt a user to type when running a sql statement through query analyzer.
thanks
Jay
create function quantprice
( @itemnmbr varchar(50), @startdate datetime, @enddate datetime)
RETURNS TABLE
AS
RETURN
(Select distinct t.itemnmbr,t.totalprice as totalcost
from
(
select distinct
vs.itemnmbr, sum(vs.totalsumprce) as totalprice
from vwquantityprice vs
Where vs.itemnmbr = @itemnmbr and (vs.docdate between @startdate and @enddate)
group by vs.itemnmbr
) as t
)
select * from quantityprice('06-5840','4/1/2007','4/1/2008')
well, guys, i have this function and obviously i will get one row from this in output...with that particular itemnumber but i want multi-itemnmbrs some times in output...some times 2..soemtimes 3...
example,
select * from quantityprice('06-5840,ab-4581,0a-1458,45-0945','4/1/2007','4/1/2008')
can you tell me what condition it will come to get this output..
like in where itemnmbr in('06-5840,ab-4581,0a-1458,45-0945')
and in starting input v ariable @itemnmbr..
create function quantprice
( @itemnmbr varchar(50)..
don't know what condition it will come and where i have to put condition in where clause or in input variable..
any help would be appreciated..thanks a lot!! guys plz reply.
I have created a query that has a "When" and an "And" function.
I know need to make the query run on user input, i want it to ask for the date, then name to run the query
Afternoon...
I have a database for a pretend dvd hire company and need to create a query that uses user input as part of the query.
Ie, select * from dvd where actor = 'Tom Cruise';
The 'Tom Cruise' part of the query needs to be user input every time the query is ran... Can you help?
Many thanks in advance!?
Hi there,
I'll be going through a training couse for SQL Server next month. The class I'm signed up for is M2780: Maintaining a Microsoft® SQL Server™ 2005. My company uses SQL Server 2000 now and unless I give good reasons to upgrade to 2005, we won't be upgrading. I have very little DBA experience, but since I was told SQL Server is my responsibility, I figured it would make sense to go through some type of training for it. My questions are: Is there enough the same between the two that it makes sense for me to take this class? Are there good business reasons I can give for upgrading? If we don't upgrade is it worth taking a class for 2000 vs 2005?
Thanks for any input.
Marcie
Need help.
I’m trying to pass inputs from one stored proc to another but I’m having problems in passing them.
First proc (input_passing) should pass the following inputs – FirstName, LastNmae and RecordID to the second proc (input_receive) – then the second proc will diplay the outcome – YES/NO if there is a match.
------------------------------------------------------------------------------------------
First proc (input_passing)
-------------------------------------------------------------------------------------------------------
DECLARE
@RecordIDVARCHAR(11),
@FirstNameVARCHAR(60),
@LastNameVARCHAR(60)
--DECLARE tbInputs CURSOR FOR
SELECT
RecordID,
FirstName,
LastName
FROM
tbInputs
OPEN tbInputs
FETCH NEXT FROM tbInputs
INTO @RecordID, @FirstName, @LastName
WHILE @@FETCH_STATUS = 0
BEGIN
--GET RECORD
SET @RecordID = RecordID from dbo.tbInputs)
END
FETCH NEXT FROM tbInputs
INTO @RecordID, @FirstName, @LastName
END
CLOSE tbInputs
DEALLOCATE tbInputs
exec input_receive
----------------------------------------------------------------------------------------------------------------- Second proc. (input_receive)
CREATE PROCEDURE input_receive
@RecordIDVARCHAR(11) = NULL,
@FirstNameVARCHAR(50) = NULL,
@LastNameVARCHAR(50) = NULL,
@RecordMatchBIT OUTPUT,
@MatchedOnVARCHAR(400) OUTPUT
AS
-- Declarations
DECLARE @CountINT
DECLARE@FoundBIT
DECLARE @AlertIDDECIMAL(18,0)
DECLARE@AlertCreateDateDATETIME
-- END Declarations
-- ******* Initializations **********
SET @Count = 0
SET @Found = 0
-- DEFAULT to No File Match
SET RecordMatch = 0
SET @MatchedOn = 'NO FOUND'
-- ******* END Initializations **********
-- First check to see if the RecordID Matches
IF( @RecordID IS NOT NULL AND @RecordID <> '' )
BEGIN
IF( CAST( REPLACE(@RecordID, '-', '') AS DECIMAL(18,0)) > 0 )
BEGIN
SELECT @Count = COUNT(*)
FROM tbAlert
WHERE REPLACE(RecordID, '-', '') = REPLACE(@RecordID, '-', '')
IF( @Count > 0 )
BEGIN
SET @RecordMatch = 1
SET @MatchedOn = 'FOUND : RecordID'
RETURN
END
END
END
Josephine
Hi ,
Im trying to build a package that will copy data from excel to SQL
in a program
unfortunately , when I open the package xml file and I drill into the
oledb destination I see that I have no available input columns
what could be the problem ?
thanks ahead
Eran
p.s
the script:
Dim p As Package = New Package()
Dim e As Executable = p.Executables.Add("DTS.Pipeline.1")
Dim thMainPipe As TaskHost = CType(e, TaskHost)
thMainPipe.Properties("Name").SetValue(thMainPipe, "Data Flow")
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
' Create excel connection MANAGER
Dim excelCon As ConnectionManager = p.Connections.Add("EXCEL")
excelCon.Name = "ExcelSourceConn"
excelCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=e: ry ry ry.XLS;Extended Properties=""Excel 8.0;HDR=YES"""
' Create sqldev connection Manager
Dim sqlCon As ConnectionManager = p.Connections.Add("OLEDB")
sqlCon.Name = "sqldevConn"
sqlCon.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=InsFocus_Admin_Eran;Data Source=SQLDEVSQLDEV"
''create source component
Dim excelSource As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()
excelSource.Name = "ExcelSource"
excelSource.ComponentClassID = "DTSAdapter.ExcelSource.1"
Dim excelInstance As CManagedComponentWrapper = excelSource.Instantiate()
excelInstance.ProvideComponentProperties()
excelSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(0))
excelInstance.SetComponentProperty("AccessMode", 0)
excelInstance.SetComponentProperty("OpenRowset", "business_codes$")
excelCon.AcquireConnection(Nothing)
'excelInstance.ReinitializeMetaData()
excelInstance.ReleaseConnections()
Dim sqldev As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New()
sqldev.Name = "sqldev"
sqldev.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
Dim sqldevInstance As CManagedComponentWrapper = sqldev.Instantiate()
sqldevInstance.ProvideComponentProperties()
sqldev.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(p.Connections(1))
sqldevInstance.SetComponentProperty("AccessMode", 0)
sqldevInstance.SetComponentProperty("OpenRowset", "business_codes")
sqldevInstance.AcquireConnections(Nothing)
sqldevInstance.ReinitializeMetaData()
sqldevInstance.ReleaseConnections()
Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()
path.AttachPathAndPropagateNotifications(excelSource.OutputCollection(0), sqldev.InputCollection(0))
MsgBox(excelSource.OutputCollection.Count)
'For Each input As IDTSInput90 In sqldev.InputCollection
' Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput
' For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection
' ' Call the SetUsageType method of the design time instance of the component.
' sqldevInstance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)
' Next
'Next
Dim app As Application = New Application()
app.SaveToXml("c:myXMLPackage.dtsx", p, Nothing)
I am trying to get the value of a column in the Input0_ProcessInputRow function and I have the column name.
There has to be an object in the pipeline that will allow me to do this right?
Something like "ComponentMetaData.InputCollection(0).InputColumnCollection([COLUMN NAME])"
Can someone recommend how I would do this. I have tried a few things, but can't seem to get to the Value or ToString of the column I want in this row.
Thanks
Hi
A script component receives some input. But I just can't get at the first row??
Basically, if i use the NextRow method in the in the Do statement, then it advances the row collection to the second row before it gets into the code inside the loop?? BUT, if I use the EndOfRowset property to define my loop then I get an error:
[PipelineBuffer has encountered an invalid row index value]
I'm guessing this means...I have to call NextRow before i access the data in the collection? But thats retarted because then I miss the first row?? what? What am I missing??
This is the code which works but I miss the first row:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strConcept As String
Do While Row.NextRow()
strConcept = Row.concept
updateDb(strConcept)
Loop
End Sub
This is the code which throws the invalid row index error:
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strConcept As String
Do While Not Row.EndOfRowSet()
strConcept = Row.concept
updateDb(strConcept)
Row.NextRow()
Loop
End Sub
I've put some try catches in there an the error happens on the line which calls Row.concept....?
Can anyone help, it must be something I'm messing up
thanks!!
andy
I need to validate my input rows. The row is valid if there exist some other input rows in the same table (I am importing data from excel and access). I'll give an example to make everything clear:
Input table boys has following columns:First_Name ,Surname and Date_of_birth.
Output table is Twin_Triple_More_Brothers. I would like to insert into this table only boys that surnames are equal and difference in date of birth is less then one day.
I was thinking about lookup component, but I cannot use it in that way (or I just do not know how).
Maybe someone has an idea how to do this?
Thanks for help.
If a component requires a sorted input it would seem reasonable that you can check the IsSorted property of the attached input, but this will always return false. I have tried this when connecting the output of the Sort transform to my component, and then check the IsSorted property for this input. It is always false. How can this be, and also how can I see if the path is indeed sorted?
If using a virtual input column in my UI, I get a SortKeyPosition on the columns, but when overriding SetUSageType in the component class I always get zero for the key. Why is the sort information not quite there for me?
I'm very new to SQL Server so please forgive me if this question is ridiculously simple. I have to upgrade the report engine from one that was used in a legacy VB6 app to a C#.net app. In doing so, I'm looking at assorted reports that came out of the old app. Here is the SQL code for one of them:
SELECT (LTRIM(STR(From_To,10,0)) + '-' + LTRIM(STR(From_To + 49,10,0))) AS Bonus_Earned, COUNT (Empl_ID) AS Men, round(SUM (SumDollars),2) AS Group_Earn, round((SUM (SumDollars) / COUNT (Empl_ID)),2) AS Calc0 FROM (SELECT CONVERT (int, round (SumDollars / 50, 2)) * 50 AS From_To, SumDollars, Empl_ID FROM (SELECT round(SUM (Actual_Hours_Dollars.Incentivedollars * Contract_History.Bonus_Pct / 100), 2) AS SumDollars, employees.Empl_ID FROM ((Employees INNER JOIN Actual_hours_dollars ON Employees.Empl_ID = Actual_Hours_Dollars.Empl_ID_R) INNER JOIN Contracts ON Actual_Hours_Dollars.Contract_ID = Contracts.Contract_No) INNER JOIN Contract_History ON Contracts.Contract_Idx = Contract_History.Contract_Idx_R where employees.empl_idx = (SELECT max(empl_idx) FROM Employees AS OuterEmployees WHERE OuterEmployees.empl_id = employees.empl_id AND outeremployees.Datex = (SELECT max(datex) FROM Employees AS InnerEmployees WHERE InnerEmployees.empl_id = employees.empl_id AND Inneremployees.disabled = 0 AND Inneremployees.Datex < '~EndDate~')) AND Contracts.Datex = (SELECT max(datex) FROM Contracts AS InnerContracts WHERE InnerContracts.contract_no = Contracts.contract_no AND InnerContracts.disabled = 0 and InnerContracts.deleted = 0 AND InnerContracts.Datex < '~EndDate~') AND Actual_hours_dollars.Datex >= '~StartDate~' AND Actual_Hours_Dollars.Datex < '~EndDate~' AND dateadd(month, Contract_History.Monthx - 1, dateadd(year, Contract_History.Yearx - 1900, '01 Jan 1900')) >= '~StartDate~' AND dateadd(month, Contract_History.Monthx - 1, dateadd(year, Contract_History.Yearx - 1900, '01 Jan 1900')) < '~EndDate~' and Actual_Hours_Dollars.IncentiveHours <> 0 GROUP BY employees.empl_ID) AS InnerRS1 GROUP BY ROUND (SumDollars * 2, -2) /2, SumDollars, Empl_ID) AS InnerRS2 GROUP BY From_To
I'm only including it for completeness. The key thing I'd like to draw your attention to are two variables that are clearly input parameters: ~StartDate~ and ~EndDate~.
My question is this: If I want to copy this code into SQL Query Analyzer and run it to see what kind of results I get back, what's the simplest way to define these two input parameters? I'm hoping you could just show me the syntax to define them above the SELECT statement.
Robert Werner
Vancouver, BC
Is it possible to allow user input via a Reporting Services Report? What I mean is could a report be created that would allow someone using the report to enter a number that would be written to the SQL database?
View 1 Replies View RelatedIn Visual Web Developer I
have created a data input form based on the documentation I found in the .NET
Framework Class Library (SqlDataSource.InsertCommand Property). Originally my form contained 4 textboxes
(FirstName, LastName, Phone, Email) and worked fine. All this data is nvarchar string data.
When I added a checkbox I
get an error that “String
was not recognized as a valid Boolean�
when the checkbox is checked.
When the checkbox is unchecked the data is input without a problem. This is a bit data field in the table.
Here is my VB code, Insert
Parameters for my SqlDataSource1, and button code:
<script
runat="server">
Private Sub InsertData(ByVal
Source As Object,
ByVal e As
EventArgs)
SqlDataSource1.Insert()
End Sub
</script>
<InsertParameters>
<asp:formParameter Name="FirstName" Type="String" FormField="txtFirstName"/>
<asp:formParameter Name="LastName" Type="String" FormField="txtLastName"/>
<asp:formParameter Name="Phone" Type="String" FormField="txtPhone"/>
<asp:formParameter Name="Email" Type="String" FormField="txtEmail"/>
<asp:formParameter Name="FreeInfo" Type="Boolean" FormField="checkbox1"/>
</InsertParameters>
<asp:Button id="Button1" runat="server" text="Submit" OnClick="insertdata"/>
I suspect that the problem
is with the VB code which will only accept string input data, but don’t know
how to fix it. Any thoughts on how to
fix this problem? Thanks in advance for
any help provided.