Restore Database: February 2012

Sunday, February 26, 2012

help to define a search criteria with FTS

Hi!
I'm using FTS in MSSQL2000.
1. i have a string "bcd" and i want the results : "abcd" or "1bcd" but
not "bcda" or "aabcd" (always from the second letter).
2. the search column is a long string. the begining of the string is
more important that the end. i want the results found in the begining
to get higher RANK so i can present them first.
Please help.
You can't do prefix based searches in SQL FTS. You might be able to store
your content in reverse and then do wildcard based searches, i.e.
select * from tablename where contains(ColumnName,'dcb*') which will return
hits to the reversed content of abcd, or 1bcd, but unfortunately also aabcd
reversed.
You may have to use like for this type of query.
Another option would be to use a thesaurus based search if you know in
advance all search tokens which you need to map to bdc, and expand then to
search on bcd,
so bcd will expand to a search on bcd and abcd, and 1bcd.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"rom_ask_u" <nimrod4u@.gmail.com> wrote in message
news:1123138487.817248.51670@.g47g2000cwa.googlegro ups.com...
> Hi!
> I'm using FTS in MSSQL2000.
> 1. i have a string "bcd" and i want the results : "abcd" or "1bcd" but
> not "bcda" or "aabcd" (always from the second letter).
> 2. the search column is a long string. the begining of the string is
> more important that the end. i want the results found in the begining
> to get higher RANK so i can present them first.
> Please help.
>

Help to Create job

i want to give to my user permission to create job , add step to job etc.
i can't do this when my user do not use sa user but i don't want to give him the sa password so how can i do this
eytanb@.mekorot.co.ilBoth SPs execute permissions are default to the public role and ensure that user does have permission on MSDB database and required privilege on underlying user database.

Help to Configure SQL 2005 to listen on a specific Port

I am trying to configure my port for my SQL 2005 named instance. When
I look under the TCP/IP properties dialog box on the IP Address tab
in the see several IP addresses in format IP1,IP2,IP3 and finally
IPALL. Which one am I suppose to configure? When I look at the IP
addresses I don't see the virtual IP address for my SQL Server
Instance. Can anyone help me on this.
I looked at BOL and it says "Right-click each address, and then click
Properties to identify the IP address that you wish to configure." But
right cliking on the address does not give me the properties option.
The server that I am working with is in a Cluster.
ThanksThe multiple IP addresses indicate you have multiple network connections.
You can set a different port for each connection OR you can set the same
port on all connections. I would use the 'ALL' option on a cluster.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>I am trying to configure my port for my SQL 2005 named instance. When
> I look under the TCP/IP properties dialog box on the IP Address tab
> in the see several IP addresses in format IP1,IP2,IP3 and finally
> IPALL. Which one am I suppose to configure? When I look at the IP
> addresses I don't see the virtual IP address for my SQL Server
> Instance. Can anyone help me on this.
> I looked at BOL and it says "Right-click each address, and then click
> Properties to identify the IP address that you wish to configure." But
> right cliking on the address does not give me the properties option.
>
> The server that I am working with is in a Cluster.
> Thanks
>|||On Oct 24, 9:05 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> The multiple IP addresses indicate you have multiple network connections.
> You can set a different port for each connection OR you can set the same
> port on all connections. I would use the 'ALL' option on a cluster.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "shub" <shubt...@.gmail.com> wrote in message
> news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>
> >I am trying to configure my port for my SQL 2005 named instance. When
> > I look under the TCP/IP properties dialog box on the IP Address tab
> > in the see several IP addresses in format IP1,IP2,IP3 and finally
> > IPALL. Which one am I suppose to configure? When I look at the IP
> > addresses I don't see the virtual IP address for my SQL Server
> > Instance. Can anyone help me on this.
> > I looked at BOL and it says "Right-click each address, and then click
> > Properties to identify the IP address that you wish to configure." But
> > right cliking on the address does not give me the properties option.
> > The server that I am working with is in a Cluster.
> > Thanks- Hide quoted text -
> - Show quoted text -
Thanks for your help. As far as deciding the port number itself do you
follow any guidelines or is there any guidelines by Microsoft?|||For whatever reason, SQL2005 Configuration Manager does NOT list the virtual
server IP address used by the SQL instance. I have no idea why.
But if ListenAll is enabled, you can use Configuration Manager to set TCP
Port under IP All on the Ip Address tab to a port of your choice, and upon
restart the instance the port will be used. Also, you only need to do this on
one of the nodes, and the cluster service will replicate the setting to the
other nodes.
Linchi
"shub" wrote:
> I am trying to configure my port for my SQL 2005 named instance. When
> I look under the TCP/IP properties dialog box on the IP Address tab
> in the see several IP addresses in format IP1,IP2,IP3 and finally
> IPALL. Which one am I suppose to configure? When I look at the IP
> addresses I don't see the virtual IP address for my SQL Server
> Instance. Can anyone help me on this.
> I looked at BOL and it says "Right-click each address, and then click
> Properties to identify the IP address that you wish to configure." But
> right cliking on the address does not give me the properties option.
>
> The server that I am working with is in a Cluster.
> Thanks
>|||I either use the autoconfig port and lock it in or I pick something that
isn't in use.
I don't recall any guidelines for this from Microsoft.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"shub" <shubtech@.gmail.com> wrote in message
news:1193279519.381435.155240@.i13g2000prf.googlegroups.com...
> On Oct 24, 9:05 pm, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
>> The multiple IP addresses indicate you have multiple network connections.
>> You can set a different port for each connection OR you can set the same
>> port on all connections. I would use the 'ALL' option on a cluster.
>> --
>> Geoff N. Hiten
>> Senior SQL Infrastructure Consultant
>> Microsoft SQL Server MVP
>> "shub" <shubt...@.gmail.com> wrote in message
>> news:1193252815.417451.311320@.y27g2000pre.googlegroups.com...
>>
>> >I am trying to configure my port for my SQL 2005 named instance. When
>> > I look under the TCP/IP properties dialog box on the IP Address tab
>> > in the see several IP addresses in format IP1,IP2,IP3 and finally
>> > IPALL. Which one am I suppose to configure? When I look at the IP
>> > addresses I don't see the virtual IP address for my SQL Server
>> > Instance. Can anyone help me on this.
>> > I looked at BOL and it says "Right-click each address, and then click
>> > Properties to identify the IP address that you wish to configure." But
>> > right cliking on the address does not give me the properties option.
>> > The server that I am working with is in a Cluster.
>> > Thanks- Hide quoted text -
>> - Show quoted text -
> Thanks for your help. As far as deciding the port number itself do you
> follow any guidelines or is there any guidelines by Microsoft?
>

Help to a query

In acces i use this:

[varebetegnelse] & " Med ordennummer:--" & [ordenummer] AS tekst

Where [varebetegnelse] and [ordennummer] is column in my query

How can i do this in a view in SQL ?

Second
also in acces i use a criteria based on a form like this

WHERE (((kategori.katId)=[Forms]![samlekursus]![kategorinr]))

How can i do this in a view in SQL

I work from access cnoocted to SQL sever

Regards

alvin

You will either have to do the filtering on the view (using the view as a mapped table and building a query in access on top of that)

Select SomeColumns
FROM
Viewname
WHERE (((kategori.katId)=[Forms]![samlekursus]![kategorinr]))

Or use a parametrized query, passing the parameter to the procedure.

EXEC SomeProcedure @.SomeVariablewithin = 'YourValue'

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

help textcopy

i like textcopy in sql server 2000, but i don't find it in sql server 2005.
is there has replace_tool in sql server 2005?
thanks
jason>i like textcopy in sql server 2000, but i don't find it in sql server 2005.
>is there has replace_tool in sql server 2005?
Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
SQL 2005, you can accomplish the same result in Transact-SQL using
OPENROWSET...BULK. The proc example below imports file contents into a temp
table and then updates the specified main table row with the blob data.
CREATE PROC dbo.UpdateMyTableImageDataFromFile
@.MyPK int,
@.FileName varchar(255)
AS
DECLARE @.SqlStatement nvarchar(MAX)
CREATE TABLE #BlobData(BlobData varbinary(MAX))
--insert blob into temp table
SET @.SqlStatement = N'
INSERT INTO #BlobData
SELECT BlobData.*
FROM OPENROWSET
(BULK ''' + @.FileName + ''',
SINGLE_BLOB) BlobData'
EXEC sp_executesql @.SqlStatement
--update main table with blob data
UPDATE dbo.MyTable
SET MyBlob = (SELECT BlobData FROM #BlobData)
WHERE MyTable.MyPK = @.MyPK
DROP TABLE #BlobData
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"udb" <datacen@.vip.sina.com> wrote in message
news:udiThYm2GHA.1292@.TK2MSFTNGP03.phx.gbl...
>i like textcopy in sql server 2000, but i don't find it in sql server 2005.
>is there has replace_tool in sql server 2005?
> thanks
>
> jason
>|||thanks!
but if i need export image/text data, how do?
SSIS or command?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> Ð´ÈëÏûÏ¢ÐÂÎÅ:uojyazm2GHA.4648@.TK2MSFTNGP04.phx.gbl...
> >i like textcopy in sql server 2000, but i don't find it in sql server
> >2005. is there has replace_tool in sql server 2005?
> Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
> SQL 2005, you can accomplish the same result in Transact-SQL using
> OPENROWSET...BULK. The proc example below imports file contents into a
> temp table and then updates the specified main table row with the blob
> data.
>
> CREATE PROC dbo.UpdateMyTableImageDataFromFile
> @.MyPK int,
> @.FileName varchar(255)
> AS
> DECLARE @.SqlStatement nvarchar(MAX)
> CREATE TABLE #BlobData(BlobData varbinary(MAX))
> --insert blob into temp table
> SET @.SqlStatement => N'
> INSERT INTO #BlobData
> SELECT BlobData.*
> FROM OPENROWSET
> (BULK ''' + @.FileName + ''',
> SINGLE_BLOB) BlobData'
> EXEC sp_executesql @.SqlStatement
> --update main table with blob data
> UPDATE dbo.MyTable
> SET MyBlob = (SELECT BlobData FROM #BlobData)
> WHERE MyTable.MyPK = @.MyPK
> DROP TABLE #BlobData
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "udb" <datacen@.vip.sina.com> wrote in message
> news:udiThYm2GHA.1292@.TK2MSFTNGP03.phx.gbl...
>>i like textcopy in sql server 2000, but i don't find it in sql server
>>2005. is there has replace_tool in sql server 2005?
>> thanks
>>
>> jason
>|||I would use SSIS. It's quite flexible and allows you to create any sort of
standard or custom file output.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"udb" <datacen@.vip.sina.com> wrote in message
news:Ow%23YLgz2GHA.1548@.TK2MSFTNGP02.phx.gbl...
> thanks!
> but if i need export image/text data, how do?
> SSIS or command?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net>
> Ð´ÈëÏûÏ¢ÐÂÎÅ:uojyazm2GHA.4648@.TK2MSFTNGP04.phx.gbl...
>> >i like textcopy in sql server 2000, but i don't find it in sql server
>> >2005. is there has replace_tool in sql server 2005?
>> Textcopy was an unsupported sample applcaiton provided with SQL 2000. In
>> SQL 2005, you can accomplish the same result in Transact-SQL using
>> OPENROWSET...BULK. The proc example below imports file contents into a
>> temp table and then updates the specified main table row with the blob
>> data.
>>
>> CREATE PROC dbo.UpdateMyTableImageDataFromFile
>> @.MyPK int,
>> @.FileName varchar(255)
>> AS
>> DECLARE @.SqlStatement nvarchar(MAX)
>> CREATE TABLE #BlobData(BlobData varbinary(MAX))
>> --insert blob into temp table
>> SET @.SqlStatement =>> N'
>> INSERT INTO #BlobData
>> SELECT BlobData.*
>> FROM OPENROWSET
>> (BULK ''' + @.FileName + ''',
>> SINGLE_BLOB) BlobData'
>> EXEC sp_executesql @.SqlStatement
>> --update main table with blob data
>> UPDATE dbo.MyTable
>> SET MyBlob = (SELECT BlobData FROM #BlobData)
>> WHERE MyTable.MyPK = @.MyPK
>> DROP TABLE #BlobData
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "udb" <datacen@.vip.sina.com> wrote in message
>> news:udiThYm2GHA.1292@.TK2MSFTNGP03.phx.gbl...
>>i like textcopy in sql server 2000, but i don't find it in sql server
>>2005. is there has replace_tool in sql server 2005?
>> thanks
>>
>> jason
>>
>

help textcopy

Help talking to stored proc

Can someone please lend a hand.
I am a total noob at this .NET/ASP stuff. I simply need help passing and returning values to a stored procedure.
Here’s my sp.

ALTER PROCEDURE [dbo].[returnIdUser]-- Add the parameters for the stored procedure here @.sessionUservarchar(25)OUTPUTASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT idUserFROM tblUsersWHERE domainUser = @.sessionUserEND

So when the user logs into to the default page (windows authentication) I capture their domain\login in a session variable (sessionUser). On the next page, when the page loads I need to pass this to my stored proc and get the idUser in return.
Here is what I have for the page load so far.

try{ SqlConnection cxnReturnID =new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString); SqlCommand cmdReturnID =new SqlCommand("returnIdUser", cxnReturnID); cmdReturnID.CommandType = CommandType.StoredProcedure; cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output; cmdReturnID.Connection.Open(); cmdReturnID.ExecuteReader(); cmdReturnID.Connection.Close(); cmdReturnID.Connection.Dispose(); Session["sessionUserID"] = cmdReturnID.Parameters["idUser"].Value;}catch (Exception ex){ lblStatus.Text = ex.Message;}

Of course this fails cause I don’t know what I’m doing. My error label shows the following: "An SqlParameter with ParameterName 'idUser' is not contained by this SqlParameterCollection."

Can someone point me to what I’m doing wrong?

Hmm.. You mixed up every thing here..

First you need to a pass an input parameter and expect an out put paremeter. So, your stored procedure should have two paramets defined in it.. You have only one

And also, in your c# code, you need to assign the value of session variable to the input parameter, which you have not.

Anyway, try this..

ALTER PROCEDURE [dbo].[returnIdUser]
-- Add the parameters for the stored procedure here


 @.sessionUservarchar(25),

 @.idOUT varchar(25) outAS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set idOUT = (SELECT idUserFROM tblUsersWHERE domainUser = @.sessionUser)

return @.idOUTEND

and your code to

cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25).Direction = ParameterDirection.Input;

command.Parameters["@.sessionUser"].Value =  Session["UserName"]

 cmdReturnID.Parameters.Add("@.idOUT", SqlDbType.VarChar, 25).Direction = ParameterDirection.Output;

SqlDataReader reader = cmdReturnID.ExecuteReader();

 reader.close();

 Session["sessionUserID"] = cmdReturnID.Parameters["idOUT"].Value;

Even though, the code looks real, its not... I typed by hand, so beware of small errors...

|||

Hi , in your example I guess you want to pass a session user into the stored procedure and return idUser. In this case your stored procedure should be :

ALTER PROCEDURE [dbo].[returnIdUser]-- Add the parameters for the stored procedure here (@.sessionUservarchar(25))ASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements.SET NOCOUNT ON;-- Insert statements for procedure hereSELECT idUserFROM tblUsersWHERE domainUser = @.sessionUserEND

And here is the modified code:

try { SqlConnection cxnReturnID =new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString); SqlCommand cmdReturnID =new SqlCommand("returnIdUser", cxnReturnID); cmdReturnID.CommandType = CommandType.StoredProcedure; cmdReturnID.Parameters.Add("@.sessionUser", SqlDbType.VarChar, 25); cmdReturnID.Connection.Open(); SqlDataReader sqldr = cmdReturnID.ExecuteReader(); cmdReturnID.Connection.Close(); cmdReturnID.Connection.Dispose(); Session["sessionUserID"] = sqldr.GetString(0); }catch (Exception ex) { lblStatus.Text = ex.Message; }

Here I use sessionUser as an input value and read idUser with SqlDataReader.Hope this helps.|||it took some alterations, but finally got it to work. Thanks to both of you!

help- table record not selected if only column names specfied

we have a SQL 2k Database of size 2 GB. when we select data from a table using * it is returning the record.
Query is "select * from customermaster where ID=5"
result 1 record selected
if only column specified then it is not returning the any data.
Query is "select customername from customermaster where ID=5"
reult zero record selected
when the same table recreated and data transferred then it is working fine
could you please tell us what is the problem and how to identify the table is having this type of problem.
Thanking You
Hi,
Can you execute the below commands in query analyzer forcustomermaster
table
use dbname
go
dbcc checktable('customermaster')
go
sp_spaceused 'customermaster',@.updateusage='true'
go
select customername from customermaster where ID=5
Thanks
Hari
MCDBA
"Narayan Saran" <Narayan Saran@.discussions.microsoft.com> wrote in message
news:2D6790EA-8717-45BF-B76D-AF5B64C3AF8D@.microsoft.com...
> we have a SQL 2k Database of size 2 GB. when we select data from a table
using * it is returning the record.
> Query is "select * from customermaster where ID=5"
> result 1 record selected
> if only column specified then it is not returning the any data.
> Query is "select customername from customermaster where ID=5"
> reult zero record selected
> when the same table recreated and data transferred then it is working fine
> could you please tell us what is the problem and how to identify the table
is having this type of problem.
> Thanking You
>
>
>

help- table record not selected if only column names specfied

we have a SQL 2k Database of size 2 GB. when we select data from a table us
ing * it is returning the record.
Query is "select * from customermaster where ID=5"
result 1 record selected
if only column specified then it is not returning the any data.
Query is "select customername from customermaster where ID=5"
reult zero record selected
when the same table recreated and data transferred then it is working fine
could you please tell us what is the problem and how to identify the table i
s having this type of problem.
Thanking YouHi,
Can you execute the below commands in query analyzer forcustomermaster
table
use dbname
go
dbcc checktable('customermaster')
go
sp_spaceused 'customermaster',@.updateusage='true'
go
select customername from customermaster where ID=5
Thanks
Hari
MCDBA
"Narayan Saran" <Narayan Saran@.discussions.microsoft.com> wrote in message
news:2D6790EA-8717-45BF-B76D-AF5B64C3AF8D@.microsoft.com...
> we have a SQL 2k Database of size 2 GB. when we select data from a table
using * it is returning the record.
> Query is "select * from customermaster where ID=5"
> result 1 record selected
> if only column specified then it is not returning the any data.
> Query is "select customername from customermaster where ID=5"
> reult zero record selected
> when the same table recreated and data transferred then it is working fine
> could you please tell us what is the problem and how to identify the table
is having this type of problem.
> Thanking You
>
>
>

help- table record not selected if only column names specfied

we have a SQL 2k Database of size 2 GB. when we select data from a table using * it is returning the record.
Query is "select * from customermaster where ID=5"
result 1 record selected
if only column specified then it is not returning the any data.
Query is "select customername from customermaster where ID=5"
reult zero record selected
when the same table recreated and data transferred then it is working fine
could you please tell us what is the problem and how to identify the table is having this type of problem.
Thanking YouHi,
Can you execute the below commands in query analyzer forcustomermaster
table
use dbname
go
dbcc checktable('customermaster')
go
sp_spaceused 'customermaster',@.updateusage='true'
go
select customername from customermaster where ID=5
--
Thanks
Hari
MCDBA
"Narayan Saran" <Narayan Saran@.discussions.microsoft.com> wrote in message
news:2D6790EA-8717-45BF-B76D-AF5B64C3AF8D@.microsoft.com...
> we have a SQL 2k Database of size 2 GB. when we select data from a table
using * it is returning the record.
> Query is "select * from customermaster where ID=5"
> result 1 record selected
> if only column specified then it is not returning the any data.
> Query is "select customername from customermaster where ID=5"
> reult zero record selected
> when the same table recreated and data transferred then it is working fine
> could you please tell us what is the problem and how to identify the table
is having this type of problem.
> Thanking You
>
>
>

Help switching to SQL Authentication

At the moment my asp.net app is working ok and I can connect to the database using windows authentication, however i'm trying to use sql authentication on my local computer.

i'm using sql server studio at the moment to manage the database. And tried numerious combinations of things to try and get it working and allow me to connect to the database using SQL authentication but still no luck :(

can anyone give me some rough step by step instructions to setting up the sql username and password for forms authentication and activating it for a certain database?

thanksWhen you say forms authentication, I assume you mean that in your web.config file you have something like this:

<authentication mode="Forms">
<forms loginUrl="Default.aspx" protection="Validation" timeout="300"/>
</authentication
If so, then a connection string that works connecting to sqlserver is this:

<add name="PolarIntegrationConnectionString2" connectionString="Data Source=INSP8600;Initial Catalog=PolarIntegration;Integrated Security=True"
providerName="System.Data.SqlClient" /
Let me know if that doesn't help.

Help SVP: How to connect to external server and backup db with SSMSExpress

Bonjour
You'll have to be gentil with me, because until today I had never worked with MS SQL (I was developing with Access).

Scenario & question:
I have installed a phpBB forum running on MS SQL on my clients server. Since Saturday this forum just won't load anymore. I have tested the connection to the SQL database within Macromedia Dreamweaver and it's fine. I can view the table and the data contained in them. I would like to re-install phpBB but I need to back up the db first.

I have installed SQL SERVER MANAGEMENT STUDIO EXPRESS, and all the other things like .NET 2.0 etc. It seems to work, as I can look at local SQL database samples, their table and data on my PC running XP Professional.

Question 1: How can connect to the external database? I have all the info necessary like ip address, name of the db, user name and password; but I can't seem to find a way to create an external connection from within the SSMSExpress. Could you please help.
Question 2: Once I am connected how can I backup the SQL db?

Many thanks in advance

When you run SSMS Express, it should give you a login screen where you can pick the server you want to log in to. You can also add servers in the Registered Servers windows.

To backup, you can go to Databases in the Object Explorer, select the database you want, right click on it and choose backup.

Thanks< MJ

|||

Thanks, done that and it works

Help SVP: How to connect to external server and backup db with SSMSExpress

Bonjour
You'll have to be gentil with me, because until today I had never worked with MS SQL (I was developing with Access).

Many thanks in advance

When you run SSMS Express, it should give you a login screen where you can pick the server you want to log in to. You can also add servers in the Registered Servers windows.

To backup, you can go to Databases in the Object Explorer, select the database you want, right click on it and choose backup.

Thanks< MJ

|||

Thanks, done that and it works

Help SVP: How to connect to external server and backup db with SSMSExpress

Bonjour
You'll have to be gentil with me, because until today I had never worked with MS SQL (I was developing with Access).

Many thanks in advance

When you run SSMS Express, it should give you a login screen where you can pick the server you want to log in to. You can also add servers in the Registered Servers windows.

To backup, you can go to Databases in the Object Explorer, select the database you want, right click on it and choose backup.

Thanks< MJ

|||

Thanks, done that and it works

Help Supress line object in CrytalReports

How can I supress line objects in crystal report?Does nobody knows ?|||well.. u need to suppress with formula..

if not ..then its so easy to suppress..right click..format line... then click on suppress...

but ..if u need to suppress with a formula / or condition..

then try this.

insert a line into the new section..
i.e. if u want to print lilne in detail section..
insert a detail section other than main detail section...so this section will be detail - 2
and insert line in new detail section...so the section have only the line..
then suppress or unsuppress the whole section with the formula ..

might be helpful
zeeshan|||you can manipulate it in VB or suppress it in the cryatal report. in VB just type the name of your report and select the line then suppres it. but first you have to make a reference to your report...

dim myreportname as yourreportname
set myreportname = new yourreportname

myreportname.line1.suppress = false

Help suppress

I am trying to suppress some details, what I have is 3 columns 1st is order number, 2nd is product, 3rd stock status ie allocated/back ordered. It prints a line per product and the status. What I am trying to do is if the order has one line on back order then I want to suppress the whole order.

Thanks

RichardHi,
Can you post the expected result with some sample data?

Madhivanan|||What version on Crystal are you using? I use 8.5, so you may need to modify the following to work with your version.

You can use a conditional suppress. I will assume that you have each order appearing in the Details section. Go into the Format of the details section. Find where you can check to Suppress that section. You should see a button that looks like 'x-2'. Click the button and enter a formula. The Detail section will be supressed each time the formula evaluates to True.

Example, put this in the Formula box when you click 'x-2':
{StockStatus} = "BackOrdered"

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 Stored Procedures

Stored procedures called from asp pages or from a VB-batch program are not executed completely. There are no error-messages or warnings generated. The SP just stops executing and returns to the initial program as if it was executed normally...

No idea how this can be solved as those SP seem only to have problems when there's a 'lot' of data that has to be processed. When it's about few data the SP executes normally...

Any one who can help?

Thanks!Get return code from sp - it was post here about it before - try to find.|||Can't get these return codes as this problem is only happening on production servers, not on dev-servers...

Supose this must be something to do with settings or so... but no idea which ones...

Thanks for help!|||Originally posted by carlbraems
The SP just stops executing and returns to the initial program as if it was executed normally...
Thanks!

It's that damn miracle thing again...

Can you repeat the problem?

Fire up profiler and see what's going on...

I bet bug in sproc...|||Found solution:
These are queries that don't return a recordset and need to have the following setting switched off: SET NOCOUNT.

Thanks anyway!

Help stored procedures

Hello ,

I am in a state of conflict with my c# asp.net web app. Basically i have a stored procedure that updates my customer table with the param supplied, and generates the primary key incrementally. My problem is returning the pk to my asp.net.

Procedure looks like so:

ALTER Procedure newUser (
/* Param List */

@.fname varchar(20),
@.lname varchar(20),
@.address1 varchar(20),
@.address2 varchar(20),
@.city varchar(20),
@.province varchar(20),
@.postalCode varchar(7),
@.country varchar(20),
@.phone varchar(10),
@.email varchar(30),
@.receiveNews bit,
@.archive bit,
@.business varchar(10),
@.fax varchar(10)
)
AS

BEGIN TRANSACTION
INSERT INTO customer (
fname,
lname,
address1,
address2,
city,
province,
postalCode,
country,
phone,
email,
receiveNews,
archive,
business,
fax
)
VALUES (
@.fname,
@.lname,
@.address1,
@.address2,
@.city,
@.province,
@.postalCode,
@.country,
@.phone,
@.email,
@.receiveNews,
@.archive,
@.business,
@.fax
)
COMMIT
RETURN

The syntax has been verified correct and does populate my customers table with valid data.

the column that i wish to return is called "pk_customerId".
I tried using "RETURN pk_customerId" but it gives me an error that the column does not exist even thogh it does.

Any ideas.

Thanks in advancein short, you can do this:

return (scope_identity())

but i'd declare a @.variable and an @.error before your begin tran, then right before your commit do this:

select @.error=@.@.error, @.variable=scope_identity()
if @.error != 0 begin
raiserror ('jkshdk fskjhdf kjshdf jkshdfh', 10, 1)
rollback tran
end
commit
return @.variable

Help Stored procedure working but not doing anything

Help Stored procedure working but not doing anything New Post
Quote Reply
Please i need some help.

I am calling a stored procedure from asp.net and there is acursor in the stored procedure that does some processing on servaltables.

if i run the stored procedure on Query Analyzer it works and does whatit is suppose to do but if i run it from my asp.net/module control itgoes. acts likes it worked but it does not do what is suppose to do.
i believe the cursor in the stroed procedure does not run where iscalled programmatically from the asp.net/module control page.plus itdoes not throw any errors

This is the code from my control
System.Data.SqlClient.SqlParameter [] param={newSystem.Data.SqlClient.SqlParameter("@.periodStart",Convert.ToDateTime(startDate)),newSystem.Data.SqlClient.SqlParameter("@.periodStart",Convert.ToDateTime(endDate)),newSystem.Data.SqlClient.SqlParameter("@.addedby",UserInfo.FullName+ ""+UserInfo.Username)};
stringstr=System.Configuration.ConfigurationSettings.AppSettings["payrollDS"];
System.Data.SqlClient.SqlConnection cn=newSystem.Data.SqlClient.SqlConnection(str);
cn.Open();
//System.Data.SqlClient.SqlTransaction trans=cn.BeginTransaction();

SqlHelper.ExecuteScalar(cn,System.Data.CommandType.StoredProcedure,"generatePaylistTuned",param);

--------THis is the code for my storedprocedure----

CREATE PROCEDURE [dbo].[generatePaylistTuned]
@.periodStart datetime,
@.periodEnd datetime,
@.addedby varchar(40)

begin transaction generatePayList

DECLARE @.pensioner_id int, @.dateadded datetime,
@.amountpaid float,
@.currentMonthlypension float,@.actionType varchar(50),
@.isAlive bit,@.isActive bit,@.message varchar(80),@.NoOfLoadedPensioners int,
@.NoOfDeadPensioners int,@.NoOfEnrolledPensioners int,@.DeactivatedPensioners int,
@.reportSummary varchar(500)

set @.NoOfLoadedPensioners =0

set @.NoOfDeadPensioners=0
set @.NoOfEnrolledPensioners=0
set @.DeactivatedPensioners=0
set @.actionType ="PayList Generation"

DECLARE paylist_cursor CURSOR FORWARD_ONLY READ_ONLY FOR

select p.pensionerId,p.isAlive,p.isActive,py.currentMonthlypension
from pensioner p left outer join pensionpaypoint py on p.pensionerid=py.pensionerId

where p.isActive = 1

OPEN paylist_cursor

FETCH NEXT FROM paylist_cursor
INTO @.pensioner_id,@.isAlive,@.isActive,@.currentMonthlypension

WHILE @.@.FETCH_STATUS = 0
BEGIN

set @.NoOfLoadedPensioners=@.NoOfLoadedPensioners+1
if(@.isAlive=0)
begin
update Pensioner
set isActive=0
where pensionerid=@.pensioner_id
set @.DeactivatedPensioners =@.@.ROWCOUNT+@.DeactivatedPensioners
set @.NoOfDeadPensioners =@.@.ROWCOUNT+@.NoOfDeadPensioners
end
else
begin
insert into pensionpaylist(pensionerId,dateAdded,addedBy,
periodStart,periodEnd,amountPaid)
values(@.pensioner_id,getDate(),@.addedby, @.periodStart, @.periodEnd,@.currentMonthlypension)
set @.NoOfEnrolledPensioners =@.@.ROWCOUNT+ @.NoOfEnrolledPensioners
end

-- Get the next author.
FETCH NEXT FROM paylist_cursor
INTO @.pensioner_id,@.isAlive,@.isActive,@.currentMonthlypension
END

CLOSE paylist_cursor
DEALLOCATE paylist_cursor

set @.reportSummary ="The No. of Pensioners Loaded:"+Convert(varchar,@.NoOfLoadedPensioners)+" "+"The No. OfDeactivated Pensioners:"+Convert(varchar,@.DeactivatedPensioners)+" "+"The No. ofEnrolled Pensioners:"+Convert(varchar,@.NoOfEnrolledPensioners)+" "+"No Of DeadPensioner from Pensioners Loaded: "+Convert(varchar,@.NoOfDeadPensioners)
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),0, @.periodStart, @.periodEnd,@.reportSummary,'Pay List Generation')

if (@.@.ERROR <> 0)
BEGIN

insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),1, @.periodStart,@.periodEnd,@.reportSummary,'Pay List Generation')

ROLLBACK TRANSACTION generatePayList

END

commit Transaction generatePayList
GOYou may need try to use"ExecuteNonQuery() ", not "ExecuteScalar()".|||I used ExecuteNonQuery and it still gave me the same result.it did nothing.
i really need ur help.thanks|||

CREATE PROCEDURE [dbo].[generatePaylistTuned]
@.periodStart datetime,
@.periodEnd datetime,
@.addedby varchar(40)

BEGIN TRANSACTION

DECLARE @.NoOfLoadedPensioners int,
@.NoOfDeadPensioners int,@.NoOfEnrolledPensioners int,@.DeactivatedPensioners int,
@.reportSummary varchar(500)

UPDATE Pensioner
SET isActive=0
WHERE isAlive=0 AND isActive=1

IF (@.@.ERROR=0)
BEGIN

SET @.DeactivatedPensioners=@.@.ROWCOUNT
SET @.NoOfDeadPensioners=@.@.ROWCOUNT

INSERT INTO pensionpaylist(pensionerId,dateAdded,addedBy,periodStart,periodEnd,amountPaid)
SELECT p.pensionerId,getDate(),@.addedby,@.periodStart,@.periodEnd,py.currentMonthlypension
FROM pensioner p
LEFT JOIN pensionpaypoint py on p.pensionerid=py.pensionerId
WHERE p.isActive = 1

IF (@.@.ERROR=0)
BEGIN

SET @.NoOfEnrolledPensioners=@.@.ROWCOUNT
SET @.NoOfLoadedPensioners=@.NoOfEnrolledPensioners+@.DeactivatedPensioners

SET @.reportSummary ="The No. of Pensioners Loaded: "+Convert(varchar,@.NoOfLoadedPensioners)+" "+"The No. Of Deactivated Pensioners: "+Convert(varchar,@.DeactivatedPensioners)+" "+"The No. of Enrolled Pensioners: "+Convert(varchar,@.NoOfEnrolledPensioners)+" "+"No Of Dead Pensioner from Pensioners Loaded: "+Convert(varchar,@.NoOfDeadPensioners)

END
END

IF (@.@.ERROR<>0)
BEGIN
INSERT INTO reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),1, @.periodStart, @.periodEnd,@.reportSummary,'Pay List Generation')
ROLLBACK TRANSACTION
END
ELSE
BEGIN
INSERT INTO reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType)
values(getDate(),0, @.periodStart,@.periodEnd,@.reportSummary,'Pay List Generation')
COMMIT TRANSACTION
END
GO

Should do the same thing as what you had, although run much, much quicker depending on how many Pensioneers you process (The more you process, the faster this will run in comparision). Although, I question the error handling/checking/reporting that you have. I've also changed that part so that if there is an error, only 1 record is inserted into the reportSummary (Your code would have indicated no errors, then another record indicating there was an error, if that is what you really wanted, you'll have to change it)

In any case, I don't see anything in the stored procedure that would cause it to behave differently depending on which environment you are running it in. You've abstracted the sql guts into you sqlhelper class, so we really can't see what you are calling anyhow. I would recommend that you run a sql trace with the sql profiler and see what sql you are generating. Then copy and paste the code into query analyzer/management studio and see if it acts the same there, then play with it until it works. My guess is the problem lies within the SqlHelper class, and it's not generating sql that you would expect from the code you've shown.

|||well thanks but i have run the stored procedure many times in query analyzer and it works fine does what it suppose to do.
but the problem is running it from the application using SqlHelper.executeNonquery that is y i am so confused.

I just don't know what the problem is.
It works in query analyzer but does not do anything when called from the application.
thanks|||I found out the problem.
Iused profiler totrace the events when i call the stored procedure and it gave a sp:cachemiss
so i fixed its working now.
thanks forur help

help Stored proc

Hi,
Could any one please help me in creating sp.
It should accept one input parameter. when you pass value 1 to this parameter it should show all odd numbers from 1 to 100 and when you pass value 2 it should show all even numbers from 1 to 100.
Thanks in advance.
-ssTry this:

create procedure p_odd_even(@.i int)
as
select number -1 + @.i from master..spt_values where type = 'P' and number % 2 = 1 and number <= 100|||Very clever trick - what is the purpose of this table in SQL|||too clever

you would need DISTINCT in there, vaxman, and also restrict number to between 0 and 100, not just less than or equal to 100

the use of master..spt_values is a hack

better to declare an integers table, because it will come in handy in so many other queries|||Not sure what spt_values is for. I have seen it used for things like this. In examining the table, it looks like type 'P' does return distinct integers, but as Rudy says, much better to have your own table. I create one like this:

select top 8000 id = identity(int,1,1) into Numbers from sysobjects s1, sysobjects s2, sysobjects s3

(8000 because I usually use it for parsing varchar strings but change for your needs)

Or you can just generate your 50 numbers on the fly. For 50 numbers this is probably more efficient (no I/O and 50 numbers is small enough not to generate a work table) but for larger counts a real table is better because statistics will be kept for it and indexes used.

create procedure p_odd_even(@.i int)
as

select (a0.id + a1.id) + @.i id
FROM
(select 0 id union select 2 union select 4 union select 6 union select 8 ) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40
union select 50 union select 60 union select 70 union select 80 union select 90) a1
order by 1|||You could always use:CREATE PROCEDURE pSSkris
@.arg INT = 1
AS

SELECT n
FROM (SELECT 1 + 10 * tens + ones AS n
FROM (SELECT 0 AS ones UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS a
CROSS JOIN (SELECT 0 AS tens UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
UNION SELECT 8 UNION SELECT 9) AS b) AS c
WHERE n % 2 = 2 - @.arg
ORDER BY n

RETURN
GO-PatP|||uh Pat, didn't I say that? (but you only need 50 numbers...)|||uh Pat, didn't I say that? (but you only need 50 numbers...)Sort of... You relied on another table outside of the problem definition, but my suggestion was self contained. I suspect that mine will be easier to explain too, but that's a relative kind of thing.

-PatP|||I think you missed this part:

create procedure p_odd_even(@.i int)
as
select (a0.id + a1.id) + @.i id
FROM
(select 0 id union select 2 union select 4 union select 6 union select 8 ) a0,
(select 0 id union select 10 union select 20 union select 30 union select 40
union select 50 union select 60 union select 70 union select 80 union select 90) a1
order by 1|||Sorry, I was only looking at your first posting in my previous comment.

-PatP|||As an interesting wrinkle, you could also use:SELECT n
FROM (SELECT d0.b + d1.b + d2.b + d3.b + d4.b + d5.b + d6.b AS n
FROM (SELECT 0 AS b UNION SELECT 1) AS d0
CROSS JOIN (SELECT 0 AS b UNION SELECT 2) AS d1
CROSS JOIN (SELECT 0 AS b UNION SELECT 4) AS d2
CROSS JOIN (SELECT 0 AS b UNION SELECT 8) AS d3
CROSS JOIN (SELECT 0 AS b UNION SELECT 16) AS d4
CROSS JOIN (SELECT 0 AS b UNION SELECT 32) AS d5
CROSS JOIN (SELECT 0 AS b UNION SELECT 64) AS d6
) AS z
WHERE n BETWEEN 1 AND 100
ORDER BY n-PatP|||As an interesting wrinkle, you could also use:CREATE PROCEDURE pSSkris2
@.arg INT = 1
AS

SELECT n
FROM (SELECT d0.b + d1.b + d2.b + d3.b + d4.b + d5.b + d6.b AS n
FROM (SELECT 2 - @.arg AS b) AS d0
CROSS JOIN (SELECT 0 AS b UNION SELECT 2) AS d1
CROSS JOIN (SELECT 0 AS b UNION SELECT 4) AS d2
CROSS JOIN (SELECT 0 AS b UNION SELECT 8) AS d3
CROSS JOIN (SELECT 0 AS b UNION SELECT 16) AS d4
CROSS JOIN (SELECT 0 AS b UNION SELECT 32) AS d5
CROSS JOIN (SELECT 0 AS b UNION SELECT 64) AS d6
) AS z
WHERE n BETWEEN 1 AND 100
ORDER BY n

RETURN-PatP|||I wonder if you'll get an "A"...|||Who wants to explain it? Without an explanation, I'd expect that the code is worthless.

-PatP|||if it's for a school assignment, i would stringly suggest to sskris to submit the solution in post #2

that'll get an A+|||I would expect that any of the solutions that we've offered will get a trip to the Dean's office and an opportunity to use this experience in their upcomming ethics class (whether it was part of the curriculum before now or not). These solutions may help them think about the answer they want to give, but I can't imagine any of them being "safe" to turn in as they are.

-PatP|||Thanks to all of you..
I really appreciate that.
Kris

help store procedure timeout

hi
I have one store procedure (SP) on a Sqlservr2000 under win2003 that SP
takes 7 minutes to run but if I get the query and tput it to the
queryanalizer it takes 16 seconds!!! I dont understand why, two days ago
that SP was working right but sudenly got slow!!! i dont know what to do ,
please helpppppppppp
Antother tip, my asp.net application reports the timeout problem but If I
run the same app with a copy of that DataBase on my nootebook (xp) using
the debuger all works fine!!! I am really confused
THANKS for your help
SergioHi Sergio
How did you run the query in Query Analyser? If you ran the query with hard
coded parameter values it is not the same as running the stored procedure.
Have you updated statistics and defragemented your indexes? You should have
a
regular maintenance plan to do this and also do this if you make significant
changes to the data (such as a batch update!). If the indexes and statistics
are up-to-date you could have a poor query plan, try executing the procedure
with the with RECOMPILE option or use sp_recompile to force a new query plan
.
If your code does not branch in the stored procedure you may be better
splitting it into multiple sub-procedures.
John
"SergioT" wrote:

> hi
> I have one store procedure (SP) on a Sqlservr2000 under win2003 that SP
> takes 7 minutes to run but if I get the query and tput it to the
> queryanalizer it takes 16 seconds!!! I don′t understand why, two days ago
> that SP was working right but sudenly got slow!!! i don′t know what to do
,
> please helpppppppppp
> Antother tip, my asp.net application reports the timeout problem but If
I
> run the same app with a copy of that DataBase on my nootebook (xp) using
> the debuger all works fine!!! I am really confused
>
> THANKS for your help
> Sergio
>
>|||Hi Sergio
How did you run the query in Query Analyser? If you ran the query with hard
coded parameter values it is not the same as running the stored procedure.
Have you updated statistics and defragemented your indexes? You should have
a
regular maintenance plan to do this and also do this if you make significant
changes to the data (such as a batch update!). If the indexes and statistics
are up-to-date you could have a poor query plan, try executing the procedure
with the with RECOMPILE option or use sp_recompile to force a new query plan
.
If your code does not branch in the stored procedure you may be better
splitting it into multiple sub-procedures.
John
"SergioT" wrote:

> hi
> I have one store procedure (SP) on a Sqlservr2000 under win2003 that SP
> takes 7 minutes to run but if I get the query and tput it to the
> queryanalizer it takes 16 seconds!!! I don′t understand why, two days ago
> that SP was working right but sudenly got slow!!! i don′t know what to do
,
> please helpppppppppp
> Antother tip, my asp.net application reports the timeout problem but If
I
> run the same app with a copy of that DataBase on my nootebook (xp) using
> the debuger all works fine!!! I am really confused
>
> THANKS for your help
> Sergio
>
>

help store procedure timeout

hi
I have one store procedure (SP) on a Sqlservr2000 under win2003 that SP
takes 7 minutes to run but if I get the query and tput it to the
queryanalizer it takes 16 seconds!!! I don´t understand why, two days ago
that SP was working right but sudenly got slow!!! i don´t know what to do ,
please helpppppppppp
Antother tip, my asp.net application reports the timeout problem but If I
run the same app with a copy of that DataBase on my nootebook (xp) using
the debuger all works fine!!! I am really confused
THANKS for your help
SergioHi Sergio
How did you run the query in Query Analyser? If you ran the query with hard
coded parameter values it is not the same as running the stored procedure.
Have you updated statistics and defragemented your indexes? You should have a
regular maintenance plan to do this and also do this if you make significant
changes to the data (such as a batch update!). If the indexes and statistics
are up-to-date you could have a poor query plan, try executing the procedure
with the with RECOMPILE option or use sp_recompile to force a new query plan.
If your code does not branch in the stored procedure you may be better
splitting it into multiple sub-procedures.
John
"SergioT" wrote:
> hi
> I have one store procedure (SP) on a Sqlservr2000 under win2003 that SP
> takes 7 minutes to run but if I get the query and tput it to the
> queryanalizer it takes 16 seconds!!! I donÂ´t understand why, two days ago
> that SP was working right but sudenly got slow!!! i donÂ´t know what to do ,
> please helpppppppppp
> Antother tip, my asp.net application reports the timeout problem but If I
> run the same app with a copy of that DataBase on my nootebook (xp) using
> the debuger all works fine!!! I am really confused
>
> THANKS for your help
> Sergio
>
>

Help stop my spinning wheels on user sql

Can query sysprocesses to get user information .Can find the top
blocker
but have spun wheels for a long long time unable to find sql run by top
blocker.
Any body done this before or has directions to scripts that can do this
Your input is highly appreciated
MassaTry:
select
p1.*
from
dbo.sysprocesses p1
where exists
(
select
*
from
dbo.sysprocesses p2
where
p2.blocked = p1.spid
)
and p1.blocked in (0, p1.spid)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<mngong@.gmail.com> wrote in message
news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
Can query sysprocesses to get user information .Can find the top
blocker
but have spun wheels for a long long time unable to find sql run by top
blocker.
Any body done this before or has directions to scripts that can do this
Your input is highly appreciated
Massa|||Thanks Dr Tom
Needed the complete sql that has been my bete noire.
at the time the blocking occured
Tom Moreau wrote:
> Try:
> select
> p1.*
> from
> dbo.sysprocesses p1
> where exists
> (
> select
> *
> from
> dbo.sysprocesses p2
> where
> p2.blocked = p1.spid
> )
> and p1.blocked in (0, p1.spid)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <mngong@.gmail.com> wrote in message
> news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
> Can query sysprocesses to get user information .Can find the top
> blocker
> but have spun wheels for a long long time unable to find sql run by top
> blocker.
> Any body done this before or has directions to scripts that can do this
> Your input is highly appreciated
> Massa|||In that case, you'd pretty much need to be running the profiler at the time
it occurred.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<mngong@.gmail.com> wrote in message
news:1153234728.918733.70910@.35g2000cwc.googlegroups.com...
Thanks Dr Tom
Needed the complete sql that has been my bete noire.
at the time the blocking occured
Tom Moreau wrote:
> Try:
> select
> p1.*
> from
> dbo.sysprocesses p1
> where exists
> (
> select
> *
> from
> dbo.sysprocesses p2
> where
> p2.blocked = p1.spid
> )
> and p1.blocked in (0, p1.spid)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <mngong@.gmail.com> wrote in message
> news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
> Can query sysprocesses to get user information .Can find the top
> blocker
> but have spun wheels for a long long time unable to find sql run by top
> blocker.
> Any body done this before or has directions to scripts that can do this
> Your input is highly appreciated
> Massa|||I can't tell if you're using SQL Server 2005 here, but if you are, you might
be interested in the 'blocked process threshold' parameter of sp_configure.
On our testing servers I have a trace going that continually monitors for
these problem areas. What gets returned is an XML block which details the
blocking and blocked processes - very nice indeed. If you're on SQL Server
2000, the sp_blocker_pss80 scripts would be worth looking at
(http://support.microsoft.com/?id=271509).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Help stop my spinning wheels on user sql

Can query sysprocesses to get user information .Can find the top
blocker
but have spun wheels for a long long time unable to find sql run by top
blocker.
Any body done this before or has directions to scripts that can do this
Your input is highly appreciated
MassaTry:
select
p1.*
from
dbo.sysprocesses p1
where exists
(
select
*
from
dbo.sysprocesses p2
where
p2.blocked = p1.spid
)
and p1.blocked in (0, p1.spid)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<mngong@.gmail.com> wrote in message
news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
Can query sysprocesses to get user information .Can find the top
blocker
but have spun wheels for a long long time unable to find sql run by top
blocker.
Any body done this before or has directions to scripts that can do this
Your input is highly appreciated
Massa|||Thanks Dr Tom
Needed the complete sql that has been my bete noire.
at the time the blocking occured
Tom Moreau wrote:
> Try:
> select
> p1.*
> from
> dbo.sysprocesses p1
> where exists
> (
> select
> *
> from
> dbo.sysprocesses p2
> where
> p2.blocked = p1.spid
> )
> and p1.blocked in (0, p1.spid)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <mngong@.gmail.com> wrote in message
> news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
> Can query sysprocesses to get user information .Can find the top
> blocker
> but have spun wheels for a long long time unable to find sql run by top
> blocker.
> Any body done this before or has directions to scripts that can do this
> Your input is highly appreciated
> Massa|||In that case, you'd pretty much need to be running the profiler at the time
it occurred.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<mngong@.gmail.com> wrote in message
news:1153234728.918733.70910@.35g2000cwc.googlegroups.com...
Thanks Dr Tom
Needed the complete sql that has been my bete noire.
at the time the blocking occured
Tom Moreau wrote:
> Try:
> select
> p1.*
> from
> dbo.sysprocesses p1
> where exists
> (
> select
> *
> from
> dbo.sysprocesses p2
> where
> p2.blocked = p1.spid
> )
> and p1.blocked in (0, p1.spid)
>
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <mngong@.gmail.com> wrote in message
> news:1153233005.503275.256110@.i42g2000cwa.googlegroups.com...
> Can query sysprocesses to get user information .Can find the top
> blocker
> but have spun wheels for a long long time unable to find sql run by top
> blocker.
> Any body done this before or has directions to scripts that can do this
> Your input is highly appreciated
> Massa|||I can't tell if you're using SQL Server 2005 here, but if you are, you might
be interested in the 'blocked process threshold' parameter of sp_configure.
On our testing servers I have a trace going that continually monitors for
these problem areas. What gets returned is an XML block which details the
blocking and blocked processes - very nice indeed. If you're on SQL Server
2000, the sp_blocker_pss80 scripts would be worth looking at
(http://support.microsoft.com/?id=271509).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Help Starting MSDE and creating a db + a log in problem! HELP!

Hello,
I downloaded MSDE from microsoft yesterday but I have been having alot of
problems setting it up. I started the install process with the following code
from te MSDN site:
Setup.exe /qb+ INSTANCENAME=VSDOTNET DISABLENETWORKPROTOCOLS=1 SAPWD=<my
password>
The instalation was to my local machine.
I have managed in visual studio to create a database with tables feild etc
but I keep on having a logon error
Can someone shed any light on the issue?
I assume my u/name is SA or my machine logon?
Did you try
OSQL -S<Servername\InstanceName> -U<Username> -P<password>
--or for integrated authentication
OSQL -S<Servername\InstanceName> -E
HTH, Jens Suessmeyer.

Help Start with MSDE???SA password??

While installing the MSDE I get the folowing error:
A storng SA password is required forsecurity reasons.
Please use SAPWD switch to supply the same.
Refer to readme for more details.
Setup will now exit.

Thanks in advance

MustaphaThe installation instructions that came with your version of MSDE should include help on how to do this.

From where did you get MSDE?

Terri|||Hi,
If you need to change the sa password after the install, and you can uses the osql command line tool that will be available through your computer's command prompt.

If you don't know what a strong password is - it's one that meets certain criteria that make it harder for a hacker to break. I belive the min length is eight characters, it should contain a mix of at least three types of characters - upper case, lower case, numerals, punctuation marks (spaces are allowed). I'd opt for a 'pass-phrase'. Because you can use spaces, etc... you can use a phrase rather than just a string of alpha numeric charcaters. A medium length phrase, 40 charcaters or so, that mixes case and has some numerics is eaier to remember (without the need to write it down; a security problem in itself), and hard to crack.

Never leave the sa password blank.|||

I hope you get this. I am having the exact same trouble and the instructions don't tell me how to deal with this. I didn't have this problem the first time I loaded the program back in September but since my computer crashed and I had to reinstall everything, I am now having to fix this. I used the disc that came with the book and I also tried the version from this site.

Do you have any ideas?

Carolyn (crowbare@.sbcglobal.net)

|||

The reason you're seeing this message is that the MSDE installation executable expects you to pass the parameter to the installer when you run it. It's not intuitive that you have to do this, so when you double-click the download file you will see an "error" indicating that you must specify a strong password.

To avoid this, open a command prompt and navigate to the directory in which the installation file was saved. Call the help on that file by executing my_msde_install_file.exe /?. It will tell you the appropriate switch to use to set the password at runtime.

Hope this helps.

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this?
Any help would be appreciated.
Thanks
Vishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this?
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this'
Any help would be appreciated.
ThanksVishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this'
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

Help --SSIS Dataflow Task

Need help regarding ssis dataflow task
I need to create a ssis package. I want to import the data from a flat
file to a table.
Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
col5.(Assume that all columns can be NULLABLE) The datafile contains
the data related to only three columns say col1, col2, col3. So when I
use dataflow task to import the data from the file to the table, I
will only get three columns, col1, col2, col3. Columns col4, col5 will
be NULL.
However, I want to populate columns col4, col5 with some values which
are stored in the variable.
IS there any way to do this'
Any help would be appreciated.
ThanksVishal wrote:
> Need help regarding ssis dataflow task
> I need to create a ssis package. I want to import the data from a flat
> file to a table.
> Lets say, the table has 5 columns -- col1, col2, col3, col4 ,
> col5.(Assume that all columns can be NULLABLE) The datafile contains
> the data related to only three columns say col1, col2, col3. So when I
> use dataflow task to import the data from the file to the table, I
> will only get three columns, col1, col2, col3. Columns col4, col5 will
> be NULL.
> However, I want to populate columns col4, col5 with some values which
> are stored in the variable.
> IS there any way to do this'
> Any help would be appreciated.
> Thanks
Suppose I have one variable which contains some value. @.[User::exp1]
Now create one more variable which contains source query.
set evaluateasexpression as true for this variable.
set expression like this
"select col1,col2,col3,'"+@.[User::exp1] + "' as col4 from a1"
In Data flow task in source select sql command from variable and select
this variable.
Regards
Amish Shah
http://shahamishm.tripod.com

HELP SQLServer will start, SQL Agent won't

We just had a security fix applied to the server.
When we restarted both SQL Server and SQL Server agent
started. However shortly after, the SQLServerAgent stopped
running. They are both using the same account, but when
you try and start up the SQL Server Agent you get the
following error messages...
[165] ODBC Error: 0, Cannot generate SSPI context
[SQLSTATE HY000]
[000] Unable to connect to server '(local)';
SQLServerAgent cannot start
I have read the KB#811889, but was looking for anyone who
has experienced this issue.
Thanks
Fredtake a look at following article:
http://support.microsoft.com/default.aspx?scid=kb;en-
us;811889
hth
>--Original Message--
>We just had a security fix applied to the server.
>When we restarted both SQL Server and SQL Server agent
>started. However shortly after, the SQLServerAgent
stopped
>running. They are both using the same account, but when
>you try and start up the SQL Server Agent you get the
>following error messages...
>[165] ODBC Error: 0, Cannot generate SSPI context
>[SQLSTATE HY000]
>[000] Unable to connect to server '(local)';
>SQLServerAgent cannot start
>I have read the KB#811889, but was looking for anyone who
>has experienced this issue.
>Thanks
>Fred
>.
>

Help SQL server 7 dump data to hard drive

Hi,

I am running sql server 7 with 200+GB database size. I have one table with following fields

IIINDEX
DOCTYPE
IMAGE BLOB

I need to dump all the information from this table to hard drive.

I have try with delphi ado and delphi odbc (limit 1mb), somehow when I run the program it gives me an error message E_ timeout.

How can I dump this information without using delphi.

Any help will be highly appreciated.

If you have any code that can help please email me samirp@.ix.netcom.com

Thanks.

Samirtry using bcp ... might work .. though havent tried it with Image data.

HELP sql server 2005 express connection hangs up

I got an access 2002 application front end with a sql server 2005 express back end. Some of my clients are having some difficulties. After using the application for a while, some of the users are finding that the system just hangs up. It usually happens after the front end application has been running for about an hour (sometimes sooner and sometimes later). There are perhaps 1 to 5 concurrent users and I have checked to see if there are any firewalls stalling it (I think I check all of them)- Is there any way that SQL Server 2005 express could be caused to just stall- This even occurs with the odd laptop. All the appropriate protocols are enabled as well. These databses are not very large.

ANY HELP WOULD BE GREATLY APPRETIATED!!!

Thanks

Frank Srebot

Moved thread to the SQL Server Express forum.|||

hi Frank,

what do you mean by "just hangs up"? does it completely stalls requiring a reboot, or it's "sleeeping" for just a while and then restarts working "as expected" or the like?

to start, few things to consider..

SQLExpress sets the "autoclose" property of it's created databases to true, and this causes the dbs to be shut down when not in use, meaning that tyey will be closed if no active connection references them.. this involves a little overhead at next re-use as the dbs must be re-open, but I do not think this is your problem... anyway, the eventual related "problem" can be workaround modifying the relative database property via sp_dboption database's system stored procedure call...

"autoshrink" database property is even set to true, and this causes, at engine scheduled time frames, the eventual shrinking of the involved databases, so that when lots of insert/delete operations are performed (actually lots of deletes), the engine wakes up a thread to shrink (when necessary) the databases, requiring some time to execute..

if the autogrowth property of the database's datafiles and logfiles is set to true and the engine states new file space is required, the engine enlarges the files (when needed) and this will obviously involve some time as well...

other non SQL Server related issues includes OS's scheduled tasks requiring lots of CPU and/or I/O..

but it's hard to solve this way

regards

|||

Thanks for the great and quick response.

To clarify, sql server just hangs up meaning that the application displays an hourglass and eventually the sql connection is lost and an error message is given. I was doing some research and I was wondering if the problem could be in the connection pooling configuration- currently the setting are that pool connections are enabled by default in the ODBC config settings- the databases which I am dealing are quite small- would any one have any ideas perhaps along these lines?

Thanks

Frank srebot

|||

This is an update to my connection Problem with Access 2002 to SQL SERVER EXPRESS 2005.

We are having random disconnects on the client side with Access putting up a "Connection Failure" dialog box even when the user is actively entering records into the system. Have any of you ever encountered this situation? We have disabled all TCP offloading engine technology on the machine thinking this was causing a problem with SQL Server as well as changing network cards to a whole different brand. We've pretty much ruled out the physical network at this point because we have changed cables and moved to another port on a different switch to no avail.

Is there some timeout setting or connection pooling setting that I am unaware of at the SQL Server level that has a problem interacting with Windows Server 2003 or Windows XP? I have checked and double checked all of the server settings between the old machine and the new and they are identical.

I did read that the connection pooling may be stressed and the pool of connections are 'Leaking'. This might be due to a bad cable or connection, but thats all I have found out.

Any info would be greatly appreciated.

Thanks

Frank

Help SQL Server 2000

When I have done to attach database at server with certain collation. So how
to change its collation like as i want?
Because I've tried to use "ALTER DATABASE ... COLLATE ...", But it doesn't
change all tables.
How to to change its collation like as i want so simple?When you say all tables, do you mean some columns. It may be more effective
to set up the db, with required collation and the migrate data
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:#D5vF2XXGHA.3328@.TK2MSFTNGP02.phx.gbl...
> When I have done to attach database at server with certain collation. So
how
> to change its collation like as i want?
> Because I've tried to use "ALTER DATABASE ... COLLATE ...", But it
doesn't
> change all tables.
> How to to change its collation like as i want so simple?
>

Help -sql query

How to do it in a query ?

IDEffDTMonthFirstDay

162028111

16202812123

16202815567

Expect result:

IDEffDTMonthFirstDayEffDTMonthFirstDay

16202811112 123

1620281212315567

1620281556715567

Thanks

Jessie,

Please post the query you ran to get the unexpected results.

You might be able to join the table to itself, but I can't be sure without more information.

|||

It is not clear what you want to get here. If you want the get on the right hand side EffDT column the next value of the left hand side EffDT column then a query that would do it is:

DECLARE @.MyTable TABLE (ID Int, EffDT Int, MonthFirstDay Int)

INSERT INTO @.MyTable

VALUES (162028, 1, 11)

INSERT INTO @.MyTable

VALUES (162028, 12, 123)

INSERT INTO @.MyTable

VALUES (162028, 15, 567)

SELECT ID, EffDT, MonthFirstDay

,EffDT = IsNULL((SELECT TOP 1 EffDT FROM @.MyTable b WHERE b.ID=a.ID AND b.EffDT>a.EffDT ORDER BY EffDT ASC), EffDT)

,MonthFirstDay = IsNull((SELECT TOP 1 MonthFirstDay FROM @.MyTable b WHERE b.ID=a.ID AND b.EffDT>a.EffDT ORDER BY EffDT ASC), MonthFirstDay)

FROM @.MyTable a

Help SQL CE Query - LOW Performance

Hi!

Sorry for bad english, I'm from Brazil.

In microsoft.public.sqlserver.ce haven't found a way to improve performance
of this query. Thanks for any help or reply!

This used to take almost 6 min !!! With index on E.Produto now takes about 30 sec...

1,909 row table

Ipaq 1950 - Samsung 300 Mhz - 32 MB RAM - Windows Mobile 5.0 - SQL CE
2.0

PK (all multiple columns) - tables:

Lotes - pk(Empresa, Lote, Contagem, Produto)

Contagem - pk(Empresa, Lote, Contagem, Produto)

Produtos - pk(Codigo) // this field also is FK <=> Produto in all other
tables

Estoque - pk(Empresa, Ordem, Produto)

Part of my VB.NET code with SQL:

sql_grd_inv = "SELECT L.Empresa, L.Lote, L.Contagem, L.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca" _
& ", Sum(CASE WHEN E.Estoque IS NULL THEN 0 ELSE E.Estoque END)" _
& "AS SomaEstoque, C.Qtde" _
& " FROM (" _
& "(Lotes L " _
& "LEFT JOIN Contagem C ON (L.Empresa = C.Empresa) " _
& "AND (L.Lote = C.Lote) AND (L.Contagem = C.Contagem) " _
& "AND (L.Produto = C.Produto)" _
& ") " _
& "LEFT JOIN Estoque E ON (L.Empresa = E.Empresa) " _
& "AND (L.Produto = E.Produto)" _
& ") " _
& "INNER JOIN Produtos P ON L.Produto = P.Codigo " _
& "GROUP BY L.Empresa, L.Lote, L.Contagem, L.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca, C.Qtde " _
& "HAVING (L.Empresa='" & IncEmpresa & "') " _
& "AND (L.Lote='" & Cbo_Lote_Pnl_Invent.Text & "') AND (L.Contagem='" _
& Cbo_Cont_Pnl_Invent.Text & "') " _
& "UNION " _
& "SELECT " _
& "C.Empresa, C.Lote, C.Contagem, C.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca" _
& ", Sum(CASE WHEN E.Estoque IS NULL THEN 0 ELSE E.Estoque END)" _
& "AS SomaEstoque, C.Qtde" _
& " FROM (" _
& "(Contagem C " _
& "LEFT JOIN Lotes L ON (C.Empresa = L.Empresa) " _
& "AND (C.Lote = L.Lote) AND (C.Contagem = L.Contagem) " _
& "AND (C.Produto = L.Produto)" _
& ") " _
& "LEFT JOIN Estoque E ON (C.Empresa = E.Empresa) " _
& "AND (C.Produto = E.Produto)" _
& ") " _
& "INNER JOIN Produtos P ON C.Produto = P.Codigo " _
& "GROUP BY C.Empresa, C.Lote, C.Contagem, C.Produto" _
& ", P.Unidade, P.Descr, P.Ref, P.Embgem, P.Marca, C.Qtde" _
& ", L.Empresa, L.Lote, L.Contagem, L.Produto " _
& "HAVING (L.Empresa Is Null) AND (L.Lote Is Null) " _
& "AND (L.Contagem Is Null) AND (L.Produto Is Null) " _
& "AND (C.Empresa='" & IncEmpresa & "') " _
& "AND (C.Lote='" & Cbo_Lote_Pnl_Invent.Text & "') AND (C.Contagem='" _
& Cbo_Cont_Pnl_Invent.Text & "') "

Did anyone have queries with more than one LEFT JOIN and 2,000 records?

I've composite PKs and indexes and I don't know if creating single indexes for each column (additionally or replacing the composite ones?) will improve this query. The query processor only use one index to run the query so I don't know wich one is being used.

I've detected that the first query (before the UNION) is the slowest. The second one most of the times returns no records and takes 1-2 seconds.

Here is a more clean version of the query:

SELECT

L.Empresa, L.Lote, L.Contagem, L.Produto,
P.Descr, Sum(CASE WHEN E.Estoque IS NULL THEN 0 ELSE E.Estoque END) AS SomaDeEstoque,
C.Qtde
FROM (
(Lotes L
LEFT JOIN Contagem C ON (L.Empresa = C.Empresa)
AND (L.Lote = C.Lote) AND (L.Contagem = C.Contagem)
AND (L.Produto = C.Produto)
)
LEFT JOIN Estoque E ON (L.Empresa = E.Empresa)
AND (L.Produto = E.Produto)
)
INNER JOIN Produtos P ON L.Produto = P.Codigo
GROUP BY L.Empresa, L.Lote, L.Contagem, L.Produto, P.Descr, C.Qtde
HAVING (L.Empresa='0001') AND (L.Lote='0001') AND (L.Contagem='1')
UNION
SELECT
C.Empresa, C.Lote, C.Contagem, C.Produto,
P.Descr, Sum(CASE WHEN E.Estoque IS NULL THEN 0 ELSE E.Estoque END) AS SomaDeEstoque,
C.Qtde
FROM (
(Contagem C
LEFT JOIN Lotes L ON (C.Produto = L.Produto)
AND (C.Contagem = L.Contagem) AND
(C.Empresa = L.Empresa) AND (C.Lote = L.Lote)
)
LEFT JOIN Estoque E ON (C.Produto = E.Produto)
AND (C.Empresa = E.Empresa)
)
INNER JOIN Produtos P ON C.Produto = P.Codigo
GROUP BY C.Empresa, C.Lote, C.Contagem, C.Produto, P.Descr, C.Qtde,
L.Empresa, L.Lote, L.Contagem, L.Produto
HAVING (L.Empresa Is Null) AND (L.Contagem Is Null) AND (L.Produto Is Null) AND (L.Lote Is Null)
AND (C.Empresa='0001') AND (C.Lote='0001') AND (C.Contagem='1')

Help SQL 2K Security :-) DBA's

Hello everyone,
Here goes another wired on... Currently I have a local test SQL server setup
on my PC. I need to give access to another (entry, verrrrrryyyy entry level)
programmer access to my local server (READ ONLY). but here goes the problem,
I discovered that the employee replicated my db and left it open causing
security issues, since the data contains lots of sensitive data. (credit
cards, soc sec, emp names, etc). Is theirs a way in SQL server to assign
field level security? for example : I want to give him access to employee
names in the employee db, but I don't want him to be able to view employees
socials? the same with customers and credit card information or to restrict
him from exporting any data/scripts. any ideas'
Thanks in advance
AlexThank u!!!!
"alex" <hjhjjhhj@.ghghhg.com> wrote in message
news:%23LWLPaEWDHA.1480@.tk2msftngp13.phx.gbl...
> Hello everyone,
> Here goes another wired on... Currently I have a local test SQL server
setup
> on my PC. I need to give access to another (entry, verrrrrryyyy entry
level)
> programmer access to my local server (READ ONLY). but here goes the
problem,
> I discovered that the employee replicated my db and left it open causing
> security issues, since the data contains lots of sensitive data. (credit
> cards, soc sec, emp names, etc). Is theirs a way in SQL server to assign
> field level security? for example : I want to give him access to employee
> names in the employee db, but I don't want him to be able to view
employees
> socials? the same with customers and credit card information or to
restrict
> him from exporting any data/scripts. any ideas'
> Thanks in advance
> Alex
>|||"alex" <hjhjjhhj@.ghghhg.com> wrote in message
news:eVCvrHFWDHA.2008@.TK2MSFTNGP11.phx.gbl...
> Thank u!!!!
>
> "alex" <hjhjjhhj@.ghghhg.com> wrote in message
> news:%23LWLPaEWDHA.1480@.tk2msftngp13.phx.gbl...
> > Hello everyone,
> >
> > Here goes another wired on... Currently I have a local test SQL server
> setup
> > on my PC. I need to give access to another (entry, verrrrrryyyy entry
> level)
> > programmer access to my local server (READ ONLY). but here goes the
> problem,
> > I discovered that the employee replicated my db and left it open causing
> > security issues, since the data contains lots of sensitive data. (credit
> > cards, soc sec, emp names, etc). Is theirs a way in SQL server to assign
> > field level security? for example : I want to give him access to
employee
> > names in the employee db, but I don't want him to be able to view
> employees
> > socials? the same with customers and credit card information or to
> restrict
> > him from exporting any data/scripts. any ideas'
> >
> > Thanks in advance
> >
> > Alex
> >
> >
I have to add one more note to this even though you've gotten your answer
and probably moved on. You said you have a "local test SQL server" setup.
You should never have legitimate, sensitive customer/employee information in
a test database. There's just no need for it, and you're taking a huge risk
of that information falling into the wrong hands. Scramble or remove the
data in the sensitive columns immediately.
If you need a legitimate CCN or SSN for testing (such as validation
routines) then pull out your wallet and use your own. Risking compromise of
a customer or employee's personal information is just insane.
Ryan LaNeve

Sunday, February 26, 2012

Blog Archive

About Me