how to execute a function in linked server - Database Discussion Boards

Re: Week of the month

PIEBALDconsult11-Sep-07 9:57

PIEBALDconsult

11-Sep-07 9:57

Months don't have weeks, years have weeks. At least that seems to be what ISO says. Big Grin | :-D

Re: Week of the month

DerekFL12-Sep-07 5:24

DerekFL

12-Sep-07 5:24

SELECT CAST(DATEPART(wk,getdate())-DATEPART(wk,DATEADD(dd, -(DATEPART(dd, GETDATE()) - 1), GETDATE()))+1 as varchar)

back up sql server databases from server to local

Saeed.39411-Sep-07 1:15

Saeed.394

11-Sep-07 1:15

Hi,i want to know that how can i get schedule back up from server to local ??
for example :
i have a database ("testDB")in the server ( www.server.com) then i need get backup every day and transfer all new data to my PC in (localhost ).

thanks.

Re: back up sql server databases from server to local

rohitsrivastava11-Sep-07 23:27

rohitsrivastava

11-Sep-07 23:27

objsqlconn=new sqlconnection("user id=sa;password=;data source=servername")
objsqlconn.open

objsqlcmd=new sqlcommand
objsqlcmd.connection=objsqlconn
objsqlcmd.commandtext="master..xp_cmdshell 'del d:\" + databasename + "'"
objsqlcmd.commandtype=commandtype.text
objsqlcmd.executenonquery

objsqlcmd.commandtext=" backup database " + databasename + " to disk='" + databasename + "'"
objsqlcmd.commandtype=commandtype.text
objsqlcmd.executenonquery
this is coding to backup of database from the server

how to convert hex value????

sonal dadhania110-Sep-07 21:09

sonal dadhania1

10-Sep-07 21:09

I have a string of hex characters "0x89504e". I convert this to a string of binary characters @sOutput = "100010010101000001001110". I then run SELECT CAST(@sOutput AS BINARY). This returns me "0x313030303130303130313031303030303031303031313130000000000000".

How would I go about converting the string to binary such that the value would return as the original "0x89504e"? It's looking like the CAST is treating the string as numeric and converting a really large int to binary.

Re: how to convert hex value????

Blue_Boy10-Sep-07 21:37

Blue_Boy

10-Sep-07 21:37

SELECT CONVERT(varbinary(20), 'stringValue')

I Love SQL

Re: how to convert hex value????

Colin Angus Mackay10-Sep-07 23:45

Colin Angus Mackay

10-Sep-07 23:45

sonal dadhania1 wrote:
It's looking like the CAST is treating the string as numeric and converting a really large int to binary

It is treating the string as a string and converting it in to its ASCII codes.

I don't use binary data types much, but the following works:

DECLARE @hex AS VARBINARY(10)
SET @hex = 0x89504e
SELECT @hex

NOTE: There are no quotes/apostrophes around the hex value.

If you are coming from an application, use varbinary parameters and pass the value to the paramter as a byte array (byte[])

Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ...

My website

how to execute a function in linked server

Sebastian T Xavier10-Sep-07 20:33

Sebastian T Xavier

10-Sep-07 20:33

Hi all,

I have a function called fnGetOrderItemPriceDummy, which takes two parameters, such as @OrderID & @chord. I was trying to execute this function in our linked server, and it fired error. My code follows.....

(Select * from Openquery([linkedserver],'select myserver.dbo.fnGetOrderItemPriceDummy ( '+ Convert(varchar(50) ,@OrderID)+' , '''''+ @chord+'''''))

.

Can anyone say where is my mistake....

Thanks for your time.....

Sebastian

Re: how to execute a function in linked server

Marek Grzenkowicz12-Sep-07 1:16

Marek Grzenkowicz

12-Sep-07 1:16

I don't know syntax of OPENQUERY command, but I am sure that you are missing a database name here:

select myserver.DATABASE.dbo.fnGetOrderItemPriceDummy

How do I do this in SQL

Yitzchok Dev10-Sep-07 14:57

Yitzchok Dev

10-Sep-07 14:57

I have these tables

Customer Table:
CustomerID int; PK
FirstName nvarchar;
LastNaem nvarchar;
etc.;

Products Table:
ProductID int; PK
Name varcahr;
etc.;

OrderItems Table:
OrderItemID int; PK
ProductID int; FK
CustomerID int; FK
etc.;

Now I would like to get all users that have ordered: Product1 AND Product2, etc.
Only those people that orders all the selected products

-- modified at 21:15 Monday 10th September, 2007

Re: How do I do this in SQL [modified]

Chetan Patel10-Sep-07 19:43

Chetan Patel

10-Sep-07 19:43

Pass @Products --> with comma separated values like 'product1,product2'
and no of items @ProductCount --> 2

Select * from Customers Where CustomerID in
(Select CustomerID From
(Select CustomerID from OrderItems Inner Join Products on Products .ProductID = OrderItems.productid
Where [Name] IN (@products)
Group by CustomerID,[Name]) as Temp
Group by CustomerID having Count(CustomerID) = @productCount)

Best Regards,
Chetan Patel

Huh?

Arjan Einbu10-Sep-07 22:15

Arjan Einbu

10-Sep-07 22:15

This can't possibly work, can it?

I mean, the WHERE [Name] IN (@Products) part specifically.

Re: Huh?

John-ph10-Sep-07 23:15

John-ph

10-Sep-07 23:15

If this has to work then the query should get execute using "EXEC sp_executesql" command

Regards
J O H N

"Even eagles need a push." David McNally

Re: How do I do this in SQL

Arjan Einbu10-Sep-07 22:58

Arjan Einbu

10-Sep-07 22:58

You could create the inner query like this:

SELECT oi.CustomerID
FROM OrderItems oi
	JOIN Products p
WHERE p.ProductID IN ('Product1', 'Product2')
GROUP BY oi.CustomerID
HAVING COUNT(*) = 2

You'll have to dynamically create the WHERE p.ProductID IN (...) part, and the value for the HAVING COUNT(*) = 2 should use a parameter.

Joined with the customer table, the results could look something like this:

DECLARE @NumberOfProducts int
SET @NumberOfProducts = 2

SELECT
	c.*
FROM Customer c
	JOIN
	(
		SELECT oi.CustomerID
		FROM OrderItems oi
			JOIN Products p
		WHERE p.ProductID IN ('Product1', 'Product2')
		GROUP BY oi.CustomerID
		HAVING COUNT(*) = @NumberOfProducts
	) AS t
		ON t.CustomerID = c.CustomerID

Re: How do I do this in SQL

Yitzchok Dev17-Sep-07 6:44

Yitzchok Dev

17-Sep-07 6:44

Thank you

analysis service sql2005

ABBASI_RA9-Sep-07 20:14

ABBASI_RA

9-Sep-07 20:14

Notify when a special partition in analysis service must update?

how i can triger a program to update a special partition of a cube at a required time?

or

how to found a way to know if a special partition need to be update?

thanks

connecting the sql db in the server

prasadbuddhika9-Sep-07 19:01

prasadbuddhika

9-Sep-07 19:01

when you want to connect to the db in the server do i have to use sqloledb provider , can't i use sqlclient provider. Smile | :)

Re: connecting the sql db in the server

Blue_Boy9-Sep-07 21:02

Blue_Boy

9-Sep-07 21:02

http://wwww.connectionstrings.com

I Love SQL

Re: connecting the sql db in the server

Rami Said Abd Alhalim9-Sep-07 23:34

Rami Said Abd Alhalim

9-Sep-07 23:34

you want to add port on firewall port number = 1433 to you can use sqlclient

Rami Abdalhalim

Re: connecting the sql db in the server

Frank Kerrigan10-Sep-07 9:24

Frank Kerrigan

10-Sep-07 9:24

sqlclient uses a an ole connection and you can use to connect it to SQL Server, this is the prefered method and is used in most examples.

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

a mistake with the "Like" statement..help!..

keremburak9-Sep-07 8:23

keremburak

9-Sep-07 8:23

dear friends,

I develop an Win application by using MS Access.I have got a problem in SQL statement.I have got a form that has

got 2 ComboBox and a DataGridView.The name of the first is cmbCategories and the second one is cmbContent.Content

is listing the content of categories.I use an array to transfer letters when I choose the category in

cmbCategories as it is Alphabetical Array.When I choose a letter from cmbContent like A,B or another one,but no

records is listing in the DataGridView.I try to use "Like" statement for this action.It is like this:

string strSQL = "select FilmName from films where FilmName like" + "'" + cmbContent.Text + "*'";

cmbContent has got the Alphabetical letters by sequence.I want to list the FilmName that is beginning as

alphabetic.For example,when I choose letter A,the films begins A must be listed.Does anybody help me?Thanks for

your help.

Re: a mistake with the "Like" statement..help!..

Blue_Boy9-Sep-07 8:33

Blue_Boy

9-Sep-07 8:33

string strSQL = "select FilmName from films where FilmName like '" + cmbContent.Text + "*'";

I Love SQL

Re: a mistake with the "Like" statement..help!..

Eddie Y Chen9-Sep-07 17:17

Eddie Y Chen

9-Sep-07 17:17

You should use parameterized SQL commands to prevent SQL injections... Smile | :)

Re: a mistake with the "Like" statement..help!.. [modified]

keremburak9-Sep-07 23:47

keremburak

9-Sep-07 23:47

I tried,but it doesnt work.I wrote the whole code below.maybe it helps what I want to do.

private DataView Get_DataAlphabetic()
{
string constr = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + Environment.CurrentDirectory + "\\mdb\\film_db.mdb";

OleDbConnection con_ole = new OleDbConnection(constr);

string strSQL = "select FilmName from films where FilmName like " + "'" + cmbContent.Text + "*'";
OleDbDataAdapter da = new OleDbDataAdapter(strSQL, con_ole);
DataTable dt = new DataTable();
da.Fill(dt);
DataView dv = new DataView(dt);
dv.Sort = "FilmName ASC";
da.Dispose();
con_ole.Close();
return dv;
}

I send it to a datagrid's datasource property in another method as I use cmobobox's SelectedIndexChanged method.Hope it is clear.I wait for your replies

-- modified at 5:58 Monday 10th September, 2007

Re: a mistake with the "Like" statement..help!..

Blue_Boy10-Sep-07 22:59

Blue_Boy

10-Sep-07 22:59

Why you aren't trying to use Filter instead of getting directrly from database.

Download all data at first time from database then use filter property.

I Love SQL

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.