Help with counting Query

I am trying to find a query to count rows with multiple conditions.
I have files which are suffixed with -R and then a number from 1 to 3 and
then two digits 00-14.
the field is char
i.e.:
-R101
-R102
-R114
-R201
-R302
I need to determine how many 101, how many 102, etc through 114 and then the
same for
the 201-214 series and then
the 301-314 series.
I will be sending the query from Visual Basic using ADODC so I am not sure
how the data will be returned. If it was written to a temp table that would
be great.
Thanks,
Bob Hiller
Lifts for the Disabled LLCselect right(columnName, 3), count(columnName)
from tableName
group by right(columnName, 3)
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:ecFHqw3TGHA.5464@.TK2MSFTNGP10.phx.gbl...
>I am trying to find a query to count rows with multiple conditions.
> I have files which are suffixed with -R and then a number from 1 to 3 and
> then two digits 00-14.
> the field is char
> i.e.:
> -R101
> -R102
> -R114
> -R201
> -R302
> I need to determine how many 101, how many 102, etc through 114 and then
> the same for
> the 201-214 series and then
> the 301-314 series.
> I will be sending the query from Visual Basic using ADODC so I am not sure
> how the data will be returned. If it was written to a temp table that
> would be great.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
>
>|||Thank you,
That worked great but I did not ask the full question. Maybe you can help
again.
if these strings are in a column
12345678-R101
12345678-R201
12345678-R301
98564512-R112
18752381-R101
18752381-R201
18752381-R110
18752381-R111
18752381-R211
If there is a -R2 there will always be a -R1. Likewise if there is a -R3
there will always be a -R2.
In the above example I need to return
12345678-R301
98564512-R112
18752381-R201
18752381-R110
18752381-R211
I hope I have explained this well enough.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uWek153TGHA.4540@.TK2MSFTNGP10.phx.gbl...
> select right(columnName, 3), count(columnName)
> from tableName
> group by right(columnName, 3)
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:ecFHqw3TGHA.5464@.TK2MSFTNGP10.phx.gbl...
>|||select left(columnName, charindex('-R',columnName)+1),
MAX(substring(columnName,charindex('-R',columnName)+2,10))
from tableName
group by left(columnName, charindex('-R',columnName)+1)
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:%23IRMFQ4TGHA.1204@.TK2MSFTNGP12.phx.gbl...
> Thank you,
> That worked great but I did not ask the full question. Maybe you can help
> again.
> if these strings are in a column
> 12345678-R101
> 12345678-R201
> 12345678-R301
> 98564512-R112
> 18752381-R101
> 18752381-R201
> 18752381-R110
> 18752381-R111
> 18752381-R211
> If there is a -R2 there will always be a -R1. Likewise if there is a -R3
> there will always be a -R2.
> In the above example I need to return
> 12345678-R301
> 98564512-R112
> 18752381-R201
> 18752381-R110
> 18752381-R211
> I hope I have explained this well enough.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
>
> "Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
> news:uWek153TGHA.4540@.TK2MSFTNGP10.phx.gbl...
>|||OOPS... You need to convert the count values to int...
select left(columnName, charindex('-R',columnName)+1),
MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
from tableName
group by left(columnName, charindex('-R',columnName)+1)
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(substring(columnName,charindex('-R',columnName)+2,10))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:%23IRMFQ4TGHA.1204@.TK2MSFTNGP12.phx.gbl...
>|||I will do some more checking but thus far your very appreciated suggestion
is producing some very strange results.
For one thing it is returning 2 expressions. I would expect only one.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
> OOPS... You need to convert the count values to int...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
>|||Ok, I can live with the 2 returned expressions, they will work fine.
Here is what is returned when I run the sample:
12345678-R301
98564512-R112
18752381-R211
I am missing:
18752381-R201
18752381-R110
Think of the first number after the -R as a counter for the last 2 numbers
that represent a group.
When these are in the table
18752381-R101 01 is the group and 1 is the counter
18752381-R201 01 is the group and 2 is the counter
we want to return the largest counter for group 01 for the number to the
left of -R
return (18752381-R201)
18752381-R110 10 is the group and 1 is the counter
return (18752381-R110) it is the only group 10 for the number to the left
of -R
18752381-R111 11 is the group and 1 is the counter
18752381-R211 11 is the group and 2 is the counter
return (18752381-R211)
Thank in advance,
Bob Hiller
Lifts for the Disabled LLC
"helpful sql" <nospam@.stopspam.com> wrote in message
news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
> OOPS... You need to convert the count values to int...
> select left(columnName, charindex('-R',columnName)+1),
> MAX(convert(int,substring(columnName,cha
rindex('-R',columnName)+2,10)))
> from tableName
> group by left(columnName, charindex('-R',columnName)+1)
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OLQc8X4TGHA.424@.TK2MSFTNGP12.phx.gbl...
>|||select max(a)
from(select '12345678-R101'
union all select '12345678-R201'
union all select '12345678-R301'
union all select '98564512-R112'
union all select '18752381-R101'
union all select '18752381-R201'
union all select '18752381-R110'
union all select '18752381-R111'
union all select '18752381-R211')x(a)
group by left(a,8),right(a,2)
-oj
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:Ow6lDS5TGHA.4600@.TK2MSFTNGP11.phx.gbl...
> Ok, I can live with the 2 returned expressions, they will work fine.
> Here is what is returned when I run the sample:
> 12345678-R301
> 98564512-R112
> 18752381-R211
> I am missing:
> 18752381-R201
> 18752381-R110
> Think of the first number after the -R as a counter for the last 2 numbers
> that represent a group.
> When these are in the table
> 18752381-R101 01 is the group and 1 is the counter
> 18752381-R201 01 is the group and 2 is the counter
> we want to return the largest counter for group 01 for the number to the
> left of -R
> return (18752381-R201)
> 18752381-R110 10 is the group and 1 is the counter
> return (18752381-R110) it is the only group 10 for the number to the
> left of -R
> 18752381-R111 11 is the group and 1 is the counter
> 18752381-R211 11 is the group and 2 is the counter
> return (18752381-R211)
> Thank in advance,
> Bob Hiller
> Lifts for the Disabled LLC
>
> "helpful sql" <nospam@.stopspam.com> wrote in message
> news:OsGYae4TGHA.4792@.TK2MSFTNGP14.phx.gbl...
>|||oj,
Thanks for the suggestion but the values where just given as samples. There
are thousands of rows that I have to search through. I don't think this
approach will work.
Thanks,
Bob Hiller
Lifts for the Disabled LLC
"oj" <nospam_ojngo@.home.com> wrote in message
news:e%23xZMS6TGHA.5108@.TK2MSFTNGP11.phx.gbl...
> select max(a)
> from(select '12345678-R101'
> union all select '12345678-R201'
> union all select '12345678-R301'
> union all select '98564512-R112'
> union all select '18752381-R101'
> union all select '18752381-R201'
> union all select '18752381-R110'
> union all select '18752381-R111'
> union all select '18752381-R211')x(a)
> group by left(a,8),right(a,2)
>
> --
> -oj
>
> "Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
> news:Ow6lDS5TGHA.4600@.TK2MSFTNGP11.phx.gbl...
>|||Bob,
You need to adapt the technique to your data.
e.g.
select max(your_col)
from tb
group by left(your_col,8),right(your_col,2)
If it does not give you the desired result, you'd want to post ddl + sample
data + expected result here so we can help.
-oj
"Bob and Sharon Hiller" <aoklans@.tir.com> wrote in message
news:uRMPBI8TGHA.5836@.TK2MSFTNGP10.phx.gbl...
> oj,
> Thanks for the suggestion but the values where just given as samples.
> There are thousands of rows that I have to search through. I don't think
> this approach will work.
> Thanks,
> Bob Hiller
> Lifts for the Disabled LLC
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e%23xZMS6TGHA.5108@.TK2MSFTNGP11.phx.gbl...
>

HELP with connection string PLEASEEEE!

hi, I'm kind of really new to this. Trying to learn asp.net 2.0. Have been designing a website, and, so far so good, I can deploy the files, but I can't get the synthax right for the connection string. in my web.config file the connection string works on the local computer, but I can't figure out how to change the data source part:
this is what I have in my web convig file
<add name="Database2ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Documents and Settings\Alexi\My Documents\Delivery\web\App_Data\Database2.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" /
the the wrox book says I have to have the|datadirectory|
does it mean I have to write the full http address on the server?
and I can't figure out what to substitute in for the ".\sqlexpress" part...

my host has ms sql server 2000 and my sql. I asked for their support and the gave me a weird looking connection string and claimed that it worked...

<%@. LANGUAGE = JScript %>
<% var oConn;
oConn = Server.CreateObject("ADODB.Connection");
oConn.Mode = 3
oConn.Open("Provider=SQLOLEDB;Server=203.89.181.78;Database=alexeyka_yah_1951com_;UID=support;PWD=test123;");
%>
'

How can I, and where, copy my existing databse, and change the connection string?

Would really appreciate any help, perhaps buy a new book from the programmers in gratitude.

there is nothing wrong with connection string - the problem is that you are using database file with SQL express and you host SQL 2000 - so they will not be able to attach database file the way SQL Express does...

few days ago i posted why not to use database file with project that will go to shared hosting here:
http://forums.asp.net/thread/1375347.aspx - in the future login to SQL (express is cool) and create database then backup it and restore on your web hosting's SQL server

More info about connection strings you will find here:
http://www.connectionstrings.com/

If the database you have is empty no real data, then copy databse structure to new server change database2ConnectionString and you are good to go

your connections strin g will look like this

<add name="Database2ConnectionString" connectionString="Data Source=203.89.181.78;Initial Catalog=alexeyka_yah_1951com_;User ID=support;Password=test123;"
providerName="System.Data.SqlClient" />

BTW if that is real user pwd - CHANGE IT

|||

Hey, thank you so much for you help. Sorry for the late reply - didn't

The database does connect now, so all I have to do now is to figure out how to actually copy my database file to server.

Alexi;)

|||

if they offer SQL 2005 you should not have any problems. If not and you have to use SQL 2000 read this:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=71448&SiteID=1

|||

Hey, thanks again.

I was able to create an identical blank database table on the server in their asp.net enterprise manager. Yes, the do only have 2000 version.

Then, in VWD express I changed datasource control to point to the connection string that connects to the db on the server - it worked.

I'm struggling to contect to the database remotely, will hopefully figure something out.

Cheers again,

Alexi

|||

" I'm struggling to contect to the database remotely " ? - IF the hosting company accepts remote connections you can use free tool like SQL Server Managment Studio Express Edition to connect and work on you database. Keep in mind that many hoster block remote connections so you can only make changes to database design via online Enterprise manager or diffrent tool. If that is the case then simply change connection string in web config when you work on this application to you local machine, and before you do upload to server change it back to real SQL (the 2000 one)

|||

Hi Tom,

Thanks for you reply. I did download the sql server management studio, and asked whether my hosts allowed the remote connection - they don't. so I guess I have to do everything from scratch on the enterprise manager online. I've figured out normal tables, my main concern now will be to generate tables to store username/password info.

Cheers once again,

Alex

|||

one solution is to script everything

or the easy way will be :) :

http://forums.commercestarterkit.org/files/folders/sql_2000_upsize_scripts/entry471.aspx

|||

you can use this tool to add/edit/ delete users, create roles

http://peterkellner.net/2006/07/17/atlasjunectpsource/

make sure you secure it on the server, so u are the only one who can access it

|||

Hi Tom,

Thanks again for you input, have been real busy populating my datatables, so haven't had time to work on logins/users. If you like, you can check out the results of your efforts atwww.takeawaydelivery.co.nz

Will now be working my way into creating loging pages. By the way, the link that you gave me for some reason, it says there was a fata error and it couldn't be viewed? Anyway, thanks for all your help, and I'll let you know if I get around to creating users/etc

|||

the one with script or the one with tool to manage users ? i chcecked both links and are working just fine for me.

|||

Hi,

Seems to be working fine now, that was the tool to manage script. Downloaded it, and ran it. Works fine on my computer, though I can't understand though where are the usernames/passwords are stored. Also, with the tables, do I just run the whole script in the enterprise manager? and how do I link the two? Thanks again,

Alexi

|||

Hi,

an update here - the admin tool works fine on the local computer. I also reconfigured the weg.config file to point towards a remote database for users/passwords:

<membershipdefaultProvider="CustomizedProvider">

<providers>

<addname="CustomizedProvider"

type="System.Web.Security.SqlMembershipProvider"

connectionStringName="MyDB"

minRequiredPasswordLength="5"

minRequiredNonalphanumericCharacters="0" />

</providers>

</membership>

(and added a corresponding connection string).

For some reason though, when I run the table building script it doesn't show that anything is happening? like tables do not appear? any ideas-

thanks

Alexi

|||

Server Error in '/' Application.

---

Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'.

Source Error:

Line 50: private void FindFirstUserName()Line 51: {Line 52: MembershipUserCollection muc = Membership.GetAllUsers();Line 53: foreach (MembershipUser mu in muc)Line 54: {

Source File:c:\Inetpub\vhosts\takeawaydelivery.co.nz\httpdocs\Default.aspx.cs Line:52

Stack Trace:

this is the erro when i go to the tool|||the database script (link i sent u) i used it few times and never had any problems with it - make sure that u can see all tables needed and stored proc.

help with activating rs

Hello,
We installed RS and got the message that we needed to activate the
reporserver. We ran the following:
c:\program files\Microsoft SQL Server\80\Tools\Binn\rsactivate -c
"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\RSReportServer.config"
Now when the site comes up, but it is missing the Nav Bar with
contents/properties so we cannot upload any files. Is there something else
we need to run?Mayby you're loged on with a user that hasn't got any rights. Use
administrator login to give the initial access-rigths.
"Jake Smythe" wrote:
> Hello,
> We installed RS and got the message that we needed to activate the
> reporserver. We ran the following:
> c:\program files\Microsoft SQL Server\80\Tools\Binn\rsactivate -c
> "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
> Services\ReportServer\RSReportServer.config"
> Now when the site comes up, but it is missing the Nav Bar with
> contents/properties so we cannot upload any files. Is there something else
> we need to run?
>
>|||Antoon,
We are logged into a user that is part of the administrators group. Any
other ideas?
Jake
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:CBAF4E9E-AD80-49AE-9E6C-209E977BC4EA@.microsoft.com...
> Mayby you're loged on with a user that hasn't got any rights. Use
> administrator login to give the initial access-rigths.
> "Jake Smythe" wrote:
>> Hello,
>> We installed RS and got the message that we needed to activate the
>> reporserver. We ran the following:
>> c:\program files\Microsoft SQL Server\80\Tools\Binn\rsactivate -c
>> "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
>> Services\ReportServer\RSReportServer.config"
>> Now when the site comes up, but it is missing the Nav Bar with
>> contents/properties so we cannot upload any files. Is there something
>> else
>> we need to run?
>>

Help with a loop.

I am writing a script that will go through all the database files on a
server, collect the file sizes and return the values in a single
table. This script works for the most part, but there is an instance
when the script fails to collect the information properly. When there
are two or more data files the script only reports the first one twice.
Can someone take a look at this loop and tell me where the error is?
Thanks
-Matt-
/ ****************************************
**********
Script to calculate information about the Data Files
****************************************
**********/
DECLARE @.dbname varchar(50)
DECLARE @.string varchar(250)
SET @.string = ''
Declare @.rows int
CREATE TABLE #dbcc_showfilestats (
fileid tinyint,
FileGroup1 tinyint,
TotalExtents1 decimal (28, 2),
UsedExtents1 decimal (28, 2),
Name varchar(50),
FileName sysname )
CREATE TABLE #dbstats (
DB_Name varchar(50),
DB_Total_Size_in_MB decimal (28, 2),
DB_Used_Size_in_MB decimal (28, 2),
DB_Free_Size_in_MB decimal (28, 2),
DB_Percent_Used decimal (28, 2))
DECLARE dbnames_cursor CURSOR FOR SELECT name FROM master..sysdatabases
-- Collects all the DB name
OPEN dbnames_cursor
FETCH NEXT FROM dbnames_cursor INTO @.dbname
WHILE (@.@.fetch_status = 0)
BEGIN
SET @.string = 'use ' + @.dbname + ' DBCC SHOWFILESTATS'
INSERT #dbcc_showfilestats
EXEC (@.string)
SELECT * FROM #dbcc_showfilestats -- Debug
SELECT @.rows = count(*) from #dbcc_showfilestats
While @.rows > 0
BEGIN
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB, DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB = sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB = sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used = sum(UsedExtents1/TotalExtents1)*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
SET @.rows = @.rows - 1
END
TRUNCATE TABLE #dbcc_showfilestats
FETCH NEXT FROM dbnames_cursor INTO @.dbname
END
CLOSE dbnames_cursor
DEALLOCATE dbnames_cursor
SELECT * FROM #dbstats --Debug
DROP TABLE #dbstats --Debug
DROP TABLE #dbcc_showfilestats --DebugYou are selecting the same rows from #dbcc_showfilestats
every time through your 'while' loop.
Add
id int identity(1,1)
to your #dbcc_showfilestats table and change
FROM #dbcc_showfilestats
to
FROM #dbcc_showfilestats where id=@.rows|||Hi Matthew,
In addition to correctly adding a unique integer to distinguish rows in
your temp table as Mark has suggested, you may want to look at using
another temp table to loop through rather than using a cursor.
Cursors are very memory heavy in comparison to a looped through temp
table.
So instead your loop (in pseudo) would look more like:
-- SET UP 'CURSOR' TABLE
SELECT name INTO #databases FROM master..sysdatabases
-- DEFINE LOOPING PARAMETER
DECLARE @.unqName nvarchar(4000)
-- SELECT LOOPING PARAMETER
SELECT @.unqName = name FROM #databases
-- ENTER WHILE LOOP
WHILE LEN(@.unqName) > 0
BEGIN
-- PERFORM LOOP CODE
--DELETE ROW FROM LOOPING TABLE #databases
DELETE FROM #databases WHERE name = @.unqName
SELECT @.unqName = '' -- CLEAR VARIABLE
SELECT @.unqName = name FROM #databases
END
This will make a big difference in large looping scenarios - just try
it out.
Andrew La Grange
Business Artists
http://www.businessartists.co.za|||By doing the SUM(...), which is an aggregate function, you are only
saying you want 1 row.
What do you really want, the size and usage of each file? Or the size
of the entire database?
-Jeff|||If you want the entire database, then there is no need for a loop use
the following:
SELECT * FROM #dbcc_showfilestats -- Debug
INSERT #dbstats (DB_Name, DB_Total_Size_in_MB,
DB_Used_Size_in_MB,
DB_Free_Size_in_MB, DB_Percent_Used)
SELECT @.dbname,
DB_Total_Size_in_MB =
sum(TotalExtents1)*65536.0/1048576.0,
DB_Used_Size_in_MB =
sum(UsedExtents1)*65536.0/1048576.0,
DB_Free_Size_in_MB =
sum(TotalExtents1-UsedExtents1)*65536.0/1048576.0,
DB_Percent_Used =
(sum(UsedExtents1)/sum(TotalExtents1))*100
FROM #dbcc_showfilestats
SELECT * FROM #dbstats
TRUNCATE TABLE #dbcc_showfilestats

Help with a Log File Query.

Two questions
First, I need to insert the dbid into the table so I can cross
reference the log files with other data. That being said, I can't
seem to get the update field to work properly. As always, it is most
likely something pretty obvious that I am missing.
The second question. The "dbcc sqlperf(logspace) with no_infomsgs"
command returns the entire spaced used by all the log files attached to
that database. is there a command that will break it into the component
parts?

code:

/ ****************************************
**********
Script to calculate information about the Log Files
 ****************************************
**********/
CREATE TABLE #dbcc_sqlperf (
DB_Name varchar(50),
Log_Size decimal (28, 5),
Log_Used_Percent decimal (28, 5),
Status tinyint )
CREATE TABLE #logstats (
DBID tinyint,
DB_Name varchar(50),
Log_Total_Size_in_MB decimal (28, 2),
Log_Used_Size_in_MB decimal (28, 2),
Log_Free_Size_in_MB decimal (28, 2),
Log_Percent_Used decimal (28, 2))
INSERT #dbcc_sqlperf EXEC ('dbcc sqlperf(logspace) with no_infomsgs')
SELECT * FROM #dbcc_sqlperf --Debug
INSERT #logstats (DBID, DB_Name, Log_Total_Size_in_MB,
Log_Used_Size_in_MB, Log_Free_Size_in_MB, Log_Percent_Used)
SELECT DB_Name = DB_Name,
Log_Total_Size_in_MB = log_size,
Log_Used_Size_in_MB = sum
(log_size*(log_used_percent/100)),
Log_Free_Size_in_MB = sum (log_size
-(log_size*(log_used_percent/100))),
Log_Percent_Used = log_used_percent
FROM #dbcc_sqlperf
GROUP BY Log_Name, Log_Size, Log_Used_Percent, Status
update #logstats (DBID)
Select dbid = DBID
from master..sysdatabases where name = #logstats.DB_Name
SELECT * FROM #logstats --Debug
DROP TABLE #logstats
DROP TABLE #dbcc_sqlperf

Mattew
update #logstats set DBID=(select DBID
from master..sysdatabases where name = #logstats.DB_Name)
where exists (select * from master..sysdatabases where name =
#logstats.DB_Name)
"Matthew" <
MKruer@.gmail.com>
wrote in message
news:1143043015.896576.256210@.v46g2000cwv.googlegroups.com...
>
Two questions
>
>
First, I need to insert the dbid into the table so I can cross
>
reference the log files with other data. That being said, I can't
>
seem to get the update field to work properly. As always, it is most
>
likely something pretty obvious that I am missing.
>
>
The second question. The "dbcc sqlperf(logspace) with no_infomsgs"
>
command returns the entire spaced used by all the log files attached to
>
that database. is there a command that will break it into the component
>
parts?
>
>

code:

>
 / ****************************************
**********
>
 Script to calculate information about the Log Files
>
 ****************************************
**********/
>
>
 CREATE TABLE #dbcc_sqlperf (
>
 DB_Name varchar(50),
>
 Log_Size decimal (28, 5),
>
 Log_Used_Percent decimal (28, 5),
>
 Status tinyint )
>
>
 CREATE TABLE #logstats (
>
 DBID tinyint,
>
 DB_Name varchar(50),
>
 Log_Total_Size_in_MB decimal (28, 2),
>
 Log_Used_Size_in_MB decimal (28, 2),
>
 Log_Free_Size_in_MB decimal (28, 2),
>
 Log_Percent_Used decimal (28, 2))
>
>
 INSERT #dbcc_sqlperf EXEC ('dbcc sqlperf(logspace) with no_infomsgs')
>
>
 SELECT * FROM #dbcc_sqlperf --Debug
>
>
 INSERT #logstats (DBID, DB_Name, Log_Total_Size_in_MB,
>
 Log_Used_Size_in_MB, Log_Free_Size_in_MB, Log_Percent_Used)
>
 SELECT DB_Name = DB_Name,
>
 Log_Total_Size_in_MB = log_size,
>
 Log_Used_Size_in_MB = sum
>
 (log_size*(log_used_percent/100)),
>
 Log_Free_Size_in_MB = sum (log_size
>
 -(log_size*(log_used_percent/100))),
>
 Log_Percent_Used = log_used_percent
>
 FROM #dbcc_sqlperf
>
 GROUP BY Log_Name, Log_Size, Log_Used_Percent, Status
>
>
 update #logstats (DBID)
>
 Select dbid = DBID
>
 from master..sysdatabases where name = #logstats.DB_Name
>
>
 SELECT * FROM #logstats --Debug
>
>
 DROP TABLE #logstats
>
 DROP TABLE #dbcc_sqlperf
>

>

HELP using SQL Server 2005

I am using SQLServer2005 Express Edition in Visual Studio 2005. I uploaded the database to the server with the rest of the web files. When I try to insert data into a table, I receive the "error: 26 - Error Locating Server/Instance Specified" error message.

It is my belief that it is the connection string that is causing the problem. Currently the connection string looks like this:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MYDATABASE.mdf;Integrated Security=True;User Instance=True

Is there a way using VS2005 to create a username and pass for the database? Then how do I set up a typical connection string that will work on a web server?

Desired connection string format would be something like:

Data Source=??serverIP??;UID=MYusername;PASSWORD=MYpassword;SERVER=?serverIP/NAME?

The question marks are in there because I am not 100% sure what would go in those properties. Any help is appreciated.

Thanks,
KJAK

Are you sure that the sqlserver is installed in the server ? When you use .mdf files is necessary that the server have the sql server installed.

A connection string that will work on a web server is something like this:

connectionString="Data Source=SERVERIP;Initial Catalog=DATABASENAE;Persist Security Info=True;User ID=USER;Password=PASSWORD"providerName="System.Data.SqlClient"

|||

SQLServer is installed on the server. The current connection string is provided from my development machine in the web.config file. I will try to adjust it to be similar to what you have posted.

I don't have full access to the server so I can't make any direct adjustments to SQLServer if that is needed. How can I create a username and password for the database using Visual Studio 2005?

|||

SQLServer is installed on the server

which version of sql server is installed? express? or sql 2005?

But whatever,based on my understanding, in neither case you can create sql user name and passwork through visual studio. The sql database is managed by database management tool thus you can only create/modify user name and psw through management studio.

I don't have full access to the server so I can't make any direct adjustments to SQLServer if that is needed.

and based on my understanding, i think you must have admin previlage if you want to create new sql user accounts.

Hope my suggestion helps

Help storing and retrieving .rtf blob as longvarbinary

Hello,
can anyone point me in the right direction? I have .rtf files stored in a
table as image or sql_longvarbinary data. I would like to be able to pull
out the data and assemble an .rtf file using VBscript or VBA
I'm not sure where to begin when working with image data types.
Thanks
Buddy G.You can take a look at these:
HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
http://support.microsoft.com/d_efau...b;en-us;1949_75
HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream Object
http://support.microsoft.com/d_efau...;EN-US;q258_038
"Jeff Boyce" <nonse
-oj
"Buddy G" <Buddy at gcsbend dot com> wrote in message
news:ub5xmnyZFHA.3328@.TK2MSFTNGP09.phx.gbl...
> Hello,
> can anyone point me in the right direction? I have .rtf files stored in a
> table as image or sql_longvarbinary data. I would like to be able to pull
> out the data and assemble an .rtf file using VBscript or VBA
> I'm not sure where to begin when working with image data types.
> Thanks
> Buddy G.
>|||Thanks,
let me take a look at those.
Buddy
"oj" <nospam_ojngo@.home.com> wrote in message
news:%23fRZc00ZFHA.3784@.TK2MSFTNGP12.phx.gbl...
> You can take a look at these:
> HOWTO: Read and Write BLOBs Using GetChunk and AppendChunk
> http://support.microsoft.com/d_efau...b;en-us;1949_75
>
> HOWTO: Access and Modify SQL Server BLOB Data by Using the ADO Stream
Object
> http://support.microsoft.com/d_efau...;EN-US;q258_038
>
> "Jeff Boyce" <nonse
> --
> -oj
>
> "Buddy G" <Buddy at gcsbend dot com> wrote in message
> news:ub5xmnyZFHA.3328@.TK2MSFTNGP09.phx.gbl...
a
pull
>

Help required in import Data into sql 2005 from excel 4.0

Hi
I have to import data from a number of excel files to corresponding tables in SQL 2005. The excel files are created using excel 4.0. I have created an excel connection manager and provided it with the path of the excel sheet.Next i have added an excel source from the toolbox to the dataflow. I have set the connection manger, data access mode, and the name of the excel sheet (the wizard detects the sheet correctly) in the dialog window i get when i double click the excel source. Every thing goes fine till here. Now when i select the 'columns' in this dialog window or the preview button, i get this error

TITLE: Microsoft Visual Studio

Error at Data Flow Task [Excel Source [1]]: An OLE DB error has occurred. Error code: 0x80004005.
Error at Data Flow Task [Excel Source [1]]: Opening a rowset for "test4$" failed. Check that the object exists in the database.

ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)


Any ideas about why is this happening?
UmerI have the exact error. Please someone help.

Help regarding storing and retrieval of files in sql server

Hi,
Thanks in advance.

I need help(Tutorials or online links) regarding storing and retrieval of files in Sql server (BLOB) using ASP.net and C#.
Secondly,Is it possible to search file in BLOB using SQL server Full text search service.TryKB 309158 -- How To Read and Write BLOB Data by Using ADO.NET with Visual C# .NET.

And yes, SQL Server 2000 full text search offers BLOB filtering.

Terri