|
DTS is called SSIS[^] in Sql Server 2005.
|
|
|
|
|
|
Hi friends
I want to know how to create a job in MSsql.
Thanks in advance
Pathan
---------------------------------------------------
|
|
|
|
|
|
I am using an OLEDB sequential stream to read data from a SQL Server stored procedure that generates XML output using FOR XML. The output is non-terminated double byte character strings preceded by 68 decimal and the length, e.g. 68 02 69 0 100 0 is "Ed". Then the next string starts. What C++ function(s) are used to decode this?
|
|
|
|
|
DECODE in SQL Server
SELECT CASE WHEN Assignment_Type = 'C' THEN 'Com' ELSE 'Donot Know'
FROM ASSIGNMENT
When I try to use CASE WHEN, it gave me error [Microsoft][ODBC SQL Server
Driver][SQL Server]Incorrect syntax near the keyword 'FROM'. What did I do
wrong?
Thanks
"David Portas" <remove_before_replying_dportas@acm.org> wrote in message
news:24GdncWmqJeLKfzcRVn-vg@thehitcounters.com...[color=blue]
> Use CASE
>
> --
> David Portas
> SQL Server MVP
> --
>
>[/color]
http://www.thehitcounters.com
Custom Logo Design Services by http://www.thebusinesslogo.com
modified on Wednesday, May 6, 2009 6:18 AM
|
|
|
|
|
Answer: add TYPE to the SELECT statement. This will return XML format instead of nvarchar. For example,
SELECT Quantity, Price FROM OrderDetails FOR XML AUTO, TYPE
|
|
|
|
|
I need to know ,How i can create publication?
SQL Server 2000
Rami Abd Alhalim
|
|
|
|
|
How can we rollback the operation performed on a table in the trigger code ? And another question : If an error occures in the trigger code will the operation performed on the table be rolled back ?
|
|
|
|
|
you can used begin transaction
Rami Abd Alhalim
|
|
|
|
|
Do anyone have any idea about how to store large amount of text(like codeproject articles) ? One solution would be to store it in a table field with ntext or text type. And the other way may be to store it in text files on hard disk. We also want to be able to search our text data. In the first solution we can do this more easily but in the second one I dont know how we would do it .
I will be waiting for anyone who may have any idea about this issue. Which method would you prefer and why ? And say if you have any other solution . Also say how you would accomplish the search if you were to store text on hard disk ?
|
|
|
|
|
If you want to search it all, then you need to store it in the DB. If you want to search just keywords, you may elect to store it in the file system, and store the keywords with the path.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Hi all,
I am using ASP.net 1.1 with backend MSSQL.
I have a table Category
Catid Catname
C1 Test1
C2 Test2
I have a Request table
id request categoryid status flag
1 abcd Request C1 0 F
2 xyz request C2 0 F
3 sdf request C1 1 I
4 ab Request C1 1 I
5 prz request C1 1 I
6 frf request C2 1 F
7 frf request C1 1 F
8 frf request C1 1 F
i want show its countlike
CaetgoryName Waiting INCOMING OUTgoing
Test1 1 3 2
TEST 2 1 0 1
waiting means status=0 and flag='O"
INCOMING mean status=1 and flag='I'
outgoing means status=1 and flag='O'
how i make sql query???
is dere any way show this to my webform???
-- modified at 2:39 Saturday 18th August, 2007
All I ever wanted is what others have.... CrazySanker
|
|
|
|
|
Don't cross-post your queries.
Paul
|
|
|
|
|
I got its solution.. but i dodnt know is it a right way or not..
create one UDF Funtion
<br />
<br />
CREATE FUNCTION GetCount (@Request CHAR(1), @CatID VARCHAR(5)) <br />
RETURNS INT AS <br />
BEGIN <br />
<br />
Declare @Count INT<br />
<br />
IF @Request = 'W' --Waiting for approval<br />
SELECT @Count = count(RemitterRefNo) from TblRemittanceTxn<br />
WHERE Category='O' AND status=0 AND Catid=@CatID<br />
<br />
ELSE IF @Request = 'I' ---Incoming <br />
SELECT @Count = count(RemitterRefNo) from TblRemittanceTxn<br />
WHERE Category='I' AND status=1 AND Catid=@CatID<br />
<br />
ELSE IF @Request = 'O' --OutGoing<br />
SELECT @Count = count(RemitterRefNo) from TblRemittanceTxn<br />
WHERE Category='O' AND status=1 AND Catid=@CatID<br />
RETURN @Count<br />
<br />
END<br />
and write a query like this
<br />
Select distinct CatName, <br />
dbo.GetCount('W',catid) as waiting_count,<br />
dbo.GetCount('I',catid) as incoming_count,<br />
dbo.GetCount('O',catid) as outgoing_count<br />
from TblCategory <br />
<br />
All I ever wanted is what others have.... CrazySanker
|
|
|
|
|
|
Don't cross-post your queries.
Paul
|
|
|
|
|
hi
thankyou for your information about my problem that write it in subject in your site
i read it but if it is possible for you please send more explane it for me
i never used this service befor and dont know where and how i can use it.
thank for your help
|
|
|
|
|
I don't really understand what your problem is.
What reporting technology are you using? Or are you taking data directly from the database?
Paul
|
|
|
|
|
i want reporting in web pages with asp.net but if we can taking data or reporting directly from sql server please write for me
thanks for helping
|
|
|
|
|
SQL Reporting Services can be used with ASP.NET. It provides the facility to export reports to a number of formats, including Excel and PDF.
Paul
|
|
|
|
|
hi thank you for helping
i read this is other places but i dont know how i can use of SQL Reporting Services.
i set SQL Server Surface Area Configuration and i know i must write a Query but i dont know where i write it and how relate it to asp.net. offcorse i read "Export huge data to Excel" topic in this site but i dont know that solve my problem or not. i want use this services in asp.net and i dont know how do i use it
|
|
|
|
|
I need some help writing a stored proc. First, I am inserting a range of ZipCodes into a db, ZipBegin and ZipEnd(12345-12346). Although that seems relatively simple, however, I need to check those ranges and make sure that there is no other over lapping range.
for more clarity:
range A 12345-12346 is in the db
range B 12344-12347 is being inserted.
as you can see these ranges overlap, if that occurs I need to use a raiserror and rollback the transaction.
Does anyone have any suggestions on how I could do that?
Chance favors the prepared mind....
|
|
|
|
|
Something like:
create procedure dbo.usp_InsertZipCodeRange
@StartZip int,
@EndZip int
as begin
declare @CurrentZip int
if (@StartZip > @EndZip or @StartZip < 0) begin
raiserror 'Invalid arguments specified'
return @@ERROR
end
if exists (select 1 from dbo.ZipCodes
where ZipCode between @StartZip and @EndZip) begin
raiserror 'Specified range overlaps existing Zip codes'
return @@ERROR
end
set @CurrentZip = @StartZip
while (@CurrentZip <= @EndZip) begin
insert into dbo.ZipCodes (ZipCode) values (@CurrentZip)
set @CurrentZip = @CurrentZip + 1
end
return 0
end If Zip codes are strings then Right('00000' + Convert(varchar, @CurrentZip), 5) should allow you to convert an integer value into the appropriate string.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi, guys. I really need some assistance with the DoCmd.SendObject object in VBA in Microsoft Access. It appears that I am only limited to specific ObjectTypes that does not include an approach to do so dynamically in code.
It appears in code that there are fewer options than there are with the implementation of macros which also permit "Stored Procedures" and "Server Views". Can I send the results of strSQLFQT to the SendObject? You see, I need to loop through two tables at the same time, and for every occurrence of the DISTINCT item in rstMRL there could be many records in rstFQT. I would like to send only the relevent records from rstFQT to each person in rstMRL. Can someone kindly tell me where I am going wrong.
The code I have so far is as follows:
Option Compare Database
Dim db As New DAO.DBEngine
Dim rec As Recordset
Function Email_New()
On Error GoTo Email_New_Err
DoCmd.OpenQuery "Final Query Test 2", acViewNormal, acReadOnly
DoCmd.GoToRecord acQuery, "Final Query Test 2", acFirst
Call SendEmails
Email_New_Exit:
Exit Function
Email_New_Err:
MsgBox Error$
Resume Email_New_Exit
End Function
Function SendEmails()
On Error GoTo ErrorHandler
Dim Cnxn As ADODB.Connection
Dim strConn As String
Dim rstMRL As ADODB.Recordset
Dim cmdSQLMRL As ADODB.Command
Dim strSQLMRL As String
Dim rstFQT As ADODB.Recordset
Dim cmdSQLFQT As ADODB.Command
Dim strSQLFQT As String
Dim strHoldRecString As String
Dim strMessage As String
Set Cnxn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\GELCO DATABASE\Gelco_USA.mdb;"
Cnxn.Open strConn
Set cmdSQLMRL = New ADODB.Command
Set cmdSQLMRL.ActiveConnection = Cnxn
strSQLMRL = "Select [Rep Number], [Rep ID], [Email Address] " & _
"FROM [Master Rep List Test] ORDER BY [Rep Number]"
cmdSQLMRL.CommandType = adCmdUnknown
cmdSQLMRL.CommandText = strSQLMRL
Set rstMRL = cmdSQLMRL.Execute()
rstMRL.MoveFirst
strHoldRecString = rstMRL![Rep Number]
Set cmdSQLFQT = New ADODB.Command
Set cmdSQLFQT.ActiveConnection = Cnxn
' Attempted Dynamic SQL here...
strSQLFQT = "SELECT [Confirmation #], [Rep Name], " & _
"[Report #], [Email Address], [Days Aged] " & _
"FROM [Final Query Test 2] WHERE [Days Aged] > 30 " & _
"AND [Rep ID2] = '" & strHoldRecString & "'"
MsgBox strSQLFQT ' Shows the results
cmdSQLFQT.CommandText = strSQLFQT
Set rstFQT = cmdSQLFQT.Execute()
rstFQT.MoveFirst
Do Until rstMRL.EOF
DoCmd.SendObject acSendQuery, "Final Query Test 2", acFormatXLS, _
"expense.reports@wrigley.com", , , "Missing Expense Reports", _
"Hello There, you have some files missing", False
' Somehow requery the code from the second table here...
strHoldRecString = rstMRL![Rep Number]
strSQLFQT = "SELECT [Confirmation #], [Rep Name], " & _
"[Report #], [Email Address], [Days Aged] " & _
"FROM [Final Query Test 2] WHERE [Days Aged] > 30 " & _
"AND [Rep ID2] = '" & strHoldRecString & "'"
rstMRL.MoveNext
Loop
' I am able to loop through the records for each table properly with this code which should identify the fields that I need to include.
'Do Until rstMRL.EOF
'strMessage = "Rep Number: " & rstMRL![Rep Number] & _
' vbCr & " Rep ID: " & rstMRL![Rep ID] & _
'vbCr & " Email Address: " & rstMRL![Email Address] & ""
'MsgBox strMessage
'rstMRL.MoveNext
'Loop
'Do Until rstFQT.EOF
'strMessage = "Confirmation #: " & rstFQT![Confirmation #] & _
' vbCr & " Rep Name: " & rstFQT![Rep Name] & _
'vbCr & " Report #: " & rstFQT![Report #] & _
' vbCr & " Email Address #: " & rstFQT![Email Address] & _
' vbCr & " Days Aged: " & rstFQT![Days Aged] & ""
'MsgBox strMessage
' rstFQT.MoveNext
'Loop
ErrorHandler:
If Not rstMRL Is Nothing Then
If rstMRL.State = adStateOpen Then rstMRL.Close
End If
Set rstMRL = Nothing
If Not rstFQT Is Nothing Then
If rstFQT.State = adStateOpen Then rstFQT.Close
End If
Set rstFQT = Nothing
If Not Cnxn Is Nothing Then
If Cnxn.State = adStateOpen Then Cnxn.Close
End If
Set Cnxn = Nothing
If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
End Function
-- modified at 13:58 Friday 17th August, 2007
|
|
|
|