izzit got for loop in transact-sql stored procedure

Dear all,
i was confuse that izzit there are sql server was support for loop
statement,
i got one section of inner query in my asp program, and i would like
to change in to stored procedure,i using ms sql server as my database.
the following is the asp code with the inner sql
For i = 1 To 10
If not fixQuote(Request.Form("txtDescription" & i)) = "" Then
strSQL = "INSERT INTO ack_item
(Ack_id,Part_no,Serial_no,Description,Qt
y,Itm) VALUES ("& strAckID
&",'"& fixQuote(Request.Form("txtPartNo" & i)) &"',"
strSQL = strSQL & "'"& fixQuote(Request.Form("txtSerialNo" & i))
&"','"& fixQuote(Request.Form("txtDescription" & i)) &"',"&
CheckComma(Request.Form("txtQty" & i)) &","
strSQL = strSQL & "'"& fixQuote(Request.Form("txtItm" & i)) &"')"
call SetConnection(strSQL,2)
End If
Next
could i change whole code into the sql server (store procedure)?Hi
Yep , there is a WHILE loop in the SQL Server
DECLARE @.i INT
SET @.i=1
WHILE @.i<=10
BEGIN
--Do something here
SET @.i=@.i+1
END
Can you elaborate a little bit what you are doing so we can suggest a
solution without using a loop?
<yokesanhoo@.gmail.com> wrote in message
news:1140499360.016330.300370@.g44g2000cwa.googlegroups.com...
> Dear all,
> i was confuse that izzit there are sql server was support for loop
> statement,
> i got one section of inner query in my asp program, and i would like
> to change in to stored procedure,i using ms sql server as my database.
> the following is the asp code with the inner sql
> For i = 1 To 10
> If not fixQuote(Request.Form("txtDescription" & i)) = "" Then
> strSQL = "INSERT INTO ack_item
> (Ack_id,Part_no,Serial_no,Description,Qt
y,Itm) VALUES ("& strAckID
> &",'"& fixQuote(Request.Form("txtPartNo" & i)) &"',"
> strSQL = strSQL & "'"& fixQuote(Request.Form("txtSerialNo" & i))
> &"','"& fixQuote(Request.Form("txtDescription" & i)) &"',"&
> CheckComma(Request.Form("txtQty" & i)) &","
> strSQL = strSQL & "'"& fixQuote(Request.Form("txtItm" & i)) &"')"
> call SetConnection(strSQL,2)
> End If
> Next
> could i change whole code into the sql server (store procedure)?
>

It's possible to call a view from a different SQL Server?

dear friends,

It's possible to call a view from a different SQL Server? I need to create a connection inside one view from the first server to call the second server...

Thanks

Create a linked server then use a fully qualified object name: SELECT * FROM Server2.database.dbo.view|||

I did it and the point 1. works, but in point 2. with Inner join, doesnt work:

1. (OK)

SELECT * FROM GCXNCLISM1S301.SMS_B02.dbo._CPY_OfferManager_1005

2. (NOT OK)

SELECT dbo.v_R_System.Netbios_Name0, dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0, dbo.v_GS_COMPUTER_SYSTEM.Model0,

dbo.v_GS_PC_BIOS.SerialNumber0, dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0, dbo.v_R_System.User_Name0

FROM GCXNCLISM1S301.SMS_B02.dbo.v_GS_COMPUTER_SYSTEM INNER JOIN

GCXNCLISM1S301.SMS_B02.dbo.v_R_System ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN

GCXNCLISM1S301.SMS_B02.dbo.v_GS_X86_PC_MEMORY ON dbo.v_R_System.ResourceID = dbo.v_GS_X86_PC_MEMORY.ResourceID INNER JOIN

GCXNCLISM1S301.SMS_B02.dbo.v_GS_PC_BIOS ON dbo.v_R_System.ResourceID = dbo.v_GS_PC_BIOS.ResourceID

Error:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_COMPUTER_SYSTEM.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_X86_PC_MEMORY.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_PC_BIOS.ResourceID" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.Netbios_Name0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_COMPUTER_SYSTEM.Model0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_PC_BIOS.SerialNumber0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0" could not be bound.

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "dbo.v_R_System.User_Name0" could not be bound.

THE SINTAX IS DIFFERENT?

THANKS!!

|||

hi,

did you try this :

SELECT T2.Netbios_Name0, T1.Manufacturer0, T1.Model0,

T4.SerialNumber0, T3.TotalPhysicalMemory0, T2.User_Name0

FROM [GCXNCLISM1S301.SMS_B02].[dbo].[v_GS_COMPUTER_SYSTEM] T1 INNER JOIN

[GCXNCLISM1S301.SMS_B02].[dbo].[v_R_System] T2 ON T1.ResourceID = T2.ResourceID INNER JOIN

[GCXNCLISM1S301.SMS_B02].[dbo].[v_GS_X86_PC_MEMORY] T3 ON T2.ResourceID = T3.ResourceID INNER JOIN

[GCXNCLISM1S301.SMS_B02].[dbo].[v_GS_PC_BIOS] T4 ON T2.ResourceID = T4.ResourceID

...

|||

Dear Stephane,

Returns the folow error:

Msg 208, Level 16, State 1, Procedure TESTE2, Line 3

Invalid object name 'GCXNCLISM1S301.SMS_B02.dbo.v_GS_COMPUTER_SYSTEM'.

(1 row(s) affected)

Thanks

|||

OK,

I called the view directly to the remote server and it works. And I have other problem when I do a Inner Join with one table in my seever:

ALTER PROCEDURE [dbo].[TESTE4]

AS

SELECT Netbios_Name0 as CN,TotalPhysicalMemory0 as RAM, Model0=

CASE

WHEN TotalPhysicalMemory0 <600000 THEN Model0 + ' 512MB'

WHEN TotalPhysicalMemory0 >600000 THEN Model0 + ' 1GB'

END, SerialNumber0 as NrSerie,

TotalPhysicalMemory0 as RAM, User_Name0 as UserID, MA_MODELO_ID, MA_Nome

FROM GCXNCLISM1S301.SMS_B02.dbo.v_gestao_desktop_balcoes, ModeloPC_ALIAS

WHERE Model0 NOT LIKE '%ProLiant%'

AND Model0 =MA_Nome

Returns the error:

Msg 468, Level 16, State 9, Procedure TESTE4, Line 4

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

IF I substitute the last line of code for this:

AND Model0 COLLATE Latin1_General_CI_AS =MA_Nome COLLATE Latin1_General_CI_AS

It works, but the query dont return rows... And I know that there is values that join the both sources... :-(

Can anyone help me with COLLATE?

THANKS!!

Issues on Decimal Type

Dear All,

The SQL server supports 38-digit deciaml numbers. Is there any known issues, error reported or penalties if all 38 digit places are declared in DECIMAL type columns?

My server version is 2005.

Thanks for all...

Gabriel
All know issues supposed to be fixed. Considering the size, don't use 38 digit wide types unless you have to. Look at bigint datatype, which takes only 8 storage bytes instead of 17 for decimal(38,0)|||

Thanks for your reply.

If I really have to use 38-digit decimal, e.g. decimal(29,9) or decimal(10, 18), will it be any known potential problem?

Issue with size

Dear gurus,
How could I figure out the size of one database in 2004?
Linking 2004 information(mdf) with the current one I will obtain how many
points in % has grow up that database, but how' Is that information lost?
I unfortunately think so.
Thanks in advance and regards,
Enricexamnotes (Enric@.discussions.microsoft.com) writes:
> How could I figure out the size of one database in 2004?
> Linking 2004 information(mdf) with the current one I will obtain how many
> points in % has grow up that database, but how' Is that information lost?
> I unfortunately think so.
I'm not sure I understand the question, but if the question is "how can I
find out how which size the database had as some point in year 2004?", then
I'm afraid that the only way to find out is to dig up a backup from
that time and restore it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Exactly, you perfectly understood it. I am afraid that such solution would
jeopardise the system. If I have to restore each database (to tell the truth
are more than 40 databases) and then compare these data with the current one
.
The next one I'll try be more explicit. English is not my mother tongue.
Thanks in advance,
"Erland Sommarskog" wrote:

> examnotes (Enric@.discussions.microsoft.com) writes:
> I'm not sure I understand the question, but if the question is "how can I
> find out how which size the database had as some point in year 2004?", the
n
> I'm afraid that the only way to find out is to dig up a backup from
> that time and restore it.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||> jeopardise the system. If I have to restore each database (to tell the
> truth
> are more than 40 databases) and then compare these data with the current
> one.
Then one thing you could do is log to an external system the results of,
say, sp_spaceused for each database, and do so periodically (e.g. daily,
wly, monthly). Then you will have a running history going forward at
least (given that it won't help you for historic data).|||Thanks Aaron,
"Aaron Bertrand [SQL Server MVP]" wrote:

> Then one thing you could do is log to an external system the results of,
> say, sp_spaceused for each database, and do so periodically (e.g. daily,
> wly, monthly). Then you will have a running history going forward at
> least (given that it won't help you for historic data).
>
>

Issue with printing/exporting reports

Dear ppl,

I have designed a report in Visual Studio 2005. The report have a couple of data regions (table and list) + some free text boxes.. The problem is when i print or export the report to pdf etc, it prints/exports a blank page after every page. e.g. if a report is of 2 pages, it will be printed/exported as Page 1 + Blank Page + Page 2 + Blank Page...

Can any one please tell me what am i missing here?

Awaiting,

just changed the Report Page width to 22cm from 21cm and it started printing/exporting perfectly without any blank pages... I thought the A4 size page width was 21cm..strange innit?

Issue with Identity Columns after replication Changes

Dear all,

We have an application that use merge replication between MSDE and Devices with SQL CE.

Due to a major application changes, we have to change our replication on all our workstation. Our process is the following:

- drop current replication

- recreate our replication

After first check our replication seems to work but after some test we have identify that all identity ranges have been reset on the workstation. As side effect, device start to reuse existing range and also existing value in the range.

- Have you ever encounter this type of issue?

- What can create it?

- Is our approach fully inappropriate?

Please let me know if you need more information,

Best regards,

I have a client (SQL2000<->SqlCE) that is experiencing similar problems. We have been unable to determine a cause as yet.

Symptoms:

-"duplicate key on insert" errors during replication for one table (two subscribers appearing to be sharing the same number range)

-"duplicate key on insert" error with a back-end process inserting data into a publisher table (diff to the one above)

The tables are using automatic identity range management and have been synchronising happily for a long time prior to this.

Does anyone know of any actions that might cause the automatic identity range management to go screwy like this?

|||You can try re-adjusting your identity ranges, look at sp_adjustpublisheridentityrange. Otherwise i'm not sure what could have caused the issue, maybe someone manually reset the identity seed on the dbs?

Issue with Identity Columns after replication Changes

Dear all,

We have an application that use merge replication between MSDE and Devices with SQL CE.

Due to a major application changes, we have to change our replication on all our workstation. Our process is the following:

- drop current replication

- recreate our replication

After first check our replication seems to work but after some test we have identify that all identity ranges have been reset on the workstation. As side effect, device start to reuse existing range and also existing value in the range.

- Have you ever encounter this type of issue?

- What can create it?

- Is our approach fully inappropriate?

Please let me know if you need more information,

Best regards,

I have a client (SQL2000<->SqlCE) that is experiencing similar problems. We have been unable to determine a cause as yet.

Symptoms:

-"duplicate key on insert" errors during replication for one table (two subscribers appearing to be sharing the same number range)

-"duplicate key on insert" error with a back-end process inserting data into a publisher table (diff to the one above)

The tables are using automatic identity range management and have been synchronising happily for a long time prior to this.

Does anyone know of any actions that might cause the automatic identity range management to go screwy like this?

|||You can try re-adjusting your identity ranges, look at sp_adjustpublisheridentityrange. Otherwise i'm not sure what could have caused the issue, maybe someone manually reset the identity seed on the dbs?

Issue with Export to CSV

Dear Anyone,

Whenever we export reports to CSV, it seems that the column headers in the CSV fileb are the actual column names in the select statement we used in the data set and not the column names that are in the report.

Is there a way to reflect the column names in the report to the CSV file rather than the actual physical column name in the report?

Thanks,
JosephAs there is no explicit binding in RDL between the data columns and the header columns, CSV export uses the names of the controls in the export. You can either change the textbox name or you can override the name using the DataElementName property (on the data tab of the textbox properties dialog). This is just like the XML data output, described here: http://msdn2.microsoft.com/en-us/library/ms156020(en-US,SQL.90).aspx. You can also exclude them here (DataElementOutput).

I have asked the documentation people to update the CSV topic.

Issue in Transaction

Dear All,

I'm very much new to SQL and right now working with basics before moving into complex SPs.

I'm facing issues in executing a simple transaction in sql. The following is the code:

"

BEGINTRANSACTION

INSERT INTO MyDatabase.dbo.Order (OrderID) values('1234')

INSERTINTO MyDatabase.dbo.MyTable (Age)values('we')

IF(@.@.ERROR<> 0)

BEGIN

PRINT('Exception Raised')

ROLLBACKTRANSACTION

END

COMMITTRANSACTION

"

The age column is of int data type and I'm trying to rollback transaction by passing char value to that column. On executing the above query, it does not go to rollback transaction part whereas it gives the error

"

Msg 245, Level 16, State 1, Line 4

Conversion failed when converting the varchar value 'we' to data type int.

"

Kindly request you all to help me in this regard.

Most conversion errors will fire a batch abort error, which will stop the batch from executing. If you are using SQL Server 2005, you can use the new error functionality which allows you to catch statement as well as batch abort errors.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

IsSorted Property problem

Dear friends,

I have a ETL to import some data. When I try to open de merge join transformation, the BIDS give me an error. The error is the IsSorted must me set to true in both datasources, but I dont want to insert the sort transformation, because I know that both datasources are sorted... How can I update the IsSorted property for the both datasources? I didn't find the place to do it... it's not possible?

Thanks!

in the SOurce Components; you can right click, advanced properties; Input and output properties and set the IsSorted property of the 'OLE DB Source Output' equal to true. Additionally; you have to expand the 'output columns' node and set SortKeyPosition property for the proper columns. 0 means it is not sorted; 1 for the first column in the sort; 2 for the second one; 3 for the 3rd one....

Notice that by doing this you are just changing the metadata; the data will no be sorted. If data is not properly sorted; merge join will yield unexpected results. Also, make sure there are not tranformations in the dataflow, prior to the merge join; that alter the original sort of the data.

|||

Dear Rafael Salas

Thanks for your answer. I have some problem about "IsSorted Property". And I solve it by your guide. It is useful.

Again, Thanks for your support!

Tam Nguyen!

IsRowGuid

Dear Experts
How to find out whether the IsRowGuid flag is set for a particular column or not?
I tried the system tables like syscolumns, sysconstraints etc., I also tried the INFORMATION_SCHEMA view, but to no avail.
Can anybody help me out.
ThanksLook out for COLUMNPROPERTY in The Holy Book (SQL Server Books Online).

COLUMNPROPERTY
Returns information about a column or procedure parameter.

Syntax
COLUMNPROPERTY ( id , column , property )

Arguments
id

Is an expression containing the identifier (ID) of the table or procedure.

column

Is an expression containing the name of the column or parameter.

property

Is an expression containing the information to be returned for id, and can be any of these values.

Property : IsRowGuidCol
The column has the uniqueidentifier data type and is defined with the ROWGUIDCOL property.

Value Returned
1 = TRUE
0 = FALSE
NULL = Invalid input