Re: Using dynamic SQL - Database Discussion Boards

Re: group by value in the prefix of the values of that group

indian14329-Jun-10 12:57

29-Jun-10 12:57

Sorry for troubling you but it is giving error "Incorrect syntax near the keyword 'FOR'". And I dont know much about using FOR in T-SQL. If possible can you pls. help me in that. Why is it coming. I tried by moving ")", but doesnt work.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

Re: group by value in the prefix of the values of that group

i.j.russell29-Jun-10 21:38

i.j.russell

29-Jun-10 21:38

I have checked the code in Sql Server Management Studio 2008 and 2005 by creating a new query, copying the code into it and running it; It works fine. How are you trying to run the code?

Re: group by value in the prefix of the values of that group

indian14328-Jun-10 11:51

indian143

28-Jun-10 11:51

Hi Russel,

I have give the example in the previous reply, can you please give me any solution to do it. Its urgent requirement.

For example
You have
Cat1 Item1
Cate2 Item 2
Cate3 Item3
Cate1 Item 2
Cate1 Item3
Cate2 Item1
Cate2 Item 3
Cate3 Item1
Cate3 Item2
Then my output should look as follows
cate1: Item1, Item2, ITem3/Cate2: Item1, Item2, Item3/Cate3:Item1, Item2, Item3.
Can you please help me in achieving it, in performance oriented way. They need that too.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

Re: group by value in the prefix of the values of that group

i.j.russell28-Jun-10 20:44

i.j.russell

28-Jun-10 20:44

Please do not demand answers otherwise you will find that no one will help you.

Re: group by value in the prefix of the values of that group

indian14328-Jun-10 20:49

indian143

28-Jun-10 20:49

I am sorry pls. I did mistake pls. forgive me. In hurry I did a blunder. Please sorry.

Thanks & Regards,

Md. Abdul Aleem
NIIT technologies

Using dynamic SQL

mjackson1128-Jun-10 5:25

mjackson11

28-Jun-10 5:25

We have a legacy database that is "configurable" by setting values in a settings table. Many of the SP in the database then pull values from this table and create dynamic SQL to query the DB. We are now having performance issues with these queries.

As an example, we push data to another DB on the same server. The name of that database is stored in the table. The SP retrieves the name, creates a SQL statement and then uses sp_execute to execute the statement.

Is there a better way to handle this so that the SQL can be compiled and optimized?

Thx very much.

Mark Jackson

Re: Using dynamic SQL

David Mujica28-Jun-10 6:05

David Mujica

28-Jun-10 6:05

Before you make any changes, verify where the performance degredation is.

Have you used SQL Profiler ? You can trace all kinds of things and try to determine what is going on. Generally, if you see high CPU usage on database server, it is an indication of lots of dynamic SQL being parsed / executed. You should see low CPU and lots of I/O on a normally operating db server.

Maybe you just need to re-create the indexes.

Re: Using dynamic SQL

PIEBALDconsult29-Jun-10 18:29

PIEBALDconsult

29-Jun-10 18:29

mjackson11 wrote:
pull values from this table and create dynamic SQL

That's insane, but doing it in other code probably won't make much of an improvement.

However, what I would prefer to do is to have a Connection to each database open and copy the data via a DataReader and ExecuteNonQuery. One of the benefits of this technique (in my experience) is the ability to log progress and errors as appropriate. This also makes moving one of the databases to another server less problematic.

Re: Using dynamic SQL

Tim Carmichael30-Jun-10 7:29

Tim Carmichael

30-Jun-10 7:29

PIEBALDconsult wrote:
That's insane

Insane it may be, but, there are business cases where this method is, in fact, required.
We have a data warehouse system that needs to extract data from a various third party or legacy systems. We have no control over if and when the database structures will change in the other systems and we do not have access to the databases to add views to define the data we need.

Since I, personally, DETEST seeing SQL statements hard-coded in an application, I am left with the option of storing the SQL statements for use at run-time.

So.... the SQL statements are stored in a database and SQL statements are dynamically 'built' for execution.

Is it perfect? No.
Does it work? Yes.

If someone can present a better solution that fits into the business climate at hand, I would be more than willing to listen.

Tim

Re: Using dynamic SQL

PIEBALDconsult30-Jun-10 15:57

PIEBALDconsult

30-Jun-10 15:57

Tim Carmichael wrote:
Since I, personally, DETEST seeing SQL statements hard-coded in an application

That's where they belong, so you know they can't be easily changed. And it is to be hoped that any changes are properly tracked in your version control system with a proper paper trail and then tested.

Stored procedures are too fragile and should be avoided at all costs. I've even had stored procedures "disappear"... there one day, gone the next.

xp_cmdshell

It_tech28-Jun-10 2:11

It_tech

28-Jun-10 2:11

Hi All,

I am Stuck with xp_cmdshell to execute a Vb Script file?

Can Any one tell me about the folder permission i have to do on the folder when excuting through commandshell?

Thanks

Re: xp_cmdshell

J4amieC28-Jun-10 2:28

J4amieC

28-Jun-10 2:28

Do not repost the same question. Just continue with your thread below.

Re: xp_cmdshell

It_tech28-Jun-10 3:17

It_tech

28-Jun-10 3:17

Sorry Its Just by Mistake.

Binding source does not update when the linked table is updated in code.

Christian Yeates28-Jun-10 0:17

Christian Yeates

28-Jun-10 0:17

Hello All,

I am currently in the process of adapting an application that previously worked only with Microsoft Access to work with SQL server or Access. Previously, all database interaction was designed based upon using the XSD file to create queries (etc).

I wasn't sure how to handle the new situation with two types of database using the visual methods (e.g. did not want two separate datasets (etc) so I created a static class that contains a function as follows:

<br />
public static DataTable SelectRows(string connectionString, string queryString)<br />

The function will connect to either type of DB based on the connection string and will run the query held in queryString returning a table of the selected records.

I create a binding source and assign it to a table called masterTable as follows:

<br />
cards_photo_accesslevelBindingSource.DataSource = masterTable;<br />

I then have a DataGridView that points to the binding source as follows:

<br />
dgNavigatorTable.DataSource = cards_photo_accesslevelBindingSource;<br />

At various times in my code, I call the SelectRows method and store the returned table over the top of the masterTable as follows:

<br />
masterTable = MyQuery.SelectRows(MyConnectionStrings.getConnectionString(),<br />
            "SELECT * FROM (Cards LEFT JOIN Photo ON Cards.Card = Photo.Id)LEFT JOIN AccLevel ON Cards.AccessLevel = AccLevel.AccessLevel WHERE Cards.FirstName LIKE '" + tbFirstName.Text + "%'");<br />

My first concern is that the binding source does not update in relation to the update of the table.

I have found that the only way to get the binding source and associated DataGridView to update after my masterTable has changed is to re-assign it as follows:

<br />
// This seems necessary to update the navigator linked to the binding source<br />
cards_photo_accesslevelBindingSource.DataSource = masterTable;<br />

Even using this naive technique, I also need at this point the PositionChanged event to fire on the binding source as it used to when I would call a Fill method on the datasource before I replaced this method with my SelectRows method.

I haven't done much C# for a while and previously have always used an XSD file for my database connection and queries and therefore I assume that I am using the binding source and DataTable in an illegal and naive way? I will keep searching the web for an answer and apologise in advance for my lack of understanding.

Regards,

Chris

xp_cmdshell [modified]

It_tech26-Jun-10 23:27

It_tech

26-Jun-10 23:27

Hi All,

I have a very strange problem with xp_cmdshell. Here is a Stored Procedure that is meant to execute a VB script file.When i run the VB Script independently it works fine.

But when i execute the stored procedure that is pointing to this VB script file it runs with No error But Doesnt do the work.

Stored Procedure

USE [Database]
GO
/****** Object: StoredProcedure [dbo].[sp_SendSMS] Script Date: 06/27/2010 10:14:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_SendSMS]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @Numbers NVARCHAR(200)
DECLARE @Body NVARCHAR(160)
DECLARE @Command NVARCHAR(500)

-- Insert statements for procedure here
SET @Numbers = '00442345672'
SET @Body = CAST(GETDATE() AS NVARCHAR(20)) + ' Test Message'

-- TODO: Get Revenue Statistics and append them to @Command
SET @Command = 'd:\test\SendSMS.vbs /Numbers:' + @Numbers + ' /Body: "' + @Body + '"'

EXEC xp_cmdshell @Command
END

The VB script file is under test folder on D drive which has Read write permission set on the folder.

Set WshShell = WScript.CreateObject("WScript.Shell")
sNumbers= WScript.Arguments.Named.Item("numbers")
sBody= WScript.Arguments.Named.Item("body")
URL = "http://sampleweb.com/samplepage.aspx?IFVERSION=210000&MESSAGETYPE=10&OADC=00447956053317&MESSAGEID=3333&RECEIVETIME=20070320151916&BODY=This%20is%20my%204th%20message%20on%20the%20LIVE%20TV&MCLASS=2&HEADER=0048001&DESTADDRESS=1234&CONNECTION=MIG01l1&DCS=240&RETRYCOUNT=3&PID=0&AVSTATUS=1&GUID=9BBA5E93-390C-450C-94C3-1122E5D4858B"
WshShell.Run(URL)
Set WshShell = Nothing

When i run the VBS file Independently it works fine.However when running the stored procedure above which contains the file i dont see the file being excuted.

Could You please advice what is going Worng?

Thanks for your time.

modified on Sunday, June 27, 2010 6:43 AM

Re: xp_cmdshell

J4amieC27-Jun-10 21:53

J4amieC

27-Jun-10 21:53

So let me get this straight; you're using a stored proc, to execute a VBS file which makes an http request to send an SMS? Multiple points of failure doesnt even begin to describe this monster.

Anyway, in answer to your question, its almost certainly a permissions problem. When you execute the VBS file directly, its running in the context of the currently logged in user. When you run it using xp_cmdshell its running in the context of whichever user SQL Server is running as.

Re: xp_cmdshell

It_tech28-Jun-10 2:21

It_tech

28-Jun-10 2:21

Thanks for your reply.Its Much appreciated.I have already checked and changed the user who loggs in as An is An administartor.

Is there any thing i have to do to force the Login name to remain as the one who had access to that particualr folder.

Many thanks for your Info.

Re: xp_cmdshell

J4amieC28-Jun-10 3:32

J4amieC

28-Jun-10 3:32

There are so many things wrong with that response.

It_tech wrote:
I have already checked and changed the user who loggs in as An is An administartor

That is why you can run the VBS manually. your logged in user is an admin, and as an admin has rights to run the script. SQL Server runs as a service, it knows nothing about your logged in user, and has a completely different set of permissions.

It_tech wrote:
Is there any thing i have to do to force the Login name to remain as the one who had access to that particualr folder.

This problem has exactly nothing to do with folder permissions. You can give a user permission to read a file from a folder, but they may still be denied things like "Execute processes".

If you have acccess to sys admins, ask them for help. If you dont then start reading up on the permission system in operation for the OS you're using. It varies quite substantially between older generation servers like Win2K and new generation servers like Win2K8.

Finally, nobody here will be able to help you on this problem, as none of us have access to your servers, and all servers are set up differently.

Re: xp_cmdshell

It_tech28-Jun-10 4:09

It_tech

28-Jun-10 4:09

Thanks for your Help.

Re: xp_cmdshell [modified]

It_tech28-Jun-10 3:28

It_tech

28-Jun-10 3:28

Thanks for the Reply.

I have checked the login on Sql Server 2008 using Services.msc and changed
the login to the login i use for windows.

Its is still the same.

Is there any advice you can suggest?

Thanks.

modified on Monday, June 28, 2010 9:34 AM

Query for multiple occurance

Nikhil Bhivgade26-Jun-10 2:16