|
Hi All,
I need to count the records of a table, but the table name is in a reference. Some thing like following.
declare @aa bigint
declare @tbl varchar(200)
set @tbl = 'mydb..myTable';
select @aa = count(*) from @tbl;
print @aa;
GO
It doesn't work right now and the message is "@tbl need to be a table". Even if @tbl is declared as table using "declare @tbl table", it still doesn't work. Any suggestions will be greatly appreciated.
Kevin
|
|
|
|
|
This will return the count in a record - any use?
declare @aa bigint
declare @tbl varchar(200)
set @tbl = 'Orders';
exec('select count(*) from ' + @tbl)
|
|
|
|
|
Hi McCodeJunky,
Many thanks for your quick response.
This is part of a procedure. I need to keep the count in @aa so I may use it later. How? Thanks.
Kevin
|
|
|
|
|
This does what you expect:
declare @tbl varchar(200)<br />
set @tbl = 'tblTest';<br />
EXEC('declare @aa bigint; select @aa = count(*) from ' + @tbl + '; print @aa;')<br />
GO
But, it is dangerous to use this method if this code is in a stored procedure. Anyone could call your procedure and execute their code on your server. You can safeguard against this by doing something similar to this:
declare @tbl varchar(200)<br />
set @tbl = 'myTable';<br />
if len(@tbl) > 20 <br />
begin<br />
print 'Cannot process---invalid table name length'<br />
end<br />
else<br />
begin<br />
EXEC('declare @aa bigint; select @aa = count(*) from ' + @tbl + '; print @aa;')<br />
end<br />
GO
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
Hi EricDV,
Many thanks for your suggestion. You have a very good point here and I'll use it when I finalize my codes.
How should I modify your codes so I can use @aa later on? Thanks.
Kevin
|
|
|
|
|
kevin127 wrote: How should I modify your codes so I can use @aa later on?
You would need to use it within the EXEC(";...") line. The variable only has a scope for as long as the EXEC() statement is executing. You could concatenate your code on the end, using the semi-colon delimiter to end each line. This isn't pretty, but it works. There may be a better way that I'm not aware of.
--SQL Server Books Online--
Changes in database context last only until the end of the EXECUTE statement. For example, after the EXEC in this example, the database context is master:
USE master EXEC ("USE pubs") SELECT * FROM authors
----------
There go my people. I must find out where they are going so I can lead them.
- Alexander Ledru-Rollin
|
|
|
|
|
I have a marketing leads table that I created. The Primay key is a value that is automatically seeded when a new record is created. In my C# app I was showing the new value in the databound field. This was when I had the adapter, connector and dataset from the toolbar. However I remeved these from the app and programmed them in. What do I need to do to show the value when a user clicks the new button.
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
Got a question.
I need to create a cluster of SQL to support more users. What's the best way as I want all computers to have the same IP using NLB. Can I put the database in a SAN box and each server load that database? Can only one SQL open that file or multiple servers can use the same database in a fisical location?
Otherwise can I use Replication and wherever I make that UPDATE will send the changes to the other computers?
Please let me know the best way to do this
Cheers
Al
|
|
|
|
|
It really doesn't work like that. You can cluster to get failover support, using Windows Server's clustering feature and SQL Server's virtual server support. You can't cluster to get better performance.
Even if your SAN would allow you to do that (and I don't think it would - I hope it wouldn't!) SQL Server gets most of its performance from caching data from the database files in RAM. It has no provision for invalidating that cache, so a change made by one server would not necessarily be seen by another server.
Transactional replication is pretty costly since all servers have to commit the transaction, so every transaction becomes a distributed transaction. This pushes costs of writes way up.
The recommended plan is, reportedly, to partition your data - to place some of the data on one server and some on another. This will never provide equal load balancing. It's not easy to do - your client now needs to know which server to look on, it's hard to dynamically change the partitions. Backing up becomes more problematic.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hi,
I have created the following connection string and loaded a dataset. How do I querey the dataset, "SQLDataset" after it has been filled?
DBConnection.Open() 'open database
Dim DBAdapter As New SqlDataAdapter("Select * from tblusers", DBConnection) 'use table adpater to pulldata
DBAdapter.Fill(SQLDataset) 'populate data
Here, I want to put an IF statement that queries the dataset as such:
If my.user.name = (the value in my column UserloginName from the SQLDataset) then
... execute statements.
Thanks,
Rashar
|
|
|
|
|
I don't know exactly what you mean...
More or lees take a look to this code, If I have undestood you this should work...
I see three approachs. Select() on the DataSet, DataView or Foreach
My sample is with a Typed DataSet on the table Categories of NorthWind
this.sqlDataAdapter1.Fill ( this.dst1 );
string filter = "CategoryID = 5";
DataRow[] SelectedRows = this.dst1.Categories.Select ( filter );
if ( SelectedRows.Length > 0 )
{
//Code
}
foreach ( dst.CategoriesRow Category in this.dst1.Categories )
{
if ( Category.CategoryID == 5 )
{
//Code
}
}
System.Data.DataView dv = new DataView ( dst1.Categories, filter, string.Empty, System.Data.DataViewRowState.CurrentRows );
if ( dv.Count > 0 )
{
//Code
}
Ricardo Casquete
|
|
|
|
|
Thanks for the reply.
You have the right idea. How would I implement this in VB.net?
Thanks,
Rashar
|
|
|
|
|
How to get all Oracle Listeners names in my Oracle Server ?
Thanks
|
|
|
|
|
Usually there is only one listener on a server, but you may have many database instances each with their own database service. When you connect to those services each connection initially is to the listener which then hands off the connection to the correct database service. If you list the services of your server, you should see a service name OraListener or something like that. There should also be services for each database instance that runs there. The names of these will correspond to the database instance name.
Chris Meech
I am Canadian. [heard in a local bar]
When I want privacy, I'll close the bathroom door. [Stan Shannon]
GOOD DAY FOR: Moonlighting, as porn star Savanna Samson has launched her own wine label. Her Sogno Uno, an Italian red, received an "outstanding" 90 to 91 score from influential wine critic Robert Parker. "I wanted to do something my parents could be proud of," she said. (Reuters in CNNMoney.com)
|
|
|
|
|
I have a stored procedure that bomb's out with an error right at the start.
The error doesn't make any sense to me so I want to debug, but when I hit debug, Query analyser runs all the way to the error withoutout breaking code-execution once.
Obliviously something is wrong in my code, how do I find it?
Inserting a print command after every single line of code doesn't work either, i think sql doesn't handle the print command in a timely fashion.
Im running Win XP Pro SP2, SQL2000 SP4
-- modified at 10:07 Tuesday 4th April, 2006
I found the problem in my code afterall, but my Query Analyser still wont debug, I now realise that it's been like this since I installed Visual Studio 2005.
Can anyone help?
you can't forget something you never knew...
|
|
|
|
|
Hi
I am trying to connect to a CSV file and import the data from the file to MSSQL database.
I am using OleDbConnection to connect to the file.
My code works fine if I run the web page on my local server. However, if I copy the project to my hosted server and then try to connect to the CSV file, I get error message: "Cannot find table ".
I believe I am unable to connect to the CSV file because of security issues. I have following code:
<br />
ConnCSV = @"\\173.0.0.5\csv\"<br />
string cs = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ConnCSV+";Extended Properties='text;HDR=No;FMT=Delimited'";<br />
<br />
string sqlSelect;<br />
OleDbConnection objOleDBConn;<br />
OleDbDataAdapter objOleDBDa;<br />
objOleDBConn = new OleDbConnection(cs);<br />
objOleDBConn.Open();<br />
sqlSelect = "select * from ["+ File.CSV +"]";<br />
objOleDBDa = new OleDbDataAdapter(sqlSelect,objOleDBConn);<br />
objOleDBDa.Fill(ds);<br />
objOleDBConn.Close();<br />
173.0.0.5 is the IP address of the network drive
So does anyone know how to connect to CSV file from a hosted environment??
regards
-- modified at 10:51 Tuesday 4th April, 2006
|
|
|
|
|
I need to create tables by calling a storage procedure. The name of table must be a parameter.
I am using Visual c++ 6.0 with sql server, can you show me how to execute it from my program also.
I learn my self
|
|
|
|
|
Hi, I have never done a Visual C++ Program so I cannot give you a lift with this part...
but I have used stored procedures to create tables and sp
alter Procedure SPCreateTable
@TableName nvarchar ( 50 )
As
declare @CreateString nvarchar ( 4000 )
Set @CreateString =
'CREATE TABLE ' + @TableName + '
(
CategoryID int IDENTITY (1, 1) NOT NULL ,
CategoryName nvarchar (15),
) '
Exec ( @CreateString )
Go
Exec SPCreateTable 't3'
This is a sample that works, you only have to paste it in the Query Analizer...
Good Luck
Ricardo Casquete
|
|
|
|
|
Could you show me some theory about managing database for multi user program? Thanks so much. 
|
|
|
|
|
As I remember that was a whole semester's worth of database lectures from my uni' days. How much information do you really want?
The shortest summary would be:
* Use transactions
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)
My: Website | Blog
|
|
|
|
|
Hi
I come from MSAccess programming and using access is easy to create a view of data coming from 2 different tables linked with a join.
I'm trying to port my application under VS2003 C# and I don't know how create the same thing with my dataset.
The brute-force mode I found is to create a new table copying the fist one, add a column and fill it with the data retrived from the second one with GetParentRow or GetChildRow methods. Is this the right way or there is a easiest way?
Thank in advance
Best regards
Massimo Nicoletti
|
|
|
|
|
Hi man...
You only have to create the typed dataSet from the View.
I mean, as well you add a table to a dataset, you can add a view.
If the View is well done with the Primary Keys and the Not Null columns you can do updates and Insert aganist the View and you will find the both tables will be updated...
Ricardo Casquete
|
|
|
|
|
Hello Ricardo,
my dataset is a Typed dataset so You think that I have all the info I need?
I can "reach" the joined column of a table using the info in the dataset?
How I can do this?
Thank you for your help
Thank you for your help
Massimo Nicoletti
|
|
|
|
|
OK Man,
If you have a view in Access or SqlServer, you should be able to see it from the Server Explorer of the Visual Studio.
Instead of the Tables Node, From the View Node.
So now, to create a Typed DataSet from the View the only thing you have to do is to Drag and drop it over a dataSet or over a form ( creating also the OdbcDataAdapter ).
Doing so, when the DataAdapter has been created if you have done the View Properly ( I mean with all the PrimaryKeys of the Tables, and all the Columns that cannot be empty ) the DataAdapter willk generate the Update and DeleteCommand.
If you don't have all this information, you won't be able to delete or update.
Now the only thing you have to do is to work with the Data of the DataSet.
I mean
foreach ( System.Data.DataColumn col in this.dst.Tables [ 0 ].Columns )
{
col [ 0 ]....
}
foreach ( System.Data.DataRow row in this.dst.Tables [ 0 ].Rows )
{
row [ 0 ].ToString()....
}
to add a row in the tables of the View, you only have to add a row to the DataSet and call Update to the DataAdapter...
and more or less thats all
Ricardo Casquete
|
|
|
|
|
Ok tested and work fine.
The last question is: if I'm working off-line, I mean with the dataset can be non syncronized with the source (access database) because I modify a record and I have not yet updated the source, before creating dataset I must update the database with my dataset or I can "start" with the data stored in the dataset?
Thank you
Massimo Nicoletti
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
