get an error on a sample code

This is a sample code from an MSDN help site. I copied it and pasted into an open new query. I tried to execute it and got two errors:

USE AdventureWorks;
GO
DECLARE @.tablename sysname
SET @.tablename = N'Person.AddressType'
table_loop:
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SELECT @.tablename = RTRIM(UPPER(@.tablename))
EXEC ('SELECT ''' + @.tablename + ''' = COUNT(*) FROM '
+ @.tablename )
PRINT ' '
END
FETCH NEXT FROM tnames_cursor INTO @.tablename
IF (@.@.FETCH_STATUS <> -1) GOTO table_loop
GO

The errors are:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'PERSON.ADDRESSTYPE'.

Msg 16916, Level 16, State 1, Line 9
A cursor with the name 'tnames_cursor' does not exist

The database is connected. Table Person.AddressType is a part of it.

What is wrong?

Thanks.

Did you get this from the GOTO help topic? This note is in the topic right above the example:

Note:

The tnames_cursor cursor is not defined. This example is for illustration only.

so effectively this example is expected not to work. If you need some specific help on using the GOTO syntax, I would suggest asking in the T-SQL forum, that's where the syntax gurus hang out.

Mike

Get a value from SQL Database...

Sorry, this maybe a very easy problem... I just want to know how get a value from SQL database ... since I am new to SQL server...

I already have code like this:

Dim queryString As String = "SELECT Password FROM [User] WHERE Name = 'Sprite'"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

What should I do next to get the value ?

thx for your help

:)

Just had a try, and it works~ haha...

Here is the code for other newbies...

Dim connectionString As String = "Connection String Here"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "Your SQL String Here"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbPassword as String
dbConnection.Open()
dbPassword = CType(dbCommand.ExecuteScalar, String)
dbConnection.Close()

Sorry for the trouble I made...

Get a value

This is my code...

Sub btnLogin_OnClick(Src As Object, E As EventArgs)
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
Dim intUserCount As Integer
Dim strSQL As String

strSQL = "SELECT COUNT(*) FROM tblLoginInfo WHEREusername=@.UserName AND

password=@.Password"

myConnection = New SqlConnection("server=

(local);database=Database;trusted_connection=true")

myCommand = New SqlCommand(strSQL, myConnection)

myCommand.Parameters.add("@.UserName",sqldbtype.varchar).value=txtUserName.text

myCommand.Parameters.add("@.Password",sqldbtype.varchar).value=txtPassword.text

myConnection.Open()
intUserCount = myCommand.ExecuteScalar()
myConnection.Close()

If intUserCount > 0 Then
lblInvalid.Text = ""
FormsAuthentication.SetAuthCookie(txtUsername.Text, True)
Response.Redirect("login_db-protected.aspx")
Else
lblInvalid.Text = "Sorry... try again..."
End If
End Sub

On the table 'tblLoginInfo' there are 3 fields... username, password and destination.
When a user clicks on the btnLogin... i want to get the value of the destination field that

lines up with the same username and password fields and place it in a string called 'test'.

Cannning

Hi, just replace count(*) with destination:

strSQL = "SELECT destination FROM tblLoginInfo WHEREusername=@.UserName AND

password=@.Password"

myConnection = New SqlConnection("server=

(local);database=Database;trusted_connection=true")

myCommand = New SqlCommand(strSQL, myConnection)

myCommand.Parameters.add("@.UserName",sqldbtype.varchar).value=txtUserName.text

myCommand.Parameters.add("@.Password",sqldbtype.varchar).value=txtPassword.text

myConnection.Open()

string test = (string) myCommand.ExecuteScalar ( ) ;

myConnection.Close()

Hope this helps

Get a list of columns

I'm trying to get a list of columns from a table. Here is my code:

public List<string> GetColumns(string serverName, string dataBaseName, string tableName)

{

List<string> columns = new List<string>();

Server server = new Server(serverName);

server.ConnectionContext.LoginSecure = true;

server.ConnectionContext.Connect();

Database dataBase = new Database(server, dataBaseName);

Table table = new Table(dataBase, tableName);

foreach (Column column in table.Columns)

{

columns.Add(column.Name);

}

columns.Sort();

return columns;

}

The problem is that table.Columns.Count is 0. How do I get a list of columns in a table?

I got this working. Instead of getting a reference to each object individually:

Database dataBase = new Database(server, dataBaseName);

Table table = new Table(dataBase, tableName);

Then looping through the Columns collection of the table, I combined it all in one:

foreach (Column column in server.Database[dataBaseName].Tables[tableName].Columns)

This now gets me a list of all the columns in the table.

Generic Stored Procedure

I have 24 lookup tables that contain the same columns (e.g. Rec_Id (identity field), Code, Desc) and I need to build an interface for each of these tables with Create, Update, and Delete capabilities.

I would like to create three stored procedures (create, update, delete) that would perform the task for any of the lookup tables.

For example, table AGENCY has three fields (Agency_id (identity field), Agency_Code, Agency_Desc) and I need to add a new record to AGENCY. I would like to call a stored procedure that would take the parameters @.tablename, @.code, @.desc, and then create a new record in the table specified by @.tablename.

I could then use a single "create" stored procedure for all of the lookup tables when adding a new record, rather than creating a separate "create" stored proc for each of my lookup tables.

Any help would be greatly appreciated!::I would like to create three stored procedures (create, update, delete) that would perform
::the task for any of the lookup tables.

Forget it. Not a feasible way.|||Thanks for your input!

I'm currently writing separate stored procs for each of my lookup tables and will continue to do so until somebody shows me a better way.|||Skip the SP's, go with dynamic SQL and use an intelligent DAL to never maintain the SQL anymore.|||I've written generic stored procedures in the past. The trick is to use the EVAL function of SQL. As an example, here is a stored procedure that would take a table name as parameter and return all records from this table.

The stored procedure is defined as follow:
CREATE PROCEDURE SP_Query
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')

GO

You must call it as follow:
SP_Query 'Customers'

As your tables have the same columns it should be easy to built your 3 generic stored procedures in the same way as my example.

Good luck,
Olivier Giulieri
www.kakoo.net|||You can use a mixture of dynamic as static.
Have a utility the writes stored proces and either map them or construct their name.

So...
Template:
Update<table>
Gives:
UpdateMyTable1
UpdateMyTable2
...etc

Then the dynamic part is constructing the name of the stored proc...
CommandText = "Update" + strTableName

PS Don't prefix stored procs with "SP_" it's bad news.|||Hm,

you guys do know the disadvantage of using the EVAL function in SQL?

And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.

I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').|||I was just making a quick example of stored procedure to illustrate the use of "EVAL". For sure, I picked a very bad name. Here is my example again:

CREATE PROCEDURE GenericQuery
(
@.table nvarchar(100)
)
AS
EXEC('SELECT * FROM ' + @.table + ' ORDER BY ID')

GO

PS: Am I accused of not reading documentations because you said "Forget it. Not a feasible way." and my solution may work? Please let's just try to be constructive here.|||*If* you do need to use an "EVAL" function then consider using sp_executeSQL instead. It's geared up for param' queries.|||<Hm,

you guys do know the disadvantage of using the EVAL function in SQL?

And why do you prefix your stored procedures with "sp_", which is "system procedure" and has special treatment in SQL Server - and none you will like.

I suggest a look into the documentation. Personally I am always reluctant to take advice from people who have not read the basics in the documentation (such as NOT naming stored procedures with 'sp_').
Easy now big fella! ;)

Check every website that has examples, I will bet 99% use sp_ . For the record I do not, but if you take samples from the net, or even Microsoft you will see they almost always use sp_ so you cannot get too uptight at people for following the convention, even if it does reduce processing efficiency.|||There is no 'good' way to do this.

If you use a new proc for each it's a lot of writing and a lot of changing if it changes. If you use dynamic stored procs you lose the security of refusing anyone direct access to your tables (dynamic stored procs require the use to have table access) and they run much more slowly.

My work around when this happens,

Write one proc per table.
Write one proc which the application calls, it then calls the correct proc. That way your application programmers only need to remember one stored procedure to call and you do the rest.

Avoid using IF statements in an SQL proc.

If you have something like this:

IF X
 Begin
 {code code code}
 End
Else
 Begin
 {code code code}
 End

When you execute only half of the proc will be in the plan and run efficiently. The other half will be adhoc.

Consider this:

If X
 Begin
 exec prCode1
 End
Else
 Begin
 exec prCode2
 End

Since each If calls a procedure that has an execution plan it runs more efficiently (AKA faster).

</code>

Generating wrapper code for SQL Server Stored Procedure

Hi

How Can I Generating wrapper code for SQL Server Stored Procedure ??

If You will Go To The Following link you will see an example for Generating wrapper code for Oracle Database .. And Also the author say there is built in tool for Generating wrapper code for SQL Server
http://www.codeproject.com/vb/net/OracleSPWrapper.asp
my question .. where is this tools ?

and thanks with my regarding

FraasYou are probably talking about the Data Adapter wizard in the design view. See this walkthrough:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbwlkWalkthroughUsingDataGridWebControlToReadWriteData.asp

Generating report from Code

I need to generate and save a report to disk from a standard Windows
Application without user intervention.
So basically, a user clicks a button, and a report is saved to his c:\ in a
predefined format and with a predefined filename.
Anyone?
J. Jespersen> So basically, a user clicks a button, and a report is saved to his c:\ in
> a
> predefined format and with a predefined filename.
Correction: File should just be saved to the servers c:\|||This code will allow you to generate a report, and create a PDF file from it.
You can tweak the parameters to export the format and type you wish:
public static void deliverReport()
{
MyReportingService.ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Render arguments
byte[] rpt = null;
string reportPath = "/ReportProject1/Report1";
string format = "PDF";
string historyID = null;
string devInfo =@."<DeviceInfo><StartPage>0</StartPage><PageHeight>8.5in</PageHeight><PageWidth>14in</PageWidth></DeviceInfo>";
ParameterValue[] parameters = new ParameterValue[0];
DataSourceCredentials[] credentials = null;
string showHideToggle = null;
string encoding;
string mimeType;
Warning[] warnings = null;
ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;
SessionHeader sh = new SessionHeader();
rs.SessionHeaderValue = sh;
rpt = rs.Render(reportPath, format, historyID, devInfo,
parameters, credentials,
showHideToggle, out encoding, out mimeType, out
reportHistoryParameters, out warnings,
out streamIDs);
FileStream stream = File.Create("test.pdf", rpt.Length);
stream.Write(rpt, 0, rpt.Length);
stream.Close();
}
This URL may help you as well:
http://www.csharphelp.com/archives3/archive545.html
Cheers,
Rich
"Jeppe Dige Jespersen" wrote:
> > So basically, a user clicks a button, and a report is saved to his c:\ in
> > a
> > predefined format and with a predefined filename.
> Correction: File should just be saved to the servers c:\
>
>|||Thank you Rich. Works perfectly! :-)
Jeppe Jespersen
Denmark
> This code will allow you to generate a report, and create a PDF file from
> it.
> You can tweak the parameters to export the format and type you wish:
> public static void deliverReport()
> {
> MyReportingService.ReportingService rs = new
> ReportingService();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> // Render arguments
> byte[] rpt = null;
> string reportPath = "/ReportProject1/Report1";
> string format = "PDF";
> string historyID = null;
> string devInfo => @."<DeviceInfo><StartPage>0</StartPage><PageHeight>8.5in</PageHeight><PageWidth>14in</PageWidth></DeviceInfo>";
> ParameterValue[] parameters = new ParameterValue[0];
> DataSourceCredentials[] credentials = null;
> string showHideToggle = null;
> string encoding;
> string mimeType;
> Warning[] warnings = null;
> ParameterValue[] reportHistoryParameters = null;
> string[] streamIDs = null;
> SessionHeader sh = new SessionHeader();
> rs.SessionHeaderValue = sh;
> rpt = rs.Render(reportPath, format, historyID, devInfo,
> parameters, credentials,
> showHideToggle, out encoding, out mimeType, out
> reportHistoryParameters, out warnings,
> out streamIDs);
> FileStream stream = File.Create("test.pdf", rpt.Length);
> stream.Write(rpt, 0, rpt.Length);
> stream.Close();
> }
> This URL may help you as well:
> http://www.csharphelp.com/archives3/archive545.html
> Cheers,
> Rich
> "Jeppe Dige Jespersen" wrote:
>> > So basically, a user clicks a button, and a report is saved to his c:\
>> > in
>> > a
>> > predefined format and with a predefined filename.
>> Correction: File should just be saved to the servers c:\
>>

Generating primary keys

What's the best way to generate primary keys for inserting a new row in a
table. Currently I'm using code create a datareader which gets the highest
number from the primary key column and adds 1 for the new row insertion.
There is a lot of overhead in this as it requires a separate round trip to
the database each time row/rows are inserted.
I'm sure there is a much better method... Any suggestions
ThxA common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx|||Then you use the SCOPE_IDENTITY() or @.@.IDENTITY system functions to reteive
the value within the same batch that executes the INSERT statement.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Mg2rh8HFHA.1172@.TK2MSFTNGP12.phx.gbl...
A common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx

Generating primary keys

What's the best way to generate primary keys for inserting a new row in a
table. Currently I'm using code create a datareader which gets the highest
number from the primary key column and adds 1 for the new row insertion.
There is a lot of overhead in this as it requires a separate round trip to
the database each time row/rows are inserted.
I'm sure there is a much better method... Any suggestions
Thx
A common method in the SLQ Server world is to define the PK column with the IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx
|||Then you use the SCOPE_IDENTITY() or @.@.IDENTITY system functions to reteive
the value within the same batch that executes the INSERT statement.
Sincerely,
Anthony Thomas

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Mg2rh8HFHA.1172@.TK2MSFTNGP12.phx.gbl...
A common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx

Generating primary keys

What's the best way to generate primary keys for inserting a new row in a
table. Currently I'm using code create a datareader which gets the highest
number from the primary key column and adds 1 for the new row insertion.
There is a lot of overhead in this as it requires a separate round trip to
the database each time row/rows are inserted.
I'm sure there is a much better method... Any suggestions
ThxA common method in the SLQ Server world is to define the PK column with the IDENTITY attribute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thx|||Then you use the SCOPE_IDENTITY() or @.@.IDENTITY system functions to reteive
the value within the same batch that executes the INSERT statement.
Sincerely,
Anthony Thomas
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Mg2rh8HFHA.1172@.TK2MSFTNGP12.phx.gbl...
A common method in the SLQ Server world is to define the PK column with the
IDENTITY attribute.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"jwk" <jwk@.discussions.microsoft.com> wrote in message
news:036BF33E-EC48-47B1-8B98-F78ECF70C6A6@.microsoft.com...
> What's the best way to generate primary keys for inserting a new row in a
> table. Currently I'm using code create a datareader which gets the highest
> number from the primary key column and adds 1 for the new row insertion.
> There is a lot of overhead in this as it requires a separate round trip to
> the database each time row/rows are inserted.
> I'm sure there is a much better method... Any suggestions
> Thxsql

Generate "RDL" xml file with XMLA.

HI,,,,,,,,,,EveryBody...
Does anybody have code sample for on the fly report generation , generate
"RDL" xml file with XMLA.
pls help me,
I am really stuck with this problem , becoz I have posted many posts but
still I didnt get a helpfull machanizam.
Thanks
Summa.Hi Sumudu,
We are working on this project and we have an alpha product now you can
generate your RDL on the fly no XML no VS
www.rdlcomponents.com
Jerry
"Sumudu Prasad" wrote:
> HI,,,,,,,,,,EveryBody...
> Does anybody have code sample for on the fly report generation , generate
> "RDL" xml file with XMLA.
> pls help me,
> I am really stuck with this problem , becoz I have posted many posts but
> still I didnt get a helpfull machanizam.
> Thanks
> Summa.
>
>

General t-sql help.

The following is my code. What I am trying to do is find all the students a teacher as assessed during a give time. Then find out which assessment was done the most recently. After that I will then be aggregating those results. I have never written any pl/sql or T-SQL... heck I don't even know what to call it!
The first sql command is doing what I want it to. I can only assume the cursor is working correctly.
Any help would be greatly appreciated.
Bryan

ALTER procedure Domain
@.UserID numeric,
@.StartDate datetime,
@.EndDate datetime

AS
-- Variable Decleration
DECLARE @.SessionID varchar(1000)
DECLARE @.EachSessionID numeric

--Cursor to find all Children that the teacher has assessed for the given time
DECLARE ChildID_cursor CURSOR
FOR
SELECT DISTINCT childID
FROM capsession
WHERE userid = @.UserID
AND sessiondate BETWEEN @.StartDate AND @.EndDate
-- looping through all Children to find there most recent assessment.
OPEN ChildID_cursor
DECLARE @.ChildID numeric
FETCH NEXT FROM ChildID_cursor INTO @.ChildID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
DECLARE SessionID_cursor CURSOR
FOR
SELECT TOP 1 CAPSessionID
FROM CapSession
WHERE (ChildID = @.ChildID) AND (SessionDate BETWEEN @.StartDate AND @.EndDate)
ORDER BY SessionDate DESC
END
FETCH NEXT FROM ChildID_cursor INTO @.ChildID
END
CLOSE ChildID_cursor

OPEN SessionID_cursor
FETCH NEXT FROM SessionID_cursor into @.EachSessionID
SET @.SessionID = ''
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
SET @.SessionID = @.SessionID + @.EachSessionID+ ','
END
FETCH NEXT FROM ChildID_cursor INTO @.ChildID
END
CLOSE SessionID_cursor
RETURN @.EachSessionID

It's T-SQL. I urge you to always try to use a single SELECTstatement and notresort to cursors. T-SQL is a set-basedlanguage and code is processed most efficiently when dealing withsets. Cursors are procedural and inefficient.
You can do what you need with a single SELECT statement. I believe this will cover it:
SELECT childID, MAX(SessionID) AS SessionID
FROM capsession
WHERE userid = @.UserID
AND sessiondate BETWEEN @.StartDate AND @.EndDate
GROUP BY childID

|||First of all that SQL worked perfectly Thanks! You really knocked that one out of the park. I have a couple questions for you. My understanding of T-SQL is that it should be used for procedural work. I mean if all I am going to do is return a simple result set why use it? (Just trying to understand the theory so I can be a better programmer) Second is there a good book you can recommend so I can learn the basics.
Thanks again for your rapid and expert advice.
Bryan|||I'm glad that helped you!
I had a typo in my post -- I meant "andnot RESORT to cursors. " By procedural code, I mean code thatdoes processing in a line-by-line fashion, typically using loops toiterate through a set of data. With T-SQL, you can perform tasksin one fell swoop, which is called a set-based approach - this wouldinclude, among others, SELECT, UPDATE, and INSERT commands.
Boy, I've learned most of what I know just by participating onlistservs and forums and migrating from Visual FoxPro to Access to SQLServer.. SQL Server 2000 Books Onlineis Microsoft's documentation for SQL Server and is an awesome freeresource -- I refer to it continually. Anyone doing SQL workshould have it. Several people I know and respect haverecommended this book:Rob Veiera's Professional SQL Server 2000 Programming. Another good resource is Ken Henderson'sThe Guru's Guide to Transact-SQL, although I don't think of this as a beginner's book.